Single Column Indexes Rarely Work
Produced: 06/11/2016 22:20:00
Again, following on from my previous posts about Key Lookups, I wanted to dispel a myth that I see used in production systems everywhere… that single column indexes are useful. In the majority of cases they just plain aren’t. Get used to it.

Continued...
Performance Impact of a Key Lookup
Produced: 30/10/2016 22:16:00
Following on from my last post about Key Lookups I wanted to emphasize the performance problems that you can get when these appear in your execution plans.

Continued...
What is a Key Lookup?
Produced: 23/10/2016 22:12:00
I was asked this the other day and it’s actually something that I’m surprised hasn’t come up before as people all seem to know it’s a bad thing, but many people don’t know why and don’t even know what one is in the first place.

Continued...

Continued...
Online Partition Index Rebuild
Produced: 07/08/2016 18:26:00
Yet more partitioning stuff… this time it’s the fact that we can rebuild an index on just a single partition of a table… and online. Now that’s handy!!!

Continued...
Unique Index with Union All
Produced: 18/10/2015 09:36:00
This follows on directly from the previous post in regard to ensuring that you use UNIQUE if possible on an index as I’ve also found that not having UNIQUE can make quite a hefty difference when using UNION ALL.

Continued...
Create UNIQUE Clustered Index
Produced: 11/10/2015 10:55:00
This is a bug bear of mine but something which I see all over the place in people’s code… if you’re going to create a clustered index on a table (including temp tables) and the key you’re using is unique, then let SQL Server know. You’ll be surprised how much difference it makes.

Continued...
How to obtain the size of a specific Index
Produced: 08/02/2015 09:31:00
We know that sp_spaceUsed is a great way to obtain a few simple figures about a table such as row count, table size, and index size… but these are all cumulative. Therefore what happens if you want to know the specific size of an individual index?

Continued...
Fill Factor With Large Row Sizes
Produced: 26/10/2014 08:47:00
This is just a little something to look out for when administrating a database as I’ve seen it catch a few people out.

Basically it’s all about making sure you have the correct fill factor for your indexes so that you don’t unnecessarily bloat your databases as a whole.

Continued...
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"

Continued...
SHRINK a data file? Just say NO!!!
Produced: 22/12/2013 14:47:00
DBCC SHRINKFILE, DBCC SHRINKDATABASE, and Auto-shrink… they’re all truly, truly evil and should not be allowed near any system… ever!!!

Now, to be fair there are times when shrinking a log file is useful and even necessary. I’ve written my fair share of bug riddled code which has caused the transaction log to spiral into oblivion and leave me with a 20GB data file and 500GB of logs. In that case… shrink away. It’s really quite handy. But data files… no… just no… again no… please just no!!!!!!

Continued...
Replication and the Disabled Index
Produced: 24/11/2013 17:53:00
This is something that I never thought to blog about as I figured it was a common practice. I’ve no idea where I got that idea from as I’d never spoken to anyone about it, just used it within a couple of environments.

Anyway, having spoken to a couple of MCMs about this it turns out my “common practice” is actually incredibly rare and not many people know about it. Therefore, presuming this to be true, I figured I should blog about it.

Continued...