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:
SELECT OBJECT_NAME(IX.OBJECT_ID) Table_Name ,IX.name 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 WHERE OBJECTPROPERTY(IX.OBJECT_ID,'IsUserTable') = 1 GROUP BY OBJECT_NAME(IX.OBJECT_ID) ,IX.name ,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?