Sql-server – Can mutli-threading java client cause high CPU utilization of SQL Server 2008

javaperformancesql-server-2008-r2

I am a Java Developer, not a DBA, and would like to consult about an issue encountered in high CPU utilization in the SQL Server 2008 (not in the Java app server).

The Java client uses multi-threading, to simplify:

  1. 40 threads each select / insert / update (simple SQL statements) from Table X – autocommit is ON
  2. 10 threads each select / insert / update (simple SQL statements) from Table Y and selects (again simple) from Table X (to check for existence) – autocommit is ON

Both set of threadpools run simultaneously. Each thread is assigned to "message from a queue" (a file), loads / reads its assigned "message", and inserts / updates to its corresponding table. These 2 thread pools get connections from a single connection pool via datasouce, for example:

    <Resource auth="Container" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" maxActive="100" maxIdle="10" maxWait="10000" name="jdbc/abc_DataSource" password="p" type="javax.sql.DataSource" url="jdbc:sqlserver://NTxxx\\instyyy:12345;databaseName=dbzzz" username="u"/>

The DB environment is SQL Server 2008 R2 and is shared by other apps, but the Java client above was flagged to be the causing the CPU utilization. When the Java client was shutdown, the utilization stabilized. From the Java client's application server Jboss, the statistics where the Java was running does not have any performance issues, CPU and memory are fine; it was only the DB Server that got its CPU spiked.

My question is, does the high amount of threads, which will consume the datasource's pool of maxActive 100, potentially be the cause? If so, how can I prove this from the DB perspective, ie. SQL server statistics pointing to the high volumne threads from Java client?

Best Answer

Potentially - yes. Especially on a totally underscaled database server. I would expect problems with 1000 connections, not with something as low as 50.

I would start investigating the SQL sent and see for optimizations there (using precompiled statements) to take load off. Also certain kinds of sub-optimal SQL can lead to significant CPU usage (due to table scans doing tons of processing instead of using an index). For that you need to find the resource intensive queries and analze them (or ask for help here).

Generally: CPU is normally not the limiting factor of a database server, IO etc. come first. But it is possible, to answer your question. Start analyzing.

Last time I had this issue on a large oracle we used 24 cores at nearly 100%, hitting the oracle server with close to 200 threads - and the CPU limitation came out to be an oracle configuration error limiting the database server to ONE CORE. That is how efficient databases are.

And obviously the java app may not even be taxed - if all it does is sending inefficient SQL to the sql server it has not a lot to do except to wait.

Related Question