The value of this post might be measured in about 10-15 years, when applications, which currently in development stage, will be migrated to newer versions of SQL Servers.
Does anybody still have SQL Server 2000 still running in their environment just because application's code can't be run on newer SQL Server versions? Do you fill that pain?
If so, you have an opportunity to change the future of your application.
When one SQL developer or group of them write a code they might use some SQL Server "Deprecated Features", which will perfectly work in current environment and might work for another 10 years until somebody refabrish the code during migration to SQL Server 20 or 25.
That is tolerable pain, but just imagine if these "Deprecated Features" were used in dynamic queries by front-end apps. Java and C# developers have no idea about SQL features which won't be available in future versions and in 2020 your organization would have to spend another fortune to re-developing whole application or will live with ancient SQL Server 2014.
There are many ways to avoid that situation:
1. Use SQL Server Profiler, which is still available.
2. Use Extended Events feature.
3. Use SQL Server Alerts.
I will explain how to do the easiest "Deprecated Features" monitoring using SQL Server Alerts.
Prerequisites:
1. You have to have Database Mail set and tested on your SQL Server.
2. You have to have "Operator" set and tested on your SQL Server.
In order to understand how theses SQL Server Alerts work we will setup one via SSMS:
1. In SSMS connect to your test server expand "SQL Server Agent", right click on alerts and select "New Alert..."
- Specify New Alert "Name";
- Change "Type" to "SQL Server Performance condition alert";
- Choose "Object" to "Deprecated Features";
- Choose "Counter" to "Usage" (only one available);
- Specify "Instance" of "Deprecated Feature" (in my case it is "DBCC SHOWCONTIG");
- Choose "Alert if counter" value as "rises above";
- The "Value" to rise above is unknown. By default it is "0", but it can be higher in case that event has been triggered since last server restart. That value can be determined by following script:
| SELECT instance_name, cntr_value FROM msdb.dbo.sysalerts_performance_counters_view WHERE instance_name = 'DBCC SHOWCONTIG'; |
The number you get in "cntr_value" you have to specify as your new "Value" to trigger the alert.
In other word if any script on your server will run command "DBCC SHOWCONTIG" the value of "cntr_value" will increment. Then SQL Agent will compare that value with the value associated with this alert and if it "rises above" that "value" alert will be triggered and you will receive an email.
You will receive that email again and again until you disable an alert or change that checkup value.
3. On "Response" page check "Notify operators" box and check "E-mail" box for an appropriate operator who is supposed to receive an e-mail (For that purpose I've created "Test" operator).
4. "Options" page is very important. At first you specify that alerting email will contain some kind of event description and also you can specify some additional description/note/directives you want to associate with that alert.
At second, you have to specify "Delay between responses". That parameter is crucial, it specifies how often you will get alerts after the event will fire. If you leave it as default "0" and event is triggered you will receive emails constantly. In order to prevent this specify delay as high as you can. The biggest delay you can set is 720 minutes and 59 seconds, which is little bit over of 12 hours.
5. After you done with setup click "OK" to set the alert.
Now you can fire the event by running following script on your server:
| DBCC SHOWCONTIG |
As the result you are supposed to receive following email:
| DATE/TIME: 9/8/2014 11:42:58 AM DESCRIPTION: The SQL Server performance counter 'Usage' (instance 'DBCC SHOWCONTIG') of object 'Deprecated Features' is now above the threshold of 4.00 (the current value is 5.00). COMMENT: Please review your T-SQL code where that feature is used and replace it with corrected code. More details: http://msdn.microsoft.com/en- JOB RUN: (None) |
You can notice the line "(the current value is 5.00)" - that means if you want to stop notifications you have to modify "rises above value" for that alert (see step #2).
Looks simple isn't it?
The immediate problem is that number of all Deprecated Features is more than 240 for SQL Server 2012 and 250 for SQL Server 2014.
To list all these events use following script:
| SELECT * FROM msdb.dbo.sysalerts_performance_counters_view WHERE [object_name] = 'Deprecated Features'; |
The second problem is that you will not be able to set alerts for following events in SSMS:
"'::' function calling syntax" and "Data types: text ntext or image".
The problem with them is that they have colon within the name. SSMS in SQL Servers 2012 and 2014 use view "msdb.dbo.sysalerts_performance_counters_view" while SQL Server uses "msdb.sys.dm_os_performance_counters" to get list of events. Stored procedure "sp_verify_performance_condition" was designed to verify alert parameters for 2008, but returns an error in 2012 and 2014. (For more details see http://social.msdn.microsoft.com/Forums/sqlserver/en-US/1b744905-2005-4124-97c9-601c31221a8e/known-bug-in-msdbdbospverifyperformancecondition?forum=sqlnotificationservices)
Hopefully, if you are still reading, you understand the nature of SQL Alerts and how to use them to identify usage of "Deprecated Features".
Now I will give you easy script to setup all Deprecated Features' Event Alerts at once:
(PLEASE READ HOW TO USE THAT SCRIPT BEFORE RUNNING)
DECLARE @Operator SYSNAME = 'DBA';
SELECT 'EXEC
msdb.dbo.sp_add_alert @name= N'''
+ CAST(SERVERPROPERTY('SERVERNAME') as NVARCHAR)
+ ' Alert -
Use of Deprecated Feature: "'
+ REPLACE(RTRIM(instance_name),'''','''''') + '"'',' + CHAR(10)
+ '@enabled=1,' + CHAR(10)
+ '@delay_between_responses=43200,'
+ CHAR(10)
+ '@include_event_description_in=1,'
+ CHAR(10)
+ '@notification_message=N'''
+ 'Please
review your T-SQL code where that feature is used and replace it with corrected
code.' + CHAR(10)
+ 'More details: http://msdn.microsoft.com/en-us/library/ms143729.aspx'','
+ CHAR(10)
+ '@performance_condition=N'''
+ RTRIM([object_name]) + '|'
+ RTRIM(Counter_Name) + '|' + REPLACE(RTRIM(instance_name),'''','''''') + '|>|'
+ CAST(cntr_value as VARCHAR) + ''',' + CHAR(10)
+ '@job_id=N''00000000-0000-0000-0000-000000000000'';'
+ CHAR(10)
+ 'GO' + CHAR(10)
+ 'EXEC
msdb.dbo.sp_add_notification @alert_name=N'''
+ CAST(SERVERPROPERTY('SERVERNAME') as NVARCHAR)
+ ' Alert - Use of Deprecated
Feature: "'
+ REPLACE(RTRIM(instance_name),'''','''''') + '"'',' + CHAR(10)
+ '@operator_name=N'''
+ @Operator + ''', @notification_method = 1'
+ CHAR(10)
+ 'GO' AS Build_Alert_Script,
CASE instance_name
WHEN 'String literals as column
aliases' THEN 1
WHEN 'USER_ID' THEN 2 ELSE 0 END
as EventOrder
FROM sys.dm_os_performance_counters
WHERE [object_name] like '%Deprecated Features%'
and
instance_name not in (
'sp_trace_setevent', 'sp_trace_setfilter',
'sp_trace_create', 'sp_trace_setstatus'
)
ORDER BY EventOrder, Build_Alert_Script;
|
Script usage:
0. Change value of "@Operator" variable by assigning yours "Operator" name.
1. Run script. Copy/Paste values (scripts) with EventOrder = 0 to a new SSMS window and run Make sure it. Make sure no errors were returned.
2. Wait for one minute and then run the script again. This time Copy/Paste values (scripts) with EventOrder = 1. That script is supposed to generate "String literals as column aliases" event. If you run it in the same batch with everything else that event will fire and you'd have to re-adjust it. Run copied script to generate the alert.
3. Wait for one minute and then run the script again. This time Copy/Paste values (scripts) with EventOrder = 2. That script is supposed to generate "USER_ID" event. The reasoning for a delay the same as for #2. Run that script to generate the alert.
Now you can go to "Alerts" and refresh the list. You have to see all newly generated events for your server.
Now you can test your alerts by firing any of "Deprecated Features". Just do not forget to re-adjust alert counters to prevent duplicate alerts after 12 hours.
NOTE: That script excludes "Deprecated Features" associated with tracing.That is done in order to avoid alerting in case of use of "SQL Server Profiler". Do not forget that whole that application will be deprecated in future versions on SQL Server.
Setting up alerts was an easy part. Now you have to monitor them and figure out which application uses them, but would say you are tired receiving these notifications you can simply delete all of them by one script.
Here is a script to generate a code to delete all "Deprecated Features" alerts:
| SELECT 'exec msdb.dbo.sp_delete_alert ''' + REPLACE(name,'''','''''') + '''; GO' FROM msdb.dbo.sysalerts WHERE name like '%Use of Deprecated Feature:%'; |
After running the produced code your test server will be clean from all alerts you've created by previous script.
I found it very useful when I did a research and troubleshooting.
Hopefully it will help you too.
More details about SQL Server "Deprecated Features" you can find here: http://msdn.microsoft.com/en-
------------- 2014-09-10
After couple of days having these alerts I've realized that several Deprecated Features are used by SQL Agent, SSMS and other tools. That means you really can't successfully monitor all alerts.
In order to list all Deprecated Features, which have been used since the last SQL Server restart, you can run following script:
SELECT * FROM sys.dm_os_performance_counters
WHERE [object_name] like '%Deprecated Features%'
|
No comments:
Post a Comment