SQL Server Blog

I’d be shocked if anyone reading this blog post hasn’t heard someone in their company saying the following… “SQL Server is slow… what’s running on it?”…

That question is the bane of all DBAs lives… but it needn’t be if we have a means of telling what’s currently running on our servers…

Now, this post is one that I’ve had in the pipeline for quite a while but I’ve always been VERY reluctant to write it… why, I hear you ask… well it’s because it’s incredibly dangerous…

We’ve all seen the “Missing Index” hint in our query plans (and if you haven’t then contact me and I’ll fill you in)… and I hope we’ve all taken them with a pinch of salt… but sadly MANY, MANY people don’t… and if you’re one of those then please read the many caveats in this post…

This is another query that I’ve been asked… although I have to admit that I’m a little confused as to why because there’s a built in report that achieves the same result… but I’ve never shied away from a challenge, so I was happy to oblige.

Basically what the asker required was a simple table which returned the output of sp_spaceUsed in a table and for every table in the database…

Well… now that I’ve got a shiny new website (see previous post)… it’s back to the task of posting about SQL Server again…

So… recently I was asked how to find out what was being stored in a specific filegroup. This came from a situation in which someone was trying to remove a filegroup from their database but was getting an error stating that it wasn’t empty.

This is obviously not a SQL Server based post, but it does explain the lull in blogs over the last few weeks… basically I realised that my website was a little outdated and things had grown to the point where I needed a web presence which required an actual skillset to produce and design rather than my feeble efforts with CSS.

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.

Well, luckily, there is a stored procedure that Microsoft have built into SQL Server which you can use to answer exactly that question…

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.

Therefore I’ve written some code that can do exactly that…

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.



However, this is most definitely not where our interest should end… wouldn’t you like to know what’s in the Buffer Pool? Ie. What’s taking up this most precious of space? Here’s how you know…

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.

Therefore this script will allow you to automate the process and therefore have a piece of code which will you can run months, maybe years, apart and it will automatically cater for changes to the underlying architecture.