Load Testing and Performance  
Author Message
Genetracer





PostPosted: Sat Apr 07 10:56:00 CDT 2007 Top

SQL Server >> Load Testing and Performance

We have an asp application some of which will be being ported over to
asp.net so this may be a little OT. It is having some bad performance
problems and we need to isolate which areas are causing the problems. The
main performance problems are database related i.e. timeouts and deadlocks

Some questions:
Microsoft's ACT. How is it better than the Web Stress Application Tool?
Is ACT purely .net or can be used to look at any web app.
What is a good way of assessing which pages are causing the problems.
How can I relate the performance of the database to the application. The
system has LOTS (this app has 1000's of pages) of sql passed as strings will
this affect how easy the profiler is to read.
I suspect there are lots of memory leaks, are there any tools to look at
what is being left in the memory.

I guess what I want is a tool which will identify which pages are hogging
the most resources and if they are not being released so we can look at
optimising the code.

SQL Server140  
 
 
jbellnewsposts





PostPosted: Sat Apr 07 10:56:00 CDT 2007 Top

SQL Server >> Load Testing and Performance Hi Chris



> We have an asp application some of which will be being ported over to
> asp.net so this may be a little OT. It is having some bad performance
> problems and we need to isolate which areas are causing the problems. The
> main performance problems are database related i.e. timeouts and deadlocks
>
> Some questions:
> Microsoft's ACT. How is it better than the Web Stress Application Tool?
> Is ACT purely .net or can be used to look at any web app.
> What is a good way of assessing which pages are causing the problems.
> How can I relate the performance of the database to the application. The
> system has LOTS (this app has 1000's of pages) of sql passed as strings will
> this affect how easy the profiler is to read.
> I suspect there are lots of memory leaks, are there any tools to look at
> what is being left in the memory.
>
> I guess what I want is a tool which will identify which pages are hogging
> the most resources and if they are not being released so we can look at
> optimising the code.
>
You don't say which version of SQL Server you are using!

For SQL 2000
Check out
http://www.sqlservercentral.com/columnists/skumar/tracingdeadlocks.asp
http://support.microsoft.com/kb/224453 and
http://support.microsoft.com/kb/271509

If you have access to SQL 2005's SQL Profiler then that will produce
deadlock diagrams for you which should be easier to understand.

If you make heavy use of tempdb the issue may be with that, rather than your
own database.

I have not used the Web Stress Tool, so I can't say which is better I do
know that ACT will work with older ASP! You may want to look at Visual Studio
Team Test, if you have your developers write unit tests then it may be that
these could be re-used later on in the testing process.

As you are using alot of dynamic SQL then it may be difficult to find all
the area where poor code is being sent to the server, also you may have left
yourself open to SQL Injection if the dynamic SQL is not being handled
correctly. Having designed your application in this way has made it very
difficult to debug as you have found out already! You may want to start by
looking at the transaction handling and if the transactions are being started
unnecessarily or are being taken out for too long. Adding comments to the SQL
being passed may be a method of identifying where it is being called from,
this could mean major changes to the code. You may be able to identify the
calling page by tracking what other statements have been issued on the given
spid in a profiler trace, this is made difficult if you have connection
pooling.

You may want to look at the cleartrace tool to identify problems statements
in a profiler trace http://www.cleardata.biz/cleartrace/default.aspx of for
SQL 2000 you could also use Read80Trace dowloadable at
http://tinyurl.com/ya9ewz
this can highlight slow running statements which may indicate missing
indexes or poor code.

HTH

John