Blog Post

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating