July 13, 2017 at 2:27 am
Hi
Last night my SQL monitoring tool reported blocking on a database. When I took a look this morning, the blocking was due to Schema Stability locks on multiple tables.
What I don't understand is that all the threads only seemed to be SELECT'ing and that the objects that the locks were on, followed the naming convention
Database.Schema.DF__TMP_tablename__First5lettersofcolumn__8characteralphnumeric
for Example
Dbase1.dbo.DF__TMP_Users_ADDRE_5542F860
Can anyone explain what those objects are? I read that the DF is the default constraint but that constraint does not appear on the properties of the column.
Cheers
Alex
July 13, 2017 at 8:54 am
That's a system-generated constraint name for an implicit DEFAULT constraint, such as:
USE tempdb;
CREATE TABLE table1 ( column1 int DEFAULT 0 );
EXEC sp_help 'table1';
DROP TABLE table1;
To help avoid blocking, don't load tables using SELECT ... INTO new_table_name. Instead, create the table that way using 0 zeros, then load the table using hints to allow the table load to still be bulk logged. For example:
SELECT TOP (0) ... INTO dbo.new_table_name FROM {some complex query}
INSERT INTO dbo.new_table_name WITH (TABLOCK) SELECT ... FROM {some complex query}
Edit: The reason this can matter is that when creating a table, SQL holds locks on its internal metadata tables.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply