Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Monitor object usage over time

Author  Topic 

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2004-06-08 : 10:26:49
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 <database of your choice>
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 <database of your choice>
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 (<provide database list>)

--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 <your database>.dbo.ObjectExecution (DatabaseID, ObjectName, LastFoundTime)
SELECT
TC.DatabaseID,
TC.ObjectName,
@CurrentDateTime
FROM #TempCache AS TC
LEFT JOIN <your database>.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 <your database>.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 <database of your choice>
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 (<provide database list>)

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 <your database>.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
   

- Advertisement -