The Query Store provides database administrators with in-depth insights on query plans and performance metrics. By default, execution...

Understanding the SQL Server Proportional fill algorithm When creating a database, SQL Server maps this database with minimum two...

-- Breaks down buffers used by current database by object (table, index) in the buffer cache SELECT OBJECT_NAME(p.[object_id]) AS...

-- Possible Bad NC Indexes (writes > reads) SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id,...

SELECT TOP(50) OBJECT_NAME(qt.objectid) AS [SP Name], (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count AS [Avg IO],...

-- Logical writes relate to both memory and disk I/O pressure SELECT TOP(25) p.name AS [SP Name], qs.total_logical_writes AS...

SELECT TOP(25) p.name AS [SP Name],qs.total_physical_reads AS [TotalPhysicalReads], qs.total_physical_reads/qs.execution_count AS...

-- Top Cached SPs By Total Logical Reads. Logical reads relate to memory pressure SELECT TOP(25) p.name AS [SP Name],...

SELECT TOP(25) p.name AS [SP Name], qs.total_worker_time AS [TotalWorkerTime], qs.total_worker_time/qs.execution_count AS...

SELECT TOP(25) p.name AS [SP Name], qs.total_elapsed_time/qs.execution_count AS [avg_elapsed_time], qs.total_elapsed_time,...

-- Top Cached SPs By Execution Count (SQL 2008) SELECT TOP(250) p.name AS [SP Name], qs.execution_count, --ISNULL(qs.execution_count/DATE...

-- Get Average Task Counts (run multiple times) SELECT AVG(current_tasks_count) AS [Avg Task Count], AVG(runnable_tasks_count) AS [Avg...

-- Signal Waits for instance SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu)...

-- Common Significant Wait types with BOL explanations -- *** Network Related Waits *** -- ASYNC_NETWORK_IO Occurs on network writes...

-- Get total buffer usage by database for current instance SELECT DB_NAME(database_id) AS [Database Name], COUNT(*) * 8/1024.0 AS [Cached...

Maxdop - 1, means no parallelism Maxdop - 0 (Default), Means SQL Server can aggressively use all the available processor to execute the...

--Script to find autogrowth event from the default trace. DECLARE @filename NVARCHAR(1000); DECLARE @bc INT; DECLARE @ec INT; DECLARE...

--Script to find number of VLF. SET NOCOUNT ON; /* declare variables required */ DECLARE @DatabaseId INT; DECLARE @TSQL varchar(MAX);...

--Collect all index stats if object_id('index_estimates') is not null drop table index_estimates go create table index_estimates (...

/*Perform a 'USE <database name>' to select the database in which to run the script.*/ -- Declare variables SET NOCOUNT ON; DECLARE...