Adsense_01

Sunday, 4 July 2010

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