Query Optimizer: concerning behavior

Last Post 03 Dec 2012 10:12 PM by peter. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages Not Resolved
nikhilvb
New Member
New Member

--
11 Aug 2012 04:09 PM
I am using SQL Server 2008 R2. I have a query that runs in under one second. However, if I add one space character (" ") at the end of the query or any where in between the query, the query takes a lot longer (approx. 55 seconds) to execute. I don't understand why the optimizer would do that. By adding the space character, I have not altered the query in any way (except increased its length by one character).

I even cleared the Query Plan cache and tried to follow the same steps again. I got the exact same result i.e. the first query executed in under a second. After adding one space character, it took approx. 55 seconds.

Any suggestions / help would be really appreciated.

Thanks.
- Nikhil.
gunneyk
New Member
New Member

--
11 Aug 2012 04:55 PM
Adding any charachter including a space will give you a new query plan since the optimizer basically compares the string to see if a plan already exists. Each time a new plan is generated it has the potential to come up with a different plan than the last one depending on several factors. It is unusual that a single space with nothing else changing would come up with two different plans but it is not outside the realm of possability. Was the query parameterized?
nikhilvb
New Member
New Member

--
12 Aug 2012 06:59 AM
Thanks for the reply. The query was not parameterized. That's what's concerning me. Why would only one space character change the query optimizer behavior to such an extent.
gunneyk
New Member
New Member

--
12 Aug 2012 10:38 AM
The space itself has nothing to do with it other than it will not match the previous plan. Things such as how many processors are used, how much memory etc. are factored in each and every time you run a query. Conditions such as how busy the server is, how much memory and such can have an effect on if the query is run in parallel and if so how many cores it uses. It's possible one was a single threaded operation and the other was parallel. But without knowing much more about the query, conditions at the time, the server etc. it is impossible to tell why it took so log. It could even have had an exact query plan but might have been blocked when it ran.
peter
New Member
New Member

--
03 Dec 2012 10:12 PM
The overall performance of your database can be greatly improved by tuning a set of most expensive or most often executed queries. In this article, I will look at some of the techniques you can employ to identify and tune the most expensive and worst performing queries on your server.
follow the below link for more detail
http://blog.sqlauthority.com/2009/0...imization/

You can also try out some tools mentioned in other thread also which will help you to improve your server performance as well
have a look at this article...

http://download.cnet.com/Lepide-SQL...61147.html


Acceptable Use Policy
---