sql local data: bol

Showing posts with label bol. Show all posts

Wednesday, March 28, 2012

Lock Timeout Resource

Using sql 2000 profiler I traced the event lock timout. In the binary field I get a value
0x000705003B468D2402000D00F5A8D578
BOL says this is the resource type. How or where do I go to get an english translation to the binary recordThis is a multi-part message in MIME format.
--=_NextPart_000_0113_01C443EA.7DB46880
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
Is this a KEY resource? If so, this value is a hash value based on the =index key, and cannot be translated. You could potentially get more =information by looking at the ObjectID or IndexID columns. What is the =duration of the lock timeout? You may see durations of 0 for this =event, which are internal timeouts that are not anything to worry about. =
If you are concerned about blocking, see if =http://support.microsoft.com/?id=3D271509 and =http://support.microsoft.com/?id=3D224453 help you out any.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Eugene" <anonymous@.discussions.microsoft.com> wrote in message =news:46470501-4BF8-47B1-A71E-631F135197AE@.microsoft.com...
Using sql 2000 profiler I traced the event lock timout. In the binary =field I get a value,
0x000705003B468D2402000D00F5A8D578,
BOL says this is the resource type. How or where do I go to get an =english translation to the binary record?
--=_NextPart_000_0113_01C443EA.7DB46880
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Is this a KEY resource? =If so, this value is a hash value based on the index key, and cannot be translated. You could potentially get more information by looking =at the ObjectID or IndexID columns. What is the duration of the lock timeout? You may see durations of 0 for this event, which are =internal timeouts that are not anything to worry about.
If you are concerned about blocking, see if http://support.microsoft.com/?id=3D271509">http://support.microso=ft.com/?id=3D271509 and http://support.microsoft.com/?id=3D224453">http://support.microso=ft.com/?id=3D224453 help you out any.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Eugene" wrote in message news:464=70501-4BF8-47B1-A71E-631F135197AE@.microsoft.com...Using sql 2000 profiler I traced the event lock timout. In the binary =field I get a value,0x000705003B468D2402000D00F5A8D578,BOL says this =is the resource type. How or where do I go to get an english translation to =the binary record?

--=_NextPart_000_0113_01C443EA.7DB46880--

Lock Timeout Resource

Using sql 2000 profiler I traced the event lock timout. In the binary field
I get a value,
0x000705003B468D2402000D00F5A8D578,
BOL says this is the resource type. How or where do I go to get an english t
ranslation to the binary record?Is this a KEY resource? If so, this value is a hash value based on the inde
x key, and cannot be translated. You could potentially get more information
by looking at the ObjectID or IndexID columns. What is the duration of the
lock timeout? You may see durations of 0 for this event, which are interna
l timeouts that are not anything to worry about.
If you are concerned about blocking, see if http://support.microsoft.com/?id=271509 and
http://support.microsoft.com/?id=224453 help you out any.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Eugene" <anonymous@.discussions.microsoft.com> wrote in message news:4647050
1-4BF8-47B1-A71E-631F135197AE@.microsoft.com...
Using sql 2000 profiler I traced the event lock timout. In the binary field
I get a value,
0x000705003B468D2402000D00F5A8D578,
BOL says this is the resource type. How or where do I go to get an english t
ranslation to the binary record?

Lock Timeout Resource

Using sql 2000 profiler I traced the event lock timout. In the binary field I get a value,
0x000705003B468D2402000D00F5A8D578,
BOL says this is the resource type. How or where do I go to get an english translation to the binary record?
Is this a KEY resource? If so, this value is a hash value based on the index key, and cannot be translated. You could potentially get more information by looking at the ObjectID or IndexID columns. What is the duration of the lock timeout? You may see durations of 0 for this event, which are internal timeouts that are not anything to worry about.
If you are concerned about blocking, see if http://support.microsoft.com/?id=271509 and http://support.microsoft.com/?id=224453 help you out any.
Thanks,
Ryan Stonecipher
Microsoft SQL Server Storage Engine
"Eugene" <anonymous@.discussions.microsoft.com> wrote in message news:46470501-4BF8-47B1-A71E-631F135197AE@.microsoft.com...
Using sql 2000 profiler I traced the event lock timout. In the binary field I get a value,
0x000705003B468D2402000D00F5A8D578,
BOL says this is the resource type. How or where do I go to get an english translation to the binary record?
sql

Monday, March 26, 2012

Lock Pages in Memory on x64 Standard

All,

I have a new database server running W2K3/MSSQL2K5 x64. I read in BOL that you can use the Lock Pages in Memory option to improve performance on your database server. I did some research on the internet and some sources are stating that you can only use that option with MSSQL2K5 Enterprise but in BOL they state that you can use this option in Standard/Enterprise.

Can anyone confirm that "Lock Pages in Memory on x64 Standard" works in MSSQL2K5 Standard?

Thanks in advance for the help.As far as I understand this, it is available on 32-bit systems with AWE and on 64-bit systems. The three versions supporting this feature is thus Standard, Enterprise and Developer.

Friday, March 23, 2012

LOCK confusion

I'm reading Delaney's Inside SQL 2000 book & BOL right now and getting
more confused....my question deals with Locking. I have a simple one
column table with 20000 records or so. I want to in this order:
A) restrict/halt all insert/update activity on this table (the only
inserts that come to this table are from a trigger on another table)
B) move the contents over to another table
C) delete the contents
D) Open the table back up for the application to use
B & C are very basic & I'm fine with INSERT, DELETE, etc. I've never
used the locking functionality, however. What locking services does
SQL automatically take care of, and what will I have to specify in my
example?This is a multi-part message in MIME format.
--030108070508030207050909
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Well, with your fairly simple example you can solve the problem simply
by doing all the operations in an explicit transaction and specifying a
locking hint or two.
The HOLDLOCK locking hint will essentially put the session into
SERIALIZABLE isolation level, which means locks are held until the end
of the transaction. So if you also specify a TABLOCKX hint, then the
whole table will be locked with an exclusive lock, meaning no other SQL
process (SPID) will be able to acquire any kind of lock on any part of
that table until the transaction has been committed (or rolled back).
So the batch would be something like:
BEGIN TRAN
INSERT INTO MyArchiveTable (col1, col2, ...)
SELECT col1, col2, ... FROM MyTable *WITH (HOLDLOCK, TABLOCKX)*
DELETE MyTable
COMMIT TRAN
In fact, you probably wouldn't even need a TABLOCKX, a TABLOCK would
probably do because INSERTs & UPDATEs on the table will require an IX
lock on the table resource and an IX lock is incompatible with the S
lock on the table, so it will wait until the S lock has been released,
which will be when you commit the transaction.
(I hope this is right - my reference to all this (Inside SQL Server
2000) is sitting at home at the moment, so this is from memory.)
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
unc27932@.yahoo.com wrote:
>I'm reading Delaney's Inside SQL 2000 book & BOL right now and getting
>more confused....my question deals with Locking. I have a simple one
>column table with 20000 records or so. I want to in this order:
>A) restrict/halt all insert/update activity on this table (the only
>inserts that come to this table are from a trigger on another table)
>B) move the contents over to another table
>C) delete the contents
>D) Open the table back up for the application to use
>B & C are very basic & I'm fine with INSERT, DELETE, etc. I've never
>used the locking functionality, however. What locking services does
>SQL automatically take care of, and what will I have to specify in my
>example?
>
>
--030108070508030207050909
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Well, with your fairly simple example you can solve the problem
simply by doing all the operations in an explicit transaction and
specifying a locking hint or two. 
 
The HOLDLOCK locking hint will essentially put the session into
SERIALIZABLE isolation level, which means locks are held until the end
of the transaction. So if you also specify a TABLOCKX hint, then the
whole table will be locked with an exclusive lock, meaning no other SQL
process (SPID) will be able to acquire any kind of lock on any part of
that table until the transaction has been committed (or rolled back). 
 
So the batch would be something like: 
 
BEGIN TRAN 
 
INSERT INTO MyArchiveTable (col1, col2, ...) 
SELECT col1, col2, ... FROM MyTable WITH (HOLDLOCK, TABLOCKX) 
 
DELETE MyTable 
 
COMMIT TRAN 
 
In fact, you probably wouldn't even need a TABLOCKX, a TABLOCK would
probably do because INSERTs & UPDATEs on the table will require an
IX lock on the table resource and an IX lock is incompatible with the S
lock on the table, so it will wait until the S lock has been released,
which will be when you commit the transaction. 
 
(I hope this is right - my reference to all this (Inside SQL Server
2000) is sitting at home at the moment, so this is from memory.) 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
blog: <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:unc27932@.yahoo.com">unc27932@.yahoo.com</a> wrote:
<blockquote
cite="mid1123014090.456062.301480@.f14g2000cwb.googlegroups.com"
type="cite">
<pre wrap="">I'm reading Delaney's Inside SQL 2000 book & BOL right now and getting
more confused....my question deals with Locking. I have a simple one
column table with 20000 records or so. I want to in this order:
A) restrict/halt all insert/update activity on this table (the only
inserts that come to this table are from a trigger on another table)
B) move the contents over to another table
C) delete the contents
D) Open the table back up for the application to use
B & C are very basic & I'm fine with INSERT, DELETE, etc. I've never
used the locking functionality, however. What locking services does
SQL automatically take care of, and what will I have to specify in my
example?
</pre>
</blockquote>
</body>
</html>
--030108070508030207050909--|||I guess I'm not getting two things here...
A) the difference between TABLOCK and TABLOCKX. Does TABLOCK allow
reads on the table, and just not insert/updates? And TABLOCKX allow
nothing at all?
B) HOLDLOCK & its purpose. If I specify TABLOCK or TABLOCKX as a hint,
why is HOLDLOCK necessary?
Mike Hodgson wrote:
> Well, with your fairly simple example you can solve the problem simply
> by doing all the operations in an explicit transaction and specifying a
> locking hint or two.
> The HOLDLOCK locking hint will essentially put the session into
> SERIALIZABLE isolation level, which means locks are held until the end
> of the transaction. So if you also specify a TABLOCKX hint, then the
> whole table will be locked with an exclusive lock, meaning no other SQL
> process (SPID) will be able to acquire any kind of lock on any part of
> that table until the transaction has been committed (or rolled back).
> So the batch would be something like:
> BEGIN TRAN
> INSERT INTO MyArchiveTable (col1, col2, ...)
> SELECT col1, col2, ... FROM MyTable *WITH (HOLDLOCK, TABLOCKX)*
> DELETE MyTable
> COMMIT TRAN
> In fact, you probably wouldn't even need a TABLOCKX, a TABLOCK would
> probably do because INSERTs & UPDATEs on the table will require an IX
> lock on the table resource and an IX lock is incompatible with the S
> lock on the table, so it will wait until the S lock has been released,
> which will be when you commit the transaction.
> (I hope this is right - my reference to all this (Inside SQL Server
> 2000) is sitting at home at the moment, so this is from memory.)
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> unc27932@.yahoo.com wrote:
> >I'm reading Delaney's Inside SQL 2000 book & BOL right now and getting
> >more confused....my question deals with Locking. I have a simple one
> >column table with 20000 records or so. I want to in this order:
> >
> >A) restrict/halt all insert/update activity on this table (the only
> >inserts that come to this table are from a trigger on another table)
> >B) move the contents over to another table
> >C) delete the contents
> >D) Open the table back up for the application to use
> >
> >B & C are very basic & I'm fine with INSERT, DELETE, etc. I've never
> >used the locking functionality, however. What locking services does
> >SQL automatically take care of, and what will I have to specify in my
> >example?
> >
> >
> >|||OK - I think I have it. TABLOCK will allow others to read the table ,
but not add any more records. Whereas TABLOCKX won't allow anyone else
to even read the table, let alone, update/insert.
And HOLDLOCK is used to hold the shared lock through the end of the
transaction, not just the data operation/read/whatever.
Is this right? Opinions?
So what happens if a user attempts to insert a row at the moment I have
it locked? Does their application just wait a few seconds, and then
SQL lets them back into the table after the lock is released? Or will
they get an error?|||Yes that is right. If you have a lock on the table and someone attempts to
insert a new row (or delete, update etc) they will be blocked (halted state)
until one of the following occurs. Either their connection times out or the
lock gets released. The timeout is dependant on the connection settings as
to how long it waits.
--
Andrew J. Kelly SQL MVP
<unc27932@.yahoo.com> wrote in message
news:1123087028.818016.284860@.o13g2000cwo.googlegroups.com...
> OK - I think I have it. TABLOCK will allow others to read the table ,
> but not add any more records. Whereas TABLOCKX won't allow anyone else
> to even read the table, let alone, update/insert.
> And HOLDLOCK is used to hold the shared lock through the end of the
> transaction, not just the data operation/read/whatever.
> Is this right? Opinions?
> So what happens if a user attempts to insert a row at the moment I have
> it locked? Does their application just wait a few seconds, and then
> SQL lets them back into the table after the lock is released? Or will
> they get an error?
>sql

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

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

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

Wednesday, March 28, 2012

Lock Timeout Resource

Lock Timeout Resource

Lock Timeout Resource

Monday, March 26, 2012

Lock Pages in Memory on x64 Standard

Friday, March 23, 2012

LOCK confusion

LOCK confusion

LOCK confusion

Lock being acquired in DBCC SHOWCONTIG and sys.dm_db_index_physica

Lock being acquired in DBCC SHOWCONTIG and sys.dm_db_index_physica

Lock being acquired in DBCC SHOWCONTIG and sys.dm_db_index_physica

sql local data

Blog Archive

About Me