April 15, 2010 at 1:03 pm
dma-669038 wrote: We typically go by below DMV based query - it has some limitations, only gives what is in cache since last reboot, but depending on your environment it might work well
Not so sure about that "since last reboot". DMVs like sys.dm_exec_query_stats have a creation time; observation has shown a relatively short life (in the order of hours) on an active system. Appears it has a lot to do with statistics updates. Observation has also shown sys.dm_sql_text to have a limited lifespan. There is nothing in SQL BOL that implies the life of sys.dm_exec_cached_plans exists beyond the cache entry lifetime. The implication is that you need a feeling for the average cache lifetime in your particular server before you can reasonably set a monitor interval. By contrast cache entries on inactive (test) servers may appear to last forever
It appears there may also be (rare) circumstances where cache entries are purged, and thus missed from your stats. SQL entries may also not necessarily be kept under certain circumstances
April 15, 2010 at 6:38 pm
Well done!
I would recommend to use built-in information views instead of system table sys.sysobjects
Cheers
April 15, 2010 at 8:08 pm
First up I start with the SQL Server Profiler with a slightly changed version of the "SP_Counts" template. After my modifications [font="Arial Black"]I have based the template on the "SP:Starting" event [/font]with the following columns:
ObjectName (name of the Stored Procedure)
[font="Arial Black"]Duration [/font](not really needed, I'll explain the purpose later)
DatabaseName (not really needed since we'll filter on the database anyway)
SPID (mandatory, I don't want it but Profiler obviously need it)
StartTime (same as Duration)
[font="Arial Black"]EndTime [/font](same as Duration)
Hmmmm.... I'm curious as to how you got "Duration" and "EndTime" to work on the "SP:Starting" event. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 16, 2010 at 9:57 am
Funny you should write an article about this. This is a good topic. I solved this problem in a similar but different way.
We have hundreds of SQL servers in our environment with thousands of changes going in a month. Over time objects become stale and no one really does the due diligence to clean up unused objects. I estimated that we had hundreds of gigabytes of unused tables as well as thousands of unused objects spread across hundreds of production databases.
We also have a mixture of SQL 2000, 2005 and 2008. So this technique had to work on all environments.
(Before reading keep in mind that what my process does is all configurable. You setup what servers and databases you want to monitor)
So I developed an automated process to collect object usage. I first take a snapshot of the target database and store the results in a table. This captures every object in the database and what objects use those objects (it goes through syscomments to find which objects are used by which objects). As someone else pointed out, what happens when a stored procedure calls a stored procedure, or a stored procedure uses a view...etc. This takes care of that concern.
Figuring out what objects use other objects is the easy part. Its figuring out what objects are used by ad-hoc queries that is hard. And for that we have to use a SQL trace.
The process starts a SQL trace to dump trace files out to directory for a given amount of time. Then I use an SSIS package to load trace files into a table and parse through all the SQL being executed against the server. I break apart every "word" in all the SQL and count each word as a potential object name. A stored procedure then executes after this that updates the meta data that was collected in the snapshot step. Keep in mind that this method has a short-coming and that is when you have a table named LA. If anywhere in your code there is a column called LA then its going to assume that the table LA is used. But I wasnt going to spend 6 months writing SQL parsing code. I think its "close enough"
When complete you will have a table that contains all the metadata you need to delete objects that are no longer used.
If you guys want the process let me know and I can email out the SSIS package and database scripts.
Below is the poorly formatted example of the results you will get:
ServerName DatabaseNameObjectNameObjectTypeUsedInProceduresUsedInFunctionsUsedInViewsUsedInTriggersUsedInAdHocQueries
NHQRPTSQL004ATT_WHSEsysconstraintsVNoNoNoNoYes
NHQRPTSQL004ATT_WHSEsyssegmentsVNoNoNoNoNo
NHQRPTSQL004AYS_AsurionCustomersUNoNoNoNoYes
NHQRPTSQL004AYS_AsurionInventoryUNoNoNoNoYes
NHQRPTSQL004AYS_AsurionInvoicesUNoNoNoNoYes
NHQRPTSQL004AYS_AsurionLedgerInvoicesUNoNoNoNoYes
NHQRPTSQL004AYS_AsurionPurchaseOrdersUNoNoNoNoYes
NHQRPTSQL004AYS_AsurionRepairOrdersUNoNoNoNoYes
NHQRPTSQL004AYS_AsurionRODetailsUNoNoNoNoYes
NHQRPTSQL004AYS_AsurionShippingUNoNoNoNoYes
NHQRPTSQL004AYS_AsurionsyssegmentsVNoNoNoNoNo
NHQRPTSQL004AYS_AsurionsysconstraintsVNoNoNoNoYes
NHQRPTSQL004AYS_AsurionMSreplication_subscriptionsUNoNoNoNoYes
This is just the results of a view I wrote. There is enough metadata to drill into exactly what objects use each object 🙂
April 17, 2010 at 4:37 pm
This sort of thing scares me rigid. The warning at the end is nowhere near strong enough.
Whatever sort or profiler run you use, or whatever sort of trace, you have to make extremely sure that you don't delete SPs that are essential.
There are often SPs that are called only when something has gone horribly wrong, as part of error recovery. SPs that are there just in case - you don't want the circumstances in which they would be called to arise, but if those circumstances do arise you had better have the SPs that are needed to deal with them. The extreme case of this is SPs that are part of disaster recovery. Unless you are unlucky and something pretty nasty happens while you are monitoring none of these will show up in your traces or profiler output.
There may be SPs that run annually, SPs that run quarterly; SPs that run only on DBMS startup - you don't want to get rid of those just because your production system has had no down time for a few months. The size of the profiler files that you would get even to catch SPs that only run monthly (and no, you can't assume they all run on the first or last working day of the month and get away with just a few days monitoring) would be enormous, looking at all that in excel is crazy: do some aggregation in the DB to get the size down, import the results of that into excel if you need to; it's not generally a good idea to use a pin hammer (excel) to where you need a sledge hammer (SQLS).
The basic rule has to be: never delete an SP unless (a) you know what id does and (b) you are certain that it is not required in the system. Think you can be certain by searching through the source code of every app that is ever allowed to run on your system? If so, think again, unless you have no third party apps for which you don't have the source and you know that the CIO, the CFO, the COO, the CEO, and the head of Marketting have carefully informed you of all the apps that they and their staff sometimes want to use on the system (perhaps once in a couple of years) and have accepted responsability for any consequences of their lists being incomplete.
Tom
April 18, 2010 at 10:39 am
nice article but don't you think, creating a new temp table and simply inserting store proc name and time it has been used will do the job. we might have to run a trigger after every query in the database but running this for one day and putting db back to normal next day will do the job and I believe, getting data for 24 hrs testing of normal running of application will do much better than job than running it for 30 min although i don't mind 30 min too.
April 18, 2010 at 11:34 pm
Thanks for feedback, Mister.Magoo! I didn't look at that particular article before posting but I'll put it on my todo list and review it soon! 🙂
April 18, 2010 at 11:41 pm
Thank you all for great feedback! As this was my first article I haven't refined it nearly good enough. Nor have I done sufficient research into alternative methods of solving the case at hand.
I will absolutely rewrite the last part of the article to underline the danger of deleting stored procedures that seems to be unused. I do believe we can all agree on that this article provides some help for small-to-midsize applications but that it doesn't really work well on largescale applications.
Again, thanks for feedback! 🙂
April 19, 2010 at 1:03 pm
dma-669038 (4/15/2010)
Great article but in large environments you dont get to run/profile all stored procs at one shot(many times the application will not even call some procs depending on how people are using it, or testing it). We typically go by below DMV based query - it has some limitations, only gives what is in cache since last reboot, but depending on your environment it might work well. We got it from Greg Larsen's article--Stored procedure usage since last reboot
SELECT DB_NAME(st.dbid) DBName
,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
,OBJECT_NAME(st.objectid,dbid) StoredProcedure
,max(cp.usecounts) Execution_count
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
group by cp.plan_handle, DB_NAME(st.dbid),
OBJECT_SCHEMA_NAME(objectid,st.dbid),
OBJECT_NAME(objectid,st.dbid)
order by max(cp.usecounts)
I tried this but received the following error when I ran it:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '.'. [/b]
Which is referencing this line: CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
I'm still debugging it but thought I'd throw this out there.
April 20, 2010 at 12:31 pm
John Waclawski (4/19/2010)
dma-669038 (4/15/2010)
Great article but in large environments you dont get to run/profile all stored procs at one shot(many times the application will not even call some procs depending on how people are using it, or testing it). We typically go by below DMV based query - it has some limitations, only gives what is in cache since last reboot, but depending on your environment it might work well. We got it from Greg Larsen's article--Stored procedure usage since last reboot
SELECT DB_NAME(st.dbid) DBName
,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
,OBJECT_NAME(st.objectid,dbid) StoredProcedure
,max(cp.usecounts) Execution_count
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
group by cp.plan_handle, DB_NAME(st.dbid),
OBJECT_SCHEMA_NAME(objectid,st.dbid),
OBJECT_NAME(objectid,st.dbid)
order by max(cp.usecounts)
I tried this but received the following error when I ran it:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '.'. [/b]
Which is referencing this line: CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
I'm still debugging it but thought I'd throw this out there.
Try changing the compatibility Level on your database. I changed it on one of mine and it got rid of the syntax error.
January 19, 2012 at 9:18 pm
Checked out the link that dma-669038 posted and there is some good stuff at the site.
Had to use this today as I am reverse engineering a DB and found this absolutely brilliant.
Also thanks to Mr G Larsen for the article and the code.:cool:
June 15, 2012 at 2:59 am
Is there a similar way to retreive the unused views / functions?
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply