Finding The Top Ten Cumulative Wait Events.

SELECT TOP

10

wait_type

,

max_wait_time_ms wait_time_ms

,

signal_wait_time_ms

,

wait_time_ms

- signal_wait_time_ms AS resource_wait_time_ms ,

100.0

* wait_time_ms / SUM(wait_time_ms) OVER ( )

AS

percent_total_waits ,

100.0

* signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )

AS

percent_total_signal_waits ,

100.0

* ( wait_time_ms - signal_wait_time_ms )

/

SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits

FROM

sys.dm_os_wait_stats

WHERE

wait_time_ms > 0 -- remove zero wait_time

AND

wait_type NOT IN -- filter out additional irrelevant waits

(

'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',

'SQLTRACE_BUFFER_FLUSH'

,'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',

'LAZYWRITER_SLEEP'

, 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',

'BROKER_EVENTHANDLER'

, 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',

'CHECKPOINT_QUEUE'

, 'FT_IFTS_SCHEDULER_IDLE_WAIT',

'BROKER_TRANSMITTER'

, 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',

'LOGMGR_QUEUE'

, 'ONDEMAND_TASK_QUEUE',

'REQUEST_FOR_DEADLOCK_SEARCH'

, 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',

'DBMIRROR_EVENTS_QUEUE'

, 'BROKER_RECEIVE_WAITFOR',

'PREEMPTIVE_OS_GETPROCADDRESS'

, 'PREEMPTIVE_OS_AUTHENTICATIONOPS',

'WAITFOR'

, 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',

'RESOURCE_QUEUE'

)

ORDER BY

wait_time_ms DESC

Finding Count of Logical CPU

SELECT cpu_countFROM sys.dm_os_sys_info
GO-- Identify Virtual Processors in for SQL Server 2000CREATE TABLE #TempTable([Index] VARCHAR(2000),[Name] VARCHAR(2000),[Internal_Value] VARCHAR(2000),[Character_Value] VARCHAR(2000)) ;INSERT INTO #TempTableEXEC xp_msver;SELECT Internal_Value AS VirtualCPUCountFROM #TempTableWHERE Name = 'ProcessorCount';DROP TABLE #TempTableGO

Last or Recently Ran Query

SELECT
deqs.last_execution_time AS [Time], dest.TEXT AS [Query]

FROM sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

ORDER BY deqs.last_execution_time DESC

Query To Find Out Full Details of Databases

SELECT database_id,CONVERT(VARCHAR(25), DB.name) AS dbName,CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],state_desc,
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],
(SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,
(SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],user_access_desc AS [User access],recovery_model_desc AS [Recovery model],CASE compatibility_levelWHEN 60 THEN '60 (SQL Server 6.0)'WHEN 65 THEN '65 (SQL Server 6.5)'WHEN 70 THEN '70 (SQL Server 7.0)'WHEN 80 THEN '80 (SQL Server 2000)'WHEN 90 THEN '90 (SQL Server 2005)'WHEN 100 THEN '100 (SQL Server 2008)'END AS [compatibility level],CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],-- last backupISNULL((SELECT TOP 1CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +' (' + CAST(DATEDIFF(second, BK.backup_start_date,BK.backup_finish_date) AS VARCHAR(4)) + ' '+ 'seconds)'FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup],CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [fulltext],CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose],page_verify_option_desc AS [page verify option],CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [read only],CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink],CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto create statistics],CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto update statistics],CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [standby],CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE '' END AS [cleanly shutdown]FROM sys.databases DBORDER BY dbName, [Last backup] DESC, NAME

Script To Identify Blocking Query

SELECTdb.name DBName,tl.request_session_id,wt.blocking_session_id,OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,tl.resource_type,h1.TEXT AS RequestingText,h2.TEXT AS BlockingTest,tl.request_modeFROM sys.dm_tran_locks AS tlINNER JOIN sys.databases db ON db.database_id = tl.resource_database_idINNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_addressINNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_idINNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_idINNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_idCROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
GO

Query to Keep CPU Busy for 60 Seconds

DECLARE @T DATETIME, @F BIGINT;SET @T = GETDATE();WHILE DATEADD(SECOND,60,@T)>GETDATE()SET @F=POWER(2,30);

Finding Memory Pressure – External and Internal

The following query will provide details of external and internal memory pressure. It will return the data how much portion is assigned to what kind of memory type.


SELECT TYPE, SUM(single_pages_kb) InternalPressure, SUM(multi_pages_kb) ExtermalPressureFROM sys.dm_os_memory_clerksGROUP BY TYPE
ORDER BY SUM(single_pages_kb) DESC, SUM(multi_pages_kb) DESCGO

Find Most Expensive Queries Using DMV

SELECTTOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs
.execution_count,
qs
.total_logical_reads, qs.last_logical_reads,
qs
.total_logical_writes, qs.last_logical_writes,
qs
.total_worker_time,
qs
.last_worker_time,
qs
.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs
.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs
.last_execution_time,
qp
.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

Find Number of Tables in a Perticular Database

USE Master
SELECT COUNT(*) from information_schema.tables
 
WHERE table_type = 'base table'
 

Find and Fix Fragmentation of a Table

Find Fragmentation
=====================
select TableName=object_name(dm.object_id)
      ,IndexName=i.name
      ,IndexType=dm.index_type_desc
      ,[%Fragmented]=avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(db_id(),null,null,null,'sampled') dm
join sys.indexes i on dm.object_id=i.object_id and dm.index_id=i.index_id
order by avg_fragmentation_in_percent desc

Remove Fragmentation
=====================
dbcc indexdefrag('master','spt_values','ix2_spt_values_nu_nc')

Quorum configuration choices

Node Majority (recommended for clusters with an odd number of nodes)

Can sustain failures of half the nodes (rounding up) minus one. For example, a seven node cluster can sustain three node failures.

Node and Disk Majority (recommended for clusters with an even number of nodes)

Can sustain failures of half the nodes (rounding up) if the disk witness remains online. For example, a six node cluster in which the disk witness is online could sustain three node failures.

Can sustain failures of half the nodes (rounding up) minus one if the disk witness goes offline or fails. For example, a six node cluster with a failed disk witness could sustain two (3-1=2) node failures.

Node and File Share Majority (for clusters with special configurations)

Works in a similar way to Node and Disk Majority, but instead of a disk witness, this cluster uses a file share witness.

Note that if you use Node and File Share Majority, at least one of the available cluster nodes must contain a current copy of the cluster configuration before you can start the cluster. Otherwise, you must force the starting of the cluster through a particular node.

No Majority: Disk Only (not recommended)

Can sustain failures of all nodes except one (if the disk is online). However, this configuration is not recommended because the disk might be a single point of failure.

 

Quorum Mode	Components that has vote	Votes for Quorum (v denotes vote)
Node Majority	Nodes (1 per node)	v/2 + 1
Node and Disk Majority	Nodes (1 per node) and Disk Witness Resource (1)	v/2 + 1
Node and File Share Majority	Nodes (1 per node) and File Share Witness Resource (1)	v/2 + 1
No Majority: Disk Only (Legacy)	Disk Witness Resource (1)	v