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…
Tag: IO
Back to Basics: SQL Parameter Sniffing due to Data Skews
In Denzil’s recent post about correlating XE query_hash he alluded to a relatively common issue. Most of the time a proc will run just fine, but sometimes it will run very slow. This is an issue that I tend to be seeing often as of late. A common cause for this type of situation is parameter…
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,…
Dissecting SQL Server physical reads with Extended Events and Process monitor
In this blog post I’ll (re)introduce you to a really neat tool to use alongside of SQL Server and use this tool to show you how SQL Server is handling IO under the covers. We will view the IOs as they occur, and then tie the IO back to the pages that we pull…
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"…