Is there a way to tell when a SP was last executed ?
-
Is there a way to tell when a SP was last executed
-
This information is not tracked by SQL Server. You can set up auditing to
track execution of procedures so that they logged a date time value in a table every time they ran.
-
If it's a gotta-have-it now type of thing, look at the proc and see if it uses GETDATE() to update any columns in tables... you might have to do a little analysis but it may give you a hint. If it's going to be a regular requirement, then Nagabhushanam has the right idea.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks so much From now on all the procs will be tracked
-
This isn't probably the best solution but it works for me:
CREATE TABLE [Admin_PerformanceStatistics] (
[recno] [int] IDENTITY (1, 1) NOT NULL ,
[ProcName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProcStart] [datetime] NOT NULL ,
[ProcFinish] [datetime] NOT NULL ,
[ProcDuration] [int] NOT NULL ,
[ProcFiledate] [datetime] NOT NULL ,
CONSTRAINT [PK_Admin_PerformanceStatistics] PRIMARY KEY CLUSTERED
(
[recno]
 
ON [PRIMARY]) ON [PRIMARY]
------------------------------------------------------------------------------------------
--Near the top of your SP
declare @ProcName varchar(100),
@ProcStart datetime
-- Gather Performance Statistics Setup
set @ProcName = 'usp_Dashboard_Metrics_Insert'
select @ProcStart = getdate()
--At the bottom of your SP
insert into dbo.Admin_PerformanceStatistics
values (@ProcName, @ProcStart, getdate(), datediff(millisecond,@ProcStart,getdate()), getdate())
-
Very nicely done and very neat idea, Harry... gotta couple of rhetorical questions and a tip or two...
1. It would appear that ProcFinish and ProcFiledate will always have the same value. Why do you need the ProcFiledate?
2. Rather than storing the duration, why not make a calculated column? In fact, make two... one for milliseconds and one for human readability.
3. Since the finish date will always be GETDATE, why not make the ProcFinish column a non-null default?
4. Rather than having to set the name of the proc (people ARE forgetful), have the table tell you who it is with a default of OBJECT_NAME(@@PROCID)
5. Add the user that invoked the proc with USER_NAME() or just plain USER. Perhaps, add the Host_Name (machine name) as well. In fact, add them to the table as defaults.
Then your first statement of your procs would look like...
DECLARE @ProcStart DATETIME
SET @ProcStart
...and this is what the last statement would look like...
INSERT INTO dbo.Admin_PerformanceStatistics (ProcStart)
VALUES (@ProcStart)
...and here's what the table would look like...
CREATE TABLE dbo.Admin_PerformanceStatistics
(
RecNo INT IDENTITY (1,1) NOT NULL,
ProcName VARCHAR(100) DEFAULT RTRIM(OBJECT_NAME(@@PROCID)) NOT NULL,
ProcStart DATETIME NOT NULL,
ProcFinish DATETIME DEFAULT GETDATE() NOT NULL,
ProcDurMS AS DATEDIFF(ms,ProcStart,ProcFinish),
ProcDur AS CONVERT(VARCHAR(12),ProcFinish-ProcStart,114),
UserName VARCHAR(100) DEFAULT RTRIM(USER_NAME()) NOT NULL,
MachineName VARCHAR(100) DEFAULT RTRIM(HOST_NAME()) NOT NULL,
CONSTRAINT PK_Admin_PerformanceStatistics_RecNo
PRIMARY KEY CLUSTERED (RecNo)
)
GO
GRANT INSERT,SELECT,REFERENCES ON dbo.Admin_PerformanceStatistics TO PUBLIC
GO
...and, yeah... I tested it... it works... and thank you very much for the great idea
CREATE PROCEDURE dbo.AdminDemo
AS
DECLARE @ProcStart DATETIME
SET @ProcStart = GETDATE()
WAITFOR DELAY '00:00:10' --Just waiting ten seconds for kicks
SELECT *
INTO #MyTemp FROM Master.dbo.SysColumns --Doing something of unknown duration
INSERT INTO dbo.Admin_PerformanceStatistics (ProcStart)
VALUES (@ProcStart)
GO
EXEC dbo.AdminDemo
SELECT * FROM dbo.Admin_PerformanceStatistics
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply