Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003
    Posts
    144

    Exclamation Unanswered: Indexes degrade too fast

    (Win2003, SQL Server 2000 SP4)

    I have a database of about 5Gb of size. Some queries where taking more than 1 minute to complete execution (all of them are stored procedures). Because of that lack of performance, I call the command DBREINDEX for each table, executed the sp_updatestats system stored procedure and finally I executed the sp_recompile system stored procedure for each sp in my database.

    After all this task, queries completed in a matter of a few seconds instead of minutes. Strange enough is that some hours later (about 6 hrs), after normal use (this database belong to a Client/Server information system), the problem appeared again: Queries started to take too long to complete.

    I am assuming that indexes are degrading too fast so that they required another ReIndex, but I am not sure.

    Any thoughts? How can I prevent this behaviour?

    Thank a lot in advanced.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Have a look at the execution plan before and after the re-indexing routine. This will give you the "good" plan, and the "bad" plan. If the plans are identical, then the reindexing helped (doubtful but possible on a 5Gb system). More likely, the sp_recompile bumped the plan back from the "bad" plan to the "good" plan. By now, you are wondering if I just like putting "good" and "bad" in quotes. Well, no, I am not particularly fond of it, but it does remind you that not all plans are created equal. Can these procedures take in parameters that can determine a range? If so, then the "bad" plan may be good for some ranges, and the "good" plan may be bad for the same range.

  3. #3
    Join Date
    Mar 2003
    Posts
    144

    Question

    Thanks. What I really find confusing is why the performance of the database degrade in a matter of just 6 hours or so.

    Of all the customers that I have distributed the same DB design, this one is the only one that presents this problem; and the size of the database is not the issue: I have other customer that has a 15 Gb database with the same DB design, then same DBMS version but with a not-as-good hardware. However, this customer does not have the same problem.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    What does DBCC SHOWCONTIG show you on the tables in question once the slow down restarts? Do you see high fragmentation numbers and low density numbers? Then yes your indices have degraded. have you traced the 2 systems. Does the one that is experiencing the issue have more insert and updates goinng on? Is it growing at a faster rate?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Mar 2003
    Posts
    144

    Question

    Thanks. I will check that out. Just in case: Which DBCC command allow me to defrag all the tables of a database? I am planning to run that command and later run a DBCC REINDEX command for each index of every table. I suppose that this would produce a clean database, am I right?

    Thanks in advance.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    There is no command to rebuild all of the tables/indexes at once. You can create or google for scripts that will do that for you, however.

Posting Permissions

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