SQL Server Performance

Insert in large table sometimes takes too long

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by joerattz, May 5, 2009.

  1. joerattz New Member

    I have a table with approximately 17,171,359 rows. I also have 16 indexes on that same table. Some of those indexes may no longer be necessary, but I am not sure which ones yet.
    Using the SQL Server Profiler, I can see that most of the time, when everything seems to be performing fine, an insert into that table takes <150ms. However, sometimes something seems to go wrong and those inserts take substantially longer, sometimes exceeding the timeout. I am having a difficult time determining what is causing the inserts to suddenly start taking so long. I am also running Performance Monitor concurrently with the profiler trying to determine what the problem is. I can say that there seems to be a correspondence between Avg. Disk Queue Length and the inserts taking too long. That is, when the inserts start taking a long time, the Avg Disk Queue Length pegs the top of the graph.
    The frequency of this happening seems to be increasing, but at the current time, it is a bit hard to predict when this will happen. I am basically having to sit and watch the profiler and performance monitor just waiting for it to happen. Not a productive way to track this down. Sure would like a tool that would snapshot the system when it gets in a situation like this to help me pinpoint where the problem is!
    I don't believe this problem was happening a month or more ago. Its hard to tell because I only would have noticed when the delays were so long that inserts started timing out, which isn't the case most of the time. Even when it starts misbehaving and inserts take 30 seconds, it ususally corrects itself and there are no errors.
    So I am wondering, is the problem that I have too many indexes? I assume that when an insert occurs, all the indexes have to be updated too. Is it possible that with a table this large, and 16 indexes, that is just too much? Since it has fine performance most of the time though, that just doesn't seem very likely.
    Anyone have any suggestions that could help me pinpoint the problem?
    I am planning to analyze the indexes and delete any that are not necessary. I am concerned it may be difficult for me to determine which ones are not necessary. Is there anything that will give me statistics on when indexes were last used or how frequently?
    Also, I could consider deleting some of the older rows from the table. It is history and I hate to do it, especially if something else is really causing the problem and the number of rows isn't really the problem, but I could delete some of the older rows.
    Thanks.
  2. FrankKalis Moderator

    This may or may not have something to do with your clustered index, e.g. there might be page splits that affect performance.
    Generally speaking is maintaining indices very costly. Thus, themore indices you have, the more time needs to be spent to maintainthem. Without knowing more about your system, I would say that 16indices are very much and i would start there investigating if you canreduce this number. Either by dropping some, combining, or redefining them. Since you've posted your question in the 2005 forum, I guess you're on this version. In that case, look at the INCLUDE() clause for indices.
    Here is a script I use to check index usage. It might get you started:
    SELECT
    o.name AS object_name,
    i.name AS index_name,
    i.type_desc,
    u.user_seeks, u.user_scans,
    u.user_lookups, u.user_updates,
    o.type
    FROM
    sys.indexes i
    JOIN
    sys.objects o ON i.object_id = o.object_id
    LEFT JOIN
    sys.dm_db_index_usage_stats u ON i.object_id = u.OBJECT_ID
    AND i.index_id = u.index_id
    AND u.database_id = DB_ID()
    WHERE
    o.type IN ('U', 'V') AND
    i.name IS NOT NULL
    ORDER BY
    o.name, i.name
  3. joerattz New Member

    Thanks for that SQL statement, it helped tremendously. I suspected there were some stats somewhere. Isn't it amazing how much a database does and how quickly it does it? To think, in addition to what I want it to do, it is doing all the housekeeping too! Just amazing.
    So, using the data from that query, I found that most of my indexes were not being used. I deleted all but 5 (meaning I deleted 11). A few had 0's for seeks, scans, and lookups. Another few had very low (<10) for seeks, scans, and lookups. Those were probably indexes I created for some ad-hoc queries I was doing to get some statistics that I no longer need, which is why I said in my initial post I thought I had some I could delete.
    One question though concerning those seeks, scans, and lookups.
    1. For how long is that the count for? I ask because even my indexes that are obviously being used are lower than I would have thought if those counts are since the beginning of the index. It makes me think they get reset at some point. Is that correct, or are those counts since the beginning of the index's existance?
    Yes, I am using SQL Server 2005.
    As I mentioned in my initial post, prior to deleting those 11 indexes, the typical insert time for the table in question was typically <150ms. Usually it was >50ms. Sometimes (even when all seemed acceptable) they were hitting 200-300ms. When unacceptable, they were hitting +10s or higher and timing out. Since deleting the unneeded indexes, it is averaging about <15ms. I don't think I ever saw it that low prior to deleting the indexes. Most of the inserts are <10ms, and I see them frequently being 3-10ms! WOW! What a difference! I just hope this means I won't see those problematic times when it starts taking over a second. So far so good though.
    Two more questions (in addition to the one about how long those counts are for from above).
    2. Can you elaborate a bit about your comment concerning the page split and the clustered index? Or point me to a good article/post somewhere? I am not a DBA and don't know what this means, its impact, or how to tune that.
    3. What is the best book on tuning SQL Server (2005 preferably since that is what I am currently using).
    Thanks for all your help.

Share This Page