How we find the performance of existing Indexes and which new Indexes SQL Server recommends

How we find the performance of existing Indexes and which new Indexes SQL Server recommends
How we find the performance of existing Indexes and which new Indexes SQL Server recommends

In a previous article we saw what are indexes. In this article we will see how to find the status of their hash, if you are using them, when they were created, when they were updated and if they are missing indexes recommended by SQL Server.

What should we remember?

  • When a table has clustered index means that the data is stored in the order that is the field/fields that we have defined the clustered index. Each table can have just one clustered index.
  • When a table does not have a clustered index it is called heap and its data is stored out of order on disk. So in the results of the script we will see that each table in the index type will definitely have either a clustered index or a heap and never both.
  • The non-clustered indexes they have his role index, are a copy of the data in the fields it has been made plus the fields they have included to a different object.
  • The index fields must not overlap, e.g. if we have a non-clustered index at [phone numbers] and one in [area] , [at phone no] the first index is not needed so it should be deleted. (for their maintenance and memory reasons)
  • The size of the table (either clustered or heap) and of each non-clustered index can also be measured in pages. Each page is 8KB, so if we multiply it by the number of them we find the size of the table / non-clustered index.
  • As the records of a table change, they are fragmented, that is, their order is lost, this is called fragmentation. To reduce it, we should do the index rebuild or reorganize if the fragmentation rate is small.
  • As it consists of fewer pages, it makes sense that it will more easily have greater fragmentation but performance is not affected as much. e.g. If a second page is added to a table with one page, we will directly have 50% fragmentation.
  • Some dynamic views that provide us with the information we need, they empty every time the SQL Server service is restarted.

How do we find the fragmentation of indexes

With the following script that I have made, we quickly find the fragmentation of each index with a series of pages. We can remove it from comment –and dbtables.[name] in ('car_calc') to find only for a specific table:

select distinct 
dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
clustertype.index_type_desc as 'IndexType',
clustertype.avg_fragmentation_in_percent,
clustertype.page_count,
clustertype.index_type_desc
FROM sys.indexes AS dbindexes 
INNER JOIN sys.dm_db_index_physical_stats  (DB_ID(), NULL, NULL, NULL, NULL) as clustertype ON clustertype.[object_id] = dbindexes.[object_id] AND clustertype.index_id = dbindexes.index_id
INNER JOIN sys.tables dbtables on dbtables.[object_id] = clustertype.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
WHERE 1=1
--and dbindexes.[name] is not NULL
--and clustertype.index_type_desc = 'NONCLUSTERED INDEX'
--and dbtables.[name] in ('car_calc')
ORDER BY page_count desc,dbtables.name desc
How we find the performance of existing Indexes and which new Indexes SQL Server recommends

Indexes recommended by SQL Server

When SQL Server sees that some queries need an index in some field that is used, it suggests creating them. Because it is not always fair, it is necessary to evaluate them. With the following script, those who recommend us are shown:

SELECT db.[name] AS [DatabaseName]
    ,id.[object_id] AS [ObjectID]
	,OBJECT_NAME(id.[object_id], db.[database_id]) AS [ObjectName]
    ,id.[statement] AS [FullyQualifiedObjectName]
    ,id.[equality_columns] AS [EqualityColumns]
    ,id.[inequality_columns] AS [InEqualityColumns]
    ,id.[included_columns] AS [IncludedColumns]
    ,gs.[unique_compiles] AS [UniqueCompiles]
    ,gs.[user_seeks] AS [UserSeeks]
    ,gs.[user_scans] AS [UserScans]
    ,gs.[last_user_seek] AS [LastUserSeekTime]
    ,gs.[last_user_scan] AS [LastUserScanTime]
    ,gs.[avg_total_user_cost] AS [AvgTotalUserCost]  -- Average cost of the user queries that could be reduced by the index in the group.
    ,gs.[avg_user_impact] AS [AvgUserImpact]  -- The value means that the query cost would on average drop by this percentage if this missing index group was implemented.
    ,gs.[system_seeks] AS [SystemSeeks]
    ,gs.[system_scans] AS [SystemScans]
    ,gs.[last_system_seek] AS [LastSystemSeekTime]
    ,gs.[last_system_scan] AS [LastSystemScanTime]
    ,gs.[avg_total_system_cost] AS [AvgTotalSystemCost]
    ,gs.[avg_system_impact] AS [AvgSystemImpact]  -- Average percentage benefit that system queries could experience if this missing index group was implemented.
    ,gs.[user_seeks] * gs.[avg_total_user_cost] * (gs.[avg_user_impact] * 0.01) AS [IndexAdvantage]
    ,'CREATE INDEX [IX_' + OBJECT_NAME(id.[object_id], db.[database_id]) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(id.[equality_columns], ''), ', ', '_'), '[', ''), ']', '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN '_'
        ELSE ''
        END + REPLACE(REPLACE(REPLACE(ISNULL(id.[inequality_columns], ''), ', ', '_'), '[', ''), ']', '') + '_' + LEFT(CAST(NEWID() AS [nvarchar](64)), 5) + ']' + ' ON ' + id.[statement] + ' (' + ISNULL(id.[equality_columns], '') + CASE
        WHEN id.[equality_columns] IS NOT NULL
            AND id.[inequality_columns] IS NOT NULL
            THEN ','
        ELSE ''
        END + ISNULL(id.[inequality_columns], '') + ')' + ISNULL(' INCLUDE (' + id.[included_columns] + ')', '') AS [ProposedIndex]
    ,CAST(CURRENT_TIMESTAMP AS [smalldatetime]) AS [CollectionDate]
FROM [sys].[dm_db_missing_index_group_stats] gs WITH (NOLOCK)
INNER JOIN [sys].[dm_db_missing_index_groups] ig WITH (NOLOCK) ON gs.[group_handle] = ig.[index_group_handle]
INNER JOIN [sys].[dm_db_missing_index_details] id WITH (NOLOCK) ON ig.[index_handle] = id.[index_handle]
INNER JOIN [sys].[databases] db WITH (NOLOCK) ON db.[database_id] = id.[database_id]
WHERE  db.[database_id] = DB_ID()
--AND OBJECT_NAME(id.[object_id], db.[database_id]) = 'YourTableName'
ORDER BY ObjectName, [IndexAdvantage] DESC
OPTION (RECOMPILE);
How we find the performance of existing Indexes and which new Indexes SQL Server recommends

How do we find when each Index was created?

With the following script we find when each index was created.

select 
    i.name as IndexName, 
    o.name as TableName, 
	co.[name] as ColumnName,
    ic.key_ordinal as ColumnOrder,
    ic.is_included_column as IsIncluded, 
    o.create_date
from sys.indexes i 
inner join sys.objects o on i.object_id = o.object_id and o.type_desc = 'USER_TABLE'
inner join sys.index_columns ic on ic.object_id = i.object_id and ic.index_id = i.index_id
inner join sys.columns co on co.object_id = i.object_id 
    and co.column_id = ic.column_id
where 1=1
and i.[type] in (1,2)
order by o.[name], i.[name], ic.is_included_column, ic.key_ordinal;
How we find the performance of existing Indexes and which new Indexes SQL Server recommends

How do we find when each Index was last Rebuilt?

SQL Server does not store the information of when the index was last rebuilt, but it stores the last time the statistics were updated. In the default behavior, every time an index rebuild is performed, the statistics update is also performed, so knowing the date of the second, we also know the date of the first.

With the following script we find the date of the last statistics:

select OBJECT_NAME(object_id) [TableName], 
       name [IndexName],
       stats_date(object_id, stats_id) [LastStatsUpdate]
from sys.stats
where name not like '_WA%'
--and stats_date(object_id, stats_id) IS NOT NULL
and OBJECTPROPERTY(object_id, 'IsMSShipped') = 0
--and OBJECT_NAME(object_id) = 'pinakas'
ORDER BY LastStatsUpdate DESC
How we find the performance of existing Indexes and which new Indexes SQL Server recommends

How we find how much each Index is used

Sometimes we create indexes that are not used, this results in them occupying space from the memory (RAM) of the SQL Server without benefit, also the increased number of indexes delays the inserts/deletes/updates since the update must be done in them as well.

With the following script we can see for each index how many times it was accessed, in what way and when was the last time:

select   OBJECT_NAME(S.[OBJECT_ID]) AS [object name], 
         i.[NAME] AS [index name], 
         s.USER_SEEKS, 
         s.USER_SCANS, 
         s.USER_LOOKUPS, 
         s.USER_UPDATES,
		 s.last_user_seek,
		 s.last_user_scan,
		 s.last_user_lookup,
		 s.last_user_update
from     SYS.DM_DB_INDEX_USAGE_STATS as S 
         inner join SYS.INDEXES I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID 
where    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1 
How we find the performance of existing Indexes and which new Indexes SQL Server recommends

Sources:

Share it

Leave a reply