Monday, February 23, 2015

Tools for carrying out performance monitoring and tuning of SQL Server

Regular and well-defined monitoring guarantees smooth running of SQL Server. Based on your goals related to monitoring, you should select the appropriate tools for monitoring. For that you have to know the tools supported by SQL Server. Microsoft SQL Server offers number of tools that can be used for performance monitoring and tuning SQL Server databases. Here is the list of tools available;

  1. Activity Monitor
    This tool can be opened with Management Studio and it gives detail view of current activities. It includes five sections: Overview, Processes, Resource Waits, Data File I/O, Recent Expensive Queries.
  2. Dynamic Management View and Functions
    These views and functions return state information that can be used to monitor the health of the instance, diagnose problems and tune performance. There are two type of views and functions: Server-scoped and Database-scoped. These are one of the best tools for monitoring, specifically on ad-hoc monitoring.

  3. Performance Monitor
    This Windows administrative tool allows to track resource usage on Microsoft Operating System and can be used to monitor information specific to SQL Server. This is used as a monitoring tool for identifying trends over a period of time and as a ad-hoc monitoring for identifying resource bottleneck responsible for performance issue.
  4. SQL Server Profiler
    This graphical tool allows to capture a trace of all events occurred in SQL Server. This is heavily used for seeing current T-SQL activities and captured info can be saved for further analysis. This tool also offers the captured events to be replayed.
    ** This tool has been deprecated in SQL Server 2012, instead use Extended events for capturing and Distributed replay for replaying events.
  5. SQL Trace
    This is T-SQL way that provides same SQL Server Profiler tracing facility. Since it does not provide GUI, it is bit difficult to set it up but it is not as heavy as Profiler and leverages T-SQL features.
  6. Database Engine Tuning Advisor
    This tool facilitates getting queries or workload analyzed and getting recommendations on indexes and statistics. This is a useful tool for determining the best index for queries and identifying less-efficient indexes added.
  7. Distributed Replay
    This is an advanced tool that support replaying captured workload across distributed set of servers. This is useful for accessing the impact of SQL Sever upgrades, hardware upgrades and operating system upgrades.

  8. SQL Server Extended Events
    This is a highly scalable and configurable architecture that offers a lightweight system with an UI for collecting information to troubleshoot SQL Server.

  9. SQL Server Data Collection
    This is an automated system for collecting and storing and reporting performance data for multiple SQL Server instances.

  10. SQL Server Utility Control Point
    This is a centralized management portal for monitoring multiple instances of SQL Server based on specific collection sets.
  11. Microsoft System Center Operations Manager
    This is an enterprise level infrastructure management solution that uses management packs to collect performance and health info from windows and application services such as SQL Server. SQL Server has a management pack that enables to create exception-driven events for resolving specific issues.

No comments: