credit: https://www.pinterest.com/pin/253749760225683004/
Found a few cool tools for disgnosing the SQL Server issues. Here is the share for you:
sp_BlitzCache
is a free tool that help analyze which queries are consuming most resources like CPU, disk, time etc.
To learn about this tool, use the help parameter sp_BlitzCache @help=1
When ran without parameters, it returns top 10 most CPU expensive queries: sp_BlitzCache
It results in all the diagnosis information like which database and stored procedures are taking the most time. It has a column named Query Plan
which links to the actual execution plan. This execution can be shared through pastetheplan.com
To narrow down the results to a single database use the parameter sp_BlitzCache @DatabaseName='DBName'
To sort the results by number of reads, pass the SortOrder
parameter sp_BlitzCache @SortOrder='reads'
. Other options include CPU, duration, executions, XPM, memory grant, or recent compilations.
To limit the number of results use the @Top
parameter. Remember that the more queries you analyze, the slower it goes sp_BlitzCache @DatabaseName='DBName', @Top=50
To do an expert level analysis and see more info, use @ExpertMode
parameter sp_BlitzCache @ExpertMode = 1
For an export to excel friendly result, use @ExportToExcel
parameter sp_BlitzCache @ExportToExcel = 1
To write the results to a separate database, use the new db entity parameters sp_BlitzCache @OutputDatabaseName = 'NewDB', @OutputSchemaName = 'dbo', @OutputTableName = 'OutputTable'
sp_Blitz
is an easy to use free simple health check tool for SQL Server. Shows stuff like non-backuped databases etc.
To learn about this tool, use the help parameter sp_Blitz @help=1
When executed without parameters, gives the list of suspicous things. Notice that issues upto priority 50 are urgent issues. So to see only the urgent issues use the priorities parmeter sp_Blitz @IgnorePrioritiesAbove = 50
Writing the results is also possible like this sp_Blitz @OutputDatabaseName = 'DBName', @OutputSchemaName = 'dbo' , @OutputTableName = 'SomeTable'
sp_BlitzFirst
when executed on it’s own gives all the stuff which are causing the server to run slow like ongoing backup execution, high CPU utilization.
To see the detiled wait stats use the expert mode sp_BlitzFirst @ExpertMode = 1
and gives 3 resultsets:
To see the results since SQL Server started, use since startup param sp_BlitzFirst @SinceStartup = 1
The results can be stored in an output database as well.
To run the index tests on all of the databases on the server use the parameter @GetAllDatabases = 1
like so sp_BlitzIndex @GetAllDatabases = 1
If you’ve got more than 50 databases on the server, this only works if you also pass in @BringThePain = 1, because it’s gonna be slow.
To get the database specific details set mode to 4 as sp_BlitzIndex @Mode = 4
Whenever SQL Server is running queries, it’s tracking how much time it spends waiting on bottlenecks. These wait statistics are the easiest way to identify your bottleneck and in general called wait stats or waits and queues.
These statistics are tracked automatically in every version/edition of SQL Server, and they’re easy to query.
Originally published at xameeramir.github.io.