Post reply

Retreiving query cost?

  • 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?

  • April 2, 2008 at 12:49 am

    #797027

    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

  • April 2, 2008 at 1:48 am

    #797041

    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.

  • April 2, 2008 at 2:12 am

    #797045

    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

  • April 2, 2008 at 3:54 am

    #797076

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

  • April 2, 2008 at 4:12 am

    #797080

    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

  • April 2, 2008 at 4:34 am

    #797088

    :Wow:

    That is exactly what I need!

    Thanks

  • April 2, 2008 at 6:45 am

    #797160

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • April 2, 2008 at 6:52 am

    #797167

    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

  • April 3, 2008 at 12:33 am

    #797621

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

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

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