is the table offline when we create an index?

Is it true that no modifiations include select statements can go through
when an index to a table is created , be it clustered or non clustered
ThanksHmm... yes, sort of.
It seems that when an index is created, SQL Server issues a row-level
exclusive lock on sysindexes, that blocks any updates or selects from
our table, until the creation of the index is completed. The blocking
probably occurs because the query optimizer needs to know what indexes
exists on our table in order to generate a query plan for the
statement.
Razvan|||Mmm... no, not necessarily.
Upon further testing, I saw that some statements may execute, even if
an index on that table is in the process of being created. It seems
that the lock on sysindexes is issued only at the end of the index
creation process. So, if the CREATE INDEX is part of an explicit
transaction that has not been comitted yet, but the index creation is
complete, then it happens like I wrote in the previous message.
Otherwise, when there is no explicit transaction (usually there isn't)
and when the index is really big, then the selects may execute (because
when the optimizer looks in the sysindexes table, it sees no evidence
of the new index, not even a lock, yet). However, certain statements
that modify the table may be blocked (for example, because they try to
get a table-level intent-exclusive lock, but the CREATE INDEX has a
shared lock on our table).
Anyway, it seems that this question is not easily answerable without
some inside knowledge of how SQL Server internally works, so it would
be better if some from MS would comment on this topic.
Razvan

is the table offline when we create an index?

Is it true that no modifiations include select statements can go through
when an index to a table is created , be it clustered or non clustered
Thanks
Hmm... yes, sort of.
It seems that when an index is created, SQL Server issues a row-level
exclusive lock on sysindexes, that blocks any updates or selects from
our table, until the creation of the index is completed. The blocking
probably occurs because the query optimizer needs to know what indexes
exists on our table in order to generate a query plan for the
statement.
Razvan
|||Mmm... no, not necessarily.
Upon further testing, I saw that some statements may execute, even if
an index on that table is in the process of being created. It seems
that the lock on sysindexes is issued only at the end of the index
creation process. So, if the CREATE INDEX is part of an explicit
transaction that has not been comitted yet, but the index creation is
complete, then it happens like I wrote in the previous message.
Otherwise, when there is no explicit transaction (usually there isn't)
and when the index is really big, then the selects may execute (because
when the optimizer looks in the sysindexes table, it sees no evidence
of the new index, not even a lock, yet). However, certain statements
that modify the table may be blocked (for example, because they try to
get a table-level intent-exclusive lock, but the CREATE INDEX has a
shared lock on our table).
Anyway, it seems that this question is not easily answerable without
some inside knowledge of how SQL Server internally works, so it would
be better if some from MS would comment on this topic.
Razvan
sql

is the table offline when we create an index?

Is it true that no modifiations include select statements can go through
when an index to a table is created , be it clustered or non clustered
ThanksHmm... yes, sort of.
It seems that when an index is created, SQL Server issues a row-level
exclusive lock on sysindexes, that blocks any updates or selects from
our table, until the creation of the index is completed. The blocking
probably occurs because the query optimizer needs to know what indexes
exists on our table in order to generate a query plan for the
statement.
Razvan|||Mmm... no, not necessarily.
Upon further testing, I saw that some statements may execute, even if
an index on that table is in the process of being created. It seems
that the lock on sysindexes is issued only at the end of the index
creation process. So, if the CREATE INDEX is part of an explicit
transaction that has not been comitted yet, but the index creation is
complete, then it happens like I wrote in the previous message.
Otherwise, when there is no explicit transaction (usually there isn't)
and when the index is really big, then the selects may execute (because
when the optimizer looks in the sysindexes table, it sees no evidence
of the new index, not even a lock, yet). However, certain statements
that modify the table may be blocked (for example, because they try to
get a table-level intent-exclusive lock, but the CREATE INDEX has a
shared lock on our table).
Anyway, it seems that this question is not easily answerable without
some inside knowledge of how SQL Server internally works, so it would
be better if some from MS would comment on this topic.
Razvan

Is the database offline as it grows data files ?

Just curious to know if the database does take some hit by going offline
when the data files grow ?Hassan
No, a database is not OFF at this time, the users will be waiting till the
growth will be completed. We should prevent from such situations
"Hassan" <Hassan@.hotmail.com> wrote in message
news:eBJAVEZyGHA.4024@.TK2MSFTNGP02.phx.gbl...
> Just curious to know if the database does take some hit by going offline
> when the data files grow ?
>
>|||I didnt know..
So if i have a 10GB database and if i have to grow it to say 100GB and it
takes 10 mins , are you saying that no users will be able to connect or
perform any work during this 10 mins ?
Will connections be timed out or will queries take longer to execute ? Can
you please comment ?
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23kEZrJayGHA.4232@.TK2MSFTNGP04.phx.gbl...
> Hassan
> No, a database is not OFF at this time, the users will be waiting till the
> growth will be completed. We should prevent from such situations
>
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:eBJAVEZyGHA.4024@.TK2MSFTNGP02.phx.gbl...
>|||Hi
If autogrow kicks in due to DB or Log space being unavailable:
Users will still be able the query the DB, assuming they are not blocked
by normal locking.
Transactions can NOT continue. Everyone doing Data modifications will be
blocked until space is available again. If the querytimeout occurs, the
specific transactions will be aborted. If the transaction that caused the
autogrow operation to occur rolls back, the autogrow rolls back.
Checkpoints will not occur until space is available again.
IO performance will be severely impacted.
Microsoft's recommendation is not to use Autogrow in a production
environment. Rather manage the space properly by pre-growing the DB and Log
in good sized chunks and only rely on autogrow as last-resort if the DBA did
not manage the space well.
If the DB or Log are grown manually, and if space is available for the
current transactions:
Users will still be able the query the DB, assuming they are not blocked
by normal locking.
Transactions can continue.
Checkpoints continues to occur.
IO performance will be severely impacted.
The severely reduced IO throughput will affect performance and may cause
queries to time-out. Growing DB's is recommended when the load is least and
in a controlled manner.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OD70MXhyGHA.4576@.TK2MSFTNGP06.phx.gbl...
>I didnt know..
> So if i have a 10GB database and if i have to grow it to say 100GB and it
> takes 10 mins , are you saying that no users will be able to connect or
> perform any work during this 10 mins ?
> Will connections be timed out or will queries take longer to execute ? Can
> you please comment ?
> Thanks
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23kEZrJayGHA.4232@.TK2MSFTNGP04.phx.gbl...
>

Is the database offline as it grows data files ?

Just curious to know if the database does take some hit by going offline
when the data files grow ?Hassan
No, a database is not OFF at this time, the users will be waiting till the
growth will be completed. We should prevent from such situations
"Hassan" <Hassan@.hotmail.com> wrote in message
news:eBJAVEZyGHA.4024@.TK2MSFTNGP02.phx.gbl...
> Just curious to know if the database does take some hit by going offline
> when the data files grow ?
>
>|||I didnt know..
So if i have a 10GB database and if i have to grow it to say 100GB and it
takes 10 mins , are you saying that no users will be able to connect or
perform any work during this 10 mins ?
Will connections be timed out or will queries take longer to execute ? Can
you please comment ?
Thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23kEZrJayGHA.4232@.TK2MSFTNGP04.phx.gbl...
> Hassan
> No, a database is not OFF at this time, the users will be waiting till the
> growth will be completed. We should prevent from such situations
>
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:eBJAVEZyGHA.4024@.TK2MSFTNGP02.phx.gbl...
>> Just curious to know if the database does take some hit by going offline
>> when the data files grow ?
>>
>|||Hi
If autogrow kicks in due to DB or Log space being unavailable:
Users will still be able the query the DB, assuming they are not blocked
by normal locking.
Transactions can NOT continue. Everyone doing Data modifications will be
blocked until space is available again. If the querytimeout occurs, the
specific transactions will be aborted. If the transaction that caused the
autogrow operation to occur rolls back, the autogrow rolls back.
Checkpoints will not occur until space is available again.
IO performance will be severely impacted.
Microsoft's recommendation is not to use Autogrow in a production
environment. Rather manage the space properly by pre-growing the DB and Log
in good sized chunks and only rely on autogrow as last-resort if the DBA did
not manage the space well.
If the DB or Log are grown manually, and if space is available for the
current transactions:
Users will still be able the query the DB, assuming they are not blocked
by normal locking.
Transactions can continue.
Checkpoints continues to occur.
IO performance will be severely impacted.
The severely reduced IO throughput will affect performance and may cause
queries to time-out. Growing DB's is recommended when the load is least and
in a controlled manner.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <Hassan@.hotmail.com> wrote in message
news:OD70MXhyGHA.4576@.TK2MSFTNGP06.phx.gbl...
>I didnt know..
> So if i have a 10GB database and if i have to grow it to say 100GB and it
> takes 10 mins , are you saying that no users will be able to connect or
> perform any work during this 10 mins ?
> Will connections be timed out or will queries take longer to execute ? Can
> you please comment ?
> Thanks
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23kEZrJayGHA.4232@.TK2MSFTNGP04.phx.gbl...
>> Hassan
>> No, a database is not OFF at this time, the users will be waiting till
>> the growth will be completed. We should prevent from such situations
>>
>>
>> "Hassan" <Hassan@.hotmail.com> wrote in message
>> news:eBJAVEZyGHA.4024@.TK2MSFTNGP02.phx.gbl...
>> Just curious to know if the database does take some hit by going offline
>> when the data files grow ?
>>
>>
>sql