Do you think it's imperative to use commercial tools like SQL Diagnostic Manager, SQL Sentry, Spotlight, SolarWind etc.? Or are the built-in features sufficient? While I think there are compelling arguments for both sides, if you can afford it, I suggest go with a third party tool. There are also free of cost third party tools out there.
In this blog, I plan to explore every built-in feature used to gather performance information. Some provide real-time overviews of your databases, while others offer detailed, on-demand data on SQL Server health and performance. Broadly speaking, the methods discussed in this article falls into 3 categories 1) T-SQL based 2) SSMS based and 3) Windows Performance Monitor.
I hope you'll learn something new.
Using T-SQL
There are several ways to use T-SQL queries to monitor SQL Server, including dynamic management views, built-in functions, stored procedures, and system data collections.
Dynamic management views (DMVs)
DMVs are virtual tables that you can query on an ad-hoc basis or as part of your custom, automated monitoring. Some display the SQL Server state at a particular instant, while others, especially those dealing with performance counters, measure values at regular intervals and show you the difference between two samples.
sys.dm_os_performance_counters DMV is of particular interest
to view every performance object and counter of interest.
For example, to view the buffer manager performance object
values:
SELECT object_name,
counter_name,
cntr_value
FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Buffer Manager'
AND cntr_value != 0;
You’ll get a result similar to this:
My goal here is not to teach you about each DMV; instead, I aim to teach you how to teach yourself! You can find all such views in the system view sys.system_objects.
select top 10000 * from sys.system_objects where type_desc = 'view' order by name;
For example, Filter the results on specific terms liKE hadr for the always on views.
select top 10000 * from sys.system_objects
where type_desc = 'view'
and name like '%hadr%'
order by name;
HA Clusters:
select top 10000 * from sys.system_objects where type_desc = 'view'
and name like '%cluster%'
order by name
To get a better understanding or each DMV, you should check
out the specific documentation.
Built-in functions
Unlike dynamic management views, which return data in the form of virtual tables, built-in functions return system data as single numerical values, calculated since the server was last started. You can use each built-in function as an argument in a SELECT statement. For example, the built-in function @@connections returns the sum of successful and unsuccessful connections over time:
SELECT @@connections AS "Total Connections";
GO
Total Connections
-----------------
121
Note: @@connections increments every time a user attempts to
log in (even if the attempt is unsuccessful).
The only built-in system statistics function that doesn’t
return a single numerical value is sys.fn_virtualfilestats.
System stored procedures
Most stored procedures assist with administrative tasks, such as attaching a database or adding a login. However, some report metrics, while others return stored metadata or configuration information.
EXEC sp_spaceused;
Management Data Warehouse
Simply put, the Management Data Warehouse (MDW) in SQL Server stores state and performance data collected from one or more SQL Servers. From this data, reports are generated, and you have the option to create your own custom reports.
This feature significantly aligns with the functionality of commercial monitoring tools and may eventually render paid tools completely redundant.
MDW utilizes SQL Server Integration Services to automate the querying of databases and the recording of results in the Management Data Warehouse.
To read more about it:
SQL Server Management Studio: Activity Monitor
The Activity Monitor makes it possible to view SQL Server
metrics in real time. To use the Activity Monitor, type “Ctrl-Alt-A” or click
the icon within the SSMS toolbar.
To view execution plan for an expensive query, right click
on the query and select Show Execution Plan:
Note: You can’t adjust the sizes of the graphs or the
metrics they show. Nor can you change the way the Activity Monitor calculates
the statistics for query performance
SQL Server Management Studio: Dashboard Reports
There are about 20 standard/default dashboards reports in SSMS.
You can create a custom report. And if you are keen to develop more reports,
check out the Power BI , SQL Server Reporting Services (SSRS) etc. that
provides more features PDF-ready reports, reports for the mobile devices etc..
Windows Performance Monitor
Windows Performance Monitor helps you visualize system-level
resource usage from your Windows hosts, and enables you to correlate these
metrics with SQL Server performance counters in time series graphs.
You can launch it from the Administrative Tools menu group
or run the following from the Run window:
%windir%\system32\perfmon.msc /s
Once in there, click on the Performance Monitor on the left
and it will automatically start showing the percentage processor time. Click on
the + to add more performance counters, including SQL Server
counters.
I believe the third party tools still have a place in enterprise database monitoring and tuning. They offer ease of setup and use—often right out of the box—and may be more valuable if you have already made significant investments in them.
Additionally, I think these tools are supplementary rather than complementary to each other, and you should consider using both.
