March 16, 2010 at 5:18 am
hi all
one of our production servers slows down greatly through out the day, it has a backend of sql 2005 on server 2003.
i ran perfmon to gather some statistics and would appreciate some advice on them, tho from what i can tell i have serious memory issues?
\\GRNSQL001\SQLServer:Buffer Manager\Page life expectancy
during the working day - from 1 -518 with an average of 119
out of hours - from 100 - 3430 with an average of 1251
March 16, 2010 at 6:58 am
It's possible that it's a memory issue. What other metrics have you gathered? Do you have wait statistics available? That will tell you what things are waiting on, which is frequently a great way to determine what is running slow, and why.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 16, 2010 at 7:15 am
wait statistics for disks and processor just
physical disks range from 0-160, and processor really no waits at all
March 16, 2010 at 7:24 am
There's still not enough information to see exactly what the cause of slow performance might be. I'd suggest you gather data using sys.dm_os_wait_stats. Get that information together over a period of time. Couple it with trace events captured on a server side trace so you can see which procedures are running at the times when things are slowing down.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 16, 2010 at 8:09 am
how do i gateher statistics with sys.dm_os_wait_stats?
March 16, 2010 at 8:11 am
Do you run a reindexing job overnight, if so are the queries which are affected those which are pulling data for the current day rather than historical data?
March 16, 2010 at 8:14 am
no indexing done on a nightly basis, and its all queries that can get slow regardless of age
March 16, 2010 at 8:17 am
Ah well - so much for that stab in the dark
March 16, 2010 at 8:19 am
help appreciated anyhows ssc!
March 16, 2010 at 8:23 am
oops i was using exec sys.dm_os_wait_stats
instead of SELECT * FROM sys.dm_os_wait_stats
March 16, 2010 at 8:28 am
Yep, that's it. It's a DMV to give you a view into what things are waiting for what in the server. You'll need to look in BOL or online at the various wait states, and you need to know that it's an aggregation, I believe since the server was restarted. So you'll want to sample it over time & compare the samples to see what's happening for sure.
There's a great article on it here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 16, 2010 at 8:29 am
DO THESE STATS INDICATE ANYTHING?
CXPACKET 3344303.7276.4476.44
PAGEIOLATCH_SH 407470.349.3185.76
LCK_M_SCH_M 173911.163.9889.73
ASYNC_NETWORK_IO 118890.632.7292.45
BROKER_TASK_STOP 93700.662.1494.59
LCK_M_S91672.272.10 96.69 2.10 96.69
March 16, 2010 at 8:31 am
THE COLUMNS ARE
TYPE WAIT TIME S PCT RUNNING PCT
March 16, 2010 at 8:48 am
malachyrafferty (3/16/2010)
DO THESE STATS INDICATE ANYTHING?CXPACKET 3344303.7276.4476.44
PAGEIOLATCH_SH 407470.349.3185.76
LCK_M_SCH_M 173911.163.9889.73
ASYNC_NETWORK_IO 118890.632.7292.45
BROKER_TASK_STOP 93700.662.1494.59
LCK_M_S91672.272.10 96.69 2.10 96.69
'
CXPACKET waits are frequently an indication of parallel execution on your queries. What's your parallelism cost threshold set to?
If you read the document that I linked to, you'll see that PAGEIOLATCH_SH waits can be caused by either IO issues or by memory pressure, which links back to your issue with page life expectancy.
Based on this information, I'd say you've got performance bottlenecks because of the queries. You either need to tune the code, the indexes, or, most likely, both. I'd start gathering statistics using a server side trace to identify the long running queries and start there. Also, check on your cost threshold for parallelism like I said before.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 16, 2010 at 9:03 am
yea document looks good, it should give me a clearer idea what im looking at
currently the cost threshold is set at 5 - i have no idea what that is doing
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply