Re: adding index NETS "Deadlock" ???

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Ray Higdon (sqlhigdon_at_nospam.yahoo.com)
Date: 03/05/04 

Date: Fri, 5 Mar 2004 18:17:09 -0500

>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...
> > 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
> >
> >
>
>

Relevant Pages

  • Re: Stored procedure error handling
    ... i'm trying to do some error checking on stored procedures and am ... > DECLARE @newSupplierId as bigint ... > INSERT INTO Supplier ... > SET NOCOUNT OFF ...
    (comp.databases.ms-sqlserver)
  • Re: SPs or triggers
    ... would be sending ad-hoc query statements from your application to SQL Server ... Stored procedures help you abstract your ... To import user data by registration whit SP or Trigger? ... > @ValuePrice BIGINT, ...
    (microsoft.public.sqlserver.programming)
  • Stored procedure error handling
    ... i'm trying to do some error checking on stored procedures and am ... CreateSupplier procedure and checking for errors, ... DECLARE @newAddressDetailId as bigint ...
    (comp.databases.ms-sqlserver)