Posted by: Colin Smith
Perfomance, Tuning Advisor
I have a newly created SQL Server instance that is hosting one database that is not very large, about 10 gigs, and nothing else. The Server is configured to use 4Gigs or Memory and lock the pages in memory. The server is running SQL Server 2005 and I am seeing a large number of cxpacket waits. I ran a profile and just used the standard profiler template. I then ran that through the Database Tuning Advisor and found 10 indexes and a large number of statistics that are recommended. The Tuning Advisor says that by implementing these changes I will see a 93% improvement in perfomance. I plan on working with the application vendor and hopefully implementing at least some of the suggestions in order to resolve the issue. My hope is that this will succesfully resolve the issue and that the core of the issue is just poor design and planning on the vendors part.