Do you have many queries (especially queries ran very often) that do not use cfqueryparam? Those symptoms remind me of system I worked in once where most of the queries were not using queryparam or any other form of binding - and as a result sql's query cache was filling up too often... though I'm sure many other things could be the culprit : )
What recovery model is your db using? On Mon, May 16, 2011 at 11:30 AM, Mark A. Kruger <[email protected]>wrote: > > Scott, > > It might pay to eliminate networking as a possible issue. For example - are > the ports on the switch trying to "scale up" to 1000/mbs from 100? This > could cause what looks like a temporary "hang". (try statically setting > ports end to end between the server and db). > > Or perhaps you have a mixed environment with XP, Linux, Windows server 00 > or 03 along with 2008r2 in it... the 08 OS defaults to larger packet sizes > on the network and this can cause detrimental effects in mixed networks > when > it kicks in. > > -Mark > > > Mark A. Kruger, MCSE, CFG > (402) 408-3733 ext 105 > www.cfwebtools.com > www.coldfusionmuse.com > www.necfug.com > > > -----Original Message----- > From: Scott Weikert [mailto:[email protected]] > Sent: Monday, May 16, 2011 9:58 AM > To: cf-talk > Subject: Odd issue with DB "pausing" > > > Ok I've got a headscratcher here, would love some input from some other > brains out there. Sometimes it sucks to be the lone gunman developer. :( > > Got CF8 running using MSSQL. Lately, every now and again (too often) the > DB appears to hang, anywhere from a minute or so to several minutes. As > best I can tell (from previous times when it's happened, and I was > remoting our DB box at the time) the CPU isn't spiking during these > hangs. It seems to just be connections from our webservers to the > database that is affected. > > Based on comments from one of my employers working in the system at the > time, and from a few clients, it seems these hangs are triggered by a > specific action in our system - a content-save to a particular table, > that holds the lion's share of client content (webpage content). > > I've tried making changes to said script a couple of times - adjusting > the query, storedproc-ing it up. There seems to be no rhyme or reason to > it - different times it hangs, there's different amounts of content > being saved. One client tends to stuff the most in there - another, not > nearly so much - and both seem to trigger the hang. > > This behavior is pretty recent - just the last few weeks. And I can't > for the life of me puzzle out what's different. We had an issue recently > with an old bug, the "corrupt table" issue, and there was a little bit > of fiddling with the datasource settings before this issue was somewhat > resolved, so I have to wonder if something adjusted during that episode > isn't the cause of this behavior. Nothing in the datasource settings > screams "ME ME ME I'M THE ONE". > > When the hangs occur, triggered by whatever use on our main webserver, > it affects the DB connections on the other servers as well - even a web > environment that is using other databases (dev env) but said other DBs > are on the same DB server. All servers just sit on "waiting on..." in > the browser status 'til the hang passes, then all is well again. > > I'm about at my wit's end trying to puzzle this one out - I sure could > use some nudging in the right direction. > > Thanks in advance - > --Scott > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:344534 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
