Last active
September 23, 2020 07:47
-
-
Save PeterOsinski/2f1d34c66c5d4aa8d4933a08ac49e4b7 to your computer and use it in GitHub Desktop.
SQL Server snippets (SSS)
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- Source | |
| -- https://www.sqlshack.com/gathering-sql-server-indexes-statistics-and-usage-information/ | |
| -- https://www.mssqltips.com/sqlservertutorial/256/discovering-unused-indexes/ | |
| -- https://www.mssqltips.com/sqlservertip/1239/how-to-get-index-usage-information-in-sql-server/ | |
| -- https://www.sqlshack.com/how-to-identify-and-monitor-unused-indexes-in-sql-server/ | |
| -- List indexes on a table | |
| EXEC sp_helpindex '--table name here--' | |
| -- Top 50 queries run on database | |
| SELECT TOP 50 dest.TEXT AS [Query], | |
| deqs.execution_count [Count], | |
| deqs.last_execution_time AS [Time], | |
| deqs.min_elapsed_time / 1000 AS [MinDuration], | |
| deqs.max_elapsed_time / 1000 AS [MaxDuration] | |
| FROM sys.dm_exec_query_stats AS deqs | |
| CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest | |
| ORDER BY deqs.max_elapsed_time DESC | |
| -- Index fragmentation info | |
| SELECT OBJECT_NAME(IDX.OBJECT_ID) AS Table_Name, | |
| IDX.name AS Index_Name, | |
| IDXPS.index_type_desc AS Index_Type, | |
| IDXPS.avg_fragmentation_in_percent Fragmentation_Percentage | |
| FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) IDXPS | |
| INNER JOIN sys.indexes IDX ON IDX.object_id = IDXPS.object_id | |
| AND IDX.index_id = IDXPS.index_id | |
| ORDER BY Fragmentation_Percentage DESC | |
| -- Show currently running queries | |
| SELECT r.start_time [Start Time],session_ID [SPID], | |
| DB_NAME(database_id) [Database], | |
| SUBSTRING(t.text,(r.statement_start_offset/2)+1, | |
| CASE WHEN statement_end_offset=-1 OR statement_end_offset=0 | |
| THEN (DATALENGTH(t.Text)-r.statement_start_offset/2)+1 | |
| ELSE (r.statement_end_offset-r.statement_start_offset)/2+1 | |
| END) [Executing SQL], | |
| Status,command,wait_type,wait_time,wait_resource, | |
| last_wait_type | |
| FROM sys.dm_exec_requests r | |
| OUTER APPLY sys.dm_exec_sql_text(sql_handle) t | |
| WHERE session_id != @@SPID -- don't show this query | |
| AND session_id > 50 -- don't show system queries | |
| ORDER BY r.start_time | |
| -- Index usage info | |
| SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name | |
| ,IX.name AS Index_Name | |
| ,IX.type_desc Index_Type | |
| ,SUM(PS.[used_page_count]) * 8 IndexSizeKB | |
| ,IXUS.user_seeks AS NumOfSeeks | |
| ,IXUS.user_scans AS NumOfScans | |
| ,IXUS.user_lookups AS NumOfLookups | |
| ,IXUS.user_updates AS NumOfUpdates | |
| ,IXUS.last_user_seek AS LastSeek | |
| ,IXUS.last_user_scan AS LastScan | |
| ,IXUS.last_user_lookup AS LastLookup | |
| ,IXUS.last_user_update AS LastUpdate | |
| FROM sys.indexes IX | |
| INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID | |
| INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id | |
| WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1 | |
| GROUP BY OBJECT_NAME(IX.OBJECT_ID) ,IX.name ,IX.type_desc ,IXUS.user_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.user_updates ,IXUS.last_user_seek ,IXUS.last_user_scan ,IXUS.last_user_lookup ,IXUS.last_user_update | |
| -- Index IO (insert/update) operations per index | |
| SELECT OBJECT_NAME(IXOS.OBJECT_ID) Table_Name | |
| ,IX.name Index_Name | |
| ,IX.type_desc Index_Type | |
| ,SUM(PS.[used_page_count]) * 8 IndexSizeKB | |
| ,IXOS.LEAF_INSERT_COUNT NumOfInserts | |
| ,IXOS.LEAF_UPDATE_COUNT NumOfupdates | |
| ,IXOS.LEAF_DELETE_COUNT NumOfDeletes | |
| FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) IXOS | |
| INNER JOIN SYS.INDEXES AS IX ON IX.OBJECT_ID = IXOS.OBJECT_ID AND IX.INDEX_ID = IXOS.INDEX_ID | |
| INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id | |
| WHERE OBJECTPROPERTY(IX.[OBJECT_ID],'IsUserTable') = 1 | |
| GROUP BY OBJECT_NAME(IXOS.OBJECT_ID), IX.name, IX.type_desc,IXOS.LEAF_INSERT_COUNT, IXOS.LEAF_UPDATE_COUNT,IXOS.LEAF_DELETE_COUNT | |
| -- Index CRUD operations report | |
| SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME], | |
| I.[NAME] AS [INDEX NAME], | |
| A.LEAF_INSERT_COUNT, | |
| A.LEAF_UPDATE_COUNT, | |
| A.LEAF_DELETE_COUNT | |
| FROM SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A | |
| INNER JOIN SYS.INDEXES AS I | |
| ON I.[OBJECT_ID] = A.[OBJECT_ID] | |
| AND I.INDEX_ID = A.INDEX_ID | |
| WHERE OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1 | |
| -- Index simple usage info | |
| -- user_seeks - number of index seeks | |
| -- user_scans- number of index scans | |
| -- user_lookups - number of index lookups | |
| -- user_updates - number of insert, update or delete operations | |
| SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], | |
| I.[NAME] AS [INDEX NAME], | |
| USER_SEEKS, | |
| USER_SCANS, | |
| USER_LOOKUPS, | |
| USER_UPDATES | |
| FROM SYS.DM_DB_INDEX_USAGE_STATS AS S | |
| INNER JOIN SYS.INDEXES AS I | |
| ON I.[OBJECT_ID] = S.[OBJECT_ID] | |
| AND I.INDEX_ID = S.INDEX_ID | |
| WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 | |
| -- List unused indexes that are candidates to removal | |
| -- it outputs the commands need to be run on database in order | |
| -- to drop the indexes. It doesn't drop those indexes automatically | |
| SELECT 'DROP INDEX '+OBJECT_NAME(dm_db_index_usage_stats.object_id)+'.'+indexes.name AS Drop_Index, user_seeks, user_scans, user_lookups, user_updates | |
| FROM | |
| sys.dm_db_index_usage_stats | |
| INNER JOIN sys.objects ON dm_db_index_usage_stats.OBJECT_ID = objects.OBJECT_ID | |
| INNER JOIN sys.indexes ON indexes.index_id = dm_db_index_usage_stats.index_id AND dm_db_index_usage_stats.OBJECT_ID = indexes.OBJECT_ID | |
| WHERE | |
| indexes.is_primary_key = 0 --This line excludes primary key constarint | |
| AND | |
| indexes. is_unique = 0 --This line excludes unique key constarint | |
| AND | |
| dm_db_index_usage_stats.user_updates <> 0 -- This line excludes indexes SQL Server hasn’t done any work with | |
| AND | |
| dm_db_index_usage_stats. user_lookups = 0 | |
| AND | |
| dm_db_index_usage_stats.user_seeks = 0 | |
| AND | |
| dm_db_index_usage_stats.user_scans = 0 | |
| ORDER BY | |
| dm_db_index_usage_stats.user_updates DESC |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment