CODEWITHSUNDEEP
sql-serverperformance
Andre
Andre

Reputation: 1902

Space in SQL Server 2008 R2 slows down performance

I have run into a rather weird problem. I have created the following query in SQL Server

SELECT * FROM leads.BatchDetails T1
INNER JOIN leads.BatchHeader h ON T1.LeadBatchHeaderId = h.ID
WHERE 
 T1.LeadBatchHeaderId = 34  
  AND (T1.TypeRC = 'R' OR h.DefaultTypeRC = 'R')    
 AND EXISTS (SELECT ID FROM leads.BatchDetails T2 where 
            T1.FirstName = T2.FirstName AND 
            T1.LastName = T2.LastName AND 
            T1.Address1 = T2.Address1 AND
            T1.City = T2.City AND                     
            T1.[State] = T2.[State] AND                     
            T1.Zip5 = T2.Zip5 AND                     
            T1.LeadBatchHeaderId = T2.LeadBatchHeaderId 
            and t2.ID < t1.ID
            AND (T2.TypeRC = 'R' OR h.DefaultTypeRC = 'R' )
     )

It runs decently fast in 2 seconds. When formatting the code I accidently added an additional SPACE between AND + EXISTS so the query look like this.

SELECT * FROM leads.BatchDetails T1
INNER JOIN leads.BatchHeader h ON T1.LeadBatchHeaderId = h.ID
WHERE 
 T1.LeadBatchHeaderId = 34  
  AND (T1.TypeRC = 'R' OR h.DefaultTypeRC = 'R')    
 AND  EXISTS (SELECT ID FROM leads.BatchDetails T2 where 
            T1.FirstName = T2.FirstName AND 
            T1.LastName = T2.LastName AND 
            T1.Address1 = T2.Address1 AND
            T1.City = T2.City AND                     
            T1.[State] = T2.[State] AND                     
            T1.Zip5 = T2.Zip5 AND                     
            T1.LeadBatchHeaderId = T2.LeadBatchHeaderId 
            and t2.ID < t1.ID
            AND (T2.TypeRC = 'R' OR h.DefaultTypeRC = 'R' )
     )

All of a sudden the query takes 13 seconds to execute. I am running SQL Server in an isolated sandbox environment and I have even tested it on a different sandbox. I also checked the executed query in profiler, the reads are virtually the same, but CPU time is way up

If this is not weird enough, it's getting weirder. When I change SELECT * FROM to SELECT Field1, ... FROM at the top of the query the execution takes over 3 minutes.

I have been working with SQL Server for 10 years and never seen anything like this.

Edit: After following the suggestions below it appears that queries are "white-space-sensitive". However I still have no idea why the SELECT * FROM is so much faster than SELECT Field1, ... FROM

Upvotes: 4

Views: 1345

Answers (2)

Tao
Tao

Reputation: 13996

I would guess that you're dealing with two different cached query plans:

  • You you ran the query once, with a certain set of parameters. SQL Server determined an appropriate query plan, and stored that query plan "Auto-parametrized", in other words replacing the values you provided with variables, for the purposes of the query plan.
  • You then ran the same query again, with different parameters. The query gets auto-parameterized, and matches the existing cached query plan (even though that query plan may not be optimal for the new parameters provided!).
  • You then run this second query again, with your extra space. This time, the auto-parametrized query does NOT match anything in the cache, and therefore gets its own plan based on THIS set of parameters (remember, the first plan was for a different set of parameters). This query plan happens to end up faster (or slower).

If this is truly the explanation, you should be able to make the effect go away, by running DBCC FREEPROCCACHE: http://msdn.microsoft.com/en-us/library/ms174283.aspx

There's lots of stuff on auto-parameterization out there, I personally liked Gail Shaw's series:

http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/ http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/ http://sqlinthewild.co.za/index.php/2008/05/22/parameter-sniffing-pt-3/

(for the record, I have no idea whether SQL Server eliminates/normalizes whitespace before storing an auto-parameterized query plan; I would have assumed so, but this entire answer asssumes that it doesn't!)

Upvotes: 4

Mr47
Mr47

Reputation: 2655

This might very well be related to caching issues. When you change your query, even by as little as a space, the cached execution plan of your previous query will no longer be used. If my answer is correct, you should see the same (2 seconds) performance when you run the bottom query for the second time...
Just my 2 cents

You could flush the cache with the following two statements: 

DBCC FreeProcCache
DBCC DROPCLEANBUFFERS

Upvotes: 3

Related Questions