Last week at the Charlotte SQL Server User Group, Aaron Bertrand covered What’s New in Denali. During that presentation he mentioned a few of the things that would not be supported in Denali. That got me thinking. I know there are techniques I’ve used that are no longer supported. I made my self a task in Outlook to start seeking out the deprecated features in use, and replacing them with the currently supported features.
In order to look for these features you could simply run a query searching your stored procedures for features have been officially deprecated.
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%foobar%' AND ROUTINE_TYPE='PROCEDURE'
You’d have to run this query over and over, searching for each feature listed one by one.
But that’s manual. We’re beyond that now, right?
We can use SQL Server Profiler to check for features being used that need to be updated.
Start up profiler. Click File, New trace. Then log in to your server. A word of warning. You don’t want to run profiler against your production servers unless you know exactly how much strain it will put on the server. As always, you want to capture the least amount of data that will answer the question you’re asking.
Set up your trace by giving it a name, and setting the results to save to a table (to make analysis easier later).
Click on the Events Selection tab, and then check the “Show all events” and “Show all columns” options. Open the list beside Deprecation, and check both options for deprecation. (I want a complete list of features that are deprecated.) Also, make sure that RPC:Completed, RPC:Starting, SQL:BatchCompleted, SQL:BatchStarting, and SQL:StatementCompleted are checked. That way you’ll be able to see the TSQL that was run that contains the deprecated code.
Collect this list for one operating cycle. This can be as short as an hour, you may have to run it several times across several days. Your goal with this trace is to run long enough to capture all the common queries run against your system.
Once you’ve captured the data, switch over to SSMS and take a look at your collected data. You’re looking for entries where the EventClass is 125, Deprecation Announcement Event Class and 126, Deprecation Final Support Event Class.
Once you find one of them, you’ll want to see what code caused the alert. Scroll up from the Deprecated event, and look for a RPC:Starting or SQL:BatchStarting event…The TSQL you need to inspect will be there.
You’re going to want to sort through these records, looking for what’s running the queries. Once you’ve updated those, each “deprecation” trace you do in the future should yield fewer and fewer results.
If you’re planning an upgrade to a new version of SQL server, this little trace can save you a lot of hassle, when you approach “Go-Live”. If you have any questions, comments, or suggestions on this trace, send them in. I’m always happy to discuss them with you!