How to Track Performance of Queries That Use RECOMPILE Hints

Say we have a stored procedure that has two queries in it – the second query uses a recompile hint, and you might recognize it from my parameter sniffing session: Transact-SQL CREATE OR ALTER PROC dbo.usp_SearchUsers @Reputation INT AS BEGIN /* Query 1, always the same: */ SELECT COUNT(*) FROM dbo.Users; /* Query 2, recompiles…
Read More
sp_BlitzCache query warnings

DBA Training Plan 12: What Query Plans Are In Cache?

In the last episode, while talking about the basics of executing queries, I said that SQL Server caches execution plans in memory, reusing them whenever the same query gets executed again. I’ve also talked about the dynamic management views (DMVs) that let you query what’s in SQL Server’s memory. Let’s put those two things together…
Read More

Learn to Use sp_Blitz, sp_BlitzCache, sp_BlitzFirst, and sp_BlitzIndex with These Tutorial Videos

There have been so many improvements lately to our First Responder Kit scripts that it was time to update our tutorial videos. Each of these is about ten minutes long and shows how to use ’em: How to Use sp_Blitz for a Health Check How to Use sp_BlitzCache to Find Slow Queries How to Use…
Read More

Query Tuning Week: How to Run sp_BlitzCache on a Single Query

The most popular way of using sp_BlitzCache® is to just run it – by default, it shows you the top 10 most CPU-intensive queries that have run on your server recently. Plus, it shows you warnings about each of the queries – if they’re missing indexes, experiencing parameter sniffing issues, running long, running frequently, doing…
Read More