credit: https://www.pinterest.com/pin/253749760225683004/ Found a few cool tools for disgnosing the Server issues. Here is the share for you: SQL Using sp_BlitzCache to Find Your Worst-Performing Queries is that help analyze which queries are consuming most resources like , disk, time etc. sp_BlitzCache a free tool CPU To learn about this tool, use the help parameter When ran without parameters, it returns top 10 most CPU expensive queries: sp_BlitzCache @help=1 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 which links to the actual execution plan. This execution can be shared through Query Plan 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 parameter . Other options include CPU, duration, executions, XPM, memory grant, or recent compilations. SortOrder sp_BlitzCache @SortOrder='reads' To limit the number of results use the parameter. Remember that the more queries you analyze, the slower it goes @Top sp_BlitzCache @DatabaseName='DBName', @Top=50 To do an expert level analysis and see more info, use parameter @ExpertMode sp_BlitzCache @ExpertMode = 1 For an export to excel friendly result, use parameter @ExportToExcel 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 — Free SQL Server Health Check Script is an easy to use for SQL Server. Shows stuff like non-backuped databases etc. sp_Blitz free simple health check tool 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 are urgent issues. So to see only the urgent issues use the priorities parmeter 50 sp_Blitz @IgnorePrioritiesAbove = 50 Writing the results is also possible like this sp_Blitz @OutputDatabaseName = 'DBName', @OutputSchemaName = 'dbo' , @OutputTableName = 'SomeTable' sp_BlitzFirst Helps Troubleshoot Slow SQL Servers 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. sp_BlitzFirst To see the detiled wait stats use the expert mode and gives 3 resultsets: sp_BlitzFirst @ExpertMode = 1 Result set 1 : queries currently executing Result set 2 : the troubleshooting info like that of without any params Result set 3 : WAIT STATS (read below for the description of the same) Result set 4 : FILE STATS (read and write speeds for each database file — both data and log file) Result set 5 : Perfmon counters (statistics from the ) performance monitor Last result set : gives the currently executing queries 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. sp_BlitzIndex — SQL Server’s Index Sanity Test To run the index tests on all of the databases on the server use the parameter like so @GetAllDatabases = 1 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 WAIT STATS 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 or . wait stats waits and queues These statistics are tracked automatically in every version/edition of SQL Server, and they’re easy to query. Source Originally published at xameeramir.github.io .
