You are employed as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Domain.com has a server named Certkiller -DB01 that is configured to run SQL Server 2005 Enterprise Edition.
Certkiller -DB01 has 2 GB of RAM. However, 1.6 GB is used by the default SQL Server database engine instance. 100 MB is the average data growth of all databases combined per month. You have received several reports from clients sating that the execution times are increasing.
A Domain.com employee named Kara Lang works in the Research and Development department. You instruct her to assess whether more RAM is required. Kara Lang needs to make use of the System Monitor to create a counter log. This counter log will aid her in deciding whether to add more RAM. Which performance object should she add to the counter log?
A.
SQLServer:General Statistics should be added by Kara Lang to the counter log.
B.
Kara Lang should add SQLServer:SQL Statistics to the counter log.
C.
She should add MSAS 2005:Cache to the counter log.
D.
Kara Lang should add SQLServer:Buffer Manager to the counter log.
E.
MSAS 2005:Memory should be added to the counter log.
Explanation:
The SQL Server:Buffer Manager object will show you
1. Low Buffer cache hit ration
2. Low Page life expectancy
3. High number of Checkpoint pages/sec
4. High number Lazy writes/sec
Insufficient memory and I/O overhead are usually related bottlenecks. SQLServer performance depends heavily on the I/O subsystem. Unless your database fits into physical memory, SQLServer constantly brings database pages in and out of the buffer pool. This generates substantial I/O traffic. Similarly, the log records need to be flushed to the disk before a transaction can be declared committed. And finally, SQLServer uses tempdb for various purposes such as to store intermediate results, to sort, to keep row versions and so on. So a good I/O subsystem is critical to the performance of SQLServer.