Thanks Charlie, 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. On Sun, Nov 28, 2010 at 5:19 PM, charlie arehart <charlie_li...@carehart.org > wrote: > 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 > > > > *From:* cfaussie@googlegroups.com [mailto:cfaus...@googlegroups.com] *On > Behalf Of *Barry Chesterman > *Sent:* Friday, November 26, 2010 5:17 PM > *To:* cfaussie@googlegroups.com > *Subject:* Re: [cfaussie] Re: Microsoft SQL server CPU very high, close to > 100% a lot of the time > > > > Hey guys, thanks for all the feedback, I'll pass on those ideas to our DBA > :) > > I haven't replied earlier sorry because I've been on holliday up in > Auckland to see U2, but we'll follow up some of the ideas here on Monday at > work and I'll let you all know if we manage to fix the problem :) > > > > 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<cfaussie%2bunsubscr...@googlegroups.com> > . > For more options, visit this group at > http://groups.google.com/group/cfaussie?hl=en. > -- 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.
