IT Job search

SQL Server : External Hardware Performance Review

Quite often it is worth performing a hardware review , prior to diving into a full OS and SQL Server instance review. Understanding the effect these components have on SQL Server Performance may enable the DBA to diagnose and solve bottlenecks quickly. SQL Server does not operate independently. There is a heavy reliance on external factors: Hard Disk, Network, Memory ,CPU CPU Bottlenecks - Look at :Number of CPUs ,CPU MHz,CPU L2 Cache Size The availability level of CPU cycles relative to requirements may slow down performance.To minimise CPU problems , look at buying faster CPUs,adding more CPUs, reduce workload or add more memory. RAM bottleneck- The Buffer Cache Hit Ratio measures the amount of times SQL Server goes to the hard disk or the buffer. The lower the ratio the greater the problem.The best solution is to add more RAM Network Bottleneck - Too many user connections can cause network bottlenecks. In reviewing measure: the speed of network cards, the amount of network cards, are the network cards linking to a switch,are the network cards automatically set for speed/duplex? Disk Bottleneck - The Physical Disk measurements: % Disk Time & Avg. Disk Queue Length can indicate the physical array traffic level. Consider looking at : Total amount of drive space, total number of physical drives in each array, hardware v software RAID, disk fragmentation levels. When monitoring SQL Server the key measurements for hardware performance are: 1) Memory : Pages/Sec 2) Physical Disk:% Disk Time 3) Average Disk Queue Length 4) Buffer Cache Hit Ratio 5) User Connections

Digg This : Click to email a colleague Author details ------------------------------------------------------------------------ Jack Vamvas is a SQL Server Consultant. To contact him about this article , or any issues related to SQL Server register on www.ciquery.com and post a question. For SQL Server Consultancy of any SQL Server task - check SQL Server Specialists Make SQL Server go faster - click here