Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312

    Unanswered: View Performance Problem

    I have a view that has been working fine. The data is returned with a second or 2. But in the last 2 or 3 days it will periodically take so long to execute that it will timeout.

    I am fairly new to SQL Server 2005, so I am not sure what to check. I am opening the view in Access using an ADP and also in Management Studio.

    Also, there are only a couple of users and at the time we are experiencing problems, there really isn't anything going on. No one is trying to run a large query or anything like that.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It could be a locking problem. Keep Management Studio open, and when this happens again, check to see if there is another process blocking your select statement.

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    How would I do that?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    In Object Explorer expand Management. Double click Activity Monitor, and inspect the Blocked column.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Maybe update stats too?

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    It seems to be working now. I updated the statistics for the tables involved in the view.

    Couple questions. We recently deleted a number of records from a large table (several million rows), would that have caused the problem? The large table stores our current records, then periodically they are archived to another database. We have not experienced this problem before though.

    There are several statistics associated with the large table, is there anyway to update them all at once? I double clicked each one and checked the box to update them but it was a little tedious.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    EXECUTE sp_updatestats
    -PatP

  8. #8
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by DCKunkle
    How would I do that?
    sp_who2 still works very well. Open a query window and execute sp_who2.

    -- This is all just a Figment of my Imagination --

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by DCKunkle
    Couple questions. We recently deleted a number of records from a large table (several million rows), would that have caused the problem? The large table stores our current records, then periodically they are archived to another database. We have not experienced this problem before though.
    You can set auto update to true (I would suggest you do this unless you know what you are doing and put in place alternatives). In SS2005 you can have this update asyncronous to the query that triggers the update. Note that the threshold for autoupdate is really high - 20% changes must occur to trigger it. AS such you can experience degradation due to statisitics being out of date and auto stats will not kick in. As such, a regular update stats job is good belt and braces.

Posting Permissions

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