Retreiving query cost?

Question

Retreiving query cost?

Michal Mokros

Hall of Fame

Points: 3259
More actions
April 2, 2008 at 12:35 am

#180514

I can display an execution plan of a query.
But is it possible to obtain cost of queires "by SELECT"?
For example - I have complex reports and their execution time depends on plenty of conditions that can be specified by user. Sometimes it can be processed quickly, another time it is very slow. And I want to warn the user, that with this set of conditions it can be very slow.
My problem is that I am not able to detect this situation on my own - even with the same conditions it can be different for each customer depending on the data and HW configuration etc..
So it would be nice for me to to retrieve somehow cost of crucial queries (generated according to set of conditions) before executing them.
Any ideas?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

shamshudheen SSCrazy Points: 2390 More actions · Answer 1

Hi Michal

From my experience you cant know query cost before selecting record but only after you can see the query cost by following statement

SET STATISTICS I0 ON

SET STATISTICS TIME ON

the above two , you have write before your select statment. the result you can see after the query result

i have one more method for fast retrieval of record is

put WITH NOLOCK in your query, it will be very fast, but one disadvantage is there, that is the result of your query cant contain any records affected by other users while you retrieving

cheers

shamsudheen

Michal Mokros Hall of Fame Points: 3259 More actions · Answer 2

Yes, I know that. But is not useful for me beacuse I don't want to execute query.

We already use NOLOCK for queries used in our reports.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You can use the estimated execution plan. Just bear in mind it is an estimate. If you're running into parameter sniffing problems (which from the sound of things you might be), the estimate will show a low cost but the actual query will be slow.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Michal Mokros Hall of Fame Points: 3259 More actions · Answer 4

I know estimated plan. But how can I retrieve its information from an application?! I see that I can do i t in SSMS...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Try SET SHOWPLAN_XML ON, then run the query. It should return the xml as a resultset.

Make sure you switch the Showplan option off, as it will affect all statements run on that connection.

I have never tried getting an exec plan from anywhere other than management studio, so I'm not 100% sure it will work, but I think it's worth a try.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Michal Mokros Hall of Fame Points: 3259 More actions · Answer 6

Michal Mokros

Hall of Fame

Points: 3259

April 2, 2008 at 4:34 am

#797088

:Wow:

That is exactly what I need!

Thanks

Grant Fritchey SSC Guru Points: 398880 More actions · Answer 7

One other thing you might check is running Profiler and getting statement complete events. This puts a significant load on the server, but you'd see the actual cost of each and every query within a procedure or batch as it came across the wire.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Michal Mokros (4/2/2008)
:Wow:
That is exactly what I need!
Thanks

Just bear in mind, as I mentioned earlier, that the estimated execution plan is just that. Estimated.

There's no guarentee that the estimated cost will relate to the query's actual run time. Even the actual execution plan's costs are misleading in some situations.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Michal Mokros Hall of Fame Points: 3259 More actions · Answer 9

That's OK. As I said, I will use it only for warning user before potencially long waiting.