More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
MS SQL Server DBA Checklist - Performance Tuning - Hardware Performance Tuning
-- Although heavy-duty hardware can help SQL Server's performance, application and database design can play a greater part in overall performance than hardware. Keep this in mind, as throwing good money after bad on server hardware does not always fix SQL Server performance problems. Before getting faster hardware, be sure you have thoroughly tuned your applications, Transact-SQL, and database indexing.
-- In many cases, adding RAM to a server is the cheapest and fastest way to boost hardware performance of a SQL Server. But before adding more RAM to a SQL Server, ensure first that it will be used by SQL Server. Adding more RAM doesn’t mean that SQL Server will always use it. If the current Buffer Hit Cache Ratio is consistently above 99% and you have well more than 100 MB of Available RAM, your server may not benefit from adding additional RAM.
-- If your SQL Server’s total CPU utilization is consistently above 80% or more, you need more CPUs, faster CPUs, or you need to find a way to reduce the load on the current server.
-- Don't run any applications on your server other than SQL Server, with the exception of necessary utilities.
-- If your SQL Server database experiences mostly reads, then a RAID 5 array offers good protection and adequate performance. If your SQL Server database is mostly writes, then use a RAID 10 array for best protection and performance.
-- The more spindles you have in an array, the faster disk I/O will be.
-- Ensure that all hardware is running the latest, approved drivers.
-- If you are storing your databases and log files on a SAN, the System Monitor performance counters for disk I/O are unreliable. Instead, use vendor-supplied tools to monitor disk performance.
(Continued on next question...)
