SQL Server Performance

LargeTable.

Discussion in 'Performance Tuning for DBAs' started by chandru, Feb 17, 2003.

  1. chandru New Member

    Hi all,

    I have been facing this problem for the last 2 weeks or so. I have a large table, with about 10 million rows. Everyday about 200k rows get added to it. This works fine regularly. About once a week, the table seems to take forever to insert into, 60+ seconds. Selects work fine. I have disabled the update statistics option and run it manually every morning. The server's CPU's are idle at 3%. Any ideas as to what is causing this? Monitoring Perfmon reveals a sudden spike in disk utilization, with extremely large wait times.

    Thanks
    CR

  2. bradmcgehee New Member

    Do you have any indexes on this large table? If so, is one of them a clustered key, and if so, is the clustered key also a primary key? Also, if you have a clustered key on the table, what is the fillfactor, and do you also rebuild this index often? Have you used Performance Monitor to track page splits? If so, what are they before and during the mass insert?

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. chandru New Member

    Sorry I forgot to mention that. Yes the Primary Key (Clustered) is an identity column with a 0 fillfactor. Have not scheduled a re-index, I have been doing it to solve the problem usually. There is no mass insert. The table is used 24 x 7. Should I be using a indexdefrag during low usage hours?

    Thanks
  4. satya Moderator

    You should consider running DBCC DBREINDEX on this table, atleast in regular intervals. Indexdefrag will help some extent to avoid fragmentation.

    Also why don't you consider archiving data from this table to other database by just keeping most required set of data, this way you reduce overhead on this 24x7 database. In any OLTP environment this works better than directly attacking production server.

    HTH

    Satya SKJ
  5. chandru New Member

    Additionally, this also does occur during low usage hours.
  6. sqljunkie New Member

    What is the backend I/O subsystem configured as? How bad is the queuing and the response times? Are you using RAID-5? You might want to consider upgrading your disk subsystem...
    I would recommend, like satya, that you schedule DBCC Indexdefrag along with your update statistics. You might want to consider a fill factor different than 0. If you are inserting records you are guaranteeing page splits with a fill factor of 0. If you haven't ran a DBCC Indexdefrag yet the tables are probably really fragmented.
    Are the database files auto growing? Or do you have them set to a large size?
  7. chandru New Member

    The problem only occurs once a week / once in 2 weeks. There is no disk queueing during regular hours. There is disk queuing while the problem occurs. I have set the files to auto-grow by 30%. I would look are upgrading the disk subsystem, if this is a problem during peak hours, but this is not. This strangely happens during of peak hours.
  8. sqljunkie New Member

    what do the page splits/sec and checkpoint pages/sec look like when this problem occurs?
  9. bradmcgehee New Member

    DBCC indexdefrag is not a great solution as it doesn't do a great job of defraging logical pages, the only really good solution is to do a complete index rebuild (of course, this will log users out when it is happening). Also, what is your fillfactor?

    You mention that your clustered index in on an indentity column. If this case, page splitting should not be much of an issue as you will be adding data to the end of the table, adding new pages as needed, which is a good thing in your case.

    I don't think I can offer much more help until you have better identified the cause of the slowdown. A combination of a Profiler Trace and Performance Monitor Log during the time of this problem would be very helpful in indentifying the issue. Once you know the actual cause, then finding the solution is much easier.

    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  10. gaurav_bindlish New Member

    Hi,

    These are the thoughts that come to my mind...

    • Since the problem occurs during non peak hours, is there any other activity like some batch job running during these hours?
    • Also since this is a big table, there is a possibility that the space for the table is being allocated (due to auto grow option) during that period. To verify this, I would suggest allocating a big space to database at one shot and then observing if syill the problem persists.
    • As per the design is concerned, I would definately agree with the suggestion posted earlier in the forum - Archive the data which is not required. This will increase performance and the batch window for rebuild od indexes etc. will also decrease.

    • Also consider spreading the table on multiple file groups (on different disks)
    Hope this helps....

    Gaurav

Share This Page