SQL PFE has a Pre-Con at the PASS Summit!

At the 2014 PASS Summit in Seattle on Tuesday November 4, Denzil Ribeiro and I (Tim Chapman) will be giving a Pre-Conference seminar entitled “Troubleshoot Customer Performance Problems like a Microsoft Engineer.”  In this full-day session Denzil and I will cover a plethora of troubleshooting tools and methodologies that we use as field engineers when confronted…


Oops… I forgot to leave an empty SQL table partition, how can I split it with minimal IO impact?

One of the several advantages you get with partitioning a very large table is the ability to add or remove partitions instantaneously to help you with sliding window scenarios. However there are caveats if best practices aren’t followed. If the right most partition (in case of a RANGE LEFT partition) or the left most partition…


Identifying the cause of SQL Server IO bottlenecks using XPerf

In a previous blog post (Troubleshooting SQL High CPU usage using Xperf), we covered the xperf basics, what types of scenarios are appropriate for xperf, and more specifically, how to look at CPU sampling within xperf.   In general, user CPU time would be investigated using the standard SQL Server tools such as Profiler/Extended Events, DMV’s,…


How and Why to Enable Instant File Initialization

You may want to consider enabling Instant File Initialization (via SE_MANAGE_VOLUME_NAME a.k.a. "Perform volume maintenance tasks") for your SQL Server startup/service account. This option allows for much faster data file allocations (CREATE AND ALTER FILE) but DOES NOT WORK FOR LOG FILE ALLOCATIONS. This is enabled for each instance via the "Perform volume maintenance tasks"…