Performance sanity check

Hi all,
I hope someone will be kind enough to have a look over this for me and
let me know if I'm correct.
We bought an application to do timetabling a couple of years ago. Last
year we had terrible problems with system performance, during the busy
period in August/September the system became basically unusable. We
tracked that down to disk performance, and after toying with using the
SAN, eventually went for Solid State Disk (RamSan 300) after some
careful analysis of the SAN. We also moved the application up to a new
4x3GHz Xeon machine with 4GB RAM.
We've just run a simulated load test to see if we think the system will
be able to stand up to the load this year and are very concerned about
the results. The load test had 20 users in it.
All IO/Memory/Paging/Compilation counters etc seemed to show the server
wasn't stretched, however the CPU load went to 100% and stayed there for
the duration. The number of Page locks got to 70,000 (last year it was
230,000 + so we've improved something!) and we saw several blocking
chains with the lead blocker in a 'sleeping' state. The wait times for
the blocked processes got to in excess of 500 seconds.
The suppliers say we need a bigger server, my concern, since we're on a
far bigger server is that the app is just highly inefficient and any
size server will get swamped by it...
Following the load test I've been looking at the Profiler tool. The
output seems to show the app uses server side cursors (we've noticed
some very heavy IO on tempdb that seems to support this). I dimly
remember reading something in the past that high CPU can be a symptom of
high lock counts rather than a cause of it - is this right?
Any help or advice will be much appreciated :-)
cheers
daveDave
Try run SQL Server Profiler to identify a long running queries/stored
procedures (look at DURATION ) , so once you have identified them , take a
look at how you casn improve it , may be adding indexes to the table or
somethinmg else
Looking at what you gave provided it seems that the APP is using cursors
(blocking/cursors) which is really bad in terms of performance
Speek to the vendor to improve the app
"Dave Thornley" <cisdht@.yahoo.com> wrote in message
news:uWzgAdxrGHA.2256@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> I hope someone will be kind enough to have a look over this for me and let
> me know if I'm correct.
> We bought an application to do timetabling a couple of years ago. Last
> year we had terrible problems with system performance, during the busy
> period in August/September the system became basically unusable. We
> tracked that down to disk performance, and after toying with using the
> SAN, eventually went for Solid State Disk (RamSan 300) after some careful
> analysis of the SAN. We also moved the application up to a new 4x3GHz Xeon
> machine with 4GB RAM.
> We've just run a simulated load test to see if we think the system will be
> able to stand up to the load this year and are very concerned about the
> results. The load test had 20 users in it.
> All IO/Memory/Paging/Compilation counters etc seemed to show the server
> wasn't stretched, however the CPU load went to 100% and stayed there for
> the duration. The number of Page locks got to 70,000 (last year it was
> 230,000 + so we've improved something!) and we saw several blocking chains
> with the lead blocker in a 'sleeping' state. The wait times for the
> blocked processes got to in excess of 500 seconds.
> The suppliers say we need a bigger server, my concern, since we're on a
> far bigger server is that the app is just highly inefficient and any size
> server will get swamped by it...
> Following the load test I've been looking at the Profiler tool. The output
> seems to show the app uses server side cursors (we've noticed some very
> heavy IO on tempdb that seems to support this). I dimly remember reading
> something in the past that high CPU can be a symptom of high lock counts
> rather than a cause of it - is this right?
> Any help or advice will be much appreciated :-)
> cheers
> dave|||Hi Dave
High CPU can be the result of poor code and you may want to look at the lock
events such as lock escallation in SQL profiler. You can also use the
profiler to find out what the I/O intensive queries are see:
http://www.sql-server-performance.c...nce_audit10.asp
You may then want to go back to the vendor and ask them to improve it.
Also check that if there are maintenance routines that will update your
indexes and statistics that they have successfully been run.
For blocking you may want to look at http://support.microsoft.com/kb/271509
HTH
John
"Dave Thornley" wrote:

> Hi all,
> I hope someone will be kind enough to have a look over this for me and
> let me know if I'm correct.
> We bought an application to do timetabling a couple of years ago. Last
> year we had terrible problems with system performance, during the busy
> period in August/September the system became basically unusable. We
> tracked that down to disk performance, and after toying with using the
> SAN, eventually went for Solid State Disk (RamSan 300) after some
> careful analysis of the SAN. We also moved the application up to a new
> 4x3GHz Xeon machine with 4GB RAM.
> We've just run a simulated load test to see if we think the system will
> be able to stand up to the load this year and are very concerned about
> the results. The load test had 20 users in it.
> All IO/Memory/Paging/Compilation counters etc seemed to show the server
> wasn't stretched, however the CPU load went to 100% and stayed there for
> the duration. The number of Page locks got to 70,000 (last year it was
> 230,000 + so we've improved something!) and we saw several blocking
> chains with the lead blocker in a 'sleeping' state. The wait times for
> the blocked processes got to in excess of 500 seconds.
> The suppliers say we need a bigger server, my concern, since we're on a
> far bigger server is that the app is just highly inefficient and any
> size server will get swamped by it...
> Following the load test I've been looking at the Profiler tool. The
> output seems to show the app uses server side cursors (we've noticed
> some very heavy IO on tempdb that seems to support this). I dimly
> remember reading something in the past that high CPU can be a symptom of
> high lock counts rather than a cause of it - is this right?
> Any help or advice will be much appreciated :-)
> cheers
> dave
>|||Hi Dave,
Since the application suffers from heavy locking/blocking you should
considder upgrading to SS2005 and use snapshot isolation.
ALTER DATABASE [S] SET read_committed_snapshot ON
Be sure that tempdb is big enough :-)
The application you are talking about sounds a lot like Axapta :-)
Do you reindex your clustered indexes from time to time - be sure there is a
clustered index on the tables?
You should look at the wait statistics for the different sessions - have you
heard about the YAPP method?
Good luck :-)
"Dave Thornley" wrote:

> Hi all,
> I hope someone will be kind enough to have a look over this for me and
> let me know if I'm correct.
> We bought an application to do timetabling a couple of years ago. Last
> year we had terrible problems with system performance, during the busy
> period in August/September the system became basically unusable. We
> tracked that down to disk performance, and after toying with using the
> SAN, eventually went for Solid State Disk (RamSan 300) after some
> careful analysis of the SAN. We also moved the application up to a new
> 4x3GHz Xeon machine with 4GB RAM.
> We've just run a simulated load test to see if we think the system will
> be able to stand up to the load this year and are very concerned about
> the results. The load test had 20 users in it.
> All IO/Memory/Paging/Compilation counters etc seemed to show the server
> wasn't stretched, however the CPU load went to 100% and stayed there for
> the duration. The number of Page locks got to 70,000 (last year it was
> 230,000 + so we've improved something!) and we saw several blocking
> chains with the lead blocker in a 'sleeping' state. The wait times for
> the blocked processes got to in excess of 500 seconds.
> The suppliers say we need a bigger server, my concern, since we're on a
> far bigger server is that the app is just highly inefficient and any
> size server will get swamped by it...
> Following the load test I've been looking at the Profiler tool. The
> output seems to show the app uses server side cursors (we've noticed
> some very heavy IO on tempdb that seems to support this). I dimly
> remember reading something in the past that high CPU can be a symptom of
> high lock counts rather than a cause of it - is this right?
> Any help or advice will be much appreciated :-)
> cheers
> dave
>|||Thanks for the help and suggestions guys.
dave