The queries using the most CPU, reads and time since their plans were cached. Change the ORDER BY to sort by reads or duration. For history across restarts use Query Store (Performance 02). Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 01_Top_Queries.sql – the queries using the most CPU, reads and time…
Run it in the database: queries that got slower in the last 24 hours than in the 7 days before, usually because the plan changed. Needs Query Store turned on. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 02_Query_Store_Regressions.sql – run IN the database: queries that got slower in the last 24…
Indexes SQL Server thinks would help, across all databases, since start-up, with a CREATE INDEX statement for each. Treat them as hints: check for overlap with existing indexes before creating any. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 03_Missing_Indexes.sql – indexes SQL Server thinks would help, across all databases, since start-up….
Run it in the database: indexes that are written to but never read since start-up. Only meaningful once the server has been up through a full business cycle (month end and so on). Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 04_Unused_Indexes.sql – run IN the database: indexes that are written to…
Run it in the database: statistics that are more than 7 days old or have had more than 20% of rows changed since they were last updated. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 05_Statistics_Age.sql – run IN the database: statistics that are old or have had many changes since they…
Run it in the database: fragmentation of every index over 1000 pages, using the light LIMITED scan. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. — 06_Index_Fragmentation.sql – run IN the database: fragmentation of indexes over 1000 pages (LIMITED scan, fairly light). Read-only. SET NOCOUNT ON; SELECT OBJECT_SCHEMA_NAME(ps.object_id) + N’.’ + OBJECT_NAME(ps.object_id) AS…
Finds where the optimiser’s row estimates are badly wrong, the root of most bad plans. Part 1 works on every version; part 2 (SQL Server 2019 and later) goes operator by operator through the last actual plan and shows the table, index and columns where a filtered statistic would help. Read-only, changes nothing. Tested on…
Run it in the database: columns whose data is lopsided enough that the normal statistics histogram misleads the optimiser, with the CREATE STATISTICS … WHERE statement that would fix it. It only suggests; it creates nothing. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. /* 08_Filtered_Statistics_Candidates.sql – run IN the database: columns whose…
Run it in the database: indexes that duplicate or are covered by another index, and what they cost in writes. It suggests DROP statements but runs none. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. /* 09_Duplicate_and_Overlapping_Indexes.sql – run IN the database: indexes that duplicate or are covered by another index, and what…
Run it in the database: the auto-statistics settings, how fresh and how well sampled every statistic is, and filtered statistics that have fallen behind. A fuller version of Performance 05. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. /* 10_Statistics_Health.sql – run IN the database: how fresh and how good every statistic is,…
Which cardinality estimator each database actually uses and why: compatibility level, LEGACY_CARDINALITY_ESTIMATION, trace flags and query hints, with advice on what to change. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. /* 11_CE_Configuration_Audit.sql – which cardinality estimator (CE) each database uses, and why. Clive Roberts DBA, 10 October 2026. Read-only. Server-wide. SQL Server…
Queries in the plan cache where SQL Server has to convert a column’s data type (usually varchar against nvarchar), which can turn a seek into a scan and ruin row estimates. Read-only, changes nothing. Tested on SQL Server 2017 and 2025. /* 12_Implicit_Conversions.sql – queries in the plan cache where SQL Server has to convert…
SELECT st.dbid AS QueryExecutionContextDBID, DB_NAME(st.dbid) AS QueryExecContextDBNAME, st.objectid AS ModuleObjectId, SUBSTRING(st.TEXT, dmv_er.statement_start_offset/2 + 1, (CASE WHEN dmv_er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2 ELSE dmv_er.statement_end_offset END – dmv_er.statement_start_offset)/2) AS Query_Text, dmv_tsu.session_id , dmv_tsu.request_id, dmv_tsu.exec_context_id, (dmv_tsu.user_objects_alloc_page_count – dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts, (dmv_tsu.internal_objects_alloc_page_count – dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts, dmv_er.start_time, dmv_er.command, dmv_er.open_transaction_count, dmv_er.percent_complete, dmv_er.estimated_completion_time, dmv_er.cpu_time, dmv_er.total_elapsed_time, dmv_er.reads,dmv_er.writes, dmv_er.logical_reads, dmv_er.granted_query_memory, dmv_es.HOST_NAME,…
VLF (virtual log file) count per database using sys.dm_db_log_info, which replaces DBCC LOGINFO from SQL Server 2016 SP2. No temp tables or sp_MSforeachdb needed. — High VLF counts can affect write performance — and they can make full database restores and crash recovery take much longer. — Try to keep VLF counts under 200 in…
VLF detail per database: count, active VLFs, log size, average VLF size and what is stopping log reuse. Uses sys.dm_db_log_info (SQL Server 2016 SP2 and later). The fix for a high count is at the bottom. — VLF detail per database (SQL Server 2016 SP2 and later) — Shows how many VLFs there are, how…
Static Tables – Set Fill Factor at 100 (or default server fill factor), As these tables are never changing, keeping the Fill Factor at 100 is the best option. They conserve the space, and also there is no fragmentation. Tables Updated Less Often – Set Fill Factor at 95. These tables are more or less…
— 25 missing indexes that will improve performance the most SELECT TOP 25 dm_mid.database_id AS DatabaseID, DB_NAME(dm_mid.database_id) AS [Database], dm_migs.avg_user_impact * ( dm_migs.user_seeks + dm_migs.user_scans ) AS [Avg Estimated Impact], dm_migs.last_user_seek AS [Last Usage Time], OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) AS [Table], ‘CREATE INDEX [IDX_’ + OBJECT_NAME(dm_mid.OBJECT_ID, dm_mid.database_id) + ‘_’ + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns, ”), ‘, ‘, ‘_’), ‘[‘, ”),…
SELECT( cpu_count / hyperthread_ratio ) AS NumberOfPhysicalCPUs, CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count ELSE(( cpu_count – hyperthread_ratio ) / ( cpu_count / hyperthread_ratio )) END AS NumberOfCoresInEachCPU, CASE WHEN hyperthread_ratio = cpu_count THEN cpu_count ELSE( cpu_count / hyperthread_ratio ) * (( cpu_count – hyperthread_ratio ) / ( cpu_count / hyperthread_ratio )) END AS TotalNumberOfCores,…
— Unused Index Script SELECT TOP 25 o.name AS ObjectName , i.name AS IndexName , i.index_id AS IndexID , dm_ius.user_seeks AS UserSeek , dm_ius.user_scans AS UserScans , dm_ius.user_lookups AS UserLookups , dm_ius.user_updates AS UserUpdates , p.TableRows , ‘DROP INDEX ‘ + QUOTENAME(i.name) + ‘ ON ‘ + QUOTENAME(s.name) + ‘.’ + QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS ‘drop statement’…
SELECT DISTINCT wt.wait_type FROM sys.dm_os_waiting_tasks AS wt JOIN sys.dm_exec_sessions AS s ON wt.session_id = s.session_id WHERE s.is_user_process = 0
DECLARE @CounterPrefix NVARCHAR(30) SET @CounterPrefix = CASE WHEN @@SERVICENAME = ‘MSSQLSERVER’ THEN ‘SQLServer:’ ELSE ‘MSSQL$’ + @@SERVICENAME + ‘:’ END ; — Capture the first counter set SELECT CAST(1 AS INT) AS collection_instance , [OBJECT_NAME] , counter_name , instance_name , cntr_value , cntr_type , CURRENT_TIMESTAMP AS collection_time INTO #perf_counters_init FROM sys.dm_os_performance_counters WHERE ( OBJECT_NAME =…
SELECT TOP 10 execution_count , statement_start_offset AS stmt_start_offset , sql_handle , plan_handle , total_logical_reads / execution_count AS avg_logical_reads , total_logical_writes / execution_count AS avg_logical_writes , total_physical_reads / execution_count AS avg_physical_reads , t.text FROM sys.dm_exec_query_stats AS s CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t ORDER BY avg_physical_reads DESC
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…
Index type Description Clustered A clustered index sorts and stores the data rows of the table or view in order based on the clustered index key. The clustered index is implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values. Non clustered A non clustered…
To analyze further, the following query can be used to find out which indexes don’t have clustered indexes defined: WITH CTE_1 AS ( SELECT db_name() as dbname, o.name as tablename, (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = i.index_id AND i.object_id = p.object_id) as number_of_rows FROM sys.indexes i INNER JOIN sys.objects o ON i.object_id =…
As we use PERFMON – Performance Monitor to identify real time happening on SQL Server, Here using a DMV “sys.dm_os_performance_counters” may be used to calculate number of transactions on SQL Server as DECLARE @Counter bigint SELECT @Counter = cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = ‘Transactions/sec’ AND object_name = ‘SQLServer:Databases’ AND instance_name = ‘Test’ — Your…
SELECT DB_NAME() AS [Database], T.TABLE_NAME AS [Tables without Primary Keys] FROM INFORMATION_SCHEMA.TABLES AS T WHERE NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC WHERE CONSTRAINT_TYPE = ‘PRIMARY KEY’ AND T.TABLE_NAME = TC.TABLE_NAME) AND T.TABLE_TYPE = ‘BASE TABLE’
Precious Lite 2025 | All Rights Reserved. Precious Lite theme by Flythemes