SQL Server Performance

Variable Insert Performance

Discussion in 'Performance Tuning for DBAs' started by seasider, May 22, 2003.

  1. seasider New Member

    I have an issue at a Production site that is caused great concern. There are INSERTs taking between 20ms and 30s on one table. This would indicate to me there is a contention issue that causes the Inserts to be blocked.

    My understanding is that to block an INSERT there would need to be a lock on the page that you are inserting into. I cannot see anyway in our application that would cause a page lock to occur.

    Would the INSERT itself cause a page lock that would stay until the end of the transaction? Then if another INSERT was attempted it would be blocked by the first transaction? I am sure I have testing this before and an Insert does not lock a full page.

    Any quick ideas would be greatly appreciated.


  2. bambola New Member

    Do you have a clustered index on the table, and if so on what field (seq. or not)? could be the result of many page splits. what else are you doing within this transaction? How many inserts do you have? try to check sp_who2 with sp_lock to see what's the lock type. open a trance to get a better idea of what is going on (lock escalation?)

  3. vbkenya New Member

    Bambola is leading you to the right track.....only that I think you might want to start from the profiler and follow his instructions (questions) backwards.

    It is dangerous to assume that a lock is the cause of the performance glitch.
  4. seasider New Member

    yes there is a clustered index, it is on a date field though. This is because the main search on the table is always on a date range. This would be set to the current date&time on the insert and then at some point in the future it would be updated to a new date.

    Now I see that this is going to cause issues because rows are going to be moving around the clustered index a lot thus causing fragmentation and splitting. How big an issue do you think this is? Can page splitting really cause variation in performance from millseconds to 20s+?

    The table has approx 600,000 rows, with 200-300 rows being inserted into it each day. Everyone of these inserts will be with the current date/time at the point of insert and then it will be updated to a new date/time usually later in the day.
  5. sqljunkie New Member

    If possible run the DBCC Showcontig to see if the table is fragmented. You may also want to look into what fill factor was used on the cl index for this amount of inserting.
    Besides checking sp_who2 and sp_lock I would run the following query during the insert to see what is causing the blocking:

    select * from master..sysprocesses where spid>50 and waittime>0

    This will give you the blocking spid and what they're waiting on. You'll know right away if it is a pagelock or not.
  6. seasider New Member

    The table is not fragmented yet but it is becoming more fragmented very quickly. It was reindexed a day or two ago and is already down to 89%.

    I am trying to run queries to monitor the blocking but it occurs so randomly that it will never occur when you want it to.
  7. bradmcgehee New Member

    When you rebuild your indexes, try a larger fillfactor and pad_index value. Don't make it to huge, as this can also cause performance issues. Perhaps you should try bumping up the current values by 5% or so, and then see what happens.

    Brad M. McGehee, MVP
  8. gaurav_bindlish New Member

    Seasider, having a clustered index on such a coulmn will lock not just the data page but also the index page as the shifting of rows will also lock the index pages. This means there is increased level of locking and hence less concurrency. So my first recommendation would be to change the clustered index from the datetime value to some other column. Say some integer column which is like an identity column. This will help in reducing the page splitting as you have guessed. Also I would like to mention although it may not be a point of concern, doing this will reduce the index size as well. Another thing would be to create an non-clustered index on the datetime column. This will help in the main query. Having a non-clustered index on this column will definately help as nw only the leaf page row indeicating the record will change on the non-clustered index and so we should experience low index page loccking.

  9. seasider New Member

    Thanks guys I will give it a go

Share This Page