Tempdb configuration and performance

How to modify SQL Server's tempdb database configuration for maximum performance by utilising file striping and traceflags to control autogrow and disk allocation to reduce metadata (PFS page) contention.

This is hardly a new topic and whilst there are a lot of articles out there instructing us on how to stripe tempdb etc I feel there’s a bit of a gap on exactly how to identify a problem with tempdb in the first place and particularly on finding out how much of your overall […]

Stick your Fusions in the right slot!

Selecting the correct PCI slot for FusionIO card installation

Finally got some breathing space for my first post of the year! In order to get a major client through Christmas which is traditionally their busiest period of the year (where load normally goes up five-fold), we embarked on a major platform refresh where both the back end SQL Server DBMS was upgraded from SQL […]

Tracking SQL Server IO performance with Performance Monitor (Perfmon)

Carrying on my series of posts on SQL Server and Disk IO it’s time to cover the old stalwart that is perfmon (referred to in Windows as Performance Monitor) which I know anyone who has delved into any Windows performance issue will have some familiarity with, so I’m not going to cover what it is […]

File fragmentation in SQL Server

How physical file fragmentation in SQL Server can cause database performance issues and how to monitor and resolve the file fragmentation.

File fragmentation? What file fragmentation? A few years back a SAN admin told me not to worry about physical file fragmentation because “The SAN takes care of it” and I took them at their word and, probably along with a lot of fellow DBAs who have their back-ends (in a manner of speaking) on  a […]

Tracking SQL Server IO performance

Tracking SQL Server IO performance can give valuable insight on database files that have become hotspots and this post shows how to track them down.

I’ve finally got around to this, the third part of my SQL Server and Disk IO series of posts: The sys.dm_io_virtual_file_stats DMV and fn_virtualfilestats function are great tools for extracting granular information about SQL Server IO performance, right down to the individual file in each database. It gives details of the number of physical reads/writes, […]

Impact of Fusion-io based tempdb on reindex duration

Tuning tempdb performance with FusionIO cards is a given but you may not be taking full advantage if reindexing is left on default settings.

Do you have tempdb on Fusion-io (or equivalent) technology? Is your reindexing job left on default settings (re-orgs at 5-15% and full rebuilds at @ 30%)? If so, then you might be interested in the findings in this post. The benefits of flash memory as a replacement for spinning drives in order to boost SQL […]

SQL Server wait stats

SQL Server wait statistics provide the most objective indication of the perfomance of a SQL Server instance and this post explores how to use them to get more details about performance issues.

The single biggest clue to the source of a performance problem will be from something referred to as the wait stats. What are waitstats? In short wait stats are statistics gathered on what resources SQL Server is waiting on internally whilst executing queries. Why are they useful? They can often provide very quick and objective […]

SQL Server and Disk IO

Disk IO against traditional spinning disks is the most critical factor affecting SQL Server performance and this post starts to explores how to mitigate disk performance issues

Many aspects of a database system’s configuration will affect the performance of queries running on that DBMS. However, there is one single component that has the greatest impact on DBMS performance, and that, of course, is disk (IO) access. It’s a broad topic and all aspects of it are pretty well covered in various blogs […]

Misaligned disk partition offsets and SQL Server Performance

I’m starting off this series of posts with a discussion about partition offsets. Diving off at the deep end a bit perhaps, but if your disk setup is not based on firm foundations, you’re setting yourself up for one of the most common and easily-avoidable performance issues right from the off, and a IO performance […]

How many CPU cores can SQL Server use?

Ever wondered how many CPU cores SQL Server is actually using? Here's a simple query that will display this information.

SQL Server and CPU core usage How many CPU cores can SQL Server support should be a simple question, you might think. However, judging by how often this question is asked on the SQL Server forums its clear that there is a lot of confusion about exactly how many CPUs a particular edition of SQL […]