March 18, 2014 at 12:14 pm
Hi,
I have a question regarding the rowversion / timestamp column. I want to use it to track changes in some tables and I created a non clustered index on the timestamp column (with three includes) to get a better performance while searching / gathering the data. It works very well, but I experienced a big performance hit on the inserts and my first thoughts are that it has something to do with this index. I know that this NCI has to be updated with every insert and so this could have an impact on performance, but we have a lot of other indexes as well, which are also get updated with every insert. Do you have any clue, why this index / column has such a big impact? Our application use a lot of dynamic cursors, maybe it has something to do with that?
Best regards!
March 18, 2014 at 1:59 pm
vip.blade (3/18/2014)
Hi,I have a question regarding the rowversion / timestamp column. I want to use it to track changes in some tables and I created a non clustered index on the timestamp column (with three includes) to get a better performance while searching / gathering the data. It works very well, but I experienced a big performance hit on the inserts and my first thoughts are that it has something to do with this index. I know that this NCI has to be updated with every insert and so this could have an impact on performance, but we have a lot of other indexes as well, which are also get updated with every insert. Do you have any clue, why this index / column has such a big impact? Our application use a lot of dynamic cursors, maybe it has something to do with that?
Best regards!
Dynamic cursors - really?!? SUPER YUCKO!! 🙂
I don't know if they are to blame for this, but I recommend you use sp_whoisactive (amazing freebie found on sqlblog.com) to watch for blocking/locking during normal ops.
Maybe the include fields on this index are fat and those columns are not included on the others? Maybe there is some fillfactor differences?
Hmm, you could also be hitting LATCH problems with that timestamp column. That is an increasing value so all of your inserts will happen on the last page in the chain and that spells latch contention. sp_whoisactive may show that as well. There are some queries you can use to look at latch wait history too. Search the web. If you are getting that you can't do much about it except remove that index.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 19, 2014 at 1:40 pm
Hey,
thanks for your fast response! I already used an extended event to collect long running queries. If it has something to do with latch contention, isn't it right that the insert or update statements should have longer durations than normal, because the update of the NCI takes very long?
The included columns are also included in the other indices and there is no difference in fillfactor.
To get further information, I'll use the sp_whoisactive stored procedure and try some other combinations of the columns.
Do you have any further ideas?
Best regards!
March 19, 2014 at 7:46 pm
Do you have any further ideas?
Not without quite a bit of extra information or access to your system. Sorry, but you just haven't given much detail at all.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 21, 2014 at 1:39 pm
OK, I played a bit around with it and created a filtered index like this (the other index is exactly the same, but it is not filtered):
CREATE NONCLUSTERED INDEX [NCFI_Test] ON [dbo].[Testtable]
(
[varchar20_one] ASC,
[varchar20_two] ASC,
[timestamp] ASC
)
WHERE ([timestamp]>0x000000058AFC0D11) ...
This reduced the index size from around 30 MB to 200 KB and now, as far as I can see, the problems are gone. I have no clue, why this should fix the problem. The index is not unique, because the column [varchar20_two] is the primary key. Do you have any idea or do you need more informations?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply