Prev: SSMS Won't Open Database
Next: Multi-value paramter syntax...help
From: SmartbizAustralia on 18 Jan 2010 21:19
Tricky one.

Have a database getting alot of locks...Darn sql server and those
silly page locks.
Anyway had to change the application to use a disconnected methodology
and check for changes before doing an update. Love the asp.net
alternative. It just clobbers data (unless you write code to
check)...record locks have their uses but...right now need to reduce
any locking possible and one article on the web mentioned even tables
where you just add data too could do with a fill factor.

One table just keeps a track of changes by the record id.
There is a clustered index on the record id but it is not unique.
(E.g. there is a new record for each field changed for that record)
Will locks be reduced if a fill factor of say 70 is added, and is it
even relevant for a clustered index?
There's also the pad factor....can see that is more relevant for non-
clustered indexes but what about clustered?

Regards,
Tom
From: Gert-Jan Strik on 20 Jan 2010 15:47
SmartbizAustralia wrote:
>
> Tricky one.
>
> Have a database getting alot of locks...Darn sql server and those
> silly page locks.
> Anyway had to change the application to use a disconnected methodology
> and check for changes before doing an update. Love the asp.net
> alternative. It just clobbers data (unless you write code to
> check)...record locks have their uses but...right now need to reduce
> any locking possible and one article on the web mentioned even tables
> where you just add data too could do with a fill factor.
>
> One table just keeps a track of changes by the record id.
> There is a clustered index on the record id but it is not unique.
> (E.g. there is a new record for each field changed for that record)
> Will locks be reduced if a fill factor of say 70 is added, and is it
> even relevant for a clustered index?
> There's also the pad factor....can see that is more relevant for non-
> clustered indexes but what about clustered?
>
> Regards,
> Tom

Unless you explicitely specify page locks (which you are probably not
doing), then changing the fillfactor to such a large number will
probably only waste space (with its potential negative effect on
performance). The same is true for the pad factor. If you disregard
explicit page locks, then these "factors" have no relation to blocking.

You will probably have to investigate when you lock rows, and whether
the used locking strategy is the best for your purpose. You could
investigate which indexes are causing the most blocking. You might come
to all kinds of conclusions, including the following:
- no blocking, just long lasting locks because of an I/O bottleneck
- too many long lasting transactions covering too many rows
- unintended lock escalation
- too many indexes
- unintended exclusive locks instead of shared locks
- inappropriate data model

Good luck,
Gert-Jan
 | 
Pages: 1
Prev: SSMS Won't Open Database
Next: Multi-value paramter syntax...help