New Course: “SQL Server: Common Query Tuning Problems and Solutions – Part 2″

Today, Pluralsight published my latest course, “SQL Server: Common Query Tuning Problems and Solutions – Part 2“.  Part two is 2 hours and 17 minutes long and covers the following: Troubleshooting Inefficiency Demo: One-Row-At-A-Time-Processing Demo: The Forgotten Trigger Demo: Scalar Function Inhibited Parallelism Demo: Scalar Function Row by Row Execution Demo: Too Few Rows Per Batch […]

SQLIntersection Post-Conference Session

I am really happy to announce that Kimberly Tripp and I will be delivering a post-conference in November’s SQLIntersection conference at the MGM Grand. The session is “Queries Gone Wild 2: Statistics and Cardinality in Versions 2008, 2008R2, 2012, and 2014” and will be delivered November 14th, from 9AM to 4:30PM. You can find registration […]

Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

I’ve been working on writing a new Microsoft white paper since January covering the main changes made in the SQL Server 2014 Cardinality Estimator, and happy to announce that it was just published: Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator A big thanks to the contributor and reviewer team – including […]

Finding Timeout-Prone Code with max_elapsed_time

Consider a scenario where your application timeout setting is 30 seconds and you’ve been asked to proactively start tuning workloads that are at risk of timing out.  There are several different places you can start, and I want to demonstrate an additional option today. To demonstrate timeout settings, I can configure it in SQL Server […]

Combining multiple CONTAINS predicates in SQL Server 2012

The SQL Server 2005  “Performance Tuning and Optimization (Full-Text Search)” books online topic has the following recommendation: “Combine multiple CONTAINS predicates into one CONTAINS predicate. In SQL Server you can specify a list of columns in the CONTAINS query.” While this shows up for the SQL Server 2005 topic, there is no such statement made […]

Troubleshooting the new Cardinality Estimator

This post is a continuation of the SQL Server 2014 Cardinality Estimator enhancements exploration series: A first look at the query_optimizer_estimate_cardinality XE event “CSelCalcCombineFilters_ExponentialBackoff” Calculator “CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II The CSelCalcAscendingKeyFilter Calculator Cardinality Estimation Model Version Comparing Root-Level Skews in the new Cardinality Estimator Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews […]

Non-Join Cross-Table Predicate Correlation Changes

This post is a continuation of the SQL Server 2014 Cardinality Estimator enhancements exploration series: A first look at the query_optimizer_estimate_cardinality XE event “CSelCalcCombineFilters_ExponentialBackoff” Calculator “CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II The CSelCalcAscendingKeyFilter Calculator Cardinality Estimation Model Version Comparing Root-Level Skews in the new Cardinality Estimator Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews […]

More on Exponential Backoff

This post is a continuation of the SQL Server 2014 Cardinality Estimator enhancements exploration series: A first look at the query_optimizer_estimate_cardinality XE event “CSelCalcCombineFilters_ExponentialBackoff” Calculator “CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II The CSelCalcAscendingKeyFilter Calculator Cardinality Estimation Model Version Comparing Root-Level Skews in the new Cardinality Estimator Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews […]

Using Legacy Methods to Lessen SQL Server 2014 Cardinality Estimator Skews

This post is a continuation of the SQL Server 2014 Cardinality Estimator enhancements exploration series: A first look at the query_optimizer_estimate_cardinality XE event “CSelCalcCombineFilters_ExponentialBackoff” Calculator “CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II The CSelCalcAscendingKeyFilter Calculator Cardinality Estimation Model Version Comparing Root-Level Skews in the new Cardinality Estimator In the previous post, I looked at root-level cardinality estimates vs. […]

Comparing Root-Level Skews in the new Cardinality Estimator

This post is a continuation of the SQL Server 2014 Cardinality Estimator enhancements exploration series: A first look at the query_optimizer_estimate_cardinality XE event “CSelCalcCombineFilters_ExponentialBackoff” Calculator “CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II The CSelCalcAscendingKeyFilter Calculator Cardinality Estimation Model Version In the previous post I showed you how to start using the new SQL Server 2014 CE model, but […]

Cardinality Estimation Model Version

This post is a continuation of the SQL Server 2014 Cardinality Estimator enhancements exploration series: A first look at the query_optimizer_estimate_cardinality XE event “CSelCalcCombineFilters_ExponentialBackoff” Calculator “CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II The CSelCalcAscendingKeyFilter Calculator In this post I just wanted to step back and discuss how to enable and identify the Cardinality Estimator version, at least as […]

The CSelCalcAscendingKeyFilter Calculator

This post is a continuation of the SQL Server 2014 Cardinality Estimator enhancements exploration series (yeah – I’ve decided to call this a series, since there will be quite a bit to talk about, time permitting): A first look at the query_optimizer_estimate_cardinality XE event “CSelCalcCombineFilters_ExponentialBackoff” Calculator “CSelCalcCombineFilters_ExponentialBackoff” Calculator– Part II So for this post, I […]

“CSelCalcCombineFilters_ExponentialBackoff” Calculator

One more post for the day, just while I have the blogging bug… Per my last post, we saw that the query_optimizer_estimate_cardinality event can provide some interesting information in SQL Server 2014 around how cardinality estimates get calculated. I have a setup I’ve used in the past to demonstrate column correlation challenges (see my post, […]

A first look at the query_optimizer_estimate_cardinality XE event

This weekend I set up a Windows Azure virtual machine running SQL Server 2014 CTP2 (12.0.1524).  I had a local copy of SQL Server 2014 CTP2 in VMware as well, but thought it would be more fun to explore it on Windows Azure (kill two explorations with one stone).  On a side note, I’m really […]

Workloads that generate the 0×0000000000000000 query hash value?

In the last SQLskills Insider demo video I went through a scenario where you can use the query_hash column from sys.dm_exec_query_stats to aggregate statistics for queries that only differ by literal values. I received an interesting question from someone who watched the video, ran the demo query in production and saw a 0×0000000000000000 value as […]

New Article on SQLPerformance.com “Exploring Low Priority Lock Wait Options in SQL Server 2014 CTP1”

My 10th guest blog post was published today on SQLPerformance.com: Exploring Low Priority Lock Wait Options in SQL Server 2014 CTP1 In this post I further explore (from the last SQLPerformance.com post) online options, and specifically low priority lock wait improvements being included in SQL Server 2014 CTP1.

New Article on SQLPerformance.com “Exploring Partition-Level Online Index Operations in SQL Server 2014 CTP1”

My 9th guest blog post was published today on SQLPerformance.com: Exploring Partition-Level Online Index Operations in SQL Server 2014 CTP1 In this post I explore the online, single-partition rebuild improvement being introduced in SQL Server 2014 CTP1.

A Brief Tour of SQL Sentry’s “AlwaysOn Monitoring”

Now before I begin this post, a quick disclaimer… SQLskills partners with SQL Sentry on a couple of things. We periodically write guest-posts on SQLPerformance.com and SQL Sentry also provides exclusive sponsorship of two of our Immersion Events. I am also a big fan of SQL Sentry Plan Explorer, but that would have been the […]

New Article on SQLPerformance.com: “Exploring SQL Server 2014 SELECT INTO Parallelism”

My 8th guest blog post was published today on SQLPerformance.com: Exploring SQL Server 2014 SELECT INTO Parallelism In this post I kick the tires of the new SELECT … INTO parallelism improvement in SQL Server 2014 CTP1 – and the results I saw were favorable.

New Article on SQLPerformance.com: “Observer Overhead and Wait Type Symptoms”

My 7th guest blog post was published today on SQLPerformance.com: Observer Overhead and Wait Type Symptoms This article details seven test scenarios I performed in order to see what wait-type symptoms (if any) arise based on different types of “observer overhead” due to concurrently running SQL Trace and XE sessions.

Control Flow vs. Data Flow Demo

I had an interesting question yesterday in class about query execution plan control flow (from the root of the plan to the leaf level) and data flow (from leaf to root) and thought I would share a small demo I put together over lunch break to walk through the discussion. Let’s start by dropping clean […]

SQLPerformance.com “Troubleshooting SQL Server CPU Performance Issues”

My fifth guest blog post was published today on SQLPerformance.com: Troubleshooting SQL Server CPU Performance Issues In this post I describe the general framework you can use to perform root cause analysis for high CPU or CPU-related performance issues.

SQLPerformance.com “The Case of the Cardinality Estimate Red Herring”

My fourth guest blog post was published today on SQLPerformance.com: The Case of the Cardinality Estimate Red Herring This was inspired by a case that Erin Stellato and I worked together on a few months ago. While I’ve talked quite a bit this year about the importance of investigating bad cardinality estimates, this blog post […]

Data Page Count Influence on the Query Execution Plan

In my post Row and Page Count Influence on Estimated CPU and I/O Cost I demonstrated how I/O cost of a clustered index scan had sensitivity to page counts but not row counts.  For this post I’ll lay out a direct connection between data page counts and the query execution plan that gets generated. To […]

Are Filtered Statistics Useful with Parameterization?

This post is based on a question I received last week… If we have a filtered index that was not a match for a query due to parameterization (or a variation of which the parameter is not known at compile time), we can see the unmatched filtered index name in the UnmatchedIndexes element of SHOWPLAN_XML […]

Redundant Query Plan Branches

Consider the following “basic_member” view definition from the Credit database: A simple SELECT from this view returns 8,498 rows and has the following plan shape (and I’m boxing in an “areas of interest” via SQL Sentry Plan Explorer’s rendering of the plan): We see that the view has a predicate on member_no NOT IN the […]

Columnstore Segment Population Skew

My last post about nonclustered Columnstore indexes was written back on February 25th, 2012 (Exploring Columnstore Index Metadata, Segment Distribution and Elimination Behaviors).  Amazed by how quickly time passes. Anyhow, this is a quick post on segment population skew based on parallel nonclustered Columnstore index creations. I’ll use the same 123,695,104 row FactInternetSales table I […]

Auto-Update Stats Default Sampling Test

In this post I’ll just share the results of a simple set of tests regarding automatic-update sampling.  Nothing fancy – I just populated the category table from the Credit sample database with varying row counts and then took note of the associated duration and sampling percent.  The query I used to kick of auto-updates after […]

New Course: “SQL Server: Troubleshooting Query Plan Quality Issues”

My latest online course, “SQL Server: Troubleshooting Query Plan Quality Issues” was published today by Pluralsight.  The short description is as follows… “Learn how to identify, diagnose, and prevent problems where SQL Server chooses the incorrect query plan for your critical queries, applicable to developers, DBAs, and anyone responsible for SQL Server, from SQL Server 2005 onwards.” This 2 […]

Detecting Selectivity Guesses

I’ve been mulling over a potential Microsoft Connect item regarding selectivity guess warnings, but I’m uncertain if it would have enough demand to drive product team attention.  Selectivity guesses, which I talked a little about in the “Selectivity Guesses in absence of Statistics” post, are sometimes referred to as “magic numbers”, “heuristics” or just plain […]

Row and Page Count Influence on Sort Memory Grants

My prior post demonstrated the influence of row and page counts on estimated CPU and I/O cost.  Now in this post I’m going to step through the influence of row and page counts on memory grants for a Sort operator.  I’ll be using the MemoryGrantInfo element from the query execution plan to measure the influence.  I […]

Row and Page Count Influence on Estimated CPU and I/O Cost

In this post I’ll step through a few scenarios regarding row and page counts – and their associated influence on estimated CPU and I/O cost.  I’ll be using the Credit database to demonstrate different scenarios… Starting State I’ll start off by checking the total number of reported data pages and row counts for the member […]

Detecting Cardinality Estimate Issues with sys.dm_exec_query_stats

SQL Server 2008 R2 SP1 and SQL Server 2012 RTM+ includes actual row count statistics in the sys.dm_exec_query_stats dynamic management view.  This includes the total_rows, last_rows, min_rows and max_rows columns, which are intuitively named. One potential use of the row statistics data is to detect cardinality estimate issues where the actual count of rows is […]

Thinking about the inaccurate_cardinality_estimate Event

SQL Server 2012 introduces the new inaccurate_cardinality_estimate extended event, and I had a few miscellaneous thoughts about it that I wanted to discuss on this post. Background reading If you’re unfamiliar with this event, there isn’t anything as of today in Books Online, but there are two blog references / discussions: How the query execution […]

SQLPerformance.com “TRANSACTION_MUTEX and Multi-Session Transaction Access”

My third guest blog post was published today on SQLPerformance.com: TRANSACTION_MUTEX and Multi-Session Transaction Access This is related to a recent SQL Server instance I was working with that had high TRANSACTION_MUTEX accumulated wait time.  Based on this observation, I explored a three different functionality areas that could be associated with this wait type.

Exploring Column Correlation and Cardinality Estimates

Last Thursday I presented a session at the PASS Winter 2012 Performance Palooza.  It was a great experience and I appreciated the opportunity.  The topic was “Troubleshooting Query Plan Quality Issues” and I received a few email questions after the presentation, so I thought I would walk through the full scenario, weaving in a few […]

New Course: “SQL Server: Common Performance Issue Patterns”

My latest course, “SQL Server: Common Performance Issue Patterns“  was just published by Pluralsight.com. The course description is as follows: “There are a wealth of problems that can affect the performance of SQL Server workloads, and Joe shows you more than 35 common performance issue patterns. With 17 detailed demos, you’re shown how to recognize each […]

Simple-Talk Article: “Fixing Gatekeeper Row Cardinality Estimate Issues”

I wrote a new article for Simple-Talk that was published recently: Fixing Gatekeeper Row Cardinality Estimate Issues In the article I talk about a specific cardinality estimation issue you may see for relational data warehouse queries (specifically against star schemas) and a few recommended solutions that may help eliminate or at least reduce the overall […]

SQLPerformance.com “Ten Common Threats to Execution Plan Quality”

My second guest blog post was published today on SQLPerformance.com: Ten Common Threats to Execution Plan Quality This is related to the subject of my recent SQLSaturday presentation and this is a big subject with a real tactical benefit from a performance tuning perspective, so I’ll be expanding it out over time in various formats […]

Distributed Query Plan Quality and SQL Server 2012 SP1

SQL Server 2012 Service Pack 1 is out today and it fixes an issue that has been around for several versions regarding distributed queries and poor quality execution plans due to bad cardinality estimates when the distributed query principal had insufficient permissions to gather the applicable statistics.  The description of this fix can be found […]

Avoid false negatives when comparing sys.dm_io_virtual_file_stats data to perfmon counter data

  A quick and somewhat nuanced tip today… When evaluating sys.dm_io_virtual_file_stats against collected perfmon counter data, be careful not to erroneously invalidate the latency findings by measuring and comparing against the averages of already-averaged collected performance monitor data (e.g. Avg. Disk sec/Write, Avg. Disk sec/Read ).  I’ve seen scenarios where there is true I/O latency […]

RunAs Radio Interview

A few weeks ago I was interviewed by Richard Cambell of RunAs Radio:  Joe Sack Tunes Queries in SQL Server! We talk about performance tuning, consulting scenarios and a little bit about SQL Server 2012 columnstore indexing.

SQL Server 2012’s Information on Parallel Thread Usage

SQLServerCentral published an article today that I wrote about SQL Server 2012’s execution plan parallel thread usage statistics: SQL Server 2012’s Information on Parallel Thread Usage The new thread statistics show information on the number of concurrent execution paths within an execution plan, the count of used threads and also the count of reserved threads […]

Memory Grant Execution Plan Statistics

SQL Server 2008 execution plans include an optional MemoryGrant attribute in the QueryPlan element – but SQL Server 2012 significantly expands on memory grant related statistics with the new MemoryGrantInfo element and associated attributes. Here is an example of MemoryGrantInfo from an actual SQL Server 2012 execution plan: <MemoryGrantInfo SerialRequiredMemory="5632" SerialDesiredMemory="11016" RequiredMemory="47368" DesiredMemory="52808" RequestedMemory="52808" GrantWaitTime="0" […]

SQL Server 2012’s RetrievedFromCache Attribute

SQL Server 2012 includes a new RetrievedFromCache attribute in the query execution plan. Let’s say I execute the following query immediately after executing DBCC FREEPROCCACHE: What value for RetrievedFromCache would you expect to see?  In this example, I saw the following attribute value (with the attribute highlighted and StmtSimple abridged for clarity): <StmtSimple StatementCompId=”2″ StatementEstRows=”5″ […]

MSQL_DQ and PREEMPTIVE_COM_QUERYINTERFACE–Two Sides of the Same Coin

The MSQL_DQ wait type accumulates while waiting for a distributed query to complete and it is not necessarily indicative of an issue. If MSQL_DQ is one of your top wait types accumulated on your SQL Server instance, you may also see an associated PREEMPTIVE_COM_QUERYINTERFACE wait type.  And actually, you may see that the accumulated values […]

SQL Server 2012 Execution Plan’s NonParallelPlanReason

SQL Server 2012 introduces a “NonParallelPlanReason” attribute in the QueryPlan element in a query execution plan.  This attribute is not officially documented as of this writing, and so I was curious which scenarios this new functionality covered. Take the following example query which runs in parallel on my test SQL Server instance: A few details […]

A first look at SQL Server 2012 Availability Group Wait Statistics

I wrote an article for Simple-Talk that was published last Monday: A first look at SQL Server 2012 Availability Group Wait Statistics AlwaysOn Availability Groups have their own host of associated wait types, so I thought I would investigate the various wait stats that accumulate based on different scenarios.  I see this topic evolving over […]

Tale of the Inconsistent UDF Logical IOs

This post was motivated by an email question I got this week. Imagine you have the following scalar UDF: CREATE FUNCTION dbo.RemoveYear (@date datetime) RETURNS datetime AS BEGIN DECLARE @removeyear datetime = DATEADD(year, -1, @date);   RETURN(@removeyear); END; GO Now – aside from the fact that this function doesn’t really need to exist in the […]

Selectivity Guesses in absence of Statistics

Let’s say you have a heap table with 1,000,000 rows in it.  Let’s also say that your automatic creation of statistics are disabled, as well as updates to the statistics (and in this scenario, there are NO existing indexes or statistics). What kind of selectivity guess would the optimizer make for a query like the […]

Being Mindful of Cursor Lock Overhead

This post is just a reminder to be attentive to the locking overhead of your Transact-SQL server cursors. For example, the following cursor is using default options in SQL Server 2012 to iterate row-by-row through the Employee table.  I’m declaring a variable and populating it with the BusinessEntityID from each row (and I’m not doing […]

Exceptions–what sys.dm_db_index_usage_stats doesn’t tell you (Part II)

Last November I blogged about how index usage stats don’t get updated when the associated index statistics (but not index) are used. This post will describe another scenario that you should be aware of (the topic came up today in class while Kimberly was teaching – as we were trying to recall tricks to clearing […]

Partitions Accessed and Partition Range in the Query Execution Plan

Let’s say you are querying a partitioned table and you would like to see which partitions were accessed by looking at the graphical execution plan: “Actual Partition Count” shows a value of 1 and “Actual Partitions Accessed” shows a value of 50.  The “Actual Partitions Accessed” property name could cause confusion though, since what you’re actually looking […]

Capturing session_id scheduler assignment using SQLCMD Mode

Yesterday I was working on a SSMS SQLCMD-mode script and I noticed that periodically my session_id changed across test executions.  I was tracking wait statistics based on specific session IDs, so I had to periodically add new session ids to my extended events session definition to make sure I captured my session’s wait stats accordingly. […]

Temporary Statistics for Database Snapshots in SQL Server 2012

You may have already heard that SQL Server 2012 adds automatic generation of statistics for AlwaysOn availability group readable secondaries, storing the statistics in tempdb.  This functionality was also extended to other areas beyond just AlwaysOn availability groups as I’ll demonstrate next… Using the AdventureWorksDWDenali database, I’ll start by checking the current statistics on the […]

Comparing Query Performance when using an “ideal” Nonclustered Index versus Columnstore Index

I gave a presentation on columnstore indexing last week and one question I received was regarding the performance of a (hypothetical) narrow, supporting nonclustered index versus a columnstore index.  We discussed how nonclustered indexes were still going to be more effective for specific types of queries (for example – singleton operations, smaller range scans, etc.).  […]

SQL Server 2012 Partitioned Index Default Sampling Algorithm Changes

When moving from SQL Server 2008+ to SQL Server 2012, be aware that the sampling algorithm has changed. For example, I created a partitioned clustered index on two identical tables (same schema, rows and distribution), one in SQL Server 2008 R2 SP1 and the other in SQL Server 2012 RC0.  Below shows the partial output […]

Exploring Columnstore Index Metadata, Segment Distribution and Elimination Behaviors

I was interested in exploring various questions about columnstore indexing this morning – and I realized I should probably blog about what I observed.  This truly was just an exploration and not a formal test (call it semi-structured fiddling).  Also, some of my “questions” were really just confirmations.  While I believe what I read, I […]

The Transactional Replication Multiplier Effect

This post idea was prompted by a discussion I had this week with Jonathan Kehayias about an environment that had multiple transactional replication publications defined with overlapping table articles.  In other words, a table was defined as an article in more than one publication. While I can think of some cases where you would want […]

SEQUENCE Object versus Modulus Hash Partitioning

Last month I wrote a post called Hash Partitioning with SQL Server 2012’s SEQUENCE object and CYCLE argument and I was asked a good question by Eric Humphrey (twitter) about whether there was any advantage of using a sequence object for hash partitioning over an identity/modulus/computed-column technique.  At that point I hadn’t compared the two […]

What are the Replication Agents waiting on? Accumulating wait stats by agent session

Consider the following scenario: ·        You have Transactional Replication deployed ·        Data is flowing, but just not as fast as you would like ·        This scenario could apply to local/remote distributors and push/pull subscribers There are several different techniques we can use to narrow down where the replication performance issue is happening.  Perhaps you’ve already […]

Workload Group MAX_DOP and the Faux Parallel Plan

Let’s say you’ve “disabled” parallelism on your SQL Server instance via the ‘max degree of parallelism’ setting as follows: EXEC sp_configure‘max degree of parallelism’, 1 RECONFIGURE Now most folks know (or are finding out) that this doesn’t really prevent parallel plans if you throw in a MAXDOP hint.  For example, let’s take the following query: […]

Hash Partitioning with SQL Server 2012’s SEQUENCE object and CYCLE argument

When I first heard about SQL Server 2012’s SEQUENCE object – I thought it was an interesting feature to be added and one that I have been asked about by customers in the past (from those who had worked on different database platforms).  But when I looked at the CYCLE argument of SEQUENCE, that’s when […]

A small-scale test measuring the impact of data compression on the transaction log

I received a question today about the impact of data compression on the transaction log.  While most of the time we talk about data compression from a data page and memory utilization perspective, I hadn’t actually directly tested the impact to transaction logging and I wanted to see it for myself.  Here is just a […]

Exceptions – what sys.dm_db_index_usage_stats doesn’t tell you

I was thinking back to a conversation I had some time ago regarding cases when sys.dm_db_index_usage_stats does not get updated after an index is used for just the statistics associated with the indexed columns.  What makes this profession so interesting is that almost every tool we use has a "yes, but…" associated with it.  What […]

Observer overhead – the perils of too much tracing

While it’s good to keep an eye on your SQL Server instances – it’s a balancing act.  On one side you want to be sure that you are monitoring critical areas and pulling enough information to perform root cause analysis on anything out-of-the-norm. But on the other side, if you pull an excessive amount of […]

Tracking isolation level

When I'm troubleshooting concurrency issues – be it deadlocking or long duration blocks, I’m immediately curious about the isolation level being used.   While I may be told that the isolation level is "XYZ", I don’t believe it until I actually see it for myself. There are multiple areas where this information can be retrieved.  This post lays […]

Hunting down the origins of FETCH API_CURSOR and sp_cursorfetch

So picture the following scenario on a SQL Server 2008 R2 instance (an amalgam of various DBA situations you've no doubt seen before)…  You get a call from the application team reporting slow performance on a specific service.  They don’t know why it is slow but they do know the session that is running too slow […]

Setting indirect checkpoints by database in SQL Server 2012

This blog post applies to SQL Server 2012 CTP3 (11.0.1440). SQL Server 2012 introduces the “indirect checkpoints” feature.  At a high level, it allows you to adjust the target recovery time of a specific database, rather than relying entirely on the SQL Server instance-level ‘recovery interval (min)’ setting. To demonstrate the impact, I’ll walk through a […]

Clearing “missing index” suggestions for a single table

Today there isn’t a fine-tuned way to clear the accumulated statistics from the “missing index” set of DMVs.  Certainly restarting SQL Server will do this, but you don’t have something like DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR) to reset statistics for dm_db_missing_index_columns, dm_db_missing_index_groups, dm_db_missing_index_group_stats and dm_db_missing_index_details. While we don’t have this direct option, there is an interesting […]

Under the covers with sp_server_diagnostics data (Part II)

This post covers examples from Denali CTP3, version 11.0.1440. In my last post, “Under The Covers With Sp_server_diagnostics Data (Part I)” I started an exploration of the output surfaced from the sp_server_diagnostics stored procedure.  In this post, I’d like to continue this exploration, having a look specifically at the “query_processing”, “io_subsystem” and “events” component data. […]

Under the covers with sp_server_diagnostics data (Part I)

This post covers examples from Denali CTP3, version 11.0.1440. In this post I’ll be discussing the SQL Server health related output from Denali’s sys.sp_server_diagnostics system stored procedure.  This system stored procedure is used to check Denali SQL Server health status behind-the-scenes for both failover cluster instances and availability groups.   While this procedure is used […]