Prev: Migrating from MsAccess to MS SQL
Next: SQL Server 2000, Non-Clustered Index Creation and Blocking
From: John Couch on 19 Apr 2010 12:07
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.
From: John Couch on 19 Apr 2010 12:14
Sorry for the double post. My initial post said it timed out and was unable
to complete. Apparently that wasn't true.

"John Couch" wrote:

> 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.
From: Gert-Jan Strik on 19 Apr 2010 15:30
John Couch wrote:
>
> 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.

It would at least block all inserts and deletes and any update to the
indexed column(s).

Selects should still be possible, although your server will be very
busy.

--
Gert-Jan
From: Tibor Karaszi on 20 Apr 2010 02:19
As I remember, non-clustered indexes should only acquire shared locks (no
exclusive locks). So, no modifications allowed while index is being created.

--
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.

From: Tibor Karaszi on 20 Apr 2010 02:30
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.

 |  Next  |  Last
Pages: 1 2
Prev: Migrating from MsAccess to MS SQL
Next: SQL Server 2000, Non-Clustered Index Creation and Blocking