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;
- Activity MonitorThis 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. - Dynamic Management View and FunctionsThese 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 - Performance MonitorThis 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. - SQL Server ProfilerThis 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. - SQL TraceThis 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. - Database Engine Tuning AdvisorThis 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.
- 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
. - 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/
. - 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/
. - 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.
. - 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
No comments:
Post a Comment