Prev: Migrating from MsAccess to MS SQL
Next: SQL Server 2000, Non-Clustered Index Creation and Blocking
From: John Couch on 20 Apr 2010 08:58
Thanks Tibor.

"Tibor Karaszi" wrote:

> FYI, I just verified, and there were no exclusive locks on the table. But
> there were shared locks. Here's the script. 5 million rows for that table
> and index definition have me plenty of time to run sp_lock (create index
> commend now in 3:30 min, and counting).
>
> DROP TABLE a
> GO
> CREATE TABLE a(c1 int identity, c2 char(500))
> INSERT INTO a(c2)
> SELECT TOP(5000000) 'hi' FROM syscolumns a, syscolumns b, syscolumns c
> GO
> DBCC DROPCLEANBUFFERS
> CREATE INDEX x ON a(c2)
>
> --While above executes, exec from different session
> SELECT OBJECT_ID('a')
> EXEC sp_lock
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
>
> "John Couch" <JohnCouch(a)discussions.microsoft.com> wrote in message
> news:C46B96D2-1B23-4E33-A3F9-D985B17FB2B8(a)microsoft.com...
> > I have tried to test this, but even with 30 million records int he table
> > the
> > index is created so fast that executing the query and hitting an sp_who2
> > isn't either showing a block, or there isn't one at all. Does anyone know
> > if
> > creating a non-clustered index is a blocking operation? I was thinking it
> > might be if the index was being created on the same fields the where
> > clause
> > was querying on. Unless the create nonclustered index statement uses a
> > nolock
> > statement on the values. Any help would be great. thanks.
>
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Migrating from MsAccess to MS SQL
Next: SQL Server 2000, Non-Clustered Index Creation and Blocking