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.
   https://msdn.microsoft.com/en-us/library/hh212951.aspx
   http://www.kodyaz.com/sql-server-tools/sql-server-activity-monitor-tool.aspx
   .
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.
   https://msdn.microsoft.com/en-us/library/ms188754.aspx
   http://download.red-gate.com/ebooks/SQL/eBook_Performance_Tuning_Davidson_Ford.pdf
   
   
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.
   http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/
   https://www.youtube.com/watch?v=nAtlan1qgso
   .
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.
   https://msdn.microsoft.com/en-us/library/ms181091.aspx
   http://www.codeproject.com/Articles/21371/SQL-Server-Profiler-Step-by-Step
   .
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.
   https://technet.microsoft.com/en-us/library/ms191006(v=sql.105).aspx
   http://blogs.msdn.com/b/sqlsecurity/archive/2008/12/12/how-to-create-a-sql-trace-without-using-sql-profiler.aspx
   .
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.
   https://msdn.microsoft.com/en-us/library/hh231122.aspx
   https://msdn.microsoft.com/en-us/library/ms166575.aspx
   .
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.
   https://msdn.microsoft.com/en-us/library/ff878183.aspx
   http://blogs.msdn.com/b/mspfe/archive/2012/11/08/using-distributed-replay-to-load-test-your-sql-server-part-1.aspx
   https://msdn.microsoft.com/en-us/library/ee210548.aspx
   .
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.
   https://technet.microsoft.com/en-us/library/bb630354(v=sql.105).aspx
   https://www.simple-talk.com/sql/database-administration/getting-started-with-extended-events-in-sql-server-2012/
   .
9. SQL Server Data Collection
   This is an automated system for collecting and storing and reporting performance data for multiple SQL Server instances.
   https://msdn.microsoft.com/en-us/library/bb677179.aspx
   http://blog.sqlauthority.com/2010/04/13/sql-server-configure-management-data-collection-in-quick-steps-t-sql-tuesday-005/
   .
10. SQL Server Utility Control Point
    This is a centralized management portal for monitoring multiple instances of SQL Server based on specific collection sets.
    https://technet.microsoft.com/en-us/library/ee210548(v=sql.120).aspx
    http://sqlmag.com/sql-server-2008/introducing-sql-server-utility.
    .
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.
    http://channel9.msdn.com/Events/TechEd/NorthAmerica/2014/DCIM-B419#fbid=
    http://www.microsoft.com/en-us/download/details.aspx?id=10631