Statistics Profile Output Formatting

Statistics profile output is an important tool when it comes to troubleshooting query plan issues. When enabled, it returns a textual representation of the query plan with a lot of detail about cost and cardinality estimates as well as actual counts.   When working in SQL Server management Studio (SSMS), it is advised to enable…

0

Showplan Trace Events

Besides SSMS, another great tool available to database developers and DBAs to view query plans and troubleshoot query compilation or execution issues is the SQL Server Profiler. In the Profiler, all the showplan events are listed under the Performance Event category. All the SQL Trace events generate showplan information at query-level granularity, i.e. a single…

1

What's this cost?

Oftentimes when people include actual execution plan (Ctrl-M, see previous posts for a good primer on execution plans) while executing a batch in SQL Server Management Studio, and they see this “Query cost (relative to the batch)” thing on top of each query in the batch, they start to ask: What does this mean? But when…

2

Viewing and Interpreting XML Showplans

As mentioned in our previous blog posting, SQL Server 2005 supports Showplan generation in XML format. XML-based Showplans provide greater flexibility in viewing query plans and saving them to files as compared to legacy Showplans. In addition to the usability benefits, XML-based Showplans also contain certain plan-specific information which is not available in Legacy Showplans. For example, Showplan XML contains the cached plan…

1

Introduction to Showplan

Showplan is a feature in SQL Server to display and read query plans. While some of you may already be very familiar with Showplan, it is one of the most important diagnostic tools that we use in the query processing team to locate and identify problems, and therefore deserves some extra exposure.  Being able to…

0

Compilation Time Issues in OLTP Applications When Upgrading to SQL 2005

I’ve helped a lot of people upgrade their SQL 2000 applications to SQL 2005.  While many of these upgrades go well, occasionally some applications have issues and the overall workload may perform more poorly after the upgrade.  One area where I’ve seen issues is with ad-hoc on-line transaction processing (OLTP) systems.  This class of application performs…

1

Query Processing -- introduction to the blog!

Since we’re starting a new blog for the whole query processing team, we think it’s appropriate to start with an introductory post about query processing as a whole and what we’ll be talking about in this blog.   If you’re unfamiliar with the way SQL Server processes its queries, a good refresher can be found…

1

Greetings!

This blog will serve as the new spot for content from the SQL Server Query Processor team. The old blog is at http://blogs.msdn.com/queryoptteam, and it has a lot of good content you can reference.  We’ll update it on this blog as we need to do so.  New content will be published here. Thanks, Conor Cunningham…

0