SQL Server – Knowing the Use of Deprecated or Discontinued Features

Upgrading your SQL Server environment is inevitable and I know most many are doing this given SQL Server 2005 support will end first half next year. Whenever I get in front of customers, I have questions always coming my way around upgrades. In a recent session, I had one of the attendees send me a mail stating I have got him into trouble. I was taken aback and wrote back to know what was going wrong and how I can help him get out of trouble. This is what he wrote back:

“ Hi Pinal,

It was great to be part of your session the other day, but after the session my management had an ask which I was not sure and want your guidance.

As a DBA, I have just upgraded from a previous version of SQL Server on one of our production environment. Prior to upgrading I utilized the SQL Server 2014 Upgrade Advisor to identify deprecated and discontinued features in SQL Server 2014. Our database and development teams have regression tested the changes identified in the Upgrade Advisor for deprecated and discontinued features and remediated the items found.

As the SQL Server Administrator I need to verify all of the deprecated and discontinued features have been removed. How can I do this? I have rerun the upgrade advisor and assume that is good enough. My Management has come back strongly with an approach to make sure we are using the latest and greatest capabilities because of this upgrade and there are no legacy around.”

This was an interesting question and I wrote back to help him. This blog has been inspired by this interaction.

Thinking Simple

There are multiple options and we need a systematic way to solve this problem. We will start by doing some simple queries to DMVs to understand if we are using any deprecated features.

instance_name AS 'Deprecated Feature',
cntr_value AS 'Number of Times Used'
FROM sys.dm_os_performance_counters
AND cntr_value > 0
ORDER BY 'Number of Times Used' DESC

I told my DBA friend to run the above query to find out if anything is still around. I always say to have a baseline trace to rerun on an upgraded test environment to know if there are still features we need to work on. This is always not simple but this is same as what we get from Perfmon counters. These two must match. If you are not aware, here are the steps:

Open up Performance Monitor (Perfmon) and under the SQL Server counters add the Deprecated Features / Usage for all counters by selecting all and Clicking ADD.

deprecated discontinuted 01 SQL Server   Knowing the Use of Deprecated or Discontinued Features


As I said this was a simple solution. We also exchanged few other emails after a week of interaction. I will try to write for a different blog. Having said that, I would highly recommend using these simple techniques to know if we are using Deprecated features. I would also like to learn from my blog readers if you have ever used these features in your environments prior to upgrade and as a validation process? Do let me know what you have been doing.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – 2005 – Use Always Outer Join Clause instead of (*= and =*)

Yesterday I wrote about how SQL Server 2005 does not support named pipes. Today, my friend called me asking some of his query does not work. I asked him to send me the queries. I asked him to send me query. I noticed in his queries something, I have never practiced before and I never had any issue therefore.

Instead of using LEFT OUTER JOIN clause he was using *= and similarly instead of using RIGHT OUTER JOIN clause he was using =*. Once I replaced did necessary modification, queries run just fine.

I wish I can give you example of *= or =*, however, I had decided to not to add any more SQL Server 2000 code to this blog few months ago.

Reference : Pinal Dave (http://blog.SQLAuthority.com), SQL Joins

SQL SERVER – 2005 – DBCC ROWLOCK – Deprecated

Title says all.

My search engine log says many web users are looking for DBCC ROWLOCK in SQL SERVER 2005. It is deprecated feature for SQL SERVER 2005. It is Automatically on for SQL SERVER 2005.

More Deprecated Features of SQL SERVER 2005 Refer MSDN Discontinued Database Engine Functionality in SQL Server 2005.

Reference : Pinal Dave (http://blog.SQLAuthority.com)