SQL Centre of Excellence

(Script can be downloaded here)


TempDB is the work horse of most SQL Servers, if a query is doing something wrong, or just being a resource hog, chances are it’s TempDB picking up the slack. It’s a safe bet that the majority of queries running on your system are using TempDB in some way. (Good technet article hereon how TempDB is used)

So it pays to give your TempDB some TLC. Starting out as a DBA I found it pretty confusing as to what the “Best Practices” actually were when it came to TempDB configuration, as there are many differing opinions. So here is a script I’ve put together with what I’m going to call my “Recommended Practices”, rather than “Best Practices”. In my experience structuring your layout to get green lights on each of these checks has yielded the best performance and stability, but your mileage may vary!

What am I checking, and why?

The script performs 6 checks, and I’ll break them down below:


1. If you have multiple CPU’s, have you got multiple TempDB data files?

On a multi core system, it is recommended to have multiple TempDB data files. This is to reduce the contention of threads requiring TempDB space trying to hit a single file. The official recommendation from Microsoft is to have 1 TempDB file for each CPU/Core in the server.

Now this is massive overkill, particularly on large multi core boxes, so a ratio of 1 File for every 4 cores is a good place to start, potentially rising to a 1:2 ratio if you still have some hotspots. Paul Randal wrote an excellent blog on this already, so I won’t repeat the effort.

This check measures the ratio of CPU’s to TempDB data files, and reports back accordingly


2. If you have multiple TempDB Data files, are they all sized equally?

If you have multiple files, you want to ensure they are all sized equally to ensure the Round Robin effect is in play, which gives you that performance boost. Again Paul discusses this in the previous blog link, so I won’t duplicate on the reasoning.

This check ensures that if multiple files exist, they are all equally sized, and if not, it has a loud whinge.

3. Do you have TF1118 enabled?

This is a contentious one, as it’s not necessarily a best practice, but I’ve found it really helps concurrency in any situation I’ve had issues in. This checks if TF1118, which disables mixed extents (Over to Paul Randal for this one again blog), and in my experience enhances concurrency.

One important note here, this disables mixed extents server wide, not just for TempDB. This isn’t a worry in most cases as disk space isn’t as expensive any more, but just be aware of this.

4. If you have multiple data files, do you have TF1117 enabled?

This trace flag ensures that if one data file experiences a growth event, then all data files grow equally. This means that file growth on a data file doesn’t effectively take it out of the Round Robin pool, as all files must be sized equally to be considered.

You should be pre-allocating anyway, but this trace flag will ensure you don’t take a performance hit if one or more of your files grows.

Again this is server wide, so if you have any user DB’s with multiple files in a Filegroup, they will exhibit the same behaviour

5. Has TempDB data file experienced any auto growth events

You should be pre-allocating space for these files where possible, but this is a check to make sure you got it right. If you have no auto growth events, then you are sized right for your system. If you do, it may be worth taking a look at how much it has grown, and re-evaluate your sizing.

6. Are the TempDB data files on their own storage

TempDB is generally the most I/O intensive database in an instance, and it can be very heavy random I/O. While a lot of workloads are now on SAN’s, it still makes sense to separate TempDB from all of your other files. This is from a management perspective within the OS, but also if you are using tiered storage, such as the EMC VNX, the partition holding TempDB can be placed on the higher tier if it’s out on its own, without having to move all of your user DB’s too.

It’s a recommended practice from me, but if you’re having no issues and you are within your I/O thresholds, then this is by no means a must do.


So there you have it, some recommended practices, and a way to give your TempDB a quick health check. If anyone has any feedback, questions or comments, or rebuttals, on any of this, as always I’d love to hear it.


Firstly, Hello all!

I’ve decided that after years of learning and “borrowing” ideas, info and techniques from other peoples blogs, it might be about time

I put one up and share some of the interesting, useful or just downright odd things I have come across in my years working in IT and particularly with SQL Server.

Hopefully you will find some of these blogs useful, interesting, or at the very least give you something to argue with me over!

Back Story – I work as a SQL Consultant for Prodata, who are the top SQL Server & BI consultants in Ireland (shameless plug!) .

My main area of expertise is as a production DBA, but I also dip into BI, SSIS, Office (well, excel) a little virtualization and general IT work.


Today’s blog is concerned with SQL Server Views, or more the issues that are encountered when using views, and how to sidestep them.


For this example I am using a subset of the DimCustomers table in AdventureWorksDW2012, available on codeplex. It’s a contrived example but gets the message across.

On running a select * from our Customers table, the results look like this:

Imagine we want to expose this table to certain users/groups via a view. This is something we do pretty regularly when exposing Data Warehouse tables via views to help minimize effect of base table changes on reports:

Create View CustomerInfo


Select from Customers 


It’s probably better to try not to use select * in views in general, but during development it can save a lot of time when schemas are changing regularly.

Running a select * from the view returns the results as shown previously, the view is after all just a select * from the underlying table.


Now, we have a request to alter the Customers Table, so that TotalChildren will be before the BirthDate column, Phone will be before AddressLine1 and LastName will be changed to Surname:


Pretty easy change right? But then look what happens if you execute the view:


The column data for TotalChildren & Phone is in the correct position, however the column headers are completely incorrect!  The birthdate column has the data for TotalChildren underneath it, AddressLine1 has the Phone number data underneath it and LastName has not been updated to Surname!


So what has happened here?

A schema change to the base table does not force a metadata update on the view. If we query the view metadata we see the column order and names reflect those of the table before the table schema change:

The issue is easily resolved by one of two ways:

·        Drop and recreate the view

·        Execute sp_refreshview

Sp_refresh view is usually the preferred solution, and can be run against a specific view or against all views.

This issue can be completely prevented by creating the view with schemabinding, however it can become quite annoying during development to have to remove schema each time a schema change is required, and a refresh of the schema metadata is still required,

however this will happen when you alter the view to re-add schemabinding.


Attached is a short script to demo items discussed.


That’s the first blog out of the way! Phew! Any questions, please just give me a shout.


view_metadata_issues_examples.sql (1.71 kb)


Paradise Lost? Advise Found

by Carmel 3. April 2012 03:28

Help me please my data’s fried

My manager is fuming

My users just cannot get in

Its very time-consuming


Resources have all gone to pot

My memory has amnesia

The processor is eaten up

And the network is freezing!


Our data feeds are going slow

And inserts do not happen

Our dashboards are not up to date

Yeah - Someone’s for a slappin’


Infrastructure took a look,

And bought some new equipment

They assured me things would go

At lightspeed, in an instant


We got a team and hauled it in

New servers, SAN and Rack

We moved the database and app

Improvement? - not jack.


Infrastructure bought some more

Disks, solid state and all

And memory, upgraded to the max

Things better? Not at all


Ah now they said, we have instead

Bought Fusion IO

They  plugged it in, it was a SIN

Performance gain? NO


They asked their devs to take a look

They said it was the SQL

And that it was above their heads

So then they called us people


In we went to take a look

Your database size?, we asked

There was a pause, a big deep breath

A GIGABYTE they gasped


Quite curiously, I gazed

Upon their stored procedure

It was quite long, I will admit

I nearly had a seizure!


With UDF’s, temp tables

And cursors abounding

Trigger-Happy to be sure

It really was astounding!


I took a look at their IO

T’was something like the matrix

Covering Indexes? Not a one

The scanning was CRAZY!


Non-sargable, crossed numa nodes

Blown cache, both plan and dough

A total mess – is this a test?

It really can’t be so!


Using Dynamic SQL doesn't always perform

And no param's is not the norm!

So what to do that is the question?

Maybe they'd take a small suggestion?

When your data fits on a memory stick

More hardware's unlikely to do the trick!!



Page List

Page List