July 30, 2009 at 10:42 pm
Hi,
I would like to clean up my database of old unused SPROCS. Is there any way to
monitor which SPROCS are being used na dnot used?
It would be extremely helpful to me if I could compile a list of stored proc names and last run date. Short of adding logging code to each one, is there an easier way to do this?
Thanks,
Win.
Cheers,
- Win.
" Have a great day "
July 31, 2009 at 12:58 am
Is there any way to see the stored procedure statistics ??
How many times stored procedure used, so we can remove some of unused stored procedure.
I need to identify the procedures which are not used from last 6 months with the process info if any? for which it is running for.. or Last executed time so that i can chek for last execution date and clean the procedures which are not required...
Fast help is appreciated...!!!!
Cheers,
- Win.
" Have a great day "
July 31, 2009 at 1:18 am
There's no easy way. You can query the plan cache (sys.dm_exec_cached_plans, sys.dm_exec_query_stats, sys.dm_exec_sql_text), but that'll only tell you if the procedure's in cache. There are a number of reasons why an proc may have run recently but not be in the cache.
If the proc is in the plan cache, it means it ran fairly recently, but you can't conclude anything about procs not returned from the plan cache. Best way to get unused stored procs is to write some logging code into them and check back after 6 months or so.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 31, 2009 at 4:06 am
Thanks Gail for reply.
I always prefer smart way. So i asked for the same. NOt the easier way, nothing is easier to get...
Any more help..
Rgds,
Win..
Cheers,
- Win.
" Have a great day "
July 31, 2009 at 8:59 am
well as Gail says, there's no east win on this one.
What I have done earlier is to a simple add a line in my procedures, and do some logging in a table.
I am aware of the overhead, and the amount of work in this if you have many procs.
At that time my conclusion was that this was the quickest way to get some clean up done...
August 4, 2009 at 10:15 pm
This is from another forum. I used this many times in the past to capture last executed time of stored procedures. It uses the syscacheobjects table.
---------------------------------------
Ok, we came up with this one so we can determine what objects (procedures, functions, views) become obsolete over time. This solution requires two procedures and one table. The first procedure (usp_dba_FindCachedObjects) is ran via a SQL Server job to capture everything in the cache at that point. We have it running every 5 minutes but the interval required is pretty much a case by case basis. The second procedure (usp_dba_GetStaleObjects) is ran when we want to generate a list of objects to review before obsoleting. Right now it only looks at objects not found at all (do not reside in ObjectExecution). However, with the column LastFoundTime we left room to modify the second procedure to query information based on dates as well.
Table Defintion:
USE
GO
IF OBJECT_ID('dbo.ObjectExecution') IS NULL
BEGIN
CREATE TABLE dbo.ObjectExecution
(
InternalGUID UNIQUEIDENTIFIER NOT NULL CONSTRAINT DF_ObjectExecution_InternalGUID DEFAULT NEWID(),
DatabaseID INT NOT NULL,
ObjectName SYSNAME NOT NULL,
LastFoundTime DATETIME NOT NULL CONSTRAINT DF_ObjectExecution_LastFoundTime DEFAULT GETDATE(),
UserAdded NVARCHAR (20) NOT NULL CONSTRAINT DF_ObjectExecution_UserAdded DEFAULT (LEFT(SUSER_SNAME(), 20)),
DateAdded DATETIME NOT NULL CONSTRAINT DF_ObjectExecution_DateAdded DEFAULT (GETDATE()),
HostAdded NVARCHAR (20) NOT NULL CONSTRAINT DF_ObjectExecution_HostAdded DEFAULT (LEFT(HOST_NAME(), 20)),
UserModified NVARCHAR (20) NOT NULL CONSTRAINT DF_ObjectExecution_UserModified DEFAULT (LEFT(SUSER_SNAME(), 20)),
DateModified DATETIME NOT NULL CONSTRAINT DF_ObjectExecution_DateModified DEFAULT (GETDATE()),
HostModified NVARCHAR (20) NOT NULL CONSTRAINT DF_ObjectExecution_HostModified DEFAULT (LEFT(HOST_NAME(), 20)),
ConcurrencyStamp TIMESTAMP NULL,
CONSTRAINT PK_ObjectExecution PRIMARY KEY NONCLUSTERED (DatabaseID, ObjectName),
CONSTRAINT IX_ObjectExecution_InternalGUID UNIQUE CLUSTERED (InternalGUID),
CONSTRAINT CK_ObjectExecution_DatabaseID CHECK (DatabaseID 0),
CONSTRAINT CK_ObjectExecution_ObjectName CHECK (LEN(ObjectName) 0)
)
END
Procedure to capture results:
USE
GO
IF OBJECT_ID('dbo.usp_dba_FindCachedObjects') IS NOT NULL
DROP PROCEDURE dbo.usp_dba_FindCachedObjects
GO
------------------------------------------------------------------------------------
--This procedure will interrogate the master.dbo.SYSCACHEOBJECTS for any non system
--procedures, views and functions and insert or update the table ObjectExecution.
------------------------------------------------------------------------------------
--03/01/2004 TAF Created.
------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.usp_dba_FindCachedObjects
AS
SET NOCOUNT ON
--Temp table to store contents of cache (filtered for only what we need).
CREATE TABLE #TempCache
(
DatabaseID INT NOT NULL,
ObjectID INT NOT NULL,
ObjectName NVARCHAR(3900) NOT NULL
)
--Variables to hold the cache variables while processing.
DECLARE @TempCacheID UNIQUEIDENTIFIER
DECLARE @DatabaseID INT
--Variables to execute dynamic SQL so we can change the database.
DECLARE @SQLString NVARCHAR(4000)
DECLARE @SQLParam NVARCHAR(4000)
--To hold time.
DECLARE @CurrentDateTime DATETIME
--Standard variables.
DECLARE @BeginTranCount INT
DECLARE @SavedError INT
DECLARE @SavedRowCount INT
DECLARE @ReturnCode INT
--Capture beginning transaction count for error handling
SET @BeginTranCount = @@TRANCOUNT
IF @BeginTranCount = 0 BEGIN TRANSACTION
--Get the current date and time.
SET @CurrentDateTime = GETDATE()
INSERT INTO #TempCache (DatabaseID, ObjectID, ObjectName)
SELECT
DISTINCT dbid, objid, SQL
FROM master.dbo.SYSCACHEOBJECTS
WHERE objtype IN ('PROC', 'VIEW')
AND DB_NAME(dbid) IN ()
--Create a cursor so we can cycle our temp table and remove any records that are system objects.
DECLARE curDatabaseID CURSOR LOCAL FAST_FORWARD
FOR
SELECT DISTINCT
DatabaseID
FROM #TempCache
OPEN curDatabaseID
FETCH curDatabaseID INTO @DatabaseID
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @SQLString =
'USE ' + DB_NAME(@DatabaseID) + CHAR(13) + CHAR(10) +
'DELETE #TempCache' + CHAR(13) + CHAR(10) +
'WHERE DatabaseID = @DatabaseID' + CHAR(13) + CHAR(10) +
'AND OBJECTPROPERTY(ObjectID, ''IsMSShipped'') = 1'
SET @SQLParam = '@DatabaseID INT'
EXEC @ReturnCode = sp_executesql @stmt=@SQLString, @params=@SQLParam, @DatabaseID=@DatabaseID
SET @SavedError = CASE @ReturnCode WHEN 0 THEN @@ERROR ELSE @ReturnCode END
IF @SavedError 0 GOTO ErrorHandler
FETCH curDatabaseID INTO @DatabaseID
END
CLOSE curDatabaseID
DEALLOCATE curDatabaseID
--Insert any records that do not already exist.
INSERT INTO .dbo.ObjectExecution (DatabaseID, ObjectName, LastFoundTime)
SELECT
TC.DatabaseID,
TC.ObjectName,
@CurrentDateTime
FROM #TempCache AS TC
LEFT JOIN .dbo.ObjectExecution AS OE
ON TC.ObjectName = OE.ObjectName
WHERE OE.InternalGUID IS NULL
SELECT @SavedError = @@ERROR, @SavedRowCount = @@ROWCOUNT
IF @SavedError 0 GOTO ErrorHandler
--Update any records that already exist.
UPDATE OE
SET LastFoundTime = @CurrentDateTime
FROM #TempCache AS TC
INNER JOIN .dbo.ObjectExecution AS OE
ON TC.ObjectName = OE.ObjectName
SELECT @SavedError = @@ERROR, @SavedRowCount = @@ROWCOUNT
IF @SavedError 0 GOTO ErrorHandler
DROP TABLE #TempCache
--Standard transaction check and error handler.
IF @@TRANCOUNT > @BeginTranCount
COMMIT TRANSACTION
RETURN 0
ErrorHandler:
IF @@TRANCOUNT > @BeginTranCount
ROLLBACK TRANSACTION
RETURN ISNULL(@SavedError, -1)
GO
GRANT ALL ON dbo.usp_dba_FindCachedObjects TO PUBLIC
GO
Procedure to query for stale objects:
USE
GO
IF OBJECT_ID('dbo.usp_dba_GetStaleObjects') IS NOT NULL
DROP PROCEDURE dbo.usp_dba_GetStaleObjects
GO
------------------------------------------------------------------------------------
--This procedure will retrieve any objects from the SYSOBJECTS tables that does not
--reside in the dbo.ObjectExecution table. This will go across specific
--databases.
------------------------------------------------------------------------------------
--03/01/2004 TAF Created.
------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.usp_dba_GetStaleObjects
AS
SET NOCOUNT ON
--Variables to execute dynamic SQL so we can change the database.
DECLARE @SQLString NVARCHAR(4000)
DECLARE @SQLParam NVARCHAR(4000)
--Procedure specific variables.
DECLARE @DatabaseID INT
--Standard variables.
DECLARE @BeginTranCount INT
DECLARE @SavedError INT
DECLARE @SavedRowCount INT
DECLARE @ReturnCode INT
--Capture beginning transaction count for error handling
SET @BeginTranCount = @@TRANCOUNT
IF @BeginTranCount = 0 BEGIN TRANSACTION
--Create a temp table.
CREATE TABLE #TempSource
(
DatabaseID INT NOT NULL,
OwnerName SYSNAME NOT NULL,
ObjectName NVARCHAR(3900) NOT NULL
)
DECLARE curDatabaseID CURSOR LOCAL FAST_FORWARD
FOR
SELECT
dbid
FROM master.dbo.SYSDATABASES
WHERE Name IN ()
OPEN curDatabaseID
FETCH curDatabaseID INTO @DatabaseID
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @SQLString =
'USE ' + DB_NAME(@DatabaseID) + CHAR(13) + CHAR(10) +
'INSERT INTO #TempSource (DatabaseID, OwnerName, ObjectName)' + CHAR(13) + CHAR(10) +
'SELECT' + CHAR(13) + CHAR(10) +
' @DatabaseID,' + CHAR(13) + CHAR(10) +
' SU.Name,' + CHAR(13) + CHAR(10) +
' SO.Name' + CHAR(13) + CHAR(10) +
'FROM ' + DB_NAME(@DatabaseID) + '.dbo.SYSOBJECTS AS SO' + CHAR(13) + CHAR(10) +
' INNER JOIN ' + DB_NAME(@DatabaseID) + '.dbo.SYSUSERS AS SU' + CHAR(13) + CHAR(10) +
' ON SO.uid = SU.uid' + CHAR(13) + CHAR(10) +
' LEFT JOIN .dbo.ObjectExecution AS OE' + CHAR(13) + CHAR(10) +
' ON SO.Name = OE.ObjectName' + CHAR(13) + CHAR(10) +
' AND OE.DatabaseID = @DatabaseID' + CHAR(13) + CHAR(10) +
'WHERE SO.XType IN (''P'', ''FN'', ''V'')' + CHAR(13) + CHAR(10) +
'AND OBJECTPROPERTY(SO.ID, ''IsMSShipped'') = 0' + CHAR(13) + CHAR(10) +
'AND SUBSTRING(SO.Name, 1, 1) ''_''' + CHAR(13) + CHAR(10) +
'AND OE.InternalGUID IS NULL'
SET @SQLParam = '@DatabaseID INT'
EXEC @ReturnCode = sp_executesql @stmt=@SQLString, @params=@SQLParam, @DatabaseID=@DatabaseID
SET @SavedError = CASE @ReturnCode WHEN 0 THEN @@ERROR ELSE @ReturnCode END
IF @SavedError 0 GOTO ErrorHandler
FETCH curDatabaseID INTO @DatabaseID
END
CLOSE curDatabaseID
DEALLOCATE curDatabaseID
SELECT
DB_NAME(DatabaseID) AS DatabaseName,
OwnerName,
ObjectName
FROM #TempSource
ORDER BY DB_NAME(DatabaseID), OwnerName, ObjectName
DROP TABLE #TempSource
--Standard transaction check and error handler.
IF @@TRANCOUNT > @BeginTranCount
COMMIT TRANSACTION
RETURN 0
ErrorHandler:
IF @@TRANCOUNT > @BeginTranCount
ROLLBACK TRANSACTION
RETURN ISNULL(@SavedError, -1)
GO
GRANT ALL ON dbo.usp_dba_GetStaleObjects TO PUBLIC
GO
Found this on central.
MJ
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply