How to Read Statistics Profile

re: How to Read Statistics Profile

Askedal — Mon, 04 Sep 2006 14:00:02 GMT

Hello,

great stuff.
I'm mostly working on performance problems and saw strange Expression calculations in plans. I would really appreciate it, when you can shed some light on them sometimes:

| |--Sort(TOP 2, ORDER BY:([Expr1008] DESC, [Expr1009] ASC, [Expr1005] ASC, [Expr1010] DESC))
PLAN_ROW
est.rows: 2 est.io: 0.00 est.cpu: 0.00
| |--Compute Scalar(DEFINE:([Expr1008]=(4&[Expr1007])=4 AND NULL=[Expr1005], [Expr1009]=4&[Expr1007], [Expr1010]=16&[Expr1007]))
PLAN_ROW
est.rows: 2 est.io: 0.00 est.cpu: 0.00
| |--Concatenation
PLAN_ROW
est.rows: 2 est.io: 0.00 est.cpu: 0.00
| |--Compute Scalar(DEFINE:([@P3]=[@P3], [@P3]=[@P3], [Expr1004]=62))
PLAN_ROW
est.rows: 1 est.io: 0.00 est.cpu: 0.00
| | |--Constant Scan
PLAN_ROW
est.rows: 1 est.io: 0.00 est.cpu: 0.00
| |--Compute Scalar(DEFINE:([@P2]=[@P2], [@P2]=[@P2], [Expr1007]=62))
PLAN_ROW

Regards
A.

re: How to Read Statistics Profile

QueryOptTeam — Tue, 05 Sep 2006 06:34:10 GMT

I believe that this logic has to do with building dynamic index ranges. When you have queries that have WHERE clauses with parameters (or queries that we automatically parameterize for you) with multiple parameters restricting the same column, those ranges may or may not overlap. The plan generation component does not know until runtime what to do, so the Optimizer builds a plan to handle the various cases, generate the needed ranges based on the supplied parameters, then performs the index seek. You should see a Nested Loops + Index Seek above this in your plan.

Query Processing -- introduction to the blog!

Tips, Tricks, and Advice from the SQL Server Query Processing Team — Tue, 26 Sep 2006 03:50:32 GMT

Since we’re starting a new blog for the whole query processing team, we think it’s appropriate to start...

Store Statistics XML in database tables using SQL Traces for further analysis.

Tips, Tricks, and Advice from the SQL Server Query Processing Team — Thu, 07 Jun 2007 03:43:03 GMT

Since SQL Server 2005, query plan as well as statistics of query execution can be captured in XML format.

» Store Statistics XML in database tables using SQL Traces for further analysis. | SQL Server Feeds

Wed, 06 Aug 2008 10:08:36 GMT

PingBack from http://www.sqlserverfeeds.com/683/store-statistics-xml-in-database-tables-using-sql-traces-for-further-analysis/

Tips Tricks and Advice from the SQL Server Query Optimization Team | Shed Kits

Tips Tricks and Advice from the SQL Server Query Optimization Team | Shed Kits — Wed, 27 May 2009 09:07:57 GMT

PingBack from http://backyardshed.info/story.php?title=tips-tricks-and-advice-from-the-sql-server-query-optimization-team

Tips Tricks and Advice from the SQL Server Query Optimization Team | alternative dating

Wed, 17 Jun 2009 09:58:22 GMT

PingBack from http://topalternativedating.info/story.php?id=6978

Tips Tricks and Advice from the SQL Server Query Optimization Team | debt consolidator

Fri, 19 Jun 2009 17:44:23 GMT

PingBack from http://mydebtconsolidator.info/story.php?id=15212