When will my DBCC complete?

On a large database a DBCC can take a while to complete. Rather than terminate a long running check this query provides a way of getting an estimate of the completion time of the DBCC

I was waiting to implement a change the other day and found the DBCC job was still running. As this was a production system I was not too keen on cancelling the DBCC check. I thought about restarting it after I had completed my change but although the DBCC had been running for nearly an […]

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 […]

FlushCache messages might not be an actual IO stall

SQL Server's FlushCache message might be not actually be an IO stall and using SQL Server's perfmon counters we can isolate another reason for this warning message.

At a client site recently a SQL Server 2012 (Enterprise, SP1 + CU#9) server with 512GB RAM and 80 cores (40 cores assigned to SQL Server) that had been patched and restarted displayed the following symptoms after an index reorg operation was restarted: CPU saturation, with all cores available to SQL Server (but not all […]

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 […]

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 […]

Troubleshooting SQL Server listener connectivity

Verifying and troubleshooting SQL Server listener connectivity is a key component in a DBAs armoury but often overlooked until now this post shows how to confirm connectivity to SQL Server using any of its connectivity options such as TCP/IP, Named Pipes or Shared Memory.

SQL Server listeners Verifying and troubleshooting SQL Server listener connectivity is a key component in a DBAs armoury but often overlooked until now (hopefully!). SQL Server can listen for connection requests via multiple listeners, such as TCP/IP, shared memory or named pipes. Sometimes it’s necessary to check connectivity by a specific listener. The first place […]