December 1, 2011 at 5:02 am
Experts,
I want to find if my SQL server is in need of more memory or not.
So What are the counters I need to capture.?
How can I utilise the DMVs ?
Do I need to run the profiler ?
And how long and how many times should I gather all the above data.
I have some idea about this, but it would be great if someone can guide me step by step as it needs to be done in one of our production server.
Thanks,
smith.
December 1, 2011 at 5:36 am
Free ebook, "index seek" for the right chapter
Gail's fabulous articles
Wait stats tells you where your server(s) hurt!
/*
Cost threshold for parallelism (CXPACKET) http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/01/19/tuning-cost-threshold-of-parallelism-from-the-plan-cache.aspx
Paul White: Understanding parallelism http://www.simple-talk.com/sql/learn-sql-server/understanding-and-using-parallelism-in-sql-server/
Microsoft White Paper on waits http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/Performance_Tuning_Waits_Queues.doc
Next query by Paul Randal http://www.sqlskills.com/BLOGS/PAUL/post/Wait-statistics-or-please-tell-me-where-it-hurts.aspx
*/
WITH Waits AS
(SELECT
wait_type,
wait_time_ms / 1000.0 AS WaitS,
(wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
signal_wait_time_ms / 1000.0 AS SignalS,
waiting_tasks_count AS WaitCount,
100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE', 'SLEEP_TASK',
'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'LOGMGR_QUEUE',
'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',
'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'BROKER_EVENTHANDLER',
'TRACEWRITE', 'FT_IFTSHC_MUTEX', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', 'BROKER_RECEIVE_WAITFOR')
)
SELECT
W1.wait_type AS WaitType,
CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
CAST (W1.WaitS * 1000 / W1.WaitCount AS DECIMAL(14, 2)) AS AvgWait_MS,
CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
W1.WaitCount AS WaitCount,
CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 97 -- percentage threshold
AND W1.WaitCount > 0;
GO
--ROLLBACK
--Last execute July 5th 9:33 AM, changed threshold paral to 15 from 5
--DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
December 1, 2011 at 10:51 am
December 2, 2011 at 12:08 pm
When you say NEED, and then look for resources of what indicates how much is used - your returned results might be a bit confusing. SQL server will use pretty much anything you throw at it, so checking how much is used is really going to be how much you allow it to use.
Are you having errors saying memory paged out? Are things being blocked in large chunks as though everything was dumped in the cache?
select count(*) from sys.dm_exec_cached_plans
You can check for recompiles, or query the above DMV to see how many plans are saved in the cache. You can check to see how large the pagefile is and if it is maxed out.
Overall, I would look at page life expectancy. You would need to get some stats to see what your "good" performance on peak load is and compare it to poor performance.
http://www.sqlserver-dba.com/2011/06/sql-server-page-life-expectancy-and-memory-bottleneck.html
December 2, 2011 at 3:45 pm
... and to keep killing the 300 PLE "recommendation"
http://sqlskills.com/BLOGS/PAUL/post/Page-Life-Expectancy-isnt-what-you-think.aspx
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply