Three parts have been published till date, and for your kind reference, here are the links to them:
- What is a SQL Trace?
- Profiler Templates, Template Types and Creating Customized Templates
- Trace execution options
Today, we will be looking at one of the events that I came across when we were certifying our product against Microsoft SQL Server 2008 when it was originally released. You may want to do the same when certifying your application against SQL 2012 (code named: “Denali”), currently in CTP03. We will see how the Profiler can help us in identifying T-SQL code that would no longer be supported going forward.
Deprecation Announcement Event Class
This Event class can be used to capture events that occur when a feature scheduled to be deprecated in some future release of Microsoft SQL Server is encountered. These features will still be available in the next major release of SQL Server, but may not be available in future releases.
If such code is encountered, it is recommended to consider replacement with suggested replacements.
Deprecation Final Support Event Class
This Event class can be used to capture events that occur when a feature scheduled to be deprecated in the next release of Microsoft SQL Server is encountered. If any such code is encountered, it is definitely time to refactor the code to use the recommended replacements.
Let us intentionally create a scenario that would trigger one of the Deprecation Event classes.
Create a New Profiler Trace
Select the “Deprecation” events from the Event Selection screen
Execute the following T-SQL code against the instance of Microsoft SQL Server being monitored
** Per Books On Line (http://msdn.microsoft.com/en-us/library/ms143729(SQL.110).aspx),
** SET ROWCOUNT for INSERT, UPDATE and DELETE will no longer be supported in the next
** Microsoft SQL Server release.
** Therefore, we expect this to be captured in the Deprecation Final Support Event Class
BEGIN TRANSACTION BRDemo
SET ROWCOUNT 10
UPDATE HumanResources.Employee SET VacationHours += 1
ROLLBACK TRANSACTION BRDemo
Notice the data captured by the Profiler
In my next post…
Essentially, two major applications of the SQL Server Profiler remain:
- Correlating Performance data
- Replaying a trace
We will be discussing these in the next 2 posts, which will be the final ones in the series.
- Deprecation Announcement Event Class
- Deprecation Final Support Event Class
- Deprecated Database Engine Features in SQL Server "Denali"
- Deprecated Database Engine Features in SQL Server 2008 R2