How to find the Index with Fragmentation issue

SELECT db.name AS databaseName ,  ps.OBJECT_ID AS objectID
, ps.index_id AS indexID,  ps.partition_number AS partitionNumber
, ps.avg_fragmentation_in_percent AS fragmentation , ps.page_count
FROM sys.databases db
INNER JOIN sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, N'Limited') ps
ON db.database_id = ps.database_id
WHERE ps.index_id > 0
AND ps.page_count > 100 AND ps.avg_fragmentation_in_percent > 30
OPTION (MaxDop 1)

List all the DMVs and DMFs in SQL Server 2008

You can able to see 136's DMV's and DMF's return for the below query

use master
go
SELECT 'sys.' + name FROM sys.sysobjects WHERE name like 'dm%'


sys.dm_audit_actions


sys.dm_audit_class_type_map

sys.dm_broker_activated_tasks

sys.dm_broker_connections

sys.dm_broker_forwarded_messages

sys.dm_broker_queue_monitors

sys.dm_cdc_errors

sys.dm_cdc_log_scan_sessions

sys.dm_clr_appdomains

sys.dm_clr_loaded_assemblies

sys.dm_clr_properties

sys.dm_clr_tasks

sys.dm_cryptographic_provider_algorithms

sys.dm_cryptographic_provider_keys

sys.dm_cryptographic_provider_properties

sys.dm_cryptographic_provider_sessions

sys.dm_database_encryption_keys

sys.dm_db_file_space_usage

sys.dm_db_index_operational_stats

sys.dm_db_index_physical_stats

sys.dm_db_index_usage_stats

sys.dm_db_mirroring_auto_page_repair

sys.dm_db_mirroring_connections

sys.dm_db_mirroring_past_actions

sys.dm_db_missing_index_columns

sys.dm_db_missing_index_details

sys.dm_db_missing_index_group_stats

sys.dm_db_missing_index_groups

sys.dm_db_partition_stats

sys.dm_db_persisted_sku_features

sys.dm_db_script_level

sys.dm_db_session_space_usage

sys.dm_db_task_space_usage

sys.dm_exec_background_job_queue

sys.dm_exec_background_job_queue_stats

sys.dm_exec_cached_plan_dependent_objects

sys.dm_exec_cached_plans

sys.dm_exec_connections

sys.dm_exec_cursors

sys.dm_exec_plan_attributes

sys.dm_exec_procedure_stats

sys.dm_exec_query_memory_grants

sys.dm_exec_query_optimizer_info

sys.dm_exec_query_plan

sys.dm_exec_query_resource_semaphores

sys.dm_exec_query_stats

sys.dm_exec_query_transformation_stats

sys.dm_exec_requests

sys.dm_exec_sessions

sys.dm_exec_sql_text

sys.dm_exec_text_query_plan

sys.dm_exec_trigger_stats

sys.dm_exec_xml_handles

sys.dm_filestream_file_io_handles

sys.dm_filestream_file_io_requests

sys.dm_fts_active_catalogs

sys.dm_fts_fdhosts

sys.dm_fts_index_keywords

sys.dm_fts_index_keywords_by_document

sys.dm_fts_index_population

sys.dm_fts_memory_buffers

sys.dm_fts_memory_pools

sys.dm_fts_outstanding_batches

sys.dm_fts_parser

sys.dm_fts_population_ranges

sys.dm_io_backup_tapes

sys.dm_io_cluster_shared_drives

sys.dm_io_pending_io_requests

sys.dm_io_virtual_file_stats

sys.dm_os_buffer_descriptors

sys.dm_os_child_instances

sys.dm_os_cluster_nodes

sys.dm_os_dispatcher_pools

sys.dm_os_dispatchers

sys.dm_os_hosts

sys.dm_os_latch_stats

sys.dm_os_loaded_modules

sys.dm_os_memory_allocations

sys.dm_os_memory_brokers

sys.dm_os_memory_cache_clock_hands

sys.dm_os_memory_cache_counters

sys.dm_os_memory_cache_entries

sys.dm_os_memory_cache_hash_tables

sys.dm_os_memory_clerks

sys.dm_os_memory_node_access_stats

sys.dm_os_memory_nodes

sys.dm_os_memory_objects

sys.dm_os_memory_pools

sys.dm_os_nodes

sys.dm_os_performance_counters

sys.dm_os_process_memory

sys.dm_os_ring_buffers

sys.dm_os_schedulers

sys.dm_os_spinlock_stats

sys.dm_os_stacks

sys.dm_os_sublatches

sys.dm_os_sys_info

sys.dm_os_sys_memory

sys.dm_os_tasks

sys.dm_os_threads

sys.dm_os_virtual_address_dump

sys.dm_os_wait_stats

sys.dm_os_waiting_tasks

sys.dm_os_worker_local_storage

sys.dm_os_workers

sys.dm_qn_subscriptions

sys.dm_repl_articles

sys.dm_repl_schemas

sys.dm_repl_tranhash

sys.dm_repl_traninfo

sys.dm_resource_governor_configuration

sys.dm_resource_governor_resource_pools

sys.dm_resource_governor_workload_groups

sys.dm_server_audit_status

sys.dm_sql_referenced_entities

sys.dm_sql_referencing_entities

sys.dm_tran_active_snapshot_database_transactions

sys.dm_tran_active_transactions

sys.dm_tran_commit_table

sys.dm_tran_current_snapshot

sys.dm_tran_current_transaction

sys.dm_tran_database_transactions

sys.dm_tran_locks

sys.dm_tran_session_transactions

sys.dm_tran_top_version_generators

sys.dm_tran_transactions_snapshot

sys.dm_tran_version_store

sys.dm_xe_map_values

sys.dm_xe_object_columns

sys.dm_xe_objects

sys.dm_xe_packages

sys.dm_xe_session_event_actions

sys.dm_xe_session_events

sys.dm_xe_session_object_columns

sys.dm_xe_session_targets

sys.dm_xe_sessions

DMVs for Transaction Blocking

Blocking the process id in sql server main concern to all dba's, how to trouble shoot using DMV's

Sys.dm_tran_locks            -- find who is locking what
Sys.dm_os_waiting_tasks  --  find what is waiting
Sys.dm_db_index_operational_stats(DMF) -- find  blocking by object

e.g:
1. select * from Sys.dm_tran_locks
2. select * from Sys.dm_os_waiting_tasks order by wait_duration_ms desc

3. --select * from Sys.dm_db_index_operational_stats

--Tables where the most latch contention is occurring

select object_schema_name(ddios.object_id) + '.' + object_name(ddios.object_id) as objectName,
indexes.name, case when is_unique = 1 then 'UNIQUE ' else '' end + indexes.type_desc as index_type,
page_latch_wait_count , page_io_latch_wait_count
from sys.dm_db_index_operational_stats(db_id(),null,null,null) as ddios
join sys.indexes
on indexes.object_id = ddios.object_id
and indexes.index_id = ddios.index_id
order by page_latch_wait_count + page_io_latch_wait_count desc

SQL Server Migration Assistant (SSMA) v5.0

SQL Server Migration Assistant (SSMA) v5.0 released by SQL Server Product Group on April 2011. This tool provide automating the migration of Oracle, Sybase, MySQL databses and Microsoft Access databases to SQL Server.
New features includes:

• Migrating to SQL Server upcoming version Denali
• Globalization support for migrating non-English databases to SQL Server
• Support for installation of an extension pack on clustered SQL Server environments
• A number of enhancements focused on Sybase and Oracle migration
• Improved scale and performance of the migration itself
 
Source : http://blogs.msdn.com/b/sqlcat/archive/2011/04/30/sql-server-migration-assistant-ssma-v5-0-just-released.aspx

To change the time-out value for mirroring database (high-safety mode only)

You can change timeout value for database mirroring.

use master
go
ALTER DATABASE  db_name SET PARTNER TIMEOUT 60

go

SELECT Mirroring_Connection_Timeout
FROM sys.database_mirroring
WHERE database_id = db_id('db_name')
go

--Output
60

SSIS Package Deployment via CMD

1. Copy SSIS Packages into specific folder e.g: d:\mssql\ssis\SSIS_Packages

2. Please type cmd in Run and then execute the below statements

3. DTUTIL /FILE d:\ app\ssis\SSIS_Packages\mypackage.DTSX /COPY SQL;mypackage /QUIET

 

SSIS Package Deployment Check


Connect to “SQL Server Integration Services” and you should see the SSIS Packages similar to the below.

 mypackage

How often checkdb should be run against database?

I would recommend to run CHECKDB against database once in a day in the midnight.

As part of regular database maintenance job i scheduled to run checks the logical and physical integrity of all the objects in the specified database.

when you run dbcc checkdb performing the following opearation:

Runs DBCC CHECKALLOC on the database.
Runs DBCC CHECKTABLE on every table and view in the database.
Runs DBCC CHECKCATALOG on the database.
Validates the contents of every indexed view in the database.
eg:
-- Check the AdventureDb database.
DBCC CHECKDB (AdventureDb)
GO

--Specifying PHYSICAL_ONLY causes DBCC CHECKDB to skip all checks of FILESTREAM data.
USE AdventureWorks
GO
DBCC CHECKDB WITH PHYSICAL_ONLY

-- Check the AdventureWorks database without nonclustered indexes.
DBCC CHECKDB (AdventureWorks, NOINDEX);
GO

A fatal error occurred during installation.

When you try to install SQL Server 2008 sample database AdventureWorks2008_SR4. If your sql server service not started before you will get the following error message

A fatal error occurred during installation.
Details: Object reference not set to an instance of an object.

Solution : Make sure SQL Server service is running

Find the database restore history

When ever you restored database from the backup file the sql server stored information on msdb database table restorehistory.

SELECT restore_date ,destination_database_name
FROM msdb..RestoreHistory
ORDER BY restore_date DESC

How to find the Linked Servers in SQL Server

You can run the below system stored procedure to find the list of linked servers configured on instance of sql servers.

sp_linkedservers

Database created date and sql server restarted time

Running the below query you can see the databases created date and time


select name, crdate from sys.sysdatabases order by crdate

Also you can run the below query to find the sql server last time restarted.

select name, crdate from sys.sysdatabases WHERE name = 'tempdb'

Diagnosing tempdb Disk Space Problems

-- Returns space usage of each file in tempdb

select * from sys.dm_db_file_space_usage


--Determining the Amount of Free Space in tempdb
SELECT SUM(unallocated_extent_page_count) AS [free pages],
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;


--Determining the Longest Running Transaction
SELECT transaction_id
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;


--Determining the Amount of Space Used by Internal Objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;

--Determining the Amount of Space Used by Internal and user Objects in tempdb
SELECT SUM(internal_object_reserved_page_count) as Internal_Pages,
(SUM(internal_object_reserved_page_count)*1.0/128) as Internal_space_MB,
SUM(User_object_reserved_page_count) as User_Pages,
(SUM(internal_object_reserved_page_count)*1.0/128) as User_space_MB
FROM sys.dm_db_file_space_usage;



--Determining the Total Amount of Space (Free and Used)
SELECT SUM(size)*1.0/128 AS [size in MB]
FROM tempdb.sys.database_files