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?
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.
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.
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?
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.
Reviving Healthy Work Habits: Toggl and Weekly Highlights
As I’ve gotten back into the swing of a more-or-less regular work schedule after the initial launch of SQL Workbooks, I’ve revived some of my favorite work habits and free tools. Tracking time with Toggl I’m a big fan of the time-tracking tool Toggl. Their tools are easy to use, and even the free version is very cool. Tracking time may seem like a giant pain if you’re used to using terrible tools for it.
Which Locks Count Toward Lock Escalation?
A little while back I wrote about Why Indexes Reduce Locks for Update and Delete Queries.
I got a great question on the post from Zac:
What’s not super clear is why it takes out a lock on the whole table, is this because it does a lock escalation as a result of the Full Scan? Will this always happen, or is there a threshold of record update counts where this will occur?
This was tough to answer in just a comment, so I promised a full post on the topic.
Why is My Transaction Log Growing in My Availability Group? (Dear SQL DBA Episode 36)
A database transaction log is expanding, even though the DBA is running log backups and doesn’t see an open transaction? What’s going on with this Availability Group?
The Case of the Blocking Online Index Create- the Shared Lock that Would Not Quit
I recently got an interesting question from a reader about running a CREATE INDEX statement with DROP_EXISTING
New weekly newsletter: a SQL Server quiz, poll or cartoon each week
I’ve started up a weekly newsletter! Sign up, and each Tuesday you’ll get a link to a quick SQL Server quiz or poll in your inbox. Most weeks there will be a cartoon thrown in there, too.