Blog

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

ML Studio login and organisational accounts

How to modify SQL Server's tempdb database configuration for maximum performance.

I’ve been very interested in Microsoft’s Machine Learning offering that they’ve recently launched on Azure as it looks like it will bring data-mining out of the stranglehold of data scientists and much closer to data users, so I eagerly set about viewing the online tutorials and webcasts Microsoft have provided. Unfortunately, this particular webcast rather […]

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

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