SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Question about Indexes and Statistics
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

xalpha
Starting Member

13 Posts

Posted - 05/24/2013 :  08:07:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3588 Posts

Posted - 05/24/2013 :  09:53:44  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 05/24/2013 :  11:11:05  Show Profile  Visit russell's Homepage  Reply with Quote
In addition, SET STATISTICS IO ON and look at the # of reads before and after any index changes.

Edited by - russell on 05/24/2013 11:11:28
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2046 Posts

Posted - 05/25/2013 :  11:32:14  Show Profile  Visit jackv's Homepage  Reply with Quote
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 - 05/25/2013 :  11:33:05  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2046 Posts

Posted - 05/27/2013 :  02:48:33  Show Profile  Visit jackv's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000