adding index NETS "Deadlock"

Can you guys help me understand the logic here.
have a very small jobQueue table
JobId
StartTime
EndTime
JobTypeId
ParentJobId
a few other trivial columns
table gets smacked all day long (thousands and thousands of times)
one stored procedure (spGetNextJob) is doing table scans based on no index
placed on StartTime. Taking 1,000+ ms to execute which is clearly
unacceptable.
I add a nonclustered index on StartTime and get Deadlocks in Production.
My confusion is that Indexes dont cause deadlocks, accessing objects in
inconsistent order causes deadlocks....
HOW, can adding an index on the table create a deadlock scenario.
(Table is constantly truncated, so never really has more than a couple
hundred records)
Greg Jackson
PDX, OregonJaxon wrote:

> My confusion is that Indexes dont cause deadlocks, accessing objects
> in inconsistent order causes deadlocks....
> HOW, can adding an index on the table create a deadlock scenario.
Try enabling trace flag T1204. When this flag is enabled, the server will
print out detailed information about any deadlocks.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Add a RowID BigINT Identity(1,1) to the beginning of the table.
Clustered UNIQUE index on RowID.
Noncluster index on Startime
Write stored procedures to do inserts, updates, and deletes.
Make developers use those procedures ONLY for accessing the table.
Watch the system run very fast.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jaxon" <GregoryAJacksonN0SPAM@.hotmail.com> wrote in message
news:uDBxDftAEHA.712@.tk2msftngp13.phx.gbl...
> Can you guys help me understand the logic here.
> have a very small jobQueue table
> JobId
> StartTime
> EndTime
> JobTypeId
> ParentJobId
> a few other trivial columns
> table gets smacked all day long (thousands and thousands of times)
> one stored procedure (spGetNextJob) is doing table scans based on no index
> placed on StartTime. Taking 1,000+ ms to execute which is clearly
> unacceptable.
> I add a nonclustered index on StartTime and get Deadlocks in Production.
> My confusion is that Indexes dont cause deadlocks, accessing objects in
> inconsistent order causes deadlocks....
> HOW, can adding an index on the table create a deadlock scenario.
> (Table is constantly truncated, so never really has more than a couple
> hundred records)
>
> Greg Jackson
> PDX, Oregon
>|||From Geoff's post, If you will not have that many rows, you might get away
with using an INT data type rather than a bigint
Ray Higdon MCSE, MCDBA, CCNA
--
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:%23z$JyxuAEHA.2480@.TK2MSFTNGP11.phx.gbl...
> Add a RowID BigINT Identity(1,1) to the beginning of the table.
> Clustered UNIQUE index on RowID.
> Noncluster index on Startime
> Write stored procedures to do inserts, updates, and deletes.
> Make developers use those procedures ONLY for accessing the table.
> Watch the system run very fast.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jaxon" <GregoryAJacksonN0SPAM@.hotmail.com> wrote in message
> news:uDBxDftAEHA.712@.tk2msftngp13.phx.gbl...
index
>|||SQL Profiler has Deadlock and Deadlock Chain events.
Couple this with looking at the statements being issued, you can get a very
good idea of what's happening, especially which processes are involved.
I think you will find that you are getting deadlocks on only one table. Ther
e are a couple of causes of this that I've had to deal with.
1. You have a trigger on the table. The trigger joins the INSERTED or DELETE
D tables onto itself. This trigger query can result in a Share Table lock. S
o you have two process, both aquire a row level eXclusive lock. They then de
adlock each other attemptin
g to get the Share Table lock. This is an escalation deadlock. This type of
problem foxed my company for 3 years until SQL Server 7.0 can in with the De
adlock and Deadlock Chain events to track it down. It then took 2 hours to f
ind and fix.
2. Two query are both using the same table, and locking a lot of rows. But o
nly one is using an index, or they are using different indexes. The rows are
locked in the order given by the indexes. The rows are then locked out of s
equence. In your case, it s
ounds as if the purge is still scanning the table, so locking all rows.
I hope this helps|||currently, the PKEY and Clustered Index are on JobId Which is a FREAKING
GUID (not my choice).
Currently there is no index on StartTime but when I ADD a nonclustered
there, I get deadlocks.
I've already analyzed the crap out of the deadlock situation.
deadlock is on the job table and is ocurring due to the two sprocs listed
above.
the sprocs SUCK (use cursors, etc etc etc) I'll fix them later.
I just dont understand how adding a nonclustered to the timeStart Column
results in Deadlocks and removing the index alleviates them.
Sure, I will fix the sprocs later (Like a 3 week task including QA, etc)
Could it just be that with the index added, it makes inserts that much
slower such that some transaction is being heald open a little longer
resulting in deadlock ?
In other words, is the index just the straw that is breaking my camels back
?
this entire job queue crap is an architectural nightmare.....the rewrite is
coming in the near future, believe me.
GAJ|||sorry, I didnt mention the sprocs...
the culprits are "spCheckJob" and "spGetNextJob"
they are both quite ugly you dont want me to send the definitions for these
believe me.
GAJ