You are the database administrator for your company and manage all the company’s SQL Server 2008databases. Database users report that overall performance of the database has degraded. You suspect that lackof memory is the cause of the degrading performance. Before you add memory to the SQL server, you want to monitor the amount of memory used by SQL Server. Which performance monitor counters should you add in the counter log? (Choose three.)
A.
Processor: %Processor Time
B.
Process: Working Set
C.
SQLServer: Buffer Manager: Buffer Cache Hit Ratio
D.
SQLServer: Locks: Number of deadlocks/sec
E.
SQLServer: Memory Manager: Total Server Memory (KB)
Explanation:
You can use a counter log in System Monitor to monitor the amount of memory used by SQL Server 2008. Youshould use the following performance counters to monitor the amount of memory being used by SQL Server:
? Process: Working Set
– Monitors if SQL Server 2008 is using too much memory. This number should notbe consistently below the amount of memory that is set in the min server memory and max servermemory server options.
? SQLServer: Buffer Manager: Buffer Cache Hit Ratio – Reflects the percentage of data requests thatwere fulfilled from the cache without requiring the need to fetch the data from the disk. This number shouldbe greater than 90 percent.
? SQLServer: Memory Manager: Total Server Memory (KB) – Indicates the total amount of dynamicmemory currently consumed by the server. This value should be compared against the amount of physicalmemory. If the Total Server Memory is consistently high, you should add memory to the server.
The Processor: %Processor Time counter cannot be used to monitor the amount of memory in the SQL Server.This counter indicates the percentage of elapsed time that the processor spends to execute a working idle thread. The SQLServer: Locks: Number of deadlocks/sec counter measures the number of lock requests per secondthat resulted in a deadlock. This counter cannot be used to determine if memory needs to be added to the SQLServer. There are many SQL Server performance objects that are added to the Performance Monitor when you installSQL Server 2008. The performance objects are divided into 23 categories. A description of some of the SQLServer performance objects is as follows:
? SQLServer: Access Methods – Searches through and categorizes access methods. For example, youcould use its counters to determine the number of table scans.
? SQLServer: Buffer Manager – Provides memory buffer information.
? SQLServer: CLR – Provides common language runtime (CLR) information.
? SQLServer: Databases – Provides performance information about a specific database.
? SQLServer: Deprecated Features – Provides deprecated features information.
? SQLServer: Locks – Provides individual lock request information.
? SQLServer: Memory Manager – Provides SQL Server memory usage information.Objective:
Optimizing SQL Server PerformanceSub-Objective:
Use Performance Studio.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Monitoring > MonitoringResource Usage (System Monitor) > Monitoring Memory Usage TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Monitoring > MonitoringResource Usage (System Monitor) > Using SQL Server Objects