Accessing Synonyms Via Linked Server
Produced: 25/06/2017 17:48:00
This was new to me and something which has appeared as a major downside for me when considering whether to use synonyms or views within my databases.

Should I Use Synonyms or Views
Produced: 18/06/2017 17:44:00
This is a discussion I’ve had recently and it’s a bit of a weird one because there are actually very few times in which I would ever use a synonym, more often than not I would favour the view.

Single Column Indexes Rarely Work
Produced: 11/06/2017 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.

Performance Impact of a Key Lookup
Produced: 04/06/2017 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.

What is a Key Lookup?
Produced: 28/05/2017 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.

Piecemeal Database Restore
Produced: 21/05/2017 16:16:00
This is a direct follow up from my last post about filegroups because without those we wouldn’t be able to perform what is called a Piecemeal database restore and, trust me, this is something you want to know how to do.

What Are Filegroups
Produced: 14/05/2017 16:13:00
Well, let’s be honest here… in a nutshell a Filegroup is… wait for it… a group of files.

Bet you didn’t see that one coming.

SQL 2016 Install Sets Multiple TempDB Files
Produced: 07/05/2017 16:10:00
This is something that I’ve been hoping would make an appearance in SQL Server at some point, and also why I included my last post about tempDB contention. It’s the ability to choose the number of tempDB files during install rather than having to add them yourself at a later date.

Contention and Multiple TempDB Files
Produced: 30/04/2017 16:07:00
We all know that tempDB is the scratchpad for SQL Server and is used for all manner of things from, obviously, housing our temp tables through to being used for memory spills during query execution.

Creating a Partitioned View
Produced: 23/04/2017 09:08:00
This is an alternative to native table Partitioning, and something I would potentially rather use instead whether I have Enterprise Edition or not.

Clustered Index Orders Pages Not Rows
Produced: 16/04/2017 09:01:00
This is something I’ve had mentioned to me a lot recently… that a clustered index ensures that records must be stored in order on the data pages.

But it’s just not true.

Handy Trick in SSMS
Produced: 09/04/2017 14:50:00
This is a quick post about something I only found out recently and which has made my life so much easier in many circumstances. It’s something I also imagine you’ll soon use a lot too if you don’t already.

Bug: Implementing Incremental Statistics
Produced: 02/04/2017 18:36:00
I’ve covered Incremental Statistics over a couple of posts now and although they are a fantastic feature, there is a bug which you need to be aware of. Luckily it’s by no means a showstopper, more just an annoyance.

Incremental Statistics Performance
Produced: 26/03/2017 18:33:00
Based on my last post we now know how to create incremental statistics on a partitioned table and I mentioned that it was a massive improvement for performance of maintenance, but just how much?

The Awesomeness of Incremental Statistics
Produced: 19/03/2017 18:29:00
I did a post a while ago about why you need to be careful with Sampled Statistics and therefore why a FULLSCAN is really the only reliable way to go… BUT there has always been the problem that a FULLSCAN update can take a VERY long time.

Oh, and yes… this is also to do with partitioning!!!

Online Partition Index Rebuild
Produced: 12/03/2017 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!!!

Individual Partition Compression
Produced: 05/03/2017 18:23:00
To continue with partitioning there’s another good feature to consider which involves combining it with another Enterprise feature… compression.

How to Find Partition Range Values
Produced: 26/02/2017 18:19:00
This is something I struggled to put together the first time I needed it because partitioning uses internal tables with some very strange IDs and even stranger links between tables.

Quick Guide to Partition Switching
Produced: 19/02/2017 18:16:00
Partition Switching is an incredibly fast way to insert or delete data from a partitioned table and works very well in data warehousing environments, for example in which large overnight loads take place.

Partition Elimination in Action
Produced: 12/02/2017 18:13:00
There are many reasons to use partitioning in order to make maintenance easier (stats and reindexing) and allowing for partition switching (fast loading and deleting of data), but there is another which is Partition Elimination.

How to Partition a Table
Produced: 05/02/2017 18:10:00
Partitioning is an Enterprise only feature and, if you happen to be lucky enough to be using Enterprise, it can be an incredibly powerful and useful feature.

Test for Compression Savings
Produced: 29/01/2017 19:21:00
This is actually an addendum to my last post as I referred to using SQL compression and provided a script in which you could compress a set of tables.

However, the problem is knowing which tables you should compress?

Script to Compress All Tables
Produced: 22/01/2017 19:19:00
This is a simple script that’s held on my website mostly for reference as it simply takes any table and then compresses it with PAGE level compression.

Columnstore A Table
Produced: 15/01/2017 19:15:00
At some point I will explain the nuances of the Columnstore index, how they work, when and where you should use them, and what they’re for… but in the mean time I have recently had situations in which I have upgraded to SQL 2014 and have needed to Columnstore a table.

Rolling Back to a Database Snapshot
Produced: 08/01/2017 22:06:00
One of the best features of a database snapshot is the ability to roll back your changes and revert to the snapshot.

Creating a Database Snapshot
Produced: 01/01/2017 22:04:00
This is pretty simple really, but it’s useful code and therefore I wanted it on my website mainly for my reference on those days in which I can’t remember the syntax and want a simple copy and paste option. We all have our forgetful moments after all :o)