June 11, 2007 at 6:47 am
One of our database is heavily used to run reports internally, this is causing external user access slow. We want to identify what locks are held on what objects and for what time?
Can we use profiler for this? or else is there any other option ?
June 11, 2007 at 7:01 am
You can use SQL Server profiler for sure. But it is also a performance killer unless you can well define your problem.
You can also use the store procedure, sp_who or sp_who2. Use a loop to record the locks.
June 11, 2007 at 9:39 am
I can get the information about what locks are holding what objects from sp_lock, but I also need the waiting time of each lock for that resource?
June 12, 2007 at 11:38 am
Check this link:
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
We compile this against the master datbase and it gives us a good picture of what is happening.
June 12, 2007 at 1:20 pm
I would suggest getting a seperate server or at least creating a new instance where you can copy your data to nightly for reporting purposes....
Ben Sullins
bensullins.com
Beer is my primary key...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply