Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2004
    Posts
    306

    Unanswered: SQL Server 7 Stored procedure performance problem

    I have a stored procedure which runs in about 30-40 seconds most of the time, however sometimes it takes over an hour to complete.
    The resultset is the same for both execution times.
    There doesn't appear to be any other significant resource hogging on the server during execution (SQL Server does use 99% CPU while it runs tho)
    The procedure itself is based on 2 views, these views in turn are themselves based on several views and some base tables and so forth.

    Anyone any ideas on how to narrow the problem a little more?

    Cheers.

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    Run it through the analyzer and find out what plan sql comes up with.

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    It could be a blocking problem, with your proc waiting for another thread to release a lock on a resource. Check the following article out:

    http://www.sql-server-performance.com/blocking.asp

    Tom

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Blocking is definitely a possibility. Also run Profiler and see if you're getting any recompiles.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jul 2004
    Posts
    306
    Run it through the analyzer and find out what plan sql comes up with.
    I can't see any obvious difference in the execution plans

    It could be a blocking problem, with your proc waiting for another thread to release a lock on a resource. Check the following article out:

    http://www.sql-server-performance.com/blocking.asp

    Tom
    I don't think its blocking, the server is at 100% CPU while this thing runs which makes it nearly impossible to do anything, afterward I don't see any wait times for the process.
    I am unfamiliar with the system (its a legacy reporting system I have been asked to look at) and I'm new to SQL Server as I'm a DB2 DBA and I have just agreed take a look see.

    Cheers for the ideas so far!

    Gerry

  6. #6
    Join Date
    Jul 2004
    Posts
    306
    Quote Originally Posted by rdjabarov
    Blocking is definitely a possibility. Also run Profiler and see if you're getting any recompiles.
    I'll take a look for the profiler (I have no idea where it is or how to use it) and I'll get back with results if I can manage it

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Now THAT's a new piece of info we should have known before! CPU at 100%??? That's an indicator that the plan changes between executions!!!! Take my word for it, man, I've written masterpieces like this!!!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Jul 2004
    Posts
    306
    (SQL Server does use 99% CPU while it runs tho)
    *cough* I DID actually mention that :P

    Problem is I didn't write this monster, and it is a monster, some guy who left the company years ago did. I'm a DB2 contractor who has been asked to take a look at some of their legacy SQL Server systems, this one being a reporting system which has about 7-10 layers of views based on views based on "temporary" tables filled by stored procedures which are based on views based on views some of which span 2 databases.
    I have also discovered that there are NO indexes defined on either database.

    Really I'm just trying to uncover why 40% of the time the execution time leaps so dramatically

  9. #9
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You said the recordset is exactly the same, but is how it gets there the same? Do you have any OR statements or LIKE '%' + @parameter + '%' or IF logic in the procedure? It's very possible that if you have this, when it hits the right one it gets stuck with a bad execution plan. It might be using the same one even when it shouldn't. If you have this kind of logic, I would break it into several subqueries off the main query and see if that fixes it.

    Also, you need to get indexes in the right place on the database (goes without saying).
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  10. #10
    Join Date
    Jul 2004
    Posts
    306
    Yea, indexes are a must, but there is a BIG reluctance to make any changes to this system, and none of the tables have one so its like "OK guys I wanna add about 30 indexes to the system" and the reply is less than enthuasiastic, especially since I am not an MS-SQL guy and they possibly don't want a rank amature wrecking something that nobody left in the company understands anymore

  11. #11
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Print this thread off and hand it to them. They need to realize this will make a DRASTIC increase in performance on this system. It sounds like they need to do a redesign of the whole system, but that's another story.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by derrickleggett
    Print this thread off and hand it to them. They need to realize this will make a DRASTIC increase in performance on this system. It sounds like they need to do a redesign of the whole system, but that's another story.

    Derrick....that's some real life advice....

    Well, not exactly...

    Here's some...

    DON'T DO ANYTHING.....

    unless they tell you, then get it in writing....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yup, my attention span is very short...You DID mention the CPU. But indexes???!!! In QA fire the "big mama" after setting IO time and CPU time ON in connection settings (right-click anywhere in QA window)

    Pick the worst, drill down, and find the base table or 2.

    Pick the fields that participate in the join and ORDER BY clause...Well, start with the JOIN/WHERE and create a couple of indexes (document each index name and fields that are involced)

    See if that improves anything.

    Brett & Derrick, I don't think they'll do redesign. They'll probably move towards putting the whole thing on UDB/DB2, but not redesign. That's how management works: "Let's put it on a different platform and hope that all problems disappear!"
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  14. #14
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Then they're stupid enough, they deserve what they get. (grin)
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Right on! Except they think it's always me
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •