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 |
|
Shell
Starting Member
2 Posts |
 Posted - 2009-08-04 : 14:31:50
|
| Hi,Ours is a Asp.net 2.0 web application using Sql server 2005 as backend. We have occasionally seen Sql command timeout errors and application getting slow intermittenly. I am working on fixing these issues. I intend to use Database engine tuner for indexes recommendations. I collected some sample traces from the production server and used them as workload for Sql server 2005 DTA. The DTA came up with several indexes and Statistics recommendation. My question is since my production server has Auto-create and Auto-update statistics on.1. Why is DTA recommending me to create so many statistics.2. Is it okay to create the recommended statistics?Thanks for your suggestions,S |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2009-08-04 : 15:10:59
|
| Auto-create Statistics generally only creates statistics on single columns. I am betting that DTA came up with several multiple column combinations for statistics.It should be OK to create the statistics in a test environment. Be sure to do a load test before and after, to see if there is any actual benefit. As good as it is, DTA can be wrong sometimes. |
 |
|
|
Shell
Starting Member
2 Posts |
Posted - 2009-08-04 : 15:18:04
|
| Thanks for the reply. You are right the statistics DTA recommended are all with multiple columns, so that clears the confusion.Do we have specific disadvantages of creating statistics just like we have for indexes i.e. slower inserts/update and space occupied by them?Also, For the indexes recommendations by DTA I am only choosing the ones for tables that I think are used most often. Can I use the same theory while choosing Statistics?Thanks,S |
|
|
|
|
|
|
Create Statistics