MS SQL Server - Performance issue (I'm stumped)

Author

Performance issue (I'm stumped)

Stijn Verrept

2005-10-27, 11:23 am

We had a SQL server instance running fine for a server which also acted
as terminal server for about 25 users. This ran fine and recently we
decided to split the SQL server to a dedicated server to get better
performance.

We did so and now one of the applications is running really slow. The
application is running on the same server as it was before, only the
SQL connection is now made to the other. There is a procedure that's
taking 15 seconds to complete, while before this was 1 second.

Now I have ran profiler on the procedure both on the old and the new
server and it shows that everything is taking longer to complete. The
hardware of both server are the same, they are connected through a
gigabit network. Network traffic shows 0,5 %.

The databases are exactly the same (as I copied and attached them on
the new dedicated server).

I have zipped the trace files and put them here:
http://www.entrysoft.com/traces.zip TraceNew is the trace of the new
dedicated SQL server (and thus the slow one).

Anyone an idea as to what could be causing this? What else should I be
looking at?

Thanks in advance,

Stijn Verrept.

Rudi Bruchez

2005-10-27, 11:23 am

On Thu, 27 Oct 2005 10:05:57 -0500, Stijn Verrept wrote:

> We had a SQL server instance running fine for a server which also acted
> as terminal server for about 25 users. This ran fine and recently we
> decided to split the SQL server to a dedicated server to get better
> performance.
>
> We did so and now one of the applications is running really slow. The
> application is running on the same server as it was before, only the
> SQL connection is now made to the other. There is a procedure that's
> taking 15 seconds to complete, while before this was 1 second.
>

Hello,

From your trace file, we see that there's no delay from SQL server itself,
both in CPU time and Duration. There's also almost no read, so your queries
should be served quite rapidly.

The trace shows that you're using a server-side cursor, does using a
client-side cursor improve performances ? Is the procedure taking 15
seconds a stored procedure, or some client code looping through a
resultset?

also, you could watch some counters on performance monitor, like CPUs,
locking (lock wait time, locks timeout, locks pers sec.)...

Rudi Bruchez

Stijn Verrept

2005-10-27, 5:17 pm

Rudi Bruchez wrote:

> From your trace file, we see that there's no delay from SQL server
> itself, both in CPU time and Duration. There's also almost no read,
> so your queries should be served quite rapidly.
>
> The trace shows that you're using a server-side cursor, does using a
> client-side cursor improve performances ? Is the procedure taking 15
> seconds a stored procedure, or some client code looping through a
> resultset?
>
> also, you could watch some counters on performance monitor, like CPUs,
> locking (lock wait time, locks timeout, locks pers sec.)...

Thanks for your answer Rudi. The application isn't mine so I can't
alter it to client-side cursors. The client code is taking 15 seconds.
But it is exactly the same program, only difference is that we set a
different server name as before and connect through TCP instead of
named pipes (but I also tried with named pipes which gave no
difference). CPU is low, as are locks.

Stijn Verrept

2005-10-27, 5:17 pm

Rudi Bruchez wrote:

> From your trace file, we see that there's no delay from SQL server
> itself, both in CPU time and Duration. There's also almost no read,
> so your queries should be served quite rapidly.

But I still think the difference is there. Did you compare the two
traces? In the old server reads are almost always zero, on the new one
it 6 for almost every and in the end there are a couple of >300 read.
What could explain that difference?

Rudi Bruchez

2005-10-31, 3:23 am

On Thu, 27 Oct 2005 11:59:15 -0500, Stijn Verrept wrote:

> Rudi Bruchez wrote:
>
>
> But I still think the difference is there. Did you compare the two
> traces? In the old server reads are almost always zero, on the new one
> it 6 for almost every and in the end there are a couple of >300 read.
> What could explain that difference?

Have a look at the query plans. Add the Show Plan Statistics event in your
trave, and compare the two. If you see some tables scanned, look at their
indexes and statistics.
You said that CPU and locks are low, what about the disk ? Have a look at
the network interface counters in perfmon, too.

Also, what happens to this trace when you replay it in the profile ? Is it
still slow ?

Sponsored Links