June 15, 2011 at 11:28 am
I am running on a 2008 R2 server that has two databases on it. I ran a SELECT TOP 0 * INTO that sent the new table into the other database, and it has hung thorougly. I cancelled the query after 15 minutes, but it is still listed as suspended with a WRITELOG wait. I then tried killing the session, and it still is suspended.
I am not the DBA, so I don't have all the info at my fingertips. But did I do something that ought not to have been done?
Kevin
--
Please upgrade to .sig 2.0
June 15, 2011 at 12:36 pm
Kevin for me, to get a table based on select into, I usually use a WHERE statement that will guarantee no rows:
SELECT * INTO ...
WHERE 1=2
that builds my structure, and I haven't seen it hang before;
i'm wondering if the TOP (without an order by?) is banging a MillionBillionRowTable before finally applying the TOP?
Lowell
June 15, 2011 at 7:09 pm
Something I had clipped into OneNote some time from some where, no idea who or I'd thank them/give them credit here..
The optimizer knows that TOP 0 and TOP 0 PERCENT never return any rows and replaces any query plan with a TOP 0 with a constant scan:
SELECT TOP 0 * FROM T
|--Constant Scan
The optimizer also knows that TOP 100 PERCENT always returns all rows and removes the top operator from the query plan:
SELECT TOP 100 PERCENT * FROM T
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[T].[TA]))
June 16, 2011 at 12:14 am
Are there other users working in the database?
A SELECT INTO puts a table lock on each involved tables in your query to prevent schema alterations during the duration of query.
If other users are working in the database, your query will have to wait until the other are finished.
Since you are using TOP(0) to get the schema only, I suggest you put a WITH (NOLOCK) on each table in your query and try again. Normally, WITH (NOLOCK) can return dirty reads in your query but you are not interested in rows now, only schema.
N 56°04'39.16"
E 12°55'05.25"
June 16, 2011 at 5:19 am
SwePeso (6/16/2011)
Are there other users working in the database?A SELECT INTO puts a table lock on each involved tables in your query to prevent schema alterations during the duration of query.
If other users are working in the database, your query will have to wait until the other are finished.
Since you are using TOP(0) to get the schema only, I suggest you put a WITH (NOLOCK) on each table in your query and try again. Normally, WITH (NOLOCK) can return dirty reads in your query but you are not interested in rows now, only schema.
That's and old wives tales. It WAS true in sql 6 or 6.5 but was fixed in sql 7. Since we're talking about sql 10 I think this is not the issue.
June 16, 2011 at 5:20 am
mark.allen 70725 (6/15/2011)
Something I had clipped into OneNote some time from some where, no idea who or I'd thank them/give them credit here..The optimizer knows that TOP 0 and TOP 0 PERCENT never return any rows and replaces any query plan with a TOP 0 with a constant scan:
SELECT TOP 0 * FROM T
|--Constant Scan
Cool trick about the top 0, I didn't know that change int he plan (always used where 1 = 0 VS TOP 0)
June 16, 2011 at 8:41 am
The execution plan comes out the same whether or not you use TOP (0) or Where 1 = 0.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply