How to reduce resource waits on sql server without upgrading CPU??

Question

Hi Experts

How to reduce resource waits on sql server without upgrading CPU??

Thanks

Answer 1

Hello,

Please share with us the result of the following query:

SELECT TOP 40 *

FROM sys.dm_os_wait_stats

ORDER BY wait_time_ms DESC

Thank you in advance.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 2

wait_type	waiting_tasks_count	wait_time_ms	max_wait_time_ms	signal_wait_time_ms
LAZYWRITER_SLEEP	5255286	2355971687	8244406	294453
SQLTRACE_BUFFER_FLUSH	294543	1178169921	4171	0
CXPACKET	960112	144910468	173843	324687
PAGEIOLATCH_SH	11201079	119171859	19703	228656
PAGEIOLATCH_EX	7481430	52116484	9843	109390
BACKUPIO	8167206	8935375	1453	12781
ASYNC_IO_COMPLETION	99	8665046	615953	0
BACKUPBUFFER	1385353	8413203	1671	49328
SLEEP_TASK	24932142	5758031	1000	25125
SLEEP_BPOOL_FLUSH	4491653	5622156	31	83968
WRITELOG	5937107	4286296	2187	302953
BACKUPTHREAD	1407	2767218	200734	31
BROKER_TASK_STOP	376	1879734	10000	0
ASYNC_NETWORK_IO	754104	1245421	2000	65015
BROKER_RECEIVE_WAITFOR	2	605906	600000	0
SOS_SCHEDULER_YIELD	42357648	535750	3406	486750
LCK_M_IX	147	340375	68343	0
PAGEIOLATCH_UP	85572	184734	921	390
LCK_M_U	13033	134453	2703	2140
LATCH_EX	53353	101953	375	3828
IO_COMPLETION	555147	101578	687	703
MSQL_XP	22837	59265	7328	0
LCK_M_X	672	31078	4843	0
PAGELATCH_EX	277786	28921	406	6984
CMEMTHREAD	1322	21921	2234	4031
LATCH_SH	17078	16109	1953	890
LCK_M_SCH_M	201	12890	2250	15
SQLTRACE_LOCK	55	11187	1000	0
LCK_M_S	345	8875	4031	46
PAGELATCH_SH	77374	8109	875	1453
PAGELATCH_UP	12094	2296	15	515
OLEDB	131407	1062	31	0
CHKPT	1	375	375	0
SLEEP_SYSTEMTASK	1	375	375	0
EXECSYNC	3214	265	15	93
LOGBUFFER	267	203	109	0
THREADPOOL	32	62	62	0
SOS_RESERVEDMEMBLOCKLIST	1	15	15	0
MSSEARCH	12	15	15	0
TRAN_MARKLATCH_NL	0	0	0	0

Answer 3

Hello,

That computer certainly has parallelism as the top cause of poor performance, but you need to need to do some work with your storage configuration also.

Please configure max degree of parallelism based on the results of the following query:

SELECT CASE

WHEN cpu_count / hyperthread_ratio > 8 then 8

ELSE cpu_count / hyperthread_ratio

END AS 'Optimal Maxdop Setting'

FROM sys.dm_os_sys_info

The following post may help:

http://blogs.msdn.com/b/arali/archive/2009/11/26/sql-server-max-degree-of-parallelism-maxdop.aspx

Configuring parallelism may reduce the storage subsystem contention, but still need to be configured better.

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com

Answer 4

Maxdop setting is 2

Answer 5

Hello,

Is that the result of the query I posted above? Then,perfect. Please configure “max degree of parallelism” with that value.

Before making improvements on the storage, make sure there are no missing indexes. Missing indexes may create parallelism, high I/O and can consume great amount of RAM.

http://blogs.msdn.com/b/bartd/archive/2007/07/19/are-you-using-sql-s-missing-index-dmvs.aspx

Please configure “max server memory” option also. Reserve 3 or 4 GB of RAM for the operating system.

Hope this helps.

Regards,

Alberto Morillo
SQLCoffee.com