SQL Server Management Tools

  • Article

Sometimes we may feel that for any monitoring or R&D, which tool to go far? Here is a handy reference for different tools available in Sql Server 2008.

Dynamic Management Views and Functions:
Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
There are two types of dynamic management views and functions:
• Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
• Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.
All dynamic management views and functions exist in the sys schema and follow this naming convention dm_*. When you use a dynamic management view or function, you must prefix the name of the view or function by using the sys schema. For example, to query the dm_os_wait_stats dynamic management view, run the following query:

SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats;

Active: Since Sql Server 2008

Sql Server Resource Governor:

Resource Governor is a new technology in SQL Server 2008 that enables you to manage SQL Server workload and resources by specifying limits on resource consumption(CPU and memory) by incoming requests. In the Resource Governor context, workload is a set of similarly sized queries or requests that can, and should be, treated as a single entity. This is not a requirement, but the more uniform the resource usage pattern of a workload is, the more benefit you are likely to derive from Resource Governor. Resource limits can be reconfigured in real time with minimal impact on workloads that are executing.

In an environment where multiple distinct workloads are present on the same server, Resource Governor enables you to differentiate these workloads and allocate shared resources as they are requested, based on the limits that you specify. These resources are CPU and memory.
Resource Governor is configurable in SQL Server Management Studio by using Transact-SQL statements or by using Object Explorer.
Resource Governor is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.
Active: Since Sql Server 2008

SQL Server Profiler:
Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. You can capture and save data about each event to a file or table to analyze later. For example, you can monitor a production environment to see which stored procedures are affecting performance by executing too slowly.
To run SQL Server Profiler, on the Start menu, point to
All Programs->Microsoft SQL Server 2008->Performance Tools, and then click SQL Server Profiler.
Using SQL Server Profiler, you can do the following:
• Create a trace that is based on a reusable template
• Watch the trace results as the trace runs
• Store the trace results in a table
• Start, stop, pause, and modify the trace results as necessary
• Replay the trace results
Active: Since SQL Server 2005

Activity Monitor:
Activity Monitor can be used to obtain information about SQL Server processes and how these processes affect the current instance of SQL Server.
The Activity Monitor page has the following sections:
• Overview - Shows graphical displays of the percent of processor time, number of waiting tasks, database I/O (megabytes per second), and batch requests (number per second).
• Active User Tasks - Shows information for active user connections to SQL Server Database Engine.
• Resource Waits - Shows wait state information.
• Data File I/O - Shows I/O information for database data and log files.
• Recent Expensive Queries - Shows information about the most expensive queries.
To view the Activity Monitor in SQL Server 2005 and SQL Server 2008, a user must have VIEW SERVER STATE permission.
To view the Activity Monitor on a SQL Server 2000 server, a user must have SELECT permission to the sysprocesses and syslocks tables in the master database. Permission to view these tables is granted by default to the public database role. To KILL a process, a user must be a member of the sysadmin or processadmin fixed server roles.
How to open Activity Monitor in SQL Server Management Studio:
1. On the SQL Server Management Studio standard toolbar, click Activity Monitor.
2. In the Connect to Server dialog box, select the server name and authentication mode, and then click Connect.
You can also open Activity Monitor at any time by pressing CTRL+ALT A.
Active: Since SQL Server 2005

Database Engine Tuning Advisor:
Microsoft SQL Server Database Engine Tuning Advisor helps you select and create an optimal set of indexes, indexed views, and partitions without requiring an expert understanding of the structure of the database or the internals of Microsoft SQL Server.
The Database Engine Tuning Advisor can:
• Recommend the best mix of indexes for databases by using the query optimizer to analyze queries in a workload.
• Recommend aligned or non-aligned partitions for databases referenced in a workload.
• Recommend indexed views for databases referenced in a workload.
• Analyze the effects of the proposed changes, including index usage, query distribution among tables, and query performance in the workload.
• Recommend ways to tune the database for a small set of problem queries.
• Allow you to customize the recommendation by specifying advanced options such as disk space constraints.
• Provide reports that summarize the effects of implementing the recommendations for a given workload.
• Consider alternatives in which you supply possible design choices in the form of hypothetical configurations for Database Engine Tuning Advisor to evaluate
Active: Since Sql Server 2005

Appreciate your comments/suggestion. I will further write blog on new addition of tools in SQL Server 2012.

References: https://msdn.microsoft.com