SQL 2005 Performance

mwebb (mwebb@discussions.microsoft.com) writes:
> We have a win2003 domain with two win2003 servers that host a large SQL
> 2005 database. For some time we (users/sys admin) have been very
> disapointed in the performance of the database whichs uses a
> client-server setup for healthcare records. I am a system admin for the
> hospital.
>
> This week we are running a "stress test" to try and determine if the
> performance problem is related to the hardware/network or the design of
> the SQL database. I have setup counters on the servers that measure the
> performance of the processor, memory, network and hard drives. The admin
> that works the SQL database has setup counters specfic to SQL.
>
> Question: What will give us the best clue(s) as to where the primary
> preformance issue lies (hardware/operating system or database?

Let me put it this way: the odds are very good that the the main issue
lies in how the database is designed or accessed. That is not to say
that an unfortunate hardware design can cause problems as well. But
there is so much more potential to really screw things up with poor
physical design and bad queries.

Therefore I would in an initial phase concentrate on the queries only.
Exactly how depends on the nature of the application. If the application
uses stored procedures only, I would simply collect SQL:BatchCompleted
and RPC:Completed for some time and aggregate to see which procedures
that takes most time and resources.

If the application does not use stored procedures, aggregation is more
difficult, particularly if the application does not parameterise the
queries. Then again, if it doesn't, you try to turn on force
parameterisation for the database. That may make wonders. (Even if
this setting is a cover-up for a bad application design.)

There are also DMV (dynamic management views) that can give a load of
information to determine where things go bad.

It is only when I've found that there are no major issues in the
database or application design that I would start to look at the
hardware. The prime suspect when it comes hardware is often the SAN.
SANs are far from always optimised for database applications, and a
poorly implemented SAN can perform a lot worse than DAS, Directly
Attached Storage.

Finally, it cannot be deined that sometimes it's cheaper to throw
hardware on a poor design than changing the application. (Then again,
fixing the database may be as simple to add a couple of indexes.)


--
Erland Sommarskog, SQL Server MVP, esquel.TakeThisOut@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: SQL 2005 Performance 

Jay Konigsberg (spam@nospam.org) writes:
> In addition to Erland's comment, I would suggest running the SQL Profiler.

Just a word of caution here: Profiler is a great tool to quick monitoring,
and if you have not worked with tracing at all before, it is a good
start.

However, beware that using Profiler rather than a server-side trace takes
far more resources, so if you want to do some long-term monitoring, server-
side trace is a must. (One hour is definitely long-term.)


--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: SQL 2005 Performance