Speak guys!
In this article I would like to share with you a number of useful DBA day-to-day scripts that you always have to look for on the Internet when you need to make a particular query. My idea here is to make your life easier and have a multi-purpose article for many different purposes for you to bookmark in your browser and always have the information you want here 🙂
No more tangling, let's get down to business!
General information
View scriptsDatabases and Settings Overview
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | SELECT CONVERT(VARCHAR(25), DB.name) AS dbName, state_desc, ( SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows' ) AS DataFiles, ( SELECT SUM(( size * 8 ) / 1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows' ) AS [Data MB], ( SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log' ) AS LogFiles, ( SELECT SUM(( size * 8 ) / 1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log' ) AS [Log MB], recovery_model_desc AS [Recovery model], CASE [compatibility_level] WHEN 60 THEN '60 (SQL Server 6.0)' WHEN 65 THEN '65 (SQL Server 6.5)' WHEN 70 THEN '70 (SQL Server 7.0)' WHEN 80 THEN '80 (SQL Server 2000)' WHEN 90 THEN '90 (SQL Server 2005)' WHEN 100 THEN '100 (SQL Server 2008)' WHEN 110 THEN '110 (SQL Server 2012)' WHEN 120 THEN '120 (SQL Server 2014)' WHEN 130 THEN '130 (SQL Server 2016)' WHEN 140 THEN '140 (SQL Server 2017)' WHEN 150 THEN '150 (SQL Server 2019)' END AS [compatibility level], CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date], -- last backup ISNULL( ( SELECT TOP 1 CASE type WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' + LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(), backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' + CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' + CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) + ' (' + CAST(DATEDIFF(SECOND, BK.backup_start_date, BK.backup_finish_date) AS VARCHAR(4)) + ' ' + 'seconds)' FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC ), '-' ) AS [Last backup], CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose], page_verify_option_desc AS [page verify option], CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink], CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto create statistics], CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto update statistics], DB.delayed_durability_desc, DB.is_parameterization_forced, DB.user_access_desc, DB.snapshot_isolation_state_desc, DB.is_read_only, DB.is_trustworthy_on, DB.is_encrypted, DB.is_query_store_on, DB.is_cdc_enabled, DB.is_remote_data_archive_enabled, DB.is_subscribed, DB.is_merge_published FROM sys.databases DB ORDER BY 6 DESC; |
Queries that are currently running
To learn more about this routine, see my article. SQL Server - Query to return running queries (sp_WhoIsActive without consuming TempDB) or SQL Server - Using SP WhoIsActive to identify Locks, Blocks, Slow Queries, Running Queries, and more.
SQL Server version 2012 +
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 | SELECT RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 86400 AS VARCHAR), 2) + ' ' + RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 3600) % 24 AS VARCHAR), 2) + ':' + RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 60) % 60 AS VARCHAR), 2) + ':' + RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) % 60 AS VARCHAR), 2) + '.' + RIGHT('000' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) AS VARCHAR), 3) AS Duration, A.session_id AS session_id, B.command, TRY_CAST('<?query --' + CHAR(10) + ( SELECT TOP 1 SUBSTRING(X.[text], B.statement_start_offset / 2 + 1, ((CASE WHEN B.statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), X.[text])) * 2) ELSE B.statement_end_offset END ) - B.statement_start_offset ) / 2 + 1 ) ) + CHAR(10) + '--?>' AS XML) AS sql_text, TRY_CAST('<?query --' + CHAR(10) + X.[text] + CHAR(10) + '--?>' AS XML) AS sql_command, A.login_name, '(' + CAST(COALESCE(E.wait_duration_ms, B.wait_time) AS VARCHAR(20)) + 'ms)' + COALESCE(E.wait_type, B.wait_type) + COALESCE((CASE WHEN COALESCE(E.wait_type, B.wait_type) LIKE 'PAGE%LATCH%' THEN ':' + DB_NAME(LEFT(E.resource_description, CHARINDEX(':', E.resource_description) - 1)) + ':' + SUBSTRING(E.resource_description, CHARINDEX(':', E.resource_description) + 1, 999) WHEN COALESCE(E.wait_type, B.wait_type) = 'OLEDB' THEN '[' + REPLACE(REPLACE(E.resource_description, ' (SPID=', ':'), ')', '') + ']' ELSE '' END), '') AS wait_info, FORMAT(COALESCE(B.cpu_time, 0), '###,###,###,###,###,###,###,##0') AS CPU, FORMAT(COALESCE(F.tempdb_allocations, 0), '###,###,###,###,###,###,###,##0') AS tempdb_allocations, FORMAT(COALESCE((CASE WHEN F.tempdb_allocations > F.tempdb_current THEN F.tempdb_allocations - F.tempdb_current ELSE 0 END), 0), '###,###,###,###,###,###,###,##0') AS tempdb_current, FORMAT(COALESCE(B.logical_reads, 0), '###,###,###,###,###,###,###,##0') AS reads, FORMAT(COALESCE(B.writes, 0), '###,###,###,###,###,###,###,##0') AS writes, FORMAT(COALESCE(B.reads, 0), '###,###,###,###,###,###,###,##0') AS physical_reads, FORMAT(COALESCE(B.granted_query_memory, 0), '###,###,###,###,###,###,###,##0') AS used_memory, NULLIF(B.blocking_session_id, 0) AS blocking_session_id, COALESCE(G.blocked_session_count, 0) AS blocked_session_count, 'KILL ' + CAST(A.session_id AS VARCHAR(10)) AS kill_command, (CASE WHEN B.[deadlock_priority] <= -5 THEN 'Low' WHEN B.[deadlock_priority] > -5 AND B.[deadlock_priority] < 5 AND B.[deadlock_priority] < 5 THEN 'Normal' WHEN B.[deadlock_priority] >= 5 THEN 'High' END) + ' (' + CAST(B.[deadlock_priority] AS VARCHAR(3)) + ')' AS [deadlock_priority], B.row_count, COALESCE(A.open_transaction_count, 0) AS open_tran_count, (CASE B.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncommitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END) AS transaction_isolation_level, A.[status], NULLIF(B.percent_complete, 0) AS percent_complete, A.[host_name], COALESCE(DB_NAME(CAST(B.database_id AS VARCHAR)), 'master') AS [database_name], (CASE WHEN D.name IS NOT NULL THEN 'SQLAgent - TSQL Job (' + D.[name] + ' - ' + SUBSTRING(A.[program_name], 67, LEN(A.[program_name]) - 67) + ')' ELSE A.[program_name] END) AS [program_name], H.[name] AS resource_governor_group, COALESCE(B.start_time, A.last_request_end_time) AS start_time, A.login_time, COALESCE(B.request_id, 0) AS request_id, W.query_plan FROM sys.dm_exec_sessions AS A WITH (NOLOCK) LEFT JOIN sys.dm_exec_requests AS B WITH (NOLOCK) ON A.session_id = B.session_id JOIN sys.dm_exec_connections AS C WITH (NOLOCK) ON A.session_id = C.session_id AND A.endpoint_id = C.endpoint_id LEFT JOIN msdb.dbo.sysjobs AS D ON RIGHT(D.job_id, 10) = RIGHT(SUBSTRING(A.[program_name], 30, 34), 10) LEFT JOIN ( SELECT session_id, wait_type, wait_duration_ms, resource_description, ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY (CASE WHEN wait_type LIKE 'PAGE%LATCH%' THEN 0 ELSE 1 END), wait_duration_ms) AS Ranking FROM sys.dm_os_waiting_tasks ) E ON A.session_id = E.session_id AND E.Ranking = 1 LEFT JOIN ( SELECT session_id, request_id, SUM(internal_objects_alloc_page_count + user_objects_alloc_page_count) AS tempdb_allocations, SUM(internal_objects_dealloc_page_count + user_objects_dealloc_page_count) AS tempdb_current FROM sys.dm_db_task_space_usage GROUP BY session_id, request_id ) F ON B.session_id = F.session_id AND B.request_id = F.request_id LEFT JOIN ( SELECT blocking_session_id, COUNT(*) AS blocked_session_count FROM sys.dm_exec_requests WHERE blocking_session_id != 0 GROUP BY blocking_session_id ) G ON A.session_id = G.blocking_session_id OUTER APPLY sys.dm_exec_sql_text(COALESCE(B.[sql_handle], C.most_recent_sql_handle)) AS X OUTER APPLY sys.dm_exec_query_plan(B.plan_handle) AS W LEFT JOIN sys.dm_resource_governor_workload_groups H ON A.group_id = H.group_id WHERE A.session_id > 50 AND A.session_id <> @@SPID AND (A.[status] != 'sleeping' OR (A.[status] = 'sleeping' AND A.open_transaction_count > 0)) |
SQL Server version 2008 and earlier
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 | SELECT RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 86400 AS VARCHAR), 2) + ' ' + RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 3600) % 24 AS VARCHAR), 2) + ':' + RIGHT('00' + CAST((DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) / 60) % 60 AS VARCHAR), 2) + ':' + RIGHT('00' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) % 60 AS VARCHAR), 2) + '.' + RIGHT('000' + CAST(DATEDIFF(SECOND, COALESCE(B.start_time, A.login_time), GETDATE()) AS VARCHAR), 3) AS Duration, A.session_id AS session_id, B.command, CAST('<?query --' + CHAR(10) + ( SELECT TOP 1 SUBSTRING(X.[text], B.statement_start_offset / 2 + 1, ((CASE WHEN B.statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), X.[text])) * 2) ELSE B.statement_end_offset END ) - B.statement_start_offset ) / 2 + 1 ) ) + CHAR(10) + '--?>' AS XML) AS sql_text, CAST('<?query --' + CHAR(10) + X.[text] + CHAR(10) + '--?>' AS XML) AS sql_command, A.login_name, '(' + CAST(COALESCE(E.wait_duration_ms, B.wait_time) AS VARCHAR(20)) + 'ms)' + COALESCE(E.wait_type, B.wait_type) + COALESCE((CASE WHEN COALESCE(E.wait_type, B.wait_type) LIKE 'PAGEIOLATCH%' THEN ':' + DB_NAME(LEFT(E.resource_description, CHARINDEX(':', E.resource_description) - 1)) + ':' + SUBSTRING(E.resource_description, CHARINDEX(':', E.resource_description) + 1, 999) WHEN COALESCE(E.wait_type, B.wait_type) = 'OLEDB' THEN '[' + REPLACE(REPLACE(E.resource_description, ' (SPID=', ':'), ')', '') + ']' ELSE '' END), '') AS wait_info, COALESCE(B.cpu_time, 0) AS CPU, COALESCE(F.tempdb_allocations, 0) AS tempdb_allocations, COALESCE((CASE WHEN F.tempdb_allocations > F.tempdb_current THEN F.tempdb_allocations - F.tempdb_current ELSE 0 END), 0) AS tempdb_current, COALESCE(B.logical_reads, 0) AS reads, COALESCE(B.writes, 0) AS writes, COALESCE(B.reads, 0) AS physical_reads, COALESCE(B.granted_query_memory, 0) AS used_memory, NULLIF(B.blocking_session_id, 0) AS blocking_session_id, COALESCE(G.blocked_session_count, 0) AS blocked_session_count, 'KILL ' + CAST(A.session_id AS VARCHAR(10)) AS kill_command, (CASE WHEN B.[deadlock_priority] <= -5 THEN 'Low' WHEN B.[deadlock_priority] > -5 AND B.[deadlock_priority] < 5 AND B.[deadlock_priority] < 5 THEN 'Normal' WHEN B.[deadlock_priority] >= 5 THEN 'High' END) + ' (' + CAST(B.[deadlock_priority] AS VARCHAR(3)) + ')' AS [deadlock_priority], B.row_count, B.open_transaction_count, (CASE B.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncommitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END) AS transaction_isolation_level, A.[status], NULLIF(B.percent_complete, 0) AS percent_complete, A.[host_name], COALESCE(DB_NAME(CAST(B.database_id AS VARCHAR)), 'master') AS [database_name], A.[program_name], H.[name] AS resource_governor_group, COALESCE(B.start_time, A.last_request_end_time) AS start_time, A.login_time, COALESCE(B.request_id, 0) AS request_id, W.query_plan FROM sys.dm_exec_sessions AS A WITH (NOLOCK) LEFT JOIN sys.dm_exec_requests AS B WITH (NOLOCK) ON A.session_id = B.session_id JOIN sys.dm_exec_connections AS C WITH (NOLOCK) ON A.session_id = C.session_id AND A.endpoint_id = C.endpoint_id LEFT JOIN ( SELECT session_id, wait_type, wait_duration_ms, resource_description, ROW_NUMBER() OVER(PARTITION BY session_id ORDER BY (CASE WHEN wait_type LIKE 'PAGEIO%' THEN 0 ELSE 1 END), wait_duration_ms) AS Ranking FROM sys.dm_os_waiting_tasks ) E ON A.session_id = E.session_id AND E.Ranking = 1 LEFT JOIN ( SELECT session_id, request_id, SUM(internal_objects_alloc_page_count + user_objects_alloc_page_count) AS tempdb_allocations, SUM(internal_objects_dealloc_page_count + user_objects_dealloc_page_count) AS tempdb_current FROM sys.dm_db_task_space_usage GROUP BY session_id, request_id ) F ON B.session_id = F.session_id AND B.request_id = F.request_id LEFT JOIN ( SELECT blocking_session_id, COUNT(*) AS blocked_session_count FROM sys.dm_exec_requests WHERE blocking_session_id != 0 GROUP BY blocking_session_id ) G ON A.session_id = G.blocking_session_id OUTER APPLY sys.dm_exec_sql_text(COALESCE(B.[sql_handle], C.most_recent_sql_handle)) AS X OUTER APPLY sys.dm_exec_query_plan(B.[plan_handle]) AS W LEFT JOIN sys.dm_resource_governor_workload_groups H ON A.group_id = H.group_id WHERE A.session_id > 50 AND A.session_id <> @@SPID AND (A.[status] != 'sleeping' OR (A.[status] = 'sleeping' AND B.open_transaction_count > 0)) |
Check the main instance parameters
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SELECT [name], [value], [description] FROM sys.configurations WHERE [name] IN ( 'max degree of parallelism', 'cost threshold for parallelism', 'min server memory (MB)', 'max server memory (MB)', 'clr enabled', 'xp_cmdshell', 'Ole Automation Procedures', 'user connections', 'fill factor (%)', 'cross db ownership chaining', 'remote access', 'default trace enabled', 'external scripts enabled', 'Database Mail XPs', 'Ad Hoc Distributed Queries', 'SMO and DMO XPs', 'clr strict security', 'remote admin connections' ) ORDER BY [name] |
Identifies the transaction log usage of each database in the instance (equivalent to DBCC SQLPERF (LOGSPACE))
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | SELECT RTRIM(A.instance_name) AS [Database Name], A.cntr_value / 1024.0 AS [Log Size (MB)], CAST(B.cntr_value * 100.0 / A.cntr_value AS DEC(18, 5)) AS [Log Space Used (%)] FROM sys.dm_os_performance_counters A JOIN sys.dm_os_performance_counters B ON A.instance_name = B.instance_name WHERE A.[object_name] LIKE '%Databases%' AND B.[object_name] LIKE '%Databases%' AND A.counter_name = 'Log File(s) Size (KB)' AND B.counter_name = 'Log File(s) Used Size (KB)' AND A.instance_name NOT IN ( '_Total', 'mssqlsystemresource' ) AND A.cntr_value > 0 |
Identify and try to fix orphaned users
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | SELECT A.[name], A.[sid], (CASE WHEN C.principal_id IS NULL THEN NULL -- Não tem o que fazer.. Login correspondente não existe ELSE 'ALTER USER [' + A.[name] + '] WITH LOGIN = [' + C.[name] + ']' -- Tenta corrigir o usuário órfão END) AS command FROM sys.database_principals A WITH(NOLOCK) LEFT JOIN sys.sql_logins B WITH(NOLOCK) ON A.[sid] = B.[sid] LEFT JOIN sys.server_principals C WITH(NOLOCK) ON (A.[name] COLLATE SQL_Latin1_General_CP1_CI_AI = C.[name] COLLATE SQL_Latin1_General_CP1_CI_AI OR A.[sid] = C.[sid]) AND C.is_fixed_role = 0 AND C.[type_desc] = 'SQL_LOGIN' WHERE A.principal_id > 4 AND B.[sid] IS NULL AND A.is_fixed_role = 0 AND A.[type_desc] = 'SQL_USER' AND A.authentication_type <> 0 -- NONE ORDER BY A.[name] |
Identifies when the SQL Server service started
Using the sys.dm_os_sys_info DMV we can identify the date the SQL Server service was started. Recommended reading to supplement this information: SQL Server - How to find out how long the instance has been online or when the instance was started
1 | SELECT sqlserver_start_time FROM sys.dm_os_sys_info |
Identifies DDL and DCL operations performed on the instance.
Using Default Trace, we can identify DDL (ALTER, CREATE, DROP) and DCL (GRANT, DENY, REVOKE) operations performed on the instance. To better understand this feature, I recommend reading the article. Monitoring DDL and DCL Operations Using SQL Server fn_trace_gettable.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | DECLARE @Ds_Arquivo_Trace VARCHAR(255) = (SELECT SUBSTRING([path], 0, LEN([path])-CHARINDEX('\', REVERSE([path]))+1) + '\Log.trc' FROM sys.traces WHERE is_default = 1) SELECT A.HostName, A.ApplicationName, A.NTUserName, A.NTDomainName, A.LoginName, A.SPID, A.EventClass, B.name, A.EventSubClass, A.TextData, A.StartTime, A.DatabaseName, A.ObjectID, A.ObjectName, A.TargetLoginName, A.TargetUserName FROM [fn_trace_gettable](@Ds_Arquivo_Trace, DEFAULT) A JOIN master.sys.trace_events B ON A.EventClass = B.trace_event_id WHERE A.EventClass IN ( 164, 46, 47, 108, 110, 152 ) AND A.StartTime >= GETDATE()-7 AND A.LoginName NOT IN ( 'NT AUTHORITY\NETWORK SERVICE' ) AND A.LoginName NOT LIKE '%SQLTELEMETRY$%' AND A.DatabaseName <> 'tempdb' AND NOT (B.name LIKE 'Object:%' AND A.ObjectName IS NULL ) AND A.ObjectName <> 'telemetry_xevents' AND NOT (A.ApplicationName LIKE 'Red Gate%' OR A.ApplicationName LIKE '%Intellisense%' OR A.ApplicationName = 'DacFx Deploy') ORDER BY StartTime DESC |
Identifies when backups were generated or restored
Using the default trace, we can identify the occurrence of BACKUP and RESTORE commands in the instance. For a better understanding of this script, I suggest reading the article. Using SQL Server Standard Trace to Audit Events (fn_trace_gettable).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1) SELECT TextData, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName FROM sys.fn_trace_gettable(@path, DEFAULT) WHERE EventClass IN ( 115 ) ORDER BY StartTime DESC |
Identify auto growth events
Using the default trace, we were able to identify the occurrence of autogrowth events in the instance, that is, when SQL Server dynamically allocated more file space as it was needed to allocate new data. For a better understanding of this script, I suggest reading the article. Monitoring Autogrowth Events in Databases in SQL Server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | DECLARE @Ds_Arquivo_Trace VARCHAR(500) = (SELECT [path] FROM sys.traces WHERE is_default = 1) DECLARE @Index INT = PATINDEX('%\%', REVERSE(@Ds_Arquivo_Trace)) DECLARE @Nm_Arquivo_Trace VARCHAR(500) = LEFT(@Ds_Arquivo_Trace, LEN(@Ds_Arquivo_Trace) - @Index) + '\log.trc' SELECT A.DatabaseName, A.[Filename], ( A.Duration / 1000 ) AS 'Duration_ms', A.StartTime, A.EndTime, ( A.IntegerData * 8.0 / 1024 ) AS 'GrowthSize_MB', A.ApplicationName, A.HostName, A.LoginName FROM ::fn_trace_gettable(@Nm_Arquivo_Trace, DEFAULT) A WHERE A.EventClass >= 92 AND A.EventClass <= 95 AND A.ServerName = @@servername ORDER BY A.StartTime DESC |
Identify DBCC Command Execution
Using the default trace, we can identify the occurrence of DBCC commands executed in the instance, such as CHECKDB. For a better understanding of this script, I suggest reading the article. Using SQL Server Standard Trace to Audit Events (fn_trace_gettable).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | DECLARE @path VARCHAR(MAX) = (SELECT [path] FROM sys.traces WHERE is_default = 1) SELECT TextData, Duration, StartTime, EndTime, SPID, ApplicationName, LoginName FROM sys.fn_trace_gettable(@path, DEFAULT) WHERE EventClass IN ( 116 ) ORDER BY StartTime DESC |
Tables and Indexes
In this session I will share some scripts related to queries on tables and indexes, such as identifying HEAP tables, fragmented indexes, etc.
Recommended reading: Performance Tuning Series
Identify uncompressed tables and indexes
Using the query below, we will identify the tables and indexes in RowStore mode that are not using data compression. Keep in mind that until 2016, this feature was unique to the Enterprise version (in addition to Trial and Developer), so if your version is inferred from 2016 and Standard edition, this script will not be useful for you. To better understand what data compression is, I suggest you read the article Compressing all tables in a database in SQL Server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | SELECT DISTINCT C.[name] AS [Schema], A.[name] AS Tabela, NULL AS Indice, 'ALTER TABLE [' + C.[name] + '].[' + A.[name] + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)' AS Comando FROM sys.tables A INNER JOIN sys.partitions B ON A.[object_id] = B.[object_id] INNER JOIN sys.schemas C ON A.[schema_id] = C.[schema_id] WHERE B.data_compression_desc = 'NONE' AND B.index_id = 0 -- HEAP AND A.[type] = 'U' UNION SELECT DISTINCT C.[name] AS [Schema], B.[name] AS Tabela, A.[name] AS Indice, 'ALTER INDEX [' + A.[name] + '] ON [' + C.[name] + '].[' + B.[name] + '] REBUILD PARTITION = ALL WITH ( STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = PAGE)' FROM sys.indexes A INNER JOIN sys.tables B ON A.[object_id] = B.[object_id] INNER JOIN sys.schemas C ON B.[schema_id] = C.[schema_id] INNER JOIN sys.partitions D ON A.[object_id] = D.[object_id] AND A.index_id = D.index_id WHERE D.data_compression_desc = 'NONE' AND D.index_id <> 0 AND A.[type] IN (1, 2) -- CLUSTERED e NONCLUSTERED (Rowstore) AND B.[type] = 'U' ORDER BY Tabela, Indice |
Identify Index Fragmentation
To identify the level of fragmentation of the indexes and to assess whether a REORGANIZE or REBUILD is required, use the script below. To better understand the structure of indexes and what fragmentation is, read the article. Understanding Index Functioning in SQL Server.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SELECT C.[name] AS TableName, B.[name] AS IndexName, A.index_type_desc AS IndexType, A.avg_fragmentation_in_percent, 'ALTER INDEX [' + B.[name] + '] ON [' + D.[name] + '].[' + C.[name] + '] REBUILD' AS CmdRebuild FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') A JOIN sys.indexes B ON B.[object_id] = A.[object_id] AND B.index_id = A.index_id JOIN sys.objects C ON B.[object_id] = C.[object_id] JOIN sys.schemas D ON D.[schema_id] = C.[schema_id] WHERE A.avg_fragmentation_in_percent > 30 AND OBJECT_NAME(B.[object_id]) NOT LIKE '[_]%' AND A.index_type_desc != 'HEAP' ORDER BY A.avg_fragmentation_in_percent DESC |
Identify HEAP tables (without clustered index)
Using the query below, you will be able to identify tables that do not have a clustered index created, which can almost always represent a potential query performance issue, as data will not be sorted and the use of only non-clustered indexes can end up generating many Key Lookup events.
To better understand the structure of indexes and what a clustered index is, read the article. Understanding Index Functioning in SQL Server.
1 2 3 4 5 6 7 8 9 10 | SELECT B.[name] + '.' + A.[name] AS table_name FROM sys.tables A JOIN sys.schemas B ON A.[schema_id] = B.[schema_id] JOIN sys.indexes C ON A.[object_id] = C.[object_id] WHERE C.[type] = 0 -- = Heap ORDER BY table_name |
Using Indexes
With the query below, you will be able to identify if the indexes created are being used correctly. It is also useful for identifying indexes that may be good candidates for deletion, as they are just taking up space, and it also helps you identify tables that are widely accessed and not accessed for a long time.
To better understand performance tuning and understand what is Seek operation, Scan, etc. read the article SQL Server - Introduction to Performance Tuning Study.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 | SELECT D.[name] + '.' + C.[name] AS ObjectName, A.[name] AS IndexName, (CASE WHEN A.is_unique = 1 THEN 'UNIQUE ' ELSE '' END) + A.[type_desc] AS IndexType, MAX(B.last_user_seek) AS last_user_seek, MAX(COALESCE(B.last_user_seek, B.last_user_scan)) AS last_read, SUM(B.user_seeks) AS User_Seeks, SUM(B.user_scans) AS User_Scans, SUM(B.user_seeks) + SUM(B.user_scans) AS User_Reads, SUM(B.user_lookups) AS User_Lookups, SUM(B.user_updates) AS User_Updates, SUM(E.[rows]) AS [row_count], CAST(ROUND(((SUM(F.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [size_mb], CAST(ROUND(((SUM(F.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS [used_mb], CAST(ROUND(((SUM(F.total_pages) - SUM(F.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS [unused_mb] FROM sys.indexes A LEFT JOIN sys.dm_db_index_usage_stats B ON A.[object_id] = B.[object_id] AND A.index_id = B.index_id AND B.database_id = DB_ID() JOIN sys.objects C ON A.[object_id] = C.[object_id] JOIN sys.schemas D ON C.[schema_id] = D.[schema_id] JOIN sys.partitions E ON A.[object_id] = E.[object_id] AND A.index_id = E.index_id JOIN sys.allocation_units F ON E.[partition_id] = F.container_id WHERE C.is_ms_shipped = 0 GROUP BY D.[name] + '.' + C.[name], A.[name], (CASE WHEN A.is_unique = 1 THEN 'UNIQUE ' ELSE '' END) + A.[type_desc] ORDER BY 1, 2 |
Identifying Possible Good Clustered Index Candidates
With the query below, I will help you identify possible better clustered index candidates than the current ones. The metric to identify this is through the number of seek reads between nonclustered and clustered indexes. Remember that this is just to give you an idea of the best candidate for the clustered index. You should not only rely on this query, do your analysis!
To better understand the structure of indexes and what a clustered index is, read the article. Understanding Index Functioning in SQL Server. To better understand performance tuning and understand what is Seek operation, Scan, etc. read the article SQL Server - Introduction to Performance Tuning Study.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | SELECT B.[name] AS table_name, idx.[name] AS clustered_index, nc.nonclusteredname AS best_non_clustered, c.user_seeks AS clustered_user_seeks, nc.user_seeks AS nonclustered_user_seeks, c.user_lookups AS clustered_user_lookups FROM sys.indexes idx JOIN sys.objects B ON idx.[object_id] = B.[object_id] LEFT JOIN sys.dm_db_index_usage_stats c ON idx.[object_id] = c.[object_id] AND idx.index_id = c.index_id AND c.database_id = DB_ID() JOIN ( SELECT idx.[object_id], idx.[name] AS nonclusteredname, ius.user_seeks FROM sys.indexes idx JOIN sys.dm_db_index_usage_stats ius ON idx.[object_id] = ius.[object_id] AND idx.index_id = ius.index_id WHERE idx.[type_desc] = 'nonclustered' AND ius.user_seeks = ( SELECT MAX(user_seeks) FROM sys.dm_db_index_usage_stats WHERE [object_id] = ius.[object_id] AND [type_desc] = 'nonclustered' AND database_id = DB_ID() ) AND ius.database_id = DB_ID() GROUP BY idx.[object_id], idx.[name], ius.user_seeks ) nc ON nc.[object_id] = idx.[object_id] WHERE idx.[type_desc] IN ( 'clustered', 'heap' ) AND nc.user_seeks |