Sunday, February 26, 2012
Don
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
Don
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
Yes 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
Don
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
Tuesday, February 14, 2012
Does truncate and API Load invalidate statistics
target database.
As example, I find it is much quicker to do a bulk API load of 13M
records and to do an update/insert of 100K rows. I also drop the
indexes before the builds and reindex after. Thats an aside.
What I am wondering is how is this impacting the statistics? Do I need
to update them?
Not well versed on statistics and any data is welcomed.
Thanks
Robrcamarda (robc390@.hotmail.com) writes:
Quote:
Originally Posted by
As part of my data warehouse nightly build, I truncate my tables in my
target database.
As example, I find it is much quicker to do a bulk API load of 13M
records and to do an update/insert of 100K rows. I also drop the
indexes before the builds and reindex after. Thats an aside.
What I am wondering is how is this impacting the statistics? Do I need
to update them?
When an indexes added, statistics based a full scan is added for that
index, so you are fairly safe.
Would could matter is statistics of non-indexed columns. These you lose
when you truncate the tables. These statistics are less essential than
index on indexed columns, but there are queries where they may be useful.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx