Indexes can hamper the SQL Server performance too

You’ve probably heard: “indexes are not free”. They are right!

Indexes can greatly improve queries performance in the SQL server, but they may hamper the performance too.

There are costs in storage (disk space) as well as resources to the maintenance required on DML operations.

So, it’s very important to understand your overall workload and find a good balance between, making your queries efficient and not paying too much for that.

There are many approaches, concepts, and technics about the index, below you will see some of them.

Missing indexes

SQL Server will sometimes suggest an index that it believes will help that query run faster.

These are known as missing indexes suggestions.

SQL Server is very good at determining when an index is needed but sometimes it fails.

The script below identifies missing indexes with more than 70000 unique compiles or user seek or user scan and average use impact higher than 80%.

You should not create all the missing indexes this script suggests. They are just for your guidance.

  1. If the server was rebooted earlier than two weeks ago, the stats are useless.
  2. You probably shouldn’t have then 5 indexes per table (Over-Indexing).
  1. select sd.name,
  2.     substring(d.statement, charindex('.', d.statement) + 1, len(d.statement)) as tablename,
  3.     s.unique_compiles, s.user_seeks, s.user_scans, s.avg_user_impact,
  4.     'use [' + sd.name + ']; create index IX_' + replace(reverse(substring(reverse(d.statement), 0, charindex('[', reverse(d.statement)))), ']', '') 
  5. 	+ '_nn ON ' + substring(d.statement, charindex('.', d.statement) + 1, len(d.statement))
  6.     + ' (' +
  7.     case
  8.         when d.equality_columns is not null and d.inequality_columns is not null then d.equality_columns + ',' 
  9. 	+ d.inequality_columns
  10.         when d.equality_columns is not null and d.inequality_columns is null then d.equality_columns
  11.         when d.equality_columns is null and d.inequality_columns is not null then d.inequality_columns
  12.     end
  13.     + ')' +
  14.     case
  15.         when d.included_columns is not null then ' include (' + d.included_columns + ')'
  16.     else ''
  17.     end
  18.     as create_cmd
  19. from master.sys.dm_db_missing_index_details d
  20.     inner join master.sys.dm_db_missing_index_groups g on d.index_handle = g.index_handle
  21.     inner join master.sys.dm_db_missing_index_group_stats s on g.index_group_handle = s.group_handle
  22.     inner join master.dbo.sysdatabases sd on d.database_id = sd.dbid
  23. where sd.dbid > 4
  24.     and sd.name not in ('distribution', 'LogShipping')
  25.     and (s.unique_compiles > 70000 or s.user_seeks > 70000 or s.user_scans > 70000)
  26.     and s.avg_user_impact > 80
  27. order by sd.name, s.user_seeks desc, s.user_scans desc

Fill factor option

When you create (or rebuild) an index, you can set “fillfactor”, what means the percentage of each 8K data page used in the “leaf” level of the index it should fill up.

Lowering this setting may improve performance by reducing page splits, but do not set the system-wide value for it, probably that will hurt more than help.

It would be best if you had a proper maintenance plan and only change the fill factor for heavily fragmented indexes.

If needed, start reducing the fillfactor gradually, going to 95%, and rebuilding the indexes in a planned change.

Unused indexes

Sometimes an index is never used to improve performance.

Whatever, SQL Server has to maintain it in every INSERT, UPDATE and DELETE operations.

So,  why keep this index?

You can use the script below to find indexes that were updated more than 5000 times, never used or with many updates and  few lookups ( < 10 ):

  1. Save the output “create_cmd” for re-create index script in case of rollback.
  2. Execute the output “drop_cmd ”  to implement the change.
  3. Make sure there is not any query or store procedure forcing hint to the indexes that will be dropped.
  1. set nocount on;
  2.  
  3. declare @DBName nvarchar(128),
  4.         @cmd nvarchar(max) = N'',
  5.         @collation varchar(100)
  6.  
  7. select @collation = convert(varchar, serverproperty('collation'))
  8.  
  9. if object_id('tempdb..#indexes') is not null
  10.     drop table #indexes
  11.  
  12. create table #indexes
  13.     (databasename sysname not null,
  14.     tablename varchar(200) not null,
  15.     indexname varchar(200) not null,
  16.     drop_cmd varchar(max) not null,
  17.     create_cmd varchar(max) not null)
  18.  
  19. declare DBList cursor local fast_forward for
  20. select cast(name as nvarchar(128))
  21. from master.dbo.sysdatabases
  22. where dbid > 4
  23.     and name not in ('distribution', 'LogShipping')
  24.     and databasepropertyex(name, 'Status') = 'ONLINE'
  25.     and databasepropertyex(name, 'Updateability') = 'READ_WRITE'
  26.  
  27. open DBList
  28. fetch next from DBList into @dbname
  29. while @@fetch_status <> -1
  30. begin
  31.  
  32. set @cmd = N'
  33. ;with
  34. notusedindexes as
  35. (select db_name(db_id()) AS DBName,
  36.     ss.name + ''.'' + o.name AS [table_name], i.name AS [index_name], i.type_desc,
  37.     ''drop index '' + i.name + '' on '' + ss.name + ''.'' + o.name AS drop_cmd
  38. from [' + @DBName + '].sys.dm_db_index_usage_stats iu
  39.     inner join [' + @DBName + '].sys.indexes i ON iu.index_id = i.index_id and iu.object_id = i.object_id
  40.     inner join [' + @DBName + '].sys.objects o ON i.object_id = o.object_id
  41.     left join [' + @DBName + '].sys.objects n ON n.name = i.name
  42.     inner join [' + @DBName + '].sys.schemas ss ON o.schema_id = ss.schema_id
  43. where iu.database_id = db_id()
  44.     and i.type <> 0
  45.     and i.type_desc = ''nonclustered''
  46.     and (n.TYPE IS NULL or n.type NOT IN (''PK'', ''UQ''))
  47.     and ((iu.user_seeks = 0 and iu.user_scans = 0 and iu.user_lookups = 0 and iu.user_updates <> 0) -- only ever updated
  48.         or (iu.user_updates > 100000 and iu.user_seeks <  10 and iu.user_scans < 10 and iu.user_lookups < 10) -- loads of updates, few lookups
  49.         or (iu.user_seeks < 10 and iu.user_scans <  10 and iu.user_lookups <10 and iu.user_updates < 10)) -- hardly used at all
  50.     and iu.user_updates > 5000), -- updated more than 5000 times
  51. indexlist as
  52. (select distinct n.table_name tablename,n.index_name indexname, n.drop_cmd, i.is_unique,
  53.     (select distinct stuff((select '', '' + c.name
  54.                             from sys.index_columns ic1 inner join
  55.                                 sys.columns c on ic1.object_id=c.object_id and
  56.                                                 ic1.column_id=c.column_id
  57.                             where ic1.index_id = ic.index_id and
  58.                                 ic1.object_id=i.object_id and
  59.                                 ic1.index_id=i.index_id and
  60.                                 ic1.is_included_column = 0
  61.                             order by ic1.key_ordinal For XML PATH('''')),1,2,'''')
  62.     from sys.index_columns ic
  63.     where object_id=i.object_id and index_id=i.index_id) as keycolumnlist,
  64.     (select distinct stuff((select '', '' + c.name
  65.                             from sys.index_columns ic1 inner join
  66.                                 sys.columns c on ic1.object_id=c.object_id and
  67.                                                 ic1.column_id=c.column_id
  68.                             where ic1.index_id = ic.index_id and
  69.                                 ic1.object_id=i.object_id and
  70.                                 ic1.index_id=i.index_id and
  71.                                 ic1.is_included_column = 1
  72.                             order by index_column_id For XML PATH('''')),1,2,'''')
  73.     from sys.index_columns ic
  74.     where object_id=i.object_id and index_id=i.index_id) as includedcolumnlist
  75. from sys.indexes i
  76.     inner join sys.index_columns ic on i.object_id=ic.object_id and i.index_id=ic.index_id
  77.     inner join sys.objects o on i.object_id=o.object_id
  78.     inner join notusedindexes n on i.object_id = object_id(n.table_name) and i.name collate ' + @collation + ' = n.index_name collate ' + @collation + '
  79. where o.is_ms_shipped=0 and i.is_primary_key = 0 and i.is_disabled = 0 and i.is_unique_constraint = 0 and i.has_filter = 0)
  80. insert into #indexes
  81. select db_name() as databasename, tablename, indexname, drop_cmd,
  82.     ''create '' + case when is_unique = 1 then ''unique '' else '''' end + ''index '' + indexname + '' on '' + tablename + '' ('' + keycolumnlist + '')'' 
  83. 	+ case when includedcolumnlist is not null then + '' include ('' + includedcolumnlist + '')'' else '''' end + '' with (data_compression = page)'' create_cmd
  84. from indexlist'
  85.  
  86. --print @cmd
  87. exec sys.sp_executesql @cmd
  88.  
  89. fetch next from DBList into @DBName
  90. end
  91.  
  92. close DBList
  93. deallocate DBList
  94.  
  95. select *
  96. from #indexes
  97.  
  98. drop table #indexes

More information:

Microsoft – Clustered and Nonclustered Indexes 
Microsoft – Specify Fill Factor for an Index
Aaron Bertrand – SQL performance – Don’t just blindly create those “missing” indexes!

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

Your email address will not be published. Required fields are marked *

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.