Blocking while creating index?

If I dont use online indexing, will there be blocking for the entire
duration of the index creation be it clustered or non clustered ? I thought
there might be an exclusive lock on the table for the entire duration where
even selects would be blocked...but it doesnt appear to be true.
Please let me know
Using SQL 2005See BOL, Alter Index, Online OFF
"Table locks are applied for the duration of the index operation. An offline
index operation that creates, rebuilds, or drops a clustered index, or
rebuilds or drops a nonclustered index, acquires a Schema modification
(Sch-M) lock on the table. This prevents all user access to the underlying
table for the duration of the operation. An offline index operation that
creates a nonclustered index acquires a Shared (S) lock on the table. This
prevents updates to the underlying table but allows read operations, such as
SELECT statements."
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> If I dont use online indexing, will there be blocking for the entire
> duration of the index creation be it clustered or non clustered ? I thought
> there might be an exclusive lock on the table for the entire duration where
> even selects would be blocked...but it doesnt appear to be true.
> Please let me know
> Using SQL 2005
>

Block Duration in SQL Server 2000

I am writing a small utility to capture Blocking/Blocked processes that is
running for certiain period of time...say 5 sec or more.
logic that I use in the stored proc is (Current Time Stamp -
sysprocesses.Last_Batch) for the duration of the process.
But I am getting incorrect values!!!
Could some one please let me know How to find the Block Duration ?
Thanks
CheriIf you don't want to reinvent the wheel, have a look at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"cheri" <cheri@.discussions.microsoft.com> wrote in message
news:026B2B6E-7664-427E-8B06-64058AE12DF7@.microsoft.com...
>I am writing a small utility to capture Blocking/Blocked processes that is
> running for certiain period of time...say 5 sec or more.
> logic that I use in the stored proc is (Current Time Stamp -
> sysprocesses.Last_Batch) for the duration of the process.
> But I am getting incorrect values!!!
> Could some one please let me know How to find the Block Duration ?
> Thanks
> Cheri
>|||cheri (cheri@.discussions.microsoft.com) writes:
> I am writing a small utility to capture Blocking/Blocked processes that is
> running for certiain period of time...say 5 sec or more.
> logic that I use in the stored proc is (Current Time Stamp -
> sysprocesses.Last_Batch) for the duration of the process.
> But I am getting incorrect values!!!
> Could some one please let me know How to find the Block Duration ?
I'm not really sure what you mean with "block duration", but if you mean
how the long the process have been blocked, I don't think this value is
available in SQL 2000.
Current Time Stamp - last_batch will give you completely irrelevant values
for idle processes. For running processes, it only tells you how they
have been running the batch, but not for how long they have been blocked.
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|||Thanks Erland !!!
I am looking for 2 thing..
1. Since How long a process is Blocking another process?
2. Since how long a process is blocked...
Any way I can find the same?
"Erland Sommarskog" wrote:

> cheri (cheri@.discussions.microsoft.com) writes:
> I'm not really sure what you mean with "block duration", but if you mean
> how the long the process have been blocked, I don't think this value is
> available in SQL 2000.
> Current Time Stamp - last_batch will give you completely irrelevant values
> for idle processes. For running processes, it only tells you how they
> have been running the batch, but not for how long they have been blocked.
>
> --
> 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
>|||Cheri
You can look at waittime in sysprocesses to see how long a process has been
blocked. Look at waittype to see if the waiting is because of a lock.
--
HTH
Kalen Delaney, SQL Server MVP
"cheri" <cheri@.discussions.microsoft.com> wrote in message
news:B9A14CB8-5637-400D-B76C-6DDC0F9C69B9@.microsoft.com...
> Thanks Erland !!!
> I am looking for 2 thing..
> 1. Since How long a process is Blocking another process?
> 2. Since how long a process is blocked...
> Any way I can find the same?
>
> "Erland Sommarskog" wrote:
>