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.
CREATE PROCEDURE dbo.uspGetUsersByAge (@age TINYINT)
FROM dbo.Users as u
WHERE u.Age = @age
Also, we need to create a non clustered index
[NCI_Users_Age] on Age column.
CREATE NONCLUSTERED INDEX NCI_Users_Age ON dbo.Users (Age)
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.
-- Analyze using Procedure Name
exec master..sp_BlitzCache @StoredProcName = 'uspGetUsersByAge'
-- Analyze using Query Hash in case SQL Code is not procedure
exec master..sp_BlitzCache @OnlyQueryHashes = '0x998533A642130191'
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.