Improve index

Microsoft SQL server 2016

Can i see if i should improve anything on my SQL server?


Mr Ozar have develop some sp that can give you more information, get the kit from

install this one in your master database


Now you have a set of SP to use.

First run (if you have performance issues just now)


Run this to get the most common wait issues you need to check on your server:

sp_BlitzFirst  @sincestartup = 1, @outputtype = 'Top10'


Setup a database LOGSQL and schedule this every month;

EXEC sp_Blitz @OutputDatabaseName = 'LOGSQL', @OutputSchemaName = 'dbo', @OutputTableName = 'BlitzResults';

Will give you a table BlitzResults to look at to see what status is for your SQL server.

Run this manually to see what can be done with index

EXEC sp_BlitzIndex @GetAllDatabases = 1, @BringThePain = 1;

copy result to excel for further analysis, remove unused index and duplicates.


Run to see the biggest queries:

EXEC sp_BlitzCache @Top = 20,  @BringThePain = 1;

Run this to see the size of index in one database:

  , AS Index_Name
  ,IX.type_desc Index_Type
  ,SUM(PS.[used_page_count]) * 8 IndexSizeKB
  ,IXUS.user_seeks AS NumOfSeeks
  ,IXUS.user_scans AS NumOfScans
  ,IXUS.user_lookups AS NumOfLookups
  ,IXUS.user_updates AS NumOfUpdates
  ,IXUS.last_user_seek AS LastSeek
  ,IXUS.last_user_scan AS LastScan
  ,IXUS.last_user_lookup AS LastLookup
  ,IXUS.last_user_update AS LastUpdate
FROM sys.indexes IX
INNER JOIN sys.dm_db_index_usage_stats IXUS ON IXUS.index_id = IX.index_id AND IXUS.OBJECT_ID = IX.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats PS on PS.object_id=IX.object_id
GROUP BY OBJECT_NAME(IX.OBJECT_ID) , ,IX.type_desc ,IXUS.user_seeks ,IXUS.user_scans ,IXUS.user_lookups,IXUS.user_updates ,IXUS.last_user_seek ,IXUS.last_user_scan ,IXUS.last_user_lookup ,IXUS.last_user_update

Check the size of the index and the usages – have it been used?

What is the most worrying wait type?


More Information: