March 14, 2013 at 10:41 am
Hi DBA
We are upgrading SQL Server 2005 Box to 2012(Side by Side), for planning new server hardware requirements. we want some useful information from current 2005 box. Can you please guys let me know what all information should we consider from current box(like activity, pressure,)while creating a new box.
Please let me know if you have any query which brings out all useful information
Thanks a lot in adavnce!
March 15, 2013 at 5:25 am
When you say "side by side," do you mean that you're going to install a 2012 instance on the same box, or that you're basically going to have a separate server and "flick a switch" at some point when you bring the production server down and move all traffic to the new box?
If you don't have any dedicated monitoring tools or haven't spent any time collecting baselines in perfmon, then Dynamic Management Views (DMV's) will probably be your best course of action at this stage.
You won't get any point in time statistics to identify any real bottlenecks for the current status of the system, but you can get cumulative stats which will give you a good indication as to how the hardware has been performing over time.
Memory, Disk I/O and CPU Usage are your 3 main stats when it comes to provisioning a new server.
After this you have Buffer Cache Hit Ratio, Plan Cache among probably many others.
Here are a couple to get you started (look at perfmon and start taking baselines asap);
Waits are a good place to start, to see what is causing your sql server to have to queue for resource;
--TOP WAITS
WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
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', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP'))
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 99; -- percentage threshold
--SIGNAL WAITS:
SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
AS [%signal (cpu) waits],
CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2))
AS [%resource waits]
FROM sys.dm_os_wait_stats;
March 15, 2013 at 5:35 am
Absolutely re the above.
I use the following query with getdate in there and schedule it once per hour (with a clear of the waits afterwards) to monitor peaks during the day
WITH waits AS
(
SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER () AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE '%SLEEP%'
--filter out any other waits here
)
SELECT
W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12,2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12,2)) AS running_pct
FROM waits AS W1
JOIN waits AS W2 ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct < 90 -- < threshold
ORDER BY W1.rn
Also I use the following for some of the perfmon counters:
SELECT
OBJECT_NAME,
counter_name,
instance_name,
cntr_value,
cntr_type
FROM sys.dm_os_performance_counters
'Only he who wanders finds new paths'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply