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.
| 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. |
 |
|
|
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. |
|
|
|
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 FREEPROCCACHEJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
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... :-) |
|
|
|
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 appearJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
|
|
Question about Indexes and Statistics