No Job History Showing
Produced: 26/06/2015 22:58:00
This is a common complaint I hear whenever I see a new server for the first time… I see a failed job, go to open the Job History, and am told “oh, there’s never anything in there. Why doesn’t SQL ever hold enough?”. Well, the answer is that it simply hasn’t been told to.

Random Number Generator
Produced: 19/06/2015 13:03:00
Surprisingly this is something that I come across quite often and it’s not the easiest thing to achieve in SQL Server in certain circumstances… a solid piece of code which generates a random number.

Vertical Partitioning To Reduce Reads
Produced: 12/06/2015 18:51:00
Directly following on from my posts about Reads and the 8k Page, Vertical Partitioning your tables can suddenly make a lot more sense and it’s very quick and easy to see how vast improvements can be made to the speed of your queries and their impact on your disks.

How Many Pages In My Table
Produced: 05/06/2015 18:48:00
This is the natural follow-up to my previous post about the 8k page. It’s all well and good knowing that SQL Server works in 8k pages and that these directly influence your read figures, but we don’t want to sit there and calculate how many records there are per page in order to know how many data pages exist in our table.

What is a Read?
Produced: 29/05/2015 12:12:00
I’ve spoken to a good few people now who didn’t actually know what a Read was inside SQL Server. They were discussing tuning of queries and, correctly, knew that less reads were advantageous and therefore that’s what they were looking for in a better performing query, but didn’t really know why.

The 8k Page
Produced: 22/05/2015 10:25:00
This will be a relatively short post simply explaining the way in which SQL Server stores databases records on disk. The reason that I’ve included this post is because I’ve come across a good few people who didn’t realise this was how SQL Server worked and yet this is an important precursor to my next few posts.

Is SQL Server Installed?
Produced: 15/05/2015 09:21:00
This came about due to incredibly bizarre request I received which happened to throw me a little. Basically I was told by a business that they actually had no idea how many SQL Servers they had or on which machines they could be running. They asked if I knew a way to find them.

Fill Factor With Large Row Sizes
Produced: 08/05/2015 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.

Check Your Database Growth Settings
Produced: 01/05/2015 08:43:00
This is another bug-bear of mine… it’s something I believe should always be set at the time of creating a database and then periodically checked and altered where necessary... and this applies not only to the data file but to the log file as well.

Full Backup Does Not Truncate Log
Produced: 24/04/2015 08:30:00
This is a massive misconception that has been going around for a long while and it needs to be wiped out.

Basic Warning About UNION
Produced: 17/04/2015 10:28:00
I was looking for some sample code to help me with a personal C# application I was coding and in looking around I saw a perfect example as to what not to do when manipulating a database.

Performance Dashboard
Produced: 10/04/2015 18:09:00
This is an incredibly valuable, and free, tool you can use with SQL Server in order to get a real feel as to how your server is performing, where to look for issues, and miscellaneous other nuggets of information.

Merge Replication Commands in Pipeline
Produced: 03/04/2015 11:38:00
Ever been in one of those situations in which you see endless blocking whilst replication makes new generations and you’re left wondering if there’s a specific table causing it or whether it’s just generic replication volume which is causing your problem?

Automatic Update Statistics Threshold
Produced: 27/03/2015 17:25:00
This is a trace flag I only learnt about recently which actually fixes something that has been a large bug bear of mine for quite some time.

Basically we all know that statistics are one of the most important things in SQL Server and therefore it’s also critical to know how they’re maintained.

What Statistics Are Being Used
Produced: 20/03/2015 17:18:00
You’re running a query and you’re looking at the execution plan… it’s all well and good, there are tables, indexes, cardinality estimates… all manner of information. We know these are all derived from statistics and that our statistics should be kept up to date. But the question is… exactly which statistics were used or considered by the optimizer?

This is where these trace flags come into their own… they will give you just that information.

Basic Look At Statistics
Produced: 13/03/2015 11:42:00
People are always mentioning Statistics… “Keep your statistics up to date” etc. but, although in my last post I gave a good example as to why you really should keep them up to date, there aren’t that many places explaining what Statistics actually are. Therefore I figured I would give a quick overview.

Statistics Affecting Query Performance
Produced: 06/03/2015 11:36:00
Another way in which query performance can suffer is down to statistics. These are the numbers and mathematical information that SQL Server holds in regard to indexes and tables. By using these SQL Server estimates the number of rows a query will return at each stage of the execution plan.

Memory Grant Affecting Query Performance
Produced: 27/02/2015 11:31:00
My last post about how much difference caching can make to a query left me thinking of other reasons that can cause a query to hand you a seemingly random set of execution times. This is one of the reasons I came up with… memory grants.

What a Difference Caching Makes
Produced: 20/02/2015 11:17:00
I was visited the other day by an irate developer who was complaining that he couldn’t get consistent results from his testing and queries were running in anything from 5 to 30 seconds and he thought there was something wrong with SQL Server.

Clustered Index vs Non-Clustered on Heap
Produced: 13/02/2015 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"


The answer I gave was as follows:

Remove Rogue Large Transactions From Replication
Produced: 06/02/2015 17:40:00
Yesterday I had an issue in which someone had accidentally run a tidy up script on a transactionally replicated table without considering the consequences this would have on replication.

Replication Verbose Logging
Produced: 30/01/2015 17:22:00
I’ve had numerous occasions in which I see something going wrong with replication, but rarely are the error messages clear and, to be honest, most of these messages simply specify something along the line of “enable verbose logging”.

Obtain Start of Day from current_timestamp
Produced: 23/01/2015 17:07:00
This is genuinely one of the things I’m most asked, and by all manner of people from DBAs to Devs because it’s never as simple as it looks and that is how to obtain the start of day from a current_timestamp (or any other datetime value).

Deadlock Alerts via Email
Produced: 16/01/2015 11:36:00
In the second episode of Sheldon Cooper’s Fun with Flags, we’re going to be looking for deadlocks.

There are numerous ways of tracking deadlocks within SQL Server but as we rarely know when they’re going to occur, don’t want traces running all day, and want to react quickly, the best method I’ve found is to have them sent to me in an email each time they appear.

Simple Cluster Failover Checker
Produced: 09/01/2015 16:40:00
Obviously I would be shocked if you don’t already have something in place which will let you know that your SQL Server Cluster has failed over to another node. But I always like to have my own backups to any other automated alerting in order to keep me informed.

Scalar Value Functions and Parallelism
Produced: 02/01/2015 16:38:00
I’ve complained before about Scalar Value Functions and how much I dislike them but, in case you weren’t convinced the first time around, here’s another reason for you.