September 24, 2008 at 12:25 pm
I'm running into sporadic performance issues that I believe I have isolated to our SQL Server database server. The app is web based going against 2 load balanced IIS 6.0 servers with a 2005 SQL Server backend. The IIS logs for both servers show high response times during this condition.
During times of reported "hangs" and "slowdowns" I see some high DURATION times out of SQL Profiler, 80-100 seconds). The nature of the issue shows a gap in transactions completing. When the situation clears, multiple (up to dozens) of other long running (over 10 seconds) transactions complete within the same quarter second. It appears that something is bottlenecking responses and I'm trying to identify what.
While a better understanding of SQL Profiler would help, I was wondering what 3rd party products are out there that do a good job (in real time) of identifying performance issues with SQL Server.
I've looked briefly at 2 products (read their description), SQL Power Tools and Teratrax performance monitor.
I was hoping to get people's comments on these 2 products, as well as any other recommended ones.
Also, based on my description, any help in using SQL Profiler (or other MS tool) in helping to identify the root cause.
Thanks...
September 24, 2008 at 12:32 pm
I had used Spotlight on SQL Server Enterprise edition and also SQL diagnostic manager by Idera in different projects and both had given good results.
SQL diagnostic manager gives good historic views while spotlight is more a pro-active tool for monitoring SQL server.
SQL DBA.
September 25, 2008 at 5:03 am
I too have used both IDEARA and Spolight both are very useful but you still need to know what you're looking for....
From what you have said, an inital guess would be your application has a long running transaction that is causing blocking...i.e. other transaction are blocked until the spid at the top of the chain has comlpeted. You can use sp_who and sp_who2 to see what spids are blocked and what spid is blocking them...probably a place to start. You can then use profiler to capture the sql doing the blocking and review with your application developers to see if it can be re-written into shorter transactions.
Hope this helps
Gethyn Elliswww.gethynellis.com
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply