April 21, 2015 at 8:18 am
Although this is currently occurring in SQL 2012, I have also seen it in 2008 R2, so I am posting here in case anyone can help. I am seeing a very strange, sporadic performance issue when doing the following within a stored procedure:
CREATE TABLE #tmp (col/datatype, col/datatype, etc)
INSERT #tmp
SELECT blah, blah, blah FROM blah, blah, blah
The SELECT itself doesn't matter - it is not wide, has no crazy datatypes, is optimized well, and returns about 10k rows in a second or two. But what happens next will amaze you - sp_who2 shows the spid hanging on the INSERT for minutes. CPUTime will increase but DiskIO does not. Finally, SQL remembers what INSERT means and finishes up.
Oddly, using SELECT INTO #tmp syntax seems to work around the issue. Also, using the hint OPTION (OPTIMIZE FOR UNKNOWN) (as described in this Connect issue) "fixes" the problem, although it shouldn't.
To summarize:
INSERT #tbl SELECT x, y, z… - very slow
SELECT x, y, z INTO #tbl – very fast
INSERT #tbl SELECT x, y, z… OPTION (OPTIMIZE FOR UNKNOWN) – very fast
Given one of the workarounds mentioned above, it's almost as though SQL is trying to validate the dataypes of the entire SELECT before doing the insert. On the other hand, the OPTIMIZE FOR UNKNOWN "fix" makes no sense, since the SELECT always returns instantly no matter what.
Is it something that tends to happen under load? I am not a DBA, although I have access to see just about everything there is to see on the instance. I am not seeing lots of contention for tempdb resources, but it's very possible I could be looking in the wrong place. Is there something else I can look for?
Thanks in advance.
April 21, 2015 at 8:26 am
Hi,
I usually create the #temp tables in the following way:
create table #tmp(
ID int identity(1,1) prmiary key,
col1 data type c1,
col2 data type c2,
...
coln data type cn)
With this the primary key satisfies the best practices for a primary key, and the addition is always at the end of the B-tree for the clustered index on ID. I've never had issues with this approach.
Igor Micev,My blog: www.igormicev.com
April 21, 2015 at 8:28 am
Just a curious shot in the dark on this... what is the default collation of the database you're select from and what is the default collation of TempDB?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 21, 2015 at 8:50 am
Jeff Moden (4/21/2015)
Just a curious shot in the dark on this... what is the default collation of the database you're select from and what is the default collation of TempDB?
Interesting question, but both the user db and tempdb are SQL_Latin1_General_CP1_CI_AS.
Other things to consider:
1) this behavior is sporadic
2) the behavior seems to be related to the method of INSERT, not the SELECT itself
3) in addition to the other workarounds mentioned, recompiling the SP seems to alleviate this. But it shouldn't/
April 21, 2015 at 9:39 am
Select statement is always fater than Insert statement.
This looks like optimizer issue.
Extract from Microsoft:
OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )
Instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and not during query execution.
UNKNOWN
Specifies that the query optimizer use statistical data instead of the initial value to determine the value for a local variable during query optimization.
I am curious, instead of temp table if we create create physical table and try your query will improve the performance? Just both way give it a try 1. How you are doing and 2. what Igor has suggested.
i.e.
INSERT tbl1 SELECT x, y, z…
AND
create table tbl1(
ID int identity(1,1) prmiary key,
col1 data type c1,
col2 data type c2,
...
coln data type cn)
And let's see the difference.
HTH
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
April 21, 2015 at 12:12 pm
free_mascot (4/21/2015)
Select statement is always fater than Insert statement.
This is interesting - I had never heard that. Why would INSERT...SELECT take noticeably longer than SELECT INTO?
free_mascot (4/21/2015)
This looks like optimizer issue.Extract from Microsoft:
OPTIMIZE FOR ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )
Instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and not during query execution.
UNKNOWN
Specifies that the query optimizer use statistical data instead of the initial value to determine the value for a local variable during query optimization.
I am curious, instead of temp table if we create create physical table and try your query will improve the performance? Just both way give it a try 1. How you are doing and 2. what Igor has suggested.
i.e.
INSERT tbl1 SELECT x, y, z…
AND
create table tbl1(
ID int identity(1,1) prmiary key,
col1 data type c1,
col2 data type c2,
...
coln data type cn)
And let's see the difference.
HTH
I will try your suggestions, but since I recompiled the SP I can no longer reproduce the behavior, so I will have to wait until this breaks again.
The behavior is not occurring in my non-prod environments either, but just for grins, I did try Igor's suggestion (creating the #tmp table with a declared primary key). The query plan was identical except for a Clustered Index Insert that wasn't there before 🙂
April 21, 2015 at 8:23 pm
joepositive (4/21/2015)
Jeff Moden (4/21/2015)
Just a curious shot in the dark on this... what is the default collation of the database you're select from and what is the default collation of TempDB?Interesting question, but both the user db and tempdb are SQL_Latin1_General_CP1_CI_AS.
Other things to consider:
1) this behavior is sporadic
2) the behavior seems to be related to the method of INSERT, not the SELECT itself
3) in addition to the other workarounds mentioned, recompiling the SP seems to alleviate this. But it shouldn't/
To be honest, that's why I almost always use SELECT/INTO to create a Temp Table. And the old wives table about it locking up TempDB hasn't been true since SP1 of SQL Server 6.5. Yep... it still takes a couple of shared locks that will tick off anyone trying to expand a folder in Object Explorer, but it has very little effect, otherwise. Just don't use it across linked servers. It still tends to lockup the source server especially if it's an SQL Server. Not sure how or why it happens, though.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 22, 2015 at 2:14 am
Recompile worked that means my guess is correct it is optimizer issue. This is also similar to parameter sniffing if you are using any parameter in stored procedure.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply