SSWUGtv
With Stephen Wynkoop
What do you know about the Idera Backup Products? Watch today’s episode of SSWUGtv as Steve interviews Rick Plezco from Idera regarding their newest 5.0 Server Backup.
Watch the Show
Analyzing Database Stored Procedures
Richard wrote in with a problem database that has been around for a while, containing many stored procedures. He doesn’t know which ones are current, or even which ones work. Posting this question in our editorial yesterday resulted in a couple answers from our readers.
Tom:
The SQL Server Data Tools (SSDT) allows you to reverse-engineer the contents of a database (tables, views, indexes, procs) into a .sqlproj. I am using this within Visual Studio 2010. When you do this, you get an error window with every unknown reference, such as undefined views, procs, or invalid column names used in procs, views, etc. This gives about the same level of functionality as working in a C# project where all invalid refernces/variables names are flagged. This doesn’t guarantee that the proc works correctly; but it detects all the errors associated with schema changes and code objects that reference columns that no longer exist.
The error list is dynamic; fix a view and all the other views or procs that referenced it suddenly have their unresolved reference fixed.
John:
I threw this together real quick from some of my snippet code thinking it might help Richard answer his stored procedure question to an extent. Though this will not help him find code that is broken per se, it will help him identify stored procs that have run since the last time the box was rebooted or the instance reset. Using the combination of a CTE to find all the distinct procedure names and dynamic management views to see which ones have been used since the last box/instance reset, he should be able to figure out which ones are at least being used.
If you run the code in the body of the CTE by itself, you get a distinct listing of stored procs in the database (if the owner of his stored procs is other than ‘dbo’ then he would want to add that criteria here to ignore stored procs provided by Microsoft). I have also included just a bit of runtime information from the DMVs about how much activity is occurring for the stored procs that have been used:
USE <your db name here>
WITH cte(procname)AS
(
-- this will get the distict stored proc names
SELECTDISTINCT cc.name
FROMsys.procedurescc
WHEREcc.[type] ='P'
--AND [schema_id] = 1
-- this is 'dbo';
--if your stored procs are owned by another schema,
--then you can exclude MS stored procs
--ORDER BY cc.name
)
SELECT
OBJECT_NAME(bb.objectid)AS StoredProcName
,aa.execution_count
,aa.min_worker_time
,aa.max_worker_time
,aa.total_physical_reads
,aa.total_logical_reads
,aa.total_elapsed_time
,aa.total_rows
FROM
sys.dm_exec_query_statsaa
CROSSAPPLY sys.dm_exec_sql_text(aa.sql_handle)bb
INNERJOIN cte cc ONcc.procname =OBJECT_NAME(bb.objectid)
WHERE
bb.dbid= DB_ID()
--AND OBJECT_NAME(sys.dm_exec_sql_text.objectid)
-- = 'specific_stored_proc_name_here'
-- you can limit to one stored proc here
ORDER BYaa.execution_count DESC;
Changing the output to some .* for each table will give you an idea of what all is available if you are not used to working with the new views yet. They can be quite handy. Best of luck and I hope this helps!
Thanks for getting into the conversation with your questions and answers. Anyone is welcome to write btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Top 10 Tips for Optimizing SQL Server Performance
read more)