Product:
Microsoft SQL server 2016
Issue:
Can i see if i should improve anything on my SQL server?
Solution:
Mr Ozar have develop some sp that can give you more information, get the kit from
https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit#writing-sp_blitz-output-to-a-table
install this one in your master database
Install-Core-Blitz-With-Query-Store.sql
Now you have a set of SP to use.
First run (if you have performance issues just now)
sp_BlitzFirst
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?
https://www.sqlshack.com/boost-sql-server-performance-with-wait-statistics/
https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
More Information:
https://www.brentozar.com/archive/2018/07/cxconsumer-is-harmless-not-so-fast-tiger/