SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Select * into blocking select against information_schema


Select * into blocking select against information_schema

Author
Message
TrailKing
TrailKing
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 1016
Hello Everyone - I have a blocking issue and need a little bit of help from the experts. My description of the issue is below and if furthur information is needed please let me know.

Our application allows customers to build sql ad-hoc sql statments to query information and to create a table from the result of the query. An example of that type of query is below.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * INTO MONTY2 FROM (SELECT "rl63_at_405", "at_176", SUM(dt."rl1267_at_21034") "rl1267_at_21034" FROM (SELECT rl63_activities.name "rl63_at_405", OTBD.name "at_176", rl1267_ELksVw.UniqueLinkClicks "rl1267_at_21034" FROM otbd_forms OTBD WITH (NoLock) LEFT OUTER JOIN action rl63_activities WITH (NoLock) ON (rl63_activities.activity_id = OTBD.activity_id) INNER JOIN ELksVw rl1267_ELksVw WITH (NoLock) ON (rl1267_ELksVw.email_id = OTBD.outbound_id) WHERE (OTBD.outbound_id = 19801)) "DT" GROUP BY "rl63_at_405", "at_176" ) createDT

The blocking occurs when the below statement is run after the select * into.

SELECT c.column_name, c.data_type, c.character_maximum_length, c.numeric_precision, c.is_nullable, c.numeric_scale, c.column_name, ISNULL(( SELECT 'YES' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pkc INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE pkcc ON pkcc.constraint_name = pkc.constraint_name WHERE pkc.table_name = c.table_name AND pkc.constraint_type = 'PRIMARY KEY' AND pkcc.column_name = c.column_name), 'NO') AS 'is_primary_key' FROM INFORMATION_SCHEMA.COLUMNS c WHERE c.table_name = 'Bobo'

Can someone explain to me why this blocking is occuring? I know that somehow the creation of the table is involved but can not pinpoint why. I tried reproducing the error on my test machine but no luck.


I have not been able to find out why the blocking is happening. Any help would be appreciated.
Tim Parker
Tim Parker
SSC-Addicted
SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)

Group: General Forum Members
Points: 412 Visits: 460
TrailKing, is the second transaction running after the first transaction has completed?

:-D
TrailKing
TrailKing
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 1016
Yes - the blocked spid runs just fine after the select * into completes
Tim Parker
Tim Parker
SSC-Addicted
SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)

Group: General Forum Members
Points: 412 Visits: 460
Ok, in our shop we do the following when working with large tables and transactions.

--This section strictly creates the table, and nothing else.
SELECT ColumnOne, ColumnTwo, ColumnThree... INTO TableName_Destination
FROM TableName_Source
WHERE 1 = 0

--This section copies the required data into the newly created table, with no metadata locks.
INSERT INTO TableName_Destination
SELECT ColumnOne, ColumnTwo, ColumnThree...
FROM TableName_Source

The reason for the (WHERE 1 = 0) is to reduce locking on the metadata tables. The typical locking required when using (WHERE 1= 0) on the metadata tables is less than a second. This should alleviate your problems.

:-D
TrailKing
TrailKing
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 1016
Thanks CoolWebs for the reply but can you explain to me why the blocking is occuring. I have tried researching the cause but nothing in google or BOL has pointed me in the right direction.
Tim Parker
Tim Parker
SSC-Addicted
SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)

Group: General Forum Members
Points: 412 Visits: 460
Before I answer without all of the correct facts, please see the link to a similar discussion below. It's very similar to your situation even though it's dealing with temporary tables.


http://www.sqlservercentral.com/Forums/Topic984394-391-1.aspx#bm984500

:-D
TrailKing
TrailKing
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 1016
Took a look at the link and although it is similar to my issue the posters did not explain the reasons for their recommendations. What I am looking for is an explanantion of why the information_schema is locked by the select * into statement?
Tim Parker
Tim Parker
SSC-Addicted
SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)SSC-Addicted (412 reputation)

Group: General Forum Members
Points: 412 Visits: 460
The underlying table being locked is the "sysobjects" table, which feeds the INFORMATION_SCHEMA view, and others.

:-D
TrailKing
TrailKing
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 1016
Outstanding!! CoolWeb would you by any chance have a link to something so I can read up on this behavior. This issue has driven me bonkers for about 3 weeks trying to research and recreate.
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33882 Visits: 18560
A couple of things looking at this would lead me to access patterns for the underlying objects.


I would do the inner joins first and the the outer joins.

You also have a join on a view which may be accessing several more tables.

Is there any way you could add a where clause to the first subquery (not the innermost subquery).


Also, could you provide DDL and sample data so people could reproduce your issue? An execution plan of the first query would prove most helpful here. I imagine there are plenty of scans and potentially locks happening in this query (don't believe everything you have heard about the NOLOCK hint - NOLOCK is usually a bad thing).



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search