sql server concurrency performance

Question

We have encountered some performance issue on our production environment.

we found that when active sessions go up above 25, the usage of CPU reach to 100% and it takes long time to go down.

---

The environment we have:

Product Microsoft SQL Server Enterprise Edition 9.3(sp2)

CPUs 2(Xeon 2.13)

Memory 7G

snapshot of session detail1

Active Sessions 25

Active Transactions 496

Idle Sessions 289

Blocked Transactions 29

snapshot of session detail2

Active Sessions 59

Active Transactions 885

Idle Sessions 267

Blocked Transactions 49

---

I'd like to know:

1. whether 2CPUs can handler 25 active sessions (500 active transactions ) well.PS: we have test that without concurrency request,one transaction ,which read/write 5 tables, takes about 1sec in application level.

2. whether the blocked transactions takes more CPU usage.PS:the blocked transactions is mainly because of locks on 2 tables.

what's the solution: Add CPUs ?or tuning application(java/hibernate) to shorten this transaction and to decrease blocks on the table?

Answer 1

Your options to have a good picture of the situation when everything is crawling:

• use server side traces to see the longest and most heavy sessions
• use the Who is Active stored procedure by Adam Machanic - to save the information of the moments when the server is loaded - fantastic free script
• use the Activity Monitor from Management Studio just to get a visual glance (Cpu and IO are the ones that I found the most useful) - pretty good tool included in Management Studio
• use a 3rd party free tool - Confio Ignite Free - that's very good for moments when everything is slow and need to see details about the wait stats, blocking..etc. - fantastic free tool
• check statistics to be up to date
• check if indexes are fragmented and, if so, defrag them
• follow the other advices of checking missing indexes, design
• check the possibility of using the snapshot isolation level in your database (you have high blocking so it's good to see if you really need your current isolation level)

And good luck investigating the problems :-).

Answer 2

Your problem is most likely poor query performance caused by

• poor design
• poor indexing

Locks/blocking come from poor indexing because of all the time spent scanning tables end to end. CPU is irrelevant here.

As a quick fix, check for missing indexes using the information in this article: Link

Answer 3

I agree with GBN and Marian.

To answer your question regarding 2-CPUs handling 25 requests: I have a 2-CPU system which supports about 750 user connections and a running average of 4K Batch Requests a second. Several items are important for me: Design, Management, and Tuning. If you start with a poor design of your application and database then you will fail at load (think scaling).

High CPU may indicate memory pressure. You mention there is only 7GB of memory available to SQL Server. If you have poorly performing indexes (Too many indexes, incorrect indexes, or no indexes) then this will cause the system to page more of the database into memory for various requests then if there are appropriate indexes. I would caution going hog-wild creating indexes as the wrong ones will also hurt you as each has the potential to require an update in the course of a row update (Create-Update-Delete) operation.

Also, using the Missing Index DMVs requires using what you know of your application and database and not just implementing each recommended index. I would checkout Kimberly Tripp's blog entries regarding indexes here. After the Index section, looking at the other categories might be helpful to you situation.

If your Java/Hibernate update of 5 tables in a single transaction is doing the updates via multiple round-trips to the database, then you are leaving yourself open to contention (Blocked CRUD requests). The issue worsens if the application is not able to return to the database in a timely manner. While in the application, the associated active transaction could block other requests from processing and cause request time-outs.

Add the two above issue together and you start to have a nasty case of performance headaches.

Reducing the number of database round-trips within the context of a single transaction would be a very good thing to pursue. Perhaps a stored procedure would help.

The rest will require work in order to get your application to scale. You might also consider memory, but that should come after a design and performance review is done and the needed changes implemented as adding memory right away will mask your issues.

Absolutely follow the suggestions Marian has outlined.

I would say you have found yourself a wonderful challenge and wish you a great success!

Answer 4

1. In my expirience we have MS SQL Server 2000 with 2 cpu and more than 30 active sessions and more than 1000 transactions. It was hard to server, but it worked.
2. I'm not sure that locks uses more CPU. Locks and CPU usage, in my opinion, are two different performance problems.

As for solution, first you should e sure that your main problem is CPU. Try to read this issue to find the source of your problems and to get appropriate solution. Finnaly, if you can to make your transaction as small as you can - do it.

Answer 5

My first thoughts are to reduce the number of transactions and reduce the amount of blocking. Can you break apart some of the transactions into multiple bits? Can you pull some of the queries out of the transactions? As Alex_l mentioned--the smallest possible transaction is ideal here.

I agree with gbn, adding indexes also might help.

Another thought is that I would also take a look at your locks. Are you taking out table-level locks when you are only updating a single row? You might consider suggesting a row-level lock to SQL Server. That would solve a lot of problems. (Granted, if you've got 5 tables, that's probably not the case, but just a thought.)

Finally, I would take a look at your tables that you're using. If everyone is blocking on a particular table, can you move the logic for that table to the end of your transaction? If you can reduce the time that you hold a lock on that high-demand table, it could help.