Monitor SqlServer Stored Proc execution time

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 :

?
1
2
3
4
5
6
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 :

?
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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

?
1
2
3
4
5
DECLARE @ProcName nvarchar(128)
 
    SET @ProcName = OBJECT_NAME(@@PROCID)
 
    exec proc_Log  @ProcName

And this at the end of stored proc :

?
1
exec proc_Log  @ProcName

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

?
1
2
3
4
5
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)