Fixing Problematic SQL Server Indexes

Indexes reduces (not always) the performance of Insert, Update, Delete and SELECT statements.

As a general rule, try to have around 5 indexes per tables, 5 columns per index. This is not a hard and fast rule. Just a general good starting point.

Indexes reduce the performance of SELECT statement even when an index is not being used.

How does an index fill factor affect fragmentation?

When you set fill factor below 100%, you are actually setting fragmentation.

You’re purposely leaving empty space on every page in an index, which:

  1. Makes your database larger
  2. Makes scans take longer
  3. Makes backups, checkdb take longer
  4. Makes index maintenance take longer

Get a snapshot of indexes

Spreadsheet inventory of existing indexes

You need to run the below script under each database:

sp_BlitzIndex @GetAllDatabases = 1, @Mode = 2

Inventory of missing indexes

sp_BlitzIndex @GetAllDatabases = 1, @Mode = 3

Find large unused indexes

/* To find large, unused, junk drawer objects: */

sp_BlitzIndex @Mode =2 , @sortorder = 'size' sp_BlitzIndex @Mode =2 , @sortorder = 'rows'

/* For management overview before & after doing index tuning*/ sp_BlitzIndex @Mode=1

%d bloggers like this: