SQL Server Blog

This is an incredibly valuable, and free, tool you can use with SQL Server in order to get a real feel as to how your server is performing, where to look for issues, and miscellaneous other nuggets of information.

Ever been in one of those situations in which you see endless blocking whilst replication makes new generations and you’re left wondering if there’s a specific table causing it or whether it’s just generic replication volume which is causing your problem?



Well the easiest way to tell is to have a look and see what commands are yet to be processed. Sadly tables such as msMerge_contents aren’t the easiest things to immediately decipher, but with a little code it’s not that difficult at all.

This is a trace flag I only learnt about recently which actually fixes something that has been a large bug bear of mine for quite some time.



Basically we all know that statistics are one of the most important things in SQL Server and therefore it’s also critical to know how they’re maintained.

You’re running a query and you’re looking at the execution plan… it’s all well and good, there are tables, indexes, cardinality estimates… all manner of information. We know these are all derived from statistics and that our statistics should be kept up to date. But the question is… exactly which statistics were used or considered by the optimizer?



This is where these trace flags come into their own… they will give you just that information.

People are always mentioning Statistics… “Keep your statistics up to date” etc. but, although in my last post I gave a good example as to why you really should keep them up to date, there aren’t that many places explaining what Statistics actually are. Therefore I figured I would give a quick overview.

Another way in which query performance can suffer is down to statistics. These are the numbers and mathematical information that SQL Server holds in regard to indexes and tables. By using these SQL Server estimates the number of rows a query will return at each stage of the execution plan.



Using these numbers SQL Server calculates what volume of data will be processed (record size etc), how much memory grant to allocate, and which joins to use at each step of the way. Therefore if these statistics are incorrect and out of date it can lead SQL Server to perform some crazy things.

My last post about how much difference caching can make to a query left me thinking of other reasons that can cause a query to hand you a seemingly random set of execution times. This is one of the reasons I came up with… memory grants.



The two main blocking operators in an execution plan are SORT and HASH JOIN. These are the most common reasons a query will require a memory grant because neither operator can complete until it has all rows present and therefore it requires memory to store the rows whilst it performs its action.

I was visited the other day by an irate developer who was complaining that he couldn’t get consistent results from his testing and queries were running in anything from 5 to 30 seconds and he thought there was something wrong with SQL Server.



As it turns out, there was nothing wrong with SQL Server, it was simply down to caching. He was running his tests on a variety of servers including a few which have incredible data throughput and relatively little RAM… therefore some servers were retaining his test data in cache, others simply couldn’t.

I had a question recently in which someone asked the following:



"I'm trying to determine what the overall performance difference would be in the following situation…


Assume I have a large table, 500M records that have a non-unique RecordID column (eg. RecordID (BIGINT), SubID (BIGINT), Name, Detail)


I'm only ever going to select * from Table where RecordID = ?


If I create a clustedIndex on RecordID the execution plan shows only two steps


Select + Clustered Index Seek


If I leave the table as a HEAP and create a no-clustered index on RecordID the execution plan shows 5 steps


Nested Loops Inner Join <- ((Compute Scaler + Index Seek) + RID Lookup)


Clearly I'm going to get better insert performance when inserting into the HEAP, especially when page spits are required on the clustered index.


What I don't know is what the select performance difference would be under load.


e.g. I can expect the RID lookup to be X% slower than the Clustered Index Seek"



The answer I gave was as follows: