Sql-server – Linking Waits to a process/statement

Thank you for the detailed explanation of your problem (one of the best laid out questions actually).

WRITELOG is a very common type of wait, so don't worry about it. Looking at the SOS_SCHEDULER_YIELD indicate CPU pressure and also the CXPACKET, it is possible that there must be some missing indexes and you may be retrieving lot of data from the queries for an OLTP system. I suggest you to look at the Missing Indexes DMV and see if there are any indexes (almost sure there will be more than few) that are in the questionable procs.

http://sqlfool.com/2009/04/a-look-at-missing-indexes/

http://troubleshootingsql.com/2009/12/30/how-to-find-out-the-missing-indexes-on-a-sql-server-2008-or-2005-instance-along-with-the-create-index-commands/

Look for Jonathan Kehayias's post on sqlblog.com on this too.

Also, take a look at Parameter sniffing.

http://sommarskog.se/query-plan-mysteries.html

http://pratchev.blogspot.com/2007/08/parameter-sniffing.html

It's NOT a compete answer for your needs but a good starting point. Let us know if you need more details.

I know it's late but there is a lot that could be going on.

-Make sure your statistics look good:

select owner,table_name,sample_size,num_rows,last_analyzed
from dba_tab_statistics.

num_rows and last_analyzed should match if you use default settings.

select owner,table_name,index_name,sample_size,num_rows,last_analyzed
from dba_ind_statistics.

Again sample_size and num_rows should match.

• Verify if you use CURSOR_SHARING=EXACT or FORCE (if SIMILAR, change it to FORCE),(will require sysdba privilege):

  SQL>show parameter cursor; SQL>alter system set cursor_sharing=FORCE; Note that ideally you want to match the parameters you have on your production system. You may want to change these to test them as a cause of your problem however.

• Verify that your SGA is sized properly:

  select * from v$sga_target_advice You would get something like this:

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS

  ---

   10240             .25    131401926              1.2592          1.4876E+10
   15360            .375    117178544              1.1229          1.1815E+10
   20480              .5    112472201              1.0778          1.0648E+10
   25600            .625    109873799              1.0529          1.0250E+10
   30720             .75    107077125              1.0261          9638685159
   35840            .875    105929237              1.0151          9404059270
   40960               1    104353499                   1          9058914623
   46080           1.125    103706507               .9938          8926654470
   51200            1.25    102788197                .985          8724640673
   56320           1.375    102391653               .9812          8646734008
   61440             1.5    101807274               .9756          8515379746
   66560           1.625    101275077               .9705          8394896181
   71680            1.75     97987941                .939          7696453864
   76800           1.875     97967070               .9388          7696453864
   81920               2     97967070               .9388          7696453864
  

Where SGA_SIZE_FACTOR is 1, is my current SGA_SIZE. You can look at column ESTD_DB_TIME_FACTOR and ESTD_PHYSICAL_READS to see how much benefit I get if I increase it to the sizes mentioned in SGA_SIZE. In my case, not much benefit which means I'm good.

• Compare your query when executed with literal values vs bind variables. You may want to see if you have a lot of hard parses for a given query. Check v$sql and v$sql_plan (have a look at column child_number for a given sql_id.

• Do you have very skewed data on some of the columns in your where clause, joins and group by? If so you may be experiencing issues related to skewed data if bind variables are used.