I often receive questions by developers and DBAs on how to find what’s the issue with their query or why it is so slow. This was a similar case. One of my team mates asked me to help her in identity the issue with procedure execution. As per the user procedure used to execute fine earlier.
Let us produce a problem of slow running query on [StackOverflow] database. So create below procedure to find out users based on age parameter.
Code for procedure uspGetUsersByAge
Also, we need to create a non clustered index
[NCI_Users_Age] on Age column.
Now, in order to create issues, we need to execute the procedure
[dbo].[uspGetUsersByAge] for 100k times passing random age parameter value between 1 and 100 each time. For this I am using SQLQueryStress.
Now, to find out any issue on procedure, we can make use of sp_BlitzCache. For me below is the result:-
Apart from @StoredProcName parameter, we can also analyze the plan cache for query using Query Hash of the query. So below are the 2 methods.
sp_BlitzCache shows all the observations based on analysis of plan cache for particular procedure or query hash. This makes our life very easy while analyzing for issues. Like in my case, for procedure
dbo.uspGetUsersByAge, it is suggesting that there is problem of frequently executed operators/busy-loops which means operations have been found that are executed 100 times more than the no of rows returned. This clearly suggests that either my query needs proper indexing because it may be going through scan rather than seeks or out of date stats forcing SQL Server to make inappropriate execution plans. But, looking at another finding Expensive Key Lookups, we can clearly guess that rather than going for index scan, SQL Server is choosing Nested loops with key lookups signifying that parameter sniffing could be the major issue in our case. We can also make use of URLs provided in 2nd result set corresponding to each performance finding.
Conclusion: Analyzing SQL Server Query performance issues is not that hard with sp_Blitz scripts. Thanks to Brent Ozar and Team. I am big fan of these scripts and hope to uncover more mysteries of SQL Server with these. Happy Coding 🙂
Please feel free to correct me by comments. If you like the article, do Like & Share.
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.