Blogs

Are Bad Statistics Making My Query Slow? (Dear SQL DBA Episode 39)

An important query is suddenly slow. Is it because statistics are out of date? This is tricky to figure out, and updating statistics right away can make troubleshooting even harder. Learn how to use query execution plans to get to the heart of the question and find out if stats are really your problem, or if it’s something else.

In this 35 minute episode:

• 00:39 SQL Server 2017 Announced
• 01:10 New video from Microsoft’s Joe Sack demonstrating Adaptive Query Processing
• 03:05 This week’s question: Are bad stats making my query slow?
• 05:26 Demo of finding plan in cache and analyzing stats begins
• 28:17 What to do when stats ARE the problem

Code samples are at the bottom of the page

Continue reading

Trace Flag 4199: No Per-Session Override if You Enable it Globally

You can enable and disable trace flags either globally or per-session in SQL Server.

This makes it seem like perhaps if you enable optimization trace flag 4199 globally for all sessions, you might be able to disable it per-session.

But that’s NOT how it works.

Continue reading

Index Maintenance and Performance (Dear SQL DBA Episode 38)

They made their index maintenance job smarter, and their queries got slower in production afterward. Could the index maintenance have harmed performance?

Continue reading

Will Optimize for Adhoc Workloads Save Memory?

A while back, I got a question about enabling SQL Server’s ‘Optimize for Adhoc Workloads’ setting. The gist of the question was whether or not enabling this setting might free up extra memory on their SQL Server instance.

Continue reading

Poster in the works: SSMS Shortcuts Explained by Cats

I had a fun highlight recently that I wanted to share. I’ve got a new poster idea I’m crazy about.

Continue reading

Lock Timeouts & Memory (Dear SQL DBA Episode 37)

The dev server got bogged down during a deployment and lock timeouts were everywhere. Was the perfmon counter way off about how much memory was being used just for locks?

Continue reading

Selectively EnableTrace Flag 4199 and QUERY_OPTIMIZER_HOTFIXES in SQL Server 2016

Trace Flag 4199 has been in SQL Server for a while. I’ve long thought of this as the “Bucket of Optimizer Hotfixes” trace flag: enabling it turns on a variety of hotfixes that have been implemented over the years.

Continue reading