PROBLEM:
You need to investigate problems with your SQL Server database and to gather some useful information about performance
SOLUTION:
First, do not lock anything, and do not get held up by any locks
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Show the version of SQL Server
SELECT @@VERSION
Get property info about server instance. IsIntegratedSecurityOnly: 1 = windows only. ProductLevel - contains RTM or SP level. Is SP the correct one?!
SELECT
SERVERPROPERTY('ServerName') AS [ServerName]
,SERVERPROPERTY('InstanceName') AS [InstanceName]
,SERVERPROPERTY('MachineName') AS [MachineName]
,SERVERPROPERTY('Edition') AS [Edition]
,SERVERPROPERTY('ProductVersion') AS [ProductVersion]
,SERVERPROPERTY('ProductLevel') AS [ProductLevel]
,SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly]
,SERVERPROPERTY('IsClustered') AS [IsClustered]
Get OS information. CPU/Memory/last reboot time useful
SELECT cpu_count AS [Logical CPUs]
, cpu_count / hyperthread_ratio AS [Physical CPUs]
, CAST(physical_memory_in_bytes / 1024.0 / 1024.0 /1024.0 AS DECIMAL(28,2)) AS [Memory (GB)]
, DATEADD(ss, -(ms_ticks / 1000), GetDate()) AS [Start DateTime]
-- , sqlserver_start_time AS [Start DateTime] -- In 2008+
FROM sys.dm_os_sys_info
Show SQL Server database info. Check compatibility level. is_read_committed_snapshot_on (1 is good for concurrency). recovery_model (want simple on non-prod boxes. Bulk_logged for prod). page_verify - want CHECKSUM
SELECT name, compatibility_level, recovery_model_desc, page_verify_option_desc, is_read_committed_snapshot_on
FROM sys.databases ORDER BY name
Show SQL Server configuration info. Priority boost - should be off (0). Cost threshold for parallelism (evaluate with MAXDOP). Max degree of parallelism (1 or 8?). Max server memory (MB) - evaluate in context of server memory. CLR enabled - generally disable, unless needed. Optimize for ad hoc workloads - often recommended to have on
SELECT name, description, value_in_use
FROM sys.configurations
WHERE NAME IN(
'clr enabled'
, 'max degree of parallelism'
, 'cost threshold for parallelism'
, 'max server memory (MB)' -- Set appropriately
, 'optimize for ad hoc workloads' -- should be 1.
, 'priority boost' -- should be 0
)
ORDER BY name
Identify what is causing waits
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK'
,'SLEEP_SYSTEMTASK','SQLTRACE_BUFFER_FLUSH','WAITFOR', 'LOGMGR_QUEUE','CHECKPOINT_QUEUE'
,'REQUEST_FOR_DEADLOCK_SEARCH','XE_TIMER_EVENT','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_MANUAL_EVENT'
,'CLR_AUTO_EVENT','DISPATCHER_QUEUE_SEMAPHORE', 'FT_IFTS_SCHEDULER_IDLE_WAIT'
,'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP','ONDEMAND_TASK_QUEUE'))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 99; -- percentage threshold
How much time is spent swapping threads - above 20% is bad
SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits],
CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%resource waits]
FROM sys.dm_os_wait_stats WITH (NOLOCK) OPTION (RECOMPILE);
CPU utilization usage per object database. Is a single DB hogging CPU? Maybe needs a separate server
--USE [YourDatabaseName]
WITH DB_CPU_Stats
AS
(SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName],
SUM(total_worker_time) AS [CPU_Time_Ms]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute = N'dbid') AS F_DB
GROUP BY DatabaseID)
SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [row_num],
DatabaseName, [CPU_Time_Ms],
CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms])
OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
FROM DB_CPU_Stats
WHERE DatabaseID > 4 -- system databases
AND DatabaseID <> 32767 -- ResourceDB
ORDER BY [CPUPercent] DESC OPTION (RECOMPILE);
Memory usage per database. Is a single DB hogging memory? Maybe needs a separate server
--USE [YourDatabaseName]
SELECT
ISNULL(DB_NAME(database_id), 'ResourceDb') AS DatabaseName
, CAST(COUNT(row_count) * 8.0 / (1024.0) AS DECIMAL(28,2)) AS [Size (MB)]
, CAST(COUNT(row_count) * 8.0 / (1024.0) / 1024.0 AS DECIMAL(28,2)) AS [Size (GB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY [Size (MB)] DESC
Find what are the physical disk metric values. Under 20ms is ok, above this need investigating
--USE [YourDatabaseName]
SELECT DB_NAME(database_id) AS DatabaseName
, file_id
, io_stall_read_ms / num_of_reads AS 'Average read time'
, io_stall_write_ms / num_of_writes AS 'Average write time'
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
WHERE num_of_reads > 0
AND num_of_writes > 0
ORDER BY DatabaseName
Check reads per write, by database. Separate out a DB or for OLAP/OLTP? OLAP often needs more indexes.. NULL DatabaseName means this query was run adhoc or prepared
--USE [YourDatabaseName]
SELECT TOP 10
DB_NAME(qt.dbid) AS DatabaseName
, SUM(total_logical_reads) AS [Total Reads]
, SUM(total_logical_writes) AS [Total Writes]
, SUM(total_logical_reads) / CASE WHEN SUM(total_logical_writes) = 0 THEN 1 ELSE SUM(total_logical_writes) END AS [Reads Per Write]
, SUM(qs.execution_count) AS [Execution count]
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Reads Per Write] DESC;
Check space used. What is DB size, and how much is unused?
--USE [YourDatabaseName]
EXEC sp_SpaceUsed
-- Database statistics settings
-- is_auto_create_stats_on should be on (1)
-- is_auto_update_stats_on should be on (1)
-- is_auto_update_stats_async_on should be off (0)
SELECT name
, is_auto_create_stats_on AS [AutoCreateStatistics]
, is_auto_update_stats_on AS [AutoUpdateStatistics]
, is_auto_update_stats_async_on AS [AutoUpdateStatisticsAsync]
FROM master.sys.databases
ORDER BY name
Check the state of your statistics. When last updated?! Maybe needs intelligent stats utility?
--USE [YourDatabaseName]
SELECT
ss.name AS SchemaName
, st.name AS TableName
, s.name AS IndexName
, STATS_DATE(s.id,s.indid) AS 'Statistics Last Updated'
, s.rowcnt AS 'Row Count'
, s.rowmodctr AS 'Number Of Changes'
, CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0)
AS DECIMAL(28,2)) AS '% Rows Changed'
FROM sys.sysindexes s
INNER JOIN sys.tables st ON st.[object_id] = s.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE s.id > 100 -- user table/index
AND s.indid > 0 -- Clustered and non-clustered indexes
AND s.rowcnt >= 500 -- want at least 500 rows
ORDER BY 'Statistics Last Updated' DESC
Missing indexes can be indicative of other bad practices in a given DB
--USE [YourDatabaseName]
SELECT
DB_NAME(database_id) AS DatabaseName
, COUNT(*) AS [Missing Index Count]
FROM sys.dm_db_missing_index_details
GROUP BY DB_NAME(database_id)
ORDER BY [Missing Index Count] DESC
What are the most costly missing indexes?
--USE [YourDatabaseName]
SELECT TOP 40 -- Too many INCLUDE cols in top entries...
ROUND(avg_total_user_cost * avg_user_impact
* (user_seeks + user_scans),0) AS [Total Cost]
, [statement] AS [Table Name]
, equality_columns
, inequality_columns
, included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC
Identify missing PKs. Most tables should have PK (for RI, FK), unless good reason
--USE [YourDatabaseName]
SELECT DB_NAME(DB_ID()) AS DatabaseName, SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY SchemaName, TableName;
Identify heaps that have non-clustered indexes. Often Heaps with Non-clustered Indexes better as Cluster based on NC
--USE [YourDatabaseName]
SELECT
DB_NAME(DB_ID()) AS DatabaseName
, ss.name AS SchemaName
, st.NAME AS TableName
, i.NAME AS IndexName
, i.type_desc
, si.rowcnt
INTO #HeapWithIndexes2
FROM sys.indexes I
LEFT JOIN sys.sysindexes SI ON SI.indid = I.index_Id AND i.object_id = si.id
INNER JOIN sys.tables st ON st.[object_id] = si.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE 1=2
Check tables names that have a heap...
--USE [YourDatabaseName]
SELECT DISTINCT ss.name AS schemaName, st.name
INTO #Heaps
FROM sys.indexes I
LEFT JOIN sys.sysindexes SI ON SI.indid = I.index_Id
AND i.object_id = si.id
INNER JOIN sys.tables st ON st.[object_id] = si.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE si.id > 100
AND i.type_desc = 'HEAP'
INTERSECT
SELECT DISTINCT ss.name AS schemaName, st.name
FROM sys.indexes I
LEFT JOIN sys.sysindexes SI ON SI.indid = I.index_Id
AND i.object_id = si.id
INNER JOIN sys.tables st ON st.[object_id] = si.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE si.id > 100
AND i.type_desc != 'HEAP'
ALL tables and their indexes...
--USE [YourDatabaseName]
INSERT INTO #HeapWithIndexes2
SELECT
DB_NAME(DB_ID()) AS DatabaseName
, ss.name AS SchemaName
, st.NAME AS TableName
, i.NAME AS IndexName
, i.type_desc
, si.rowcnt
FROM sys.indexes I
LEFT JOIN sys.sysindexes SI ON SI.indid = I.index_Id AND i.object_id = si.id
INNER JOIN sys.tables st ON st.[object_id] = si.[id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
INNER JOIN #Heaps h ON st.name = h.name AND ss.name = h.schemaName
WHERE si.id > 100
SELECT * FROM #HeapWithIndexes2 ORDER BY DatabaseName, SchemaName, TableName, IndexName
DROP TABLE #Heaps
DROP TABLE #HeapWithIndexes2
Allows targeted improvements, based on query duration. Query plan may offer hints for improvements
SELECT TOP 20
CAST(total_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) AS [Total Duration (s)]
, CAST(total_worker_time * 100.0 / total_elapsed_time AS DECIMAL(28, 2)) AS [% CPU]
, CAST((total_elapsed_time - total_worker_time)* 100.0 / total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
, execution_count
, CAST(total_elapsed_time / 1000000.0 / execution_count AS DECIMAL(28, 2)) AS [Average Duration (s)]
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS [Individual Query]
, SUBSTRING(qt.text,1,100) AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
INNER JOIN sys.dm_exec_cached_plans as cp
ON qs.plan_handle=cp.plan_handle
WHERE total_elapsed_time > 0
ORDER BY total_elapsed_time DESC
If you find this useful, you are welcome to press one of the ads in this page.. Thanks!
