Hello, We have resolved our SQL server CPU issue and I thought i'd post the resolution here in case anyone is interested.
We switched from Windows server 2008 to Windows server 2008 R2 (although this didn't make any difference to performance, it was simply that our database server got upgraded so the latest OS went on) The RAM was increased from 4GB to 8GB which initially didn't show any improvement until AWE (Address Windowing Extensions) was forced on, that resulted in a much higher cache usage by SQL. We added a second CPU which made a big difference, and it was also found that with VMWare (and I'm trying to regurgitate what our DBA told me :)), a single CPU with two cores apparently doesn't do multi- threading as you would expect, the VM only sends one thread at a time to it. It wasn't until a second CPU was added that it would do proper multi-threading (at least this is how it was in our overall VM configuration). So now our CPU activity on the server is fine, so it seems it was a combination of things server wise that fixed it, we also had a good look into all our queries and didn't find any problems there. Barry. On Nov 24, 10:13 am, BarryC <barrychester...@gmail.com> wrote: > Hi, > > We have an issue currently where our Microsoft SQL server runs close > to 100% CPU for a lot of the time. There also seems to be a lack of > memory cache hits (we would expect a reasonable amount given the > traffic for our site). > > Our website generates a lot of small queries (approximately 200odd > queries a second on average, which includes all our cluster servers), > most of these queries complete in under 2 or 3 milliseconds. > > Has anyone had anything similar to this, or may know of any related > issues to do with the servers / software versions we are running? this > is our configuration; > > Microsoft SQL server 2008, single CPU on VMWare. > Coldfusion 9 (no updaters) - runs on Linux Red Hat servers with JRun > 4. > Our Coldfusion datasource driver is the Microsoft SQL server drvier > that ships with CF 9. > > Our datasource configuration parameters are: > > Driver: MSSQLServer > JDBC URL: jdbc: macromedia:sqlserver://ourservername: > 1433;databaseName=dbprod;SelectMethod=direct;sendStringParametersAsUnicode=true;querytimeout=0;MaxPooledStatements=1000 > Login timeout: 30 seconds > Long text buffer size: 256000 > Timeout: 1200 seconds > Maintain connections: Yes > Interval: 420 seconds > Restricted SQL operations: > Disable connections: No > CLOB is enabled (as well as High ASCII characters) > > I know this isn't an SQL group, but someone might have run into this > before :) > > It is possible we just need to add another CPU to our DB server, but > we are trying to eliminate other possible issues first. > > Thanks > Barry -- You received this message because you are subscribed to the Google Groups "cfaussie" group. To post to this group, send email to cfaus...@googlegroups.com. To unsubscribe from this group, send email to cfaussie+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
