لطفا منتظر باشید ...
Objective 5.5: Monitor and troubleshoot database activity by using SQL Profiler.
The SQL Profiler is an excellent tool for auditing a SQL Server database. Security events, scheduled jobs, login failures, transaction log writes, and database and log file growth are some of the many events that can be monitored for both performance and security reasons. Auditing can be customized as needed and can be extensive enough to conform to the C2 auditing standards. Operations that can cause performance problems can also be monitored, such as memory changes, large sorts, deadlocks, logical reads, physical writes, and DBCCs run during peak performance hours. User activity can also be monitored, including determining the host name, application name, and Microsoft Windows NT domain name of logged-on users. The time that activities occur as well as the duration can also be monitored. SQL Profiler can also monitor errors, including severity.
Objective 5.5 Questions
1. | 70-229.05.05.001 Your corporation has policies in place that do not allow any administrative work to take place during the daytime hours. You think that someone might be granting permissions on objects during the day. You would like to determine whether object permissions are being granted during the day, and if so, as much information about the user as possible. Which is the best method to accomplish this? You create a SQL Profiler trace that includes the SQL:BatchStarting event class, and includes the NTUserName and LoginName data columns. You create a SQL Profiler trace that includes all of the Security Audit event classes, and only the TextData, SPID, and EventClass data columns. You create a SQL Profiler trace that includes the Audit Object GDR Event event class and includes the NTUserName, LoginName, and StartTime data columns. | |
2. | 70-229.05.05.002 You receive a report that an application starts performing poorly when a large number of users are connected. You suspect that there might be transactions that have been left open for too long, because either queries are taking too long to complete, or user interaction is being requested in the middle of a transaction. What methods can you use to determine whether blocking is occurring in the database or transactions are being held open too long? (Choose all that apply.) Run the system-stored procedure sp_who on the server every few seconds, and look in the BLK column of the result set for any incidence of a blocking SPID. Run the DBCC OPENTRAN command every few seconds. Run the SQL Profiler and capture the Lock:Deadlock event, grouping by SPID. Run the SQL Profiler and capture the event classes in the Transaction event category and the command being run, and then group the events by the Duration data column. | |
3. | 70-229.05.05.003 You have a large database application that uses many stored procedures. Whenever the server is rebooted, the system will run a bit more slowly, because each of the stored procedures is compiled before the execution plan. The application then tends to run faster. However, a certain number of stored procedures never seem to run faster. What is the best approach to determine whether these stored procedures are reusing a cached execution plan? Run a SQL Profiler trace to capture the SP:Completed event grouped by the Duration data column. Run a SQL Profiler trace capturing the SP:CacheHit, SP:CacheMiss, and SP:ExecContextHit events. Run the system-stored procedure sp_recompile against all of the stored procedures. | |
4. | 70-229.05.05.004 You have an application that is occasionally returning error message 1205, indicating that a deadlock has occurred and that the process has been chosen as the deadlock victim. Which of the following troubleshooting methods can you use to determine what query might be producing the deadlock? (Choose all that apply.) Add the 1204 trace flag to the startup options of the server to capture deadlock information and output it to the error log. Restart the server. Run the DBCC TRACEON (1204) command to capture deadlock information (trace flag 1204). Capture the Lock:Deadlock and Lock:Deadlock Chain events in SQL Profiler. Run the sp_lock system-stored procedure. |
Answers
1. | C. The Audit Object GDR Event event class will capture events that occur when a GRANT, DENY, or REVOKE permission is made against an object. The NTUserName and LoginName data columns will indicate who is performing the operation, and the StartTime data column will show the time of the start of the permission event. |
2. | A. Although this is a very simple method, it is a quick way to determine whether a connection is blocking another connection for a long period of time. The SPID identified in the BLK column will indicate the offending connection, which can then be further examined. B. The DBCC OPENTRAN command will return information about the oldest open transaction in the database. If DBCC OPENTRAN continuously returns the same transaction as being open, there is a good possibility that something is holding the transaction open too long and should be investigated. D. The Transaction events grouped by Duration will provide information on the longestrunning transaction, and include the SPID. This can then be used to further investigate the offending queries. |
3. | B. Capturing the cache hit, cache miss, and execution context hit events can help determine whether a stored procedure is being found in the cache and whether the execution plan is therefore reused. |
4. | A. The 1204 trace flag will capture deadlock information and output it to the SQL Server error log. Once the server is restarted, any deadlocks will be captured, and information determining the SPIDs involved, and other diagnostic information, will be output as well. You can then use this information to evaluate the troublesome code. B. The 1204 trace flag will capture deadlock information. With this DBCC command, the server does not need to be restarted. The SQL error log can then be examined for deadlock output and the offending queries investigated and corrected. C. The SQL Profiler can capture deadlock information when it occurs, and these traces can then be evaluated to determine which processes are involved, and what the query running at the time was doing. |
