I have been trying to track down a database problem in which for some reason
when somebody enters in some data into the database it becauses unavailable
for the application. I looked under Locks/Proccess ID and found that one of
the proccess is 'blocking' can somebody elaborate as to what sort of
processes these are and what blocking means? In addition, about 5 minutes or
so later the application can enter in data again and the web site that uses
the database is available.... any ideas? Thank you.Gabe,
We can tell nothing about the process, we do not have the information. About
blocking, the process is blocking a resource (table, index, row, etc.) and
for that reason other processes do not have access to the resource.
See "Understanding and Avoiding Blocking" in BOL for more info.
AMB
"Gabe Matteson" wrote:
> I have been trying to track down a database problem in which for some reas
on
> when somebody enters in some data into the database it becauses unavailabl
e
> for the application. I looked under Locks/Proccess ID and found that one
of
> the proccess is 'blocking' can somebody elaborate as to what sort of
> processes these are and what blocking means? In addition, about 5 minutes
or
> so later the application can enter in data again and the web site that use
s
> the database is available.... any ideas? Thank you.
>
>|||I have posted screenshots of the issue the resource that is blocking says
its the table, index and row. here are the screenshots, thank you for the
help.
http://66.31.179.80/sql/screenshots.doc
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:920757E2-C5E4-4421-8484-D398215531AF@.microsoft.com...[vbcol=seagreen]
> Gabe,
> We can tell nothing about the process, we do not have the information.
> About
> blocking, the process is blocking a resource (table, index, row, etc.) and
> for that reason other processes do not have access to the resource.
> See "Understanding and Avoiding Blocking" in BOL for more info.
>
> AMB
> "Gabe Matteson" wrote:
>|||test
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:uNiskn2cFHA.228@.TK2MSFTNGP12.phx.gbl...
> I have posted screenshots of the issue the resource that is blocking says
> its the table, index and row. here are the screenshots, thank you for the
> help.
> http://66.31.179.80/sql/screenshots.doc
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message
> news:920757E2-C5E4-4421-8484-D398215531AF@.microsoft.com...
and[vbcol=seagreen]
one[vbcol=seagreen]
minutes[vbcol=seagreen]
>|||Dear Sir,
please run sp_who2 it will let you know which process is blocking
another process.
Regards.
ghemant
---
ghemant's Profile: http://www.msmcse.ms/member.php?userid=2348
View this thread: http://www.msmcse.ms/t-1870545056|||Thank you.
"ghemant" <ghemant.1qtd4p@.no-mx.msmcse.ms> wrote in message
news:ghemant.1qtd4p@.no-mx.msmcse.ms...
> Dear Sir,
> please run sp_who2 it will let you know which process is blocking
> another process.
> Regards.
>
> --
> ghemant
>
> ---
> ghemant's Profile: http://www.msmcse.ms/member.php?userid=2348
> View this thread: http://www.msmcse.ms/t-1870545056
>|||Is there anyway to tell why a process is being blocked?
"ghemant" <ghemant.1qtd4p@.no-mx.msmcse.ms> wrote in message
news:ghemant.1qtd4p@.no-mx.msmcse.ms...
> Dear Sir,
> please run sp_who2 it will let you know which process is blocking
> another process.
> Regards.
>
> --
> ghemant
>
> ---
> ghemant's Profile: http://www.msmcse.ms/member.php?userid=2348
> View this thread: http://www.msmcse.ms/t-1870545056
>|||A process is blocked when a lock cannot be obtained because another session
already has a lock on the requested resource and the requested lock type is
incompatible. Common causes of blocking include long-running transactions
and queries.
You can use DBCC INPUTBUFFER on the blocking spid to see the currently
executing (or last) SQL statement. Use DBCC UPENTRAN to identify the oldest
open transaction. See http://support.microsoft.com/kb/224453/EN-US/ for
detailed information on troubleshooting blocking problems.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eTg013VdFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Is there anyway to tell why a process is being blocked?
> "ghemant" <ghemant.1qtd4p@.no-mx.msmcse.ms> wrote in message
> news:ghemant.1qtd4p@.no-mx.msmcse.ms...
>|||their's a tip to avoid blocking , see the below link.
http://www.sql-server-performance.com/blocking.asp
Regards.
ghemant
---
ghemant's Profile: http://www.msmcse.ms/member.php?userid=2348
View this thread: http://www.msmcse.ms/t-1870545056
Showing posts with label locks. Show all posts
Showing posts with label locks. Show all posts
Monday, February 20, 2012
Locks/Process ID
I have been trying to track down a database problem in which for some reason
when somebody enters in some data into the database it becauses unavailable
for the application. I looked under Locks/Proccess ID and found that one of
the proccess is 'blocking' can somebody elaborate as to what sort of
processes these are and what blocking means? In addition, about 5 minutes or
so later the application can enter in data again and the web site that uses
the database is available.... any ideas? Thank you.Gabe,
We can tell nothing about the process, we do not have the information. About
blocking, the process is blocking a resource (table, index, row, etc.) and
for that reason other processes do not have access to the resource.
See "Understanding and Avoiding Blocking" in BOL for more info.
AMB
"Gabe Matteson" wrote:
> I have been trying to track down a database problem in which for some reason
> when somebody enters in some data into the database it becauses unavailable
> for the application. I looked under Locks/Proccess ID and found that one of
> the proccess is 'blocking' can somebody elaborate as to what sort of
> processes these are and what blocking means? In addition, about 5 minutes or
> so later the application can enter in data again and the web site that uses
> the database is available.... any ideas? Thank you.
>
>|||I have posted screenshots of the issue the resource that is blocking says
its the table, index and row. here are the screenshots, thank you for the
help.
http://66.31.179.80/sql/screenshots.doc
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:920757E2-C5E4-4421-8484-D398215531AF@.microsoft.com...
> Gabe,
> We can tell nothing about the process, we do not have the information.
> About
> blocking, the process is blocking a resource (table, index, row, etc.) and
> for that reason other processes do not have access to the resource.
> See "Understanding and Avoiding Blocking" in BOL for more info.
>
> AMB
> "Gabe Matteson" wrote:
>> I have been trying to track down a database problem in which for some
>> reason
>> when somebody enters in some data into the database it becauses
>> unavailable
>> for the application. I looked under Locks/Proccess ID and found that one
>> of
>> the proccess is 'blocking' can somebody elaborate as to what sort of
>> processes these are and what blocking means? In addition, about 5 minutes
>> or
>> so later the application can enter in data again and the web site that
>> uses
>> the database is available.... any ideas? Thank you.
>>|||test
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:uNiskn2cFHA.228@.TK2MSFTNGP12.phx.gbl...
> I have posted screenshots of the issue the resource that is blocking says
> its the table, index and row. here are the screenshots, thank you for the
> help.
> http://66.31.179.80/sql/screenshots.doc
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message
> news:920757E2-C5E4-4421-8484-D398215531AF@.microsoft.com...
> > Gabe,
> >
> > We can tell nothing about the process, we do not have the information.
> > About
> > blocking, the process is blocking a resource (table, index, row, etc.)
and
> > for that reason other processes do not have access to the resource.
> >
> > See "Understanding and Avoiding Blocking" in BOL for more info.
> >
> >
> > AMB
> >
> > "Gabe Matteson" wrote:
> >
> >> I have been trying to track down a database problem in which for some
> >> reason
> >> when somebody enters in some data into the database it becauses
> >> unavailable
> >> for the application. I looked under Locks/Proccess ID and found that
one
> >> of
> >> the proccess is 'blocking' can somebody elaborate as to what sort of
> >> processes these are and what blocking means? In addition, about 5
minutes
> >> or
> >> so later the application can enter in data again and the web site that
> >> uses
> >> the database is available.... any ideas? Thank you.
> >>
> >>
> >>
>|||Dear Sir
please run sp_who2 it will let you know which process is blockin
another process
Regards
--
gheman
----
ghemant's Profile: http://www.msusenet.com/member.php?userid=234
View this thread: http://www.msusenet.com/t-187054505|||Is there anyway to tell why a process is being blocked?
"ghemant" <ghemant.1qtd4p@.no-mx.msusenet.com> wrote in message
news:ghemant.1qtd4p@.no-mx.msusenet.com...
> Dear Sir,
> please run sp_who2 it will let you know which process is blocking
> another process.
> Regards.
>
> --
> ghemant
>
> ---
> ghemant's Profile: http://www.msusenet.com/member.php?userid=2348
> View this thread: http://www.msusenet.com/t-1870545056
>|||Thank you.
"ghemant" <ghemant.1qtd4p@.no-mx.msusenet.com> wrote in message
news:ghemant.1qtd4p@.no-mx.msusenet.com...
> Dear Sir,
> please run sp_who2 it will let you know which process is blocking
> another process.
> Regards.
>
> --
> ghemant
>
> ---
> ghemant's Profile: http://www.msusenet.com/member.php?userid=2348
> View this thread: http://www.msusenet.com/t-1870545056
>|||A process is blocked when a lock cannot be obtained because another session
already has a lock on the requested resource and the requested lock type is
incompatible. Common causes of blocking include long-running transactions
and queries.
You can use DBCC INPUTBUFFER on the blocking spid to see the currently
executing (or last) SQL statement. Use DBCC UPENTRAN to identify the oldest
open transaction. See http://support.microsoft.com/kb/224453/EN-US/ for
detailed information on troubleshooting blocking problems.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eTg013VdFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Is there anyway to tell why a process is being blocked?
> "ghemant" <ghemant.1qtd4p@.no-mx.msusenet.com> wrote in message
> news:ghemant.1qtd4p@.no-mx.msusenet.com...
>> Dear Sir,
>> please run sp_who2 it will let you know which process is blocking
>> another process.
>> Regards.
>>
>> --
>> ghemant
>>
>> ---
>> ghemant's Profile: http://www.msusenet.com/member.php?userid=2348
>> View this thread: http://www.msusenet.com/t-1870545056
>|||their's a tip to avoid blocking , see the below link
http://www.sql-server-performance.com/blocking.as
Regards
--
gheman
----
ghemant's Profile: http://www.msusenet.com/member.php?userid=234
View this thread: http://www.msusenet.com/t-187054505
when somebody enters in some data into the database it becauses unavailable
for the application. I looked under Locks/Proccess ID and found that one of
the proccess is 'blocking' can somebody elaborate as to what sort of
processes these are and what blocking means? In addition, about 5 minutes or
so later the application can enter in data again and the web site that uses
the database is available.... any ideas? Thank you.Gabe,
We can tell nothing about the process, we do not have the information. About
blocking, the process is blocking a resource (table, index, row, etc.) and
for that reason other processes do not have access to the resource.
See "Understanding and Avoiding Blocking" in BOL for more info.
AMB
"Gabe Matteson" wrote:
> I have been trying to track down a database problem in which for some reason
> when somebody enters in some data into the database it becauses unavailable
> for the application. I looked under Locks/Proccess ID and found that one of
> the proccess is 'blocking' can somebody elaborate as to what sort of
> processes these are and what blocking means? In addition, about 5 minutes or
> so later the application can enter in data again and the web site that uses
> the database is available.... any ideas? Thank you.
>
>|||I have posted screenshots of the issue the resource that is blocking says
its the table, index and row. here are the screenshots, thank you for the
help.
http://66.31.179.80/sql/screenshots.doc
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:920757E2-C5E4-4421-8484-D398215531AF@.microsoft.com...
> Gabe,
> We can tell nothing about the process, we do not have the information.
> About
> blocking, the process is blocking a resource (table, index, row, etc.) and
> for that reason other processes do not have access to the resource.
> See "Understanding and Avoiding Blocking" in BOL for more info.
>
> AMB
> "Gabe Matteson" wrote:
>> I have been trying to track down a database problem in which for some
>> reason
>> when somebody enters in some data into the database it becauses
>> unavailable
>> for the application. I looked under Locks/Proccess ID and found that one
>> of
>> the proccess is 'blocking' can somebody elaborate as to what sort of
>> processes these are and what blocking means? In addition, about 5 minutes
>> or
>> so later the application can enter in data again and the web site that
>> uses
>> the database is available.... any ideas? Thank you.
>>|||test
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:uNiskn2cFHA.228@.TK2MSFTNGP12.phx.gbl...
> I have posted screenshots of the issue the resource that is blocking says
> its the table, index and row. here are the screenshots, thank you for the
> help.
> http://66.31.179.80/sql/screenshots.doc
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message
> news:920757E2-C5E4-4421-8484-D398215531AF@.microsoft.com...
> > Gabe,
> >
> > We can tell nothing about the process, we do not have the information.
> > About
> > blocking, the process is blocking a resource (table, index, row, etc.)
and
> > for that reason other processes do not have access to the resource.
> >
> > See "Understanding and Avoiding Blocking" in BOL for more info.
> >
> >
> > AMB
> >
> > "Gabe Matteson" wrote:
> >
> >> I have been trying to track down a database problem in which for some
> >> reason
> >> when somebody enters in some data into the database it becauses
> >> unavailable
> >> for the application. I looked under Locks/Proccess ID and found that
one
> >> of
> >> the proccess is 'blocking' can somebody elaborate as to what sort of
> >> processes these are and what blocking means? In addition, about 5
minutes
> >> or
> >> so later the application can enter in data again and the web site that
> >> uses
> >> the database is available.... any ideas? Thank you.
> >>
> >>
> >>
>|||Dear Sir
please run sp_who2 it will let you know which process is blockin
another process
Regards
--
gheman
----
ghemant's Profile: http://www.msusenet.com/member.php?userid=234
View this thread: http://www.msusenet.com/t-187054505|||Is there anyway to tell why a process is being blocked?
"ghemant" <ghemant.1qtd4p@.no-mx.msusenet.com> wrote in message
news:ghemant.1qtd4p@.no-mx.msusenet.com...
> Dear Sir,
> please run sp_who2 it will let you know which process is blocking
> another process.
> Regards.
>
> --
> ghemant
>
> ---
> ghemant's Profile: http://www.msusenet.com/member.php?userid=2348
> View this thread: http://www.msusenet.com/t-1870545056
>|||Thank you.
"ghemant" <ghemant.1qtd4p@.no-mx.msusenet.com> wrote in message
news:ghemant.1qtd4p@.no-mx.msusenet.com...
> Dear Sir,
> please run sp_who2 it will let you know which process is blocking
> another process.
> Regards.
>
> --
> ghemant
>
> ---
> ghemant's Profile: http://www.msusenet.com/member.php?userid=2348
> View this thread: http://www.msusenet.com/t-1870545056
>|||A process is blocked when a lock cannot be obtained because another session
already has a lock on the requested resource and the requested lock type is
incompatible. Common causes of blocking include long-running transactions
and queries.
You can use DBCC INPUTBUFFER on the blocking spid to see the currently
executing (or last) SQL statement. Use DBCC UPENTRAN to identify the oldest
open transaction. See http://support.microsoft.com/kb/224453/EN-US/ for
detailed information on troubleshooting blocking problems.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eTg013VdFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Is there anyway to tell why a process is being blocked?
> "ghemant" <ghemant.1qtd4p@.no-mx.msusenet.com> wrote in message
> news:ghemant.1qtd4p@.no-mx.msusenet.com...
>> Dear Sir,
>> please run sp_who2 it will let you know which process is blocking
>> another process.
>> Regards.
>>
>> --
>> ghemant
>>
>> ---
>> ghemant's Profile: http://www.msusenet.com/member.php?userid=2348
>> View this thread: http://www.msusenet.com/t-1870545056
>|||their's a tip to avoid blocking , see the below link
http://www.sql-server-performance.com/blocking.as
Regards
--
gheman
----
ghemant's Profile: http://www.msusenet.com/member.php?userid=234
View this thread: http://www.msusenet.com/t-187054505
Locks/Process ID
I have been trying to track down a database problem in which for some reason
when somebody enters in some data into the database it becauses unavailable
for the application. I looked under Locks/Proccess ID and found that one of
the proccess is 'blocking' can somebody elaborate as to what sort of
processes these are and what blocking means? In addition, about 5 minutes or
so later the application can enter in data again and the web site that uses
the database is available.... any ideas? Thank you.
Gabe,
We can tell nothing about the process, we do not have the information. About
blocking, the process is blocking a resource (table, index, row, etc.) and
for that reason other processes do not have access to the resource.
See "Understanding and Avoiding Blocking" in BOL for more info.
AMB
"Gabe Matteson" wrote:
> I have been trying to track down a database problem in which for some reason
> when somebody enters in some data into the database it becauses unavailable
> for the application. I looked under Locks/Proccess ID and found that one of
> the proccess is 'blocking' can somebody elaborate as to what sort of
> processes these are and what blocking means? In addition, about 5 minutes or
> so later the application can enter in data again and the web site that uses
> the database is available.... any ideas? Thank you.
>
>
|||I have posted screenshots of the issue the resource that is blocking says
its the table, index and row. here are the screenshots, thank you for the
help.
http://66.31.179.80/sql/screenshots.doc
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:920757E2-C5E4-4421-8484-D398215531AF@.microsoft.com...[vbcol=seagreen]
> Gabe,
> We can tell nothing about the process, we do not have the information.
> About
> blocking, the process is blocking a resource (table, index, row, etc.) and
> for that reason other processes do not have access to the resource.
> See "Understanding and Avoiding Blocking" in BOL for more info.
>
> AMB
> "Gabe Matteson" wrote:
|||test
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:uNiskn2cFHA.228@.TK2MSFTNGP12.phx.gbl...
> I have posted screenshots of the issue the resource that is blocking says
> its the table, index and row. here are the screenshots, thank you for the
> help.
> http://66.31.179.80/sql/screenshots.doc
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
> news:920757E2-C5E4-4421-8484-D398215531AF@.microsoft.com...
and[vbcol=seagreen]
one[vbcol=seagreen]
minutes
>
|||Dear Sir,
please run sp_who2 it will let you know which process is blocking
another process.
Regards.
ghemant
ghemant's Profile: http://www.mswebservertalk.com/member.php?userid=2348
View this thread: http://www.mswebservertalk.com/t-1870545056
|||Thank you.
"ghemant" <ghemant.1qtd4p@.no-mx.mswebservertalk.com> wrote in message
news:ghemant.1qtd4p@.no-mx.mswebservertalk.com...
> Dear Sir,
> please run sp_who2 it will let you know which process is blocking
> another process.
> Regards.
>
> --
> ghemant
>
> ghemant's Profile: http://www.mswebservertalk.com/member.php?userid=2348
> View this thread: http://www.mswebservertalk.com/t-1870545056
>
|||Is there anyway to tell why a process is being blocked?
"ghemant" <ghemant.1qtd4p@.no-mx.mswebservertalk.com> wrote in message
news:ghemant.1qtd4p@.no-mx.mswebservertalk.com...
> Dear Sir,
> please run sp_who2 it will let you know which process is blocking
> another process.
> Regards.
>
> --
> ghemant
>
> ghemant's Profile: http://www.mswebservertalk.com/member.php?userid=2348
> View this thread: http://www.mswebservertalk.com/t-1870545056
>
|||A process is blocked when a lock cannot be obtained because another session
already has a lock on the requested resource and the requested lock type is
incompatible. Common causes of blocking include long-running transactions
and queries.
You can use DBCC INPUTBUFFER on the blocking spid to see the currently
executing (or last) SQL statement. Use DBCC UPENTRAN to identify the oldest
open transaction. See http://support.microsoft.com/kb/224453/EN-US/ for
detailed information on troubleshooting blocking problems.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eTg013VdFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Is there anyway to tell why a process is being blocked?
> "ghemant" <ghemant.1qtd4p@.no-mx.mswebservertalk.com> wrote in message
> news:ghemant.1qtd4p@.no-mx.mswebservertalk.com...
>
|||their's a tip to avoid blocking , see the below link.
http://www.sql-server-performance.com/blocking.asp
Regards.
ghemant
ghemant's Profile: http://www.mswebservertalk.com/member.php?userid=2348
View this thread: http://www.mswebservertalk.com/t-1870545056
when somebody enters in some data into the database it becauses unavailable
for the application. I looked under Locks/Proccess ID and found that one of
the proccess is 'blocking' can somebody elaborate as to what sort of
processes these are and what blocking means? In addition, about 5 minutes or
so later the application can enter in data again and the web site that uses
the database is available.... any ideas? Thank you.
Gabe,
We can tell nothing about the process, we do not have the information. About
blocking, the process is blocking a resource (table, index, row, etc.) and
for that reason other processes do not have access to the resource.
See "Understanding and Avoiding Blocking" in BOL for more info.
AMB
"Gabe Matteson" wrote:
> I have been trying to track down a database problem in which for some reason
> when somebody enters in some data into the database it becauses unavailable
> for the application. I looked under Locks/Proccess ID and found that one of
> the proccess is 'blocking' can somebody elaborate as to what sort of
> processes these are and what blocking means? In addition, about 5 minutes or
> so later the application can enter in data again and the web site that uses
> the database is available.... any ideas? Thank you.
>
>
|||I have posted screenshots of the issue the resource that is blocking says
its the table, index and row. here are the screenshots, thank you for the
help.
http://66.31.179.80/sql/screenshots.doc
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:920757E2-C5E4-4421-8484-D398215531AF@.microsoft.com...[vbcol=seagreen]
> Gabe,
> We can tell nothing about the process, we do not have the information.
> About
> blocking, the process is blocking a resource (table, index, row, etc.) and
> for that reason other processes do not have access to the resource.
> See "Understanding and Avoiding Blocking" in BOL for more info.
>
> AMB
> "Gabe Matteson" wrote:
|||test
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:uNiskn2cFHA.228@.TK2MSFTNGP12.phx.gbl...
> I have posted screenshots of the issue the resource that is blocking says
> its the table, index and row. here are the screenshots, thank you for the
> help.
> http://66.31.179.80/sql/screenshots.doc
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
> news:920757E2-C5E4-4421-8484-D398215531AF@.microsoft.com...
and[vbcol=seagreen]
one[vbcol=seagreen]
minutes
>
|||Dear Sir,
please run sp_who2 it will let you know which process is blocking
another process.
Regards.
ghemant
ghemant's Profile: http://www.mswebservertalk.com/member.php?userid=2348
View this thread: http://www.mswebservertalk.com/t-1870545056
|||Thank you.
"ghemant" <ghemant.1qtd4p@.no-mx.mswebservertalk.com> wrote in message
news:ghemant.1qtd4p@.no-mx.mswebservertalk.com...
> Dear Sir,
> please run sp_who2 it will let you know which process is blocking
> another process.
> Regards.
>
> --
> ghemant
>
> ghemant's Profile: http://www.mswebservertalk.com/member.php?userid=2348
> View this thread: http://www.mswebservertalk.com/t-1870545056
>
|||Is there anyway to tell why a process is being blocked?
"ghemant" <ghemant.1qtd4p@.no-mx.mswebservertalk.com> wrote in message
news:ghemant.1qtd4p@.no-mx.mswebservertalk.com...
> Dear Sir,
> please run sp_who2 it will let you know which process is blocking
> another process.
> Regards.
>
> --
> ghemant
>
> ghemant's Profile: http://www.mswebservertalk.com/member.php?userid=2348
> View this thread: http://www.mswebservertalk.com/t-1870545056
>
|||A process is blocked when a lock cannot be obtained because another session
already has a lock on the requested resource and the requested lock type is
incompatible. Common causes of blocking include long-running transactions
and queries.
You can use DBCC INPUTBUFFER on the blocking spid to see the currently
executing (or last) SQL statement. Use DBCC UPENTRAN to identify the oldest
open transaction. See http://support.microsoft.com/kb/224453/EN-US/ for
detailed information on troubleshooting blocking problems.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eTg013VdFHA.2212@.TK2MSFTNGP14.phx.gbl...
> Is there anyway to tell why a process is being blocked?
> "ghemant" <ghemant.1qtd4p@.no-mx.mswebservertalk.com> wrote in message
> news:ghemant.1qtd4p@.no-mx.mswebservertalk.com...
>
|||their's a tip to avoid blocking , see the below link.
http://www.sql-server-performance.com/blocking.asp
Regards.
ghemant
ghemant's Profile: http://www.mswebservertalk.com/member.php?userid=2348
View this thread: http://www.mswebservertalk.com/t-1870545056
Locks, Scope & Performance
I'm getting LOCKS caused by a particular report that uses two sProcs to rend
er a
report.
I'm
as to why the following should be locking my contact calendar a
nd
history tables, they just do a select into a table, shouldn't this be read o
nly
and not cause any locks?
Is there a way to be sure that I exec as read only and do not create a table
lock?
Details....
The 1st sProc is called (nested) from within the second as
create proc my_sProc_Main
declare some vars, @.tmpTbl varchar(36)
set @.tmpTbl = replace('-',newID(),'9')
create a real table.... exec(create table table tblWorkingName'+@. tmpTbl)
Insert myTable exec my_sProc_Sub
(my_sProc_Sub - select complex conditions, union another set of complex
conditions, scan 1m row table return 1200 rows)
create another real results table.... exec(create table tblResultsName'+@.
tmpTbl)
Evaluate data in the first table (containing 1200 rows) and insert into the
second table about 20 resulting rows
return the results of the second table
drop both tables
Total process time between 13 and 56 seconds
I run similar processes, some as complex but not using two sProcs, however t
his
one report brings my server to its knees.
The production server is a Compaq dual 700ghz, 1gb Ram
My dev box is a single 1.4ghz, 1gb Ram and it execs somewhat faster and with
half the server killing effects.
TIA
JeffP....use NO LOCK hints on your SELECT statements
OR change isolation level to READ UNCOMMITTED
Greg Jackson
Portland, OR|||SELECT still issues shared resorce locks and INSERT EXEC runs the entire sub
procedure within the INSERT transaction. This combined with the fact you are
selecting from 1M rows is most likely your culprit.
You could override the locking policy on certian tables and tell it to use
page locks or no locks instead. or change the transaction isolation level to
a more lax level.
You may want to consider a different way of storing the intermediate data,
e.g. temp table which can be filled within the sub procedure and then the
calling procedure would get recompiled if necessary for the next step.
This would address the cause rather than the symptom and probably give a
significant performance boost into the bargain.
Mr Tea
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:%23wEsvHuEFHA.628@.TK2MSFTNGP15.phx.gbl...
> I'm getting LOCKS caused by a particular report that uses two sProcs to
> render a
> report.
> I'm as to why the following should be locking my contact calendar
> and
> history tables, they just do a select into a table, shouldn't this be read
> only
> and not cause any locks?
> Is there a way to be sure that I exec as read only and do not create a
> table
> lock?
> Details....
> The 1st sProc is called (nested) from within the second as
> create proc my_sProc_Main
> declare some vars, @.tmpTbl varchar(36)
> set @.tmpTbl = replace('-',newID(),'9')
> create a real table.... exec(create table table tblWorkingName'+@. tmpTbl)
> Insert myTable exec my_sProc_Sub
> (my_sProc_Sub - select complex conditions, union another set of complex
> conditions, scan 1m row table return 1200 rows)
> create another real results table.... exec(create table tblResultsName'+@.
> tmpTbl)
>
> Evaluate data in the first table (containing 1200 rows) and insert into
> the
> second table about 20 resulting rows
> return the results of the second table
> drop both tables
> Total process time between 13 and 56 seconds
> I run similar processes, some as complex but not using two sProcs, however
> this
> one report brings my server to its knees.
> The production server is a Compaq dual 700ghz, 1gb Ram
> My dev box is a single 1.4ghz, 1gb Ram and it execs somewhat faster and
> with
> half the server killing effects.
> TIA
> JeffP....
>|||Thanks to pdxJaxon, I'm looking into with NOLOCK
if my exec(@.str) from looks like...
from
'+@.db+'.dbo.CONTACT1 contact1
,'+@.db+'.dbo.CONTACT2 contact2
,'+@.db+'.dbo.CAL cal
,'+@.db+'.dbo.conthist ch
,'+@.db+'.dbo.users
where
..adding...
from
'+@.db+'.dbo.CONTACT1 contact1 with NOLOCK
,'+@.db+'.dbo.CONTACT2 contact2 with NOLOCK
,'+@.db+'.dbo.CAL cal with NOLOCK
,'+@.db+'.dbo.conthist ch with NOLOCK
,'+@.db+'.dbo.users with NOLOCK
where
Lee,
I moved away from using the temp db #mytable to creating a table dynamically
named as noted in my pseudo snipit.
I did this because it improved performance from some now in place processes
that
used to bring the server to it's knees.
However I'm still interested and would appreciate a clarification
> e.g. temp table which can be filled within the sub procedure and then the
> calling procedure would get recompiled if necessary for the next step.
1. Is there any difference between using a table dynamically named within my
outer sProc and #myTemp?
2. Would the process you are suggesting look like a proc that calls a sub pr
oc
and creates w/recompile an inner sProc?
create proc my_sProc_Main_Outer
exec mysProc_sub inserting rows to a (temp) table
then...
create proc my_sProc_Main_Inner
with recomplike
as
....
return
drop table(s)
return
Exec'ing the sProc for the report would look something like....
exec my_sProc_Main_Outer @.db ,@.w_str ,@.action
TIA
JeffP...
"Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
news:A5aQd.547$Se3.306@.newsfe5-win.ntli.net...
> SELECT still issues shared resorce locks and INSERT EXEC runs the entire s
ub
> procedure within the INSERT transaction. This combined with the fact you a
re
> selecting from 1M rows is most likely your culprit.
> You could override the locking policy on certian tables and tell it to use
> page locks or no locks instead. or change the transaction isolation level
to
> a more lax level.
> You may want to consider a different way of storing the intermediate data,
> e.g. temp table which can be filled within the sub procedure and then the
> calling procedure would get recompiled if necessary for the next step.
> This would address the cause rather than the symptom and probably give a
> significant performance boost into the bargain.
> Mr Tea
> "JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
> news:%23wEsvHuEFHA.628@.TK2MSFTNGP15.phx.gbl...
>|||Understand that NOLOCK can give you invalid results, since it can see dirty
data. If having consistent results are critical, this is not a good idea.
If you can live with possible spurrious results, based on rows being picked
up that turn out to have been invalid, then it is fine. I don't know about
your architecture, but chances are you will have very litte of this unless
you have quite high throughput, but it is a concern.
A million rows is quite a few, but it is not an incredible amount. Consider
optimizing your query, and you might only have blocks that last a second or
two if any. A query that brings the server to its knees is often a sign
that you have table scans that are slowly walking through your table. It
might also be taking a table lock rather than rowlocks based on resource
utilization. This query could be the problem, but it could also be your
update queries or others. Can you tell us which query is blocking which?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:ezFTA9uEFHA.3200@.TK2MSFTNGP10.phx.gbl...
> Thanks to pdxJaxon, I'm looking into with NOLOCK
> if my exec(@.str) from looks like...
> from
> '+@.db+'.dbo.CONTACT1 contact1
> ,'+@.db+'.dbo.CONTACT2 contact2
> ,'+@.db+'.dbo.CAL cal
> ,'+@.db+'.dbo.conthist ch
> ,'+@.db+'.dbo.users
> where
> ..adding...
> from
> '+@.db+'.dbo.CONTACT1 contact1 with NOLOCK
> ,'+@.db+'.dbo.CONTACT2 contact2 with NOLOCK
> ,'+@.db+'.dbo.CAL cal with NOLOCK
> ,'+@.db+'.dbo.conthist ch with NOLOCK
> ,'+@.db+'.dbo.users with NOLOCK
> where
> Lee,
> I moved away from using the temp db #mytable to creating a table
> dynamically
> named as noted in my pseudo snipit.
> I did this because it improved performance from some now in place
> processes that
> used to bring the server to it's knees.
> However I'm still interested and would appreciate a clarification
>
> 1. Is there any difference between using a table dynamically named within
> my
> outer sProc and #myTemp?
> 2. Would the process you are suggesting look like a proc that calls a sub
> proc
> and creates w/recompile an inner sProc?
> create proc my_sProc_Main_Outer
> exec mysProc_sub inserting rows to a (temp) table
> then...
> create proc my_sProc_Main_Inner
> with recomplike
> as
> ....
> return
> drop table(s)
> return
> Exec'ing the sProc for the report would look something like....
> exec my_sProc_Main_Outer @.db ,@.w_str ,@.action
> TIA
> JeffP...
>
> "Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
> news:A5aQd.547$Se3.306@.newsfe5-win.ntli.net...
>|||When the Crystal (pronounced cryst-hel) report runs we see that all the tabl
es
are blocking other requests.
We have less than 25 users in the system and about another 50+/- running
reports, but another 500 who syncronize their data once a day this occurs ne
arly
all day long, someone is sync'ing with the server at most any time of the da
y.
My sProcs provide data for reports, if they miss an item on a report that th
ey
expect to see their first recourse is to think that the data has not sync'd
with
the server and they will re-sync, running the query after a moment should no
w
include any data that was being updated. Most users only have their own dat
a,
however it's not uncommon for our other daily updating and sales-process-cap
ture
to lock tables as well.
It was more pronounced that when one of the regional VPs would try to run th
e
report for the entire region we'd see that there were blocks and processes w
ould
hang as well as my dynamically named webReprt3984023984023840238402 tables w
ould
still be there due to the proc not completing or the user canceling.
The report takes a little over 2 min to render for an entire market center a
nd
little less than 20 seconds for an individual salesperson.
Another issue is that each time I optimize the sProcs' they add another colu
mn,
some are complex and are dependent on other table info to validate the resul
ts
data, this cross table, cross db stuff is just way too intense for such a lo
w
power system and I hear that we are getting a new production server in the n
ext
month or so.
I also see that workning on a low power system has a side benefit to encoura
ge
us to squeze as much as we can. Another issue is bandwidth which in a word
sucks.
Any I was able to get my outer sProc to run in as little as 4 seconds for a
sales person down from 1min44seconds to 19 seconds and now the 4 seconds.
I know that there must be substantially more optimizations available, howeve
r we
don't have a budget, so we are working for nearly free at this point just sa
ving
face that our stuff works at all. The client knows that they have a margina
l
network, a low power server and very complex reports.
This report for example is a whiteboard, filling in the data in each column
not
respectful of rows except that a particular column in that row is filled, it
loads data just as a human would fill in a sales whiteboard in the office fo
r
each salesperson, with first time visits scheduled, along with columns or %
probablity of closing columns for each pending sale, or targeted prospect.
Thanks to all.
JeffP...
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:eRIEKGxEFHA.3928@.TK2MSFTNGP15.phx.gbl...
> Understand that NOLOCK can give you invalid results, since it can see dirt
y
> data. If having consistent results are critical, this is not a good idea.
> If you can live with possible spurrious results, based on rows being picke
d
> up that turn out to have been invalid, then it is fine. I don't know abou
t
> your architecture, but chances are you will have very litte of this unless
> you have quite high throughput, but it is a concern.
> A million rows is quite a few, but it is not an incredible amount. Consid
er
> optimizing your query, and you might only have blocks that last a second o
r
> two if any. A query that brings the server to its knees is often a sign
> that you have table scans that are slowly walking through your table. It
> might also be taking a table lock rather than rowlocks based on resource
> utilization. This query could be the problem, but it could also be your
> update queries or others. Can you tell us which query is blocking which?
>
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
> news:ezFTA9uEFHA.3200@.TK2MSFTNGP10.phx.gbl...
>|||@.db? Now you're getting serious :)
I would try and acheive the desired result without too much dynamic SQL as
its pretty hard to tune. one way would be to put my_sProc_Main_Outer within
the master db and then call it in the context of a specific DB using
SET @.sp=@.db+'..sp_my_sProc_Main_Outer'
EXEC @.sp @.w_str ,@.action
if you dont like that then you will have to ditch the sp_ prefixes and put
the procs on each database
then your outer sproc would do its stuff (you may be able to simplify this
as well):
CREATE TABLE #tmp
EXEC sp_my_sProc_Main_Inner
Do something with data in #tmp.
your sproc inner would just add data to the temporary table:
insert #tmp select col001...coln FROM dbo. ...
when you return to the inner table, the db will check the stats on the
temporary table data and recompile it for better execution if necesary (if
you know that there is no need for dynamic recompilation then you can look
at some other way of storing the data, e.g. table variable and inner UDF)
getting rid of those dynamic clauses, if possible, allowing you to view and
check the query plan.
Mr Tea
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:ezFTA9uEFHA.3200@.TK2MSFTNGP10.phx.gbl...
> Thanks to pdxJaxon, I'm looking into with NOLOCK
> if my exec(@.str) from looks like...
> from
> '+@.db+'.dbo.CONTACT1 contact1
> ,'+@.db+'.dbo.CONTACT2 contact2
> ,'+@.db+'.dbo.CAL cal
> ,'+@.db+'.dbo.conthist ch
> ,'+@.db+'.dbo.users
> where
> ..adding...
> from
> '+@.db+'.dbo.CONTACT1 contact1 with NOLOCK
> ,'+@.db+'.dbo.CONTACT2 contact2 with NOLOCK
> ,'+@.db+'.dbo.CAL cal with NOLOCK
> ,'+@.db+'.dbo.conthist ch with NOLOCK
> ,'+@.db+'.dbo.users with NOLOCK
> where
> Lee,
> I moved away from using the temp db #mytable to creating a table
> dynamically
> named as noted in my pseudo snipit.
> I did this because it improved performance from some now in place
> processes that
> used to bring the server to it's knees.
> However I'm still interested and would appreciate a clarification
>
> 1. Is there any difference between using a table dynamically named within
> my
> outer sProc and #myTemp?
> 2. Would the process you are suggesting look like a proc that calls a sub
> proc
> and creates w/recompile an inner sProc?
> create proc my_sProc_Main_Outer
> exec mysProc_sub inserting rows to a (temp) table
> then...
> create proc my_sProc_Main_Inner
> with recomplike
> as
> ....
> return
> drop table(s)
> return
> Exec'ing the sProc for the report would look something like....
> exec my_sProc_Main_Outer @.db ,@.w_str ,@.action
> TIA
> JeffP...
>
> "Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
> news:A5aQd.547$Se3.306@.newsfe5-win.ntli.net...
>|||I'm sorry I was so long...
I moved away from #tmp tables as it alone with some earlier report sProcs wa
s
dogging the server and using the dynamic real tables helped.
As far as the location of the sProcs as often as possible we've placed all t
he
sProcs in the Reports db. There are now 4 regional plus another 2 reported
db's
and to keep all the versions updated has been another challenge, so keeping
them
in the reports db has been a benefit to Q&A.
The way I optimize is...
1. Add elements like the new "with (nolock) and see if it doesn't exec faste
r,
it does, in fact my partner was slightly amazed that I was able to run the
sProc/Report for an entire region and market center without locking. We'll
see
in the next few days if it's truely helped.
2. During dev, I run my exec's as select @.str's take the results and run the
m in
the QA with execPlan on.
There isn't often much I can do, we are working in part with an out'a the bo
x
app and adding indexes without pain can be difficult.
I've no real experience with @.table vars and I thought that their scope was
only
within the calling sProc, could the Inner sProc return a @.table ?
Also, with memory a premium, would the @.table var be an improvement over
creating a real table dynamically named? (FYI: I dynamicaly name my tables s
o
that each user who is running a report only see's their stuff)
UDF, I have a few UDF's, a fnProperCase and fnParse which parses any string
by
any separator, anyway I was recently reading about using a UDF to return a t
able
as a parameterized UDF. Anyone have a practical link?
TIA
JeffP....
"Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
news:5LiQd.128$dv2.54@.newsfe5-win.ntli.net...
> @.db? Now you're getting serious :)
> I would try and acheive the desired result without too much dynamic SQL as
> its pretty hard to tune. one way would be to put my_sProc_Main_Outer withi
n
> the master db and then call it in the context of a specific DB using
> SET @.sp=@.db+'..sp_my_sProc_Main_Outer'
> EXEC @.sp @.w_str ,@.action
> if you dont like that then you will have to ditch the sp_ prefixes and put
> the procs on each database
> then your outer sproc would do its stuff (you may be able to simplify this
> as well):
> CREATE TABLE #tmp
> EXEC sp_my_sProc_Main_Inner
> Do something with data in #tmp.
>
> your sproc inner would just add data to the temporary table:
> insert #tmp select col001...coln FROM dbo. ...
> when you return to the inner table, the db will check the stats on the
> temporary table data and recompile it for better execution if necesary (if
> you know that there is no need for dynamic recompilation then you can look
> at some other way of storing the data, e.g. table variable and inner UDF)
> getting rid of those dynamic clauses, if possible, allowing you to view an
d
> check the query plan.
> Mr Tea
> "JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
> news:ezFTA9uEFHA.3200@.TK2MSFTNGP10.phx.gbl...
>
er a
report.
I'm
as to why the following should be locking my contact calendar and
history tables, they just do a select into a table, shouldn't this be read o
nly
and not cause any locks?
Is there a way to be sure that I exec as read only and do not create a table
lock?
Details....
The 1st sProc is called (nested) from within the second as
create proc my_sProc_Main
declare some vars, @.tmpTbl varchar(36)
set @.tmpTbl = replace('-',newID(),'9')
create a real table.... exec(create table table tblWorkingName'+@. tmpTbl)
Insert myTable exec my_sProc_Sub
(my_sProc_Sub - select complex conditions, union another set of complex
conditions, scan 1m row table return 1200 rows)
create another real results table.... exec(create table tblResultsName'+@.
tmpTbl)
Evaluate data in the first table (containing 1200 rows) and insert into the
second table about 20 resulting rows
return the results of the second table
drop both tables
Total process time between 13 and 56 seconds
I run similar processes, some as complex but not using two sProcs, however t
his
one report brings my server to its knees.
The production server is a Compaq dual 700ghz, 1gb Ram
My dev box is a single 1.4ghz, 1gb Ram and it execs somewhat faster and with
half the server killing effects.
TIA
JeffP....use NO LOCK hints on your SELECT statements
OR change isolation level to READ UNCOMMITTED
Greg Jackson
Portland, OR|||SELECT still issues shared resorce locks and INSERT EXEC runs the entire sub
procedure within the INSERT transaction. This combined with the fact you are
selecting from 1M rows is most likely your culprit.
You could override the locking policy on certian tables and tell it to use
page locks or no locks instead. or change the transaction isolation level to
a more lax level.
You may want to consider a different way of storing the intermediate data,
e.g. temp table which can be filled within the sub procedure and then the
calling procedure would get recompiled if necessary for the next step.
This would address the cause rather than the symptom and probably give a
significant performance boost into the bargain.
Mr Tea
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:%23wEsvHuEFHA.628@.TK2MSFTNGP15.phx.gbl...
> I'm getting LOCKS caused by a particular report that uses two sProcs to
> render a
> report.
> I'm
as to why the following should be locking my contact calendar> and
> history tables, they just do a select into a table, shouldn't this be read
> only
> and not cause any locks?
> Is there a way to be sure that I exec as read only and do not create a
> table
> lock?
> Details....
> The 1st sProc is called (nested) from within the second as
> create proc my_sProc_Main
> declare some vars, @.tmpTbl varchar(36)
> set @.tmpTbl = replace('-',newID(),'9')
> create a real table.... exec(create table table tblWorkingName'+@. tmpTbl)
> Insert myTable exec my_sProc_Sub
> (my_sProc_Sub - select complex conditions, union another set of complex
> conditions, scan 1m row table return 1200 rows)
> create another real results table.... exec(create table tblResultsName'+@.
> tmpTbl)
>
> Evaluate data in the first table (containing 1200 rows) and insert into
> the
> second table about 20 resulting rows
> return the results of the second table
> drop both tables
> Total process time between 13 and 56 seconds
> I run similar processes, some as complex but not using two sProcs, however
> this
> one report brings my server to its knees.
> The production server is a Compaq dual 700ghz, 1gb Ram
> My dev box is a single 1.4ghz, 1gb Ram and it execs somewhat faster and
> with
> half the server killing effects.
> TIA
> JeffP....
>|||Thanks to pdxJaxon, I'm looking into with NOLOCK
if my exec(@.str) from looks like...
from
'+@.db+'.dbo.CONTACT1 contact1
,'+@.db+'.dbo.CONTACT2 contact2
,'+@.db+'.dbo.CAL cal
,'+@.db+'.dbo.conthist ch
,'+@.db+'.dbo.users
where
..adding...
from
'+@.db+'.dbo.CONTACT1 contact1 with NOLOCK
,'+@.db+'.dbo.CONTACT2 contact2 with NOLOCK
,'+@.db+'.dbo.CAL cal with NOLOCK
,'+@.db+'.dbo.conthist ch with NOLOCK
,'+@.db+'.dbo.users with NOLOCK
where
Lee,
I moved away from using the temp db #mytable to creating a table dynamically
named as noted in my pseudo snipit.
I did this because it improved performance from some now in place processes
that
used to bring the server to it's knees.
However I'm still interested and would appreciate a clarification
> e.g. temp table which can be filled within the sub procedure and then the
> calling procedure would get recompiled if necessary for the next step.
1. Is there any difference between using a table dynamically named within my
outer sProc and #myTemp?
2. Would the process you are suggesting look like a proc that calls a sub pr
oc
and creates w/recompile an inner sProc?
create proc my_sProc_Main_Outer
exec mysProc_sub inserting rows to a (temp) table
then...
create proc my_sProc_Main_Inner
with recomplike
as
....
return
drop table(s)
return
Exec'ing the sProc for the report would look something like....
exec my_sProc_Main_Outer @.db ,@.w_str ,@.action
TIA
JeffP...
"Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
news:A5aQd.547$Se3.306@.newsfe5-win.ntli.net...
> SELECT still issues shared resorce locks and INSERT EXEC runs the entire s
ub
> procedure within the INSERT transaction. This combined with the fact you a
re
> selecting from 1M rows is most likely your culprit.
> You could override the locking policy on certian tables and tell it to use
> page locks or no locks instead. or change the transaction isolation level
to
> a more lax level.
> You may want to consider a different way of storing the intermediate data,
> e.g. temp table which can be filled within the sub procedure and then the
> calling procedure would get recompiled if necessary for the next step.
> This would address the cause rather than the symptom and probably give a
> significant performance boost into the bargain.
> Mr Tea
> "JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
> news:%23wEsvHuEFHA.628@.TK2MSFTNGP15.phx.gbl...
>|||Understand that NOLOCK can give you invalid results, since it can see dirty
data. If having consistent results are critical, this is not a good idea.
If you can live with possible spurrious results, based on rows being picked
up that turn out to have been invalid, then it is fine. I don't know about
your architecture, but chances are you will have very litte of this unless
you have quite high throughput, but it is a concern.
A million rows is quite a few, but it is not an incredible amount. Consider
optimizing your query, and you might only have blocks that last a second or
two if any. A query that brings the server to its knees is often a sign
that you have table scans that are slowly walking through your table. It
might also be taking a table lock rather than rowlocks based on resource
utilization. This query could be the problem, but it could also be your
update queries or others. Can you tell us which query is blocking which?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:ezFTA9uEFHA.3200@.TK2MSFTNGP10.phx.gbl...
> Thanks to pdxJaxon, I'm looking into with NOLOCK
> if my exec(@.str) from looks like...
> from
> '+@.db+'.dbo.CONTACT1 contact1
> ,'+@.db+'.dbo.CONTACT2 contact2
> ,'+@.db+'.dbo.CAL cal
> ,'+@.db+'.dbo.conthist ch
> ,'+@.db+'.dbo.users
> where
> ..adding...
> from
> '+@.db+'.dbo.CONTACT1 contact1 with NOLOCK
> ,'+@.db+'.dbo.CONTACT2 contact2 with NOLOCK
> ,'+@.db+'.dbo.CAL cal with NOLOCK
> ,'+@.db+'.dbo.conthist ch with NOLOCK
> ,'+@.db+'.dbo.users with NOLOCK
> where
> Lee,
> I moved away from using the temp db #mytable to creating a table
> dynamically
> named as noted in my pseudo snipit.
> I did this because it improved performance from some now in place
> processes that
> used to bring the server to it's knees.
> However I'm still interested and would appreciate a clarification
>
> 1. Is there any difference between using a table dynamically named within
> my
> outer sProc and #myTemp?
> 2. Would the process you are suggesting look like a proc that calls a sub
> proc
> and creates w/recompile an inner sProc?
> create proc my_sProc_Main_Outer
> exec mysProc_sub inserting rows to a (temp) table
> then...
> create proc my_sProc_Main_Inner
> with recomplike
> as
> ....
> return
> drop table(s)
> return
> Exec'ing the sProc for the report would look something like....
> exec my_sProc_Main_Outer @.db ,@.w_str ,@.action
> TIA
> JeffP...
>
> "Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
> news:A5aQd.547$Se3.306@.newsfe5-win.ntli.net...
>|||When the Crystal (pronounced cryst-hel) report runs we see that all the tabl
es
are blocking other requests.
We have less than 25 users in the system and about another 50+/- running
reports, but another 500 who syncronize their data once a day this occurs ne
arly
all day long, someone is sync'ing with the server at most any time of the da
y.
My sProcs provide data for reports, if they miss an item on a report that th
ey
expect to see their first recourse is to think that the data has not sync'd
with
the server and they will re-sync, running the query after a moment should no
w
include any data that was being updated. Most users only have their own dat
a,
however it's not uncommon for our other daily updating and sales-process-cap
ture
to lock tables as well.
It was more pronounced that when one of the regional VPs would try to run th
e
report for the entire region we'd see that there were blocks and processes w
ould
hang as well as my dynamically named webReprt3984023984023840238402 tables w
ould
still be there due to the proc not completing or the user canceling.
The report takes a little over 2 min to render for an entire market center a
nd
little less than 20 seconds for an individual salesperson.
Another issue is that each time I optimize the sProcs' they add another colu
mn,
some are complex and are dependent on other table info to validate the resul
ts
data, this cross table, cross db stuff is just way too intense for such a lo
w
power system and I hear that we are getting a new production server in the n
ext
month or so.
I also see that workning on a low power system has a side benefit to encoura
ge
us to squeze as much as we can. Another issue is bandwidth which in a word
sucks.
Any I was able to get my outer sProc to run in as little as 4 seconds for a
sales person down from 1min44seconds to 19 seconds and now the 4 seconds.
I know that there must be substantially more optimizations available, howeve
r we
don't have a budget, so we are working for nearly free at this point just sa
ving
face that our stuff works at all. The client knows that they have a margina
l
network, a low power server and very complex reports.
This report for example is a whiteboard, filling in the data in each column
not
respectful of rows except that a particular column in that row is filled, it
loads data just as a human would fill in a sales whiteboard in the office fo
r
each salesperson, with first time visits scheduled, along with columns or %
probablity of closing columns for each pending sale, or targeted prospect.
Thanks to all.
JeffP...
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:eRIEKGxEFHA.3928@.TK2MSFTNGP15.phx.gbl...
> Understand that NOLOCK can give you invalid results, since it can see dirt
y
> data. If having consistent results are critical, this is not a good idea.
> If you can live with possible spurrious results, based on rows being picke
d
> up that turn out to have been invalid, then it is fine. I don't know abou
t
> your architecture, but chances are you will have very litte of this unless
> you have quite high throughput, but it is a concern.
> A million rows is quite a few, but it is not an incredible amount. Consid
er
> optimizing your query, and you might only have blocks that last a second o
r
> two if any. A query that brings the server to its knees is often a sign
> that you have table scans that are slowly walking through your table. It
> might also be taking a table lock rather than rowlocks based on resource
> utilization. This query could be the problem, but it could also be your
> update queries or others. Can you tell us which query is blocking which?
>
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
> news:ezFTA9uEFHA.3200@.TK2MSFTNGP10.phx.gbl...
>|||@.db? Now you're getting serious :)
I would try and acheive the desired result without too much dynamic SQL as
its pretty hard to tune. one way would be to put my_sProc_Main_Outer within
the master db and then call it in the context of a specific DB using
SET @.sp=@.db+'..sp_my_sProc_Main_Outer'
EXEC @.sp @.w_str ,@.action
if you dont like that then you will have to ditch the sp_ prefixes and put
the procs on each database
then your outer sproc would do its stuff (you may be able to simplify this
as well):
CREATE TABLE #tmp
EXEC sp_my_sProc_Main_Inner
Do something with data in #tmp.
your sproc inner would just add data to the temporary table:
insert #tmp select col001...coln FROM dbo. ...
when you return to the inner table, the db will check the stats on the
temporary table data and recompile it for better execution if necesary (if
you know that there is no need for dynamic recompilation then you can look
at some other way of storing the data, e.g. table variable and inner UDF)
getting rid of those dynamic clauses, if possible, allowing you to view and
check the query plan.
Mr Tea
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:ezFTA9uEFHA.3200@.TK2MSFTNGP10.phx.gbl...
> Thanks to pdxJaxon, I'm looking into with NOLOCK
> if my exec(@.str) from looks like...
> from
> '+@.db+'.dbo.CONTACT1 contact1
> ,'+@.db+'.dbo.CONTACT2 contact2
> ,'+@.db+'.dbo.CAL cal
> ,'+@.db+'.dbo.conthist ch
> ,'+@.db+'.dbo.users
> where
> ..adding...
> from
> '+@.db+'.dbo.CONTACT1 contact1 with NOLOCK
> ,'+@.db+'.dbo.CONTACT2 contact2 with NOLOCK
> ,'+@.db+'.dbo.CAL cal with NOLOCK
> ,'+@.db+'.dbo.conthist ch with NOLOCK
> ,'+@.db+'.dbo.users with NOLOCK
> where
> Lee,
> I moved away from using the temp db #mytable to creating a table
> dynamically
> named as noted in my pseudo snipit.
> I did this because it improved performance from some now in place
> processes that
> used to bring the server to it's knees.
> However I'm still interested and would appreciate a clarification
>
> 1. Is there any difference between using a table dynamically named within
> my
> outer sProc and #myTemp?
> 2. Would the process you are suggesting look like a proc that calls a sub
> proc
> and creates w/recompile an inner sProc?
> create proc my_sProc_Main_Outer
> exec mysProc_sub inserting rows to a (temp) table
> then...
> create proc my_sProc_Main_Inner
> with recomplike
> as
> ....
> return
> drop table(s)
> return
> Exec'ing the sProc for the report would look something like....
> exec my_sProc_Main_Outer @.db ,@.w_str ,@.action
> TIA
> JeffP...
>
> "Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
> news:A5aQd.547$Se3.306@.newsfe5-win.ntli.net...
>|||I'm sorry I was so long...
I moved away from #tmp tables as it alone with some earlier report sProcs wa
s
dogging the server and using the dynamic real tables helped.
As far as the location of the sProcs as often as possible we've placed all t
he
sProcs in the Reports db. There are now 4 regional plus another 2 reported
db's
and to keep all the versions updated has been another challenge, so keeping
them
in the reports db has been a benefit to Q&A.
The way I optimize is...
1. Add elements like the new "with (nolock) and see if it doesn't exec faste
r,
it does, in fact my partner was slightly amazed that I was able to run the
sProc/Report for an entire region and market center without locking. We'll
see
in the next few days if it's truely helped.
2. During dev, I run my exec's as select @.str's take the results and run the
m in
the QA with execPlan on.
There isn't often much I can do, we are working in part with an out'a the bo
x
app and adding indexes without pain can be difficult.
I've no real experience with @.table vars and I thought that their scope was
only
within the calling sProc, could the Inner sProc return a @.table ?
Also, with memory a premium, would the @.table var be an improvement over
creating a real table dynamically named? (FYI: I dynamicaly name my tables s
o
that each user who is running a report only see's their stuff)
UDF, I have a few UDF's, a fnProperCase and fnParse which parses any string
by
any separator, anyway I was recently reading about using a UDF to return a t
able
as a parameterized UDF. Anyone have a practical link?
TIA
JeffP....
"Lee Tudor" <mr_tea@.ntlworld.com> wrote in message
news:5LiQd.128$dv2.54@.newsfe5-win.ntli.net...
> @.db? Now you're getting serious :)
> I would try and acheive the desired result without too much dynamic SQL as
> its pretty hard to tune. one way would be to put my_sProc_Main_Outer withi
n
> the master db and then call it in the context of a specific DB using
> SET @.sp=@.db+'..sp_my_sProc_Main_Outer'
> EXEC @.sp @.w_str ,@.action
> if you dont like that then you will have to ditch the sp_ prefixes and put
> the procs on each database
> then your outer sproc would do its stuff (you may be able to simplify this
> as well):
> CREATE TABLE #tmp
> EXEC sp_my_sProc_Main_Inner
> Do something with data in #tmp.
>
> your sproc inner would just add data to the temporary table:
> insert #tmp select col001...coln FROM dbo. ...
> when you return to the inner table, the db will check the stats on the
> temporary table data and recompile it for better execution if necesary (if
> you know that there is no need for dynamic recompilation then you can look
> at some other way of storing the data, e.g. table variable and inner UDF)
> getting rid of those dynamic clauses, if possible, allowing you to view an
d
> check the query plan.
> Mr Tea
> "JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
> news:ezFTA9uEFHA.3200@.TK2MSFTNGP10.phx.gbl...
>
locks with select
Hey I have a terrible problem !!
I have a query, and It doesn't matter if it has finished, the table is still locked. Is there any clause to unlock the share mod lock (force) when the query has finished or any way to ensure that table has no locks to continue with another query or transaction ?
Could you give me any sentence ?
Thanks you !!Also, see if there are any open transactions in your database, or in tempdb if your query references temporary objects:
DBCC OPENTRAN('<your_database>')|||Is there any way to turn the data base in optimistic mode, or to execute that in optimistic mode ?|||Yes (http://www.dbforums.com/showpost.php?p=3690898&postcount=13).
-PatP|||Well, but I have a concurrency problem: Update sentence has been thrown while a select is being executed (of the same table), so the select is locking the update, and the query takes a lot of time, what I want to do, is to execute the query and not to wait a long time to be able to execute the update sentence, guaranteeing that I won't have many transactions stuck, and guaranteeing the resulset (of the query) won't have uncommited data.
Any Suggestion ?
I have a query, and It doesn't matter if it has finished, the table is still locked. Is there any clause to unlock the share mod lock (force) when the query has finished or any way to ensure that table has no locks to continue with another query or transaction ?
Could you give me any sentence ?
Thanks you !!Also, see if there are any open transactions in your database, or in tempdb if your query references temporary objects:
DBCC OPENTRAN('<your_database>')|||Is there any way to turn the data base in optimistic mode, or to execute that in optimistic mode ?|||Yes (http://www.dbforums.com/showpost.php?p=3690898&postcount=13).
-PatP|||Well, but I have a concurrency problem: Update sentence has been thrown while a select is being executed (of the same table), so the select is locking the update, and the query takes a lot of time, what I want to do, is to execute the query and not to wait a long time to be able to execute the update sentence, guaranteeing that I won't have many transactions stuck, and guaranteeing the resulset (of the query) won't have uncommited data.
Any Suggestion ?
Locks the tables while running Report...Very Very Urgent....
Hi all ,
When i Run the report in reporting services, it locks the tables.
so is there any option to Unlock the tables. I m using just select query to run the report but when i run the report it locks the tables.
I used with(nolock) option in select query but it didnt work...still showing me lock on the tables.
Pls help...its urgent
Thanking You,
Rupali Rane.
If this is SQL server you could try changing the query to a stored procedure and putting "set transaction isolation level read uncommitted" in the beginning of the procedure.
|||"set transaction isolation level read uncommitted" is the equivelant of specifying (NOLOCK) on all tables.
rupamp- Where are you seeing the locks, what type of locks, and do you have a (NOLOCK) on all tables, even in subqueries/derived tables?
As stated above, if you specify "set transaction isolation level read uncommitted" at the beginning of the query, you can skip the (NOLOCK) in the query.
BobP
locks preventing backup of database
The database is configured for single publisher, many subscribers,
merge replication. The maintenance plan started to fail a couple of
months ago and the database would not get backed up. After clearing
all the locks, I am able to backup the database manually. The locks
return again and I'm not able to backup the database with the
maintenance plan. How can I get around the lock issue or solve it so
that I can backup the database again?
merge replication. The maintenance plan started to fail a couple of
months ago and the database would not get backed up. After clearing
all the locks, I am able to backup the database manually. The locks
return again and I'm not able to backup the database with the
maintenance plan. How can I get around the lock issue or solve it so
that I can backup the database again?
Thanks,
Chris"cloverme" <ads4sms@.yahoo.com> wrote in message
news:fe5c75a8.0401071659.5b96ba2c@.posting.google.c om...
> The database is configured for single publisher, many subscribers,
> merge replication. The maintenance plan started to fail a couple of
> months ago and the database would not get backed up. After clearing
> all the locks, I am able to backup the database manually. The locks
> return again and I'm not able to backup the database with the
> maintenance plan. How can I get around the lock issue or solve it so
> that I can backup the database again?
How are you trying to back it up? I've never seen locks prevent a db
backup.
> Thanks,
> Chris
Locks per File
For some time, I have been trying to determine why we are getting
timeouts under certain conditions using an A2003 front end, SQL2000
backend. I have recently seen several quirky conditions related to a
too-low setting of maxlocksperfile.
Can that setting perhaps cause a timeout if the process is trying to
acquire too many locks?
TIAMaxlocksperfile is a Jet setting, not SQL Server setting. It
really depends on how you have the Access piece implemented.
If it's an ADP, there is no jet so no. Other than that, it
depends.
If this is just SQL Server and no jet involved, you'd
probably want to start by checking for locking, blocking
issues. You can use the system stored procedures sp_lock,
sp_who2 and query master..sysprocesses.
You may also want to take a look at the following article:
INF: How to Monitor SQL Server 7.0 Blocking
http://support.microsoft.com/?id=251004
-Sue
On Fri, 04 Nov 2005 09:54:12 -0500, elf
<eric@.northstarcc.com> wrote:
>For some time, I have been trying to determine why we are getting
>timeouts under certain conditions using an A2003 front end, SQL2000
>backend. I have recently seen several quirky conditions related to a
>too-low setting of maxlocksperfile.
>Can that setting perhaps cause a timeout if the process is trying to
>acquire too many locks?
>TIA|||Thanks, It is DAO.
Sue Hoegemeier wrote:
> Maxlocksperfile is a Jet setting, not SQL Server setting. It
> really depends on how you have the Access piece implemented.
> If it's an ADP, there is no jet so no. Other than that, it
> depends.
> If this is just SQL Server and no jet involved, you'd
> probably want to start by checking for locking, blocking
> issues. You can use the system stored procedures sp_lock,
> sp_who2 and query master..sysprocesses.
> You may also want to take a look at the following article:
> INF: How to Monitor SQL Server 7.0 Blocking
> http://support.microsoft.com/?id=251004
> -Sue
> On Fri, 04 Nov 2005 09:54:12 -0500, elf
> <eric@.northstarcc.com> wrote:
>
>|||So with it being Jet, don't know - that's an Access specific
thing. You'd probably want to ask that in one of the Access
newsgroups.
-Sue
On Tue, 08 Nov 2005 22:55:42 -0500, elf
<eric@.northstarcc.com> wrote:
[vbcol=seagreen]
>Thanks, It is DAO.
>
>Sue Hoegemeier wrote:
timeouts under certain conditions using an A2003 front end, SQL2000
backend. I have recently seen several quirky conditions related to a
too-low setting of maxlocksperfile.
Can that setting perhaps cause a timeout if the process is trying to
acquire too many locks?
TIAMaxlocksperfile is a Jet setting, not SQL Server setting. It
really depends on how you have the Access piece implemented.
If it's an ADP, there is no jet so no. Other than that, it
depends.
If this is just SQL Server and no jet involved, you'd
probably want to start by checking for locking, blocking
issues. You can use the system stored procedures sp_lock,
sp_who2 and query master..sysprocesses.
You may also want to take a look at the following article:
INF: How to Monitor SQL Server 7.0 Blocking
http://support.microsoft.com/?id=251004
-Sue
On Fri, 04 Nov 2005 09:54:12 -0500, elf
<eric@.northstarcc.com> wrote:
>For some time, I have been trying to determine why we are getting
>timeouts under certain conditions using an A2003 front end, SQL2000
>backend. I have recently seen several quirky conditions related to a
>too-low setting of maxlocksperfile.
>Can that setting perhaps cause a timeout if the process is trying to
>acquire too many locks?
>TIA|||Thanks, It is DAO.
Sue Hoegemeier wrote:
> Maxlocksperfile is a Jet setting, not SQL Server setting. It
> really depends on how you have the Access piece implemented.
> If it's an ADP, there is no jet so no. Other than that, it
> depends.
> If this is just SQL Server and no jet involved, you'd
> probably want to start by checking for locking, blocking
> issues. You can use the system stored procedures sp_lock,
> sp_who2 and query master..sysprocesses.
> You may also want to take a look at the following article:
> INF: How to Monitor SQL Server 7.0 Blocking
> http://support.microsoft.com/?id=251004
> -Sue
> On Fri, 04 Nov 2005 09:54:12 -0500, elf
> <eric@.northstarcc.com> wrote:
>
>|||So with it being Jet, don't know - that's an Access specific
thing. You'd probably want to ask that in one of the Access
newsgroups.
-Sue
On Tue, 08 Nov 2005 22:55:42 -0500, elf
<eric@.northstarcc.com> wrote:
[vbcol=seagreen]
>Thanks, It is DAO.
>
>Sue Hoegemeier wrote:
Locks per File
For some time, I have been trying to determine why we are getting
timeouts under certain conditions using an A2003 front end, SQL2000
backend. I have recently seen several quirky conditions related to a
too-low setting of maxlocksperfile.
Can that setting perhaps cause a timeout if the process is trying to
acquire too many locks?
TIA
Maxlocksperfile is a Jet setting, not SQL Server setting. It
really depends on how you have the Access piece implemented.
If it's an ADP, there is no jet so no. Other than that, it
depends.
If this is just SQL Server and no jet involved, you'd
probably want to start by checking for locking, blocking
issues. You can use the system stored procedures sp_lock,
sp_who2 and query master..sysprocesses.
You may also want to take a look at the following article:
INF: How to Monitor SQL Server 7.0 Blocking
http://support.microsoft.com/?id=251004
-Sue
On Fri, 04 Nov 2005 09:54:12 -0500, elf
<eric@.northstarcc.com> wrote:
>For some time, I have been trying to determine why we are getting
>timeouts under certain conditions using an A2003 front end, SQL2000
>backend. I have recently seen several quirky conditions related to a
>too-low setting of maxlocksperfile.
>Can that setting perhaps cause a timeout if the process is trying to
>acquire too many locks?
>TIA
|||Thanks, It is DAO.
Sue Hoegemeier wrote:
> Maxlocksperfile is a Jet setting, not SQL Server setting. It
> really depends on how you have the Access piece implemented.
> If it's an ADP, there is no jet so no. Other than that, it
> depends.
> If this is just SQL Server and no jet involved, you'd
> probably want to start by checking for locking, blocking
> issues. You can use the system stored procedures sp_lock,
> sp_who2 and query master..sysprocesses.
> You may also want to take a look at the following article:
> INF: How to Monitor SQL Server 7.0 Blocking
> http://support.microsoft.com/?id=251004
> -Sue
> On Fri, 04 Nov 2005 09:54:12 -0500, elf
> <eric@.northstarcc.com> wrote:
>
>
|||So with it being Jet, don't know - that's an Access specific
thing. You'd probably want to ask that in one of the Access
newsgroups.
-Sue
On Tue, 08 Nov 2005 22:55:42 -0500, elf
<eric@.northstarcc.com> wrote:
[vbcol=seagreen]
>Thanks, It is DAO.
>
>Sue Hoegemeier wrote:
timeouts under certain conditions using an A2003 front end, SQL2000
backend. I have recently seen several quirky conditions related to a
too-low setting of maxlocksperfile.
Can that setting perhaps cause a timeout if the process is trying to
acquire too many locks?
TIA
Maxlocksperfile is a Jet setting, not SQL Server setting. It
really depends on how you have the Access piece implemented.
If it's an ADP, there is no jet so no. Other than that, it
depends.
If this is just SQL Server and no jet involved, you'd
probably want to start by checking for locking, blocking
issues. You can use the system stored procedures sp_lock,
sp_who2 and query master..sysprocesses.
You may also want to take a look at the following article:
INF: How to Monitor SQL Server 7.0 Blocking
http://support.microsoft.com/?id=251004
-Sue
On Fri, 04 Nov 2005 09:54:12 -0500, elf
<eric@.northstarcc.com> wrote:
>For some time, I have been trying to determine why we are getting
>timeouts under certain conditions using an A2003 front end, SQL2000
>backend. I have recently seen several quirky conditions related to a
>too-low setting of maxlocksperfile.
>Can that setting perhaps cause a timeout if the process is trying to
>acquire too many locks?
>TIA
|||Thanks, It is DAO.
Sue Hoegemeier wrote:
> Maxlocksperfile is a Jet setting, not SQL Server setting. It
> really depends on how you have the Access piece implemented.
> If it's an ADP, there is no jet so no. Other than that, it
> depends.
> If this is just SQL Server and no jet involved, you'd
> probably want to start by checking for locking, blocking
> issues. You can use the system stored procedures sp_lock,
> sp_who2 and query master..sysprocesses.
> You may also want to take a look at the following article:
> INF: How to Monitor SQL Server 7.0 Blocking
> http://support.microsoft.com/?id=251004
> -Sue
> On Fri, 04 Nov 2005 09:54:12 -0500, elf
> <eric@.northstarcc.com> wrote:
>
>
|||So with it being Jet, don't know - that's an Access specific
thing. You'd probably want to ask that in one of the Access
newsgroups.
-Sue
On Tue, 08 Nov 2005 22:55:42 -0500, elf
<eric@.northstarcc.com> wrote:
[vbcol=seagreen]
>Thanks, It is DAO.
>
>Sue Hoegemeier wrote:
Locks output from sysprocesses table
Can you explain the lock information output from
sysprocesses table that I received?
SPID waittype waittime lastwaittype
waitresources
155 0x0000 0 LCK_M_S KEY: 7:1:1 (b400b60e9149)
160 0x0000 0 PAGELATCH_UP 2:1:31965
166 0x0000 0 PAGEIOLATCH_SH 7:1:523911
183 0x0000 0 PAGELATCH_UP 2:1:31988
198 0x0000 0 LCK_M_S KEY: 7:1:1 (b400b60e9149)
216 0x0000 0 LCK_M_S KEY: 7:1:1 (8b00c4cca785)
217 0x0000 0 PAGELATCH_UP 2:1:31984
218 0x0000 0 PAGEIOLATCH_SH 7:1:1395322
219 0x0000 0 PAGEIOLATCH_SH 7:1:642128
228 0x0000 0 PAGEIOLATCH_SH 7:1:1605295
230 0x0000 0 PAGEIOLATCH_SH 7:1:1587592
Thank You,
MikeHi Mike
Right now it is showing that none of your processes are waiting for
anything. All of them have a waittype and waittime of 0 which means they are
NOT waiting. THe lastwaittype column, as the name implies, is the last thing
the process waited on, but there is no indication of how long ago or what
duration that wait was. The LCK-* waits are normal locks being waited for,
and the PAGEIOLATCH* waits are waiting for IO.
This looks totally boring and nothing to worry about.
Did you have some specific concerns about it?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:1886001c41b62$9579a9e0$a301280a@.phx
.gbl...
> Can you explain the lock information output from
> sysprocesses table that I received?
> SPID waittype waittime lastwaittype
> waitresources
> 155 0x0000 0 LCK_M_S KEY: 7:1:1 (b400b60e9149)
> 160 0x0000 0 PAGELATCH_UP 2:1:31965
> 166 0x0000 0 PAGEIOLATCH_SH 7:1:523911
> 183 0x0000 0 PAGELATCH_UP 2:1:31988
> 198 0x0000 0 LCK_M_S KEY: 7:1:1 (b400b60e9149)
> 216 0x0000 0 LCK_M_S KEY: 7:1:1 (8b00c4cca785)
> 217 0x0000 0 PAGELATCH_UP 2:1:31984
> 218 0x0000 0 PAGEIOLATCH_SH 7:1:1395322
> 219 0x0000 0 PAGEIOLATCH_SH 7:1:642128
> 228 0x0000 0 PAGEIOLATCH_SH 7:1:1605295
> 230 0x0000 0 PAGEIOLATCH_SH 7:1:1587592
> Thank You,
> Mike
sysprocesses table that I received?
SPID waittype waittime lastwaittype
waitresources
155 0x0000 0 LCK_M_S KEY: 7:1:1 (b400b60e9149)
160 0x0000 0 PAGELATCH_UP 2:1:31965
166 0x0000 0 PAGEIOLATCH_SH 7:1:523911
183 0x0000 0 PAGELATCH_UP 2:1:31988
198 0x0000 0 LCK_M_S KEY: 7:1:1 (b400b60e9149)
216 0x0000 0 LCK_M_S KEY: 7:1:1 (8b00c4cca785)
217 0x0000 0 PAGELATCH_UP 2:1:31984
218 0x0000 0 PAGEIOLATCH_SH 7:1:1395322
219 0x0000 0 PAGEIOLATCH_SH 7:1:642128
228 0x0000 0 PAGEIOLATCH_SH 7:1:1605295
230 0x0000 0 PAGEIOLATCH_SH 7:1:1587592
Thank You,
MikeHi Mike
Right now it is showing that none of your processes are waiting for
anything. All of them have a waittype and waittime of 0 which means they are
NOT waiting. THe lastwaittype column, as the name implies, is the last thing
the process waited on, but there is no indication of how long ago or what
duration that wait was. The LCK-* waits are normal locks being waited for,
and the PAGEIOLATCH* waits are waiting for IO.
This looks totally boring and nothing to worry about.
Did you have some specific concerns about it?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:1886001c41b62$9579a9e0$a301280a@.phx
.gbl...
> Can you explain the lock information output from
> sysprocesses table that I received?
> SPID waittype waittime lastwaittype
> waitresources
> 155 0x0000 0 LCK_M_S KEY: 7:1:1 (b400b60e9149)
> 160 0x0000 0 PAGELATCH_UP 2:1:31965
> 166 0x0000 0 PAGEIOLATCH_SH 7:1:523911
> 183 0x0000 0 PAGELATCH_UP 2:1:31988
> 198 0x0000 0 LCK_M_S KEY: 7:1:1 (b400b60e9149)
> 216 0x0000 0 LCK_M_S KEY: 7:1:1 (8b00c4cca785)
> 217 0x0000 0 PAGELATCH_UP 2:1:31984
> 218 0x0000 0 PAGEIOLATCH_SH 7:1:1395322
> 219 0x0000 0 PAGEIOLATCH_SH 7:1:642128
> 228 0x0000 0 PAGEIOLATCH_SH 7:1:1605295
> 230 0x0000 0 PAGEIOLATCH_SH 7:1:1587592
> Thank You,
> Mike
Locks Option - Need advanced help
I am having a problem with lock resources on my server. I am getting
Error: 1204 Cannot obtain LOCK resource at this time.
Now, before you jump to any quick answers, please keep reading.
SQL2K SP3, 8 GB RAM, AWE Enabled, Min Server memory = 1024 MB, Max
Server memory = 7168 MB
When I had the server set to dynamically configure the locks, I would
get error 1204 after my Lock memory increased to 985,728 KB. It would
increase steadily, but once it got to 985,728 KB - it would hit a wall
and not increase any further. It is my understanding that lock memory
should allocate up to 40% of the total server memory. This number
represents only about 13% of the total server memory allocated at the
time.
Considering I could not get enough resources allocated, I tried to do
the math myself and manually configure the locks option on the server
to a value of 34,000,000. Considering each lock represents 96 bytes,
this number should be about 40% of my total server memory.
Problem is that when my server starts, I get the following error:
Can't allocate 34000000 locks on startup, reverting to 4309162, (25%
of committed memory)
Where is that coming from? Why is it allocating only 25% and 25% of
what? Certainly not 7168 MB. It is my understanding that when AWE is
enabled, it reserves the maximum server memory as soon as SQL starts.
Also, when I configure the lock option manually, I thought I was just
configuring the maximum it could get to, not the value that it would
reserve all the time, so it should not try to allocate that much at
startup.
I would love to use the dynamic configuration of locks if it would
wouldn't get stuck at a maximum of 13% of server memory.
Please help. Thanks.I could be wrong but I believe that locks are one of the many things in
memory that can not live in the AWE memory space. That would explain why
you can't go higher. That's a lot of memory for locks. I would look into
why you are taking so many locks as that is the real root of the problem.
--
Andrew J. Kelly
SQL Server MVP
"Jeff Albenberg" <jalbenberg@.yahoo.com> wrote in message
news:e9dc0a21.0310281557.6785de18@.posting.google.com...
> I am having a problem with lock resources on my server. I am getting
> Error: 1204 Cannot obtain LOCK resource at this time.
> Now, before you jump to any quick answers, please keep reading.
> SQL2K SP3, 8 GB RAM, AWE Enabled, Min Server memory = 1024 MB, Max
> Server memory = 7168 MB
> When I had the server set to dynamically configure the locks, I would
> get error 1204 after my Lock memory increased to 985,728 KB. It would
> increase steadily, but once it got to 985,728 KB - it would hit a wall
> and not increase any further. It is my understanding that lock memory
> should allocate up to 40% of the total server memory. This number
> represents only about 13% of the total server memory allocated at the
> time.
> Considering I could not get enough resources allocated, I tried to do
> the math myself and manually configure the locks option on the server
> to a value of 34,000,000. Considering each lock represents 96 bytes,
> this number should be about 40% of my total server memory.
> Problem is that when my server starts, I get the following error:
> Can't allocate 34000000 locks on startup, reverting to 4309162, (25%
> of committed memory)
> Where is that coming from? Why is it allocating only 25% and 25% of
> what? Certainly not 7168 MB. It is my understanding that when AWE is
> enabled, it reserves the maximum server memory as soon as SQL starts.
> Also, when I configure the lock option manually, I thought I was just
> configuring the maximum it could get to, not the value that it would
> reserve all the time, so it should not try to allocate that much at
> startup.
> I would love to use the dynamic configuration of locks if it would
> wouldn't get stuck at a maximum of 13% of server memory.
> Please help. Thanks.|||Andrew is right. Locks can not be allocated from the AWE space. The lock
manager constrains the amount of memory it consumes based upon the committed
size of the buffer pool. There is a perfmon counter that will give you the
buffer manager's committed size.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u0ODNDbnDHA.2068@.TK2MSFTNGP09.phx.gbl...
> I could be wrong but I believe that locks are one of the many things in
> memory that can not live in the AWE memory space. That would explain why
> you can't go higher. That's a lot of memory for locks. I would look into
> why you are taking so many locks as that is the real root of the problem.
>|||what do you mean by committed size of the buffer pool ?
"David Campbell" <dave_gc_nospam@.hotmail.com> wrote in message
news:vpuekj1v7dot38@.corp.supernews.com...
> Andrew is right. Locks can not be allocated from the AWE space. The lock
> manager constrains the amount of memory it consumes based upon the
committed
> size of the buffer pool. There is a perfmon counter that will give you the
> buffer manager's committed size.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u0ODNDbnDHA.2068@.TK2MSFTNGP09.phx.gbl...
> > I could be wrong but I believe that locks are one of the many things in
> > memory that can not live in the AWE memory space. That would explain
why
> > you can't go higher. That's a lot of memory for locks. I would look
into
> > why you are taking so many locks as that is the real root of the
problem.
> >
>|||Jeff, Still struggling eh? You asked yourself 25% of what. Isn't it simply
25% of min server memory? I think that comes very lcose to 64 bytes per lock
(64*4309162*4(=25%)) is about 1024MB, your 'min server mem'.
Locks are 64 byte structures, a lock owner adds 32 bytes, and there is also
a lock hash slot structure with 8 byte per lock(as far as I could see). So
maybe by combining some of these other 32 and 8 byte numbers, sql comes up
with the 4309162 locks
I would think that increasing the 'min server memory' could solve your
problem, but that's (an educated) guess.
As the other authors say, SQLserver doesn't allocate lock structures from
AWE memory. I think because of the implementation of AWE, it is to 'clumsy'
to use it for other things than database page buffers.
regards,
Mario
http://www.sqlinternals.com
"Jeff Albenberg" <jalbenberg@.yahoo.com> wrote in message
news:e9dc0a21.0310281557.6785de18@.posting.google.com...
> I am having a problem with lock resources on my server. I am getting
> Error: 1204 Cannot obtain LOCK resource at this time.
> Now, before you jump to any quick answers, please keep reading.
> SQL2K SP3, 8 GB RAM, AWE Enabled, Min Server memory = 1024 MB, Max
> Server memory = 7168 MB
> When I had the server set to dynamically configure the locks, I would
> get error 1204 after my Lock memory increased to 985,728 KB. It would
> increase steadily, but once it got to 985,728 KB - it would hit a wall
> and not increase any further. It is my understanding that lock memory
> should allocate up to 40% of the total server memory. This number
> represents only about 13% of the total server memory allocated at the
> time.
> Considering I could not get enough resources allocated, I tried to do
> the math myself and manually configure the locks option on the server
> to a value of 34,000,000. Considering each lock represents 96 bytes,
> this number should be about 40% of my total server memory.
> Problem is that when my server starts, I get the following error:
> Can't allocate 34000000 locks on startup, reverting to 4309162, (25%
> of committed memory)
> Where is that coming from? Why is it allocating only 25% and 25% of
> what? Certainly not 7168 MB. It is my understanding that when AWE is
> enabled, it reserves the maximum server memory as soon as SQL starts.
> Also, when I configure the lock option manually, I thought I was just
> configuring the maximum it could get to, not the value that it would
> reserve all the time, so it should not try to allocate that much at
> startup.
> I would love to use the dynamic configuration of locks if it would
> wouldn't get stuck at a maximum of 13% of server memory.
> Please help. Thanks.|||Certainly don't want to put words in David's mouth but I believe he is
referring to the amount of memory in the buffer pool that is actually being
used. Just because you set the max memory to a certain size doesn't mean it
is actually holding data in all those buffer slots.
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uA96d8dnDHA.2404@.TK2MSFTNGP12.phx.gbl...
> what do you mean by committed size of the buffer pool ?
> "David Campbell" <dave_gc_nospam@.hotmail.com> wrote in message
> news:vpuekj1v7dot38@.corp.supernews.com...
> > Andrew is right. Locks can not be allocated from the AWE space. The lock
> > manager constrains the amount of memory it consumes based upon the
> committed
> > size of the buffer pool. There is a perfmon counter that will give you
the
> > buffer manager's committed size.
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:u0ODNDbnDHA.2068@.TK2MSFTNGP09.phx.gbl...
> > > I could be wrong but I believe that locks are one of the many things
in
> > > memory that can not live in the AWE memory space. That would explain
> why
> > > you can't go higher. That's a lot of memory for locks. I would look
> into
> > > why you are taking so many locks as that is the real root of the
> problem.
> > >
> >
> >
>|||"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uA96d8dnDHA.2404@.TK2MSFTNGP12.phx.gbl...
> what do you mean by committed size of the buffer pool ?
SQL Server's buffer manager allocates the bulk of a processes *VIRTUAL*
address space on startup and then controls the amount of committed memory it
consumes by using the VirtualAlloc API with the MEM_COMMIT flag. This is how
SQL Server dynamically grows and shrinks its memory in response to system
demand.
The "committed" size of the buffer pool is the amount of memory the buffer
pool has committed at that point.
I agree with Andrew in thinking the interesting question is why the process
is consuming so many locks.
Error: 1204 Cannot obtain LOCK resource at this time.
Now, before you jump to any quick answers, please keep reading.
SQL2K SP3, 8 GB RAM, AWE Enabled, Min Server memory = 1024 MB, Max
Server memory = 7168 MB
When I had the server set to dynamically configure the locks, I would
get error 1204 after my Lock memory increased to 985,728 KB. It would
increase steadily, but once it got to 985,728 KB - it would hit a wall
and not increase any further. It is my understanding that lock memory
should allocate up to 40% of the total server memory. This number
represents only about 13% of the total server memory allocated at the
time.
Considering I could not get enough resources allocated, I tried to do
the math myself and manually configure the locks option on the server
to a value of 34,000,000. Considering each lock represents 96 bytes,
this number should be about 40% of my total server memory.
Problem is that when my server starts, I get the following error:
Can't allocate 34000000 locks on startup, reverting to 4309162, (25%
of committed memory)
Where is that coming from? Why is it allocating only 25% and 25% of
what? Certainly not 7168 MB. It is my understanding that when AWE is
enabled, it reserves the maximum server memory as soon as SQL starts.
Also, when I configure the lock option manually, I thought I was just
configuring the maximum it could get to, not the value that it would
reserve all the time, so it should not try to allocate that much at
startup.
I would love to use the dynamic configuration of locks if it would
wouldn't get stuck at a maximum of 13% of server memory.
Please help. Thanks.I could be wrong but I believe that locks are one of the many things in
memory that can not live in the AWE memory space. That would explain why
you can't go higher. That's a lot of memory for locks. I would look into
why you are taking so many locks as that is the real root of the problem.
--
Andrew J. Kelly
SQL Server MVP
"Jeff Albenberg" <jalbenberg@.yahoo.com> wrote in message
news:e9dc0a21.0310281557.6785de18@.posting.google.com...
> I am having a problem with lock resources on my server. I am getting
> Error: 1204 Cannot obtain LOCK resource at this time.
> Now, before you jump to any quick answers, please keep reading.
> SQL2K SP3, 8 GB RAM, AWE Enabled, Min Server memory = 1024 MB, Max
> Server memory = 7168 MB
> When I had the server set to dynamically configure the locks, I would
> get error 1204 after my Lock memory increased to 985,728 KB. It would
> increase steadily, but once it got to 985,728 KB - it would hit a wall
> and not increase any further. It is my understanding that lock memory
> should allocate up to 40% of the total server memory. This number
> represents only about 13% of the total server memory allocated at the
> time.
> Considering I could not get enough resources allocated, I tried to do
> the math myself and manually configure the locks option on the server
> to a value of 34,000,000. Considering each lock represents 96 bytes,
> this number should be about 40% of my total server memory.
> Problem is that when my server starts, I get the following error:
> Can't allocate 34000000 locks on startup, reverting to 4309162, (25%
> of committed memory)
> Where is that coming from? Why is it allocating only 25% and 25% of
> what? Certainly not 7168 MB. It is my understanding that when AWE is
> enabled, it reserves the maximum server memory as soon as SQL starts.
> Also, when I configure the lock option manually, I thought I was just
> configuring the maximum it could get to, not the value that it would
> reserve all the time, so it should not try to allocate that much at
> startup.
> I would love to use the dynamic configuration of locks if it would
> wouldn't get stuck at a maximum of 13% of server memory.
> Please help. Thanks.|||Andrew is right. Locks can not be allocated from the AWE space. The lock
manager constrains the amount of memory it consumes based upon the committed
size of the buffer pool. There is a perfmon counter that will give you the
buffer manager's committed size.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u0ODNDbnDHA.2068@.TK2MSFTNGP09.phx.gbl...
> I could be wrong but I believe that locks are one of the many things in
> memory that can not live in the AWE memory space. That would explain why
> you can't go higher. That's a lot of memory for locks. I would look into
> why you are taking so many locks as that is the real root of the problem.
>|||what do you mean by committed size of the buffer pool ?
"David Campbell" <dave_gc_nospam@.hotmail.com> wrote in message
news:vpuekj1v7dot38@.corp.supernews.com...
> Andrew is right. Locks can not be allocated from the AWE space. The lock
> manager constrains the amount of memory it consumes based upon the
committed
> size of the buffer pool. There is a perfmon counter that will give you the
> buffer manager's committed size.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u0ODNDbnDHA.2068@.TK2MSFTNGP09.phx.gbl...
> > I could be wrong but I believe that locks are one of the many things in
> > memory that can not live in the AWE memory space. That would explain
why
> > you can't go higher. That's a lot of memory for locks. I would look
into
> > why you are taking so many locks as that is the real root of the
problem.
> >
>|||Jeff, Still struggling eh? You asked yourself 25% of what. Isn't it simply
25% of min server memory? I think that comes very lcose to 64 bytes per lock
(64*4309162*4(=25%)) is about 1024MB, your 'min server mem'.
Locks are 64 byte structures, a lock owner adds 32 bytes, and there is also
a lock hash slot structure with 8 byte per lock(as far as I could see). So
maybe by combining some of these other 32 and 8 byte numbers, sql comes up
with the 4309162 locks
I would think that increasing the 'min server memory' could solve your
problem, but that's (an educated) guess.
As the other authors say, SQLserver doesn't allocate lock structures from
AWE memory. I think because of the implementation of AWE, it is to 'clumsy'
to use it for other things than database page buffers.
regards,
Mario
http://www.sqlinternals.com
"Jeff Albenberg" <jalbenberg@.yahoo.com> wrote in message
news:e9dc0a21.0310281557.6785de18@.posting.google.com...
> I am having a problem with lock resources on my server. I am getting
> Error: 1204 Cannot obtain LOCK resource at this time.
> Now, before you jump to any quick answers, please keep reading.
> SQL2K SP3, 8 GB RAM, AWE Enabled, Min Server memory = 1024 MB, Max
> Server memory = 7168 MB
> When I had the server set to dynamically configure the locks, I would
> get error 1204 after my Lock memory increased to 985,728 KB. It would
> increase steadily, but once it got to 985,728 KB - it would hit a wall
> and not increase any further. It is my understanding that lock memory
> should allocate up to 40% of the total server memory. This number
> represents only about 13% of the total server memory allocated at the
> time.
> Considering I could not get enough resources allocated, I tried to do
> the math myself and manually configure the locks option on the server
> to a value of 34,000,000. Considering each lock represents 96 bytes,
> this number should be about 40% of my total server memory.
> Problem is that when my server starts, I get the following error:
> Can't allocate 34000000 locks on startup, reverting to 4309162, (25%
> of committed memory)
> Where is that coming from? Why is it allocating only 25% and 25% of
> what? Certainly not 7168 MB. It is my understanding that when AWE is
> enabled, it reserves the maximum server memory as soon as SQL starts.
> Also, when I configure the lock option manually, I thought I was just
> configuring the maximum it could get to, not the value that it would
> reserve all the time, so it should not try to allocate that much at
> startup.
> I would love to use the dynamic configuration of locks if it would
> wouldn't get stuck at a maximum of 13% of server memory.
> Please help. Thanks.|||Certainly don't want to put words in David's mouth but I believe he is
referring to the amount of memory in the buffer pool that is actually being
used. Just because you set the max memory to a certain size doesn't mean it
is actually holding data in all those buffer slots.
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uA96d8dnDHA.2404@.TK2MSFTNGP12.phx.gbl...
> what do you mean by committed size of the buffer pool ?
> "David Campbell" <dave_gc_nospam@.hotmail.com> wrote in message
> news:vpuekj1v7dot38@.corp.supernews.com...
> > Andrew is right. Locks can not be allocated from the AWE space. The lock
> > manager constrains the amount of memory it consumes based upon the
> committed
> > size of the buffer pool. There is a perfmon counter that will give you
the
> > buffer manager's committed size.
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:u0ODNDbnDHA.2068@.TK2MSFTNGP09.phx.gbl...
> > > I could be wrong but I believe that locks are one of the many things
in
> > > memory that can not live in the AWE memory space. That would explain
> why
> > > you can't go higher. That's a lot of memory for locks. I would look
> into
> > > why you are taking so many locks as that is the real root of the
> problem.
> > >
> >
> >
>|||"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uA96d8dnDHA.2404@.TK2MSFTNGP12.phx.gbl...
> what do you mean by committed size of the buffer pool ?
SQL Server's buffer manager allocates the bulk of a processes *VIRTUAL*
address space on startup and then controls the amount of committed memory it
consumes by using the VirtualAlloc API with the MEM_COMMIT flag. This is how
SQL Server dynamically grows and shrinks its memory in response to system
demand.
The "committed" size of the buffer pool is the amount of memory the buffer
pool has committed at that point.
I agree with Andrew in thinking the interesting question is why the process
is consuming so many locks.
locks on tables...
i have transactional replication with a push, updatable subscriber
configured and
when updates (UPDATEs and DELETEs wrapped in a single transaction) are
executed on the subscriber the changes EVENTUALLY make it back to the
publisher as they should.
the problem i am having is when these sp_MSsync_upd_<tablename> and
sp_MSupd_<tablename> sprocs run on the publisher when the changes occur on
the subscriber they are locking these tables and the entire website (on
publisher side) is unusable due to locks on the records.
but whats weird is that a trace on the publisher that shows all the
sp_MSsync_upd_<tablename> and sp_MSupd_<tablename> calls have SO MANY OF
THEM compared to the actual number of records being deleted on the
subscriber.
Any ideas.
-Terry
Yes, this is one of the problems of using updateable subscribers. Each
singleton in a batch is fired over the network and can cause huge latency
issues, like the one you are seeing. Perhaps you should revisit how you are
doing your replication and select a more appropriate model.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Terry Mulvany" <terry.mulvany@.rouseservices.com> wrote in message
news:OYEe4tnZHHA.3928@.TK2MSFTNGP03.phx.gbl...
>i have transactional replication with a push, updatable subscriber
>configured and
> when updates (UPDATEs and DELETEs wrapped in a single transaction) are
> executed on the subscriber the changes EVENTUALLY make it back to the
> publisher as they should.
> the problem i am having is when these sp_MSsync_upd_<tablename> and
> sp_MSupd_<tablename> sprocs run on the publisher when the changes occur on
> the subscriber they are locking these tables and the entire website (on
> publisher side) is unusable due to locks on the records.
> but whats weird is that a trace on the publisher that shows all the
> sp_MSsync_upd_<tablename> and sp_MSupd_<tablename> calls have SO MANY OF
> THEM compared to the actual number of records being deleted on the
> subscriber.
> Any ideas.
> -Terry
>
>
configured and
when updates (UPDATEs and DELETEs wrapped in a single transaction) are
executed on the subscriber the changes EVENTUALLY make it back to the
publisher as they should.
the problem i am having is when these sp_MSsync_upd_<tablename> and
sp_MSupd_<tablename> sprocs run on the publisher when the changes occur on
the subscriber they are locking these tables and the entire website (on
publisher side) is unusable due to locks on the records.
but whats weird is that a trace on the publisher that shows all the
sp_MSsync_upd_<tablename> and sp_MSupd_<tablename> calls have SO MANY OF
THEM compared to the actual number of records being deleted on the
subscriber.
Any ideas.
-Terry
Yes, this is one of the problems of using updateable subscribers. Each
singleton in a batch is fired over the network and can cause huge latency
issues, like the one you are seeing. Perhaps you should revisit how you are
doing your replication and select a more appropriate model.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Terry Mulvany" <terry.mulvany@.rouseservices.com> wrote in message
news:OYEe4tnZHHA.3928@.TK2MSFTNGP03.phx.gbl...
>i have transactional replication with a push, updatable subscriber
>configured and
> when updates (UPDATEs and DELETEs wrapped in a single transaction) are
> executed on the subscriber the changes EVENTUALLY make it back to the
> publisher as they should.
> the problem i am having is when these sp_MSsync_upd_<tablename> and
> sp_MSupd_<tablename> sprocs run on the publisher when the changes occur on
> the subscriber they are locking these tables and the entire website (on
> publisher side) is unusable due to locks on the records.
> but whats weird is that a trace on the publisher that shows all the
> sp_MSsync_upd_<tablename> and sp_MSupd_<tablename> calls have SO MANY OF
> THEM compared to the actual number of records being deleted on the
> subscriber.
> Any ideas.
> -Terry
>
>
locks on table when update statistics
Does anybody know what kind of lock will put on the table
when sqlserver update the statistics?Only a schema stability lock, which means that you can't change the table,
but you can do everything else, and the statistics update won't block
anything.
--
Jacco Schalkwijk
SQL Server MVP
"jzhu" <hzhua16@.hotmail.com> wrote in message
news:03ef01c39e54$197dc460$a101280a@.phx.gbl...
> Does anybody know what kind of lock will put on the table
> when sqlserver update the statistics?
when sqlserver update the statistics?Only a schema stability lock, which means that you can't change the table,
but you can do everything else, and the statistics update won't block
anything.
--
Jacco Schalkwijk
SQL Server MVP
"jzhu" <hzhua16@.hotmail.com> wrote in message
news:03ef01c39e54$197dc460$a101280a@.phx.gbl...
> Does anybody know what kind of lock will put on the table
> when sqlserver update the statistics?
locks on object "xp_startmail"
Hy,
recently I got a problem with locks on my sql server 2000
SP3. I took a trace and I could see that there are a lot
of lock timeout (sometimes deadlock, but not regularly)
when the load is heavy on the sql server machine.
I took a trace and tryed to understand the trace result,
and I found that almost always the lock were on an
objectid (309576141) not corresponding to a table or an
index but to a store procedure : xp_sendmail ! I was very
astonished because this seems to be a procedure for
sending mail (I'm not sending any mail in my sql server
application). Moreover the SPID timing out is often a
task manager process.
Now, I would like to know why I can't see the real object
on which the process is timed out OR if this kind of lock
hides some other problems I'd better face.
Many thanks for any kind of suggestion.
cristina
Sorry for the basic question, but did you check for the object id in the correct database?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cris" <anonymous@.discussions.microsoft.com> wrote in message news:1fe201c44a4b$32290c10$7d02280a@.phx.gbl...
> Hy,
> recently I got a problem with locks on my sql server 2000
> SP3. I took a trace and I could see that there are a lot
> of lock timeout (sometimes deadlock, but not regularly)
> when the load is heavy on the sql server machine.
> I took a trace and tryed to understand the trace result,
> and I found that almost always the lock were on an
> objectid (309576141) not corresponding to a table or an
> index but to a store procedure : xp_sendmail ! I was very
> astonished because this seems to be a procedure for
> sending mail (I'm not sending any mail in my sql server
> application). Moreover the SPID timing out is often a
> task manager process.
> Now, I would like to know why I can't see the real object
> on which the process is timed out OR if this kind of lock
> hides some other problems I'd better face.
> Many thanks for any kind of suggestion.
> cristina
|||Thankyou very much for your suggestion!
I feel very ashamed about my error. I was checking in the 'master' database!
Escuse me, but I made a blunder, and without your help I couldn't get out of this error...
recently I got a problem with locks on my sql server 2000
SP3. I took a trace and I could see that there are a lot
of lock timeout (sometimes deadlock, but not regularly)
when the load is heavy on the sql server machine.
I took a trace and tryed to understand the trace result,
and I found that almost always the lock were on an
objectid (309576141) not corresponding to a table or an
index but to a store procedure : xp_sendmail ! I was very
astonished because this seems to be a procedure for
sending mail (I'm not sending any mail in my sql server
application). Moreover the SPID timing out is often a
task manager process.
Now, I would like to know why I can't see the real object
on which the process is timed out OR if this kind of lock
hides some other problems I'd better face.
Many thanks for any kind of suggestion.
cristina
Sorry for the basic question, but did you check for the object id in the correct database?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cris" <anonymous@.discussions.microsoft.com> wrote in message news:1fe201c44a4b$32290c10$7d02280a@.phx.gbl...
> Hy,
> recently I got a problem with locks on my sql server 2000
> SP3. I took a trace and I could see that there are a lot
> of lock timeout (sometimes deadlock, but not regularly)
> when the load is heavy on the sql server machine.
> I took a trace and tryed to understand the trace result,
> and I found that almost always the lock were on an
> objectid (309576141) not corresponding to a table or an
> index but to a store procedure : xp_sendmail ! I was very
> astonished because this seems to be a procedure for
> sending mail (I'm not sending any mail in my sql server
> application). Moreover the SPID timing out is often a
> task manager process.
> Now, I would like to know why I can't see the real object
> on which the process is timed out OR if this kind of lock
> hides some other problems I'd better face.
> Many thanks for any kind of suggestion.
> cristina
|||Thankyou very much for your suggestion!
I feel very ashamed about my error. I was checking in the 'master' database!
Escuse me, but I made a blunder, and without your help I couldn't get out of this error...
locks on object "xp_startmail"
Hy,
recently I got a problem with locks on my sql server 2000
SP3. I took a trace and I could see that there are a lot
of lock timeout (sometimes deadlock, but not regularly)
when the load is heavy on the sql server machine.
I took a trace and tryed to understand the trace result,
and I found that almost always the lock were on an
objectid (309576141) not corresponding to a table or an
index but to a store procedure : xp_sendmail ! I was very
astonished because this seems to be a procedure for
sending mail (I'm not sending any mail in my sql server
application). Moreover the SPID timing out is often a
task manager process.
Now, I would like to know why I can't see the real object
on which the process is timed out OR if this kind of lock
hides some other problems I'd better face.
Many thanks for any kind of suggestion.
cristinaSorry for the basic question, but did you check for the object id in the correct database?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cris" <anonymous@.discussions.microsoft.com> wrote in message news:1fe201c44a4b$32290c10$7d02280a@.phx.gbl...
> Hy,
> recently I got a problem with locks on my sql server 2000
> SP3. I took a trace and I could see that there are a lot
> of lock timeout (sometimes deadlock, but not regularly)
> when the load is heavy on the sql server machine.
> I took a trace and tryed to understand the trace result,
> and I found that almost always the lock were on an
> objectid (309576141) not corresponding to a table or an
> index but to a store procedure : xp_sendmail ! I was very
> astonished because this seems to be a procedure for
> sending mail (I'm not sending any mail in my sql server
> application). Moreover the SPID timing out is often a
> task manager process.
> Now, I would like to know why I can't see the real object
> on which the process is timed out OR if this kind of lock
> hides some other problems I'd better face.
> Many thanks for any kind of suggestion.
> cristina|||Thankyou very much for your suggestion!
I feel very ashamed about my error. I was checking in the 'master' database
Escuse me, but I made a blunder, and without your help I couldn't get out of this error..
recently I got a problem with locks on my sql server 2000
SP3. I took a trace and I could see that there are a lot
of lock timeout (sometimes deadlock, but not regularly)
when the load is heavy on the sql server machine.
I took a trace and tryed to understand the trace result,
and I found that almost always the lock were on an
objectid (309576141) not corresponding to a table or an
index but to a store procedure : xp_sendmail ! I was very
astonished because this seems to be a procedure for
sending mail (I'm not sending any mail in my sql server
application). Moreover the SPID timing out is often a
task manager process.
Now, I would like to know why I can't see the real object
on which the process is timed out OR if this kind of lock
hides some other problems I'd better face.
Many thanks for any kind of suggestion.
cristinaSorry for the basic question, but did you check for the object id in the correct database?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cris" <anonymous@.discussions.microsoft.com> wrote in message news:1fe201c44a4b$32290c10$7d02280a@.phx.gbl...
> Hy,
> recently I got a problem with locks on my sql server 2000
> SP3. I took a trace and I could see that there are a lot
> of lock timeout (sometimes deadlock, but not regularly)
> when the load is heavy on the sql server machine.
> I took a trace and tryed to understand the trace result,
> and I found that almost always the lock were on an
> objectid (309576141) not corresponding to a table or an
> index but to a store procedure : xp_sendmail ! I was very
> astonished because this seems to be a procedure for
> sending mail (I'm not sending any mail in my sql server
> application). Moreover the SPID timing out is often a
> task manager process.
> Now, I would like to know why I can't see the real object
> on which the process is timed out OR if this kind of lock
> hides some other problems I'd better face.
> Many thanks for any kind of suggestion.
> cristina|||Thankyou very much for your suggestion!
I feel very ashamed about my error. I was checking in the 'master' database
Escuse me, but I made a blunder, and without your help I couldn't get out of this error..
locks on object "xp_startmail"
Hy,
recently I got a problem with locks on my sql server 2000
SP3. I took a trace and I could see that there are a lot
of lock timeout (sometimes deadlock, but not regularly)
when the load is heavy on the sql server machine.
I took a trace and tryed to understand the trace result,
and I found that almost always the lock were on an
objectid (309576141) not corresponding to a table or an
index but to a store procedure : xp_sendmail ! I was very
astonished because this seems to be a procedure for
sending mail (I'm not sending any mail in my sql server
application). Moreover the SPID timing out is often a
task manager process.
Now, I would like to know why I can't see the real object
on which the process is timed out OR if this kind of lock
hides some other problems I'd better face.
Many thanks for any kind of suggestion.
cristinaSorry for the basic question, but did you check for the object id in the cor
rect database?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cris" <anonymous@.discussions.microsoft.com> wrote in message news:1fe201c44a4b$32290c10$7d0
2280a@.phx.gbl...
> Hy,
> recently I got a problem with locks on my sql server 2000
> SP3. I took a trace and I could see that there are a lot
> of lock timeout (sometimes deadlock, but not regularly)
> when the load is heavy on the sql server machine.
> I took a trace and tryed to understand the trace result,
> and I found that almost always the lock were on an
> objectid (309576141) not corresponding to a table or an
> index but to a store procedure : xp_sendmail ! I was very
> astonished because this seems to be a procedure for
> sending mail (I'm not sending any mail in my sql server
> application). Moreover the SPID timing out is often a
> task manager process.
> Now, I would like to know why I can't see the real object
> on which the process is timed out OR if this kind of lock
> hides some other problems I'd better face.
> Many thanks for any kind of suggestion.
> cristina|||Thankyou very much for your suggestion!
I feel very ashamed about my error. I was checking in the 'master' database!
Escuse me, but I made a blunder, and without your help I couldn't get out o
f this error...
recently I got a problem with locks on my sql server 2000
SP3. I took a trace and I could see that there are a lot
of lock timeout (sometimes deadlock, but not regularly)
when the load is heavy on the sql server machine.
I took a trace and tryed to understand the trace result,
and I found that almost always the lock were on an
objectid (309576141) not corresponding to a table or an
index but to a store procedure : xp_sendmail ! I was very
astonished because this seems to be a procedure for
sending mail (I'm not sending any mail in my sql server
application). Moreover the SPID timing out is often a
task manager process.
Now, I would like to know why I can't see the real object
on which the process is timed out OR if this kind of lock
hides some other problems I'd better face.
Many thanks for any kind of suggestion.
cristinaSorry for the basic question, but did you check for the object id in the cor
rect database?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"cris" <anonymous@.discussions.microsoft.com> wrote in message news:1fe201c44a4b$32290c10$7d0
2280a@.phx.gbl...
> Hy,
> recently I got a problem with locks on my sql server 2000
> SP3. I took a trace and I could see that there are a lot
> of lock timeout (sometimes deadlock, but not regularly)
> when the load is heavy on the sql server machine.
> I took a trace and tryed to understand the trace result,
> and I found that almost always the lock were on an
> objectid (309576141) not corresponding to a table or an
> index but to a store procedure : xp_sendmail ! I was very
> astonished because this seems to be a procedure for
> sending mail (I'm not sending any mail in my sql server
> application). Moreover the SPID timing out is often a
> task manager process.
> Now, I would like to know why I can't see the real object
> on which the process is timed out OR if this kind of lock
> hides some other problems I'd better face.
> Many thanks for any kind of suggestion.
> cristina|||Thankyou very much for your suggestion!
I feel very ashamed about my error. I was checking in the 'master' database!
Escuse me, but I made a blunder, and without your help I couldn't get out o
f this error...
locks on devices....
Hi,
Anyone know whether the devices are exclusivley locked when the database comes online?
rajAll of the data files would be locked, unless (maybe) someone has set a database to "AutoClose". In order to copy datafiles from one place to another on the same server, you should detach and reattach them.
Anyone know whether the devices are exclusivley locked when the database comes online?
rajAll of the data files would be locked, unless (maybe) someone has set a database to "AutoClose". In order to copy datafiles from one place to another on the same server, you should detach and reattach them.
Locks on a table with nonclustered index
If i have a query such as
delete from table1
where col1 = 200
Say i had a non clustered index on col1 and thats the only index that i
have...
1) Can the execution plan include a non-clustered index scan if the
selectivity is low ?
2) If it does use a nonclustered index scan and maybe start of with row
locks and then escalates to table lock, how do you find out if the lock is
on the index pages or on the data pages or on both although in sp_lock it
would show an exclusive table lock ?
3) Even it it just row locks, what info can we get from the resource column
in sp_lock to tell us whether a row in the data page or index page is being
locked.. Would it just be data pages or would it be holding multiple row
locks ..some to delete its rows in data pages and some to delete the rows in
its index pages1. I doubt it would do an index scan in this case, but if the selectivity
were high enough, it could do an index seek.
2. I'm not sure exactly what you're asking. If there is a table lock X,
other processes cannot access the indexes on that table, so whether the
index pages are locked is irrelevant.
3. The resource column doesn't directly tell us, but if you have a page
number, you can use dbcc page to tell what kind of page it is. Also, you can
use the indid column, and if it is >1, then the page is from a nonclustered
index. You can have key locks (which are row locks in an index) on both
index keys and rows/keys in the table itself. In fact, if the table has nc
indexes, you'll usually see both.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"sql" <sql@.hotmail.com> wrote in message
news:u47QCA9hDHA.460@.TK2MSFTNGP12.phx.gbl...
> If i have a query such as
> delete from table1
> where col1 = 200
> Say i had a non clustered index on col1 and thats the only index that i
> have...
> 1) Can the execution plan include a non-clustered index scan if the
> selectivity is low ?
> 2) If it does use a nonclustered index scan and maybe start of with row
> locks and then escalates to table lock, how do you find out if the lock is
> on the index pages or on the data pages or on both although in sp_lock it
> would show an exclusive table lock ?
> 3) Even it it just row locks, what info can we get from the resource
column
> in sp_lock to tell us whether a row in the data page or index page is
being
> locked.. Would it just be data pages or would it be holding multiple row
> locks ..some to delete its rows in data pages and some to delete the rows
in
> its index pages
>
>
delete from table1
where col1 = 200
Say i had a non clustered index on col1 and thats the only index that i
have...
1) Can the execution plan include a non-clustered index scan if the
selectivity is low ?
2) If it does use a nonclustered index scan and maybe start of with row
locks and then escalates to table lock, how do you find out if the lock is
on the index pages or on the data pages or on both although in sp_lock it
would show an exclusive table lock ?
3) Even it it just row locks, what info can we get from the resource column
in sp_lock to tell us whether a row in the data page or index page is being
locked.. Would it just be data pages or would it be holding multiple row
locks ..some to delete its rows in data pages and some to delete the rows in
its index pages1. I doubt it would do an index scan in this case, but if the selectivity
were high enough, it could do an index seek.
2. I'm not sure exactly what you're asking. If there is a table lock X,
other processes cannot access the indexes on that table, so whether the
index pages are locked is irrelevant.
3. The resource column doesn't directly tell us, but if you have a page
number, you can use dbcc page to tell what kind of page it is. Also, you can
use the indid column, and if it is >1, then the page is from a nonclustered
index. You can have key locks (which are row locks in an index) on both
index keys and rows/keys in the table itself. In fact, if the table has nc
indexes, you'll usually see both.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"sql" <sql@.hotmail.com> wrote in message
news:u47QCA9hDHA.460@.TK2MSFTNGP12.phx.gbl...
> If i have a query such as
> delete from table1
> where col1 = 200
> Say i had a non clustered index on col1 and thats the only index that i
> have...
> 1) Can the execution plan include a non-clustered index scan if the
> selectivity is low ?
> 2) If it does use a nonclustered index scan and maybe start of with row
> locks and then escalates to table lock, how do you find out if the lock is
> on the index pages or on the data pages or on both although in sp_lock it
> would show an exclusive table lock ?
> 3) Even it it just row locks, what info can we get from the resource
column
> in sp_lock to tell us whether a row in the data page or index page is
being
> locked.. Would it just be data pages or would it be holding multiple row
> locks ..some to delete its rows in data pages and some to delete the rows
in
> its index pages
>
>
Locks on a table
For some reason, one table in our database (SQL 2000) keeps getting locks on
it so attempts to insert records on it seems to time out frequently. Any
ideas on what I might look for to keep this from happening? The table has
only a unique int for a PK and no other indexes. Thanks.David wrote:
> For some reason, one table in our database (SQL 2000) keeps getting locks on
> it so attempts to insert records on it seems to time out frequently. Any
> ideas on what I might look for to keep this from happening? The table has
> only a unique int for a PK and no other indexes. Thanks.
>
When the locking occurs, look at sp_lock, sp_who2, determine what spid
is locking the table. Then use DBCC INPUTBUFFER to see what that spid
is doing. My guess is that something is table-scanning the table, maybe
a sign that additional indexes are needed.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I'm not sure if this helps, but when I go into EM and look at the
Locks/process ID and click on a couple of the spid records, I see the
following entry in the objects list on a couple of the spid records:
Object = tempdb.dbo.##lockinfo67
Lock Type = TAB
Mode = X
Status = GRANT
Owner = Xact
Index = ##lockinfo67
Not sure if this helps.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45ACFD6F.3060409@.realsqlguy.com...
> David wrote:
>> For some reason, one table in our database (SQL 2000) keeps getting locks
>> on it so attempts to insert records on it seems to time out frequently.
>> Any ideas on what I might look for to keep this from happening? The
>> table has only a unique int for a PK and no other indexes. Thanks.
> When the locking occurs, look at sp_lock, sp_who2, determine what spid is
> locking the table. Then use DBCC INPUTBUFFER to see what that spid is
> doing. My guess is that something is table-scanning the table, maybe a
> sign that additional indexes are needed.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David wrote:
> I'm not sure if this helps, but when I go into EM and look at the
> Locks/process ID and click on a couple of the spid records, I see the
> following entry in the objects list on a couple of the spid records:
> Object = tempdb.dbo.##lockinfo67
> Lock Type = TAB
> Mode = X
> Status = GRANT
> Owner = Xact
> Index = ##lockinfo67
> Not sure if this helps.
>
Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||OK. It looks like there is a missing index on a critical FK field on a
frequently used table. I think your comment about doing table scans is
probably correct. I'm going to add an index tonight and see if the problem
goes away.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD017B.40400@.realsqlguy.com...
> David wrote:
>> I'm not sure if this helps, but when I go into EM and look at the
>> Locks/process ID and click on a couple of the spid records, I see the
>> following entry in the objects list on a couple of the spid records:
>> Object = tempdb.dbo.##lockinfo67
>> Lock Type = TAB
>> Mode = X
>> Status = GRANT
>> Owner = Xact
>> Index = ##lockinfo67
>> Not sure if this helps.
> Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the problem
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the problem
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com
it so attempts to insert records on it seems to time out frequently. Any
ideas on what I might look for to keep this from happening? The table has
only a unique int for a PK and no other indexes. Thanks.David wrote:
> For some reason, one table in our database (SQL 2000) keeps getting locks on
> it so attempts to insert records on it seems to time out frequently. Any
> ideas on what I might look for to keep this from happening? The table has
> only a unique int for a PK and no other indexes. Thanks.
>
When the locking occurs, look at sp_lock, sp_who2, determine what spid
is locking the table. Then use DBCC INPUTBUFFER to see what that spid
is doing. My guess is that something is table-scanning the table, maybe
a sign that additional indexes are needed.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I'm not sure if this helps, but when I go into EM and look at the
Locks/process ID and click on a couple of the spid records, I see the
following entry in the objects list on a couple of the spid records:
Object = tempdb.dbo.##lockinfo67
Lock Type = TAB
Mode = X
Status = GRANT
Owner = Xact
Index = ##lockinfo67
Not sure if this helps.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45ACFD6F.3060409@.realsqlguy.com...
> David wrote:
>> For some reason, one table in our database (SQL 2000) keeps getting locks
>> on it so attempts to insert records on it seems to time out frequently.
>> Any ideas on what I might look for to keep this from happening? The
>> table has only a unique int for a PK and no other indexes. Thanks.
> When the locking occurs, look at sp_lock, sp_who2, determine what spid is
> locking the table. Then use DBCC INPUTBUFFER to see what that spid is
> doing. My guess is that something is table-scanning the table, maybe a
> sign that additional indexes are needed.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David wrote:
> I'm not sure if this helps, but when I go into EM and look at the
> Locks/process ID and click on a couple of the spid records, I see the
> following entry in the objects list on a couple of the spid records:
> Object = tempdb.dbo.##lockinfo67
> Lock Type = TAB
> Mode = X
> Status = GRANT
> Owner = Xact
> Index = ##lockinfo67
> Not sure if this helps.
>
Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||OK. It looks like there is a missing index on a critical FK field on a
frequently used table. I think your comment about doing table scans is
probably correct. I'm going to add an index tonight and see if the problem
goes away.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD017B.40400@.realsqlguy.com...
> David wrote:
>> I'm not sure if this helps, but when I go into EM and look at the
>> Locks/process ID and click on a couple of the spid records, I see the
>> following entry in the objects list on a couple of the spid records:
>> Object = tempdb.dbo.##lockinfo67
>> Lock Type = TAB
>> Mode = X
>> Status = GRANT
>> Owner = Xact
>> Index = ##lockinfo67
>> Not sure if this helps.
> Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the problem
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the problem
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Locks on a table
For some reason, one table in our database (SQL 2000) keeps getting locks on
it so attempts to insert records on it seems to time out frequently. Any
ideas on what I might look for to keep this from happening? The table has
only a unique int for a PK and no other indexes. Thanks.David wrote:
> For some reason, one table in our database (SQL 2000) keeps getting locks
on
> it so attempts to insert records on it seems to time out frequently. Any
> ideas on what I might look for to keep this from happening? The table has
> only a unique int for a PK and no other indexes. Thanks.
>
When the locking occurs, look at sp_lock, sp_who2, determine what spid
is locking the table. Then use DBCC INPUTBUFFER to see what that spid
is doing. My guess is that something is table-scanning the table, maybe
a sign that additional indexes are needed.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I'm not sure if this helps, but when I go into EM and look at the
Locks/process ID and click on a couple of the spid records, I see the
following entry in the objects list on a couple of the spid records:
Object = tempdb.dbo.##lockinfo67
Lock Type = TAB
Mode = X
Status = GRANT
Owner = Xact
Index = ##lockinfo67
Not sure if this helps.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45ACFD6F.3060409@.realsqlguy.com...
> David wrote:
> When the locking occurs, look at sp_lock, sp_who2, determine what spid is
> locking the table. Then use DBCC INPUTBUFFER to see what that spid is
> doing. My guess is that something is table-scanning the table, maybe a
> sign that additional indexes are needed.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David wrote:
> I'm not sure if this helps, but when I go into EM and look at the
> Locks/process ID and click on a couple of the spid records, I see the
> following entry in the objects list on a couple of the spid records:
> Object = tempdb.dbo.##lockinfo67
> Lock Type = TAB
> Mode = X
> Status = GRANT
> Owner = Xact
> Index = ##lockinfo67
> Not sure if this helps.
>
Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||OK. It looks like there is a missing index on a critical FK field on a
frequently used table. I think your comment about doing table scans is
probably correct. I'm going to add an index tonight and see if the problem
goes away.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD017B.40400@.realsqlguy.com...
> David wrote:
> Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the proble
m
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the proble
m
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com
it so attempts to insert records on it seems to time out frequently. Any
ideas on what I might look for to keep this from happening? The table has
only a unique int for a PK and no other indexes. Thanks.David wrote:
> For some reason, one table in our database (SQL 2000) keeps getting locks
on
> it so attempts to insert records on it seems to time out frequently. Any
> ideas on what I might look for to keep this from happening? The table has
> only a unique int for a PK and no other indexes. Thanks.
>
When the locking occurs, look at sp_lock, sp_who2, determine what spid
is locking the table. Then use DBCC INPUTBUFFER to see what that spid
is doing. My guess is that something is table-scanning the table, maybe
a sign that additional indexes are needed.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I'm not sure if this helps, but when I go into EM and look at the
Locks/process ID and click on a couple of the spid records, I see the
following entry in the objects list on a couple of the spid records:
Object = tempdb.dbo.##lockinfo67
Lock Type = TAB
Mode = X
Status = GRANT
Owner = Xact
Index = ##lockinfo67
Not sure if this helps.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45ACFD6F.3060409@.realsqlguy.com...
> David wrote:
> When the locking occurs, look at sp_lock, sp_who2, determine what spid is
> locking the table. Then use DBCC INPUTBUFFER to see what that spid is
> doing. My guess is that something is table-scanning the table, maybe a
> sign that additional indexes are needed.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David wrote:
> I'm not sure if this helps, but when I go into EM and look at the
> Locks/process ID and click on a couple of the spid records, I see the
> following entry in the objects list on a couple of the spid records:
> Object = tempdb.dbo.##lockinfo67
> Lock Type = TAB
> Mode = X
> Status = GRANT
> Owner = Xact
> Index = ##lockinfo67
> Not sure if this helps.
>
Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||OK. It looks like there is a missing index on a critical FK field on a
frequently used table. I think your comment about doing table scans is
probably correct. I'm going to add an index tonight and see if the problem
goes away.
David
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AD017B.40400@.realsqlguy.com...
> David wrote:
> Forget the GUI - go to Query Analyzer and run the stuff that I mentioned.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the proble
m
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||David wrote:
> OK. It looks like there is a missing index on a critical FK field on a
> frequently used table. I think your comment about doing table scans is
> probably correct. I'm going to add an index tonight and see if the proble
m
> goes away.
>
That'll do it!
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Subscribe to:
Posts (Atom)