Post reply

Performance issues with SQL Server Stored Procedures

  • January 7, 2009 at 6:25 am

    #383697

    Hi

    I have a stored procedure called sp_importdata. sp_importdata calls several other stored procedures that use data from an import table. The database was orignially writen in SQL Server 2000 and was moved to SQL Server 2005. Does anyone have any tips in finding the stored procedure that is causing the bottleneck?

    TIA

    Graham Harris

  • January 7, 2009 at 6:33 am

    #922484

    Run profiler and capture the SP_Completed event. That will get you the stats for every proc run.

    You could run the proc with the execution plan on and see which queries have the highest cost, with Statistics IO on to see the highest IO impact and/or with Statistics Time on to see what takes the longest.

    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

  • January 7, 2009 at 6:56 am

    #922491

    Tried runing it with the estimated plan and Management studio returned too much info!! I think the answer is, and it is not want I kind of want, is that the whole lot needs to be re written!

    Graham

  • January 7, 2009 at 6:58 am

    #922493

    Too much information in the Estimated execution plan? I don't understand.

    You can export the plan as .sqlplan file, zip it and post it here to have someone look it over.

    Try what Gail says with a trace.

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

  • January 7, 2009 at 7:12 am

    #922510

    Hi

    I have uploaded the estimated plan. See Estimated Plan.zip

  • January 7, 2009 at 7:16 am

    #922512

    Grant Fritchey (1/7/2009)

    Too much information in the Estimated execution plan? I don't understand.

    I'm guessing too many plans.

    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

  • January 7, 2009 at 7:22 am

    #922522

    Yes!! That was the case. I have noticed that some of the stored procs trouble is that the procedure is running now. And I do not want to run it again as it has taken helf a day to process around 100 records!

    Hence the post here.....

    Graham

  • January 7, 2009 at 7:22 am

    #922523

    gharris_35 (1/7/2009)

    Hi

    I have uploaded the estimated plan. See Estimated Plan.zip

    Did you check that before you posted? 😉

    Management studio saves plans one at a time, so all that's attached is a plan that reads 'Execute proc'

    I think you're going to have to use profiler to narrow things down a little. Look for the procs with the highest duration, highest CPU or highest reads. Take the worst one or two and examine them in detail.

    If possible, run the one or two procs that you've identified as worst in management studio and look for the bottlenecks within them. Post here if you need advice on any sections or interpretation of exec plans.

    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

  • January 7, 2009 at 7:31 am

    #922533

    GilaMonster (1/7/2009)

    gharris_35 (1/7/2009)

    Hi

    Did you check that before you posted? 😉

    Whoops!

    I think you're going to have to use profiler to narrow things down a little. Look for the procs with the highest duration, highest CPU or highest reads. Take the worst one or two and examine them in detail.

    If possible, run the one or two procs that you've identified as worst in management studio and look for the bottlenecks within them. Post here if you need advice on any sections or interpretation of exec plans.

    Ta.

    Graham

  • January 7, 2009 at 8:09 am

    #922560

    Use Statistics IO and Statistics Time when you're investigating the procs themselves, along with the exec plan, to see where the problem points are.

    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

  • January 7, 2009 at 10:08 am

    #922697

    Managed to grad the actual execution plan! Please see attached zip.

  • January 7, 2009 at 10:37 am

    #922722

    gharris_35 (1/7/2009)

    Managed to grad the actual execution plan! Please see attached zip.

    I'm probably not going to go through all 30 plans. You'll still need to do what Gail asked for with the trace.

    However, I opened up the first plan, just to get an idea of how things might look. Just the first query does a table scan and a clustered index scan (same thing basically). Since this is the query to load a cursor, these scans might be very costly. So, right off the bat, you need to check the indexes on these tables.

    Same thing on plan 2 and plan 3. Indexes are just not getting used in these queries.

    You need to do what Gail posted to identify which of these are the most painful and then start with them, but it looks like it's going to be pretty endemic.

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

  • January 7, 2009 at 10:41 am

    #922729

    Thanks!

    There is an index on the lead_fromdataid and lead_type though there is a seperate index on both fields. I gues that is why SQL Server is using the clustered index!

    Graham

  • January 7, 2009 at 10:55 am

    #922740

    gharris_35 (1/7/2009)

    Thanks!

    There is an index on the lead_fromdataid and lead_type though there is a seperate index on both fields. I gues that is why SQL Server is using the clustered index!

    Graham

    Just taking Plan 2 as an example, you've got a table scan because the query is running functions as part of the WHERE clause:

    ... and ISNULL(user_disabled,'N') <> 'Y' and ISNULL(user_resource,'false') <> 'true'

    These are just going to automatically cause scans. SQL Server can't use an index to satisify the query. A scan means it looks at every single row in the table. As the table grows, this problem just gets worse and worse.

    To fix this, you migh need to redesign your data storage so that it doesn't store null values and you can always do positive assertions:

    ... AND user_disable = 'N' AND user_resource = 'false'

    That can use an index on those two columns.

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

  • January 7, 2009 at 10:58 am

    #922742

    Thanks for the tip!!

    Graham

Viewing 15 posts - 1 through 15 (of 16 total)

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