How Effective Is Data Compression?
Produced: 01/11/2017 07:07:00
In this day and age with such a prolific speed in processor it’s relatively rare (although not unheard of by any stretch) that I hear people saying that they don’t have the CPU resources to run compression on their databases… but what I do regularly hear is people asking what space saving benefits it will give them.

Which Table or Index is Hogging the Buffer Pool?
Produced: 25/10/2017 14:37:00
In the last post I showed how we can easily tell which database is hogging our precious resources but, although that’s useful in helping us narrow down rogue processes, it would be a lot more helpful to be able to go a step further, picking out the exact table or index.

What's Using the Buffer Pool?
Produced: 18/10/2017 11:09:00
We all know that SQL Server stores data in memory in a place it calls the Buffer Pool (and if you don’t, then speak up and I’ll explain) but, for a lot of us, that’s where our knowledge ends.

Dynamically Script a Table
Produced: 08/10/2017 11:29:47
This is a useful script that will let you dynamically script a table… yes, I realise that SSMS offers a point and click function which does the same, but if you’re building a script that you wish to run over and over on a changing architecture then you can’t afford to spend your time scripting from SSMS and maintaining all changes to the scripts over time.

Moving a Partition to a Different Filegroup
Produced: 01/10/2017 11:25:33
This is a very common thing to need to do and will put together the pieces of my last two posts into one large post in order to achieve the desired outcome.

Split a Partition
Produced: 24/09/2017 11:22:08
Cleary this is a direct follow-up to my last post in which I showed how to merge a partition… this is the exact opposite in which we’ll split a partition into 2.

Merge a Partition
Produced: 17/09/2017 10:54:13
I’m going to briefly dip back into Partitioning for a few posts because this is something I was working on recently and I realized I didn’t have the code to hand on my blog… not that I rely on all code being present, but it’s definitely easier having something to hand than not.

Script to Update Specific Stats
Produced: 10/09/2017 12:27:00
In my previous post I was stating that we need to be more intelligent with our stats updates rather than hitting a whole table with a sledgehammer and potentially suffering poor performance as a result.

Only Update Stats You Need
Produced: 03/09/2017 12:22:00
I’ve had a LOT of issues with stats over the years, mostly due to ascending keys, and I’m not the only one. However, the way in which I see people try to deal with these issues is quite interesting.

Setting Trigger Order
Produced: 27/08/2017 12:19:00
This is something that comes up rarely, but when it does it can be a real pain.

I’ve seen situations in which you have multiple triggers on the same table and these Triggers could also be set to fire from the same action.

This can cause a lot of issues.

How Inserted and Deleted Tables Work
Produced: 20/08/2017 12:16:00
This is another post about Triggers, but in this one we’re going to focus more on the Inserted and Deleted tables that a Trigger has access to.

SQL Server Trigger Examples
Produced: 13/08/2017 12:15:00
As mentioned in my previous post, we have 2 types of Trigger available in SQL Server… AFTER and INSTEAD OF.

In this post I’m going to go through a few examples to show syntax and a couple of uses.

SQL Server Triggers
Produced: 06/08/2017 12:10:00
Triggers are kind of like stored procedures… they’re simply pieces of code that can execute based on another event… ie. They’re “triggered” by another event.

Practical Applications of Extended Properties
Produced: 30/07/2017 12:01:00
So you might be wondering what use Extended Properties could have outside of the simplistic “this helps explain columns” approach that I mentioned?

Querying Extended Properties
Produced: 23/07/2017 11:58:00
Now you know all about Extended Properties, I’ll provide a quick script that allows you to query them effectively and quickly.

Adding and Removing Extended Properties
Produced: 16/07/2017 11:54:00
This is an obvious continuation of my previous post about Extended Properties in which I will show you how easy it is to add and remove Extended Properties from a table in SQL Server.

What Are Extended Properties
Produced: 09/07/2017 11:50:00
Extended Properties are a feature of SQL Server which are, in my opinion at least, massively underutilized and should be embraced a lot more frequently.

Statistics On Views Via Linked Server
Produced: 02/07/2017 17:51:00
As a follow-up to my last post about accessing synonyms via a linked server (you can’t), I stated that it was therefore better to use a view. However, that has issues of its own to consider.