| Author |
Topic |
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
 Posted - 2007-11-27 : 04:26:57
|
| Hi,After slow performance on the db, I added some clustered indexes on a few huge tables, that resulted in significant improvement. The vendor is now saying they should be removed (back to table scans), as in their experience clustered indexes cause locks.Can a clustered index cause locks? If so, how/why.ThanksDrew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-11-27 : 04:43:39
|
| I have never heard of a clustered index causing locks, unless it happens to lock the same row on an insert (and that would be the vendors code, not the clustered index).If you are happy with the performance while they are on, then do not remove them. |
 |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-11-27 : 04:50:34
|
| you might get conversion locks that lead to blocking or deadlocks when performing concurrent updates or range scans.however this can happen even without clusetered index.of course it depends on your setup and transactions frequency._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-11-27 : 04:59:23
|
| This is a huge financial app vendor, many people would be familiar with. I've been finding more and more problems (like, there are NO stored procedures in the database) that could be causing performance problems. I've yet to talk with their support, but our tech support have spoken to them and said..."Asked us to remove all the clustered indexes which we put on a number of tables. They said something along the lines of they don't recommend clustered indexes, something to do with the order on the disk and how it read/writes/organises the data on it. One of the tables we've put it on is used every time the ****** Service runs and this Service creates locks (this was a problem some users were having when logging on or trying to save a transaction). Removing the indexes should sort this."We have SQL Server on a virtual box, which they are now saying we should remove, as the database is so resource hungry.I'm going to have to start blogging some of this.Drew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-11-27 : 05:02:54
|
| Well, to me it just sounds very badly written, but then so is the app where i'm working at the moment, but we are doing as much as we can to speed it up and do not have to listen to a vendor. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-11-27 : 05:05:05
|
| @drewsalem:that's just a bunch of crap.where's your blog?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-11-27 : 05:10:26
|
 I haven't started it yet.It's just a very popular financial app, I can't believe no one else is having these problems...Oh duck it! Agresso. Anyone else use Agresso?Drew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-11-27 : 05:20:23
|
| never heard of it...._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-11-27 : 09:58:02
|
| So just spoke to the vendor.He says that everytime an update is made on a table with a clustered index, the data needs to be shuffled around in order to optimise the index. If there are lots of updates, then the drive will be working lots and hence... cause bad performance.Is this true? Kind of defeats the purpose of a clustered index.Drew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-11-27 : 10:07:26
|
| hehe... yes that's true but only for variable length columns. for example:1 you have 100 rows on a page with a varchar(1000) column, but every value is just 10 chars long in that column andthen you update one value with a 1000 lenght string this will cause page splits and reshuffling.otherwise this can also happen with inserts if your CI is not an ever increasing unique value.however that's why you should have a nightly (or when ever needed) index degrad/rebuild in place.you can see index fragmentation with dbcc showcontig_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-11-27 : 10:12:08
|
quote:
Originally posted by drewsalem
This is a huge financial app vendor, many people would be familiar with.
Those of us who have been around the block a few times know that the correlation between vendor size and application quality approaches zero.Accounting applications, in particular, are notorious for being poorly written.The representative you are speaking with sounds pretty ignorant to me.e4 d5 xd5 Nf6 |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2007-11-27 : 10:21:49
|
quote:
Accounting applications, in particular, are notorious for being poorly written.
He's confirmed that the application does not use stored procedure (and agrees that this is unusual). He's also confirmed that only if a query exceeds 20-25 seconds (that's not a typo), should we be worried. quote:
1 you have 100 rows on a page with a varchar(1000) column, but every value is just 10 chars long in that column andthen you update one value with a 1000 lenght string this will cause page splits and reshuffling.
But surely that's a very common scenario. Which begs the question, in what situation does a clustered index not become detrimental. I thought it was the daddy of indexes suitable for most tables with a key, where an index goes on the key. quote:
otherwise this can also happen with inserts if your CI is not an ever increasing unique value.
Can you explain what you mean by ever increasing unique value.ThanksDrew---------------------"It's Saturday night; I've got no date, a two litre bottle of Shasta, and my all-Rush mix tape... LET'S ROCK." |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-11-27 : 10:53:26
|
| > Can you explain what you mean by ever increasing unique value.identity, datetime that does not have more than 1 insert in 3 miliseconds, etc> But surely that's a very common scenario. Which begs the question, in what situation does a clustered index not become > detrimental. I thought it was the daddy of indexes suitable for most tables with a key, where an index goes on the key.that's where reindexing and Fill Factor come in._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
|
Can a clustered index cause locks?