July 22, 2011 at 2:07 am
I want to log my stored procedure which client use how many times execute which parameters set etc... How can i do it? I have solution ı can add some scripts all procedures
but it dosen't effective can ı write another thinks for it.
July 22, 2011 at 2:51 am
to be honest the best way to do this is to call a proc with the info you want ( from the being called procedure ) which writes to a logging table. You can achieve the same thing with Profiler or server side traces BUT to be blunt actually building the auditing into the proc is guaranteed to work and once done is easy to implement across your application(s).
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 22, 2011 at 2:59 am
Yes it is but sometimes i forgot some procedure to add this script thats why i want to learn....thankss
July 22, 2011 at 4:31 am
colin.Leversuch-Roberts (7/22/2011)
to be honest the best way to do this is to call a proc with the info you want ( from the being called procedure ) which writes to a logging table.
+1
The only ways to do this reliably are:
1) A logging table and an insert in each proc
2) A trace that runs 24x7x365 and logs SP:Started. Considering I have clients who have servers so busy that said trace write more than 100MB every 10 minutes, such a trace is going to need a hell of a lot of storage and you'll have the fun of importing said data.
The plan cache (which I know someone's going to recommend) is not reliable as plans can be dropped without warning and some plans won't go into cache at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2011 at 4:42 am
Can i do it with alerts?
I can log deadlocks with alerts.
July 22, 2011 at 4:48 am
No. You can log deadlocks with an alert because a deadlock is an error message and alerts fire for errors. There's no error fired when a stored proc executes.
You could probably do it with extended events, but the problem will be much the same as for Profiler.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2011 at 4:58 am
July 22, 2011 at 5:35 am
why not which procedure is most used which client which procedure execute which parameters most which time period which procedure use etc......
July 23, 2011 at 2:36 am
I found solution in here you can collect everythink....
http://msdn.microsoft.com/en-us/library/cc645955.aspx
thanks for everybody....
July 23, 2011 at 2:55 am
ESAT ERKEC (7/23/2011)
I found solution in here you can collect everythink....http://msdn.microsoft.com/en-us/library/cc645955.aspx
thanks for everybody....
That's the trace approach Gail mentioned in her first reply (option 2). Make sure you're taking care of the disk space issue as well as an appropriate concept how to analyze the data. Additionally, you'll need to make sure the trace will be restarted after a server reboot.
July 23, 2011 at 3:45 am
I m writing it into the table it is about mdf space isn t it?
July 23, 2011 at 3:48 am
Don't use Profiler to a table unless you want serious performance impact and potential problems. It's the profiling method with the highest impact, worst overhead. It's possible to crash a server doing that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 23, 2011 at 3:52 am
I can t understand exactly I m using Data Collection Set for it and i can schedule it one minute or once a time.
If i start the collection set the DataCollectionSet Start isn ' t like Disk Usage Query Statistics...
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply