Last active
December 3, 2017 17:37
-
-
Save ignas-sakalauskas/9baa0667369089b0c5cdba29723be833 to your computer and use it in GitHub Desktop.
MSSQL Server Index fragmentation
This file contains 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
-- CPU intensive queries | |
SELECT TOP 50 | |
qs.total_worker_time/qs.execution_count as [Avg CPU Time], | |
SUBSTRING(qt.text,qs.statement_start_offset/2, | |
(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) | |
as query_text, | |
qt.dbid, dbname=db_name(qt.dbid), | |
qt.objectid | |
FROM sys.dm_exec_query_stats qs | |
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt | |
ORDER BY | |
[Avg CPU Time] DESC | |
-- https://ignas.me/tech/mssql-server-index-fragmentation/ |
This file contains 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
-- Index fragmentation | |
select object_name(s.object_id), i.name, s.avg_fragmentation_in_percent, | |
'ALTER INDEX ' + i.name +' ON [' + object_name(s.object_id) + '] REBUILD ', | |
* from sys.dm_db_index_physical_stats(DB_ID(), null, null, null, null)s | |
inner join sys.indexes i on i.index_id = s.index_id and i.object_id = s.object_id | |
order by 3 desc | |
-- https://ignas.me/tech/mssql-server-index-fragmentation/ |
This file contains 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
-- Missing indices | |
SELECT TOP 25 | |
dm_mid.database_id AS DatabaseID, | |
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact, | |
dm_migs.last_user_seek AS Last_User_Seek, | |
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName], | |
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_' | |
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') + | |
CASE | |
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_' | |
ELSE '' | |
END | |
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','') | |
+ ']' | |
+ ' ON ' + dm_mid.statement | |
+ ' (' + ISNULL (dm_mid.equality_columns,'') | |
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE | |
'' END | |
+ ISNULL (dm_mid.inequality_columns, '') | |
+ ')' | |
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement | |
FROM sys.dm_db_missing_index_groups dm_mig | |
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs | |
ON dm_migs.group_handle = dm_mig.index_group_handle | |
INNER JOIN sys.dm_db_missing_index_details dm_mid | |
ON dm_mig.index_handle = dm_mid.index_handle | |
WHERE dm_mid.database_ID = DB_ID() | |
ORDER BY Avg_Estimated_Impact DESC | |
GO | |
-- https://ignas.me/tech/mssql-server-index-fragmentation/ |
This file contains 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
-- Row counts | |
SELECT so.name as TableName, ddps.row_count as [RowCount] | |
FROM sys.objects so | |
JOIN sys.indexes si ON si.OBJECT_ID = so.OBJECT_ID | |
JOIN sys.dm_db_partition_stats AS ddps ON si.OBJECT_ID = ddps.OBJECT_ID AND si.index_id = ddps.index_id | |
WHERE si.index_id < 2 AND so.is_ms_shipped = 0 | |
ORDER BY ddps.row_count DESC | |
-- https://ignas.me/tech/mssql-server-index-fragmentation/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment