reads on inserts

  • I had some complains for slowing down of a 3rd party application running on Sql server 2000-Sp4 on Windows 2003 server SP1.

    When setting up a Profiler trace, I could see that some insert statements have a duration >1000 and all of them have a lot of reads on the insert. There are 2-3 single indexes on each of these tables and 1 composite of 4 columns.

    What can I do to tune this situation? What else to monitor to give me better idea what is going on?

    Thanks a lot, mj

  • in this incidence there is drastic change of distribution statistics on all index keys. try to look from application side whether it is calling store procedure or making adoc's query. how the inserts are made & is there any trigger's involved. need to provide more information about index on individual & covered. how covered index is configured, is there any constraints on the table, is your code using any locking hints etc, what is ratio for fillfactor if there is any, etc. i hope this helps

  • Just out of curiosity but what are the columns in each index and which is the clustered index? I ask because a lot of folks make mistakes with composite indexes with regards to what they need to have in them and what is there by default due to the clustered index.

  • There are no SPs in the applciation - all handled by Rogwave library's calls - prepared SQL. No triggers, or FK - so the app will not lose times on inserts. For the same reason (faster inserts), there is not a single clustered index - all indexes are created as non clustered. (I do not agree with that but this is the case). There are multiple Not Null constraints as you will see in the trace, no locking hints at all.

    The 4 field index is actually used by the applciation as an unique constraint rather then index, but it is in use as well.

    The inserts are made as straight insert - Below is what I get in the trace for 1 of these tables. For this query I get 45 reads, 1 write, 0 CPU and 1863 duration.

    INSERT INTO ATTRIBUTE(ATTR_ID,ATTR_DT,ATTR_TYPE_ID,ATTR_VALUE,DSRC_ACCT_ID,ENTITY_ID,HIST_STAT,QC_STAT,SYS_CREATE_DT,SYS_DELETE_DT,SYS_LSTUPD_DT,VALID_FROM_DT,VALID_THRU_DT) VALUES(@P1,NULL,@P2,@P3,@P4,@P5,@P6,@P7,@P8,NULL,@P9,NULL,NULL)

    exec sp_execute 208734, 682471, 2, 'F', 782539, 779015, 0, 0, '2007-05-13 08:18:26', '2007-05-13 08:18:26'

    Execution Tree

    --------------

    Table Insert(OBJECT[test].[dbo].[ATTRIBUTE]), SET[ATTRIBUTE].[SYS_DELETE_STAT]=NULL, [ATTRIBUTE].[SYS_DELETE_DT]=NULL, [ATTRIBUTE].[SYS_LSTUPD_DT]=RaiseIfNull([Expr1007]), [ATTRIBUTE].[SYS_CREATE_DT]=RaiseIfNull([Expr1005]), [ATTRIBUTE].[VALID_THRU_DT]=NULL, [ATTRIBUTE].[VALID_FROM_DT]=NULL, [ATTRIBUTE].[QC_STAT]=RaiseIfNull([Expr1004]), [ATTRIBUTE].[ATTR_DT]=NULL, [ATTRIBUTE].[ENTITY_ID]=RaiseIfNull([@P5]), [ATTRIBUTE].[ATTR_ID]=RaiseIfNull([@P1]), [ATTRIBUTE].[HIST_STAT]=RaiseIfNull([Expr1003]), [ATTRIBUTE].[ATTR_TYPE_ID]=RaiseIfNull([Expr1001]), [ATTRIBUTE].[DSRC_ACCT_ID]=RaiseIfNull([@P4]), [ATTRIBUTE].[ATTR_VALUE]=RaiseIfNull([Expr1002])), DEFINE[Expr1001]=Convert([@P2]), [Expr1002]=Convert([@P3]), [Expr1003]=Convert([@P6]), [Expr1004]=Convert([@P7]), [Expr1005]=Convert([@P8]), [Expr1007]=Convert([@P9])))

    Thanks a lot for your help.

  • I think it is because of not having a CLustered Index on the tables... which might cause longer scan on tables.

    It is recommended to have atleast one clustered index...


    Thanks ,

    Shekhar

  • I changed the indexes around but converting the non-clustered to clustered index on most accessed field did not change a thing. I also tried different combinations for the clustering.

    What else I could try to eliminate the reads on insert?

    Or, at least to see why they appear?

    Thanks a lot, mj

  • i have the same problem. but i'm concerned about something else. I have an table with 2 indexes, one clustered and one non-clustered. I have a lot of inserts - about 2 millions a day. Also, I have some jobs which runs in the night - they work on reindex all the tables and update statistics on all tables. I do not know why "Mem usage" and "VM size" in Task Manager are increasing until SQL Server reach a limit when it says "Insufficient memory".

    In Theory, theory and practice are the same...In practice, they are not.
  • Still trying to figure out the other note to give a logical reason or a way to reduce. But as for your item Sorin, I would look at the number of active connections in SQL Server. Sounds like the application is opening and not reusing pooled connections or not closing connections at all during each run.

  • I tried the advice to use clustered indexes, and still do not see any improvement in the inserts. Systematically seeing reads on them as the example above.

    Investigated the connection from the application - all the application does is to read the new data, select to compare if this data in already in the database, and, if - not, then inserts it. This is all done by opening and closing a transaction. Then it goes to the next new data set. These sets might come from a file, database, http, etc. different ways - but on all of them the reads on the inserts are there.

    What else I could monitor to figure out why it is doing it - I suspesct this is the applciation and/or their rogwave database layer, but still need help on how to appropach this issue.

    Thanks a lot, mj

  • It sounds like the obvious hasn't been stated.  Some inserts take over a second, but with 2m per day they all aren't.  Reads block.  Sounds like during the times when they meet, you are getting the wait.

    Change the reads to nolock (assuming you can deal with uncommited transactions.  (are you using transactions?) 

    If you are using transactions consider using version based locking in 2005.  It isn't much slower, but no blocking.

  • Thanks a lot.

    yes, the application uses transactions.Unfortunately, I cannot use uncommitted transaction. For a while I'll be on SQl server 2000 (2005 is scheduled for late 2008) so I have to deal with this.

    Also, I do not see any blocking/locking time spent. Just the reads on the inserts and they are taking more time and CPU. On most of the major tables the reads are consistently present on every single insert, and consistently have the same values.

    This is most likely Application issue, but I do not know where to start to troubleshoot it... If I could get to the cause, I'll convince the development for changes needed

    Thanks a lot, mj

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply