Blogs

Using sp_WhoIsActive with Temp Tables to Limit Data Collected

on June 28, 2017

The free sp_WhoIsActive procedure by Adam Machanic can be very useful for monitoring SQL Server: I’m a big fan.

But I’ve learned the hard way that it’s easy to collect too much information with sp_WhoIsActive, particularly if you set up jobs where you have the procedure log data to a table for later review. Collecting the text of SQL Server queries, their execution plans, and more, can take up a lot of room, and I’ve managed to fill up a server drive or two with a little over-eager collection. Whoops!

Join me for Why Did My Clever Index Change Backfire? at the 24 Hours of PASS

on June 23, 2017

You’ve got a performance problem and you know just the cool index trick that will fix it!

Until it goes utterly wrong.

Join me for this free session in the 24 hours of PASS to see multiple quick demos of index changes that seem like a great idea… until they either don’t work, or make things worse.

My session is on Wed, July 19, 9 am Pacific / 12 pm Eastern / 4pm UTC.

How to Enable the Debug Channel in Extended Events for the Query Thread Profile XEvent in SQL Sever 2014+

on June 19, 2017

Microsoft recently gave us a more lightweight way to trace data related to execution plan performance. As of SQL Server 2014 SP2, you can trace the query_thread_profile Extended Event.

How Much Longer Does it Take To Update Statistics with FULLSCAN?

on June 14, 2017

When I was recently working on the course, “Should Developers Manage Index Maintenance?” I explained that in my experience, statistics maintenance can make more of a difference to performance than index maintenance can.

When does a Snapshot Transaction Really Begin?

on June 12, 2017

They say, “never read the comments” on the internet, but I’m lucky to get lots of interesting points and questions in my comments.

When does physical reads include read-ahead reads in SQL Server?

on June 7, 2017

SQL Server has more than one way to pull pages in from disk for your queries. SQL Server can do a physical read of an 8KB page, or an extent of 8 of those 8KB pages.

Top 5 Misleading SQL Server Performance Counters

on June 5, 2017

Perfmon counters are an excellent tool for monitoring and sometimes troubleshooting Microsoft SQL Server. But some counters can get you into trouble, because they don’t mean what many people think. Learn what to look out for in the world of widely-used perfmon counters.