Notable SQL Server Performance Monitor Counters - SQL Server 2008

SQL Server has exposed various Performance Monitor counters for a couple of releases. Obtaining the values for these counters could be achieved programmatically via Windows API calls and other ways that aren’t very T-SQL friendly. With the introduction of dynamic management views came a view that could easily return Performance Monitor counters via T-SQL: sys.dm_os_performance_counters.

SQL Server has continued to evolve over the past few releases and now has a plethora of features. With many of these features come extra Performance Monitor counters. If you take a look at the list of counters available in SQL Server 2008, you will see many more than were provided with previous versions of SQL Server. Some of the more notable features that now have expanded counters are Service Broker, Resource Governor, and Database Mirroring. Together, these new counters can provide additional insight into the operations of SQL Server.

One other notable Performance Monitor counter doesn’t have much to do with performance, availability, or any other of the sweet topics DBAs and managers like to talk about. However, this counter is probably just as important in some ways. It’s called Deprecated Features.

As we’ve mentioned, Microsoft is pushing a lot of new features into the product and deprecating old ones. The company is now getting serious with its deprecation policy, and really will be physically removing the old bits from the SQL Server executable. This means that if your database applications or configuration still rely on deprecated features and functionality, they won’t work in a future version of SQL Server—guaranteed. Since breaking backward compatibility is very bad from a product experience standpoint, Microsoft has made an effort to simplify determining if your database application is using anything deprecated. Before you upgrade, you can always use the SQL Server Upgrade Advisor to get a summary of potential upgrade issues, which would include using deprecated features within scripts. The other way to track deprecated feature usage is to use the Deprecated Features Performance Monitor counter. Issuing the following statement yields about 220 deprecated items at the time of this writing:

SELECT * FROM sys.dm_os_performance_counters
WHERE object_name = 'SQLServer:Deprecated Features'


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics