Microsoft SQL server CPU very high, close to 100% a lot of the time
BarryC
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
Steve Onnis
experiences running SQL server on VMWare. There just isn't the grunt there
to do it. I started shifting my SQL servers to VMs and started having CPU
issues so i moved them back to physical servers.
Hi,
Thanks
Barry
--
You received this message because you are subscribed to the Google Groups
"cfaussie" group.
To post to this group, send email to cfau...@googlegroups.com.
To unsubscribe from this group, send email to
cfaussie+u...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/cfaussie?hl=en.
=======
Email scanned by PC Tools - No viruses or spyware found.
(Email Guard: 7.0.0.21, Virus/Spyware Database: 6.16350)
http://www.pctools.com/
=======
=======
Email scanned by PC Tools - No viruses or spyware found.
(Email Guard: 7.0.0.21, Virus/Spyware Database: 6.16350)
http://www.pctools.com/
=======
Kai Koenig
Another thing I'd look into before moving it away from VMWare though would be the VMWare setup itself, e.g. assigned memory, giving it more CPU power etc. How's your disk IO looking on that VM?
Cheers
Kai
--
Kai Koenig - Ventego Creative Ltd
ph: +64 4 476 6781 - mob: +64 21 928 365 / +61 435 263 414
web: http://www.ventego-creative.co.nz
blog: http://www.bloginblack.de
twitter: http://www.twitter.com/agentK
!!! New AU mobile number !!!
--
Joey Daly
Carl
Some people say to obtain maximum performance the JDBC driver should
transmit data
to the database server in packets that are equal in size to the
maximum packet size configured on the database server.
For SQL to check packet size:
-SQL Management Studio (connect to server etc)
-Server Properties
-Advanced
-Network
-Network Packet Size
Network Packet Size is typically 4096
In your JDBC driver add ;PacketSize=8 to the JDBC URL. Note:
PacketSize=8, the packet size is set to 8 * 512 bytes (4096 bytes)
HTH, Carl.
On Nov 24, 7: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).
>
>
> 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:
> 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)
>
Kym Kovan
> What Steve says is def. contributing to it.
>
> Another thing I'd look into before moving it away from VMWare though would be the VMWare setup itself, e.g. assigned memory, giving it more CPU power etc. How's your disk IO looking on that VM?
We use SQL2008 on win2008 R2 on VMs with no issues at all. 500 or 600
transactions per sec on a single server.
Definitely set the vCPU count to 2 or more and scads of RAM, ie dedicate
a third or a half of your host to the DB server. Our rule-of-thumb is 3
big-end DB servers per host or 20 CF9 servers :-)
These are dual quad core hosts so give your SQL server the equivalent of
two or more cores but the critical thing is 2 or more vCPUs so SQL can
split its threads more efficiently as if it thinks it has only one CPU
it changes mode internally.
Check your writing rate, if there is a lot then the temp db and the
transaction log are getting written to a _lot_, we see several MBps on
peaks and then things like your network speed into the SAN
infrastructure start affecting things.
--
Yours,
Kym Kovan
mbcomms.net.au
Pat
jtds.sourceforge.net/) used to be faster than default JDBC driver CF
come's with for MSSQL
Carl
I use the divers built in CF. The JDBC divers in CF8/9 are type4. I
see datadirect has released jdbc type5 drivers:
http://web.datadirect.com/products/jdbc/index.html
Regards, Carl.
Kai Koenig
- the type doesn't reflect on the quality. E.g. a type 4 driver is not better than a type3 driver and a type5 driver not necessary better than a type4 driver. They just do things in different ways. I just want to avoid that the type is seen as a quality indicator.
- there is no such thing as jdbc type5 drivers. The product Carl is pointing to is nothing but a vendor-specific implementation of a type4-driver with a few new and different features that Data Direct have decided to give as much marketing spin as they can and call it "type5".
- the features in that "type5" driver that appear to make them be miles ahead are mostly irrelevant from a CF point of view imho. For Java developers - looks certainly interesting. But a lot of the stuff this driver promises to fix is dealt with within CF anyway so that I think it won't be relevant for CF devs.
- I haven't looked at the price tag, but I assume the DD drivers don't come free of charge.
Happy to be proven wrong in all of my points though :-)
Cheers
Kai
> --
> You received this message because you are subscribed to the Google Groups "cfaussie" group.
> To post to this group, send email to cfau...@googlegroups.com.
> To unsubscribe from this group, send email to cfaussie+u...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/cfaussie?hl=en.
>
Carl
Did any ot those suggestions help sort your problem out?
Briefly I think there are:
- yes to VM but more cores (which you already raised)
- no to VM
- packetsize setting match
- alternate JDBC driver
To add to JDBC driver change, you could try the free type4 SQL driver
from Microsoft. Might reiterate I use the drivers built-in CF and am
not canvassing for DD product.
Regards, Carl.
Pat
marketing spin.
Carl, swapping out your JDBC drivers would be a fairly small amount of
effort to see if it gives you any performance improvements. You could
just run your regression tests over your app after the swap and see
the footprint is lower.
On Nov 24, 2:25 pm, Kai Koenig <k...@koeni.de> wrote:
> Just a quick note to make a few things clear:
>
> - the type doesn't reflect on the quality. E.g. a type 4 driver is not better than a type3 driver and a type5 driver not necessary better than a type4 driver. They just do things in different ways. I just want to avoid that the type is seen as a quality indicator.
>
> - there is no such thing as jdbc type5 drivers. The product Carl is pointing to is nothing but a vendor-specific implementation of a type4-driver with a few new and different features that Data Direct have decided to give as much marketing spin as they can and call it "type5".
>
> - the features in that "type5" driver that appear to make them be miles ahead are mostly irrelevant from a CF point of view imho. For Java developers - looks certainly interesting. But a lot of the stuff this driver promises to fix is dealt with within CF anyway so that I think it won't be relevant for CF devs.
>
> - I haven't looked at the price tag, but I assume the DD drivers don't come free of charge.
>
> Happy to be proven wrong in all of my points though :-)
>
> Cheers
> Kai
>
>
>
> > Hello Pat,
>
> > I use the divers built in CF. The JDBC divers in CF8/9 are type4. I
> > see datadirect has released jdbc type5 drivers:
>
> >http://web.datadirect.com/products/jdbc/index.html
>
> > Regards, Carl.
>
> >> I haven't checked in a while but the jtds driver (http://
> >> jtds.sourceforge.net/) used to be faster than default JDBC driver CF
> >> come's with for MSSQL
>
> > --
> > You received this message because you are subscribed to the Google Groups "cfaussie" group.
> > To post to this group, send email to cfau...@googlegroups.com.
> > To unsubscribe from this group, send email to cfaussie+u...@googlegroups.com.
Kym Kovan
> Good clarifications Kai. Always nice to cut through some of the
> marketing spin.
>
> Carl, swapping out your JDBC drivers would be a fairly small amount of
> effort to see if it gives you any performance improvements.
Are we drifting off-track? The OP complaint was 100% CPU, network
performance has only a minor effect here.
I'd say the two "we don't have a problem" replies out vote the single
"VMs are no good" reply so try the "more CPU" route first.
If the host is grunty then build a new VM with more than a single vCPU
and install SQL2008 on that and see what you get. (It depends on your OS
licensing but we find it convenient to keep 1, 2, and 4 vCPU templates
sitting there so you can get a new machine up in a few minutes then
throw your applications on.) If the host is not very grunty then assess
what you have got, just the one VM maybe?, etc.
Barry Chesterman
--
charlie arehart
Barry, here are some other thoughts I’ve not yet seen mentioned. (Like you, I am behind on this thread due to vacation, though in my case it was my time there in OZ—Melbourne, specifically—the past two weeks. Great time. Thanks to all involved and those who I met while there.)
As for your problems, are you saying that SQL Server itself is running at 100%? Or is that the whole box? If the latter, there could of course be many influences. I’ll assume that this SQL Server implementation is on a box separate from CF, right? Is anything else running on the SQL Server box? Specifically, any antivirus tool? I’ve helped solve problems with people who had an a/v tool set to check every file when it was modified—and of course, a DB engine would be modifying its files extremely frequently—which was using a lot of resources on the SQL Server box.
But assuming that it’s SQL Server itself that’s running 100%, the next question would be: do you see anything in the Windows Event logs?
Also, when it happens, have you looked at any tools on SQL Server to see what queries are running and whether any one (or some) are causing a lot of the high CPU use? It could be, though it may not be.
And you may be wondering if something in CF is influencing things. Do you have anything that’s monitoring the SQL being sent from CF to the DB, to see if there’s a connection, whether in terms of specific high-CPU queries, or perhaps just a high volume of queries? You could monitor such queries either from the SQL Server side (such as with SQL Server Profiler or other tools), or you could monitor from the CF side (with tools like the CF Server Monitor, FusionReactor, or SeeFusion). It could be very insightful to discover if something is contributing a high load of queries.
It could be perhaps spiders or bots, or a hack attack, or perhaps just a coding mistake. It could also be a configuration issue, such as client variables being enabled and set to write to a DB, with the “disable global client variable updates” option leading to queries on every page request and inserts/updates on many of them. Or it could be the new “validation query” feature added in CF 8 (I believe) which is defined in the CF DSN “advanced settings” and executes the specified query when a connection in the connection pool is initialized/reused.
Finally, you mention running CF 9 with no updater. If none of the above helps, it would seem reasonable to read about and perhaps try implementing the cumulative hotfix, or the updater (and its CHF). Similarly, you may want to check if perhaps there are SQL Server updates you could/should apply which could address such a “100% cpu issue”.
Hope that helps.
/charlie
Barry Chesterman
It is SQL running on its own box, I'll get the DBA to check what else is running (such as antivirus).
I'm pretty sure he checked the event logs and there was nothing odd there, he pretty much said that the query activity is normal, just that there is a very high volume of queries coming through.
We did look at the queries coming through and there were no queries in particular that stood out as causing additional CPU usage, all the queries completed executing in a very quick time.
The only SQL monitoring we have is on one of our 4 front end servers, and that's just FusionReactor monitoring at most 30 queries per request, and only queries above 2 miliseconds. I did have it on 50 queries and anything above 0ms for 10 or so minutes, but I haven't spotted anything (nor has our DBA) that looks out of place.
We haven't got the validation query thing on.
We haven't spied any kind of hack attacks in our http logs (I did disable one of our pages that gets hit a lot and does a decent number of queries, but the DBA said it pretty much had no effect).
I'll check out the client variables setting, but I'm pretty sure that setting is all good as well.
I did look up the updater notes and there was nothing I could find about database performance or anything similar.
I'll find out if there are any SQL patches that may be required too.
Cheers
Barry.
--
charlie arehart
Barry, I would just wonder if perhaps you’re suffering the “death of a thousand cuts”, where it’s not that you have any (or many) long-running (or even moderately long-running queries), but instead maybe lots (and lots) of small-running requests. Just something to consider.
And as for using FR, I would recommend that you do NOT limit what is logged (and be aware that there is a separate setting on the JDBC settings page for first what is shown in the interface, and what is logged). And of course it’s critical that you wrap *all* datasources being used—not just those that you think “might be a problem”, as there could be requests coming in (again, perhaps from bots) going against a DB (and app) you think is otherwise insignificant. Finally, you should be sure to wrap (with FR) especially any DSN set to be a client variable datasource. Again, I find this is often a huge resource-suck, with ever page request generating multiple i/o’s to the cglobal table.
/charlie
From: cfau...@googlegroups.com [mailto:cfau...@googlegroups.com] On Behalf Of Barry Chesterman
Sent: Sunday, November 28, 2010 5:14 PM
To: cfau...@googlegroups.com
Subject: Re: [cfaussie] Re: Microsoft SQL server CPU very high, close to 100% a lot of the time
Thanks Charlie,
Barry Chesterman
We have limits on the JDBC stuff in fusionreactor because we haven't really needed to use it. Our DBA is fairly certain that there isn't a set of, or a specific query that is causing a problem. All of our datasources are indeed wrapped.
As far as I know, we don't have any DSN set to a client variable datasource.
charlie arehart
Well, as far as having the limits because you’ve not needed to use it, I’m proposing that now’s the time. :-) And after capturing that data, then look in the JDBC logs (which are not enabled by default—again, controlled in the jdbc settings page). It’s going to be a lot of data, but you can do all sorts of things to analyze it, whether using Excel (or Open Office Calc) or the free Microsoft Log Parser tool. There is a technote from Intergral on using Excel to analyze the FR logs (http://www.fusion-reactor.com/support/kb/FRS-161.cfm), and I have a page I created with lots of info on analyzing them using LogParser (http://groups.google.com/group/fusionreactor/web/log-parser-commands-for-fr-3).
For instance, just getting a count of queries happening per minute can help (you can also interpolate that from the resource log instead, since it has a count of queries that finished every 5 seconds). But you could go further and report things like how many times specific queries are running, etc.
As for the DBA being “fairly certain” in his assertion, this is one of those times where analysis of diagnostics should be trusted more than intuition. I make a living help people find and correct such misconceptions. :-)
As for the client var question, the first step is simply to look in the CF Admin, on the Client variables page, to see if any DSNs are listed. Are you able to do that, or are you perhaps not allowed to access the Admin? Just can’t tell what you’re conveying in only being able to guess at things (it’s your Monday workday for you guys, right, so I’m assuming it isn’t that you’re not at work). :-)
As always, just trying to help.
/charlie
From: cfau...@googlegroups.com [mailto:cfau...@googlegroups.com] On Behalf Of Barry Chesterman
Sent: Sunday, November 28, 2010 8:25 PM
To: cfau...@googlegroups.com
Subject: Re: [cfaussie] Re: Microsoft SQL server CPU very high, close to 100% a lot of the time
It is looking like a 'death of a thousand cuts' issue as you put it, and we do have a plan if that is the case :)
Gavin Beau Baumanis
Carl
This Adobe ref says:
Choosing a client variable storage method
By default, ColdFusion stores client variables in the Registry. In
most cases, however, it is more appropriate to store the information
as client cookies or in a SQL database.
ref:
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=sharedVars_08.html
HTH, Carl.
charlie arehart
Yes, that’s right: if it’s set to registry (and any code is set to enable ClientManagement and doesn’t specify a different ClientStorage, both settable in CFAPPLICATION or application.cfc), then yes, the i/o is to a file that simulates the registry (called registry.db, which I think is in the [cf]\lib directory on Linux.) If there’s a lot of reading/writing there, it could be a real drain—but of course would not have any direct impact on SQL Server CPU being high.
But you say you “have yours set to registry”, and by that I assume you mean it’s selected as the default client storage (on the client variables page, for those trying to follow along). The question, though, is whether you have any DSNs listed on that page also. If you do, then even though the registry is the chosen default, again an application could change that by specifying it with the ClientStorage attribute (discussed above). In that case, requests to that app would use that DB.
Again, if you have any at all, then I’d recommend you wrap it with FR and track what sort of I/O is going on, bringing things back to your original question. And if you want to know more about the whole matter of the potential performance impact of client variables, and how to analyze it and mitigate it, see the resources I’ve written, presented, or recorded on the matter:
http://carehart.org/presentations/#sessclicrash
http://www.carehart.org/blog/client/index.cfm/2006/10/4/bots_and_spiders_and_poor_CF_performance
Hope that helps.
/charlie
From: cfau...@googlegroups.com [mailto:cfau...@googlegroups.com] On Behalf Of Gavin Beau Baumanis
Sent: Sunday, November 28, 2010 11:33 PM
To: cfau...@googlegroups.com
Subject: Re: [cfaussie] Re: Microsoft SQL server CPU very high, close to 100% a lot of the time
As for the client settings / DSN.
Carl
Here is another Adobe reference on client variable (I could not find
it before when I posted other and got side tracked).
Details:
Adobe recommends that you do not store client variables in the
registry because it can critically degrade performance of the server.
If you use the registry to store client variables, you allocate
sufficient memory and disk space.
URL ref:
http://help.adobe.com/en_US/ColdFusion/9.0/Admin/WSc3ff6d0ea77859461172e0811cbf3638e6-7ffc.html#WSE012D66A-E6D8-4dab-BAEC-35856D8EB780
HTH again, Carl.
Barry Chesterman
I have checked our client variables pages and there aren't any DSN's listed (just the default option of registry selected), I've checked the cfapplication tag for the site too and it uses the default (registry).
I'll log full query stats for a day anyhow and see if it shows us anything interesting :)
Regards
Barry.
charlie arehart
Ok, yes, then if there is no DSN listed in the client variables page, that confirms that you cannot possibly be using them (whether intentional or otherwise). Glad to see that ruled out.
The query logging (and analysis) would seem to be the useful next step—though again, 100% CPU in the DB sure sounds like something that could be observed with monitoring on the DB side. It may not even be CF’s fault that it’s being stressed so. But if you can’t get the DBA to consider that, then the next best thing is to see if the query logs may show whether in fact CF is stressing the DB (whether in terms of the number or duration of queries, especially around the time that the 100% CPU issue is being reported).
Will look forward to hearing how things sort out.
/charlie
From: cfau...@googlegroups.com [mailto:cfau...@googlegroups.com] On Behalf Of Barry Chesterman
Sent: Monday, November 29, 2010 3:41 PM
To: cfau...@googlegroups.com
Subject: Re: [cfaussie] Re: Microsoft SQL server CPU very high, close to 100% a lot of the time
Hi Charlie,
BarryC
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.