Forced Parameterization Can Lead to Poor Performance

Something that is a relatively common performance eye opener is when you have a large ad hoc workload, and you’re getting a huge gap in the lack of plan reuse.  You talk to the application team that is responsible for this possibly dreaded scenario and relay your concerns about the high CPU caused by the…


Correlating XE query_hash and query_plan_hash to sys.dm_exec_query_stats in order to retrieve Execution plans for high Resource statements.

Extended events is a powerful feature that allows us to troubleshoot performance issues within SQL Server. One of the ways you can utilize extended events to aggregate statements is described in a blog post below by Bob Dorr SQL Server 2012: RML, XEvent Viewer and Distributed Replay Let’s apply that methodology to a contrived scenario,…


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


SQL 2012 System Health Reporting Dashboard – Visualizing sp_server_diagnostics results.

We have introduced the System Health Session in SQL 2008 in order to capture some critical Events to make post mortem analysis much easier. However in SQL 2008, by default the system_health session was only collected to a ring buffer and not persisted to disk. SQL 2012 truly provides more of a black box recorder…


SQL Server memory models (Part II)

So what is part II about? It is about a change in Standard 64 bit Editions of SQL Server 2005 and 2008. This change implements the support of AWE APIs and “Lock Pages in Memory” privilege by these editions.  It has been already advertised by Bob Ward and worth to be known especially by the…


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 Server memory models (Part I)

You may have questioned why, on 64 bit enterprise Edition, DBCC MEMORYSTATUS reports non zero “AWE allocated” memory even if ‘AWE enabled’ option is not enabled. A short description of SQL Server memory model will help to answer. Here is an example of a DBCC MEMORYSTATUS output on a 32GB X64 Enterprise SQL Server 2005:…