SQL FileStream Restore and Anti-Virus

I encountered an interesting case with FileStream and Antivirus which I looked at with Bob Dorr, blogged about it on the PSS SQL Blog, thought I’d share that link here as well http://blogs.msdn.com/b/psssql/archive/2013/03/29/the-case-of-anti-virus-filter-drive-interference-with-file-stream-restore.aspx -Denzil Ribeiro


Observing SQL Server Transaction Log Flush Sizes using Extended Events and Process Monitor

In this blog post we’ll take a look at how SQL Server interacts with the transaction log file using Extended Events and Process Monitor.  We will see how small transactions can have a negative impact on transaction log IO throughput and how larger transactions can actually be much faster than a lot of small transactions….


Troubleshooting SQL Server High CPU usage using Xperf

  Xperf/WPA is a powerful Windows tracing tool that can be used to assist in troubleshooting numerous issues on a Windows server.   The utility allows for in depth tracing without the high overhead typically associated with such tracing.   This is accomplished by making use of the Event Tracing for Windows architecture (ETW).     ETW is built…


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"…


SQL High CPU scenario troubleshooting using sys.dm_exec_query_stats and RING_BUFFER_SCHEDULER_MONITOR ring buffer in sys.dm_os_ring_buffers

I ran into a scenario a while ago that I thought I would share, where we were troubleshooting high CPU on  SQL Server.  The sporadic High CPU ( 90+ % )  pretty much brought the box down to its knees for a period of 5-10 minutes minutes or so, and then cleared itself up. We…


SQL High CPU scenario troubleshooting using sys.dm_exec_query_stats and RING_BUFFER_SCHEDULER_MONITOR ring buffer in sys.dm_os_ring_buffers

I ran into a scenario a while ago that I thought I would share, where we were troubleshooting high CPU on  SQL Server.  The sporadic High CPU ( 90+ % )  pretty much brought the box down to its knees for a period of 5-10 minutes minutes or so, and then cleared itself up. We…