SQL Server Blog

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.



Basically it involves the practice of creating a temporary table (or permanent table) and only populating the columns in stages as the data becomes available. I’ll explain this with an example.

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.

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.

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.

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.

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.

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.

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.



By default SQL Server doesn’t hold a very long job history for your server… but this is because the guys who wrote it can’t predict how you’re going to use your instance. But this is something that is very easy to rectify.

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.