Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Select * into blocking select against information_schema Expand / Collapse
Author
Message
Posted Monday, September 13, 2010 10:10 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, August 17, 2014 6:50 PM
Points: 66, Visits: 1,016
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.

Post #984920
Posted Monday, September 13, 2010 10:40 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 26, 2013 12:38 PM
Points: 338, Visits: 449
TrailKing, is the second transaction running after the first transaction has completed?

Post #984935
Posted Monday, September 13, 2010 10:42 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, August 17, 2014 6:50 PM
Points: 66, Visits: 1,016
Yes - the blocked spid runs just fine after the select * into completes
Post #984938
Posted Monday, September 13, 2010 10:48 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 26, 2013 12:38 PM
Points: 338, Visits: 449
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.


Post #984942
Posted Monday, September 13, 2010 10:52 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, August 17, 2014 6:50 PM
Points: 66, Visits: 1,016
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.
Post #984945
Posted Monday, September 13, 2010 10:59 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 26, 2013 12:38 PM
Points: 338, Visits: 449
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



Post #984950
Posted Monday, September 13, 2010 11:27 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, August 17, 2014 6:50 PM
Points: 66, Visits: 1,016
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?
Post #984966
Posted Monday, September 13, 2010 11:35 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 26, 2013 12:38 PM
Points: 338, Visits: 449
The underlying table being locked is the "sysobjects" table, which feeds the INFORMATION_SCHEMA view, and others.

Post #984972
Posted Monday, September 13, 2010 11:40 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Sunday, August 17, 2014 6:50 PM
Points: 66, Visits: 1,016
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.
Post #984973
Posted Monday, September 13, 2010 11:48 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 5:51 PM
Points: 17,845, Visits: 15,797
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
Post #984977
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse