June 7, 2002 at 5:12 am
Hi,
Can anybody tell how to use the profiling stored procedures in SQL 2000. Basically I want to write my own Profiling utility to monitor the database activity and plot graphs of the various operations carried out.
I will be using Delphi 6 for that purpose.
Thanks in advance.
June 7, 2002 at 5:37 am
This is an example of generating the .trc output file from a trace of Login Auditing.
SQL 2000
declare @P1 int
set @P1=0
exec sp_trace_create @P1 output, 2, N'C:\LoginAudit', NULL, NULL
select @P1 --You will need this output to be able to stop trace otherwise shutdown SQL.
--@x is a bit value, for some reason you must submit parameter 4 this way to get to work properly.
declare @x bit
set @x = 1
--Login
exec sp_trace_setevent @P1,14,11, @x
exec sp_trace_setevent @P1,14,14, @x
--Logout
exec sp_trace_setevent @P1,15,11, @x
exec sp_trace_setevent @P1,15,14, @x
--LoginFailed
exec sp_trace_setevent @P1,20,11, @x
exec sp_trace_setevent @P1,20,14, @x
--Start trace
exec sp_trace_setstatus @P1, 1
/*
--Parameter 1 is the trace id to stop and remove from queue
exec sp_trace_setstatus @P1,0
exec sp_trace_setstatus @P1,2
*/
Look at SQL BOL for the relative information of each process.
Now as I said this outputs to a profiler trace file. The way you could do but I haven't tried or figured out is to use an undocumented stored procedure named
sp_trace_getdata @traceid {, @records}
(note: @records is optional and I don't understand how works this may allow you to geta set at the time, but I am not sure.)
Now I know if the value of @records is no9t messed with thn the trace will send it's buffer contents as they come in to the output. So you will have a connection that receives data until to stop the trace. The only problem is even thou I can figure out the first column is the trace item from setevent the secound it is the output data which is encrypted. I have a piece of code I have not tested on it yet that may decrypt but straight out you cannot read. If you find any additional data please let me know otherwise all the other items are available in BOL.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 7, 2002 at 5:39 am
Thanks a lot.
June 7, 2002 at 6:24 am
Please dont cross post.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply