[quoted text, click to view] <aaronGerlich@gmail.com> wrote in message
news:1167413336.159314.140340@48g2000cwx.googlegroups.com...
> Hi everyone,
>
> I have been experiencing a problem that is locking up queries on some
> of my db's. We do a lot of record updating, deleting, and inserting
> with a good number of indexes and it appears as if occassionaly
> something will happen to the table that causes all queries that use the
> index to just halt in there tracks. It won't return an error but just
> hang.
>
> I have tried running a number of the dbcc maintenance scripts on the db
> to try and figure out what table the problem is happening but all
> return everything is ok. The only thing i have found to fix it, is to
> reindex all the user tables.
>
> I was wondering if anyone has any methods to determine what index this
> may be happening on,
For this answer I am translating "halt in their tracks" to blocking.
(Deadlocking, although similar, is more likely an application design issue.)
When blocking occurs I run sp_who2 which will list which spid are being
blocked and using that information find the root spid doing the blocking.
The I run sp_lock to find what exclusive locks are be held by that blocking
spid. This gives object id's on which the exclusive locks are being held.
Looking up the objects using these id's will lead you to the source of your
problem.
Solving the problem is usually much harder, but usually a good first step is
the make sure your data reads are done with the (nolock) hint where
possible. The next step would be to use an identity column for your
clustered index. This helps to avoid physical reorganization of the table
pages during inserts and updates.
[quoted text, click to view] > so i don't have to reindex the whole db.
>
> Ive tried
> dbcc checkdb
> dbcc checktable
> dbcc dbrepair
>
> none of these work, i have been using dbcc dbreindex(@tablename) and
> running in a cursor of Usertables.
>
> Thanks,
> Aaron
>
