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?

2 Answers 2



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, 2012 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, 2012 at 10:15
  • I just want the result count estimation.
    – AliBZ
    Feb 20, 2012 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, 2012 at 2:32
  • Thanx but I really don't know how to use it. Where should I type these codes?
    – AliBZ
    Feb 21, 2012 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, 2012 at 4:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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