Category: Administration

Thomas Rushton shows what happens when your clustered index is not unique and you have a lot of time to kill:

The theory behind clustered indexes is that they are (usually) unique – after all, they define the logical layout of your table on disk. And if you have multiple records with the same clustering index key, then which order would they be in? If you don’t define the CI as unique, then SQL Server will add (behind the scenes) a so-called “Uniqueifier” (or maybe “uniquifier”) to fix that. Grant’s first post in the thread referenced above gives some information about how to see this Uniqu[e]ifier in the table structure itself.

Read the whole thing.

Kendra Little describes five Performance Monitor counters whose metrics aren’t quite as straightforward as you’d think:

SQLServer: Locks – Average Wait Time (ms); Lock Wait Time (ms);  Lock Waits/sec

The idea: Alert if you have high lock waits

The problem: These counters update when the lock wait ends, not while it’s ongoing. Let’s say you have a query that’s blocked for five minutes. You’ll only see this in the perf monitor when it becomes un-blocked.

These counters can cause confusion and make you look for blocking queries at the wrong times.

Kendra provides better solutions for each of the five perfmon counters, so read the whole thing.

Daniel Janik shows us two ways to create our own Perfmon counters, the good way and the not-as-good way:

Whether you’re just now finding out about these or you’ve been reminded that these exist, I’m sure your asking: “How can I use them?”

Some many years ago, I used the procedures in SQL 2000 to monitor blocked processes. I was onsite at a customer in San Jose, Costa Rica and I figured pictures would help bridge the gap since I didn’t speak Spanish.

The customer was experiencing outages that they could see with blocking. While investigating I found that blocking was directly related to disk latency. More Latency = More Blocking.

I was unaware that this even existed, but it could be very useful in some circumstances.

Jason Brimhall explains what SQL Server endpoints are:

You may have heard the term endpoints thrown around in technical discussion and wondered “what the heck is an endpoint?” Well, that is a good question. An endpoint in the simplest form is a connection or point of entry into SQL server.

Another way of thinking about an endpoint is to look at the ends of a line. Generally speaking, there is a stop point at each end of the line. At this stopping point, the line may connect to something else and therefore be a point of entry into that “something else”.

Read on for more information, as well as a script to show who owns each endpoint.

Brent Ozar shows a limitation in the sys.dm_exec_query_plan DMV:

The query with six joins has a cached plan that you can click on – because it’s from sys.dm_exec_query_plan, a management object that returns execution plans as XML.

The query with seven joins does not. Its cached plan is too large or complex for that DMV. Microsoft quickly realized this problem when 2005 came out, so in 2005 Service Pack 2, they introduced sys.dm_exec_text_query_plan. That function returns bigger data – but it only comes back in text format.

There are a couple of workarounds, at least, but they aren’t ideal.

Jonathan Kehayias walks through some common Distributed Replay errors and how to solve them:

On the Distributed Replay Controller machine, permissions need to be set in Component Services to allow the Distributed Replay Client Service accounts Launch and Activate permissions remotely on the COM component. The service accounts also need to be in the Distributed COM Users group in Windows. So in Component Services, expand Computers > My Computer > DCOM Config > DReplayController and right-click and open the Properties for the COM Component.

A lot of this boils down to permissions, but the error messages are about as unhelpful as they get.

Paul Randal describes a few things that can change behind the scenes when you run a SELECT query:

Statistics Update

If the database property Auto Update Statistics is set to True, when a query is being compiled and a necessary statistic is determined to be out-of-date, it will be automatically updated before optimization continues, thus changing the database. Your SELECT statement could cause this to happen. Additionally, if the Auto Update Statistics Asynchronously property is enabled, the statistic will be automatically updated, but after the optimization process (so the compiling query doesn’t have to wait).

Read on for a few more activities.