sql-server-technical-posts

It can be tricky to remember that SQL Server doesn’t short circuit on IF statements in stored procedures. Even when you know this, sometimes it’s hard to recognize the impacts.

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.

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

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.

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.

Collecting perfmon counters from SQL Server databases where you are using database mirroring is a little tricky

The counters won’t behave “normally” until after you set up mirroring for a database.

You’ve got some troubling wait stats in SQL Server. How can you tell which queries are causing those waits?

Learn the pros and cons of different techniques to track down the cause of both common and tricky waits in SQL Server, including CXPACKET, PAGEIOLATCH, LCK, RESOURCE_SEMAPHORE, and THREADPOOL waits.

Nonclustered indexes are awesome in SQL Server: they can get you huge performance gains.

But we can’t always create the perfect index for every query. And sometimes when SQL Server finds an index that isn’t quite perfect and decides to use it, it might make your query slower instead of faster.

If you need to add, remove, or replace hints from ad-hoc queries where you can’t change the code, plan guides can help. See a demo of removing a query hint from parameterized TSQL run from an application, and get tips on how to make your plan guides work in SQL Server.

The name of the “SQL Statistics \ Batch Requests/sec” counter is confusing: exactly what is a request? The request is the WHOLE batch. See a demo of this counter in this quick 5 minute video.

What’s batch mode? Batch mode was introduced as a way to help SQL Server process data from columnstore indexes faster. The whole idea with columnstore is that you pull big compressed sets of rows out for aggregation or other operations in big chunks. Batch mode is a way that operators can work on a “batch” of up to 900 values at a time, instead of working on individual rows. Batch mode can reduce the overhead of metadata and make more efficient use of your CPUs.

Every now and again, I need use a global temporary table for some testing or demo code.

Each time I do, I stumble a little bit when it comes to checking for the existence of the global temp table, in order to make my code re-runnable.

Sometimes you need to compare lots of counters at once - for example, counters that report at the database level. This can be frustrating in “Line” view, but the “Report” view in perfmon makes life much simpler.

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.

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.

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.

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.

I recently got an interesting question from a reader about running a CREATE INDEX statement with DROP_EXISTING

I’ve never claimed to be great at math, but until recently I thought I knew how to count to one. Zero… one. That’s what we learned in kindergarten.

Apparently SQL Server didn’t go to kindergarten.