Select * into blocking select against information

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Select * into blocking select against...

17 posts, Page 1 of 2

1 2 »»

Select * into blocking select against information_schema

Rate Topic

Display Mode

Topic Options

Author

Message

TrailKing

Posted Monday, September 13, 2010 10:10 AM

Valued Member

Group: General Forum Members
Last Login: Friday, June 14, 2013 1:19 PM
Points: 66, Visits: 972

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

Tim Parker

Posted Monday, September 13, 2010 10:40 AM

Old Hand

Group: General Forum Members
Last Login: Friday, May 10, 2013 8:23 PM
Points: 338, Visits: 443

TrailKing, is the second transaction running after the first transaction has completed?

BigGrin

Post #984935

TrailKing

Posted Monday, September 13, 2010 10:42 AM

Valued Member

Group: General Forum Members
Last Login: Friday, June 14, 2013 1:19 PM
Points: 66, Visits: 972

Yes - the blocked spid runs just fine after the select * into completes

Post #984938

Tim Parker

Posted Monday, September 13, 2010 10:48 AM

Old Hand

Group: General Forum Members
Last Login: Friday, May 10, 2013 8:23 PM
Points: 338, Visits: 443

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.

BigGrin

Post #984942

TrailKing

Posted Monday, September 13, 2010 10:52 AM

Valued Member

Group: General Forum Members
Last Login: Friday, June 14, 2013 1:19 PM
Points: 66, Visits: 972

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

Tim Parker

Posted Monday, September 13, 2010 10:59 AM

Old Hand

Group: General Forum Members
Last Login: Friday, May 10, 2013 8:23 PM
Points: 338, Visits: 443

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

BigGrin

Post #984950

TrailKing

Posted Monday, September 13, 2010 11:27 AM

Valued Member

Group: General Forum Members
Last Login: Friday, June 14, 2013 1:19 PM
Points: 66, Visits: 972

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

Tim Parker

Posted Monday, September 13, 2010 11:35 AM

Old Hand

Group: General Forum Members
Last Login: Friday, May 10, 2013 8:23 PM
Points: 338, Visits: 443

The underlying table being locked is the "sysobjects" table, which feeds the INFORMATION_SCHEMA view, and others.

BigGrin

Post #984972

TrailKing

Posted Monday, September 13, 2010 11:40 AM

Valued Member

Group: General Forum Members
Last Login: Friday, June 14, 2013 1:19 PM
Points: 66, Visits: 972

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

SQLRNNR

Posted Monday, September 13, 2010 11:48 AM

SSCoach

Group: General Forum Members
Last Login: Today @ 12:32 AM
Points: 18,855, Visits: 12,439

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 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #984977

« Prev Topic | Next Topic »

17 posts, Page 1 of 2

1 2 »»

Permissions