Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question

2 Answers

up vote 1 down vote

Use:

set showplan_xml on;
go
Your query;
go
set showplan_xml off;
go

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

share|improve this answer
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
show 1 more comment
up vote 0 down vote

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.

share|improve this answer
Thanx but it doesn't work – AliBZ Feb 19 '12 at 4:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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