[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Deadlock on sql server

Roy Goldhammer

3/22/2007 2:56:00 PM

Hello there

I've run very complex query on sql server, and in some of the huge tables
there aren't any indexes, including primary key.

This cause failure of my query on error: Process 81 become deaclock

How missing of indexes could cause deadlock?


6 Answers

Russell Fields

3/22/2007 3:07:00 PM

0

Roy,

Useful indexes help your complex query to hold fewer locks on the tables,
which it must scan otherwise. They are no guarantee of avoiding deadlocks,
but by reducing locks they narrow the oppotunity for a deadlock.

However, there are many more things to try. In the SQL Books Online, look
for the topic "Minimizing Deadlocks". If you are using SQL 2005 you have
additional options (including TRY ... CATCH) to handle deadlock issues.


RLF
"Roy Goldhammer" <roy@hotmail.com> wrote in message
news:u8l67HJbHHA.1400@TK2MSFTNGP06.phx.gbl...
> Hello there
>
> I've run very complex query on sql server, and in some of the huge tables
> there aren't any indexes, including primary key.
>
> This cause failure of my query on error: Process 81 become deaclock
>
> How missing of indexes could cause deadlock?
>


Roy Goldhammer

3/22/2007 3:20:00 PM

0

Whell Russell:

I'm using sql server 2000

And i can't add more indexes to the database because the amout of ram now is
1.76 GB in standard edition that can use only 2GB.

So in this limitations what can i do?

"Russell Fields" <russellfields@nomail.com> wrote in message
news:eaUAvOJbHHA.5052@TK2MSFTNGP06.phx.gbl...
> Roy,
>
> Useful indexes help your complex query to hold fewer locks on the tables,
> which it must scan otherwise. They are no guarantee of avoiding
> deadlocks, but by reducing locks they narrow the oppotunity for a
> deadlock.
>
> However, there are many more things to try. In the SQL Books Online, look
> for the topic "Minimizing Deadlocks". If you are using SQL 2005 you have
> additional options (including TRY ... CATCH) to handle deadlock issues.
>
>
> RLF
> "Roy Goldhammer" <roy@hotmail.com> wrote in message
> news:u8l67HJbHHA.1400@TK2MSFTNGP06.phx.gbl...
>> Hello there
>>
>> I've run very complex query on sql server, and in some of the huge tables
>> there aren't any indexes, including primary key.
>>
>> This cause failure of my query on error: Process 81 become deaclock
>>
>> How missing of indexes could cause deadlock?
>>
>
>


Tibor Karaszi

3/22/2007 3:43:00 PM

0

> And i can't add more indexes to the database because the amout of ram now is 1.76 GB in standard
> edition that can use only 2GB.

The amount of RAM that SE supports has nothing (very little) to do with how large databases you can
have. In fact, by having proper indexes, SQL will have to read *less* data to find the rows you are
after and will *reduce* amount of RAM needed.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://www.solidqualitylea...


"Roy Goldhammer" <roy@hotmail.com> wrote in message news:e3QPWVJbHHA.5044@TK2MSFTNGP05.phx.gbl...
> Whell Russell:
>
> I'm using sql server 2000
>
> And i can't add more indexes to the database because the amout of ram now is 1.76 GB in standard
> edition that can use only 2GB.
>
> So in this limitations what can i do?
>
> "Russell Fields" <russellfields@nomail.com> wrote in message
> news:eaUAvOJbHHA.5052@TK2MSFTNGP06.phx.gbl...
>> Roy,
>>
>> Useful indexes help your complex query to hold fewer locks on the tables, which it must scan
>> otherwise. They are no guarantee of avoiding deadlocks, but by reducing locks they narrow the
>> oppotunity for a deadlock.
>>
>> However, there are many more things to try. In the SQL Books Online, look for the topic
>> "Minimizing Deadlocks". If you are using SQL 2005 you have additional options (including TRY ...
>> CATCH) to handle deadlock issues.
>>
>>
>> RLF
>> "Roy Goldhammer" <roy@hotmail.com> wrote in message news:u8l67HJbHHA.1400@TK2MSFTNGP06.phx.gbl...
>>> Hello there
>>>
>>> I've run very complex query on sql server, and in some of the huge tables there aren't any
>>> indexes, including primary key.
>>>
>>> This cause failure of my query on error: Process 81 become deaclock
>>>
>>> How missing of indexes could cause deadlock?
>>>
>>
>>
>
>


Roy Goldhammer

3/25/2007 7:33:00 AM

0

Thankes Tibor.

This is good issue.

Where can i find offical document that support this metter?

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:OdIXHjJbHHA.1300@TK2MSFTNGP02.phx.gbl...
>> And i can't add more indexes to the database because the amout of ram now
>> is 1.76 GB in standard edition that can use only 2GB.
>
> The amount of RAM that SE supports has nothing (very little) to do with
> how large databases you can have. In fact, by having proper indexes, SQL
> will have to read *less* data to find the rows you are after and will
> *reduce* amount of RAM needed.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/d...
> http://www.solidqualitylea...
>
>
> "Roy Goldhammer" <roy@hotmail.com> wrote in message
> news:e3QPWVJbHHA.5044@TK2MSFTNGP05.phx.gbl...
>> Whell Russell:
>>
>> I'm using sql server 2000
>>
>> And i can't add more indexes to the database because the amout of ram now
>> is 1.76 GB in standard edition that can use only 2GB.
>>
>> So in this limitations what can i do?
>>
>> "Russell Fields" <russellfields@nomail.com> wrote in message
>> news:eaUAvOJbHHA.5052@TK2MSFTNGP06.phx.gbl...
>>> Roy,
>>>
>>> Useful indexes help your complex query to hold fewer locks on the
>>> tables, which it must scan otherwise. They are no guarantee of avoiding
>>> deadlocks, but by reducing locks they narrow the oppotunity for a
>>> deadlock.
>>>
>>> However, there are many more things to try. In the SQL Books Online,
>>> look for the topic "Minimizing Deadlocks". If you are using SQL 2005
>>> you have additional options (including TRY ... CATCH) to handle deadlock
>>> issues.
>>>
>>>
>>> RLF
>>> "Roy Goldhammer" <roy@hotmail.com> wrote in message
>>> news:u8l67HJbHHA.1400@TK2MSFTNGP06.phx.gbl...
>>>> Hello there
>>>>
>>>> I've run very complex query on sql server, and in some of the huge
>>>> tables there aren't any indexes, including primary key.
>>>>
>>>> This cause failure of my query on error: Process 81 become deaclock
>>>>
>>>> How missing of indexes could cause deadlock?
>>>>
>>>
>>>
>>
>>
>
>


Tibor Karaszi

3/25/2007 7:52:00 AM

0

Books Online is a good start, for instance the "Physical Database Architecture" section.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/d...
http://sqlblog.com/blogs/tib...


"Roy Goldhammer" <roy@hotmail.com> wrote in message news:%23ErtA%23qbHHA.596@TK2MSFTNGP06.phx.gbl...
> Thankes Tibor.
>
> This is good issue.
>
> Where can i find offical document that support this metter?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message
> news:OdIXHjJbHHA.1300@TK2MSFTNGP02.phx.gbl...
>>> And i can't add more indexes to the database because the amout of ram now is 1.76 GB in standard
>>> edition that can use only 2GB.
>>
>> The amount of RAM that SE supports has nothing (very little) to do with how large databases you
>> can have. In fact, by having proper indexes, SQL will have to read *less* data to find the rows
>> you are after and will *reduce* amount of RAM needed.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/d...
>> http://www.solidqualitylea...
>>
>>
>> "Roy Goldhammer" <roy@hotmail.com> wrote in message news:e3QPWVJbHHA.5044@TK2MSFTNGP05.phx.gbl...
>>> Whell Russell:
>>>
>>> I'm using sql server 2000
>>>
>>> And i can't add more indexes to the database because the amout of ram now is 1.76 GB in standard
>>> edition that can use only 2GB.
>>>
>>> So in this limitations what can i do?
>>>
>>> "Russell Fields" <russellfields@nomail.com> wrote in message
>>> news:eaUAvOJbHHA.5052@TK2MSFTNGP06.phx.gbl...
>>>> Roy,
>>>>
>>>> Useful indexes help your complex query to hold fewer locks on the tables, which it must scan
>>>> otherwise. They are no guarantee of avoiding deadlocks, but by reducing locks they narrow the
>>>> oppotunity for a deadlock.
>>>>
>>>> However, there are many more things to try. In the SQL Books Online, look for the topic
>>>> "Minimizing Deadlocks". If you are using SQL 2005 you have additional options (including TRY
>>>> ... CATCH) to handle deadlock issues.
>>>>
>>>>
>>>> RLF
>>>> "Roy Goldhammer" <roy@hotmail.com> wrote in message
>>>> news:u8l67HJbHHA.1400@TK2MSFTNGP06.phx.gbl...
>>>>> Hello there
>>>>>
>>>>> I've run very complex query on sql server, and in some of the huge tables there aren't any
>>>>> indexes, including primary key.
>>>>>
>>>>> This cause failure of my query on error: Process 81 become deaclock
>>>>>
>>>>> How missing of indexes could cause deadlock?
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Johanna Gronlund

4/1/2010 8:20:00 AM

0

Thanks, this was very useful and worked.

Johanna



"Jason Falzon" wrote:

> Also in your case use
>
>
> Sheets("Sheet 10").Activate
>
> and not Sheet10 as your sheet name has a space.
>
>
> Jason
>
> "Jason Falzon" wrote:
>
> > Try attaching this macro
> >
> > Sub Button4585_Click()
> >
> > Sheets("Sheet10").Activate
> > 'Range("A147").Select 'this will go to A147
> > Range("A147:A180").Select 'this will HIGHLIGHT range
> >
> > End Sub
> >
> >
> > Choose one of the Range() lines only remove ' from Range("A147").select if
> > want to use that
> >
> > Jason
> > "Johanna Gronlund" wrote:
> >
> > > Hello,
> > >
> > > I have a command button (Button 4585). I would like it to take the user to
> > > another place (cells A147:A180) on the same sheet (Sheet 10). Is this
> > > possible? I cannot just use a hyperlink on a cell as the button only becomes
> > > visible if the user makes certain choices.
> > >
> > > Thanks very much in advance.
> > >
> > > Johanna
> > >