July 17, 2004 at 8:06 pm
HI,
This was a question that I was asked and I was hoping to get some input from you guys. Two tables that are roughly 12 million and 3 million in size on a sql server 2000 box. Identical integer fields that are not null and have a default value of 0 were added to these tables. As far as I know these fields were not added to the indexes or directly used in the queries. Immediately the queries running against these tables tripled in time. They defraged the indexes and finally recreated/repopulated both tables. It did not improve the query speeds.
Any ideas?...This is all I currently know about the situation, but I was hoping someone had stumbled across this before.
Thanks
PK
July 17, 2004 at 9:33 pm
Have you tried updating the statistics and recompiling the database object (i.e: view, stored proc, etc.) in which the query resides to see if that improves performance?
I'm thinking that maybe SQL Server might be using old statistics and/or execution plan possibly brought about the intro of the new field.
Probably someone more experienced might have a more definitive answer on the performance degradation ...
JP
July 19, 2004 at 7:45 am
Two points that will affect query speeds but maybe not to the extent that you are saying...
The queries will take longer if there are more physical disk reads to do. An extra 4 bytes might mean that fewer records are stored in each table extent. If the record length is large for these tables, then it could have increased the space used by tables significantly. Try doing DBCC SHOWCONTIG to see if your average bytes free per page has gone up (you have lost a whole record from each page).
Also, I have heard that index seeks are carried out if all the columns referenced in a query (in the columns SELECTed as well as the WHERE block) are in an index. If the new column has been added to the SELECTed columns it might have switched the queries to using an index scan rather than an index seek. Show the Execution Plan in query analyser to see how the queries are using the indexes.
You could run the index tuning wizard to see if it makes any suggestions using the new column.
Hope this gives you some ideas
Peter
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply