July 11, 2014 at 8:49 am
I do have very old versions of duplicate store procedures on my databases. I know there is no "safe" way to do this using DMVs, so I am planning to combine that with a trace. But I would like to get others opinions about that.
Here's the DMV I am planning to use:
SELECT
CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName
,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]
,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
,cached_time
,last_execution_time
,execution_count
,total_worker_time / execution_count AS AVG_CPU
,total_elapsed_time / execution_count AS AVG_ELAPSED
,total_logical_reads / execution_count AS AVG_LOGICAL_READS
,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
,total_physical_reads / execution_count AS AVG_PHYSICAL_READS
FROM sys.dm_exec_procedure_stats
WHERE DB_NAME(database_id) LIKE 'MyDatabase%' --AND OBJECT_NAME(object_id,database_id) LIKE 'sproc_name_here%'
ORDER BY OBJECT_NAME(object_id,database_id)
I will save that on a local table and run it every 5 min maybe? Or at an interval equal or lower than PLE?
July 11, 2014 at 9:25 am
The biggest issue is this will miss things that get run very rarely. Consider procedures used during a year end process or something similar. These may not show up in your trace that you run for 3 months but suddenly somebody tries to execute them. One way you can deal with is to rename the procedures with a date or something like that appended to the name. Then you keep them around for a year or two so that these rarely used ones can be renamed quickly to deal with issues that may arise. It is a slippery slope for sure.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 11, 2014 at 9:43 am
Sean Lange (7/11/2014)
The biggest issue is this will miss things that get run very rarely. Consider procedures used during a year end process or something similar. These may not show up in your trace that you run for 3 months but suddenly somebody tries to execute them. One way you can deal with is to rename the procedures with a date or something like that appended to the name. Then you keep them around for a year or two so that these rarely used ones can be renamed quickly to deal with issues that may arise. It is a slippery slope for sure.
Good point.
Yes, renaming store procedures is something I will do for sure. The DMV , even the trace, won't capture those that may run monthly or sporadically (uncommon on my case, but could happen)
What could be a good interval for this DMV or trace? was thinking about 5 min and check any performance impact and adjust.
Also, what could be a good way to renaming those old store procedures, using pure T-SQL , PowerShell? I would like to later save the store procedure definition and name on a different box.
July 11, 2014 at 9:58 am
sql-lover (7/11/2014)
What could be a good interval for this DMV or trace? was thinking about 5 min and check any performance impact and adjust.
That would generate an awful lot of data to parse through but I don't know much about your system either.
[qutoe]
Also, what could be a good way to renaming those old store procedures, using pure T-SQL , PowerShell? I would like to later save the store procedure definition and name on a different box.[/quote]
This kind of depends on how many you need to rename. I would do it with t-sql but I am pretty fast with t-sql and haven't used PowerShell much. I would probably use t-sql to generate dynamic sql and execute that.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 11, 2014 at 10:12 am
Thanks Sean,
Appreciate the feedback.
July 11, 2014 at 10:26 am
Can I ask why you care about those objects and why you feel the need to remove them?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply