May 17, 2012 at 9:47 am
I have been asked to identify which of the 250 or so User Defined Functions in a database are now obsolete.
I can see those without dependancies in the database but potentially they could still be called externally.
Is it possible to either find statistics on their use or configure some sort of lightweight audit that will capture their use over a period of say 3 months?
I have tried querying the systemtables and although I can see some information relating to functions called, it does not provide details of all functions used and each time used. (I know some of the functions have been used and they are not appearing in the query results). This is what I tried:
SELECT qt.TEXT AS 'SP Name',
qs.execution_count AS 'Execution Count',
qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',
qs.total_worker_time AS 'TotalWorkerTime',
qs.total_physical_reads AS 'PhysicalReads',
qs.creation_time 'CreationTime',
qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS 'Calls/Second'
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE qt.dbid = (
SELECT dbid
FROM sys.sysdatabases
WHERE name = '<MyDBName>')
and qt.text like '%cf_%'
ORDER BY qs.total_physical_reads DESC
Thanks
Brian
May 17, 2012 at 9:59 am
How would any of that information, if it existed, guarantee that the function was obsolete? 🙂 I personally believe that the proper and best way to do it is to go through each function, understand what it does, and then find if and where it is used. The original purpose of the function will help to find if it is still referenced.
Jared
CE - Microsoft
May 17, 2012 at 10:04 am
Use the Depreciated Feature Object in sQL 2008R2
Here is a link to describe its use and limitations
http://technet.microsoft.com/en-us/library/bb510662(v=sql.105).aspx
May 17, 2012 at 10:04 am
I agree that it wouldn't guarantee that it was obsolete, but I would then be able to rename those that were indicated in this way and then if nothing broke for the next few months back them up and deleted them. The issue is that in 12 months time we have alot of code to migrate and if I can remove the obsolete items it will reduce the time required to migrate everything.
May 17, 2012 at 12:40 pm
Start capturing execution metrics. This will at give you some indications as to what is regularly referenced and what isn't. Be prepared to capture a lot of data though. Since you're using SQL Server 2008, I'd suggest using extended events. They cause much less impact on the server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply