Tag: monitor

I have had the task to made something to monitor what stored procedure, in Sql Server,in most used. The problem that I have had is that the host_name is easily changed by putting, in the connection string WSID (see http://msdn.microsoft.com/en-us/library/ms178598.aspx )
The code is for sql server 2005 and I have not realized without the help of http://sqlserver.ro/forums/thread/8332.aspx
So there are the three easy steps to have the monitor proceudre in place:
1. Define an audit table :

CREATE TABLE [Audit](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ProcName] [varchar](128)
	[IdentifierAccess] [varchar](max)
	[DateAccess] [datetime] NULL DEFAULT (getdate())
) ON [PRIMARY]

2. Stored proc to insert data :

alter proc dbo.proc_Log(@procname varchar(100))

as
begin
declare @userid varchar(100)
BEGIN TRY
select @userid=client_net_address from sys.[dm_exec_connections] with(nolock)
where session_id = @@SPID
End TRY
BEGIN CATCH
--GRANT SELECT ON sys.dm_exec_connections TO the user or
--grant view server state to the user ! http://msdn.microsoft.com/en-us/library/ms186717.aspx
set @userid = SUSER_NAME()
End CATCH

INSERT INTO [Audit]
(
[ProcName]
,[IdentifierAccess]
)
VALUES
(@procname,
@userid
)
end

3. In each stored procedure put this at beginning

DECLARE @ProcName nvarchar(128)

    SET @ProcName = OBJECT_NAME(@@PROCID)

    exec proc_Log  @ProcName

And this at the end of stored proc :

    exec proc_Log  @ProcName

4. See what the procedure calling are and the time (basic)

select a1.*,'--',a2.* , datediff(ss,a2.DateAccess,a1.DateAccess) 
from [Audit] a1  inner join [Audit] a2 on
a2.ID =
( select max(ID) from [Audit] a2 where a1.ProcName= a2.ProcName and a1.IdentifierAcces=a2.IdentifierAcces and a1.ID > a2.ID )
order by 1

From there is a simple way to make a group by to have the sum * count or other relevant data ( which proceudres are most used in what times of the day, and so on)

Work from home :
1. From the previous select(put it into a Common table Expression ), see what procedure use most
a) number of uses
b) number of time
2. What is missing from Audit table definition ? (Hint :easy clustered index definition)