SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL MCM Training – Day 4

Today Jonathan has presented on analyzing trace data, and Kimberly has spoken on troubleshooting Plan Cache Issues and Index Consolidation. It's really awesome when you listen to Kimberly, because she is speaking so fast, and delivers such a great quality of content – really awesome!!!

Module 10: Analyzing Trace Data

  • Default Trace
    • Logs everytime when an object is created and deleted
    • Also for Temp tables => Default Trace can get really big
    • Lightweight Trace defined by the Product Support Team
    • Uses DBCC commands
    • DBCC command are also logged in the Default Trace
    • sys.configurations, "default trace enabled" to check if Default Trace is enabled/disabled
    • Can be enabled/disabled through sp_configure
    • Default Trace has always ID of 1
    • Default Trace can't be changed
    • You can create your own Default Trace
      • Disable the original Default Trace
      • Create your own
        • TempDb object creations can be excluded
    • 5x50MB files
      • Server Activity impacts retention period
    • It writes to the error log path
      • Can't be changed
      • If you change the default error log path, you also change indirectly the location where the Default Trace is written
  • sp_get_query_template to normalize statements can be expensive!
    • Cursor must be used, so that you can use it for the result in a trace file
  • When you consolidate several databases onto a new instance, ad hoc workloads should put together, because they both have problems
    • Doesn't make sense to mix these workloads with parametrized workloads
  • SQLDiag can be used for (Remote) SQL Server Health Checks
    • Use Extended Events in it through custom tasks
    • SQLskills runs SQLDiag in the snapshot mode for SQL Server Health Checks
    • /C 2 compresses the output into a CAB file
    • SQLDiag needs sysadmin priviledges on the monitored server
  • Blocked Process Report

Module 11: DMVs – Part 1

Thanks for reading


Klaus Aschenbrenner

Klaus Aschenbrenner provides independent SQL Server Consulting Services across Europe and the US. Klaus works with the .NET Framework and especially with the SQL Server 2005/2008 from the very beginnings. In the years 2004 - 2005 Klaus was entitled with the MVP award from Microsoft for his tremendous support in the .NET Community. Klaus has also written the book Pro SQL Server 2008 Service Broker which was published by Apress in the Summer of 2008. Further information about Klaus you can find on his homepage at http://www.SQLpassion.at. He also twitters at http://twitter.com/Aschenbrenner.


No comments.

Leave a Comment

Please register or log in to leave a comment.