July 10, 2008 at 3:58 am
Hello,
Do you guys know any particular scripts where I can get response times access from the user?
Example:
- User logins in (how much time did it take to login).
- User selects a table on the program (how much time did it take to open that table on the program).
- etc...
This script must be run a few times a day for one hour each time and also needs to automaticly save all the info into a file (txt, xls, etc...).
Any hints? 🙂
Thanks in advance.
July 10, 2008 at 4:27 am
I don't know how you would script this, but maybe you can use SQL Server Profiler?
The problem here I think would be that you would need to constantly run Profiler, but it can get durations on nearly anything.
July 10, 2008 at 5:00 am
Yes, I thought on using Profiler to get this info, but I wish to use a script together with an Agent. I suspect it will have lesser load on the Productions servers.
Besides, I don't want to keep it running all the time, only at certain hours and for a limited ammount of time.
July 10, 2008 at 6:26 am
If you limit the events captured to only what you really need (especially staying away from capturing individual statements) and you use the extended stored procedures to fire off Profiler and you capture it out to a file, then, Profiler has a very light footprint. We run it on several of our production servers 24/7 with no issues. The only problem we're seeing is that we're collecting too much data. You can pass commands to the profiler through the extended procedures so that it stops after a certain amount of time, at a certain date and time, or after a certain amount of data is collected. It really is the answer for most of what you're looking for.
In SQL 2005, you can query the dynamic management views to get some of the data you require, but not as much as you'll get through Profiler.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 10, 2008 at 7:00 am
Thx for your replies.
I am afraid on using Profiler because my DB's usually are as big as 160GB.
I already have some problems with Deadlocks and don't want to affect even more the situation by using Profiler.
Any especial events that you recommend to trace?
July 10, 2008 at 7:17 am
160gb is not small, but I've used Profiler (in a limited fashion) back in 7.0 on 300gb databases. That was before the improvements to the tool in 2000. You should be OK by and large. Also, deadlocks can be a result of long running queries, but usually it's a more fundamental coding issue, so it's unlikely that added load will increase the number of deadlocks.
As far as events, rpc complete, sql batch complete, from what you said, audit_logout, and lock_deadlockchain and lock_deadlock. I wouldn't go past those and as long as you collect a standard set of columns from this, you should be fine.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply