I want to check the estimated number of rows of a query in MS SQL Server. I can right click on the query and select "display estimated execution plan". but I want to do the exact thing with a hard code query. Is there any way to do this without using the GUI?



set showplan_xml on;
Your query;
set showplan_xml off;

This will give the XML, and you should be able to see the Estimates in there.

  • Is there any way that I could get it like a simple query? I prefer not to parse the xml file. – AliBZ Feb 19 '12 at 4:25
  • You'll need to work out which part of the plan you want. There will be several Estimates in there. XQuery will let you get at it, if you know where to look. – Rob Farley Feb 19 '12 at 10:15
  • I just want the result count estimation. – AliBZ Feb 20 '12 at 0:41
  • Here's what I do in PowerShell (with the SQL SnapIns added): function Get-SqlPlan([string] $query, [string] $server, [string] $db) { return ([xml] (invoke-sqlcmd -Server $server -Database $db -Query "set showplan_xml on;`ngo`n$query").Item( 0)) } $plan = get-sqlplan "select name from Production.Product" "." "AdventureWorks"; $plan.ShowPlanXML.BatchSequence.Batch.Statements.StmtSimple.StatementEstRows; – Rob Farley Feb 20 '12 at 2:32
  • Thanx but I really don't know how to use it. Where should I type these codes? – AliBZ Feb 21 '12 at 18:30

Use the DMV query: select row_count from sys.dm_exec_requests when your hard coded query is being run. You can also filter by session_id.

  • Thanx but it doesn't work – AliBZ Feb 19 '12 at 4:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.