How we find the performance of Indexes in SQL Server
Certifications:
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
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
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 our script, those who suggest:
select d.[object_id], s = OBJECT_SCHEMA_NAME(d.[object_id]), o = OBJECT_NAME(d.[object_id]), d.equality_columns, d.inequality_columns, d.included_columns, s.unique_compiles, s.user_seeks, s.last_user_seek, s.user_scans, s.last_user_scan FROM sys.dm_db_missing_index_details AS d INNER JOIN sys.dm_db_missing_index_groups AS g ON d.index_handle = g.index_handle INNER JOIN sys.dm_db_missing_index_group_stats AS s ON g.index_group_handle = s.group_handle WHERE d.database_id = DB_ID() AND OBJECTPROPERTY(d.[object_id], 'IsMsShipped') = 0;
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;
When was each Index 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 much 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
