index – Run SQL Server BLOG

7th December 20227th December 2022

Index usage statistics

Do you have unused index? Do you have ineffective index? How many times was it used for select or update?
Is it used by scan , seeks or lookups?
Index usage statistics :

-- index usage statistics
SELECT d.name AS DatabaseName, t.name AS TableName, i.name AS IndexName, ius.*
FROM sys.dm_db_index_usage_stats ius
JOIN sys.databases d ON d.database_id = ius.database_id
JOIN sys.tables t ON t.object_id = ius.object_id
JOIN sys.indexes i ON i.object_id = ius.object_id AND i.index_id = ius.index_id
WHERE d.database_id = (DB_ID(N'DataBaseName'))   and t.name='TablesName'    -- Database Name and Table
ORDER BY user_updates DESC

-- index usage statistics

SELECT d.name AS DatabaseName, t.name AS TableName, i.name AS IndexName, ius.*

FROM sys.dm_db_index_usage_stats ius

JOIN sys.databases d ON d.database_id = ius.database_id

JOIN sys.tables t ON t.object_id = ius.object_id

JOIN sys.indexes i ON i.object_id = ius.object_id AND i.index_id = ius.index_id

WHERE d.database_id = (DB_ID(N'DataBaseName')) and t.name='TablesName' -- Database Name and Table

ORDER BY user_updates DESC

21st July 202121st July 2021

Collect Index defragmentation

I do it for registered servers:

Declare @TableIndex table (ServerName sql_variant, DBName nvarchar(128), SchemaName varchar(128), TableName nvarchar (128), IndexName nvarchar (128), index_type_desc nvarchar (60),FragPercent float, record_count bigint, create_date datetime, modify_date datetime);

INSERT into @TableIndex 
exec SP_MSforeachdb @command1 = 
            'use [?];
			 IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''Distribution'',''SQLMonitor'')
			 BEGIN

                SELECT 
					
					SERVERPROPERTY(''ServerName'')  
					,DB_NAME() as DBName
					,s.[name]  AS SchemaName
					,t.[name] AS TableName
					 ,i.NAME AS index_name
					 ,index_type_desc

					 ,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent
					 ,record_count AS table_record_count
					 ,create_date
					 ,modify_date
					FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ''SAMPLED'') ips
					INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]
					INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id]
					INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)
					where record_count>1000
					ORDER BY record_count desc, avg_fragmentation_in_percent DESC
				END'
select * from @TableIndex 
go

Declare @TableIndex table (ServerName sql_variant, DBName nvarchar(128), SchemaName varchar(128), TableName nvarchar (128), IndexName nvarchar (128), index_type_desc nvarchar (60),FragPercent float, record_count bigint, create_date datetime, modify_date datetime);

INSERT into @TableIndex

exec SP_MSforeachdb @command1 =

'use [?];

IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''Distribution'',''SQLMonitor'')

BEGIN

SELECT

SERVERPROPERTY(''ServerName'')

,DB_NAME() as DBName

,s.[name] AS SchemaName

,t.[name] AS TableName

,i.NAME AS index_name

,index_type_desc

,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent

,record_count AS table_record_count

,create_date

,modify_date

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, ''SAMPLED'') ips

INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]

INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id]

INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)

where record_count>1000

ORDER BY record_count desc, avg_fragmentation_in_percent DESC

END'

select * from @TableIndex

27th January 202127th January 2021

Index fragmentation with table row count and index creation and modification date

You can often check the index defragmentation. It is useful if you start checking with the biggest table, because the biggest effect is there.
I like to check the indexes with the creation and modification date. If you rebuild the index your index modification date will be updated.

USE UserDB     -- with modifydate -order by rowcount
GO

SELECT s.[name] +'.'+t.[name]  AS table_name
 ,i.NAME AS index_name
 ,index_type_desc
 ,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent
 ,record_count AS table_record_count
 ,create_date
 ,modify_date
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]
INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id]
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)
ORDER BY record_count desc, avg_fragmentation_in_percent DESC

USE UserDB -- with modifydate -order by rowcount

SELECT s.[name] +'.'+t.[name] AS table_name

,i.NAME AS index_name

,index_type_desc

,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent

,record_count AS table_record_count

,create_date

,modify_date

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips

INNER JOIN sys.tables t on t.[object_id] = ips.[object_id]

INNER JOIN sys.schemas s on t.[schema_id] = s.[schema_id]

INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id = i.index_id)

ORDER BY record_count desc, avg_fragmentation_in_percent DESC

If you want to see for all database you can replace the

DB_ID()

with

NULL

in the script.

13th January 202013th January 2020

How to Check Index Fragmentation and Partitioned Table Index Fragmentation

I use two script. The firs one is for general purpose and run faster, the second one is good for partitioned table and get many details about partitioned index and table.

SELECT OBJECT_NAME(ips.OBJECT_ID)
 ,i.NAME
 ,ips.index_id
 ,ips.partition_number
 ,index_type_desc
 ,avg_fragmentation_in_percent
 ,avg_page_space_used_in_percent
 ,page_count
 ,ips.record_count
 
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)
 AND (ips.index_id = i.index_id)
-- where OBJECT_NAME(ips.OBJECT_ID) ='table_name'
ORDER BY index_id,ips.partition_number,avg_fragmentation_in_percent DESC

SELECT OBJECT_NAME(ips.OBJECT_ID)

,i.NAME

,ips.index_id

,ips.partition_number

,index_type_desc

,avg_fragmentation_in_percent

,avg_page_space_used_in_percent

,page_count

,ips.record_count

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips

INNER JOIN sys.indexes i ON (ips.object_id = i.object_id)

AND (ips.index_id = i.index_id)

-- where OBJECT_NAME(ips.OBJECT_ID) ='table_name'

ORDER BY index_id,ips.partition_number,avg_fragmentation_in_percent DESC

Detailed query for partitioned table:

/*
Script  : Index Fragmentation Status (includes Partitioned Tables/Indexes)
Version : 1.0 (April 2010)
Author  : Richard Doering
Web     : http://sqlsolace.blogspot.com
*/
- Hide quoted text -
SELECT
             SCHEMA_NAME(o.schema_id) AS SchemaName               
            ,OBJECT_NAME(o.object_id) AS TableName
            ,i.name  AS IndexName
            ,i.type_desc AS IndexType
            ,CASE WHEN ISNULL(ps.function_id,1) = 1 THEN 'NO' ELSE 'YES' END AS Partitioned
            ,COALESCE(fg.name ,fgp.name) AS FileGroupName
            ,p.partition_number AS PartitionNumber
            ,p.rows AS PartitionRows
            ,dmv.Avg_Fragmentation_In_Percent
            ,dmv.Fragment_Count
            ,dmv.Avg_Fragment_Size_In_Pages
            ,dmv.Page_Count 
            ,prv_left.value  AS PartitionLowerBoundaryValue
            ,prv_right.value AS PartitionUpperBoundaryValue
            ,CASE WHEN pf.boundary_value_on_right = 1 THEN 'RIGHT' WHEN pf.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'NONE' END AS PartitionRange
            ,pf.name        AS PartitionFunction
            ,ds.name AS PartitionScheme
FROM sys.partitions AS p WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
            ON i.object_id = p.object_id
            AND i.index_id = p.index_id
INNER JOIN sys.objects AS o WITH (NOLOCK)
            ON o.object_id = i.object_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') dmv
            ON dmv.OBJECT_ID = i.object_id
            AND dmv.index_id = i.index_id
            AND dmv.partition_number  = p.partition_number
LEFT JOIN sys.data_spaces AS ds WITH (NOLOCK)
      ON ds.data_space_id = i.data_space_id
LEFT JOIN sys.partition_schemes AS ps WITH (NOLOCK)
      ON ps.data_space_id = ds.data_space_id
LEFT JOIN sys.partition_functions AS pf WITH (NOLOCK)
      ON pf.function_id = ps.function_id
LEFT JOIN sys.destination_data_spaces AS dds WITH (NOLOCK)
      ON dds.partition_scheme_id = ps.data_space_id
      AND dds.destination_id = p.partition_number
LEFT JOIN sys.filegroups AS fg WITH (NOLOCK)
      ON fg.data_space_id = i.data_space_id
LEFT JOIN sys.filegroups AS fgp WITH (NOLOCK)
      ON fgp.data_space_id = dds.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left WITH (NOLOCK)
      ON ps.function_id = prv_left.function_id
      AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right WITH (NOLOCK)
      ON ps.function_id = prv_right.function_id
      AND prv_right.boundary_id = p.partition_number
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0  
ORDER BY
     SchemaName
    ,TableName
    ,IndexName
    ,PartitionNumber

Script : Index Fragmentation Status (includes Partitioned Tables/Indexes)

Version : 1.0 (April 2010)

Author : Richard Doering

Web : http://sqlsolace.blogspot.com

- Hide quoted text -

SELECT

SCHEMA_NAME(o.schema_id) AS SchemaName

,OBJECT_NAME(o.object_id) AS TableName

,i.name AS IndexName

,i.type_desc AS IndexType

,CASE WHEN ISNULL(ps.function_id,1) = 1 THEN 'NO' ELSE 'YES' END AS Partitioned

,COALESCE(fg.name ,fgp.name) AS FileGroupName

,p.partition_number AS PartitionNumber

,p.rows AS PartitionRows

,dmv.Avg_Fragmentation_In_Percent

,dmv.Fragment_Count

,dmv.Avg_Fragment_Size_In_Pages

,dmv.Page_Count

,prv_left.value AS PartitionLowerBoundaryValue

,prv_right.value AS PartitionUpperBoundaryValue

,CASE WHEN pf.boundary_value_on_right = 1 THEN 'RIGHT' WHEN pf.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'NONE' END AS PartitionRange

,pf.name AS PartitionFunction

,ds.name AS PartitionScheme

FROM sys.partitions AS p WITH (NOLOCK)

INNER JOIN sys.indexes AS i WITH (NOLOCK)

ON i.object_id = p.object_id

AND i.index_id = p.index_id

INNER JOIN sys.objects AS o WITH (NOLOCK)

ON o.object_id = i.object_id

INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') dmv

ON dmv.OBJECT_ID = i.object_id

AND dmv.index_id = i.index_id

AND dmv.partition_number = p.partition_number

LEFT JOIN sys.data_spaces AS ds WITH (NOLOCK)

ON ds.data_space_id = i.data_space_id

LEFT JOIN sys.partition_schemes AS ps WITH (NOLOCK)

ON ps.data_space_id = ds.data_space_id

LEFT JOIN sys.partition_functions AS pf WITH (NOLOCK)

ON pf.function_id = ps.function_id

LEFT JOIN sys.destination_data_spaces AS dds WITH (NOLOCK)

ON dds.partition_scheme_id = ps.data_space_id

AND dds.destination_id = p.partition_number

LEFT JOIN sys.filegroups AS fg WITH (NOLOCK)

ON fg.data_space_id = i.data_space_id

LEFT JOIN sys.filegroups AS fgp WITH (NOLOCK)

ON fgp.data_space_id = dds.data_space_id

LEFT JOIN sys.partition_range_values AS prv_left WITH (NOLOCK)

ON ps.function_id = prv_left.function_id

AND prv_left.boundary_id = p.partition_number - 1

LEFT JOIN sys.partition_range_values AS prv_right WITH (NOLOCK)

ON ps.function_id = prv_right.function_id

AND prv_right.boundary_id = p.partition_number

WHERE

OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0

ORDER BY

SchemaName

,TableName

,IndexName

,PartitionNumber

18th June 201821st June 2018

SQL Server Index Tuning

Egy több millió sorral rendelkező táblán már súlyos gondokat okozhat egy-egy index hiány.

5 Millió sor, paraméterezett select, a szerver túlterhelt:

CPU and Processor Queue Length - Performance Monitor — CPU and Processor Queue Length – Performance Monitor

11:20-kor 1 db index felrakása után látványosan csökken a CPU (kék) és CPU Queue (piros). Ellenpróba 13:35-kor az index letiltásakor ismét befullad a szerver (a határértéknek számító 80%-ról 95% átlagos cpu használatra ugrik). Ráadásul a szerveren több mint 100 adatbázis van és mégis egy darab eljárás jelentősen beterhel.
Persze a valóság nem ilyen egyszerű, egy -egy index felrakásának hatását többnyire nem lehet performance monitor-ból kimutatni, a fenti példa egy rosszul tervezett gyakori futású kódra vonatkozik.

25th May 2018

Index méretek

Index size

--  USE <databasename>
SELECT i.[name] AS IndexName
                ,SUM(s.[used_page_count]) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
                AND s.[index_id] = i.[index_id]
-- where i.[name] like 'mix%'
GROUP BY i.[name]
ORDER BY IndexSizeKB Desc, i.[name]
GO

-- USE <databasename>

SELECT i.[name] AS IndexName

,SUM(s.[used_page_count]) * 8 AS IndexSizeKB

FROM sys.dm_db_partition_stats AS s

INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]

AND s.[index_id] = i.[index_id]

-- where i.[name] like 'mix%'

GROUP BY i.[name]

ORDER BY IndexSizeKB Desc, i.[name]

Az egyes indexek méretei. Az adott adatbázisra kell használni, szükség esetén szűrni.