November 22, 2008 at 11:26 pm
Hi!
I am a new to the world of DBAs and right now working on my first assignment. Coming to the problem, I see some issues in the database with the help of Performance Monitor(Perfmon) counters like a lot of full table scans/sec, SQL errors and a lot of lock requests/sec (avg 3000). Now to validate that the above alerts really lead to a bottleneck or not, I am trying to see which tables are being fully scanned(as i know that full table scans are not always bad),which table/row/transaction is using up a lot of locks. For getting all this information I thought of using SQL Profiler when the application is running. But to my surprise the application hangs when the SQL Profiler is on.When I stop the SQL Profiler the application behaves normally. I also tried to use the SQL Profiler remotely but was of no help. Probably its using a lot of memory so the application hangs. Can someone guess why this is happening?
Is there any limitation of SQL Profiler while monitoring real application or the number of events that it can monitor?
Also if SQL Profiler suffers from this limitation,how can i get the locking info which will enable me to say that locks are/would become a bottleneck if load increases?
Seeking your help.
Regards,
Neha
November 22, 2008 at 11:37 pm
You should not be using the profiler gui to monitor a busy production server. Especially not very frequent events like the lock aquired. At best you'll kill your machine, at worst you'll completely kill the server.
If you need to monitor a production server, use the server-side trace procedures (sp_trace*)
However, from what you've said, I'm not sure that profiler's the best tool. What are you trying to discover/prove?
if you're trying to see the number of locks, use sp_lock. If you're checking for blocking (which is what proves that locks are or are not a problem) use sys.dm_exec_requests.
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
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply