Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
daibaocun
Starting Member
9 Posts |
 Posted - 2011-01-05 : 10:43:57
|
| env: Jboss + MSSQL server 2008.JBoss and MSSQL server installed at two different machines.JBoss utilized about 10% CPU. MS SQL 2008 utilized about 100% CPU.Using Activity monitor to monitor SQL server and found there are many CXPACKET type lock.Set "Cost threshold Paralleism" to 10Set "Max Degree of Paralleism" to 1the lock issue became better.But the CUP still heavily used by SQL server. Why? and how to know what happens underneath inside SQL server? |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-05 : 12:56:53
|
| Probably missing indexes.Use sp_who2/dbcc inputbuffer or profiler to find the queries churning up the cpu. Then you'll need to tune those. |
 |
|
|
daibaocun
Starting Member
9 Posts |
Posted - 2011-01-05 : 22:07:26
|
| Hi russell, thanks for your reply.I used sp_who2 and found some sleeping SPID still utilized CPU. It is unbeliveable.Now my connection pool size is 200. there are many sleeping SPIDs. I do not know what percentage of CPU time used by sleeping SPID. I am about to try to reduce the pool size to see the effect and let your know result. thanks, |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-06 : 12:41:34
|
| You should be more concerned with the runnable SPIDs and see what they are doing. |
|
|
|
daibaocun
Starting Member
9 Posts |
Posted - 2011-01-19 : 22:08:18
|
| Hi russell,The reason is index issue. we create one index on varchar(36) field. but the data in the table is very large. I request dev change the column type from varchar(36) to int. the issue was resolved.thanks, russell! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-20 : 09:23:21
|
| Awesome. Glad you got it resolved! |
|
|
|
|
|
|
SQL server 2008 utilize almost 100% CPU.