I'm looking for some suggestions on how I can go about the following task. I have an application which connects to our SQL2000 servers. Ocasionally some of the db connections take a long time to run. What I'd like to do is enable some sort of logging which would provide me with the following pieces of information... where the request originated from, length of time servicing the request, some sort of info on the nature of the request (search, insert, update, sproc, etc...), time of the request.
I'd then like to cross reference these logs with my application logs to isolate which requests are taking to long to service. Hopefully this will help us as we attempt to eliminate the issue.
Thanks for any suggetions you might have!!!! much appreciated!
You can use the Profiler which is a standard tool that comes with SQL Server. Several templates are already defined to create a trace (such as for performance measuring) or you can create your own. See Books Online for more info about the parameters and settings.