This set of SQL Server Multiple Choice Questions & Answers (MCQs) focuses on “Performance Monitoring”.
1. Which of the following option can be used to launch Perfmon?
a) Type “start perfmon” at the command prompt
b) Go to Start | Programs | Administrative Tools | Performance Monitor
c) Go to Start | Run | Perfmon
d) All of the mentioned
View Answer
Explanation: There are three ways to launch Perfmon which is performance monitoring tool for SQL Server.
2. Point out the wrong statement.
a) There are hundreds of Performance Counters
b) Performance Counters are very important to evaluate
c) SQL Server provides several tools for monitoring events in SQL Server and for tuning the physical database design
d) None of the mentioned
View Answer
Explanation: There are more than thousands of Performance Counters.
3. Which of the following is one of the most crucial performance counters for monitoring?
a) Disk Write Bytes/sec
b) Memory Queue Length
c) Virtual Memory
d) All of the mentioned
View Answer
Explanation: Disk Write Bytes/sec report the number of bytes written to the disk.
4. _________ reports the total processor time with respect to the available capacity of the server.
a) Disk Read Bytes/sec
b) Avg.Disk Queue Length
c) Processor time
d) Response time
View Answer
Explanation: If counter is between 50 to 70 % consistently, investigate the process which is taking long time.
5. Point out the correct statement.
a) Slow SELECT queries with high physical reads and low queue lengths demonstrates under performance of Disk read and write
b) If many simultaneous requests are waiting, investigate the process which is taking less time
c) SQL Server 2008 R2 Data Collection provides information about SQL Server processes and how these processes affect the current instance of SQL Server
d) All of the mentioned
View Answer
Explanation: Index optimization can reduce this problem.
6. Which of the following stored procedure reports snapshot information about current SQL Server user?
a) sp_lock
b) sp_who
c) sp_monitor
d) sp_user
View Answer
Explanation: sp_who reports snapshot information about current SQL Server users and processes, including the currently executing statement and whether the statement is blocked.
7. _____________ displays an estimate of the current amount of disk space used by a table.
a) sp_lock
b) sp_who
c) sp_monitor
d) sp_spaceused
View Answer
Explanation: sp_spaceused displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.
8. Which of the following field in the following code is qualified name?
sp_spaceused [[ @objname = ] 'objname' ] [,[ @updateusage = ] 'updateusage' ]
a) updateusage
b) @objname
c) @updateusage
d) All of the mentioned
View Answer
Explanation: @objname is the qualified or nonqualified name of the table, indexed view, or queue for which space usage information is requested.
9. Which of the following determines type or resource to which the lock applies?
a) sp_lock
b) sp_who
c) sp_monitor
d) sp_spaceused
View Answer
Explanation: sp_lock reports information about locks.use the sys.dm_tran_locks dynamic management view in future versions.
10. The Activity Monitor in SQL Server Management Studio graphically displays information about ______________
a) User activity
b) Blocked processes
c) Locks
d) All of the mentioned
View Answer
Explanation: These parameters are useful for ad hoc views of current activity.
Sanfoundry Global Education & Learning Series – SQL Server.
To practice all areas of SQL Server, here is complete set of 1000+ Multiple Choice Questions and Answers.
If you find a mistake in question / option / answer, kindly take a screenshot and email to [email protected]- Check Information Technology Books
- Practice Programming MCQs
- Check SQL Server Books
- Apply for Programming Internship
