Basic Look At Statistics
Produced: 31/08/2014 11:42:00
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.

Statistics Affecting Query Performance
Produced: 24/08/2014 11:36:00
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.

Memory Grant Affecting Query Performance
Produced: 17/08/2014 11:31:00
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.

What a Difference Caching Makes
Produced: 10/08/2014 11:17:00
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.

Clustered Index vs Non-Clustered on Heap
Produced: 03/08/2014 10:46:00
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"