I am looking for a tool that helps make index recommendations automatically by using the new DMVs. I think DTA is great, but having to capture long traces to analyze traffic is annoying. I think there should be something out there that uses the information from the default trace in 2005 and 2008 and pumps out recommendations. This would be great. Is there enything like this??? Mike
Mike I haven't seen any DMVs that recommend so and so solution, rather I treat them as to get system information status. I suggest to refer to the BOL which is obvious documentaiton for more information specific for index fragmentation.
Thanks Satya. I think that I am not looking for fragmentation. The thing is, the default trace captures so much that you can get worst performing queries, etc. I am looking for somthing similar to DTA that will review that data and make recommendations based on workload.
No tool, but do you know this one: http://blogs.msdn.com/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx
I knew of this too. I thank you for that though. The issue with this is that it doesnt truely look at workload as DTA would. it is close, but I am hoping some good programmer can put this into a good tool. Thanks all! sorry I havent been on much lately. Been busy!
2008 does that for you. In 2005 I think it just tells you that you are missing indexes. 2008 actually creates the scripts for you. Check out codeplex.com. there are some custom projects that might be of help..Also I think DTA is more efficient than DMVs. DTA considers all SELECTs/UPDATEs/DELETEs before making a recommendation where as DMVs only looks at SELECTs.
