With every release of SQL Server, new features are added. Also, some features are discontinued or marked as deprecated, which means that they’ll be removed in a future version of SQL Server. Starting with SQL Server 2008, the deprecation policy is taken very seriously. How seriously? Seriously enough to mention that the first public CTP of the product came with a feature that allows you to monitor usage of deprecated functionality. You may ask yourself: why would I need to track the usage of deprecated features?
Among the possible reasons are the following two:
The deprecation feature consists of two components:
The complete list of deprecated features can be found in Books Online and has more than nine printed pages. Table 1 displays a few of them.
Deprecated feature |
Replacement |
---|---|
DATABASEPROPERTY |
DATABASEPROPERTYEX |
The 80 compatibility level and upgrade from version 80 |
Only compatibility levels for the last two versions of the product available |
sp_dboption |
ALTER DATABASE |
text, ntext, image data types |
varchar(max), nvarchar(max), varbinary(max) data types |
sp_attach_db |
CREATE DATABASE statement with the FOR ATTACH option |
To get a feel for how the deprecation feature works, listing 1 shows how to read the performance counter SQLServer:Deprecated Features before and after using the DATABASEPROPERTY function (which will be replaced by the DATABASEPROPERTYEX function). Note that if you run the sample on a named instance, you have to change the counter name.
-- Declare a variable to hold the current value of the counter
DECLARE @CurrentCount bigint;
SELECT
@CurrentCount = cntr_value
FROM sys.dm_os_performance_counters
WHERE
object_name='SQLServer:Deprecated Features'
AND instance_name='DATABASEPROPERTY';
-- Increase the value of the counter by 1
-- using the deprecated feature the function DATABASEPROPERTY
SELECT DATABASEPROPERTY('master','IsTruncLog') AS IsTruncLog;
-- Retrieve the difference between the current counter value
-- and the original value
-- The value returned is 1 (or greater if another session used the
-- DATABASEPROPERTY function after saving the counter value
SELECT
(cntr_value - @CurrentCount) AS SessionUsage
FROM sys.dm_os_performance_counters
WHERE
object_name='SQLServer:Deprecated Features'
AND instance_name='DATABASEPROPERTY';
The example in the listing reads the performance counters from T-SQL by using the sys.dm_os_performance_counters dynamic management view. A more attractive image can be obtained using the Performance Monitor. From the SQLServer:Deprecated Features performance object, select and add the DATABASEPROPERTY counter. Then back in the SQL Server Management Studio, run again the next statement:
SELECT DATABASEPROPERTY('master','IsTruncLog') AS IsTruncLog;
Figure 1 shows the SQLServer:Deprecated Features performance object in Performance Monitor.
Probably the most useful scenario based on tracking deprecated features is to collect data and save it on a data store, and then build a report over the data store and optionally include the report in SQL Server Management Studio. The frequency of collection, the synchronous or asynchronous mode of collection, and the data store used are a matter of choice (personally I prefer using Extended Events). Some of the possible tracking methods follow:
The next section highlights two of the mentioned methods. First, a simple skeleton for the event notifications method is displayed in listing 2.
-- Create a queue
CREATE QUEUE DeprecationFeatures_Queue;
GO
-- Create a service
CREATE SERVICE DeprecationFeatures_Service
ON QUEUE DeprecationFeatures_Queue([http://schemas.microsoft.com/SQL/
Notifications/PostEventNotification]);
GO
-- Create a route
CREATE ROUTE DeprecationFeatures_Route
WITH SERVICE_NAME = N'DeprecationFeatures_Service',
ADDRESS = N'LOCAL';
GO
-- Create the actual event notification
CREATE EVENT NOTIFICATION DeprecationFeatures_Notification
ON SERVER
FOR DEPRECATION_ANNOUNCEMENT, DEPRECATION_FINAL_SUPPORT
TO SERVICE 'DeprecationFeatures_Service', 'current database';
GO
For using Extended Events, listing 3 creates a session and then adds the two deprecation events. For storage, a ring buffer with maximum 1000 entries is used.
-- Create an event session and add the two deprecation events
CREATE EVENT SESSION TrackDF
ON SERVER
ADD EVENT sqlserver.deprecation_announcement,
ADD EVENT sqlserver.deprecation_final_support;
GO
-- Add a the ring buffer target and configure it
-- to retain 1000 events
ALTER EVENT SESSION TrackDF
ON SERVER
ADD TARGET package0.ring_buffer
(
SET occurrence_number = 1000
);
GO
-- Start the session and begin event collection
ALTER EVENT SESSION TrackDF
ON SERVER
STATE = start
GO
-- Use a deprecated feature
SELECT DATABASEPROPERTY('master','IsTruncLog') AS IsTruncLog;
-- View the collected events
SELECT CAST(xet.target_data as xml)
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xe
ON (xe.address = xet.event_session_address)
WHERE xe.name = 'TrackDF'
GO
-- Stop the event session and remove it from the server
ALTER EVENT SESSION TrackDF
ON SERVER
STATE = STOP;
GO
DROP EVENT SESSION TrackDF
ON SERVER
GO
The result of the previous SELECT query is displayed in listing 4.
<RingBufferTarget eventsPerSec="0" processingTime="0" totalEventsProcessed="1" eventCount="1" droppedCount="0" memoryUsed="424">
<event name="deprecation_final_support" package="sqlserver" id="207" version="1" timestamp="2008-10-30T08:54:31.689Z">
<data name="feature">
<type name="unicode_string" package="package0" />
<value>DATABASEPROPERTY</value>
<text />
</data>
<data name="message">
<type name="unicode_string" package="package0" />
<value>DATABASEPROPERTY will be removed in the next version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use it.</value>
<text />
</data>
</event>
</RingBufferTarget>
SQL Server 2008 makes it easy to track the features that will be removed in future releases of the product. Pick any of the methods mentioned in this chapter and make sure that your applications will have a greater longevity.
Cristian Lefter is a SQL Server MVP and a former SQL Server developer, currently working as a consultant at Micro Training, a consulting and training company. Cristian is a writer, blogger, SQL expert, and frequent reviewer for Manning, Apress, Wiley, and other publishers, as well as for ASPToday, Simple-Talk, Microsoft E-Learning, Asentus, Content Master, GrandMasters, and more. He’s based in Bucharest, Romania.
You can reach him at his blog at http://sqlserver.ro/blogs/cristians_blog/default.aspx, or his email address at [email protected].