472 Chapter10•MonitoringandTroubleshooting 9. You are looking at a SQL Server that is having performance problems. You have found that the SAN storage is not working correctly. Which perfor- mance monitor should you look at to confirm that the storage is not writing data quickly enough? A. Current Disk Queue B. Avg Disk Sec / Write C. Avg Disk Write / Sec D. Avg Disk Queue 10. You wish to monitor the throughput of your backup operation to see if the backups are running at their full potential. What performance monitor counter should you monitor? A. SQL Server: Backup Device – Device Throughput Bytes / sec B. PhysicalDisk – Avg Disk Write / Sec C. PhysicalDisk – Disk Writes / Sec 11. You have a SQL Server Agent job configured to run T/SQL statements. The job runs under a domain account that is not a member of the sysadmin fixed server role. You wish to add a Command Exec job step to the job. The job fails on the step because the domain account doesn’t have rights to run Command Exec job steps. What should you do to correct this issue without granting excessive rights to the instance? A. Make the logon a member of the sysadmin fixed server role. B. Make the logon a member of the Windows Administrators group on the server. C. Create a credential based on the Windows logon. Create a proxy based on the credential. Grant the logon rights to the proxy. Assign the proxy to run the job step. D. Create a proxy based on the Windows logon. Create a credential based on the proxy. Grant the logon rights to the credential. Assign the credential to run the job step. MonitoringandTroubleshooting•Chapter10 473 12. You have a SQL Server with several instances installed on it. After the server reboots users are only able to connect to the default instance. What should to do to enable your clients to connect to the named instances again? A. Restart the SQL Server services for the named instances. B. Restart the SQL Server Agent services for the named instances. C. Restart the SQL Server service for the default instance. D. Restart the SQL Browser service. 13. While attempting to troubleshoot connection issues to your SQL Server, you need to identify the TCP port the SQL Server instance is listening on. You examine the SQL Server ERRORLOG as show in Figure 10.16. What ports are being used to connect to the instance? Figure 10.16 A SQL Server ERRORLOG A. TCP Port 65216 B. TCP Port 50584 C. UDP Port 65216 D. UDP Port 50584 474 Chapter10•MonitoringandTroubleshooting 14. You have restarted your SQL Server in single user mode but are unable to connect to the SQL Server using the sqlcmd command line utility. The error message that you receive says that only one administrator can connect at this time. What does this error mean? A. The sqlcmd utility cannot be used when the SQL Server is in single user mode. B. Another login that is a member of the sysadmin fixed server role has connected to the SQL Server. Disconnect that login before you can connect. C. The SQL Server is not yet ready for connections. Wait for the SQL Server to complete its startup process before connecting. 15. You are attempting to troubleshoot a poorly performing stored procedure that is very complex. What tool should you use to identity the problem statement within the procedure? A. Database Tuning Wizard B. SQL Server Management Studio C. SQL Server Profiler D. Database Engine Tuning Advisor 16. You are attempting to troubleshoot another poorly performing stored procedure that is very complex. You need to determine which statement within the stored procedure is causing the performance problem. You decide to use SQL Server Profiler to identity the problem statement. What event should you be monitoring for? A. RPC:Completed B. SQL:Batch Completed C. SP:Stmt Completed 17. You are administrating a system that was upgraded from a single-user Microsoft Access database to a multi-user inventory application using SQL Server 2008 as the backend. Users are complaining that the inventory is no longer accurate. You examine the stored procedure that updates the inventory and you find the following stored procedure: CREATE PROCEDURE UpdateInventory @Sku VARCHAR(50), @InventoryAmount INT MonitoringandTroubleshooting•Chapter10 475 AS UPDATE Inventory SET Inventory = @InventoryAmount WHERE Sku = @Sku GO How should you change this procedure in order to correct the problem? A. CREATE PROCEDURE UpdateInventory @Sku VARCHAR(50), @AmountSold INT AS UPDATE Inventory SET Inventory = Inventory - @AmountSold WHERE Sku = @Sku GO B. CREATE PROCEDURE UpdateInventory @Sku VARCHAR(50), @AmountSold INT AS UPDATE Inventory SET Inventory = Inventory + @AmountSold WHERE Sku = @Sku GO C. CREATE PROCEDURE UpdateInventory @Sku VARHCAR(50), @InventoryAmount INT AS UPDATE Inventory SET Inventory = Inventory - @InventoryAmount WHERE Sku = @Sku GO 476 Chapter10•MonitoringandTroubleshooting D. CREATE PROCEDURE UpdateInventory @Sku VARCHAR(50), @InventoryAmount INT AS UPDATE Inventory SET Inventory = Inventory + @InventoryAmount WHERE Sku = @Sku GO 18. You are the database administrator for Contoso, Ltd. Your company policy states that each employee uses a single workstation. The business unit uses a Win32 application on each workstation and a hard coded SQL Server login to connect to the database. You wish to monitor the activity of a specific user within the application by using SQL Profiler. What column can you use to filter against to restrict the data to only the one user in question? A. TextData B. LoginName C. NTUserName D. HostName 19. Suppose you are running a SQL Agent job with a command executable under a domain account that is not a member of the sysadmin fixed server role. If no proxy account is configured, what domain account would a job step be run under? A. Users account B. Sysadmin account C. Account running the SQL Server Agent D. Account running the SQL Server 20. A user who is not a member of the sysadmin fixed server role is attempting to create a job to call an SSIS package. This job step must be created under a proxy account, but the user reports that they do not have the correct proxy account listed in their drop down menu. What should be done to resolve the issue without granting the user unneeded rights? . the TCP port the SQL Server instance is listening on. You examine the SQL Server ERRORLOG as show in Figure 10.16. What ports are being used to connect to the instance? Figure 10.16 A SQL Server. mean? A. The sqlcmd utility cannot be used when the SQL Server is in single user mode. B. Another login that is a member of the sysadmin fixed server role has connected to the SQL Server. Disconnect. for the named instances. C. Restart the SQL Server service for the default instance. D. Restart the SQL Browser service. 13. While attempting to troubleshoot connection issues to your SQL Server,