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

The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P120: 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. | Performance Tuning Chapter 13 577 EXERCISE Monitor Deadlocks This exercise will demonstrate how to enable a trace flag to proactively monitor for deadlocks within SQL Server 1. Start SQL Server Configuration Manager from Start Programs Microsoft SQL Server 2008 Configuration Tools. 2. Select SQL Server Services from the left-hand pane. 3. Locate the SQL Server instance where the trace flag is to be added in the right-hand pane. 4. Double-click the SQL Server service. 5. Click the Advanced tab. 6. Locate the startup parameters field. 7. Move cursor to the right-most position. 8. Add a semicolon and T1222 to the end of the string the command should finish up something like this -dC Program Files Microsoft SQL Server MSSQL DATA -eC Program Files Microsoft SQL Server MSSQL LOG ERRORLOG -lC Program Files Microsoft SQL Server MSSQL DATA -T8029 T1222 9. Click OK. 10. Restart the SQL Server service for the trace flag to take effect. To view the output of the traceflag that is to monitor whether any deadlocks have occurred 1. Start SQL Server Management Studio. 2. Expand the Management folder. 3. Expand SQL Server logs. 4. Double-click the log file. Alternately run sp_readerrorlog from a query window to read the contents of the active error log. 578 Chapter 13 Performance Tuning Guide to the DYNAMIC Management Views DMVs DMVs are almost certainly the single most useful tool for troubleshooting and performance tuning for SQL Server databases. DMVs and DYNAMIC Management Functions DMFs provide Administrators with a simple yet powerful insight into the workings of SQL Server and hardware resources disk memory CPU . There were 89 DMVs introduced to SQL Server 2005 and 47 new DMVs released with SQL Server 2008. These can be grouped in two broad types Server Scoped require VIEW SERVER STATE permission on the server Database Scoped require VIEW DATABASE STATE permission on the database DMVs can be categorized as follows there are