Index Locks

Is it true that index locks would occur when the index's on a table are being reorganized ? Would this also exclusively lock the table ?
How serious are these Locks in performance ? Will these lead to [upd-stat] locks ?
Am encountering a lot of index locks in my db on the temp tables, Could using fillfactor on create index statement would lessen these locks ?
IDX: 2:544547798:1412364626 [[INDEX_ID]]
IDX: 2:1610018179:0 [[INDEX_ID]]
Thanks a lot.
Yes, if you are rebuilding a clustered index, your table will be locked.
Greg Jackson
PDX, Oregon
|||Will the Sqlserver automatically rebuid the index on Temp tables whenever there are lot of updates/inserts ? If it does that , and these updates/inserts are in a transaction could it eventually lead up to contention ?
Thanks!!
"Jaxon" wrote:

> Yes, if you are rebuilding a clustered index, your table will be locked.
>
> Greg Jackson
> PDX, Oregon
>
>
|||sql will update statistics. It will not rebuild the Index
Further, you can turn auto update statistics off if you really want to.
(Directions in Books On Line)
GAJ

Index Locks

Is it true that index locks would occur when the index's on a table are bein
g reorganized ? Would this also exclusively lock the table ?
How serious are these Locks in performance ? Will these lead to [upd-st
at] locks ?
Am encountering a lot of index locks in my db on the temp tables, Could usin
g fillfactor on create index statement would lessen these locks ?
IDX: 2:544547798:1412364626 [[INDEX_ID]]
IDX: 2:1610018179:0 [[INDEX_ID]]
Thanks a lot.Yes, if you are rebuilding a clustered index, your table will be locked.
Greg Jackson
PDX, Oregon|||Will the Sqlserver automatically rebuid the index on Temp tables whenever th
ere are lot of updates/inserts ? If it does that , and these updates/insert
s are in a transaction could it eventually lead up to contention ?
Thanks!!
"Jaxon" wrote:

> Yes, if you are rebuilding a clustered index, your table will be locked.
>
> Greg Jackson
> PDX, Oregon
>
>|||sql will update statistics. It will not rebuild the Index
Further, you can turn auto update statistics off if you really want to.
(Directions in Books On Line)
GAJ

Index Locks

Is it true that index locks would occur when the index's on a table are being reorganized ? Would this also exclusively lock the table ?
How serious are these Locks in performance ? Will these lead to [upd-stat] locks ?
Am encountering a lot of index locks in my db on the temp tables, Could using fillfactor on create index statement would lessen these locks ?
IDX: 2:544547798:1412364626 [[INDEX_ID]]
IDX: 2:1610018179:0 [[INDEX_ID]]
Thanks a lot.Yes, if you are rebuilding a clustered index, your table will be locked.
Greg Jackson
PDX, Oregon|||Will the Sqlserver automatically rebuid the index on Temp tables whenever there are lot of updates/inserts ? If it does that , and these updates/inserts are in a transaction could it eventually lead up to contention ?
Thanks!!
"Jaxon" wrote:
> Yes, if you are rebuilding a clustered index, your table will be locked.
>
> Greg Jackson
> PDX, Oregon
>
>|||sql will update statistics. It will not rebuild the Index
Further, you can turn auto update statistics off if you really want to.
(Directions in Books On Line)
GAJ

Index creation in SQL Server 2005 Management Studio - Page locks disabled by default

When I create an index on a table using SQL Server Management Studio, the index has page locks disabled by default. I'd prefer to have page locks enabled by default so neither I nor any other developer has to remember to manually modify. How can I accomplish this?

Thanks,

Pat Brickson

exec sp_indexoption '<TableName>', 'DisAllowPageLocks','FALSE'|||

Thank you, that solves my problem for existing tables. How can I ensure that this is the default for all new tables as well?

The CREATE INDEX statement in T-SQL enables page level locks by default but if I create via Management Studio, they're disabled by default. Why is there a discrepency?

Thanks, Pat

|||

After testing, I find that this only affects indexes that already exist on the table. I'd like to ensure that any new indexes on this table (or any existing or new table for that matter) have page locks enabled. Unfortunately this stored procedure doesn't really help me any more than just manually altering the index's options via the index properties dialog in Management Studio.

Again, I appreciate any assistance. Any other ideas?

Thanks, Pat

|||

The default settings for new indexes are hard coded in the dialog to match the defaults in SQL Server if you don't specify any options. There is no way for end users to change these defaults in the dialog.

Thanks,
Steve

|||

Maybe I misundertand you, but isn't the default to allow page locks when accessing the database? That's the default when issuing a T-SQL CREATE INDEX statement when no options are specified.

I'm really not concerned with end users changing the default. I'm more interested in ensuring that an any index created via Management Studio by a developer or DBA will have page locks enabled. Since that doesn't seem to be the default when creating in management studio, is there a way for me to specify which defaults the indexes should take?

Thanks, Pat

|||

If the dialog defaults aren't matching the default behavior in T-SQL, that's not intentional. Please file a defect report for this on http://connect.microsoft.com. Defects reported by customers via the connect site carry extra weight when the development team is prioritizing future work, including service pack work.

Be sure to mention the version of management studio you are working with and that the dialog is not defaulting to the engine default.

Thanks,
Steve

Index creation in SQL Server 2005 Management Studio - Page locks disabled by default

When I create an index on a table using SQL Server Management Studio, the index has page locks disabled by default. I'd prefer to have page locks enabled by default so neither I nor any other developer has to remember to manually modify. How can I accomplish this?

Thanks,

Pat Brickson

exec sp_indexoption '<TableName>', 'DisAllowPageLocks','FALSE'|||

Thank you, that solves my problem for existing tables. How can I ensure that this is the default for all new tables as well?

The CREATE INDEX statement in T-SQL enables page level locks by default but if I create via Management Studio, they're disabled by default. Why is there a discrepency?

Thanks, Pat

|||

After testing, I find that this only affects indexes that already exist on the table. I'd like to ensure that any new indexes on this table (or any existing or new table for that matter) have page locks enabled. Unfortunately this stored procedure doesn't really help me any more than just manually altering the index's options via the index properties dialog in Management Studio.

Again, I appreciate any assistance. Any other ideas?

Thanks, Pat

|||

The default settings for new indexes are hard coded in the dialog to match the defaults in SQL Server if you don't specify any options. There is no way for end users to change these defaults in the dialog.

Thanks,
Steve

|||

Maybe I misundertand you, but isn't the default to allow page locks when accessing the database? That's the default when issuing a T-SQL CREATE INDEX statement when no options are specified.

I'm really not concerned with end users changing the default. I'm more interested in ensuring that an any index created via Management Studio by a developer or DBA will have page locks enabled. Since that doesn't seem to be the default when creating in management studio, is there a way for me to specify which defaults the indexes should take?

Thanks, Pat

|||

If the dialog defaults aren't matching the default behavior in T-SQL, that's not intentional. Please file a defect report for this on http://connect.microsoft.com. Defects reported by customers via the connect site carry extra weight when the development team is prioritizing future work, including service pack work.

Be sure to mention the version of management studio you are working with and that the dialog is not defaulting to the engine default.

Thanks,
Steve