INDEX DMVs

1. Find fill factor of the indexes on the tables in  the current databas

SELECT DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, i.fill_factor
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE s.database_id = DB_ID()
AND i.name IS NOT NULL
AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0
ORDER BY fill_factor DESC

2.  Locate fragmentation percentage of the indexes on a table within a database named warehousedw

SELECT i.name AS IndexName
, ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %]
FROM sys.dm_db_index_physical_stats(DB_ID(‘warehousedw’),
OBJECT_ID('table'), NULL, NULL, NULL) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
ANDs.index_id = i.index_id

3. Identifying the most important missing indexes

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
ROUND(s.avg_total_user_cost *
s.avg_user_impact
* (s.user_seeks + s.user_scans),0)
AS [Total Cost]
, d.[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

4. The most-costly unused indexes

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, s.user_updates
, s.system_seeks + s.system_scans + s.system_lookups
AS [System usage]
INTO #TempUnusedIndexes
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE 1=2
EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempUnusedIndexes SELECT TOP 20 DB_NAME() AS DatabaseName , SCHEMA_NAME(o.Schema_ID) AS SchemaName , OBJECT_NAME(s.[object_id]) AS TableName , i.name AS IndexName , s.user_updates , s.system_seeks + s.system_scans + s.system_lookups AS [System usage] FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id INNER JOIN sys.objects o ON i.object_id = O.object_id WHERE s.database_id = DB_ID() AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 AND s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 AND i.name IS NOT NULL ORDER BY s.user_updates DESC'
SELECT TOP 20 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC
DROP TABLE #TempUnusedIndexes

5. Finding the top high-maintenance indexes

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, (s.user_updates ) AS [update usage]
, (s.user_seeks + s.user_scans + s.user_lookups)
AS [Retrieval usage]
, (s.user_updates) -
(s.user_seeks + s.user_scans + s.user_lookups) AS [Maintenance cost]
, s.system_seeks + s.system_scans + s.system_lookups AS [System usage]
, s.last_user_seek
, s.last_user_scan
, s.last_user_lookup
INTO #TempMaintenanceCost
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE 1=2
EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempMaintenanceCost SELECT TOP 20 DB_NAME() AS DatabaseName , SCHEMA_NAME(o.Schema_ID) AS SchemaName , OBJECT_NAME(s.[object_id]) AS TableName , i.name AS IndexName , (s.user_updates ) AS [update usage] , (s.user_seeks + s.user_scans + s.user_lookups) AS [Retrieval usage] , (s.user_updates) - (s.user_seeks + user_scans + s.user_lookups) AS [Maintenance cost] , s.system_seeks + s.system_scans + s.system_lookups AS [System usage] , s.last_user_seek , s.last_user_scan , s.last_user_lookup FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id INNER JOIN sys.objects o ON i.object_id = O.object_id WHERE s.database_id = DB_ID() AND i.name IS NOT NULL AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 AND (s.user_seeks + s.user_scans + s.user_lookups) > 0 ORDER BY [Maintenance cost] DESC'
SELECT top 20 * FROM #TempMaintenanceCost ORDER BY [Maintenance cost] DESC
DROP TABLE #TempMaintenanceCost

6. Finding the most-used indexes

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
DB_NAME() AS DatabaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, (s.user_seeks + s.user_scans + s.user_lookups) AS [Usage]
, s.user_updates
, i.fill_factor
INTO #TempUsage
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE 1=2
EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempUsage SELECT TOP 20 DB_NAME() AS DatabaseName , SCHEMA_NAME(o.Schema_ID) AS SchemaName , OBJECT_NAME(s.[object_id]) AS TableName , i.name AS IndexName , (s.user_seeks + s.user_scans + s.user_lookups) AS [Usage] , s.user_updates , i.fill_factor FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id INNER JOIN sys.objects o ON i.object_id = O.object_id WHERE s.database_id = DB_ID() AND i.name IS NOT NULL AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 ORDER BY [Usage] DESC'
SELECT TOP 20 * FROM #TempUsage ORDER BY [Usage] DESC
DROP TABLE #TempUsage

7. Finding the most-fragmented indexes

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
DB_NAME() AS DatbaseName
, SCHEMA_NAME(o.Schema_ID) AS SchemaName
, OBJECT_NAME(s.[object_id]) AS TableName
, i.name AS IndexName
, ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %]
INTO #TempFragmentation
FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON i.object_id = O.object_id
WHERE 1=2
EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempFragmentation SELECT TOP 20 DB_NAME() AS DatbaseName , SCHEMA_NAME(o.Schema_ID) AS SchemaName , OBJECT_NAME(s.[object_id]) AS TableName , i.name AS IndexName , ROUND(s.avg_fragmentation_in_percent,2) AS [Fragmentation %] FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id INNER JOIN sys.objects o ON i.object_id = O.object_id WHERE s.database_id = DB_ID() AND i.name IS NOT NULL AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 ORDER BY [Fragmentation %] DESC'
SELECT top 20 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC
DROP TABLE #TempFragmentation

8. The databases with the most missing indexes

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
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

9. Indexes that aren’t used at all

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
DB_NAME() AS DatbaseName
, SCHEMA_NAME(O.Schema_ID) AS SchemaName
, OBJECT_NAME(I.object_id) AS TableName
, I.name AS IndexName
INTO #TempNeverUsedIndexes
FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id
WHERE 1=2
EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempNeverUsedIndexes SELECT DB_NAME() AS DatbaseName , SCHEMA_NAME(O.Schema_ID) AS SchemaName , OBJECT_NAME(I.object_id) AS TableName , I.NAME AS IndexName FROM sys.indexes I INNER JOIN sys.objects O ON I.object_id = O.object_id LEFT OUTER JOIN sys.dm_db_index_usage_stats S ON S.object_id = I.object_id AND I.index_id = S.index_id AND DATABASE_ID = DB_ID() WHERE OBJECTPROPERTY(O.object_id,''IsMsShipped'') = 0 AND I.name IS NOT NULL AND S.object_id IS NULL'
SELECT * FROM #TempNeverUsedIndexes
ORDER BY DatbaseName, SchemaName, TableName, IndexName
DROP TABLE #TempNeverUsedIndexes

10. Finding the state of your statistics

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
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
AND s.indid > 0
AND s.rowcnt >= 500
ORDER BY SchemaName, TableName, IndexName

QUERY DMVs

11. How to find a cached plan

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
st.text AS [SQL]
, cp.cacheobjtype
, cp.objtype
, COALESCE(DB_NAME(st.dbid),
DB_NAME(CAST(pa.value AS INT))+'*',
'Resource') AS [DatabaseName]
, cp.usecounts AS [Plan usage]
, qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
OUTER APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa
WHERE pa.attribute = 'dbid'
AND st.text LIKE '%PartyType%'

12. Finding where a query is used

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
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]
, qt.text 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
WHERE 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)
LIKE '%insert into dbo.deal%'

13. The queries that take the longest time to run

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))
AS [Total Duration (s)]
, CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time
AS DECIMAL(28, 2)) AS [% CPU]
, CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /
qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
, qs.execution_count
, CAST(qs.total_elapsed_time / 1000000.0 / qs.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]
, qt.text 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
WHERE qs.total_elapsed_time > 0
ORDER BY qs.total_elapsed_time DESC

14. The queries spend the longest time being blocked

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
CAST((qs.total_elapsed_time - qs.total_worker_time) /
1000000.0 AS DECIMAL(28,2)) AS [Total time blocked (s)]
, CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time
AS DECIMAL(28,2)) AS [% CPU]
, CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /
qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
, qs.execution_count
, CAST((qs.total_elapsed_time - qs.total_worker_time) / 1000000.0
/ qs.execution_count AS DECIMAL(28, 2)) AS [Blocking average (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]
, qt.text 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
WHERE qs.total_elapsed_time > 0
ORDER BY [Total time blocked (s)] DESC

15. The queries that use the most CPU

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
CAST((qs.total_worker_time) / 1000000.0 AS DECIMAL(28,2))
AS [Total CPU time (s)]
, CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time
AS DECIMAL(28,2)) AS [% CPU]
, CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /
qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting]
, qs.execution_count
, CAST((qs.total_worker_time) / 1000000.0
/ qs.execution_count AS DECIMAL(28, 2)) AS [CPU time average (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]
, qt.text 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
WHERE qs.total_elapsed_time > 0
ORDER BY [Total CPU time (s)] DESC

16. The queries that use the most I/O

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
[Total IO] = (qs.total_logical_reads + qs.total_logical_writes)
, [Average IO] = (qs.total_logical_reads + qs.total_logical_writes) /
qs.execution_count
, qs.execution_count
, 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]
, qt.text 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
ORDER BY [Total IO] DESC

17. The queries that have been executed the most often

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
qs.execution_count
, 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]
, qt.text 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
ORDER BY qs.execution_count DESC;

18. Finding when a query was last run

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT DISTINCT TOP 20
qs.last_execution_time
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
WHERE qt.text LIKE '%CREATE PROCEDURE%List%PickList%'
ORDER BY qs.last_execution_time DESC

19. Finding when a table was last inserted

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
qs.last_execution_time
, 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]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
WHERE 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)
LIKE '%INSERT INTO dbo.Underlying%'
ORDER BY qs.last_execution_time DESC
The listing is very similar to the other scripts

20. Finding queries with missing statistics

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
st.text AS [Parent Query]
, DB_NAME(st.dbid)AS [DatabaseName]
, cp.usecounts AS [Usage Count]
, qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE CAST(qp.query_plan AS NVARCHAR(MAX))
LIKE '%<ColumnsWithNoStatistics>%'
ORDER BY cp.usecounts DESC

21. Finding your default statistics options

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT name AS DatabaseName
, is_auto_create_stats_on AS AutoCreateStatistics
, is_auto_update_stats_on AS AutoUpdateStatistics
, is_auto_update_stats_async_on
AS AutoUpdateStatisticsAsync
FROM sys.databases
ORDER BY DatabaseName

22. Finding disparate columns with different data types

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
COLUMN_NAME
,[%] = CONVERT(DECIMAL(12,2),COUNT(COLUMN_NAME)*
100.0 / COUNT(*)OVER())
INTO #Prevalence
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY COLUMN_NAME
SELECT DISTINCT
C1.COLUMN_NAME
, C1.TABLE_SCHEMA
, C1.TABLE_NAME
, C1.DATA_TYPE
, C1.CHARACTER_MAXIMUM_LENGTH
, C1.NUMERIC_PRECISION
, C1.NUMERIC_SCALE
, [%]
FROM INFORMATION_SCHEMA.COLUMNS C1
INNER JOIN INFORMATION_SCHEMA.COLUMNS C2 ON C1.COLUMN_NAME =
C2.COLUMN_NAME
INNER JOIN #Prevalence p ON p.COLUMN_NAME = C1.COLUMN_NAME
WHERE ((C1.DATA_TYPE != C2.DATA_TYPE)
OR (C1.CHARACTER_MAXIMUM_LENGTH != C2.CHARACTER_MAXIMUM_LENGTH)
OR (C1.NUMERIC_PRECISION != C2.NUMERIC_PRECISION)
OR (C1.NUMERIC_SCALE != C2.NUMERIC_SCALE))
ORDER BY [%] DESC, C1.COLUMN_NAME, C1.TABLE_SCHEMA, C1.TABLE_NAME
DROP TABLE #Prevalence

23. Finding queries that are running slower than normal

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 100
qs.execution_count AS [Runs]
, (qs.total_worker_time - qs.last_worker_time) / (qs.execution_count - 1)
AS [Avg time]
, qs.last_worker_time AS [Last time]
, (qs.last_worker_time - ((qs.total_worker_time - qs.last_worker_time) /
(qs.execution_count - 1))) AS [Time Deviation]
, CASE WHEN qs.last_worker_time = 0
THEN 100
ELSE (qs.last_worker_time - ((qs.total_worker_time -
qs.last_worker_time) / (qs.execution_count - 1))) * 100
END
/ (((qs.total_worker_time - qs.last_worker_time) /
(qs.execution_count - 1.0))) AS [% Time Deviation]
,qs.last_logical_reads + qs.last_logical_writes + qs.last_physical_reads
AS [Last IO]
, ((qs.total_logical_reads + qs.total_logical_writes +
qs.total_physical_reads) -
(qs.last_logical_reads + last_logical_writes
+ qs.last_physical_reads))
/ (qs.execution_count - 1) AS [Avg IO]
, 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]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS [DatabaseName]
INTO #SlowQueries
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) qt
WHERE qs.execution_count > 1
AND qs.total_worker_time != qs.last_worker_time
ORDER BY [% Time Deviation] DESC
SELECT TOP 100 [Runs]
, [Avg time]
, [Last time]
, [Time Deviation]
, [% Time Deviation]
, [Last IO]
, [Avg IO]
, [Last IO] - [Avg IO] AS [IO Deviation]
, CASE WHEN [Avg IO] = 0
THEN 0
ELSE ([Last IO]- [Avg IO]) * 100 / [Avg IO]
END AS [% IO Deviation]
, [Individual Query]
, [Parent Query]
, [DatabaseName]
INTO #SlowQueriesByIO
FROM #SlowQueries
ORDER BY [% Time Deviation] DESC
SELECT TOP 100
[Runs]
, [Avg time]
, [Last time]
, [Time Deviation]
, [% Time Deviation]
, [Last IO]
, [Avg IO]
, [IO Deviation]
, [% IO Deviation]
, [Impedance] = [% Time Deviation] - [% IO Deviation]
, [Individual Query]
, [Parent Query]
, [DatabaseName]
FROM #SlowQueriesByIO
WHERE [% Time Deviation] - [% IO Deviation] > 20
ORDER BY [Impedance] DESC
DROP TABLE #SlowQueries
DROP TABLE #SlowQueriesByIO

24. Finding unused stored procedures

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT s.name, s.type_desc
FROM sys.procedures s
LEFT OUTER JOIN sys.dm_exec_procedure_stats d
ON s.object_id = d.object_id
WHERE d.object_id IS NULL
ORDER BY s.name

25. Which queries run over a given time period

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT sql_handle, plan_handle, total_elapsed_time
, total_worker_time, total_logical_reads, total_logical_writes
, total_clr_time, execution_count, statement_start_offset
, statement_end_offset
INTO #PreWorkSnapShot
FROM sys.dm_exec_query_stats
WAITFOR DELAY '00:05:00'
SELECT sql_handle, plan_handle, total_elapsed_time
, total_worker_time, total_logical_reads, total_logical_writes
, total_clr_time, execution_count, statement_start_offset
, statement_end_offset
INTO #PostWorkSnapShot
FROM sys.dm_exec_query_stats
SELECT
p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0) AS [Duration]
, p2.total_worker_time - ISNULL(p1.total_worker_time, 0) AS [Time on CPU]
, (p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0)) -
(p2.total_worker_time - ISNULL(p1.total_worker_time, 0))
AS [Time blocked]
, p2.total_logical_reads - ISNULL(p1.total_logical_reads, 0) AS [Reads]
, p2.total_logical_writes - ISNULL(p1.total_logical_writes, 0)
AS [Writes]
, p2.total_clr_time - ISNULL(p1.total_clr_time, 0) AS [CLR time]
, p2.execution_count - ISNULL(p1.execution_count, 0) AS [Executions]
, SUBSTRING (qt.text,p2.statement_start_offset/2 + 1,
((CASE WHEN p2.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE p2.statement_end_offset
END - p2.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM #PreWorkSnapShot p1
RIGHT OUTER JOIN
#PostWorkSnapShot p2 ON p2.sql_handle =
ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =
ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset =
ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(p2.plan_handle) qp
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
AND qt.text NOT LIKE '--ThisRoutineIdentifier99%'
ORDER BY [Duration] DESC
DROP TABLE #PreWorkSnapShot
DROP TABLE #PostWorkSnapShot

26. Amalgamated DMV snapshots

--ThisRoutineIdentifier
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
index_group_handle, index_handle
, avg_total_user_cost, avg_user_impact, user_seeks, user_scans
INTO #PreWorkMissingIndexes
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
SELECT
sql_handle, plan_handle, total_elapsed_time, total_worker_time
, total_logical_reads, total_logical_writes, total_clr_time
, execution_count, statement_start_offset, statement_end_offset
INTO #PreWorkQuerySnapShot
FROM sys.dm_exec_query_stats
SELECT
[object_name], [counter_name], [instance_name]
, [cntr_value], [cntr_type]
INTO #PreWorkOSSnapShot
FROM sys.dm_os_performance_counters
SELECT
wait_type, waiting_tasks_count
, wait_time_ms, max_wait_time_ms, signal_wait_time_ms
INTO #PreWorkWaitStats
FROM sys.dm_os_wait_stats
WAITFOR DELAY '00:05:00'
SELECT wait_type, waiting_tasks_count, wait_time_ms
, max_wait_time_ms, signal_wait_time_ms
INTO #PostWorkWaitStats
FROM sys.dm_os_wait_stats
SELECT [object_name], [counter_name], [instance_name]
, [cntr_value], [cntr_type]
INTO #PostWorkOSSnapShot
FROM sys.dm_os_performance_counters
SELECT sql_handle, plan_handle, total_elapsed_time, total_worker_time
, total_logical_reads, total_logical_writes, total_clr_time
, execution_count, statement_start_offset, statement_end_offset
INTO #PostWorkQuerySnapShot
FROM sys.dm_exec_query_stats
SELECT index_group_handle, index_handle
, avg_total_user_cost, avg_user_impact, user_seeks, user_scans
INTO #PostWorkMissingIndexes
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
SELECT
p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0) AS [Duration]
, p2.total_worker_time - ISNULL(p1.total_worker_time, 0) AS [Time on CPU]
, (p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0)) -
(p2.total_worker_time - ISNULL(p1.total_worker_time, 0))
AS [Time blocked]
, p2.total_logical_reads - ISNULL(p1.total_logical_reads, 0) AS [Reads]
, p2.total_logical_writes - ISNULL(p1.total_logical_writes, 0)
AS [Writes]
, p2.total_clr_time - ISNULL(p1.total_clr_time, 0) AS [CLR time]
, p2.execution_count - ISNULL(p1.execution_count, 0) AS [Executions]
, SUBSTRING (qt.text,p2.statement_start_offset/2 + 1,
((CASE WHEN p2.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE p2.statement_end_offset
END - p2.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
FROM #PreWorkQuerySnapShot p1
RIGHT OUTER JOIN
#PostWorkQuerySnapShot p2 ON p2.sql_handle =
ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =
ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset =
ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
AND qt.text NOT LIKE '--ThisRoutineIdentifier%'
SELECT
p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) AS wait_time_ms
, p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0)
AS signal_wait_time_ms
, ((p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0))
- (p2.signal_wait_time_ms
- ISNULL(p1.signal_wait_time_ms, 0))) AS RealWait
, p2.wait_type
FROM #PreWorkWaitStats p1
RIGHT OUTER JOIN
#PostWorkWaitStats p2 ON p2.wait_type = ISNULL(p1.wait_type, p2.wait_type)
WHERE p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) > 0
AND p2.wait_type NOT LIKE '%SLEEP%'
AND p2.wait_type != 'WAITFOR'
ORDER BY RealWait DESC
SELECT
ROUND((p2.avg_total_user_cost - ISNULL(p1.avg_total_user_cost, 0))
* (p2.avg_user_impact - ISNULL(p1.avg_user_impact, 0)) *
((p2.user_seeks - ISNULL(p1.user_seeks, 0))
+ (p2.user_scans - ISNULL(p1.user_scans, 0))),0)
AS [Total Cost]
, p2.avg_total_user_cost - ISNULL(p1.avg_total_user_cost, 0)
AS avg_total_user_cost
, p2.avg_user_impact - ISNULL(p1.avg_user_impact, 0) AS avg_user_impact
, p2.user_seeks - ISNULL(p1.user_seeks, 0) AS user_seeks
, p2.user_scans - ISNULL(p1.user_scans, 0) AS user_scans
, d.statement AS TableName
, d.equality_columns
, d.inequality_columns
, d.included_columns
FROM #PreWorkMissingIndexes p1
RIGHT OUTER JOIN
#PostWorkMissingIndexes p2 ON p2.index_group_handle =
ISNULL(p1.index_group_handle, p2.index_group_handle)
AND p2.index_handle =
ISNULL(p1.index_handle, p2.index_handle)
INNER JOIN sys.dm_db_missing_index_details d
ON p2.index_handle = d.index_handle
WHERE p2.avg_total_user_cost - ISNULL(p1.avg_total_user_cost, 0) > 0
OR p2.avg_user_impact - ISNULL(p1.avg_user_impact, 0) > 0
OR p2.user_seeks - ISNULL(p1.user_seeks, 0) > 0
OR p2.user_scans - ISNULL(p1.user_scans, 0) > 0
ORDER BY [Total Cost] DESC
SELECT
p2.object_name, p2.counter_name, p2.instance_name
, ISNULL(p1.cntr_value, 0) AS InitialValue
, p2.cntr_value AS FinalValue
, p2.cntr_value - ISNULL(p1.cntr_value, 0) AS Change
, (p2.cntr_value - ISNULL(p1.cntr_value, 0)) * 100 / p1.cntr_value
AS [% Change]
FROM #PreWorkOSSnapShot p1
RIGHT OUTER JOIN
#PostWorkOSSnapShot p2 ON p2.object_name =
ISNULL(p1.object_name, p2.object_name)
AND p2.counter_name = ISNULL(p1.counter_name, p2.counter_name)
AND p2.instance_name = ISNULL(p1.instance_name, p2.instance_name)
WHERE p2.cntr_value - ISNULL(p1.cntr_value, 0) > 0
AND ISNULL(p1.cntr_value, 0) != 0
ORDER BY [% Change] DESC, Change DESC
DROP TABLE #PreWorkQuerySnapShot
DROP TABLE #PostWorkQuerySnapShot
DROP TABLE #PostWorkWaitStats
DROP TABLE #PreWorkWaitStats
DROP TABLE #PreWorkOSSnapShot
DROP TABLE #PostWorkOSSnapShot
DROP TABLE #PreWorkMissingIndexes
DROP TABLE #PostWorkMissingIndexes

27. What queries are running now

SELECT
es.session_id, es.host_name, es.login_name
, er.status, DB_NAME(database_id) AS DatabaseName
, SUBSTRING (qt.text,(er.statement_start_offset/2) + 1,
((CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, es.program_name, er.start_time, qp.query_plan
, er.wait_type, er.total_elapsed_time, er.cpu_time, er.logical_reads
, er.blocking_session_id, er.open_transaction_count, er.last_wait_type
, er.percent_complete
FROM sys.dm_exec_requests AS er
INNER JOIN sys.dm_exec_sessions AS es ON es.session_id = er.session_id
CROSS APPLY sys.dm_exec_sql_text( er.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
WHERE es.is_user_process=1
AND es.session_Id NOT IN (@@SPID)
ORDER BY es.session_id

28. Determining your most-recompiled queries

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
qs.plan_generation_num
, qs.total_elapsed_time
, qs.execution_count
, 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]
, qt.text AS [Parent Query], DB_NAME(qt.dbid) AS DatabaseName
, qs.creation_time
, qs.last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY plan_generation_num DESC

Operating system DMVs

29.  SQL script shown here will identify the top 20 causes of waiting on your server instance

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
wait_type, wait_time_ms, signal_wait_time_ms
, wait_time_ms - signal_wait_time_ms AS RealWait
, CONVERT(DECIMAL(12,2), wait_time_ms * 100.0 / SUM(wait_time_ms) OVER())
AS [% Waiting]
, CONVERT(DECIMAL(12,2), (wait_time_ms - signal_wait_time_ms) * 100.0
/ SUM(wait_time_ms) OVER()) AS [% RealWait]
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
AND wait_type != 'WAITFOR'
ORDER BYwait_time_ms DESC

30. Script will identify the waits that occur over a given 10-minute period, ordered by RealWait

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT wait_type, waiting_tasks_count
, wait_time_ms, max_wait_time_ms, signal_wait_time_ms
INTO #PreWorkWaitStats
FROM sys.dm_os_wait_stats
WAITFOR DELAY '00:10:00'
SELECT wait_type, waiting_tasks_count
, wait_time_ms, max_wait_time_ms, signal_wait_time_ms
INTO #PostWorkWaitStats
FROM sys.dm_os_wait_stats
SELECT
p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) AS wait_time_ms
, p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0)
AS signal_wait_time_ms
, ((p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0))
- (p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0)))
AS RealWait
, p2.wait_type
FROM #PreWorkWaitStats p1
RIGHT OUTER JOIN
#PostWorkWaitStats p2 ON p2.wait_type = ISNULL(p1.wait_type, p2.wait_type)
WHERE p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) > 0
AND p2.wait_type NOT LIKE '%SLEEP%'
AND p2.wait_type != 'WAITFOR'
ORDER BY RealWait DESC
DROP TABLE #PostWorkWaitStats
DROP TABLE #PreWorkWaitStats

31. Why your queries are waiting

--ThisRoutineIdentifier
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
sql_handle, plan_handle, total_elapsed_time, total_worker_time
, total_logical_reads, total_logical_writes, total_clr_time
, execution_count, statement_start_offset, statement_end_offset
INTO #PreWorkQuerySnapShot
FROM sys.dm_exec_query_stats
SELECT
wait_type, waiting_tasks_count
, wait_time_ms, max_wait_time_ms, signal_wait_time_ms
INTO #PreWorkWaitStats
FROM sys.dm_os_wait_stats
WAITFOR DELAY '00:05:00'
SELECT
wait_type, waiting_tasks_count, wait_time_ms
, max_wait_time_ms, signal_wait_time_ms
INTO #PostWorkWaitStats
FROM sys.dm_os_wait_stats
SELECT
sql_handle, plan_handle, total_elapsed_time, total_worker_time
, total_logical_reads, total_logical_writes, total_clr_time
, execution_count, statement_start_offset, statement_end_offset
INTO #PostWorkQuerySnapShot
FROM sys.dm_exec_query_stats
SELECT
p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) AS wait_time_ms
, p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0)
AS signal_wait_time_ms
, ((p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0))
– (p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0)))
AS RealWait
, p2.wait_type
FROM #PreWorkWaitStats p1
RIGHT OUTER JOIN
#PostWorkWaitStats p2 ON p2.wait_type = ISNULL(p1.wait_type, p2.wait_type)
WHERE p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) > 0
AND p2.wait_type NOT LIKE '%SLEEP%'
AND p2.wait_type != 'WAITFOR'
ORDER BY RealWait DESC
SELECT
p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0) AS [Duration]
, p2.total_worker_time - ISNULL(p1.total_worker_time, 0) AS [Time on CPU]
, (p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0)) -
(p2.total_worker_time - ISNULL(p1.total_worker_time, 0))
AS [Time blocked]
, p2.total_logical_reads - ISNULL(p1.total_logical_reads, 0) AS [Reads]
, p2.total_logical_writes - ISNULL(p1.total_logical_writes, 0)
AS [Writes]
, p2.total_clr_time - ISNULL(p1.total_clr_time, 0) AS [CLR time]
, p2.execution_count - ISNULL(p1.execution_count, 0) AS [Executions]
, SUBSTRING (qt.text,p2.statement_start_offset/2 + 1,
((CASE WHEN p2.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE p2.statement_end_offset
END - p2.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
FROM #PreWorkQuerySnapShot p1
RIGHT OUTER JOIN
#PostWorkQuerySnapShot p2 ON p2.sql_handle =
ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =
ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset =
ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
AND qt.text NOT LIKE '--ThisRoutineIdentifier%'
ORDER BY [Time blocked] DESC
DROP TABLE #PostWorkWaitStats
DROP TABLE #PreWorkWaitStats
DROP TABLE #PostWorkQuerySnapShot
DROP TABLE #PreWorkQuerySnapShot

32. What’s blocking my SQL query?

SELECT
Blocking.session_id as BlockingSessionId
, Sess.login_name AS BlockingUser
, BlockingSQL.text AS BlockingSQL
, Waits.wait_type WhyBlocked
, Blocked.session_id AS BlockedSessionId
, USER_NAME(Blocked.user_id) AS BlockedUser
, BlockedSQL.text AS BlockedSQL
, DB_NAME(Blocked.database_id) AS DatabaseName
FROM sys.dm_exec_connections AS Blocking
INNER JOIN sys.dm_exec_requests AS Blocked
ON Blocking.session_id = Blocked.blocking_session_id
INNER JOIN sys.dm_os_waiting_tasks AS Waits
ON Blocked.session_id = Waits.session_id
RIGHT OUTER JOIN sys.dm_exec_sessions Sess
ON Blocking.session_id = sess.session_id
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle)
AS BlockingSQL
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS BlockedSQL
ORDER BY BlockingSessionId, BlockedSessionId

33.  Script here will show how the performance counters change over the given time interval.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
[object_name], [counter_name], [instance_name]
, [cntr_value], [cntr_type]
INTO #PreWorkOSSnapShot
FROM sys.dm_os_performance_counters
WAITFOR DELAY '00:05:00'
SELECT
[object_name], [counter_name], [instance_name]
, [cntr_value], [cntr_type]
INTO #PostWorkOSSnapShot
FROM sys.dm_os_performance_counters
SELECT
p2.object_name, p2.counter_name, p2.instance_name
, ISNULL(p1.cntr_value, 0) AS InitialValue
, p2.cntr_value AS FinalValue
, p2.cntr_value - ISNULL(p1.cntr_value, 0) AS Change
, (p2.cntr_value - ISNULL(p1.cntr_value, 0)) * 100 / p1.cntr_value
AS [% Change]
FROM #PreWorkOSSnapShot p1
RIGHT OUTER JOIN
#PostWorkOSSnapShot p2 ON p2.object_name =
ISNULL(p1.object_name, p2.object_name)
AND p2.counter_name = ISNULL(p1.counter_name, p2.counter_name)
AND p2.instance_name = ISNULL(p1.instance_name, p2.instance_name)
WHERE p2.cntr_value - ISNULL(p1.cntr_value, 0) > 0
AND ISNULL(p1.cntr_value, 0) != 0
ORDER BY [% Change] DESC, Change DESC
DROP TABLE #PreWorkOSSnapShot
DROP TABLE #PostWorkOSSnapShot

34. Changes in performance counters and wait states

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
[object_name], [counter_name], [instance_name]
, [cntr_value], [cntr_type]
INTO #PreWorkOSSnapShot
FROM sys.dm_os_performance_counters
SELECT
wait_type, waiting_tasks_count
, wait_time_ms, max_wait_time_ms, signal_wait_time_ms
INTO #PreWorkWaitStats
FROM sys.dm_os_wait_stats
WAITFOR DELAY '00:05:00'
SELECT
wait_type, waiting_tasks_count, wait_time_ms
, max_wait_time_ms, signal_wait_time_ms
INTO #PostWorkWaitStats
FROM sys.dm_os_wait_stats
SELECT
[object_name], [counter_name], [instance_name]
, [cntr_value], [cntr_type]
INTO #PostWorkOSSnapShot
FROM sys.dm_os_performance_counters
SELECT
p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) AS wait_time_ms
, p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0)
AS signal_wait_time_ms
, ((p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0)) –
(p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0)))
AS RealWait
, p2.wait_type
FROM #PreWorkWaitStats p1
RIGHT OUTER JOIN
#PostWorkWaitStats p2 ON p2.wait_type = ISNULL(p1.wait_type, p2.wait_type)
WHERE p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) > 0
AND p2.wait_type NOT LIKE '%SLEEP%'
AND p2.wait_type != 'WAITFOR'
ORDER BY RealWait DESC
SELECT
p2.object_name, p2.counter_name, p2.instance_name
, ISNULL(p1.cntr_value, 0) AS InitialValue
, p2.cntr_value AS FinalValue
, p2.cntr_value - ISNULL(p1.cntr_value, 0) AS Change
, (p2.cntr_value - ISNULL(p1.cntr_value, 0)) * 100 / p1.cntr_value
AS [% Change]
FROM #PreWorkOSSnapShot p1
RIGHT OUTER JOIN
#PostWorkOSSnapShot p2 ON p2.object_name =ISNULL(p1.object_name, p2.object_name)
AND p2.counter_name = ISNULL(p1.counter_name, p2.counter_name)
AND p2.instance_name = ISNULL(p1.instance_name, p2.instance_name)
WHERE p2.cntr_value - ISNULL(p1.cntr_value, 0) > 0
AND ISNULL(p1.cntr_value, 0) != 0
ORDER BY [% Change] DESC, Change DESC
DROP TABLE #PostWorkWaitStats
DROP TABLE #PreWorkWaitStats
DROP TABLE #PreWorkOSSnapShot
DROP TABLE #PostWorkOSSnapShot

35. Changes in performance counters and wait states

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT sql_handle
    ,plan_handle
    ,total_elapsed_time
    ,total_worker_time
    ,total_logical_reads
    ,total_logical_writes
    ,total_clr_time
    ,execution_count
    ,statement_start_offset
    ,statement_end_offset
INTO #PreWorkQuerySnapShot
FROM sys.dm_exec_query_stats

SELECT [object_name]
    ,[counter_name]
    ,[instance_name]
    ,[cntr_value]
    ,[cntr_type]
INTO #PreWorkOSSnapShot
FROM sys.dm_os_performance_counters

SELECT wait_type
    ,waiting_tasks_count
    ,wait_time_ms
    ,max_wait_time_ms
    ,signal_wait_time_ms
INTO #PreWorkWaitStats
FROM sys.dm_os_wait_stats

WAITFOR DELAY '00:05:00'

SELECT wait_type
    ,waiting_tasks_count
    ,wait_time_ms
    ,max_wait_time_ms
    ,signal_wait_time_ms
INTO #PostWorkWaitStats
FROM sys.dm_os_wait_stats

SELECT [object_name]
    ,[counter_name]
    ,[instance_name]
    ,[cntr_value]
    ,[cntr_type]
INTO #PostWorkOSSnapShot
FROM sys.dm_os_performance_counters

SELECT sql_handle
    ,plan_handle
    ,total_elapsed_time
    ,total_worker_time
    ,total_logical_reads
    ,total_logical_writes
    ,total_clr_time
    ,execution_count
    ,statement_start_offset
    ,statement_end_offset
INTO #PostWorkQuerySnapShot
FROM sys.dm_exec_query_stats

SELECT p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0) AS [Duration]
    ,p2.total_worker_time - ISNULL(p1.total_worker_time, 0) AS [Time on CPU]
    ,(p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0)) - (p2.total_worker_time - ISNULL(p1.total_worker_time, 0)) AS [Time blocked]
    ,p2.total_logical_reads - ISNULL(p1.total_logical_reads, 0) AS [Reads]
    ,p2.total_logical_writes - ISNULL(p1.total_logical_writes, 0) AS [Writes]
    ,p2.total_clr_time - ISNULL(p1.total_clr_time, 0) AS [CLR time]
    ,p2.execution_count - ISNULL(p1.execution_count, 0) AS [Executions]
    ,SUBSTRING(qt.TEXT, p2.statement_start_offset / 2 + 1, (
            (
                CASE 
                    WHEN p2.statement_end_offset = - 1
                        THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
                    ELSE p2.statement_end_offset
                    END - p2.statement_start_offset
                ) / 2
            ) + 1) AS [Individual Query]
    ,qt.TEXT AS [Parent Query]
    ,DB_NAME(qt.dbid) AS DatabaseName
FROM #PreWorkQuerySnapShot p1
RIGHT OUTER JOIN #PostWorkQuerySnapShot p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle)
    AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
    AND p2.statement_start_offset = ISNULL(p1.statement_start_offset, p2.statement_start_offset)
    AND p2.statement_end_offset = ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) AS qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
    AND qt.TEXT NOT LIKE '--ThisRoutineIdentifier%'
ORDER BY [Duration] DESC

SELECT p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) AS wait_time_ms
    ,p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0) AS signal_wait_time_ms
    ,((p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0))) AS wait_time_ms
    ,(p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0)) AS RealWait
    ,p2.wait_type
FROM #PreWorkWaitStats p1
RIGHT OUTER JOIN #PostWorkWaitStats p2 ON p2.wait_type = ISNULL(p1.wait_type, p2.wait_type)
WHERE p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) > 0
    AND p2.wait_type NOT LIKE '%SLEEP%'
    AND p2.wait_type != 'WAITFOR'
ORDER BY RealWait DESC

SELECT p2.object_name
    ,p2.counter_name
    ,p2.instance_name
    ,ISNULL(p1.cntr_value, 0) AS InitialValue
    ,p2.cntr_value AS FinalValue
    ,p2.cntr_value - ISNULL(p1.cntr_value, 0) AS Change
    ,(p2.cntr_value - ISNULL(p1.cntr_value, 0)) * 100 / p1.cntr_value AS [% Change]
FROM #PreWorkOSSnapShot p1
RIGHT OUTER JOIN #PostWorkOSSnapShot p2 ON p2.object_name = ISNULL(p1.object_name, p2.object_name)
    AND p2.counter_name = ISNULL(p1.counter_name, p2.counter_name)
    AND p2.instance_name = ISNULL(p1.instance_name, p2.instance_name)
WHERE p2.cntr_value - ISNULL(p1.cntr_value, 0) > 0
    AND ISNULL(p1.cntr_value, 0) != 0
ORDER BY [% Change] DESC
    ,Change DESC

DROP TABLE #PreWorkQuerySnapShot
DROP TABLE #PostWorkQuerySnapShot
DROP TABLE #PostWorkWaitStats
DROP TABLE #PreWorkWaitStats
DROP TABLE #PreWorkOSSnapShot
DROP TABLE #PostWorkOSSnapShot

Resolving transaction issues

36. Observing the current locks

SELECT DB_NAME(resource_database_id) AS DatabaseName, request_session_id
, resource_type, request_status, request_mode
FROM sys.dm_tran_locks
WHERE request_session_id !=@@spid
ORDER BY request_session_id

37. Information contained in sessions, connections, and requests

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT *
FROM sys.dm_exec_sessions s
LEFT OUTER JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id
LEFT OUTER JOIN sys.dm_exec_requests r
ON c.connection_id = r.connection_id
WHERE s.session_id > 50

38. How to discover which locks are currently held

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT DB_NAME(resource_database_id) AS DatabaseName
, request_session_id
, resource_type
, CASE
WHEN resource_type = 'OBJECT'
THEN OBJECT_NAME(resource_associated_entity_id)
WHEN resource_type IN ('KEY', 'PAGE', 'RID')
THEN (SELECT OBJECT_NAME(OBJECT_ID)
FROM sys.partitions p
WHERE p.hobt_id = l.resource_associated_entity_id)
END AS resource_type_name
, request_status
, request_mode
FROM sys.dm_tran_locks l
WHERE request_session_id !=@@spid
ORDER BY request_session_id

39. How to identify contended resources

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
tl1.resource_type,
DB_NAME(tl1.resource_database_id) AS DatabaseName,
tl1.resource_associated_entity_id,
tl1.request_session_id,
tl1.request_mode,
tl1.request_status
, CASE
WHEN tl1.resource_type = 'OBJECT'
THEN OBJECT_NAME(tl1.resource_associated_entity_id)
WHEN tl1.resource_type IN ('KEY', 'PAGE', 'RID')
THEN (SELECT OBJECT_NAME(OBJECT_ID)
FROM sys.partitions s
WHERE s.hobt_id = tl1.resource_associated_entity_id)
END AS resource_type_name
FROM sys.dm_tran_locks as tl1
INNER JOIN sys.dm_tran_locks as tl2
ON tl1.resource_associated_entity_id = tl2.resource_associated_entity_id
AND tl1.request_status <> tl2.request_status
AND (tl1.resource_description = tl2.resource_description
OR (tl1.resource_description IS NULL
AND tl2.resource_description IS NULL))
ORDER BY tl1.resource_associated_entity_id, tl1.request_status

40. How to identify contended resources, including SQL query details

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
tl1.resource_type
, DB_NAME(tl1.resource_database_id) AS DatabaseName
, tl1.resource_associated_entity_id
, tl1.request_session_id
, tl1.request_mode
, tl1.request_status
, CASE
WHEN tl1.resource_type = 'OBJECT'
THEN OBJECT_NAME(tl1.resource_associated_entity_id)
WHEN tl1.resource_type IN ('KEY', 'PAGE', 'RID')
THEN (SELECT OBJECT_NAME(OBJECT_ID)
FROM sys.partitions s
WHERE s.hobt_id = tl1.resource_associated_entity_id)
END AS resource_type_name
, t.text AS [Parent Query]
, SUBSTRING (t.text,(r.statement_start_offset/2) + 1,
((CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), t.text)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset)/2) + 1) AS [Individual Query]
FROM sys.dm_tran_locks as tl1
INNER JOIN sys.dm_tran_locks as tl2
ON tl1.resource_associated_entity_id = tl2.resource_associated_entity_id
AND tl1.request_status <> tl2.request_status
AND (tl1.resource_description = tl2.resource_description
OR (tl1.resource_description IS NULL
AND tl2.resource_description IS NULL))
INNER JOIN sys.dm_exec_connections c
ON tl1.request_session_id = c.most_recent_session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
LEFT OUTER JOIN sys.dm_exec_requests r ON c.connection_id = r.connection_id
ORDER BY tl1.resource_associated_entity_id, tl1.request_status

41. How to find an idle session with an open transaction

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT es.session_id, es.login_name, es.host_name, est.text
, cn.last_read, cn.last_write, es.program_name
FROM sys.dm_exec_sessions es
INNER JOIN sys.dm_tran_session_transactions st
ON es.session_id = st.session_id
INNER JOIN sys.dm_exec_connections cn
ON es.session_id = cn.session_id
CROSS APPLY sys.dm_exec_sql_text(cn.most_recent_sql_handle) est
LEFT OUTER JOIN sys.dm_exec_requests er
ON st.session_id = er.session_id
AND er.session_id IS NULL

42. Amount of space (total, used, and free) in tempdb

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT SUM(user_object_reserved_page_count
+ internal_object_reserved_page_count
+ version_store_reserved_page_count
+ mixed_extent_page_count
+ unallocated_extent_page_count) * (8.0/1024.0)
AS [TotalSizeOfTempDB(MB)]
, SUM(user_object_reserved_page_count
+ internal_object_reserved_page_count
+ version_store_reserved_page_count
+ mixed_extent_page_count) * (8.0/1024.0)
AS [UsedSpace (MB)]
, SUM(unallocated_extent_page_count * (8.0/1024.0))
AS [FreeSpace (MB)]
FROM sys.dm_db_file_space_usage

43. Total amount of space (data, log, and log used) by database

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT instance_name
, counter_name
, cntr_value / 1024.0 AS [Size(MB)]
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Databases'
AND counter_name IN (
'Data File(s) Size (KB)'
, 'Log File(s) Size (KB)'
, 'Log File(s) Used Size (KB)')
ORDER BY instance_name, counter_name

44. Tempdb total space usage by object type

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
SUM (user_object_reserved_page_count) * (8.0/1024.0)
AS [User Objects (MB)],
SUM (internal_object_reserved_page_count) * (8.0/1024.0)
AS [Internal Objects (MB)],
SUM (version_store_reserved_page_count) * (8.0/1024.0)
AS [Version Store (MB)],
SUM (mixed_extent_page_count)* (8.0/1024.0)
AS [Mixed Extent (MB)],
SUM (unallocated_extent_page_count)* (8.0/1024.0)
AS [Unallocated (MB)]
FROM sys.dm_db_file_space_usage

45.  tempdb Space usage by session

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT es.session_id
, ec.connection_id
, es.login_name
, es.host_name
, st.text
, su.user_objects_alloc_page_count
, su.user_objects_dealloc_page_count
, su.internal_objects_alloc_page_count
, su.internal_objects_dealloc_page_count
, ec.last_read
, ec.last_write
, es.program_name
FROM sys.dm_db_session_space_usage su
INNER JOIN sys.dm_exec_sessions es
ON su.session_id = es.session_id
LEFT OUTER JOIN sys.dm_exec_connections ec
ON su.session_id = ec.most_recent_session_id
OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
WHERE su.session_id > 50

46. Space used and reclaimed in tempdb for completed batches

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT CAST(SUM(su.user_objects_alloc_page_count
+ su.internal_objects_alloc_page_count) * (8.0/1024.0)
AS DECIMAL(20,3)) AS [SpaceUsed(MB)]
, CAST(SUM(su.user_objects_alloc_page_count
– su.user_objects_dealloc_page_count
+ su.internal_objects_alloc_page_count
– su.internal_objects_dealloc_page_count)
* (8.0/1024.0) AS DECIMAL(20,3)) AS [SpaceStillUsed(MB)]
, su.session_id
, ec.connection_id
, es.host_name
, st.text AS [LastQuery]
, ec.last_read
, ec.last_write
, es.program_name
FROM sys.dm_db_session_space_usage su
INNER JOIN sys.dm_exec_sessions es ON su.session_id = es.session_id
LEFT OUTER JOIN sys.dm_exec_connections ec
ON su.session_id = ec.most_recent_session_id
OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
WHERE su.session_id > 50
GROUP BY su.session_id, ec.connection_id, es.login_name, es.host_name
, st.text, ec.last_read, ec.last_write, es.program_name
ORDER BY [SpaceStillUsed(MB)] DESC

47. Space used by running SQL queries

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT es.session_id
, ec.connection_id
, es.login_name
, es.host_name
, st.text
, tu.user_objects_alloc_page_count
, tu.user_objects_dealloc_page_count
, tu.internal_objects_alloc_page_count
, tu.internal_objects_dealloc_page_count
, ec.last_read
, ec.last_write
, es.program_name
FROM sys.dm_db_task_space_usage tu
INNER JOIN sys.dm_exec_sessions es ON tu.session_id = es.session_id
LEFT OUTER JOIN sys.dm_exec_connections ec
ON tu.session_id = ec.most_recent_session_id
OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
WHERE tu.session_id > 50

48. Space used and not reclaimed by active SQL queries

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT SUM(ts.user_objects_alloc_page_count
+ ts.internal_objects_alloc_page_count)
* (8.0/1024.0) AS [SpaceUsed(MB)]
, SUM(ts.user_objects_alloc_page_count
– ts.user_objects_dealloc_page_count
+ ts.internal_objects_alloc_page_count
– ts.internal_objects_dealloc_page_count)
* (8.0/1024.0) AS [SpaceStillUsed(MB)]
, ts.session_id
, ec.connection_id
, es.login_name
, es.host_name
, st.text AS [Parent Query]
, SUBSTRING (st.text,(er.statement_start_offset/2) + 1,
((CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1) AS [Current Query]
, ec.last_read
, ec.last_write
, es.program_name
FROM sys.dm_db_task_space_usage ts
INNER JOIN sys.dm_exec_sessions es ON ts.session_id = es.session_id
LEFT OUTER JOIN sys.dm_exec_connections ec
ON ts.session_id = ec.most_recent_session_id
OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) st
LEFT OUTER JOIN sys.dm_exec_requests er ON ts.session_id = er.session_id
WHERE ts.session_id > 50
GROUP BY ts.session_id, ec.connection_id, es.login_name, es.host_name
, st.text, ec.last_read, ec.last_write, es.program_name
, SUBSTRING (st.text,(er.statement_start_offset/2) + 1,
((CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1)
ORDER BY [SpaceStillUsed(MB)] DESC

49. Indexes under row-locking pressure

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
x.name AS SchemaName
, OBJECT_NAME(s.object_id) AS TableName
, i.name AS IndexName
, s.row_lock_wait_in_ms
, s.row_lock_wait_count
FROM sys.dm_db_index_operational_stats(db_ID(), NULL, NULL, NULL) s
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.indexes i ON s.index_id = i.index_id
AND i.object_id = o.object_id
INNER JOIN sys.schemas x ON x.schema_id = o.schema_id
WHERE s.row_lock_wait_in_ms > 0
AND o.is_ms_shipped = 0
ORDER BY s.row_lock_wait_in_ms DESC

50. Indexes with the most lock escalations

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
x.name AS SchemaName
, OBJECT_NAME (s.object_id) AS TableName
, i.name AS IndexName
, s.index_lock_promotion_count
FROM sys.dm_db_index_operational_stats(db_ID(), NULL, NULL, NULL) s
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.indexes i ON s.index_id = i.index_id
AND i.object_id = o.object_id
INNER JOIN sys.schemas x ON x.schema_id = o.schema_id
WHERE s.index_lock_promotion_count > 0
AND o.is_ms_shipped = 0
ORDER BY s.index_lock_promotion_count DESC

51. Indexes with the most unsuccessful lock escalations

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
x.name AS SchemaName
, OBJECT_NAME (s.object_id) AS TableName
, i.name AS IndexName
, s.index_lock_promotion_attempt_count – s.index_lock_promotion_count
AS UnsuccessfulIndexLockPromotions
FROM sys.dm_db_index_operational_stats(db_ID(), NULL, NULL, NULL) s
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.indexes i ON s.index_id = i.index_id
AND i.object_id = o.object_id
INNER JOIN sys.schemas x ON x.schema_id = o.schema_id
WHERE (s.index_lock_promotion_attempt_count - index_lock_promotion_count)>0
AND o.is_ms_shipped = 0
ORDER BY UnsuccessfulIndexLockPromotions DESC

52. Indexes with the most page splits

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
x.name AS SchemaName
, object_name(s.object_id) AS TableName
, i.name AS IndexName
, s.leaf_allocation_count
, s.nonleaf_allocation_count
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) s
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.indexes i ON s.index_id = i.index_id
AND i.object_id = o.object_id
INNER JOIN sys.schemas x ON x.schema_id = o.schema_id
WHERE s.leaf_allocation_count > 0
AND o.is_ms_shipped = 0
ORDER BY s.leaf_allocation_count DESC

53. Indexes with the most latch contention

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
x.name AS SchemaName
, OBJECT_NAME(s.object_id) AS TableName
, i.name AS IndexName
, s.page_latch_wait_in_ms
, s.page_latch_wait_count
FROM sys.dm_db_index_operational_stats(db_ID(), NULL, NULL, NULL) s
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.indexes i ON s.index_id = i.index_id
AND i.object_id = o.object_id
INNER JOIN sys.schemas x ON x.schema_id = o.schema_id
WHERE s.page_latch_wait_in_ms > 0
AND o.is_ms_shipped = 0
ORDER BY s.page_latch_wait_in_ms DESC

54. Indexes with the most page I/O-latch contention

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT x.NAME AS SchemaName
    ,OBJECT_NAME(s.object_id) AS TableName
    ,i.NAME AS IndexName
    ,s.row_lock_wait_in_ms
INTO #PreWorkIndexCount
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) s
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.indexes i ON s.index_id = i.index_id
    AND i.object_id = o.object_id
INNER JOIN sys.schemas x ON x.schema_id = o.schema_id
WHERE s.row_lock_wait_in_ms > 0
    AND o.is_ms_shipped = 0

SELECT sql_handle
    ,plan_handle
    ,total_elapsed_time
    ,total_worker_time
    ,total_logical_reads
    ,total_logical_writes
    ,total_clr_time
    ,execution_count
    ,statement_start_offset
    ,statement_end_offset
INTO #PreWorkQuerySnapShot
FROM sys.dm_exec_query_stats

WAITFOR DELAY '01:00:00'

SELECT x.NAME AS SchemaName
    ,OBJECT_NAME(s.object_id) AS TableName
    ,i.NAME AS IndexName
    ,s.row_lock_wait_in_ms
INTO #PostWorkIndexCount
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) s
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.indexes i ON s.index_id = i.index_id
    AND i.object_id = o.object_id
INNER JOIN sys.schemas x ON x.schema_id = o.schema_id
WHERE s.row_lock_wait_in_ms > 0
    AND o.is_ms_shipped = 0

SELECT sql_handle
    ,plan_handle
    ,total_elapsed_time
    ,total_worker_time
    ,total_logical_reads
    ,total_logical_writes
    ,total_clr_time
    ,execution_count
    ,statement_start_offset
    ,statement_end_offset
INTO #PostWorkQuerySnapShot
FROM sys.dm_exec_query_stats

SELECT p2.SchemaName
    ,p2.TableName
    ,p2.IndexName
    ,p2.row_lock_wait_in_ms - ISNULL(p1.row_lock_wait_in_ms, 0) AS RowLockWaitTimeDelta_ms
FROM #PreWorkIndexCount p1
RIGHT OUTER JOIN #PostWorkIndexCount p2 ON p2.SchemaName = ISNULL(p1.SchemaName, p2.SchemaName)
    AND p2.TableName = ISNULL(p1.TableName, p2.TableName)
    AND p2.IndexName = ISNULL(p1.IndexName, p2.IndexName)
WHERE p2.row_lock_wait_in_ms - ISNULL(p1.row_lock_wait_in_ms, 0) > 0
ORDER BY RowLockWaitTimeDelta_ms DESC

SELECT p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0) AS [Duration]
    ,p2.total_worker_time - ISNULL(p1.total_worker_time, 0) AS [Time on CPU]
    ,(p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0))
    ,(p2.total_worker_time - ISNULL(p1.total_worker_time, 0)) AS [Time blocked]
    ,p2.total_logical_reads - ISNULL(p1.total_logical_reads, 0) AS [Reads]
    ,p2.total_logical_writes - ISNULL(p1.total_logical_writes, 0) AS [Writes]
    ,p2.total_clr_time - ISNULL(p1.total_clr_time, 0) AS [CLR time]
    ,p2.execution_count - ISNULL(p1.execution_count, 0) AS [Executions]
    ,SUBSTRING(qt.TEXT, p2.statement_start_offset / 2 + 1, (
            (
                CASE 
                    WHEN p2.statement_end_offset = - 1
                        THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
                    ELSE p2.statement_end_offset
                    END - p2.statement_start_offset
                ) / 2
            ) + 1) AS [Individual Query]
    ,qt.TEXT AS [Parent Query]
    ,DB_NAME(qt.dbid) AS DatabaseName
FROM #PreWorkQuerySnapShot p1
RIGHT OUTER JOIN #PostWorkQuerySnapShot p2 ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle)
    AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
    AND p2.statement_start_offset = ISNULL(p1.statement_start_offset, p2.statement_start_offset)
    AND p2.statement_end_offset = ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) AS qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
    AND qt.TEXT NOT LIKE '--ThisRoutineIdentifier%'
ORDER BY [Duration] DESC

DROP TABLE #PreWorkIndexCount
DROP TABLE #PostWorkIndexCount
DROP TABLE #PreWorkQuerySnapShot
DROP TABLE #PostWorkQuerySnapShot

55. Determining how many rows are inserted/deleted/updated/selected

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
sql_handle, plan_handle, total_elapsed_time, total_worker_time
, total_logical_reads, total_logical_writes, total_clr_time
, execution_count, statement_start_offset, statement_end_offset
INTO #PreWorkQuerySnapShot
FROM sys.dm_exec_query_stats
SELECT x.name AS SchemaName
, OBJECT_NAME (s.object_id) AS TableName
, i.name AS IndexName
, s.leaf_delete_count
, s.leaf_ghost_count
, s.leaf_insert_count
, s.leaf_update_count
, s.range_scan_count
, s.singleton_lookup_count
INTO #PreWorkIndexCount
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) s
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.indexes i ON s.index_id = i.index_id
AND i.object_id = o.object_id
INNER JOIN sys.schemas x ON x.schema_id = o.schema_id
WHERE o.is_ms_shipped = 0
WAITFOR DELAY '01:00:00'
SELECT
sql_handle, plan_handle, total_elapsed_time, total_worker_time
, total_logical_reads, total_logical_writes, total_clr_time
, execution_count, statement_start_offset, statement_end_offset
INTO #PostWorkQuerySnapShot
FROM sys.dm_exec_query_stats
SELECT x.name AS SchemaName
, OBJECT_NAME (s.object_id) AS TableName
, i.name AS IndexName
, s.leaf_delete_count
, s.leaf_ghost_count
, s.leaf_insert_count
, s.leaf_update_count
, s.range_scan_count
, s.singleton_lookup_count
INTO #PostWorkIndexCount
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) s
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.indexes i ON s.index_id = i.index_id
AND i.object_id = o.object_id
INNER JOIN sys.schemas x ON x.schema_id = o.schema_id
WHERE o.is_ms_shipped = 0
SELECT
p2.SchemaName
, p2.TableName
, p2.IndexName
, p2.leaf_delete_count - ISNULL(p1.leaf_delete_count, 0)
AS leaf_delete_countDelta
, p2.leaf_ghost_count - ISNULL(p1.leaf_ghost_count, 0)
AS leaf_ghost_countDelta
, p2.leaf_insert_count - ISNULL(p1.leaf_insert_count, 0)
AS leaf_insert_countDelta
, p2.leaf_update_count - ISNULL(p1.leaf_update_count, 0)
AS leaf_update_countDelta
, p2.range_scan_count - ISNULL(p1.range_scan_count, 0)
AS range_scan_countDelta
, p2.singleton_lookup_count - ISNULL(p1.singleton_lookup_count, 0)
AS singleton_lookup_countDelta
FROM #PreWorkIndexCount p1
RIGHT OUTER JOIN
#PostWorkIndexCount p2 ON p2.SchemaName =
ISNULL(p1.SchemaName, p2.SchemaName)
AND p2.TableName = ISNULL(p1.TableName, p2.TableName)
AND p2.IndexName = ISNULL(p1.IndexName, p2.IndexName)
WHERE p2.leaf_delete_count - ISNULL(p1.leaf_delete_count, 0) > 0
OR p2.leaf_ghost_count - ISNULL(p1.leaf_ghost_count, 0) > 0
OR p2.leaf_insert_count - ISNULL(p1.leaf_insert_count, 0) > 0
OR p2.leaf_update_count - ISNULL(p1.leaf_update_count, 0) > 0
OR p2.range_scan_count - ISNULL(p1.range_scan_count, 0) > 0
OR p2.singleton_lookup_count - ISNULL(p1.singleton_lookup_count, 0) > 0
ORDER BY leaf_delete_countDelta DESC
SELECT
p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0) AS [Duration]
, p2.total_worker_time - ISNULL(p1.total_worker_time, 0) AS [Time on CPU]
, (p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0)) –
(p2.total_worker_time - ISNULL(p1.total_worker_time, 0))
AS [Time blocked]
, p2.total_logical_reads - ISNULL(p1.total_logical_reads, 0) AS [Reads]
, p2.total_logical_writes - ISNULL(p1.total_logical_writes, 0)
AS [Writes]
, p2.total_clr_time - ISNULL(p1.total_clr_time, 0) AS [CLR time]
, p2.execution_count - ISNULL(p1.execution_count, 0) AS [Executions]
, SUBSTRING (qt.text,p2.statement_start_offset/2 + 1,
((CASE WHEN p2.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE p2.statement_end_offset
END - p2.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
FROM #PreWorkQuerySnapShot p1
RIGHT OUTER JOIN
#PostWorkQuerySnapShot p2 ON p2.sql_handle =
ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =
ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset =
ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
ORDER BY [Duration] DESC
DROP TABLE #PreWorkIndexCount
DROP TABLE #PostWorkIndexCount
DROP TABLE #PreWorkQuerySnapShot
DROP TABLE #PostWorkQuerySnapShot

56. Rebuilding and reorganizing fragmented indexes

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
CREATE TABLE #FragmentedIndexes(
DatabaseName SYSNAME
, SchemaName SYSNAME
, TableName SYSNAME
, IndexName SYSNAME
, [Fragmentation%] FLOAT)
INSERT INTO #FragmentedIndexes
SELECT
DB_NAME(DB_ID()) AS DatabaseName
, ss.name AS SchemaName
, OBJECT_NAME (s.object_id) AS TableName
, i.name AS IndexName
, s.avg_fragmentation_in_percent AS [Fragmentation%]
FROM sys.dm_db_index_physical_stats(db_id(),NULL, NULL, NULL, 'SAMPLED') s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]
AND s.index_id = i.index_id
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.schemas ss ON ss.[schema_id] = o.[schema_id]
WHERE s.database_id = DB_ID()
AND i.index_id != 0
AND s.record_count > 0
AND o.is_ms_shipped = 0
DECLARE @RebuildIndexesSQL NVARCHAR(MAX)
SET @RebuildIndexesSQL = ''
SELECT
@RebuildIndexesSQL = @RebuildIndexesSQL +
CASE
WHEN [Fragmentation%] > 30
THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
+ QUOTENAME(SchemaName) + '.'
+ QUOTENAME(TableName) + ' REBUILD;'
WHEN [Fragmentation%] > 10
THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
+ QUOTENAME(SchemaName) + '.'
+ QUOTENAME(TableName) + ' REORGANIZE;'
END
FROM #FragmentedIndexes
WHERE [Fragmentation%] > 10
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset < LEN(@RebuildIndexesSQL))
BEGIN
PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
SET @StartOffset = @StartOffset + @Length
END
PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
EXECUTE sp_executesql @RebuildIndexesSQL
DROP TABLE #FragmentedIndexes

57. Rebuild/reorganize for all databases on a given server

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
CREATE TABLE #FragmentedIndexes(
DatabaseName SYSNAME
, SchemaName SYSNAME
, TableName SYSNAME
, IndexName SYSNAME
, [Fragmentation%] FLOAT)
EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #FragmentedIndexes SELECT DB_NAME(DB_ID()) AS DatabaseName , ss.name AS SchemaName , OBJECT_NAME (s.object_id) AS TableName , i.name AS IndexName , s.avg_fragmentation_in_percent AS [Fragmentation%] FROM sys.dm_db_index_physical_stats(db_id(),NULL, NULL, NULL, ''SAMPLED'') s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id INNER JOIN sys.objects o ON s.object_id = o.object_id INNER JOIN sys.schemas ss ON ss.[schema_id] = o.[schema_id] WHERE s.database_id = DB_ID() AND i.index_id != 0 AND s.record_count > 0 AND o.is_ms_shipped = 0 ;'
DECLARE @RebuildIndexesSQL NVARCHAR(MAX)
SET @RebuildIndexesSQL = ''
SELECT
@RebuildIndexesSQL = @RebuildIndexesSQL +
CASE
WHEN [Fragmentation%] > 30
THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
+ QUOTENAME(DatabaseName) + '.'+ QUOTENAME(SchemaName) + '.'
+ QUOTENAME(TableName) + ' REBUILD;'
WHEN [Fragmentation%] > 10
THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
+ QUOTENAME(DatabaseName) + '.'+ QUOTENAME(SchemaName) + '.'
+ QUOTENAME(TableName) + ' REORGANIZE;'
END
FROM #FragmentedIndexes
WHERE [Fragmentation%] > 10
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset < LEN(@RebuildIndexesSQL))
BEGIN
PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
SET @StartOffset = @StartOffset + @Length
END
PRINT SUBSTRING(@RebuildIndexesSQL, @StartOffset, @Length)
EXECUTE sp_executesql @RebuildIndexesSQL
DROP TABLE #FragmentedIndexes

58. Intelligently update statistics—simple version

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
ss.name AS SchemaName
, st.name AS TableName
, si.name AS IndexName
, si.type_desc AS IndexType
, STATS_DATE(si.object_id,si.index_id) AS StatsLastTaken
, ssi.rowcnt
, ssi.rowmodctr
INTO #IndexUsage
FROM sys.indexes si
INNER JOIN sys.sysindexes ssi ON si.object_id = ssi.id
AND si.name = ssi.name
INNER JOIN sys.tables st ON st.[object_id] = si.[object_id]
INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
WHERE st.is_ms_shipped = 0
AND si.index_id != 0
AND ssi.rowcnt > 100
AND ssi.rowmodctr > 0
DECLARE @UpdateStatisticsSQL NVARCHAR(MAX)
SET @UpdateStatisticsSQL = ''
SELECT
@UpdateStatisticsSQL = @UpdateStatisticsSQL
+ CHAR(10) + 'UPDATE STATISTICS '
+ QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)
+ ' ' + QUOTENAME(IndexName) + ' WITH SAMPLE '
+ CASE
WHEN rowcnt < 500000 THEN '100 PERCENT'
WHEN rowcnt < 1000000 THEN '50 PERCENT'
WHEN rowcnt < 5000000 THEN '25 PERCENT'
WHEN rowcnt < 10000000 THEN '10 PERCENT'
WHEN rowcnt < 50000000 THEN '2 PERCENT'
WHEN rowcnt < 100000000 THEN '1 PERCENT'
ELSE '3000000 ROWS '
END
+ '-- ' + CAST(rowcnt AS VARCHAR(22)) + ' rows'
FROM #IndexUsage
DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000
WHILE (@StartOffset < LEN(@UpdateStatisticsSQL))
BEGIN
PRINT SUBSTRING(@UpdateStatisticsSQL, @StartOffset, @Length)
SET @StartOffset = @StartOffset + @Length
END
PRINT SUBSTRING(@UpdateStatisticsSQL, @StartOffset, @Length)
EXECUTE sp_executesql @UpdateStatisticsSQL
DROP TABLE #IndexUsage

59. Estimating when a job will finish

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT r.percent_complete
, DATEDIFF(MINUTE, start_time, GETDATE()) AS Age
, DATEADD(MINUTE, DATEDIFF(MINUTE, start_time, GETDATE()) /
percent_complete * 100, start_time) AS EstimatedEndTime
, t.Text AS ParentQuery
, SUBSTRING (t.text,(r.statement_start_offset/2) + 1,
((CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), t.text)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset)/2) + 1) AS IndividualQuery
, start_time
, DB_NAME(Database_Id) AS DatabaseName
, Status
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE session_id > 50
AND percent_complete > 0
ORDER BY percent_complete DESC

60. Who’s doing what and when?

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
CREATE TABLE dbo.WhatsGoingOnHistory(
[Runtime] [DateTime],
[session_id] [smallint] NOT NULL,
[login_name] [varchar](128) NOT NULL,
[host_name] [varchar](128) NULL,
[DBName] [varchar](128) NULL,
[Individual Query] [varchar](max) NULL,
[Parent Query] [varchar](200) NULL,
[status] [varchar](30) NULL,
[start_time] [datetime] NULL,
[wait_type] [varchar](60) NULL,
[program_name] [varchar](128) NULL
)
GO
CREATE UNIQUE NONCLUSTERED INDEX
[NONCLST_WhatsGoingOnHistory] ON [dbo].[WhatsGoingOnHistory]
([Runtime] ASC, [session_id] ASC)
GO
INSERT INTO dbo.WhatsGoingOnHistory
SELECT
GETDATE()
, s.session_id
, s.login_name
, s.host_name
, DB_NAME(r.database_id) AS DBName
, SUBSTRING (t.text,(r.statement_start_offset/2) + 1,
((CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), t.text)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset)/2) + 1) AS [Individual Query]
, SUBSTRING(text, 1, 200) AS [Parent Query]
, r.status
, r.start_time
, r.wait_type
, s.program_name
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
INNER JOIN sys.dm_exec_requests r ON c.connection_id = r.connection_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.session_id > 50
AND r.session_id != @@spid
WAITFOR DELAY '00:01:00'
GO 1440 -- 60 * 24 (one day)

61. Determining where your query spends its time

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
sql_handle, plan_handle, total_elapsed_time, total_worker_time
, total_logical_reads, total_logical_writes, total_clr_time
, execution_count, statement_start_offset, statement_end_offset
INTO #PreWorkQuerySnapShot
FROM sys.dm_exec_query_stats
EXEC MO.PNLYearToDate_v01iws
@pControlOrgIds = '537'
, @pCOBStart = '27 may 2009'
, @pCOBEnd = '27 may 2009'
SELECT
sql_handle, plan_handle, total_elapsed_time, total_worker_time
, total_logical_reads, total_logical_writes, total_clr_time
, execution_count, statement_start_offset, statement_end_offset,
last_execution_time
INTO #PostWorkQuerySnapShot
FROM sys.dm_exec_query_stats
SELECT
p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0) AS [Duration]
, p2.total_worker_time - ISNULL(p1.total_worker_time, 0) AS [Time on CPU]
, (p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0)) -
(p2.total_worker_time - ISNULL(p1.total_worker_time, 0))
AS [Time waiting]
, p2.total_logical_reads - ISNULL(p1.total_logical_reads, 0) AS [Reads]
, p2.total_logical_writes - ISNULL(p1.total_logical_writes, 0)
AS [Writes]
, p2.total_clr_time - ISNULL(p1.total_clr_time, 0) AS [CLR time]
, p2.execution_count - ISNULL(p1.execution_count, 0) AS [Executions]
, p2.last_execution_time
, SUBSTRING (qt.text,p2.statement_start_offset/2 + 1,
((CASE WHEN p2.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE p2.statement_end_offset
END - p2.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
FROM #PreWorkQuerySnapShot p1
RIGHT OUTER JOIN
#PostWorkQuerySnapShot p2
ON p2.sql_handle = ISNULL(p1.sql_handle, p2.sql_handle)
AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)
AND p2.statement_start_offset =
ISNULL(p1.statement_start_offset, p2.statement_start_offset)
AND p2.statement_end_offset =
ISNULL(p1.statement_end_offset, p2.statement_end_offset)
CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt
WHERE p2.execution_count != ISNULL(p1.execution_count, 0)
AND qt.text LIKE '%PNLYearToDate_v01iws %'
ORDER BY [Parent Query], p2.statement_start_offset
DROP TABLE #PreWorkQuerySnapShot
DROP TABLE #PostWorkQuerySnapShot

62. Memory used per database

SET TRAN ISOLATION LEVEL READ UNCOMMITTED
SELECT
ISNULL(DB_NAME(database_id), 'ResourceDb') AS DatabaseName
, CAST(COUNT(row_count) * 8.0 / (1024.0) AS DECIMAL(28,2))
AS [Size (MB)]
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
ORDER BY DatabaseName

63. Memory used by objects in the current database

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
OBJECT_NAME(p.[object_id]) AS [TableName]
, (COUNT(*) * 8) / 1024 AS [Buffer size(MB)]
, ISNULL(i.name, '-- HEAP --') AS ObjectName
, COUNT(*) AS NumberOf8KPages
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
INNER JOIN sys.indexes i ON p.index_id = i.index_id
AND p.[object_id] = i.[object_id]
ON a.container_id = p.hobt_id
WHERE b.database_id = DB_ID()
AND p.[object_id] > 100
GROUP BY p.[object_id], i.name
ORDER BY NumberOf8KPages DESC

64. I/O stalls at the database level

SET TRAN ISOLATION LEVEL READ UNCOMMITTED
SELECT DB_NAME(database_id) AS [DatabaseName]
, SUM(CAST(io_stall / 1000.0 AS DECIMAL(20,2))) AS [IO stall (secs)]
, SUM(CAST(num_of_bytes_read / 1024.0 / 1024.0 AS DECIMAL(20,2)))
AS [IO read (MB)]
, SUM(CAST(num_of_bytes_written / 1024.0 / 1024.0 AS DECIMAL(20,2)))
AS [IO written (MB)]
, SUM(CAST((num_of_bytes_read + num_of_bytes_written)
/ 1024.0 / 1024.0 AS DECIMAL(20,2))) AS [TotalIO (MB)]
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
GROUP BY database_id
ORDER BY [IO stall (secs)] DESC

65. I/O waits at the file level

SET TRAN ISOLATION LEVEL READ UNCOMMITTED
SELECT DB_NAME(database_id) AS [DatabaseName]
, file_id
, SUM(CAST(io_stall / 1000.0 AS DECIMAL(20,2))) AS [IO stall (secs)]
, SUM(CAST(num_of_bytes_read / 1024.0 / 1024.0 AS DECIMAL(20,2)))
AS [IO read (MB)]
, SUM(CAST(num_of_bytes_written / 1024.0 / 1024.0 AS DECIMAL(20,2)))
AS [IO written (MB)], SUM(CAST((num_of_bytes_read + num_of_bytes_written)
/ 1024.0 / 1024.0 AS DECIMAL(20,2))) AS [TotalIO (MB)]
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
GROUP BY database_id, file_id
ORDER BY [IO stall (secs)] DESC

66. Average read/write times per file, per database

SET TRAN ISOLATION LEVEL READ UNCOMMITTED
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

SELECT    TOP 10
session_id,
database_id,
user_objects_alloc_page_count + internal_objects_alloc_page_count / 129
AS tempdb_usage_MB
FROM sys.dm_db_session_space_usage
ORDER BY user_objects_alloc_page_count + internal_objects_alloc_page_count DESC;

To view SQL Server error log from SQL Server Enterprise Manager
– Expand a server group, and then expand a server.
– Expand Management, and then expand SQL Server Logs.
– Click the SQL Server Log to view it. Error log information appears in the details pane

One can also execute “exec xp_readerrorlog”

SQL server logs are generally located at “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG”.

The log files are rolled over on each sql server instance restart, or when running DBCC ERRORLOG statement. sql server will only retain max 6 old file. This could be changed byselecting configure

How do Add detailed logging

Edit NSService.exe.config File

The default installation folder is C:\Program Files\Microsoft SQL Server\90\NotificationServices\n.n.nnn\bin.
Open the NSservice.exe.config file.

<?xml version=”1.0″ encoding=”UTF-8″?>
<!–
The default logging levels for all components is Warning

Off = 0 < Error = 1 < Warning = 2 < Info = 3 < Verbose = 4

Change the values of the value attribute to change the logging
level.

Setting the logging level enables all log events which are less
than or equal to the log level setting
–>
<configuration>
<system.diagnostics>
<switches>
<add name=”LogAdministrative” value=”2″/>
<add name=”LogService” value=”2″/>
<add name=”LogEventProvider” value=”2″/>
<add name=”LogEventCollector” value=”2″/>
<add name=”LogGenerator” value=”2″/>
<add name=”LogDistributor” value=”2″/>
<add name=”LogVacuumer” value=”2″/>
<add name=”LogOther” value=”2″/>
<add name=”LogPerformanceMonitor” value=”2″/>
</switches>
</system.diagnostics>
</configuration>

Initially, each logging option is set to 2, which turns on logging for error and warning messages only.
To apply the logging changes, save the file and restart the instance of Notification Services.

Logging Level Values : Logging levels determine which types of events are written to the Application log. You can control the logging level by setting the value attribute to one of the following values.

0 Off
1 Error messages only
2 Error and warning messages
3 Error, warning, and informational messages
4 Verbose logging, which writes all messages to the Application log

Step 1.  Find Size of Database Log files

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) like ‘%AdventureWorks%’
and name like ‘%Log%’
order by SizeMB desc

Step 2.  See if databases have active transactions . You won’t able to truncate log easily with active transactions on database.

select name,log_reuse_wait_desc from sys.databases

name    log_reuse_wait_desc
master    NOTHING
tempdb    ACTIVE_TRANSACTION
model    NOTHING

Step 3. Locate active transaction details . If needed kill sessions returned by dbcc opentran.

DBCC OPENTRAN

Transaction information for database ‘Test’.

Oldest active transaction:
SPID (server process ID): 79
UID (user ID) : -1
Name          : BULK INSERT
LSN           : (63361:51900:6)
Start time    : Aug 16 2013  7:42:53:783AM
SID           : 0x978700b4443f8e489fa13f97b3f375fc
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Step 4.  TSQL to Shrink Transaction log from all databases. To truncate log from specific databases alter NOT IN condition.

DECLARE @dbname VARCHAR(100)
DECLARE @dbid INT
DECLARE curDatabases CURSOR FOR
SELECT name, database_id
FROM sys.databases
WHERE name NOT IN (‘master’, ‘model’, ‘msdb’)
OPEN curDatabases
FETCH NEXT FROM curDatabases INTO
@dbname,
@dbid
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
DECLARE @dbfilename VARCHAR(100)
PRINT ‘USE MASTER’
PRINT ‘GO’
PRINT ‘ALTER DATABASE [‘ + @dbname + ‘] SET RECOVERY SIMPLE’
PRINT ‘GO’
PRINT ‘USE [‘ + @dbname + ‘]’
PRINT ‘GO’
Select @dbfilename = name
FROM sys.master_files
WHERE type = 1
AND database_id = @dbid
PRINT ‘DBCC SHRINKFILE (‘ + @dbfilename + ‘, 1024)’
PRINT ‘GO’
PRINT ”
END
FETCH NEXT FROM curDatabases INTO
@dbname,
@dbid
END
CLOSE curDatabases
DEALLOCATE curDatabases

Fixed database roles can be mapped to the more detailed permissions that are included in SQL Server.

The following table describes the mapping of the fixed database roles to permissions.

Fixed database role	Database-level permission	Server-level permission
db_accessadmin	Granted: ALTER ANY USER, CREATE SCHEMAGranted with GRANT option: CONNECT	Granted: VIEW ANY DATABASE
db_backupoperator	Granted: BACKUP DATABASE, BACKUP LOG, CHECKPOINT	Granted: VIEW ANY DATABASE
db_datareader	Granted: SELECT	Granted: VIEW ANY DATABASE
db_datawriter	Granted: DELETE, INSERT, UPDATE	Granted: VIEW ANY DATABASE
db_ddladmin	Granted: ALTER ANY ASSEMBLY, ALTER ANY ASYMMETRIC KEY, ALTER ANY CERTIFICATE, ALTER ANY CONTRACT, ALTER ANY DATABASE DDL TRIGGER, ALTER ANY DATABASE EVENT, NOTIFICATION, ALTER ANY DATASPACE, ALTER ANY FULLTEXT CATALOG, ALTER ANY MESSAGE TYPE, ALTER ANY REMOTE SERVICE BINDING, ALTER ANY ROUTE, ALTER ANY SCHEMA, ALTER ANY SERVICE, ALTER ANY SYMMETRIC KEY, CHECKPOINT, CREATE AGGREGATE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE QUEUE, CREATE RULE, CREATE SYNONYM, CREATE TABLE, CREATE TYPE, CREATE VIEW, CREATE XML SCHEMA COLLECTION, REFERENCES	Granted: VIEW ANY DATABASE
db_denydatareader	Denied: SELECT	Granted: VIEW ANY DATABASE
db_denydatawriter	Denied: DELETE, INSERT, UPDATE
db_owner	Granted with GRANT option: CONTROL	Granted: VIEW ANY DATABASE
db_securityadmin	Granted: ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, VIEW DEFINITION	Granted: VIEW ANY DATABASE
dbm_monitor	Granted: VIEW most recent status in Database Mirroring Monitor Important The dbm_monitor fixed database role is created in the msdb database when the first database is registered in Database Mirroring Monitor. The new dbm_monitor role has no members until a system administrator assigns users to the role.	Granted: VIEW ANY DATABASE

Fixed database roles are not equivalent to their database-level permission. For example, the db_owner fixed database role has the CONTROL DATABASE permission. But granting the CONTROL DATABASE permission does not make a user a member of the db_owner fixed database role. Members of the db_owner fixed database role are identified as the dbo user in the databases, but users with the CONTROL DATABASE permission, are not.

SQL Server comes with pre-defined error messages that can be raised when a particular condition occurs. Built-in error messages are stored in the sysmessages table of the master database. Depending on the severity level, error messages can be returned to the offending connection or could be recorded in the SQL Server error log. The severity level determines the type and seriousness of the problem that caused the error. The following table summarizes various error severity levels:

SQLDIAG.exe executable can be found in the SQL Server installation directory under the BINN folder. SQLDIAG records the SQL Server configuration, contents of the error logs (current, as well as archived logs), and Windows configuration including registry keys. If SQL Server is running when SQLDIAG is executed, it will record the output of the following routines:

>──sqldiag─┬───────────────────┬─┬───────────────────────────────────┬──>
           ├─ -? ──────────────┤ └─┬──────────────┬─┬──────────────┬─┘
           └─ -I instance_name─┘   └─ -U login_id─┘ ├─ -P password─┤
                                                    └─ -E ─────────┘

>─┬─────────────────┬─┬──────┬─┬──────┬─┬──────┬────────────────────────>
  └─ -O output_file─┘ └─ -X ─┘ └─ -M ─┘ └─ -C ─┘

sqldiag

Connecting to server SERVERNAME
Getting file E:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG
Getting file E:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG.1
Getting file E:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG.2
Getting file E:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG.3
Getting file E:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG.4
Getting file E:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG.5
Getting file E:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG.6
Getting registry information
Getting library version information
Getting configuration information
Getting current user information
Getting lock information
Getting database information
Getting product information
Getting extended procedures information
Getting process information
Getting input buffers
Getting head blockers
Getting machine information. Please wait, this may take a few minutes
Data Stored in E:\Program Files\Microsoft SQL Server\MSSQL\log\SQLdiag.txt

I have number of SQL server databases and users . I am in a need to create read only access to users who wants to look at data.

• Create read only role within single database

CREATE ROLE [readOnlyAccess] AUTHORIZATION dbo
GO
GRANT  SELECT ON SCHEMA ::dbo TO readOnlyAccess

• Create read only role within All databases in an Instance

CREATE ROLE [readOnlyAccess] AUTHORIZATION dbo
GO
exec sp_MSforeachdb 'USE [?]; drop role readOnlyAccess; CREATE ROLE [readOnlyAccess] AUTHORIZATION dbo; GRANT SELECT ON SCHEMA ::dbo TO readOnlyAccess'

• Create a readonlyuser within single database

USE [master]
GO
CREATE LOGIN [sagarreadonly] WITH PASSWORD=N'sagarreadonly', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [DataEncryptDemo]
GO
CREATE USER [sagarreadonly] FOR LOGIN [sagarreadonly]
GO
USE [DataEncryptDemo]
GO
EXEC sp_addrolemember N'readOnlyAccess', N'sagarreadonly'
GO

• Create a readonlyuser within ALL databases

USE [master]
GO
CREATE LOGIN [sagarreadonly] WITH PASSWORD=N'sagarreadonly', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
use master
exec sp_MSforeachdb 'IF ''?'' NOT IN (''master'',''model'',''msdb'',''tempdb'' ) begin
USE [?]; CREATE USER [sagarreadonly] FOR LOGIN [sagarreadonly]; end'
go
USE [master]
GO
exec sp_MSforeachdb 'IF ''?'' NOT IN (''master'',''model'',''msdb'',''tempdb'' ) begin
USE [?]; EXEC sp_addrolemember N''readOnlyAccess'', N''sagarreadonly''; end'
GO
USE [master] 
GO
exec sp_MSForEachDb 'USE ? GRANT VIEW DEFINITION TO schemareader'
go

Above SQL will pass access to database tables but you won’t see any procedures,triggers,functions or TSQL objects. Use following TSQL procedure to grant access on all objects within schema.

USE DataEncryptDemo
GO 
CREATE PROCEDURE usp_ExecGrantViewDefinition 
(@login VARCHAR(30)) 
AS 
/*
Included Object Types are: 
P - Stored Procedure 
V - View 
FN - SQL scalar-function
TR - Trigger 
IF - SQL inlined table-valued function
TF - SQL table-valued function
U - Table (user-defined)
*/ 
SET NOCOUNT ON 

CREATE TABLE #runSQL
(runSQL VARCHAR(2000) NOT NULL) 

--Declare @execSQL varchar(2000), @login varchar(30), @space char (1), @TO char (2) 
DECLARE @execSQL VARCHAR(2000), @space CHAR (1), @TO CHAR (2) 

SET @to = 'TO'
SET @execSQL = 'Grant View Definition ON ' 
SET @login = REPLACE(REPLACE (@login, '[', ''), ']', '')
SET @login = '[' + @login + ']'
SET @space = ' '

INSERT INTO #runSQL 
SELECT @execSQL + schema_name(schema_id) + '.' + [name] + @space + @TO + @space + @login 
FROM sys.all_objects s 
WHERE type IN ('P', 'V', 'FN', 'TR', 'IF', 'TF', 'U') 
AND is_ms_shipped = 0 
ORDER BY s.type, s.name 

SET @execSQL = '' 

Execute_SQL: 
SET ROWCOUNT 1 
SELECT @execSQL = runSQL FROM #runSQL
PRINT @execSQL --Comment out if you don't want to see the output
EXEC (@execSQL)
DELETE FROM #runSQL WHERE runSQL = @execSQL
IF EXISTS (SELECT * FROM #runSQL) 
  GOTO Execute_SQL 
SET ROWCOUNT 0
DROP TABLE #runSQL 
GO

Grant privs to all databases except master,model,msdb,tempdb

exec sp_MSforeachdb 'IF ''?'' NOT IN (''master'',''model'',''msdb'',''tempdb'' ) begin USE [?]; exec usp_ExecGrantViewDefinition @login=''sagarreadonly''; end'

You should now see TSQL procedures and other objects.

Often I have to copy number of SQL server database structures from prod system for testing. It’s difficult to get rid of all objects within a database manually. Following script will do just about that.

DropAllObjectsTSQL.txt : Please be careful with database you connect to as this is a destructive script.

use TestDB
go
/* Drop all non-system stored procs */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = ‘P’ AND category = 0 ORDER BY [name])

WHILE @name is not null
BEGIN
SELECT @SQL = ‘DROP PROCEDURE [dbo].[‘ + RTRIM(@name) +’]’
EXEC (@SQL)
PRINT ‘Dropped Procedure: ‘ + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = ‘P’ AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all views */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = ‘V’ AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = ‘DROP VIEW [dbo].[‘ + RTRIM(@name) +’]’
EXEC (@SQL)
PRINT ‘Dropped View: ‘ + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = ‘V’ AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all functions */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’) AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = ‘DROP FUNCTION [dbo].[‘ + RTRIM(@name) +’]’
EXEC (@SQL)
PRINT ‘Dropped Function: ‘ + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N’FN’, N’IF’, N’TF’, N’FS’, N’FT’) AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all Foreign Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = ‘FOREIGN KEY’ ORDER BY TABLE_NAME)

WHILE @name is not null
BEGIN
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = ‘FOREIGN KEY’ AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
WHILE @constraint IS NOT NULL
BEGIN
SELECT @SQL = ‘ALTER TABLE [dbo].[‘ + RTRIM(@name) +’] DROP CONSTRAINT [‘ + RTRIM(@constraint) +’]’
EXEC (@SQL)
PRINT ‘Dropped FK Constraint: ‘ + @constraint + ‘ on ‘ + @name
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = ‘FOREIGN KEY’ AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = ‘FOREIGN KEY’ ORDER BY TABLE_NAME)
END
GO

/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128)
DECLARE @constraint VARCHAR(254)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = ‘PRIMARY KEY’ ORDER BY TABLE_NAME)

WHILE @name IS NOT NULL
BEGIN
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = ‘PRIMARY KEY’ AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
WHILE @constraint is not null
BEGIN
SELECT @SQL = ‘ALTER TABLE [dbo].[‘ + RTRIM(@name) +’] DROP CONSTRAINT [‘ + RTRIM(@constraint)+’]’
EXEC (@SQL)
PRINT ‘Dropped PK Constraint: ‘ + @constraint + ‘ on ‘ + @name
SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = ‘PRIMARY KEY’ AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)
END
SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = ‘PRIMARY KEY’ ORDER BY TABLE_NAME)
END
GO

/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = ‘U’ AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
SELECT @SQL = ‘DROP TABLE [dbo].[‘ + RTRIM(@name) +’]’
EXEC (@SQL)
PRINT ‘Dropped Table: ‘ + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = ‘U’ AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

 

Use

• select @@VERSION”  OR
• SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’) ” Or
• Simply look at the value in the Management Studio Explorer

Now compare it to the list of SQL Server builds to determine which patches are installe

sp_configure is a tool to display and change SQL Server settings. Please be aware that changing these settings can dramatically affect your instance.

How to view configured values

One can use SYS.CONFIGURATIONS else sp_configure without parameters to view current values

How to enable listing of all parameters

Run “select * from SYS.CONFIGURATIONS where name like ‘show%advanced%’” to see if show advanced options is already enabled?

sp_configure will only list 16 parameters than 68 total for SQL2008R2 with “show advanced options “ disabled. Enable “show advanced options” to get listing of all parameters

sp_configure ‘show advanced options’, 1
GO
reconfigure
GO

How to change the sql server configured value

Syntax: sp_configure ‘<<Configuration Name>>’,'<<Configuration Value>>’

sp_configure 'max server memory', 12288
RECONFIGURE
GO

Difference between SYS.CONFIGURATIONS & sp_configure

Columns of SYS.CONFIGURATIONS

configuration_id – Internal ID of the configuration setting
name – Config value name
value – Config value
value_in_use – The twin of run_value above
description – Details of a parameter
is_dynamic –  If a value is dynamic or not. 1 = Dynamic, just run reconfigure after changing and it changes “on the fly”. 0 = not dynamic – need to stop and start SQL Server service
is_advanced – Like the above, ever wonder if you have to change the show advanced option to display a value? Well you can find out here. It’s a flag, 1 is yes, 0 is no, like the is_dynamic flag

Running the procedure sp_configure without parameters gives a result set which contains the column run_value. The difference between Config_Value and run_value is that config_value is the value that the configration name is configured, run_value  is the value that the instance is currently using.

Columns of SP_CONFIGURE
name – The name of the value to be changed
minimum – The minimum value setting that is allowed
maximum – The maximum value that is allowed
config_value – What value is currently configured?
run_value – What value is currently running?

If you are looking for a good TSQL packaged scripts which will do usual database maintenance activities then you are on right page. I have detailed 2 such projects which will help you to setup maintenance job in less than an hour.

Please use it at your own risk. I would highly recommend running it on test setups  before adding at prod/useful boxes.

1. YourSqlDba  : T-Sql Database script that automate database maintenance using SQL Agent an DB Mail. Performs optimizations (index, statistics), integritry check, databases backups (complete and tx logs).

What it does…

• Automate full backups and recurrent log backups.
• Perform update statistics (spread all of them over a specified period of time, by default a week)
• Selectively reorganize or rebuild index that show a fragmentation thresold
• Check database integrity
• Keep an up-to-date backup of MSDB which record all backup history (at the end of each full or log backup cycle)
• Clean-up history log (backup, Sql Agent, Sql maintenance)
• Recycle SQL Server error log every day (keep up to 30 archives)
• Provides simplified sp utilities to do manual backup, duplicate database, and restore database.
• Schedule agent task for maintenance
• Configure database mail for maintenance reporting
• Send e-mail that regular notify maintenance activity
• Send exceptional e-mail for notifying problematic maintenance issues
• Disable immediately databases with integrity problem
• Disable databases which failed to be backup more than 3 consecutives times (manual backup can reset the failure count)
• Connect network drives visible to database engine for backup purposes, and make them auto-reconnect at startup.

How it does it ?

• Everything is stored in a database named YourSQLDba
  • Stored procedures
  • Internal table (logs, and informational tracking info)
• It uses SQL Server Agent to run maintenance job
• Two job are created that use the same procedure with different parameters
  • One for daily maintenance (integrity test, update statistics, reorganize or rebuild, full backups)
  • One for log backups
• Job backups reset log size that grow beyond a given threshold
• It communicates to operator or Dba or whatever else is responsible (SQL Agent operator) through database mail

How to Install it?

Download script from here

Compile the script on database. You will notice 2 default jobs added as part of a install.

Default Maintenance

YourSQLDba automated part is expressed as two tasks in the SQL Server Agent:

• YourSQLDba_FullBackups_And_Maintenance
• YourSQLDba_LogBackups.

Each task contains a single step which call YourSQLDba_DoMaint, but with different parameters.

YourSQLDba_LogBackups is quicker to explain, as it deal exclusively with log backups.

• They are performed continuouly all around the clock, at every 15 minutes (thanls to SQL Server Agent Scheduler). Some may wonder how it avoid problems like running into a concurrent attempt to do log backup at the same time there is an ongoing database backup on the same database. YourSQLDba use a feature introduced in SQL2000 to avoid, this. This is called application locks (See sp_getapplock). A log backup for a given database give up immediately, if it can’t obtain the application lock. YourSQLDba defines for both log and complete maintenance for a given database. However complete maintenance wait at least 5 minutes before giving up waiting for a lock. We suppose that 5 minutes is a reasonable time to wait for a log backup, since log backup done at every 15 minutes doesn’t have too much time to become too large, and hence long to backup.
• At log backup time, after the log is emptied by the log backup, log file that have grown beyond a certain size are shrunk. This target size is calculated this way (sum of size of primary data file + sum of size of files in default file group + 1/10 sum of the size of files outside default file group). If log size that is greater than 40% of this computation, we assumed that shrunk time has come. Why compute on 1/10 of the file outside of default file group ? Usually special filegroup are used to stored blob, or historical data, or to do table and index partionning. We have a big volume in these filegroups for which only a fraction of the content is usually changed. Log ratio size relatively to these big file need to be smaller, contrary to primary data file of default filegroup.

YourSQLDba_FullBackups_And_Maintenance needs more explanations as it performs more tasks.

• Log cleanup are performed
  • Sql Server current is recycled (it means archived) and a new log is generated.
  • Logs like SQL Agent backup history are limited to 30 days.
  • YourSQLDba log history is limited to 30 days.
  • Mail items log is limited to 30 days
  • Agent job history is limited to 30 days.
• By default the procedure YourSQLDba_DoMaint is called with the option to do the following tasks.
  • Check database integrity. If a database is found with any integrity problem, it is put immediately in emergency mode
  • Update distribution statistics. Updates are done using full scan, but process is spread by default (parameter) on seven days.
  • Reorganize or Rebuild index depending on their real level of fragmentation, base on a threshold specified internally into the procedure
  • Perform full database backup
    • Full database backup files older that one day (parameter) are suppressed from disk backup directory
    • Log backup files older that seven day (parameter) are suppressed from disk backup directory
    • A full backup file of the database is created and named this way dbname_[yyyy-mm-dd_hhhmimsss_dw].bak where dbname is the name of the database, yyyy-mm-dd HHhMImSSs_dw a timestamp plus the day name of the week and .Bak extension is the regular extension used for full database backup.
    • A log backup file is created and named about the same as a full backup file except the extension which is .Trn for transaction log backups.
    • Every backup cycle, whether it is full backup or transaction log backup cycle, is followed by a complete backup of MSDB. Msdb keeps tracks of all backups done and makes it easier to do other restores once it is restored since all the backup history become accurate and up-to-date.
    • Backup file name and locations are retained into a table into YourSQLDba. It allows YourSQLDba to reuse the backup log file for a given database, to continue to append other log backups to the same file. It greatly reduce the number of files produced by the backup procedure, by using the same log backup file for all the day.

2.  SQL Server Maintenance Solution by  ola.hallengren.com

The SQL Server Maintenance Solution comprises scripts for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012. The solution is based on stored procedures, functions, the sqlcmd utility, and SQL Server Agent jobs.

Download MaintenanceSolution.sql. This script creates all the objects and jobs that you need.

Learn more about using the SQL Server Maintenance Solution:

• DatabaseBackup: SQL Server Backup
• DatabaseIntegrityCheck: SQL Server Integrity Check
• IndexOptimize: SQL Server Index and Statistics Maintenance

Helpful SQL Scripts

SQL Server Maintenance Solution logs the command executed in table dbo.CommandLog

1. Find the most expensive “SQL server maintenance” job for last one month

SELECT command,starttime, DATEDIFF(MINUTE,starttime,endtime) as duration FROM dbo.CommandLog
WHERE starttime BETWEEN GETDATE()-30 AND GETDATE()
ORDER BY 3 DESC

2. Find list of reorg/rebuilt indexes & their fragmentation details

select command,starttime, DATEDIFF(MINUTE,starttime,endtime) as "Duration In Mins", ExtendedInfo.value('(/ExtendedInfo/PageCount)[1]', 'int') as PageCount,
          ExtendedInfo.value('(/ExtendedInfo/Fragmentation)[1]', 'decimal(10,4)') as Fragmentation
from commandlog
where command is not null 
and ExtendedInfo.value('(/ExtendedInfo/PageCount)[1]', 'int') is not null
and ExtendedInfo.value('(/ExtendedInfo/Fragmentation)[1]', 'decimal(10,4)') is not null
ORDER BY 3 DESC

3.  Script to shrink all user SQL databases.

DECLARE @db VARCHAR(255)
DECLARE c CURSOR FOR
SELECT name FROM sys.databases WHERE is_read_only=0 AND STATE=0
  AND name NOT IN ('master','model','tempdb','msdb')
OPEN c
FETCH c INTO @db
WHILE @@FETCH_STATUS=0
BEGIN
  EXEC SP_dboption @db,'trunc. log on chkpt.','true' 
  DBCC shrinkdatabase (@db)
  FETCH NEXT FROM c INTO @db
END
CLOSE c
DEALLOCATE c

4. Shrink log files for all databases

declare @ssql nvarchar(4000)
set @ssql= '
        if ''?'' not in (''tempdb'',''master'',''model'',''msdb'') begin
        use [?]
        declare @tsql nvarchar(4000) set @tsql = ''''
        declare @iLogFile int
        declare LogFiles cursor for
        select fileid from sysfiles where  status & 0x40 = 0x40
        open LogFiles
        fetch next from LogFiles into @iLogFile
        while @@fetch_status = 0
        begin
          set @tsql = @tsql + ''DBCC SHRINKFILE(''+cast(@iLogFile as varchar(5))+'', 1) ''
          fetch next from LogFiles into @iLogFile
        end
        set @tsql = @tsql + '' BACKUP LOG [?] WITH TRUNCATE_ONLY '' + @tsql
        --print @tsql
        exec(@tsql)
        close LogFiles
        DEALLOCATE LogFiles
        end'

exec sp_msforeachdb @ssql

We can only configure one default instance under SQL server installation.  If we need additional instance  it should be a named instance.

Start installation and select “new installation or add feature”

Select option button for “new installation or add feature”.

Accept defaults and click on NEXT until you see “Instance Configuration” screen as below.

Please name your named instance, I set it as “standby”

It will take a while, once done you should see a success message.

Let’s connect to newly created “standby” instance using SSMS.

1. How to locate if I am connected to a SQL server cluster?

SELECT SERVERPROPERTY(‘IsClustered’)

2. Find name of the Node on which SQL Server Instance is Currently running

SELECT SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) AS [CurrentNodeName]
If the server is not cluster, then the above query returns the Host Name of the Server.

3. Find SQL Server Cluster Nodes

a. SELECT * FROM fn_virtualservernodes()
b. SELECT * FROM sys.dm_os_cluster_nodes

4. Find SQL Server Cluster Shared Drives

a. SELECT * FROM fn_servershareddrives()
b. SELECT * FROM sys.dm_io_cluster_shared_drives

1. Download http://www.microsoft.com/downloads/details.aspx?familyid=9a8b005b-84e4-4f24-8d65-cb53442d9e19&displaylang=en and install SQLIO
2. It will be installed under C:\Program Files\SQLIO. Please add this directory in your FIle Path
3. SQLIO will need a test file to run against your drives for performance.

First look at C:\SQLIO\param.txt which would look like something below.

• First parameter (D:\testfile.dat)- the physical location of the testing file. Change the drive letter to point to the SAN drive you want to test, like D:\testfile.dat here.
• Last parameter (10)- the size of the testing file in megabytes. Increase this to 20480 or larger.

Now run following command to create D:\testfile.dat. test file. “sqlio -kW -s10 -fsequential -o8 -b8 -LS -Fparam.txt timeout /T 10”

Please see there is a file created at “D:\testfile.dat”, here 100MB as I defined it in the param.txt

Testing Your SAN Performance:

Let’s create a batch file SAN_RESULTS.TXT to take all of the possibilities and run them all.

— Random Reads
sqlio -kW -t3 -s90 -dD -o1 -frandoD -b64 -BH -LS Testfile.dat
sqlio -kW -t3 -s90 -dD -o2 -frandoD -b64 -BH -LS Testfile.dat
sqlio -kW -t3 -s90 -dD -o4 -frandoD -b64 -BH -LS Testfile.dat
sqlio -kW -t3 -s90 -dD -o8 -frandoD -b64 -BH -LS Testfile.dat
sqlio -kW -t3 -s90 -dD -o16 -frandoD -b64 -BH -LS Testfile.dat
sqlio -kW -t3 -s90 -dD -o32 -frandoD -b64 -BH -LS Testfile.dat
sqlio -kW -t3 -s90 -dD -o64 -frandoD -b64 -BH -LS Testfile.dat
sqlio -kW -t3 -s90 -dD -o128 -frandoD -b64 -BH -LS Testfile.dat

— Sequential Reads
sqlio -kW -t3 -s90 -dD -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t3 -s90 -dD -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t3 -s90 -dD -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t3 -s90 -dD -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t3 -s90 -dD -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t3 -s90 -dD -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t3 -s90 -dD -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio -kW -t3 -s90 -dD -o128 -fsequential -b64 -BH -LS Testfile.dat

• -kW means writes (as opposed to reads)
• -t23means two threads
• -s120 means test for 120 seconds
• -dD means drive letter D (Please edit and add your SAN drive there)
• -o1 means one outstanding request (not piling up requests)
• -frandom means random access (as opposed to sequential)
• -b64 means 64kb IOs

Commonly used SQLIO.exe options

Then go to the command prompt in the same directory as SQLIO is installed and type: “SANTESTER.BAT > SAN_RESULTS.TXT ” , You will see a output like below.

For in depth details see Using SQLIO.rtf

C:\Program Files\SQLIO>REM — Sequential Reads

C:\Program Files\SQLIO>sqlio -kW -t1 -s90 -dD -o1 -fsequential -b64 -BH -LS Testfile.dat
sqlio v1.5.SG
using system counter for latency timings, 3579545 counts per second
1 thread writing for 90 secs to file D:Testfile.dat
using 64KB sequential IOs
enabling multiple I/Os per thread with 1 outstanding
buffering set to use hardware disk cache (but not file cache)
using current size: 100 MB for file: D:Testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 353.01
MBs/sec: 22.06
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 2
Max_Latency(ms): 748
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 52 36 8 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2

C:\Program Files\SQLIO>sqlio -kW -t1 -s90 -dD -o2 -fsequential -b64 -BH -LS Testfile.dat
sqlio v1.5.SG
using system counter for latency timings, 3579545 counts per second
1 thread writing for 90 secs to file D:Testfile.dat
using 64KB sequential IOs
enabling multiple I/Os per thread with 2 outstanding
buffering set to use hardware disk cache (but not file cache)
using current size: 100 MB for file: D:Testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 473.17
MBs/sec: 29.57
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 3
Max_Latency(ms): 331
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 8 18 4 42 26 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1

C:\Program Files\SQLIO>sqlio -kW -t1 -s90 -dD -o4 -fsequential -b64 -BH -LS Testfile.dat
sqlio v1.5.SG
using system counter for latency timings, 3579545 counts per second
1 thread writing for 90 secs to file D:Testfile.dat
using 64KB sequential IOs
enabling multiple I/Os per thread with 4 outstanding
buffering set to use hardware disk cache (but not file cache)
using current size: 100 MB for file: D:Testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 515.11
MBs/sec: 32.19
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 7
Max_Latency(ms): 350
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 2 6 0 0 0 21 68 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1

C:\Program Files\SQLIO>sqlio -kW -t1 -s90 -dD -o8 -fsequential -b64 -BH -LS Testfile.dat
sqlio v1.5.SG
using system counter for latency timings, 3579545 counts per second
1 thread writing for 90 secs to file D:Testfile.dat
using 64KB sequential IOs
enabling multiple I/Os per thread with 8 outstanding
buffering set to use hardware disk cache (but not file cache)
using current size: 100 MB for file: D:Testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 516.03
MBs/sec: 32.25
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 14
Max_Latency(ms): 360
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 2 3 3 0 0 0 0 0 0 0 32 44 14 0 0 0 0 0 0 0 0 1

C:\Program Files\SQLIO>sqlio -kW -t1 -s90 -dD -o16 -fsequential -b64 -BH -LS Testfile.dat
sqlio v1.5.SG
using system counter for latency timings, 3579545 counts per second
1 thread writing for 90 secs to file D:Testfile.dat
using 64KB sequential IOs
enabling multiple I/Os per thread with 16 outstanding
buffering set to use hardware disk cache (but not file cache)
using current size: 100 MB for file: D:Testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 519.12
MBs/sec: 32.44
latency metrics:
Min_Latency(ms): 1
Avg_Latency(ms): 30
Max_Latency(ms): 364
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 0 0 0 0 1 1 3 1 0 0 0 0 0 0 0 0 0 0 0 0 92

C:\Program Files\SQLIO>sqlio -kW -t1 -s90 -dD -o32 -fsequential -b64 -BH -LS Testfile.dat
sqlio v1.5.SG
using system counter for latency timings, 3579545 counts per second
1 thread writing for 90 secs to file D:Testfile.dat
using 64KB sequential IOs
enabling multiple I/Os per thread with 32 outstanding
buffering set to use hardware disk cache (but not file cache)
using current size: 100 MB for file: D:Testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 524.43
MBs/sec: 32.77
latency metrics:
Min_Latency(ms): 13
Avg_Latency(ms): 60
Max_Latency(ms): 414
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 1 1 0 0 94

C:\Program Files\SQLIO>sqlio -kW -t1 -s90 -dD -o64 -fsequential -b64 -BH -LS Testfile.dat
sqlio v1.5.SG
using system counter for latency timings, 3579545 counts per second
1 thread writing for 90 secs to file D:Testfile.dat
using 64KB sequential IOs
enabling multiple I/Os per thread with 64 outstanding
buffering set to use hardware disk cache (but not file cache)
using current size: 100 MB for file: D:Testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 522.62
MBs/sec: 32.66
latency metrics:
Min_Latency(ms): 28
Avg_Latency(ms): 121
Max_Latency(ms): 507
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 100

C:\Program Files\SQLIO>sqlio -kW -t1 -s90 -dD -o128 -fsequential -b64 -BH -LS Testfile.dat
sqlio v1.5.SG
using system counter for latency timings, 3579545 counts per second
1 thread writing for 90 secs to file D:Testfile.dat
using 64KB sequential IOs
enabling multiple I/Os per thread with 128 outstanding
buffering set to use hardware disk cache (but not file cache)
using current size: 100 MB for file: D:Testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 527.72
MBs/sec: 32.98
latency metrics:
Min_Latency(ms): 11
Avg_Latency(ms): 241
Max_Latency(ms): 597
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 100

Let’s create a TRACE for Performance Analysis

USE [Test]
GO
/****** Object: Table [dbo].[Person] Script Date: 07/30/2009 15:33:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Person](
[ID] [int] NOT NULL,
[First] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[last] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[County] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Let’s enable TRACE using SQL Profiler

Click on “Events Selection” and then choose “Show All Events”. I picked up “ShowPlan All” & “Performance Statistics” & then hit RUN

One defined click on

Enable IO and Explain Plan settings using “set options”

Run the SQL and look at “Messages” & “Execution plan” tab to see the relevant output.

Right click on “server name” and select “properties”

If you have BATCH processing environment , try increasing MAX degree of Parallism to enhance query performance.

SQL server will use Parallism for any SQL whose cost is more than 5

1. Windows System Monitor – PART I
2. DBCC commands – PART II
3. Dynamic Memory Views – PART III
4. SQL Server Profiler – – PART IV

There are 2 types of dynamic views, System and Database views.

System views are located at System Databases -> Master -> Views -> DM_OS_XXXX

sys.dm_os_buffer_descriptors
sys.dm_os_memory_pools
sys.dm_os_child_instances
sys.dm_os_nodes
sys.dm_os_cluster_nodes
sys.dm_os_performance_counters
sys.dm_os_dispatcher_pools
sys.dm_os_process_memory
sys.dm_os_hosts
sys.dm_os_schedulers
sys.dm_os_latch_stats
sys.dm_os_stacks
sys.dm_os_loaded_modules
sys.dm_os_sys_info
sys.dm_os_memory_brokers
sys.dm_os_sys_memory
sys.dm_os_memory_cache_clock_hands
sys.dm_os_tasks
sys.dm_os_memory_cache_counters
sys.dm_os_threads
sys.dm_os_memory_cache_entries
sys.dm_os_virtual_address_dump
sys.dm_os_memory_cache_hash_tables
sys.dm_os_wait_stats
sys.dm_os_memory_clerks
sys.dm_os_waiting_tasks
sys.dm_os_memory_nodes
sys.dm_os_workers
sys.dm_os_memory_objects

Database views are located at User Database ->views -> DM_DB_XXX , DM_Exec_XXX etc

sys.dm_exec_background_job_queue
sys.dm_exec_query_optimizer_info
sys.dm_exec_background_job_queue
sys.dm_exec_query_plan
sys.dm_exec_background_job_queue_stats
sys.dm_exec_query_resource_semaphores
sys.dm_exec_cached_plans
sys.dm_exec_query_stats
sys.dm_exec_cached_plan_dependent_objects
sys.dm_exec_requests
sys.dm_exec_connections
sys.dm_exec_sessions
sys.dm_exec_cursors
sys.dm_exec_sql_text
sys.dm_exec_plan_attributes
sys.dm_exec_text_query_plan
sys.dm_exec_procedure_stats
sys.dm_exec_trigger_stats
sys.dm_exec_query_memory_grants
sys.dm_exec_xml_handles

sys.dm_db_file_space_usage
sys.dm_db_session_space_usage
sys.dm_db_partition_stats
sys.dm_db_task_space_usage
sys.dm_db_persisted_sku_features

Here is a situation – users are complaining about a slow SQL server response.

We want to quantify if that is result of a hard hitting SQL query or this is to do with some application/background jobs running on the server.

Let’s put to practise our skills from http://www.oracledbasupport.co.uk/troubleshooting-bottlenecks-using-sql-server-profiler-part-iv/ & http://www.oracledbasupport.co.uk/troubleshooting-bottlenecks-using-windows-system-monitor-part-i/

Please initiate a Trace on SQL server before going ahead. Within a SQL server Profiler window click on the top button for “Performancve Monitor” to enable logging.

Read more…

1. Windows System Monitor – PART I
2. DBCC commands – PART II
3. Dynamic Memory Views – PART III
4. SQL Server Profiler – – PART IV

SQL Server Profiler – – PART IV

Read more…

1. Windows System Monitor – PART I
2. DBCC commands – PART II
3. Dynamic Memory Views – PART III
4. SQL Server Profiler – – PART IV

Windows System Monitor

Read more…

Backup Types

There are 5 backup types. All these backups can be carried out online. databases. Offline backups require SQLSERVER be stopped. Offline backups are useful for the master, msdb and model databases

1. Full : This takes a complete copy of the database and logs.

2. Differential : This takes a copy of all changed pages since the last full backup (for database and logs.)

3. Transaction Log : This takes a copy of the transactions logs and clears its contents on completion.

4. File or File group : This copies only the specified file or group.

5. File differential : This takes a copy of all changed pages in file since the last file backup. In situations when the backup is too big for a backup window. Read more…

Maintenance Plans are only displayed to those users who are connected to the SQL Server using Windows Authentication Mode.

I will use AdventureWorksDW database which uses Simple Recovery Model and hence transactional log backups is not possible.

We first need to change the Recovery Model of AdventureWorks database to Full using GUI/TSQL.

Use master
GO

ALTER DATABASE AdventureWorks
SET RECOVERY FULL
GO

1. Connect to SQL Server 2008 Instance using SQL Server Management Studio.

2. In the Object Explorer, Click on Management and right click Maintenance Plans to open up the popup windows. In the popup windows you need to select Maintenance Plan Wizard.

In the Select Maintenance Tasks screen you need to choose Back up Databases (Full), Backup Databases (Differential) and Back up Database (Transactional Log) as shown in the below snippet and then click Next to continue with the wizard.

This will pop up a screen to Select a database you want to backup.

Then you need to select the option Create a backup file for every database, next select the checkbox, Create a sub-directory for each database checkbox and then provide the folder location where you want the databases backups to be stored along with the file extension as “diff”. If you are interested in verifying backup integrity then you can select Verify backup integrity option.

Click on “Change” to schedulethe the job & timing

Define where you want to store log files on the server.

In Complete the Wizard screen you could see the summary of all the options which you have selected so far in the maintenance plan wizard,

In the Maintenance Plan Wizard Progress screen you need to make sure that all the tasks have completed successfully and then click Close to complete the wizard.

If you want to run the backups manually just navigate to SQL Serevr Agent -> Job Activity Monitor . Right click on the job and select “Start Job”

I can now see a successful backup fie at “D:\SQL_TRAN_LOG\backups\AdventureWorksDW”

Prerequisite :

• 2 separate SQL database instances at 2 different physical boxes – T-B0399254ED744 & TVLX4686.
• A share accessible to Primary and Secondary Server for log shipping

Here I am going to log replicate AdventureWorks database from T-B0399254ED744 to TVLX4686

Read more…