Performance Tuning

ByDr. SubraMANI Paramasivam

Memory upgrade can lead to performance issues

I had a live scenario where my servers were upgraded with the memory and all my ETL & Replication were totally down. Couldn’t figure out the actual issue and need to reset the whole process and run manually. I have about 6TB of database and 4Bn records. My initial workout with the admin guy is to find out the server problem itself and then came out with the server is kind of parallelized with the faulty motherboard. I have 2 processor and equal amount of RAM supplied to both of them. When the memory was upgraded equally one part of the processor couldn’t recognize the memory and found the faulty motherboard. One side it was good to find out the actual memory in place helped to identify and replace the faulty one. But on the other side the production system was totally disastrous. Then need to configure with the resource governor to distinguish the workload to help more for replication and also the default one. After the resource governor was setup properly, the following query produced the results to show average time take per millisecond.

SELECT
rpool.name as PoolName,
COALESCE(SUM(rgroup.total_request_count), 0)as TotalRequest,
COALESCE(SUM(rgroup.total_cpu_usage_ms), 0)as TotalCPUinMS,
CASE
WHEN SUM(rgroup.total_request_count) > 0 THEN
SUM(rgroup.total_cpu_usage_ms) /SUM(rgroup.total_request_count)
ELSE
0
END as AvgCPUinMS
FROM sys.dm_resource_governor_resource_pools AS rpool LEFT OUTER JOIN
sys.dm_resource_governor_workload_groups AS rgroup
ON rpool.pool_id = rgroup.pool_id
GROUP BY rpool.name
GO

Result:

PoolName TotalRequest TotalCPUinMS AvgCPUinMS
——————————————————————————–
PP_BusUsers 0 0 0
PP_SSUsers 0 0 0
distribution 2613809 30510900 11
default 5730860 179191854 31
internal 0 4966873 0

12