-1

http://pastebin.com/cx4hFSYb I have a issue with a query that inserts data into multiple temp table ( total 4 ) from the select statement, suddenly it is slow when I try using temp variable it works fast, may be because of no logging.

I do not have the plan when the query used to perform well but I can provide the bad plan that running slow now. I have updated the stats on all tables. As a temporary workaround I have update the procedure with query hint FORCE ORDER.

Below is the actual execution plan:

StatsIO:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 78 ms, elapsed time = 82 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
Table 'Test16'. Scan count 0, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
Table 'DP_CrossReference'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table '#ADD171C4'. Scan count 0, logical reads 21, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(10 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 3 ms.
Table '#ABE92952'. Scan count 0, logical reads 63, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test1'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test2'. Scan count 0, logical reads 20, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#ADD171C4'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(31 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 3 ms.
SQL Server parse and compile time: 
   CPU time = 65 ms, elapsed time = 65 ms.
Table '#Temp1______________________________________________________________________________________________________________00000002AF38'. Scan count 0, logical reads 25003, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test3'. Scan count 0, logical reads 36357, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test4'. Scan count 12108, logical reads 36324, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test5'. Scan count 0, logical reads 24216, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#ABE92952'. Scan count 0, logical reads 322880, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test6'. Scan count 0, logical reads 322880, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test7'. Scan count 0, logical reads 322880, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test8'. Scan count 322880, logical reads 645760, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test9'. Scan count 4045, logical reads 11162358, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test10'. Scan count 7780, logical reads 48235, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test11'. Scan count 1, logical reads 3700, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(12119 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 67719 ms,  elapsed time = 70219 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

(12119 row(s) affected)
Table '#Temp1______________________________________________________________________________________________________________00000002AF38'. Scan count 1, logical reads 155, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 807 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 3 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Can anyone please help me on the reason why a plan or query performance is slow.

  • We would just be speculating without the query, execution plan and statistics IO output. Please update your post with this info. – Tara Kizer Aug 11 '16 at 14:54
  • thanks Tara. Can you guide in attaching the files ? – Robin16 Aug 11 '16 at 15:11
  • Can anyone help me how to upload the execution plan and stats IO ? – Robin16 Aug 11 '16 at 16:46
  • You can use pastebin and link it here. Also, a picture of execution plan is useless. We need the actual plan xml (use pastebin and link it here). – Kin Aug 11 '16 at 18:50
0

You haven't not provided query plan for following portion of IO statistics:

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 3 ms.
SQL Server parse and compile time: 
   CPU time = 65 ms, elapsed time = 65 ms.
Table '#Temp1______________________________________________________________________________________________________________00000002AF38'. Scan count 0, logical reads 25003, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test3'. Scan count 0, logical reads 36357, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test4'. Scan count **12108, logical reads 36324,** physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test5'. Scan count 0, logical reads 24216, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#ABE92952'. Scan count 0, logical reads 322880, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test6'. Scan count 0, logical reads 322880, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test7'. Scan count 0, logical reads 322880, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test8'. Scan count 322880, logical reads 645760, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test9'. Scan count 4045, logical reads 11162358, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test10'. Scan count 7780, logical reads 48235, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Test11'. Scan count 1, logical reads 3700, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(12119 row(s) affected)

Items with big values in Scan count and logical reads are red flags, such as Table scans and Key Lookups.

There can be because of bad indexing, statistics, etc. Wild guess: when you use Temp Table Variable SQL comes up with better query plan.

  • Salva, I have renamed the table names for security reasons but all the stats were updated and we also use Filtered stats for one table here it's Test9....I found this is an issue with stale stats on temp table...after updating the stats on temp table...query runs very fast.. here's the link to Paul White's blog on temporary table caching in stored procedure.. sqlkiwi.blogspot.com/2012/08/… – Robin16 Aug 12 '16 at 15:32
  • but when i run the query with "drop temp table, create table and insert into temp table" ...its slow again....after the initial run ( which is slow ) and updating the stats on temp table..the query completes in 2 seconds... Please let me know if i'm missing anything. – Robin16 Aug 12 '16 at 15:37
  • Salva, yes we tried running with temp variable and it runs fine but we do not want to use temp variable when records count is more than 1000...also, main question here why the query is performing slow when it used to run fine previously.... – Robin16 Aug 12 '16 at 16:11
  • Actually i overlooked at temp table stats...it is not the cause of the issue.. – Robin16 Aug 12 '16 at 18:24
  • You have to look at plan differences between Temp tables and temp table variables. If it is possible, use staging tables. – Slava Murygin Aug 12 '16 at 23:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.