Waitevent 250 in ASE157 and ASE16

[Former Member]

Say for example if you are executing a huge sql file with each statement separated by a "go".

select 1

go

select 1

go

.......go this way up to 50000

go

select * from master..monProcessWaits

where SPID = @@spid

go

The amount of Waits for WaitEventID will be equal to the number of "go" statements in the file. We have measured the Waits and WaitTime between ASE16 and ASE157. We see the WaitTime in ASE16 is 1/3rd of what it is in ASE157.

Why is this so important?

If you are running some benchmarking tests between ASE157 and ASE16 then this becomes an important factor. It will never be like for like.

However, we think this is a good improvement in ASE16 and will help improve elapse times.

Version	ElapsedTime_ms	Waits	WaitTime_ms
ASE16	113,416	2,906,998	983,803
ASE157	276,943	2,907,216	2,581,027

[Mark_A_Parsons]

OK, a few other possibilities that would need to be investigated ...

- ASE 15.7 dataserver has a heavier workload so it's unable to process the client activity is a timely manner

- ASE 15.7 dataserver engine is spending a good bit of time swapped off of the cpu so it's not processing the client activity in a timely manner

- verify ASE configs are comparable (in particular, those config settings that could affect how quickly ASE can respond to a new client request/packet)

[Former Member]

Mark,

Thank you very much for your detailed analysis.

1. The client is the same in both cases.

2. I do not think there is variability in terms of load on the client end

3. I have used sqsh and isql and in both cases and the results were the same

4. The ASE157 is on sp63 and ASE16 is SP02 PL04

5. The load on dataservers is predictable. I have done this test a number of times

6. Dataservers are on identical hardware and also they are in the same datacenter

6. The whole reason this came to our notice was for the fact that we were trying to benchmarch a defined workload which contained 177 million statements (via multiple threads) and ASE157 elapse time was consistently high compared to ASE16

7. The major variance that was saw was on WaitEvent 250

8. It was hard to believe if WaitEvent 250 would be this different. So kept going at it in different forms and the variance is seen consistently

I will dig little deeper and report back!

Thanks again,

Prasad

[Mark_A_Parsons]

If I've got my math right, we're talking about 0.34ms per WaitEvent for ASE 16.0, and 0.89ms per WaitEvent for ASE15.7.

That works out to a difference of 0.55ms per WaitEvent, well within the differences that could be introduced with slight variations in network components (eg, difference in router/packet processing, NIC settings, OS/network stack configs, perhaps a difference in firewall rules, etc).

Can the network/hardware folks rule out any difference in the network components?

Any chance of running some tests against other ASE instances (any version) running on other machines? [Curious if you're seeing comparable differences in WaitTimes for ASE 15.x vs ASE 16.0 on different hosts/network configs.]

[Former Member]

Out of interest, what kernel mode are you using in Sybase 15.7 and Sybase 16 ?

What version of Sybase 16 are you using ?

[former_member89972]

"<snip>

We see the WaitTime in ASE16 is 1/3rd of what it is in ASE157.

</snip>

That is music

Cheers

Avinash