Minimize performance impact from one user

[bgreiman]

We are using SQL Anywhere v12.01.3810.

We have some large postings that run for about 4 hours (Day End type postings) that we schedule to run at Midnight each night. We also have a shift of workers that are doing transactional type work (small queries / update / insert statements) at the same time. The problem we have is that some of the postings are very intensive and are causing performance issues for the other workers.

What are your suggestions in this case?

I have tried setting priority of posting user to Low (SET OPTION "Admin"."priority" = 'Low';) and our transactional user to Critical. I am going to change priority of our "Admin" user to Background to see if that will make a difference.

Thanks for any suggestions.

Brian

[justin_willey]

When you say performance issues, do you mean slowness, pauses etc rather than blocked connections, deadlocks or actual failures?

Does your "posting" user do everything in one big transaction or is it done bit by bit? Is there any deleting involved? Are the transactional users working on the same tables as the posting user?

Sorry for lots of questions rather than answers 🙂

[Breck_Carter]

Justin's questions are the right ones to ask. Some of them can be answered by running Foxhound.

[bgreiman]

Pauses. The stored procedure calls triggered the "transactional" users are stalled out. I don't believe there are any blocking / deadlocks - but that is an angle I could pursue.

"Posting" user is running many steps with updates / inserts / deletes involved. Not typically using the same tables as the "posting user".

[Breck_Carter]

Here are some questions to ask about the overall server performance (other questions can be asked about individual connections)...

What is the cache size? What percentage of the maximum has it grown to? (from CurrentCacheSize and MaxCacheSize properties)

Is the cache big enough? (from the CacheHits, CachePanics, CacheRead and QueryLowMemoryStrategy properties)

What is the throughput? (requests per second, commits per second, bytes per second from Req, Commit, BytesReceived and BytesSent properties)

What is the latency? (response time to a simple query like select * from dummy)

What are the numbers of active request being processed and waiting requests? (from the ActiveReq, MultiProgrammingLevel, Threads and UnschReq properties)

How many locks are being held and connections blocked? (from the LockCount and BlockedOn properties)

How much CPU time is being used? (from the NumLogicalProcessorsUsed, NumProcessorsAvail, NumProcessorsMax and ProcessCPU properties)

How much temporary space is being used? (from the PageSize and TempFilePages properties)

Is the disk drive being pounded? (from the DiskRead, DiskWrite, LogWrite, IndAdd, IndLookup and FullCompare properties)

( FWIW Foxhound shows you all that stuff on a single page, continuously, 24 x 7... 🙂