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:
- 40 threads each select / insert / update (simple SQL statements) from Table X - autocommit is ON
- 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?