Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2009
    Posts
    2

    Unanswered: Table reindexing in sql server 2000

    Hello Guys,

    I am new to this forum, I am using SQL server 2000 as my back end. I have 8 GB of database on my production server. However I am very concern about SQL performance, database takes my site down for several times a day.
    Kindly suggest something on how can I overcome from this? Also suggest how can i re index all database tables by using any query or stored procedure.

    Thanks in Advance.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    An 8GB database is not really considered large in the SQL Server world (or most RDBMS worlds). Have you isolated the bottleneck to disk I/O? Get a profiler trace to see what queries are racking up the most reads, then see if you can tune those statements a little better.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11

  4. #4
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"

  5. #5
    Join Date
    Jan 2009
    Posts
    2
    Hello PMASchmed,

    I have run your script with minor changes

    EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?',' ',80)"

    But it made my database response too slow. Do i need to run some thing else?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Explain. Provide context. Give specific examples. Tell us what you have tried. Show us the code that is running slowly. Tell us if it is intermittent or all the time. etc etc

    If there was simply a command
    Code:
    SET DATABASE RUN_FAST
    then there wouldn't be much need for us or this forum would there?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Quote Originally Posted by MCrowley
    An 8GB database is not really considered large in the SQL Server world (or most RDBMS worlds). Have you isolated the bottleneck to disk I/O? Get a profiler trace to see what queries are racking up the most reads, then see if you can tune those statements a little better.
    Any luck on isolating the actual performance bottleneck? How about that profiler trace?

  8. #8
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by chaitu18
    Hello PMASchmed,

    I have run your script with minor changes

    EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?',' ',80)"

    But it made my database response too slow. Do i need to run some thing else?
    I would only run this off hours.

Posting Permissions

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