I have set the Isolation Level to Read Uncommitted but the tables are still
locked after i have run a select in Query Analyzer, can anyone explain why
this is 'Can you provide more details? Hoe did you set the isolation level. Can you reproduce this from two
connection in query analyzer?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eGpQzej$EHA.2572@.tk2msftngp13.phx.gbl...
>I have set the Isolation Level to Read Uncommitted but the tables are still
> locked after i have run a select in Query Analyzer, can anyone explain why
> this is '
>|||All i did was run this
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
begin transaction
select * from gipsilicence
commit transaction
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||That doesn't help us reproduce the problem as this is only one connection, and I assume that the one
connection doesn't block itself. Are you saying that this connection is blocking another connection,
or is it blocked by another connection? NOLOCK should not acquire shared locks and should not honor
exclusive locks, hence I'm asking as much details as possible, preferably a repro.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eqoQm3j$EHA.2136@.TK2MSFTNGP10.phx.gbl...
> All i did was run this
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> go
> begin transaction
> select * from gipsilicence
> commit transaction
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!sql
Showing posts with label explain. Show all posts
Showing posts with label explain. Show all posts
Friday, March 30, 2012
Locking
I have set the Isolation Level to Read Uncommitted but the tables are still
locked after i have run a select in Query Analyzer, can anyone explain why
this is ?
Can you provide more details? Hoe did you set the isolation level. Can you reproduce this from two
connection in query analyzer?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eGpQzej$EHA.2572@.tk2msftngp13.phx.gbl...
>I have set the Isolation Level to Read Uncommitted but the tables are still
> locked after i have run a select in Query Analyzer, can anyone explain why
> this is ?
>
|||All i did was run this
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
begin transaction
select * from gipsilicence
commit transaction
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||That doesn't help us reproduce the problem as this is only one connection, and I assume that the one
connection doesn't block itself. Are you saying that this connection is blocking another connection,
or is it blocked by another connection? NOLOCK should not acquire shared locks and should not honor
exclusive locks, hence I'm asking as much details as possible, preferably a repro.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eqoQm3j$EHA.2136@.TK2MSFTNGP10.phx.gbl...
> All i did was run this
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> go
> begin transaction
> select * from gipsilicence
> commit transaction
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
locked after i have run a select in Query Analyzer, can anyone explain why
this is ?
Can you provide more details? Hoe did you set the isolation level. Can you reproduce this from two
connection in query analyzer?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eGpQzej$EHA.2572@.tk2msftngp13.phx.gbl...
>I have set the Isolation Level to Read Uncommitted but the tables are still
> locked after i have run a select in Query Analyzer, can anyone explain why
> this is ?
>
|||All i did was run this
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
begin transaction
select * from gipsilicence
commit transaction
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||That doesn't help us reproduce the problem as this is only one connection, and I assume that the one
connection doesn't block itself. Are you saying that this connection is blocking another connection,
or is it blocked by another connection? NOLOCK should not acquire shared locks and should not honor
exclusive locks, hence I'm asking as much details as possible, preferably a repro.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eqoQm3j$EHA.2136@.TK2MSFTNGP10.phx.gbl...
> All i did was run this
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> go
> begin transaction
> select * from gipsilicence
> commit transaction
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
Lock_timeout
Hello there,
Is there any way to set a default lock time out for the server with out using the sentence SET LOCK_TIMEOUT 20 ?
Could you explain me step by step ?
Thanks !Please Check sp_configure locks execute this first check the locks space allocated, Increase the locks by executing sp_configure locks 100000 then execute the Store Procedure you will not get the problem.|||Please try with this procedure
Execute sp_configure locks
check the locks allocated
now increase the locks by exec
Execute sp_configure locks 100000
then execute your request you will not get the error|||Thanks !|||I want to eliminate automatically every lock after 10 seconds and I did this:
USE master
go
EXEC sp_configure 'locks', 10000
go
RECONFIGURE WITH OVERRIDE
go
Is this correct ? Do I have to restart sql server ?
Thanks|||If you want to make the locks "go away", why not just ignore them and deal with the consequences later? You can set the transaction isolation level down, and the server will just ignore the locks held by other processes.
This is very dangerous, but it is less dangerous than simply trying to break the existing locks since it only puts your process at risk instead of the whole server.
-PatP|||I want to eliminate automatically every lock after 10 seconds and I did this:
USE master
go
EXEC sp_configure 'locks', 10000
go
RECONFIGURE WITH OVERRIDE
go
Is this correct ? Do I have to restart sql server ?
Thanks
Yes, Exactly
Is there any way to set a default lock time out for the server with out using the sentence SET LOCK_TIMEOUT 20 ?
Could you explain me step by step ?
Thanks !Please Check sp_configure locks execute this first check the locks space allocated, Increase the locks by executing sp_configure locks 100000 then execute the Store Procedure you will not get the problem.|||Please try with this procedure
Execute sp_configure locks
check the locks allocated
now increase the locks by exec
Execute sp_configure locks 100000
then execute your request you will not get the error|||Thanks !|||I want to eliminate automatically every lock after 10 seconds and I did this:
USE master
go
EXEC sp_configure 'locks', 10000
go
RECONFIGURE WITH OVERRIDE
go
Is this correct ? Do I have to restart sql server ?
Thanks|||If you want to make the locks "go away", why not just ignore them and deal with the consequences later? You can set the transaction isolation level down, and the server will just ignore the locks held by other processes.
This is very dangerous, but it is less dangerous than simply trying to break the existing locks since it only puts your process at risk instead of the whole server.
-PatP|||I want to eliminate automatically every lock after 10 seconds and I did this:
USE master
go
EXEC sp_configure 'locks', 10000
go
RECONFIGURE WITH OVERRIDE
go
Is this correct ? Do I have to restart sql server ?
Thanks
Yes, Exactly
Friday, March 23, 2012
Lock being acquired in DBCC SHOWCONTIG and sys.dm_db_index_physica
Hi,
Can anyone explain below why the different explanation in terms of S and IS
lock on the table? Or simply an errata in the 2005 BOL or the best practice?
· From SQL 2005 BOL,
Scanning Modes
The mode in which the function is executed determines the level of scanning
performed to obtain the statistical data that is used by the function. mode
is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
page chains for the allocation units that make up the specified partitions of
the table or index. Unlike DBCC SHOWCONTIG that generally requires a shared
(S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Shared
(IS) table lock, regardless of the mode that it runs in. For more information
about locking, see Lock Modes.
· From
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dbcc_showcontig_improvements.mspx
This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowing
concurrent DML operations.
My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You might want to do a BOL
feedback for this (the link at the bottom of the article).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bill k." <billk@.discussions.microsoft.com> wrote in message
news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
> Hi,
>
> Can anyone explain below why the different explanation in terms of S and IS
> lock on the table? Or simply an errata in the 2005 BOL or the best practice?
>
> · From SQL 2005 BOL,
>
> Scanning Modes
> The mode in which the function is executed determines the level of scanning
> performed to obtain the statistical data that is used by the function. mode
> is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
> page chains for the allocation units that make up the specified partitions of
> the table or index. Unlike DBCC SHOWCONTIG that generally requires a shared
> (S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Shared
> (IS) table lock, regardless of the mode that it runs in. For more information
> about locking, see Lock Modes.
> · From
> http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dbcc_showcontig_improvements.mspx
>
> This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
> usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowing
> concurrent DML operations.
>
>
|||> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
Yes, please do send feedback for the sys.dm_db_index_physical_stats so that
we can get this inaccurate statement corrected.
Thanks,
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HRyInIjHHA.4596@.TK2MSFTNGP05.phx.gbl...
> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bill k." <billk@.discussions.microsoft.com> wrote in message
> news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
>
Can anyone explain below why the different explanation in terms of S and IS
lock on the table? Or simply an errata in the 2005 BOL or the best practice?
· From SQL 2005 BOL,
Scanning Modes
The mode in which the function is executed determines the level of scanning
performed to obtain the statistical data that is used by the function. mode
is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
page chains for the allocation units that make up the specified partitions of
the table or index. Unlike DBCC SHOWCONTIG that generally requires a shared
(S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Shared
(IS) table lock, regardless of the mode that it runs in. For more information
about locking, see Lock Modes.
· From
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dbcc_showcontig_improvements.mspx
This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowing
concurrent DML operations.
My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You might want to do a BOL
feedback for this (the link at the bottom of the article).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bill k." <billk@.discussions.microsoft.com> wrote in message
news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
> Hi,
>
> Can anyone explain below why the different explanation in terms of S and IS
> lock on the table? Or simply an errata in the 2005 BOL or the best practice?
>
> · From SQL 2005 BOL,
>
> Scanning Modes
> The mode in which the function is executed determines the level of scanning
> performed to obtain the statistical data that is used by the function. mode
> is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
> page chains for the allocation units that make up the specified partitions of
> the table or index. Unlike DBCC SHOWCONTIG that generally requires a shared
> (S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Shared
> (IS) table lock, regardless of the mode that it runs in. For more information
> about locking, see Lock Modes.
> · From
> http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dbcc_showcontig_improvements.mspx
>
> This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
> usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowing
> concurrent DML operations.
>
>
|||> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
Yes, please do send feedback for the sys.dm_db_index_physical_stats so that
we can get this inaccurate statement corrected.
Thanks,
Gail
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HRyInIjHHA.4596@.TK2MSFTNGP05.phx.gbl...
> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bill k." <billk@.discussions.microsoft.com> wrote in message
> news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
>
Lock being acquired in DBCC SHOWCONTIG and sys.dm_db_index_physica
Hi,
Can anyone explain below why the different explanation in terms of S and IS
lock on the table? Or simply an errata in the 2005 BOL or the best practice?
· From SQL 2005 BOL,
Scanning Modes
The mode in which the function is executed determines the level of scanning
performed to obtain the statistical data that is used by the function. mode
is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
page chains for the allocation units that make up the specified partitions of
the table or index. Unlike DBCC SHOWCONTIG that generally requires a shared
(S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Shared
(IS) table lock, regardless of the mode that it runs in. For more information
about locking, see Lock Modes.
· From
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dbcc_showcontig_improvements.mspx
This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowing
concurrent DML operations.My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You might want to do a BOL
feedback for this (the link at the bottom of the article).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bill k." <billk@.discussions.microsoft.com> wrote in message
news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
> Hi,
>
> Can anyone explain below why the different explanation in terms of S and IS
> lock on the table? Or simply an errata in the 2005 BOL or the best practice?
>
> · From SQL 2005 BOL,
>
> Scanning Modes
> The mode in which the function is executed determines the level of scanning
> performed to obtain the statistical data that is used by the function. mode
> is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
> page chains for the allocation units that make up the specified partitions of
> the table or index. Unlike DBCC SHOWCONTIG that generally requires a shared
> (S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Shared
> (IS) table lock, regardless of the mode that it runs in. For more information
> about locking, see Lock Modes.
> · From
> http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dbcc_showcontig_improvements.mspx
>
> This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
> usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowing
> concurrent DML operations.
>
>|||> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
Yes, please do send feedback for the sys.dm_db_index_physical_stats so that
we can get this inaccurate statement corrected.
Thanks,
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HRyInIjHHA.4596@.TK2MSFTNGP05.phx.gbl...
> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bill k." <billk@.discussions.microsoft.com> wrote in message
> news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
>> Hi,
>>
>> Can anyone explain below why the different explanation in terms of S and
>> IS
>> lock on the table? Or simply an errata in the 2005 BOL or the best
>> practice?
>>
>> · From SQL 2005 BOL,
>>
>> Scanning Modes
>> The mode in which the function is executed determines the level of
>> scanning
>> performed to obtain the statistical data that is used by the function.
>> mode
>> is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
>> page chains for the allocation units that make up the specified
>> partitions of
>> the table or index. Unlike DBCC SHOWCONTIG that generally requires a
>> shared
>> (S) table lock, sys.dm_db_index_physical_stats requires only an
>> Intent-Shared
>> (IS) table lock, regardless of the mode that it runs in. For more
>> information
>> about locking, see Lock Modes.
>> · From
>> http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dbcc_showcontig_improvements.mspx
>>
>> This problem has been resolved in SQL Server 2005. In SQL Server 2005,
>> all
>> usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby
>> allowing
>> concurrent DML operations.
>>
>
Can anyone explain below why the different explanation in terms of S and IS
lock on the table? Or simply an errata in the 2005 BOL or the best practice?
· From SQL 2005 BOL,
Scanning Modes
The mode in which the function is executed determines the level of scanning
performed to obtain the statistical data that is used by the function. mode
is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
page chains for the allocation units that make up the specified partitions of
the table or index. Unlike DBCC SHOWCONTIG that generally requires a shared
(S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Shared
(IS) table lock, regardless of the mode that it runs in. For more information
about locking, see Lock Modes.
· From
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dbcc_showcontig_improvements.mspx
This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowing
concurrent DML operations.My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You might want to do a BOL
feedback for this (the link at the bottom of the article).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bill k." <billk@.discussions.microsoft.com> wrote in message
news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
> Hi,
>
> Can anyone explain below why the different explanation in terms of S and IS
> lock on the table? Or simply an errata in the 2005 BOL or the best practice?
>
> · From SQL 2005 BOL,
>
> Scanning Modes
> The mode in which the function is executed determines the level of scanning
> performed to obtain the statistical data that is used by the function. mode
> is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
> page chains for the allocation units that make up the specified partitions of
> the table or index. Unlike DBCC SHOWCONTIG that generally requires a shared
> (S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Shared
> (IS) table lock, regardless of the mode that it runs in. For more information
> about locking, see Lock Modes.
> · From
> http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dbcc_showcontig_improvements.mspx
>
> This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
> usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowing
> concurrent DML operations.
>
>|||> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
Yes, please do send feedback for the sys.dm_db_index_physical_stats so that
we can get this inaccurate statement corrected.
Thanks,
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HRyInIjHHA.4596@.TK2MSFTNGP05.phx.gbl...
> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bill k." <billk@.discussions.microsoft.com> wrote in message
> news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
>> Hi,
>>
>> Can anyone explain below why the different explanation in terms of S and
>> IS
>> lock on the table? Or simply an errata in the 2005 BOL or the best
>> practice?
>>
>> · From SQL 2005 BOL,
>>
>> Scanning Modes
>> The mode in which the function is executed determines the level of
>> scanning
>> performed to obtain the statistical data that is used by the function.
>> mode
>> is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
>> page chains for the allocation units that make up the specified
>> partitions of
>> the table or index. Unlike DBCC SHOWCONTIG that generally requires a
>> shared
>> (S) table lock, sys.dm_db_index_physical_stats requires only an
>> Intent-Shared
>> (IS) table lock, regardless of the mode that it runs in. For more
>> information
>> about locking, see Lock Modes.
>> · From
>> http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/dbcc_showcontig_improvements.mspx
>>
>> This problem has been resolved in SQL Server 2005. In SQL Server 2005,
>> all
>> usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby
>> allowing
>> concurrent DML operations.
>>
>
Lock being acquired in DBCC SHOWCONTIG and sys.dm_db_index_physica
Hi,
Can anyone explain below why the different explanation in terms of S and IS
lock on the table? Or simply an errata in the 2005 BOL or the best practice?
· From SQL 2005 BOL,
Scanning Modes
The mode in which the function is executed determines the level of scanning
performed to obtain the statistical data that is used by the function. mode
is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
page chains for the allocation units that make up the specified partitions o
f
the table or index. Unlike DBCC SHOWCONTIG that generally requires a shared
(S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Share
d
(IS) table lock, regardless of the mode that it runs in. For more informatio
n
about locking, see Lock Modes.
· From
http://www.microsoft.com/technet/pr...
ovements.mspx
This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowing
concurrent DML operations.My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You m
ight want to do a BOL
feedback for this (the link at the bottom of the article).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bill k." <billk@.discussions.microsoft.com> wrote in message
news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
> Hi,
>
> Can anyone explain below why the different explanation in terms of S and I
S
> lock on the table? Or simply an errata in the 2005 BOL or the best practic
e?
>
> · From SQL 2005 BOL,
>
> Scanning Modes
> The mode in which the function is executed determines the level of scannin
g
> performed to obtain the statistical data that is used by the function. mod
e
> is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
> page chains for the allocation units that make up the specified partitions
of
> the table or index. Unlike DBCC SHOWCONTIG that generally requires a share
d
> (S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Sha
red
> (IS) table lock, regardless of the mode that it runs in. For more informat
ion
> about locking, see Lock Modes.
> · From
> http://www.microsoft.com/technet/pr...provements.mspx
>
> This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
> usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowin
g
> concurrent DML operations.
>
>|||> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
Yes, please do send feedback for the sys.dm_db_index_physical_stats so that
we can get this inaccurate statement corrected.
Thanks,
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HRyInIjHHA.4596@.TK2MSFTNGP05.phx.gbl...
> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bill k." <billk@.discussions.microsoft.com> wrote in message
> news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
>sql
Can anyone explain below why the different explanation in terms of S and IS
lock on the table? Or simply an errata in the 2005 BOL or the best practice?
· From SQL 2005 BOL,
Scanning Modes
The mode in which the function is executed determines the level of scanning
performed to obtain the statistical data that is used by the function. mode
is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
page chains for the allocation units that make up the specified partitions o
f
the table or index. Unlike DBCC SHOWCONTIG that generally requires a shared
(S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Share
d
(IS) table lock, regardless of the mode that it runs in. For more informatio
n
about locking, see Lock Modes.
· From
http://www.microsoft.com/technet/pr...
ovements.mspx
This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowing
concurrent DML operations.My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You m
ight want to do a BOL
feedback for this (the link at the bottom of the article).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"bill k." <billk@.discussions.microsoft.com> wrote in message
news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
> Hi,
>
> Can anyone explain below why the different explanation in terms of S and I
S
> lock on the table? Or simply an errata in the 2005 BOL or the best practic
e?
>
> · From SQL 2005 BOL,
>
> Scanning Modes
> The mode in which the function is executed determines the level of scannin
g
> performed to obtain the statistical data that is used by the function. mod
e
> is specified as LIMITED, SAMPLED, or DETAILED. The function traverses the
> page chains for the allocation units that make up the specified partitions
of
> the table or index. Unlike DBCC SHOWCONTIG that generally requires a share
d
> (S) table lock, sys.dm_db_index_physical_stats requires only an Intent-Sha
red
> (IS) table lock, regardless of the mode that it runs in. For more informat
ion
> about locking, see Lock Modes.
> · From
> http://www.microsoft.com/technet/pr...provements.mspx
>
> This problem has been resolved in SQL Server 2005. In SQL Server 2005, all
> usages of DBCC SHOWCONTIG acquire an IS lock on the table, thereby allowin
g
> concurrent DML operations.
>
>|||> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
Yes, please do send feedback for the sys.dm_db_index_physical_stats so that
we can get this inaccurate statement corrected.
Thanks,
Gail
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23HRyInIjHHA.4596@.TK2MSFTNGP05.phx.gbl...
> My guess is that the BOL writer compared to DBCC SHOWCONTIG *in 2000*. You
> might want to do a BOL feedback for this (the link at the bottom of the
> article).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "bill k." <billk@.discussions.microsoft.com> wrote in message
> news:F3C6FB0A-03D4-4267-9F54-0E38F2673C88@.microsoft.com...
>sql
Subscribe to:
Posts (Atom)