19Mar 2009 by Grant Fritchey 13 Comments

Execution Plan Estimated Operator Cost

Uncategorized
I've said it over and over again, the costs on operators in execution plans, even in actual execution plans are estimates.  You need to understand that when looking at your execution plans. It's vital because you need to be able to distinguish between the truly costly parts of a plan and the less costly parts of a plan. Don't believe me? Take a look at this picture and see if you can spot the discrepancy: Spot it yet? Add up the costs for the operators visible in the part of the plan... Yep 125%.  And there's more to the plan that I'm not showing. I think this one must total near 200%. The statistics are up to date and there's no consistency errors in the database. These estimates are just off sometimes.…
Read More
30Oct 2008 by Grant Fritchey No Comments

Procedure Cache and Dynamic Management Views

SQL Server, T-SQL
I'm just going through the chapter on the procedure cache in the new book and I'm having a blast playing with the dynamic management views and functions that let you access the procedure cache. It's just too much fun and way too easy to get detailed information about the queries in the system, not like the old days. First, you can access the cache itself with the DMV, sys.dm_exec_cached_plans. This shows some of the data describing the plan in cache, but most importantly it provides the plan_handle. You need this for other joins later. You can also use sys.dm_exec_query_stats to get aggregated performance statistics about the plan. It also has the plan_handle and two things new to SQL Server 2008, the query_hash and the query_plan_hash, also known as query fingerprints.…
Read More
09Sep 2008 by Grant Fritchey No Comments

Query Fingerprints and Plan Fingerprints

nHibernate, T-SQL
SQL Server 2008 has been released, but the full capabilities of the product are still be discovered. This isn't a case of discovery so much as Microsoft, in the person of Bart Duncan, has shown us something new. Read the article. This is one of the most exciting things I've seen in a long time. It's especially exciting as we see databases becoming less and less manageable through the implementation of tools like nHibernate. As I noted in a previous post, nHibernate will create a lot of execution plans. With the capabilities here, we'll be able to easily and quickly aggregate some of those plans to identify the costly queries coming out of nHibernate without having to resort to 24 hour Profiler monitoring. Great stuff.
Read More
01Jul 2008 by Grant Fritchey 10 Comments

Constant Scan in Execution Plans

SQL Server, T-SQL
I see a lot of searches from people apparently trying to find out what having a Constant Scan in their execution plan means. I can understand why. Here's the definition from the Books Online: The Constant Scan operator introduces one or more constant rows into a query. A Compute Scalar operator is often used to add columns to a row produced by a Constant Scan operator. OK. Very precise and yet, unless you know what the sentence means, reading it can be pretty confusing. The key is to see what Compute Scalar means: The Compute Scalar operator evaluates an expression to produce a computed scalar value. This may then be returned to the user, referenced elsewhere in the query, or both. An example of both is in a filter predicate…
Read More
29May 2008 by Grant Fritchey 1 Comment

Break Down Complex Execution Plans

T-SQL
I've seen this question posted in a lot places and I've seen the search come in to the blog: "How do you break down a complex execution plan?" The short answer; just like eating an elephant, one bite at a time. The longer answer... First you have to define what you mean by a complex execution plan. For example, I've seen 16 page stored procedures that consist of about 50 or more different statements executed in series (and I 've heard of worse). No single statement was very big, but trying to dig through all the statements to identify which may be causing slow execution was a problem. This is the easiest type of complex execution plan to solve. In SQL Server 2005 and 2008, when you turn on a graphical…
Read More
28May 2008 by Grant Fritchey No Comments

XML Showplan in 2008

SQL Server, T-SQL
I just found my first fundamental difference between execution plans in SQL Server 2005 and SQL Server 2008. In 2005 when you run an query and have it display an XML execution plan by issuing the statement "SET STATISTICS XML ON" you get a second result set that includes a URL. The same thing happens in 2008. When you click on that URL in 2005 it opens the XML file in a new window. If you want to look at the graphical plan, you have to save the file with  a "*.SQLPLAN" exentension. In 2008 if you click on the URL, it opens a new window, but it shows the graphical plan. What happened to the XML? The developers at Microsoft are smart, that's what happened. If you then right click…
Read More