6

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.

Improve this question
2
  • 2
    Trying to tune Sharepoint's SQL Server usage is an excercise in extreme futility and dangerous levels of frustration. You cannot add, modify or remove any of the indexes and stay supported. The only good news there is that it probably wouldn't matter if you could, because the real problem with Sharepoint is it's SQL code (especially the list processing code) which is a text book in how not to do such things in SQL Server. In fact, it will really make you question how Sharepoint and SQL Server could have been written by the same company.
    – RBarryYoung
    Commented Jul 24, 2012 at 3:33
  • SharePoint really run best when the SQL is dedicated and not shared with other instances, that may and may not have heavy load. Read more in this article on TechNet regarding best practices for SQL 2008 and SharePoint technet.microsoft.com/en-us/library/hh292622.aspx
    – Anders Rask
    Commented Jul 24, 2012 at 7:24

1 Answer 1

Reset to default
8

As this is SharePoint your hands are tied. You can't create indexes or statistics without loosing support from Microsoft.

CXPACKET just means that one thread of a parallized query is waiting for another thread to finish waiting for something else. SOS_SCHEDULER_YIELD can mean a few things, typically it means that you need to fix your indexes are you have queries that aren't tuned correctly so the SQL Server is pausing those queries (yielding them) so that it can work on other stuff (as this is SharePoint you can't really do anything about this). OLEDB means that the SQL Server is waiting on the OLEDB driver so probably there are linked server queries or something along those lines that are calling out to another database server. There's nothing you can do about that either.

From what it sounds like there's about nothing you can do without throwing more hardware at the problem. Use Profiler, Server Side Tracing or Extended Events and find the queries which are taking to long to run and check their execution plans and see how bad that are. Odds are those search queries are doing large scan operations.

How big are the content databases? Microsoft recommends keeping the content databases under 100 Gigs so that scanning is kept to a minimum.

Improve this answer
2
  • Content db is about 6 GB - so that shouldn't really be an issue. But even if I do get query plans for the awful queries - since I cannot add indices or alter the database layout - can I even do something about it? Some queries finish in sub-seconds - others take up to 15 seconds just to find a handful of rows ...
    – marc_s
    Commented Jul 23, 2012 at 18:34
  • There's really nothing you can do besides identify the problem and tell the users not to search the system in that way. The nice thing about doing this is that you'll get the query and you can see where the wildcard in the search predicate is.
    – mrdenny
    Commented Jul 23, 2012 at 22:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.