CREATE STATISTICS locking up tables?

Hi
I'm just wondering if executing CREATE STATISTICS (full scan) would cause
problems to the user operations by locking up tables or rows. Thanks!It certainly does. Note the create statistics are updated as part of an
index rebuild, so you might not have to do this.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Zen" <zen@.nononospam.com> wrote in message
news:OHVSBq6bGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Hi
> I'm just wondering if executing CREATE STATISTICS (full scan) would cause
> problems to the user operations by locking up tables or rows. Thanks!
>

Create index + blocking

Would a Create Index on a table cause blocking on the table for the entire
length of the duration ?
I should add that creating a clustered index causes an exclusive lock, where a non-clustered index
cause a shared lock.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:ek4XupkbFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Would a Create Index on a table cause blocking on the table for the entire
> length of the duration ?
>
|||Yes. In 2005, you will have an ONLINE option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:ek4XupkbFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Would a Create Index on a table cause blocking on the table for the entire
> length of the duration ?
>
|||An exclusive lock at the table for the entire duration or at a lower
granularity such as page which would then be intermittent blocking ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uSWCTVlbFHA.2520@.TK2MSFTNGP09.phx.gbl...
> I should add that creating a clustered index causes an exclusive lock,
where a non-clustered index
> cause a shared lock.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ek4XupkbFHA.3184@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
entire
>
|||When building a clustered index, SQL Server has to completely reorganize the
entire table, so it gets an exclusive table lock for the duration.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O0MUk2vbFHA.1392@.TK2MSFTNGP14.phx.gbl...
> An exclusive lock at the table for the entire duration or at a lower
> granularity such as page which would then be intermittent blocking ?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:uSWCTVlbFHA.2520@.TK2MSFTNGP09.phx.gbl...
> where a non-clustered index
> news:ek4XupkbFHA.3184@.TK2MSFTNGP15.phx.gbl...
> entire
>

Create index + blocking

Would a Create Index on a table cause blocking on the table for the entire
length of the duration ?I should add that creating a clustered index causes an exclusive lock, where a non-clustered index
cause a shared lock.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:ek4XupkbFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Would a Create Index on a table cause blocking on the table for the entire
> length of the duration ?
>|||Yes. In 2005, you will have an ONLINE option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:ek4XupkbFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Would a Create Index on a table cause blocking on the table for the entire
> length of the duration ?
>|||An exclusive lock at the table for the entire duration or at a lower
granularity such as page which would then be intermittent blocking ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uSWCTVlbFHA.2520@.TK2MSFTNGP09.phx.gbl...
> I should add that creating a clustered index causes an exclusive lock,
where a non-clustered index
> cause a shared lock.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ek4XupkbFHA.3184@.TK2MSFTNGP15.phx.gbl...
> > Would a Create Index on a table cause blocking on the table for the
entire
> > length of the duration ?
> >
> >
>|||When building a clustered index, SQL Server has to completely reorganize the
entire table, so it gets an exclusive table lock for the duration.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O0MUk2vbFHA.1392@.TK2MSFTNGP14.phx.gbl...
> An exclusive lock at the table for the entire duration or at a lower
> granularity such as page which would then be intermittent blocking ?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:uSWCTVlbFHA.2520@.TK2MSFTNGP09.phx.gbl...
>> I should add that creating a clustered index causes an exclusive lock,
> where a non-clustered index
>> cause a shared lock.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ek4XupkbFHA.3184@.TK2MSFTNGP15.phx.gbl...
>> > Would a Create Index on a table cause blocking on the table for the
> entire
>> > length of the duration ?
>> >
>> >
>

Create index + blocking

Would a Create Index on a table cause blocking on the table for the entire
length of the duration ?I should add that creating a clustered index causes an exclusive lock, where
a non-clustered index
cause a shared lock.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:ek4XupkbFHA.3184@.TK2MSFTNGP15.phx.gbl
..
> Would a Create Index on a table cause blocking on the table for the entire
> length of the duration ?
>|||Yes. In 2005, you will have an ONLINE option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Hassan" <fatima_ja@.hotmail.com> wrote in message news:ek4XupkbFHA.3184@.TK2MSFTNGP15.phx.gbl
..
> Would a Create Index on a table cause blocking on the table for the entire
> length of the duration ?
>|||An exclusive lock at the table for the entire duration or at a lower
granularity such as page which would then be intermittent blocking ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uSWCTVlbFHA.2520@.TK2MSFTNGP09.phx.gbl...
> I should add that creating a clustered index causes an exclusive lock,
where a non-clustered index
> cause a shared lock.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ek4XupkbFHA.3184@.TK2MSFTNGP15.phx.gbl...
entire[vbcol=seagreen]
>|||When building a clustered index, SQL Server has to completely reorganize the
entire table, so it gets an exclusive table lock for the duration.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O0MUk2vbFHA.1392@.TK2MSFTNGP14.phx.gbl...
> An exclusive lock at the table for the entire duration or at a lower
> granularity such as page which would then be intermittent blocking ?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:uSWCTVlbFHA.2520@.TK2MSFTNGP09.phx.gbl...
> where a non-clustered index
> news:ek4XupkbFHA.3184@.TK2MSFTNGP15.phx.gbl...
> entire
>