Access a Network Drive Using SQL Server
Produced: 25/12/2015 07:38:00
This is a handy hint as to how to access a network drive from within SQL Server. It just happened to be something that I was using in some code and was asked what the code was doing.

Grant Execute To All Procedures
Produced: 18/12/2015 07:34:00
I’ve been in numerous situations now in which people require security in their SQL Server (obviously) but find that the provided db_dataReader and db_dataWriter aren’t quite good enough. They require execute rights on all procedures, but find this is hard to set up.

Current State of Scheduled Jobs
Produced: 11/12/2015 07:22:00
This is another useful piece of code (well, I find it handy anyway) which I wrote to help populate a dashboard.

Gradual Database Shrink
Produced: 04/12/2015 07:17:00
First things first… NEVER shrink your database unless you REALLY have to.

Vertical Partitioning Using sp_tableOption
Produced: 27/11/2015 09:48:00
This was an incredibly cool thing that I learnt the other week whilst attending a SQLSkills course. I have previously made mention of Vertical Partioning and how useful it can be, BUT it does have the downside of re-architecting… this can change that.

Check SQL CPU Utilisation vs Other
Produced: 20/11/2015 09:46:00
This is a simple piece of code pinched from the Microsoft Performance Dashboard, but it’s useful and needs highlighting on its own as I tend to use it extensively.

Instant File Initialization
Produced: 13/11/2015 18:19:00
This is actually a Windows level setting, but something that is hugely beneficial to a SQL Server and should be enabled where possible.

Naming Constraints on Temp Tables
Produced: 06/11/2015 18:13:00
This post comes about due to my spending an annoying large amount of time on a code failure that turned out to be utterly infuriating, but ultimately interesting as well. Immersion Event
Produced: 30/10/2015 18:06:00
I’ve been wanting to attend one of these ultimate training courses from Paul Randal and Kimberly Tripp for a long time now and finally I managed to get the time off, the money together and book myself on.

Therefore at the beginning of this month I was out in Chicago partaking in some of the most intensive, yet enjoyable, SQL training money can buy. (Aside from my own, of course :o))

Wait Stats Per Query
Produced: 23/10/2015 09:35:00
This was something that I have wanted to be able to do for a while. I like having wait stats to look at (I’ll do a blog about those at some point - this is for people who already know what they are)… but until Extended Events came about these were only available, to my knowledge, at the server level. This was great, but what if you wanted to know what your specific query was doing?

Optimize For Ad-Hoc Workloads
Produced: 16/10/2015 09:18:00
Since finding out about this option it has been an sp_configure setting that I’ve generally recommended everyone turns on within their SQL Server.

Every Statement Is Parameterized
Produced: 09/10/2015 10:15:00
This is a common misconception that I hear from devs and DBAs alike… people seem to believe that SQL Server will parameterize every statement it runs and store the plan for re-use later.

Updating Varchar Column Values In Replicated Tables
Produced: 02/10/2015 08:01:00
This is just a quick post as it’s something I came across the other day and I thought it warranted a note and word of caution.

Easily Baseline Your Servers
Produced: 25/09/2015 12:04:00
This is a common requirement in all companies… servers must be baselined otherwise how do you know if changes are detrimental or beneficial? How do you know where to look if something suddenly goes wrong?

Produced: 18/09/2015 18:46:00
This was something I created due to being fed up with constantly having to set up Profiler, having it “forget” my custom traces, and also wanting the data in SSMS so that I could query it if necessary.

SSIS Dynamic String Connection
Produced: 11/09/2015 14:44:00
This is something that’s really useful to know. It threw me for a little while when I first thought of doing it, but now it’s something I use quite frequently and to good effect. It’s really simple to set up once you know what you’re doing.

SSIS Pass Variable Between SQL Tasks
Produced: 04/09/2015 11:32:00
This is something that I found tricky when I first started to work with SSIS packages and something that I regularly see asked on the internet, therefore I figured it was something worth documenting.

SSIS Basic Package
Produced: 28/08/2015 10:46:00
I’ve been using a lot of SSIS recently and whilst doing so I figured I should really add a few basics to the blog in case anyone either hasn’t used it before or simply hasn’t used it for a while and wants a few pointers.

How to obtain the size of a specific Index
Produced: 21/08/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?

Part Populating Temp Tables
Produced: 14/08/2015 19:43:00
The other day I was going through some code looking for a reduction in reads (as they were rather higher than expected), and whilst doing so I came across a piece of code that seems logical yet is actually quite a performance killer.

SQL Server Agent Job Ownership
Produced: 07/08/2015 19:57:00
I’ve come across a few issues recently with regard to the ownership of SQL Server Agent jobs which caused some scheduled tasks to fail and therefore critical maintenance tasks weren’t carried out. Therefore I thought I would share my thoughts on Job Ownership.

CTEs - Formatting Not Performance
Produced: 31/07/2015 19:13:00
I was reading a forum the other day in which someone was asking whether they would be better off changing their code to use CTEs for performance gains. I was surprised by the amount of responses by people thinking that CTEs are a performance tool.

Database Consistency Checks
Produced: 24/07/2015 19:27:00
This is vital to the health and safety of your data and databases. Running regular consistency checks will allow you to spot any corruption within your database at any and all levels. Nearly all databases will have some element of corruption at some time or other and therefore you need to be prepared.

Critical Alerting Costs Nothing
Produced: 17/07/2015 09:53:00
I’ve yet to come across a SQL Server which actually makes use of some of the free alerts which SQL Server will hand you if you let it. These are alerts which are already logged inside of SQL Server and are just waiting for you to set up some notifications around them.

Job History Timeout
Produced: 10/07/2015 09:37:00
This is actually an extension to my previous No Job History Showing post in which I pointed out how easy it is to end up without any job history in your SQL Server Agent and therefore not be able to diagnose problems.

Obtain Job Name from sp_who2
Produced: 03/07/2015 23:25:00
This is a personal pet hate of mine and of many people I’ve spoken to… we have all these lovely tools to obtain a list of what’s happening on our SQL Server but invariably sp_who, sp_who2, and sysprocesses all find it highly entertaining to provide you with a seemingly encrypted result instead of a job name.