tailieunhanh - The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P97

The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P97: Congratulations on your journey to become certified in SQL Server 2008. This book will help prepare you for your exam and give you a practical view of working with SQL Server 2008. | 462 Chapter 10 Monitoring and Troubleshooting Figure Default SQL Profiler Event Selection You can turn off the events in the Security Audit and Sessions sections by unchecking their respective check boxes. You can also uncheck the SQL BatchStarting event because it only shows when commands are started not when they are completed. You will also want to check the box in the TextData column for the RPC Completed event. This will leave the trace returning all the statements that are executed against the SQL Server instance see Figure . Monitoring and Troubleshooting Chapter 10 463 Figure SQL Profiler Event Selection When you run the SQL Profiler Trace by clicking the Run button every statement being executed against the database instance is returned to the SQL Profiler application. If you are monitoring a high load database server this will quickly become more data than you can sift through. You can reduce the amount of data by clicking the Column Filters button. This allows you to return only certain rows back to the client. By setting the trace to be filtered against the Duration column you will see all statements executed against the database that take over seconds 2500 milliseconds to complete see Figure . If this trace returns too much data you can stop it change the filter to a higher number and then run it again. 464 Chapter 10 Monitoring and Troubleshooting Figure SQL Profiler Trace Set to Return Queries That Run for Longer Than Seconds Next begin performance tuning the queries by looking at your indexes and execution plans. As you can see in Figure it becomes easy to identify which queries are long running and need to be tuned. Figure SQL Server Profiler Showing Long Running .