Blogs

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

April 20, 2017

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?

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

in query-tuning, sql-server-technical-posts

April 19, 2017

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.

Index Maintenance and Performance (Dear SQL DBA Episode 38)

in sql

April 13, 2017

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?

in sql-server-configuration

April 12, 2017

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

in sql

April 10, 2017

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)

in sql

April 6, 2017

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

in query-tuning, sql-server-technical-posts

April 5, 2017

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

in sql

April 3, 2017

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?

in locking, sql-server-technical-posts

April 3, 2017

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)

in sql

March 30, 2017

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?