Don

Hi every one,
I learn a lot reading your comments,
Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
performance or create locks ?
I mean : While UPDATE STATISTICS is runnning, will it
affect performance ?
Thanks !
DonYes it will have an impact. Whether this will be noticeable to users are
not is another question...
Regarding locking, SQL Server takes two types of locks:
"Sch-S: Schema Stability Lock
--
This lock ensures that a schema element, such as a table or index, will
not be dropped while any session holds a schema stability lock on the
schema element.
Sch-M-UPD-STATS: Schema Modification Lock
---
This is a non-blocking lock that is used by the system to ensure that
only one automatic UPDATE STATISTICS process is run against a table at
any given point in time. The sp_lock stored procedure will report this
lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M."
Above information found here:
INF: How SQL Server 7.0 and SQL Server 2000 Autostats Work
http://support.microsoft.com/kb/q195565/
They should not block usual DML queries (insert, update, delete), but
may block DDL queries (drop table, alter table, alter database, etc).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
PERFORMANCE DURING UPDATING STATS wrote:
> Hi every one,
> I learn a lot reading your comments,
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
> Thanks !
> Don|||"PERFORMANCE DURING UPDATING STATS" <anonymous@.discussions.microsoft.com>
wrote in message news:1ecd01c4b5d7$297b9ea0$a401280a@.phx.gbl...
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
It will have an effect on performance, in that it uses system resources.
How much of an effect is dependant on your server's horsepower, the size of
the table you're updating statistics for, etc... It will, like any other
read operation, create shared locks, but I have not seen it create exclusive
locks. So blocking should not be an issue.|||"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:OtavF8dtEHA.2072@.tk2msftngp13.phx.gbl...
> any given point in time. The sp_lock stored procedure will report this
> lock has having a type = TAB, resouce = UPD-STATS and mode = SCH-M."
Mark,
I've never seen the SCH-M lock; I always get only SCH-S when I use
UPDATE STATISTICS on my systems. Is there an option that controls which
lock types are taken?|||Adam,
No, not that I'm aware of.
Adam Machanic wrote:
> I've never seen the SCH-M lock; I always get only SCH-S when I use
> UPDATE STATISTICS on my systems. Is there an option that controls which
> lock types are taken?
>|||UPDATE STATISTICS doesn't use locks, but it will affect performance, because
data pages are randomly accessed from the table to create the statistics.
Most likely not all of these pages are in memory and have to be read from
disk.
Jacco Schalkwijk
SQL Server MVP
"PERFORMANCE DURING UPDATING STATS" <anonymous@.discussions.microsoft.com>
wrote in message news:1ecd01c4b5d7$297b9ea0$a401280a@.phx.gbl...
> Hi every one,
> I learn a lot reading your comments,
> Does runnning "UPDATE STATISTICS MYBIGTABLE" will affect
> performance or create locks ?
> I mean : While UPDATE STATISTICS is runnning, will it
> affect performance ?
> Thanks !
> Don