Space in SQL Server 2008 R2 slows performance

Question

I ran into a rather strange problem. I 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 works pretty fast in 2 seconds. When formatting the code, I accidentally added an extra SPACE between AND + EXISTS , so the query looks 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' ) ) 

As a result, the request takes 13 seconds. I am running SQL Server in a sandbox sandbox, and I even tested it on a different sandbox. I also checked the completed request in the profiler, the reading has not changed much, but the processor time is up.

If it is not too strange, it becomes more strange. When I change SELECT * FROM to SELECT Field1, ... FROM at the top of the query, it takes more than 3 minutes to complete.

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

Edit: Following the recommendations below, you will see that queries are "space-sensitive." However, I still don't know why SELECT * FROM is much faster than SELECT Field1, ... FROM

Answer 1

I would suggest that you are dealing with two different cached query plans:

• You ran the query once with a specific set of parameters. SQL Server determined the appropriate query plan and saved that query query as "Automatically parameterized", in other words, replacing the values ​​you provided with the variables for the purposes of the query plan.
• Then you run the same query again with different parameters. The request receives automatic parameterization and corresponds to the existing cached tariff plan (even if this query plan may not be optimal for new parameters!).
• Then you run this second query again using the extra space. This time, a request with automatic parameterization does NOT match anything in the cache and therefore gets its own plan based on THIS set of parameters (remember that the first plan was for a different set of parameters). This query plan ends faster (or slower).

If this is really an explanation, you can get away from the effect by running DBCC FREEPROCCACHE : http://msdn.microsoft.com/en-us/library/ms174283.aspx

There is a lot of material on automatic parameterization, I personally liked the Gail Show 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 deleted or normalizes the space before saving the plan with the parameters with automatic parameterization, I would have suggested so, but this whole answer claims that it does not!)

Answer 2

This can be very well related to caching issues. When you change your request, even to the minimum space, the cached execution plan of the previous request will no longer be used. If my answer is correct, you should see the same thing (2 seconds) when you execute the bottom query a second time ...
Only my 2 cents

You can clear the cache with the following two statements:

 DBCC FreeProcCache DBCC DROPCLEANBUFFERS