reads on inserts

Question

reads on inserts

mjschwenger

SSCarpal Tunnel

Points: 4019
More actions
May 14, 2007 at 9:46 am

#62501

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

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

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

sqldba-294117 Hall of Fame Points: 3486 More actions · Answer 1

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

Antares686 SSC Guru Points: 125444 More actions · Answer 2

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.

mjschwenger SSCarpal Tunnel Points: 4019 More actions · Answer 3

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.

ShekharNaidu SSCrazy Points: 2092 More actions · Answer 4

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

mjschwenger SSCarpal Tunnel Points: 4019 More actions · Answer 5

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

Sorin Petcu Hall of Fame Points: 3195 More actions · Answer 6

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.

Antares686 SSC Guru Points: 125444 More actions · Answer 7

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.

mjschwenger SSCarpal Tunnel Points: 4019 More actions · Answer 8

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

Bob Fazio SSChampion Points: 10794 More actions · Answer 9

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.

mjschwenger SSCarpal Tunnel Points: 4019 More actions · Answer 10

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