locking

How does SS2000 lock by default for a select, update and delete statement.
Is it row level, page, table?
Thanks,
DanIt depends. Default is row level, but optimizer may escalate it to page
or table level locking if it see fit. You can force it to stay at row
level by using query hints. Check query hints in BOL
Eric Li
SQL DBA
MCDBA
Dan wrote:

> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||Eric is correct but I want to make one slight comment. SQL Server never
escalates to a page level lock from a row level. All escalation is always
to table level if it occurs. But it can choose to take out a page or table
level lock in the first place.
Andrew J. Kelly
SQL Server MVP
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:OMU0VgBSEHA.1392@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> It depends. Default is row level, but optimizer may escalate it to page
> or table level locking if it see fit. You can force it to stay at row
> level by using query hints. Check query hints in BOL
> --
> Eric Li
> SQL DBA
> MCDBA
> Dan wrote:
>
statement.[vbcol=seagreen]|||It appears to select the type of locking largely based on the number of
records you are processing. If you processing a large number of records,
taking out a page / table lock is more resource friendly than individual row
locks. Having said that, it's possible to force the use of a particular
locking mechanism with hints.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||If I also may add, SQL does not necessarily lock at the row level... One of
the recent improvements in the locking code for SQL is that the lock manager
takes into account how busy the object ( table for instance) has been
lately...
For instance, you are updating all of the rows in a table. If the table has
been very busy lately, SQL will choose a lower level lock ( page or row
level). This will cause your update to run more slowly ( because there will
be many more calls to the lock manager) , but overall concurrency will be
improved, since you will not be locking rows until right before you make the
update.
If the table has not been used lately, then you may get a whole table
lock... your update runs faster (due to the single lock manager call), and
no one else is hurt, since the table has not been used recently.
However in a busy multi user environment, lower level locks are most often
used...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||Thanks Eric, Andrew, Peter and Wayne. I understand it better now and I'll do
some more reading.
Dan
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:egQfZvJSEHA.1160@.TK2MSFTNGP09.phx.gbl...
> If I also may add, SQL does not necessarily lock at the row level... One
of
> the recent improvements in the locking code for SQL is that the lock
manager
> takes into account how busy the object ( table for instance) has been
> lately...
> For instance, you are updating all of the rows in a table. If the table
has
> been very busy lately, SQL will choose a lower level lock ( page or row
> level). This will cause your update to run more slowly ( because there
will
> be many more calls to the lock manager) , but overall concurrency will be
> improved, since you will not be locking rows until right before you make
the
> update.
> If the table has not been used lately, then you may get a whole table
> lock... your update runs faster (due to the single lock manager call), and
> no one else is hurt, since the table has not been used recently.
> However in a busy multi user environment, lower level locks are most often
> used...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
statement.[vbcol=seagreen]
>

Locking

It appears that when we issue an UPDATE statement on a table, it locks the
WHOLE table so that any other users can not complete SELECT queries on the
table (without using "WITH (nolock)" in the SELECT statement)
Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing an
update, so that maybe it takes longer, but it only locks 1 row or page at a
time?Mike
The optimiser escalates to a full table lock when it thinks it will be more
efficient way of processing the data than using page locks. There are a
number of things you can do if you think it should not be performing a table
lock.
Are your statistics up to date? If you don't regularly update statistics,
the optimiser is working with at least one hand tied behind it's back. If the
statistics are not up tp date it may be making incorrect choices.
Do you have suitable indexes? If you have indexes on the data you are
updating (especially a clustered index) it will be easier for the optimiser
to use page locks rather than table locks, if suitable.
It may be you are updating enough of the table that a table lock is the
correct option. In this case you can split up your updates into smaller
transactions, small enough that the optimiser uses page locks.
Hope this helps
John
"Mike" wrote:
> It appears that when we issue an UPDATE statement on a table, it locks the
> WHOLE table so that any other users can not complete SELECT queries on the
> table (without using "WITH (nolock)" in the SELECT statement)
> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing an
> update, so that maybe it takes longer, but it only locks 1 row or page at a
> time?
>
>|||Forgive my ignorance, but how do I get my statistics up to date? I am not
experienced in SQL Server administration.
I am using the pubs database in a new installation of SQL Server.
When I issue:
UPDATE authors set au_lname = 'a'
and then jump over to Enterprise Manager and right-click on "Current
Activity" under Management and select Refresh, then I go to Locks/Object and
pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
Lock Type "PAG".
If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
but less PAG locks.
(I inserted thousands more rows into authors than it comes with to give me
time to go to Ent. Mgr and select Refresh while the update is running)
There is a compound index on au_lname and au_fname.
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...
> Mike
> The optimiser escalates to a full table lock when it thinks it will be
> more
> efficient way of processing the data than using page locks. There are a
> number of things you can do if you think it should not be performing a
> table
> lock.
> Are your statistics up to date? If you don't regularly update statistics,
> the optimiser is working with at least one hand tied behind it's back. If
> the
> statistics are not up tp date it may be making incorrect choices.
> Do you have suitable indexes? If you have indexes on the data you are
> updating (especially a clustered index) it will be easier for the
> optimiser
> to use page locks rather than table locks, if suitable.
> It may be you are updating enough of the table that a table lock is the
> correct option. In this case you can split up your updates into smaller
> transactions, small enough that the optimiser uses page locks.
> Hope this helps
> John
> "Mike" wrote:
>> It appears that when we issue an UPDATE statement on a table, it locks
>> the
>> WHOLE table so that any other users can not complete SELECT queries on
>> the
>> table (without using "WITH (nolock)" in the SELECT statement)
>> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing
>> an
>> update, so that maybe it takes longer, but it only locks 1 row or page at
>> a
>> time?
>>|||I think you see the IX (intent exclusive) lock on the table.
That is not a real lock, it just tells the the engine that there is a execute
or update lock somewhere in the table.
Normally SQLServer will use row locks. The only way you get a real table
lock with this statement is if you have forbidden row and page locks to a
clustered index on the table. I doubt you have been playing with the
sp_indexoption, so that seems unlikely.
Joachim.
On Fri, 21 Jan 2005 11:28:34 -0500, "Mike W" <mikeotown@.nospam.msn.com>
wrote:
>Forgive my ignorance, but how do I get my statistics up to date? I am not
>experienced in SQL Server administration.
>I am using the pubs database in a new installation of SQL Server.
>When I issue:
>UPDATE authors set au_lname = 'a'
>and then jump over to Enterprise Manager and right-click on "Current
>Activity" under Management and select Refresh, then I go to Locks/Object and
>pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
>Lock Type "PAG".
>If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
>but less PAG locks.
>(I inserted thousands more rows into authors than it comes with to give me
>time to go to Ent. Mgr and select Refresh while the update is running)
>There is a compound index on au_lname and au_fname.
>"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
>message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...
>> Mike
>> The optimiser escalates to a full table lock when it thinks it will be
>> more
>> efficient way of processing the data than using page locks. There are a
>> number of things you can do if you think it should not be performing a
>> table
>> lock.
>> Are your statistics up to date? If you don't regularly update statistics,
>> the optimiser is working with at least one hand tied behind it's back. If
>> the
>> statistics are not up tp date it may be making incorrect choices.
>> Do you have suitable indexes? If you have indexes on the data you are
>> updating (especially a clustered index) it will be easier for the
>> optimiser
>> to use page locks rather than table locks, if suitable.
>> It may be you are updating enough of the table that a table lock is the
>> correct option. In this case you can split up your updates into smaller
>> transactions, small enough that the optimiser uses page locks.
>> Hope this helps
>> John
>> "Mike" wrote:
>> It appears that when we issue an UPDATE statement on a table, it locks
>> the
>> WHOLE table so that any other users can not complete SELECT queries on
>> the
>> table (without using "WITH (nolock)" in the SELECT statement)
>> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing
>> an
>> update, so that maybe it takes longer, but it only locks 1 row or page at
>> a
>> time?
>>
>
--
This post is free post; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.
Joachim Verhagen
http://www.xs4all.nl/~jcdverha/ (Science Jokes)|||Mike
You can update statistics automatically by enabling the auto update
statistics option. You can do this through enterprise manager. Right click on
the database you want to set the option for and choose properties. On the
option page of properties you can turn this option on and off. You can also
turn the option on or off through query analyser using sp_dboption. (See BOL
for details).
You can use dbcc show_statistics to see how up to date your statistics are.
Statistics also get updated when you rebuild a clustered index.
Hope this helps
John
"Mike W" wrote:
> Forgive my ignorance, but how do I get my statistics up to date? I am not
> experienced in SQL Server administration.
> I am using the pubs database in a new installation of SQL Server.
> When I issue:
> UPDATE authors set au_lname = 'a'
> and then jump over to Enterprise Manager and right-click on "Current
> Activity" under Management and select Refresh, then I go to Locks/Object and
> pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
> Lock Type "PAG".
> If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
> but less PAG locks.
> (I inserted thousands more rows into authors than it comes with to give me
> time to go to Ent. Mgr and select Refresh while the update is running)
> There is a compound index on au_lname and au_fname.
> "John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
> message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...
> > Mike
> >
> > The optimiser escalates to a full table lock when it thinks it will be
> > more
> > efficient way of processing the data than using page locks. There are a
> > number of things you can do if you think it should not be performing a
> > table
> > lock.
> >
> > Are your statistics up to date? If you don't regularly update statistics,
> > the optimiser is working with at least one hand tied behind it's back. If
> > the
> > statistics are not up tp date it may be making incorrect choices.
> >
> > Do you have suitable indexes? If you have indexes on the data you are
> > updating (especially a clustered index) it will be easier for the
> > optimiser
> > to use page locks rather than table locks, if suitable.
> >
> > It may be you are updating enough of the table that a table lock is the
> > correct option. In this case you can split up your updates into smaller
> > transactions, small enough that the optimiser uses page locks.
> >
> > Hope this helps
> >
> > John
> >
> > "Mike" wrote:
> >
> >> It appears that when we issue an UPDATE statement on a table, it locks
> >> the
> >> WHOLE table so that any other users can not complete SELECT queries on
> >> the
> >> table (without using "WITH (nolock)" in the SELECT statement)
> >>
> >> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing
> >> an
> >> update, so that maybe it takes longer, but it only locks 1 row or page at
> >> a
> >> time?
> >>
> >>
> >>
>
>

Locking

Why does a simple SELECT statement take an Intent-Exclusive lock? Surely
there is no need to even think about excluding anything else when just
reading. For example SELECT * from pubs..authors aquires two locks - an
Intent-Exclusive table level lock on the clustered index and another
Intent-Exclusive table on the non-clustered index.
I believe this is excessive. Can anyone please explain?
Thanks in advance,
Tim G-JTim
The very simple reason is to stop other processes from updating the data
while you are processing it. This is an effect of isolation levels. If you
don't mind data being updated while you are processing it you can change the
isolation level. Look at isolation levels in Books on line for a full
explaination.
Hope this helps
John
"Tim G-J" wrote:
> Why does a simple SELECT statement take an Intent-Exclusive lock? Surely
> there is no need to even think about excluding anything else when just
> reading. For example SELECT * from pubs..authors aquires two locks - an
> Intent-Exclusive table level lock on the clustered index and another
> Intent-Exclusive table on the non-clustered index.
> I believe this is excessive. Can anyone please explain?
> Thanks in advance,
> Tim G-J|||Thanks John,
Roll on Yukon and optimistic locking.
Tim
"John Bandettini" wrote:
> Tim
> The very simple reason is to stop other processes from updating the data
> while you are processing it. This is an effect of isolation levels. If you
> don't mind data being updated while you are processing it you can change the
> isolation level. Look at isolation levels in Books on line for a full
> explaination.
> Hope this helps
> John
> "Tim G-J" wrote:
> > Why does a simple SELECT statement take an Intent-Exclusive lock? Surely
> > there is no need to even think about excluding anything else when just
> > reading. For example SELECT * from pubs..authors aquires two locks - an
> > Intent-Exclusive table level lock on the clustered index and another
> > Intent-Exclusive table on the non-clustered index.
> >
> > I believe this is excessive. Can anyone please explain?
> >
> > Thanks in advance,
> > Tim G-J

locking

How does SS2000 lock by default for a select, update and delete statement.
Is it row level, page, table?
Thanks,
DanIt depends. Default is row level, but optimizer may escalate it to page
or table level locking if it see fit. You can force it to stay at row
level by using query hints. Check query hints in BOL
--
Eric Li
SQL DBA
MCDBA
Dan wrote:
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||Eric is correct but I want to make one slight comment. SQL Server never
escalates to a page level lock from a row level. All escalation is always
to table level if it occurs. But it can choose to take out a page or table
level lock in the first place.
--
Andrew J. Kelly
SQL Server MVP
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:OMU0VgBSEHA.1392@.TK2MSFTNGP09.phx.gbl...
> It depends. Default is row level, but optimizer may escalate it to page
> or table level locking if it see fit. You can force it to stay at row
> level by using query hints. Check query hints in BOL
> --
> Eric Li
> SQL DBA
> MCDBA
> Dan wrote:
> > How does SS2000 lock by default for a select, update and delete
statement.
> > Is it row level, page, table?
> >
> > Thanks,
> >
> > Dan
> >
> >|||It appears to select the type of locking largely based on the number of
records you are processing. If you processing a large number of records,
taking out a page / table lock is more resource friendly than individual row
locks. Having said that, it's possible to force the use of a particular
locking mechanism with hints.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||If I also may add, SQL does not necessarily lock at the row level... One of
the recent improvements in the locking code for SQL is that the lock manager
takes into account how busy the object ( table for instance) has been
lately...
For instance, you are updating all of the rows in a table. If the table has
been very busy lately, SQL will choose a lower level lock ( page or row
level). This will cause your update to run more slowly ( because there will
be many more calls to the lock manager) , but overall concurrency will be
improved, since you will not be locking rows until right before you make the
update.
If the table has not been used lately, then you may get a whole table
lock... your update runs faster (due to the single lock manager call), and
no one else is hurt, since the table has not been used recently.
However in a busy multi user environment, lower level locks are most often
used...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||Thanks Eric, Andrew, Peter and Wayne. I understand it better now and I'll do
some more reading.
Dan
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:egQfZvJSEHA.1160@.TK2MSFTNGP09.phx.gbl...
> If I also may add, SQL does not necessarily lock at the row level... One
of
> the recent improvements in the locking code for SQL is that the lock
manager
> takes into account how busy the object ( table for instance) has been
> lately...
> For instance, you are updating all of the rows in a table. If the table
has
> been very busy lately, SQL will choose a lower level lock ( page or row
> level). This will cause your update to run more slowly ( because there
will
> be many more calls to the lock manager) , but overall concurrency will be
> improved, since you will not be locking rows until right before you make
the
> update.
> If the table has not been used lately, then you may get a whole table
> lock... your update runs faster (due to the single lock manager call), and
> no one else is hurt, since the table has not been used recently.
> However in a busy multi user environment, lower level locks are most often
> used...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> > How does SS2000 lock by default for a select, update and delete
statement.
> > Is it row level, page, table?
> >
> > Thanks,
> >
> > Dan
> >
> >
>

locking

How does SS2000 lock by default for a select, update and delete statement.
Is it row level, page, table?
Thanks,
Dan
It depends. Default is row level, but optimizer may escalate it to page
or table level locking if it see fit. You can force it to stay at row
level by using query hints. Check query hints in BOL
Eric Li
SQL DBA
MCDBA
Dan wrote:

> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>
|||Eric is correct but I want to make one slight comment. SQL Server never
escalates to a page level lock from a row level. All escalation is always
to table level if it occurs. But it can choose to take out a page or table
level lock in the first place.
Andrew J. Kelly
SQL Server MVP
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:OMU0VgBSEHA.1392@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> It depends. Default is row level, but optimizer may escalate it to page
> or table level locking if it see fit. You can force it to stay at row
> level by using query hints. Check query hints in BOL
> --
> Eric Li
> SQL DBA
> MCDBA
> Dan wrote:
statement.[vbcol=seagreen]
|||It appears to select the type of locking largely based on the number of
records you are processing. If you processing a large number of records,
taking out a page / table lock is more resource friendly than individual row
locks. Having said that, it's possible to force the use of a particular
locking mechanism with hints.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>
|||If I also may add, SQL does not necessarily lock at the row level... One of
the recent improvements in the locking code for SQL is that the lock manager
takes into account how busy the object ( table for instance) has been
lately...
For instance, you are updating all of the rows in a table. If the table has
been very busy lately, SQL will choose a lower level lock ( page or row
level). This will cause your update to run more slowly ( because there will
be many more calls to the lock manager) , but overall concurrency will be
improved, since you will not be locking rows until right before you make the
update.
If the table has not been used lately, then you may get a whole table
lock... your update runs faster (due to the single lock manager call), and
no one else is hurt, since the table has not been used recently.
However in a busy multi user environment, lower level locks are most often
used...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>
|||Thanks Eric, Andrew, Peter and Wayne. I understand it better now and I'll do
some more reading.
Dan
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:egQfZvJSEHA.1160@.TK2MSFTNGP09.phx.gbl...
> If I also may add, SQL does not necessarily lock at the row level... One
of
> the recent improvements in the locking code for SQL is that the lock
manager
> takes into account how busy the object ( table for instance) has been
> lately...
> For instance, you are updating all of the rows in a table. If the table
has
> been very busy lately, SQL will choose a lower level lock ( page or row
> level). This will cause your update to run more slowly ( because there
will
> be many more calls to the lock manager) , but overall concurrency will be
> improved, since you will not be locking rows until right before you make
the[vbcol=seagreen]
> update.
> If the table has not been used lately, then you may get a whole table
> lock... your update runs faster (due to the single lock manager call), and
> no one else is hurt, since the table has not been used recently.
> However in a busy multi user environment, lower level locks are most often
> used...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
statement.
>
sql

Locking

Why does a simple SELECT statement take an Intent-Exclusive lock? Surely
there is no need to even think about excluding anything else when just
reading. For example SELECT * from pubs..authors aquires two locks - an
Intent-Exclusive table level lock on the clustered index and another
Intent-Exclusive table on the non-clustered index.
I believe this is excessive. Can anyone please explain?
Thanks in advance,
Tim G-J
Tim
The very simple reason is to stop other processes from updating the data
while you are processing it. This is an effect of isolation levels. If you
don't mind data being updated while you are processing it you can change the
isolation level. Look at isolation levels in Books on line for a full
explaination.
Hope this helps
John
"Tim G-J" wrote:

> Why does a simple SELECT statement take an Intent-Exclusive lock? Surely
> there is no need to even think about excluding anything else when just
> reading. For example SELECT * from pubs..authors aquires two locks - an
> Intent-Exclusive table level lock on the clustered index and another
> Intent-Exclusive table on the non-clustered index.
> I believe this is excessive. Can anyone please explain?
> Thanks in advance,
> Tim G-J
|||Thanks John,
Roll on Yukon and optimistic locking.
Tim
"John Bandettini" wrote:
[vbcol=seagreen]
> Tim
> The very simple reason is to stop other processes from updating the data
> while you are processing it. This is an effect of isolation levels. If you
> don't mind data being updated while you are processing it you can change the
> isolation level. Look at isolation levels in Books on line for a full
> explaination.
> Hope this helps
> John
> "Tim G-J" wrote:

Locking

It appears that when we issue an UPDATE statement on a table, it locks the
WHOLE table so that any other users can not complete SELECT queries on the
table (without using "WITH (nolock)" in the SELECT statement)
Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing an
update, so that maybe it takes longer, but it only locks 1 row or page at a
time?
Mike
The optimiser escalates to a full table lock when it thinks it will be more
efficient way of processing the data than using page locks. There are a
number of things you can do if you think it should not be performing a table
lock.
Are your statistics up to date? If you don't regularly update statistics,
the optimiser is working with at least one hand tied behind it's back. If the
statistics are not up tp date it may be making incorrect choices.
Do you have suitable indexes? If you have indexes on the data you are
updating (especially a clustered index) it will be easier for the optimiser
to use page locks rather than table locks, if suitable.
It may be you are updating enough of the table that a table lock is the
correct option. In this case you can split up your updates into smaller
transactions, small enough that the optimiser uses page locks.
Hope this helps
John
"Mike" wrote:

> It appears that when we issue an UPDATE statement on a table, it locks the
> WHOLE table so that any other users can not complete SELECT queries on the
> table (without using "WITH (nolock)" in the SELECT statement)
> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing an
> update, so that maybe it takes longer, but it only locks 1 row or page at a
> time?
>
>
|||Forgive my ignorance, but how do I get my statistics up to date? I am not
experienced in SQL Server administration.
I am using the pubs database in a new installation of SQL Server.
When I issue:
UPDATE authors set au_lname = 'a'
and then jump over to Enterprise Manager and right-click on "Current
Activity" under Management and select Refresh, then I go to Locks/Object and
pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
Lock Type "PAG".
If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
but less PAG locks.
(I inserted thousands more rows into authors than it comes with to give me
time to go to Ent. Mgr and select Refresh while the update is running)
There is a compound index on au_lname and au_fname.
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...[vbcol=seagreen]
> Mike
> The optimiser escalates to a full table lock when it thinks it will be
> more
> efficient way of processing the data than using page locks. There are a
> number of things you can do if you think it should not be performing a
> table
> lock.
> Are your statistics up to date? If you don't regularly update statistics,
> the optimiser is working with at least one hand tied behind it's back. If
> the
> statistics are not up tp date it may be making incorrect choices.
> Do you have suitable indexes? If you have indexes on the data you are
> updating (especially a clustered index) it will be easier for the
> optimiser
> to use page locks rather than table locks, if suitable.
> It may be you are updating enough of the table that a table lock is the
> correct option. In this case you can split up your updates into smaller
> transactions, small enough that the optimiser uses page locks.
> Hope this helps
> John
> "Mike" wrote:
|||I think you see the IX (intent exclusive) lock on the table.
That is not a real lock, it just tells the the engine that there is a execute
or update lock somewhere in the table.
Normally SQLServer will use row locks. The only way you get a real table
lock with this statement is if you have forbidden row and page locks to a
clustered index on the table. I doubt you have been playing with the
sp_indexoption, so that seems unlikely.
Joachim.
On Fri, 21 Jan 2005 11:28:34 -0500, "Mike W" <mikeotown@.nospam.msn.com>
wrote:

>Forgive my ignorance, but how do I get my statistics up to date? I am not
>experienced in SQL Server administration.
>I am using the pubs database in a new installation of SQL Server.
>When I issue:
>UPDATE authors set au_lname = 'a'
>and then jump over to Enterprise Manager and right-click on "Current
>Activity" under Management and select Refresh, then I go to Locks/Object and
>pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
>Lock Type "PAG".
>If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
>but less PAG locks.
>(I inserted thousands more rows into authors than it comes with to give me
>time to go to Ent. Mgr and select Refresh while the update is running)
>There is a compound index on au_lname and au_fname.
>"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
>message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...
>
This post is free post; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.
Joachim Verhagen
http://www.xs4all.nl/~jcdverha/ (Science Jokes)
|||Mike
You can update statistics automatically by enabling the auto update
statistics option. You can do this through enterprise manager. Right click on
the database you want to set the option for and choose properties. On the
option page of properties you can turn this option on and off. You can also
turn the option on or off through query analyser using sp_dboption. (See BOL
for details).
You can use dbcc show_statistics to see how up to date your statistics are.
Statistics also get updated when you rebuild a clustered index.
Hope this helps
John
"Mike W" wrote:

> Forgive my ignorance, but how do I get my statistics up to date? I am not
> experienced in SQL Server administration.
> I am using the pubs database in a new installation of SQL Server.
> When I issue:
> UPDATE authors set au_lname = 'a'
> and then jump over to Enterprise Manager and right-click on "Current
> Activity" under Management and select Refresh, then I go to Locks/Object and
> pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
> Lock Type "PAG".
> If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
> but less PAG locks.
> (I inserted thousands more rows into authors than it comes with to give me
> time to go to Ent. Mgr and select Refresh while the update is running)
> There is a compound index on au_lname and au_fname.
> "John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
> message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...
>
>

Lock Type

Hello there,

Example

I pass this statement to the database:

SELECT FunctionName,FunctionDescription FROM Functions WHERE FunctionID = 21

Nothing special here.

Now i check in the Enterprise Manager in the Managment->Current Activity->Locks/Process ID Map and see this for my process

Object: LISPROD
Lock Type: DB
Mode: S
Status: Grant
Owner Sess
Index:
Resource:

My question now, is the LockType. Isn't this a bit too much. A full database lock while the query is only on one table.
All locks in the database seem to have this behaviour.

Cheers Erik.The lock type u see in
Enterprise Manager in the
Managment->Current Activity->Locks/Process ID

is DB...means database level locking.

Its not bacause u have fired a query, but Quey analyzer locks it as soon as u select a database form the drop down combo...

When we generally fire a query DB lavel locking is not used but Table level locking is used.

u can change it to page lavel or row level locking if required...but that approach has its own implications.

Naveen Mehta.

lock timeout

my SQL studio view is running into timeout error block. how do i insert the

SET LOCK_TIMEOUT -1
GO

in the SQL statement of the view to allow this to run to completion? an example of the SQL view is;

SELECT TOP (100) PERCENT dbo.Entry_Race.E_TDR, dbo.Entry_Race.E_Surface, dbo.Entry_Race.E_Race_Class_Codes,
FROM dbo.Entry_Race INNER JOIN
dbo.Entry_Horse ON dbo.Entry_Race.E_TDR = dbo.Entry_Horse.E_TDR
WHERE (CONVERT(varchar(07), dbo.Entry_Horse.E_Date) BETWEEN CONVERT(varchar(07), GETDATE(), 0) AND CONVERT(varchar(07), GETDATE() + 1, 0))
ORDER BY dbo.Entry_Race.E_TDR, dbo.Entry_Horse.E_Horse, dbo.Entry_Horse.E_Traininer

Do you really need to wait indefinitelly? It is not a very normal situation to have the client waiting tens of seconds for a response - why not using a more optimistic locking mechanism?

You shouldn't user the convert funcion to compare the dates, but using dateadd () over the getdate() functions and compare directly - as it is, any indexes over dbo.Entry_Horse.E_Date will not be used by SQL...

Lock table until transaction finish.

HOw do I write a statement to lock a table until my transactions are finish?

e.g...

declare @.LatestRecordid int
declare @.Value varchar(10)
set @.Value='just a value'

'Lock Syantax here

insert into table_to_be_lock (tablecolumn) values (@.value)
set @.LatestRecordid=(select ident_current('table_to_be_lock')+1
Update summarytable set LatestId=@.LatestRecordid, ItsValue=@.value

'Lock Syantax end

The reason I need to lock it is because at the second line of code, I am afraid another person has run the same query and inserted another record b4 my second line of code is runned, thus I'll get the wrong Recordid pointing to the wrong "ItsValue".

I am pretty sure there is a syantax for it...but I just can't seem to remember...can anybody help?Not that it will happen because SQl Server Default isolation level is "Read Commited". In other words , other user will see only written records and SQL will handle locks internally (shared, update,Row level, Table level ..etc.)|||Use SCOPE_IDENTITY instead of ident_current - you will have your id.

Anyway it is good idea to open transaction before your insert, check for erros during insert and updates (rollback if it needs) and commit transaction.|||okay....wil try to read on scope....

err..btw..this is abit out of the topic...

I was wondering,which is much faster and less taxing on cpu processing?

select ident_current('tablename') vs select max(tableidentitycolumn)

as both gets the same value. I can't test it out as I only have a small table here, can't tell the difference.

can someone actually really test it out at their environment ?

I'm guessing ident_current will be much faster, correct?|||Patrick, ur guess is correct, max will parse through each row in the table. I guess ident_current,@.@.identity is like a global variable which stores the last ident value.|||So many problems would be so much easier if developers used UniqueIdentifiers instead of GUIDs.

Sigh. :(|||hmm..what are the difference??
isit the ones that is being generated usually during replication??
read somewhere that those datatype are bigger in size and may cause slower indexes...|||They may be bigger in size, but you can use them in very creative SQL schemas and avoid much of the overhead of tracking and looking up identity values.

blindman

local variable in select statement

Hello to all,
are there that have already used a select like the
following to catch backup history rows from different SQL
server defined as linked server
select *
from @.my_system.msdb.dbo.sysdbmaintplan_history
where .........
the local variable @...... set using a cursor seams to be
not sintactically correct.
Any idea
Thanks marinoYou need to use dynamic sql if the servername changes
e.g.
declare @.cmd nvarchar(500)
set @.cmd = N'select * from ' + @.my_system +
N'.msdb.dbo.sysdbmaintplan_history'
exec sp_executesql @.cmd
For more on dynamic sql see
http://www.algonet.se/~sommar/dynamic_sql.html
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Marino Prandini" <marino_prandini@.hotmail.com> wrote in message
news:181101c3aeb4$3af029b0$a601280a@.phx.gbl...
Hello to all,
are there that have already used a select like the
following to catch backup history rows from different SQL
server defined as linked server
select *
from @.my_system.msdb.dbo.sysdbmaintplan_history
where .........
the local variable @...... set using a cursor seams to be
not sintactically correct.
Any idea
Thanks marino

Local Cubes in SS2005, specifically for the dev team

Hi,

A question on local cubes...

If I'm right in saying that if you use the CREATE CUBE statement to create a local cube using the MSOLAP.3 provider, that under the hood you will in fact still be using the MSOLAP.2 provider and hence creating AS2K local cubes, how will this tally with mainstream support for AS2K ending in 2008? Will these form of local cube cease to be supported at that time?

regards

Colin

No, your assumption is wrong - CREATE CUBE is sent to msolap90.dll and creates a AS2005 cube. It can't be opened with MSOLAP.2 provider.|||

Well I'm basing my assumption on the information in MDX Solutions, chapter 17, which in turn was based on information obtained directly from the development team

As I understand it, when the connection is made to the empty .cub file and the MSOLAP.3 provider sees the syntax CREATE CUBE and INSERT INTO, it automatically delegates the query to the MSOLAP80.dll which in turn creates an AS2K cube rather than an AS2005 one.

You only create an AS2005 cube if you use the CREATE GLOBAL CUBE syntax or ASSL.

regards

Colin

LoadReportDefinition->Root element is missing!?

Hi all,
I try to load a RDL-File by code with the following statement:
rpvReport.ServerReport.LoadReportDefinition(strReader);
Unfortunaltely the following error occurs "Root element is missing"
(see more beneath).
I dont get the point as in my eyes the rdl (xml) files i tried are
well-formed.
Also when I upload them by the report manager they are created without
any problems.
Does anybody have a clue where the bug could be?
THX a lot for any help!!
Cheers
Marc
RDL-FILE:
---
<?xml version=3D"1.0" encoding=3D"utf-8"?>
<Report
xmlns=3D"http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdef= inition"
xmlns:rd=3D"http://schemas.microsoft.com/SQLServer/reporting/reportdesigner= ">
<PageWidth>7.5 in</PageWidth>
<ReportParameters>
</ReportParameters>
<LeftMargin>0.5 in</LeftMargin>
<RightMargin>0.5 in</RightMargin>
<TopMargin>0.5 in</TopMargin>
<BottomMargin>0.3 in</BottomMargin>
<PageHeader>
<Height>1 in</Height>
<ReportItems>
<Textbox Name=3D"Label13">
<Value>PAGE HEADER CONTROL</Value>
</Textbox>
</ReportItems>
</PageHeader>
<Body>
<ReportItems>
<Line Name=3D"BodyLine">
</Line>
</ReportItems>
<Height>1 in</Height>
<Style />
</Body>
<PageFooter>
<Height>0.17 in</Height>
<ReportItems>
<Textbox Name=3D"Label18">
<Value>PAGE FOOTER CONTROL</Value>
</Textbox>
</ReportItems>
</PageFooter>
</Report>
----
ERROR-MESSAGE:
Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 09.10.2006 18:37:19
Event time (UTC): 09.10.2006 16:37:19
Event ID: f84f56da10ee4e3991c3de6244b58009
Event sequence: 36
Event occurrence: 1
Event detail code: 0
Application information:
Application domain: cab7894e-6-128048849773258592
Trust level: Full
Application Virtual Path: /
Application Path: C:\_workspace\RenoSF.Reporting\Controls\
Machine name: MTHIERRTCB2TR
Process information:
Process ID: 3600
Process name: WebDev.WebServer2.exe
Account name: DELTA_ZUG\mthierer
Exception information:
Exception type: ReportServerException
Exception message: Die Berichtsdefinition ist ung=FCltig. Details:
Root element is missing. (rsInvalidReportDefinition)
Request information:
Request URL: http://localhost:1831/default.aspx
Request path: /default.aspx
User host address: 127.0.0.1
User: DELTA_ZUG\mthierer
Is authenticated: True
Authentication Type: NTLM
Thread account name: DELTA_ZUG\mthierer
Thread information:
Thread ID: 4
Thread account name: DELTA_ZUG\mthierer
Is impersonating: False
Stack trace: at
Microsoft.Reporting.WebForms.ServerReport.LoadReportDefinition(TextReader
report)
at
RTC.SharePoint.RenoSF.Reporting.BasisReports.ReportFormControls.ReportViewe= r=2ELoadDefinition(List`1
list, String rdl) in
C:\_workspace\RenoSF.Reporting\Controls\ReportFormControls\ReportViewer.asc= x=2Ecs:line
90
at
RTC.SharePoint.RenoSF.Reporting.BasisReports.ReportFormControls.ReportViewe= r=2EBtnCreateReportClick(Object
sender, EventArgs e) in
C:\_workspace\RenoSF.Reporting\Controls\ReportFormControls\ReportViewer.asc= x=2Ecs:line
138
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String
eventArgument)
at
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaiseP= ostBackEvent(String
eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection
postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)ReportingService2005 rs = new ReportingService2005();
byte[] b = rs.GetReportDefinition("/ReportServer/RolesReport");
MemoryStream strm = new MemoryStream(b);
XmlDocument doc = new XmlDocument();
doc.Load(strm);
// modify xml for ad-hoc report
MemoryStream strmOut = new MemoryStream();
doc.Save(strmOut);
strmOut.Position = 0; // must call this
this.rvReport.ProcessingMode =Microsoft.Reporting.WebForms.ProcessingMode.Remote;
this.rvReport.ServerReport.ReportServerUrl = new
Uri("http://localhost/ReportServer");
this.rvReport.ServerReport.ReportPath = "/ReportServer/RolesReport";
this.rvReport.ServerReport.LoadReportDefinition(strmOut);
System.Diagnostics.Debug.WriteLine(this.rvReport.ServerReport.GetExecutionId());
strm.Close();
strmOut.Close();
I think you lost " strmOut.Position = 0";
--sidney
"Marc" wrote:
> Hi all,
> I try to load a RDL-File by code with the following statement:
> rpvReport.ServerReport.LoadReportDefinition(strReader);
> Unfortunaltely the following error occurs "Root element is missing"
> (see more beneath).
> I dont get the point as in my eyes the rdl (xml) files i tried are
> well-formed.
> Also when I upload them by the report manager they are created without
> any problems.
> Does anybody have a clue where the bug could be?
> THX a lot for any help!!
> Cheers
> Marc
>
> RDL-FILE:
> ---
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <PageWidth>7.5 in</PageWidth>
> <ReportParameters>
> </ReportParameters>
> <LeftMargin>0.5 in</LeftMargin>
> <RightMargin>0.5 in</RightMargin>
> <TopMargin>0.5 in</TopMargin>
> <BottomMargin>0.3 in</BottomMargin>
> <PageHeader>
> <Height>1 in</Height>
> <ReportItems>
> <Textbox Name="Label13">
> <Value>PAGE HEADER CONTROL</Value>
> </Textbox>
> </ReportItems>
> </PageHeader>
> <Body>
> <ReportItems>
> <Line Name="BodyLine">
> </Line>
> </ReportItems>
> <Height>1 in</Height>
> <Style />
> </Body>
> <PageFooter>
> <Height>0.17 in</Height>
> <ReportItems>
> <Textbox Name="Label18">
> <Value>PAGE FOOTER CONTROL</Value>
> </Textbox>
> </ReportItems>
> </PageFooter>
> </Report>
> ----
>
> ERROR-MESSAGE:
> Event code: 3005
> Event message: An unhandled exception has occurred.
> Event time: 09.10.2006 18:37:19
> Event time (UTC): 09.10.2006 16:37:19
> Event ID: f84f56da10ee4e3991c3de6244b58009
> Event sequence: 36
> Event occurrence: 1
> Event detail code: 0
> Application information:
> Application domain: cab7894e-6-128048849773258592
> Trust level: Full
> Application Virtual Path: /
> Application Path: C:\_workspace\RenoSF.Reporting\Controls\
> Machine name: MTHIERRTCB2TR
> Process information:
> Process ID: 3600
> Process name: WebDev.WebServer2.exe
> Account name: DELTA_ZUG\mthierer
> Exception information:
> Exception type: ReportServerException
> Exception message: Die Berichtsdefinition ist ungültig. Details:
> Root element is missing. (rsInvalidReportDefinition)
> Request information:
> Request URL: http://localhost:1831/default.aspx
> Request path: /default.aspx
> User host address: 127.0.0.1
> User: DELTA_ZUG\mthierer
> Is authenticated: True
> Authentication Type: NTLM
> Thread account name: DELTA_ZUG\mthierer
> Thread information:
> Thread ID: 4
> Thread account name: DELTA_ZUG\mthierer
> Is impersonating: False
> Stack trace: at
> Microsoft.Reporting.WebForms.ServerReport.LoadReportDefinition(TextReader
> report)
> at
> RTC.SharePoint.RenoSF.Reporting.BasisReports.ReportFormControls.ReportViewer.LoadDefinition(List`1
> list, String rdl) in
> C:\_workspace\RenoSF.Reporting\Controls\ReportFormControls\ReportViewer.ascx.cs:line
> 90
> at
> RTC.SharePoint.RenoSF.Reporting.BasisReports.ReportFormControls.ReportViewer.BtnCreateReportClick(Object
> sender, EventArgs e) in
> C:\_workspace\RenoSF.Reporting\Controls\ReportFormControls\ReportViewer.ascx.cs:line
> 138
> at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
> at System.Web.UI.WebControls.Button.RaisePostBackEvent(String
> eventArgument)
> at
> System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
> eventArgument)
> at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
> sourceControl, String eventArgument)
> at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection
> postData)
> at System.Web.UI.Page.ProcessRequestMain(Boolean
> includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
>