Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Question about Indexes and Statistics

Author  Topic 

xalpha
Starting Member

13 Posts

Posted - 2013-05-24 : 08:07:43
Hello,

I just had a curious behaviour related to indexes and statistics. A big query was a bit slow and I decided to put the query in the Database Engine Tuning Advisor.

The suggestion was to create a few indexes and statistics. I followed the suggestion but after that the query was not really faster. Then I deleted exactly (and only) the indexes and statistics that were created a few minutes before to get the original condition.

After that the Query was extremly slow und not useable for the productive use. Before the approach of tuning the query took 4 seconds. After all I canceled the same query after a few minutes...

After a recreation of the new indexes and statistics everything worked fine again.

Has anyone a idea about that strange behavior?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-24 : 09:53:44
It is hard to say anything specific with only the information you have provided. However, if you have the opportunity to do so:

1. Compare the before and after query plans. Are they the same, or are they different?
2. If they are different, are the faster queries using your new indexes? If they are, obviously the new indexes are helping.
3. If the query plans are the same, and you are observing slowness in one, I don't know for certain what might be causing that - the query plan generation might be one reason. To eliminate that, run each a few times in succession.

In general, while the tuning advisor can sometimes give you good hints and suggestions, that is not always the case. So you need to look at your queries and see if they really need those indexes that it is suggesting.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-05-24 : 11:11:05
In addition, SET STATISTICS IO ON and look at the # of reads before and after any index changes.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-05-25 : 11:32:14
Did you clear the cache after you deleted the Indexes - i.e using DBCC FREEPROCCACHE

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

xalpha
Starting Member

13 Posts

Posted - 2013-05-25 : 11:33:05
Thank you for the suggestions!

I will try to continue analysing this behavior the next days. Maybe I can reconstruct the old conditions with the backup. The query plans are a good hint to find differences.

For me it is a bit wondering that add a new index and then delete it not ends in the former condition. As far as I know in general it should be this way. It is again a mystic side of ms sql... :-)
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-05-27 : 02:48:33
It is important when applying this sort of analysis - to test consistently. In other works - clear buffer , clear cache .
If this consistency is not applied skewed results appear

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -