Performance problem...

Hi there.
This is not my 1st post related with performance. I am experiencing serious
performance problems with a VB6 application running over a SQL Server 2000
database. I have already used SQL Analyser, SQL Profiler and Performance
Monitor to collect data that might help resolve this problem. However, my
dificulty is to analyze this data. Is there any tool that it helps to it? I
have downloaded from Microsofts site a tool that collects, at the same time,
data from Profiler, Perfmon and SQL Analyser. However, I have the same
problem... I can't interprete it. This tool is called PSSDIAG. There must be
some powerful tools that analyse the database and provides some hypothetical
solutions. Tools that tell us what is wrong. I do not know none . I would
appreciate some help... some 'freeware' help .
Thanks in advance.
Regards,
Marco
There are some tools, but they're not really better than what you can do
yourself.
A) Get to know your application. Review the architecture. Have someone
else review the architecture. Most performance problems are the result of
poorly thought-out architecture (for instance, a part of the app that needs
to call back to the database 100 or 1000 times to fill a page, instead of
doing just a single query to get all of the data at once).
B) If you determine it's a database problem, start by profiling for
SQL:BatchCompleted and RPC:Completed events and look at the duration column.
Which stored procedures are taking the longest? Those are your easiest
targets. Start tuning them. Look at the execution plans, look at what
indexes exist, and start making things better.
If you need help with that second step, start by reading everything at
http://www.sql-server-performance.com .
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:e$s3iFiNFHA.3328@.TK2MSFTNGP14.phx.gbl...
> Hi there.
> This is not my 1st post related with performance. I am experiencing
serious
> performance problems with a VB6 application running over a SQL Server 2000
> database. I have already used SQL Analyser, SQL Profiler and Performance
> Monitor to collect data that might help resolve this problem. However, my
> dificulty is to analyze this data. Is there any tool that it helps to it?
I
> have downloaded from Microsofts site a tool that collects, at the same
time,
> data from Profiler, Perfmon and SQL Analyser. However, I have the same
> problem... I can't interprete it. This tool is called PSSDIAG. There must
be
> some powerful tools that analyse the database and provides some
hypothetical
> solutions. Tools that tell us what is wrong. I do not know none . I
would
> appreciate some help... some 'freeware' help .
> Thanks in advance.
> Regards,
> Marco
>
|||There are so many factors involved with performance tuning... everything
from optimizing your queries to hardware to indexes to statistics... Do you
have more specific information about your problem?
Google "Performance Monitor SQL Server" and you'll find pages devoted to
interpreting commonly used counters. You can also use QA to view graphic
representations of your estimated query execution plans. Check them out -
if performance used to be good, and just recently became an issue it might
be as simple as missing or out of date statistics...
"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:e$s3iFiNFHA.3328@.TK2MSFTNGP14.phx.gbl...
> Hi there.
> This is not my 1st post related with performance. I am experiencing
> serious performance problems with a VB6 application running over a SQL
> Server 2000 database. I have already used SQL Analyser, SQL Profiler and
> Performance Monitor to collect data that might help resolve this problem.
> However, my dificulty is to analyze this data. Is there any tool that it
> helps to it? I have downloaded from Microsofts site a tool that collects,
> at the same time, data from Profiler, Perfmon and SQL Analyser. However, I
> have the same problem... I can't interprete it. This tool is called
> PSSDIAG. There must be some powerful tools that analyse the database and
> provides some hypothetical solutions. Tools that tell us what is wrong. I
> do not know none . I would appreciate some help... some 'freeware' help
> .
> Thanks in advance.
> Regards,
> Marco
>
|||I can get a good conclusion... it is not a hardware problem. I have already
made many changings to the hardware structure. It can't get much better. I
have a powerful server and very good workstations. The problem must be in
application design and/or database architecture. The application has also
suffered many changes. I have created many SPs instead using dynamic SQL
queries inlaid in aplication.
The last analysis I made tells me that the aplication works fine (or
reasonable) when 3/4 users are connected. In hard periods (10-15 users
connected), the aplication gets very slow. I am realist... 10, 15, 20 users
connected should not be a problem. The database is 1 GB large. I am very
concerned. In some years, or even months, it will get worst... and I do not
know what else must I do...
"Michael C#" <xyz@.abcdef.com> wrote in message
news:NS23e.18750$nE1.5330@.fe09.lga...
> There are so many factors involved with performance tuning... everything
> from optimizing your queries to hardware to indexes to statistics... Do
> you have more specific information about your problem?
> Google "Performance Monitor SQL Server" and you'll find pages devoted to
> interpreting commonly used counters. You can also use QA to view graphic
> representations of your estimated query execution plans. Check them out -
> if performance used to be good, and just recently became an issue it might
> be as simple as missing or out of date statistics...
> "Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
> news:e$s3iFiNFHA.3328@.TK2MSFTNGP14.phx.gbl...
>
|||Just one thing more... how can I work 'missing or out of date statistics'?
"Michael C#" <xyz@.abcdef.com> wrote in message
news:NS23e.18750$nE1.5330@.fe09.lga...
> There are so many factors involved with performance tuning... everything
> from optimizing your queries to hardware to indexes to statistics... Do
> you have more specific information about your problem?
> Google "Performance Monitor SQL Server" and you'll find pages devoted to
> interpreting commonly used counters. You can also use QA to view graphic
> representations of your estimated query execution plans. Check them out -
> if performance used to be good, and just recently became an issue it might
> be as simple as missing or out of date statistics...
> "Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
> news:e$s3iFiNFHA.3328@.TK2MSFTNGP14.phx.gbl...
>
|||I just saw your other post. It sounds like your statistics and/or indexes
might be out of whack a little. Look up CREATE STATISTICS and UPDATE
STATISTICS in BOL. You'll want to make sure you have current statistics for
your tables, and you might want to look for columns that are commonly
queried to ensure you have appropriate indexes.
"Marco Pais" <mpaisIGNORE@.foruminformatica.pt> wrote in message
news:Oh3Y$mqNFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Just one thing more... how can I work 'missing or out of date
> statistics'?
>
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:NS23e.18750$nE1.5330@.fe09.lga...
>