I recently did a Dear SQL DBA episode answering a question about lock timeouts and memory in SQL Server. I really enjoyed the episode, and thought it would be fun to follow up and show what it looks like if SQL Server doesn’t have enough memory to allocate locks.
The problem: by default, the Performance Monitor application in Windows doesn’t remember which counters you like to use.
This can mean a lot of clicking every single time you open perfmon.
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:
Code samples are at the bottom of the page
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.
They made their index maintenance job smarter, and their queries got slower in production afterward. Could the index maintenance have harmed performance?
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.
I had a fun highlight recently that I wanted to share. I’ve got a new poster idea I’m crazy about.
Copyright (c) 2025, Catalyze SQL, LLC; all rights reserved. Opinions expressed on this site are solely those of Kendra Little of Catalyze SQL, LLC. Content policy: Short excerpts of blog posts (3 sentences) may be republished, but longer excerpts and artwork cannot be shared without explicit permission.