I'm pretty new to this performance tuning with Waits and Queues thing - fascinating, but also not always all that intuitive...
Right now, a customer of mine has a SQL Server 2008 64-bit Enterprise Edition, currently with 16 GB of RAM assigned to it, running on a physical server with 64 GB of RAM (there are other and changing SQL Server instances on the same machine, too).
This app he's running is a Sharepoint 2010 solution, which performs - well - OK for a Sharepoint site - most of the time. Except for searches, which are terribly slow.
Now from a SQL Server perspective, I've been watching the wait statistics for a few days, and the top three wait types are:
1) CXPACKET at around 49%
2) SOS_SCHEDULER_YIELD at around 10.5%
3) OLEDB at around 9.5%
This seems to be quite consistent - no big changes over time.
- PAGEIOLATCH_SH comes in as seventh - 2.5% of total wait times, average resource wait time is 14ms.
- ASYNC_IO_COMPLETION comes in eight - less than 2% of total wait times, but high average resource wait time of 38 seconds (yes, seconds - not milliseconds!)
The signal wait time is extremely low, so that doesn't seem to indicate CPU pressure. So what's causing this pattern - and what can we do to (a) find more relevant info, or (b) find a solution to speed things up?
Any thoughts? Insights? Ideas? I'm pretty much open to anything - what we cannot change is the basic architecture (separate SQL Server instances, being moved between physical servers - and a Unix-based SAN which cannot guarantee separation of data and log onto separate, physical disks)
Again: this is a SharePoint site - so as far as I know, trying to solve things with indexing or restructuring the database is somewhere between hard and impossible.