Dear All
My SQL Server is contains 2 databases. Both of them are used by
different client/server application and each application uses several
stored procedure to query and update its database. The problem is my
users reports that those two application respond slowly.

I used SQL Profile, and here the result.

EventClass______________________TestData
SQL:BatchCompleted If @@TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update TableA  Set Col1 =
SQL:BatchCompleted Update TableB Set Col2 = '23456..
SQL:BatchCompleted If @@TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update TableA Set Col1=


What should I do to improve the performance?
- by using views to query data or modify the stored procedure so that
they select data into temporary tables.
Pls give me your advice

Thanks

Robert Lie

There's a boundless number of potential issues and bottlenecks.  Far more
information is needed to help with this.  First try to gather some
perfromance stats.  Is the server busy?  Is there blocking?  What
perfromance do you get when running in query analyzer?

Show quoteHide quote

As mentioned by Danny, there can be a lot of reason. Before you just jump to
Profiler, I'd investigate a bit more what the users means that the
applications respond slowly. It might help to know if it's something that
has happended all of a sudden or if performance has degraded slowly over
time. It will also be different things to look for if it's a new application
that just never has performed decent.
If performance has degraded slowly over time, I'd look at things like
missing statistic update, Index defragmentation, memory consumption,
Disksystem defragmention, logfile size (i.e. does it autogrow too often?),
disk space etc.
If performance has degraded all of a sudden, I'd more look for diskproblems,
disk space issues, server HW problems, CPU utilization etc. I'd then also
check what have been done to the server in terms of updates (Windows, SQL,
AntiVirus etc) to see if it could something that are consuming a lof of CPU.
If performance never has been decent with those applications I'd look at
more basic things like server configuration, RAID, disksystem, memory etc.
It could also be poorly designed applications, but if it's two applications
independent of each other, it's not very likely that they both suffer from
poor design.

I hope that some of the above can help you to troubleshoot the issues?

Regards
Steen

robert lie wrote:
Show quoteHide quote

sql data corruption
Format Files - Bcp
Images and PDF's in the same DB or in another DB?
how to resolve deadlock issues
Problem with different version of SQL Server
validate data of target database against source database structure..How?
Restore needs too much space
Do I still need pagefile?
List all the logins that have a certain privileges
Configuration