SQL Server is an enterprise-level database server that has been used by organizations to run their mission-critical applications worldwide. These applications impose the toughest requirements in terms of availability, performance, and scalability. One such deployment of SQL Server is NASDAQ, which may be familiar to most readers. It runs SQL Server 2005 on two, 4-node Dell Power Edge 6850 clusters to support its Market Data Dissemination System (MDDS). Every trade that is processed in NASDAQ marketplace goes through MDDS, with SQL Server 2005 handling 5,000 transactions per second at market open. So if you are experiencing performance/scalability issues with your application(s), chances are that it is not the SQL Server but something else. The trick is to identify what it is (that is, to make a diagnosis) and how to fix it (that is, to troubleshoot) so that your application runs smoothly again. Diagnosing and troubleshooting performance problems cannot be done on an ad hoc basis. You need a strategy/methodology to diagnose and troubleshoot the performance problem(s) quickly to minimize the time your applications are unavailable or any slowdown is experienced by your users.

Audience

This course is intended for IT Professionals who can able to dig performance problems and CPU or I/O bottlenecks and providing the solution with various native tools and adaptive process involved in SQL Server 2008.

Course fee - 200$

For any queries on how to pay, mail us to billing@sqlservermasters.com

Online Payment:

Microsoft SQL Server 2008 Performance tuning and Optimization Outline :

1. A Performance Troubleshooting Methodology

  • Factors That Impact Performance
  • Troubleshooting Overview

2. Tracing and Profiling

  • SQL Trace Architecture and Terminology Security and Permissions
  • Profiler
  • Server-Side Tracing and Collection
  • Troubleshooting and Analysis with Traces
  • Tracing Considerations and Design
  • Auditing: SQL Server's Built-in Traces

3. Query Execution

  • Query Processing and Execution Overview
  • Reading Query Plans
  • Analyzing Plans

4. Troubleshooting Query Performance

  • Compilation and Optimization
  • Detecting Problems in Plans
  • Monitoring Query Performance
  • Query Improvements
  • Query Processing Best Practices

5. Plan Caching and Recompilation

  • The Plan Cache
  • Caching Mechanisms
  • The Plan Cache
  • Internals Objects in Plan Cache: The Big Picture
  • Multiple Plans in Cache
  • When to Use Stored Procedures and Other Caching Mechanisms
  • Troubleshooting Plan Cache Issues

6. Concurrency Problems

  • New Tools for Troubleshooting Concurrency
  • Troubleshooting Locking
  • Troubleshooting Blocking
  • Troubleshooting Deadlocking

Practical Trouble shooting on Performance

  • Case Study A: Performance Counters Setup-Collect-Analyze
  • Case Study B: Performance Counters- Thresholds
  • Case Study C: Performance Tuning on OLAP processing

2 to 3 Years of work experience in SQL Server DEV/DBA.

Hands-on/Lecture Ratio

This class is 30% hands-on, 70% lecture.

Software Needed on Each Student PC

  • Windows Server 2003 or 2008, Windows XP Professional, with at least 1GB RAM
  • SQL Server 2008

After completing this course, students will be able to:

  • Can able to create a methodology how to identify what are all the factors that impact performance.
  • Can understand clearly how to use the profiler as well as system monitor for setting up the counters and analyze the data.
  • Can easily understand the execution plans and also identify the poor query processing.