Re: OT: sql server: SQLTransaction
Just for the record, things seemed to quiten down after i rebuilt all the statistics. AskJeeves spider was hitting the server in the particular way which was causing the problems: there's a big cfquery with loads of conditional stuff (and someCol=whatever), and using particular search criteria was causing the query to run 00's of times slower than normal. All the same SQL was run in query analyser it ran fast. Removing the cfqueryparam tags also made the query run fast, so i ran sp_updatestats, and everything ran fast again - hurrah! I suppose lessons would be to run sp_updatestats before anything else. And that the AskJeeves spider likes SES urls. Cheers Bert On Tue, 7 Dec 2004 13:13:48 +, Bert Dawson <[EMAIL PROTECTED]> wrote: > > Are you sure the username is the one that is used from CF then? > > Yes, it definately the CF user - when i started looking at this i > created a new new SQL login, and the only place this is used is in the > DSN set up in CFadmin. > > > > > > > > How would you suggest i use a manual checkpoint? Just open up query > > > analyser and run CHECKPOINT? > > > > Yes. > > > > I'll give that a go... > > > > > > Also, have just profiler again, over a period of about 10 minutes > > > when there appeared to be no abnormal activity (according to CPU > > > usage), and there are still times where the number of SQLtransactions > > > appear v high (up to 9,000 in a second), so i'm begining to wonder if > > > this level of SQLTransactions/second is unusual, or just normal > > > activity... > > > > I think it would be unusual > > > > Thats what i would have thought. They seem to some in pairs, with > EventSubSlass: Begin and Commit, and ObjectName: "sort_init" > > > Do the periods of high activity coincide with the transaction log > > backups? > > > > Nope, they seem to come and go, though sometimes they show up failry > regularly, and other times the CPU is just up and down all day > > I'm begining to think i might need to rebuild some indexes, as there > are a few tables which have fairly heavy levels of delete and insert. > > Do you think fragmente indexes could cause this sort of behaviour? > > Thanks > Bert > ~| Special thanks to the CF Community Suite Silver Sponsor - RUWebby http://www.ruwebby.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:187528 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: sql server: SQLTransaction
> Are you sure the username is the one that is used from CF then? Yes, it definately the CF user - when i started looking at this i created a new new SQL login, and the only place this is used is in the DSN set up in CFadmin. > > > > How would you suggest i use a manual checkpoint? Just open up query > > analyser and run CHECKPOINT? > > Yes. > I'll give that a go... > > > Also, have just profiler again, over a period of about 10 minutes > > when there appeared to be no abnormal activity (according to CPU > > usage), and there are still times where the number of SQLtransactions > > appear v high (up to 9,000 in a second), so i'm begining to wonder if > > this level of SQLTransactions/second is unusual, or just normal > > activity... > > I think it would be unusual > Thats what i would have thought. They seem to some in pairs, with EventSubSlass: Begin and Commit, and ObjectName: "sort_init" > Do the periods of high activity coincide with the transaction log > backups? > Nope, they seem to come and go, though sometimes they show up failry regularly, and other times the CPU is just up and down all day I'm begining to think i might need to rebuild some indexes, as there are a few tables which have fairly heavy levels of delete and insert. Do you think fragmente indexes could cause this sort of behaviour? Thanks Bert ~| Special thanks to the CF Community Suite Silver Sponsor - RUWebby http://www.ruwebby.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:186433 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: sql server: SQLTransaction
Bert Dawson wrote: > We're not using client variables (default storage in cfadmin=none, > clientManagement="No", no DSNs are configured to use client variables, > or have the tables in them). Are you sure the username is the one that is used from CF then? > How would you suggest i use a manual checkpoint? Just open up query > analyser and run CHECKPOINT? Yes. > Also, have just profiler again, over a period of about 10 minutes > when there appeared to be no abnormal activity (according to CPU > usage), and there are still times where the number of SQLtransactions > appear v high (up to 9,000 in a second), so i'm begining to wonder if > this level of SQLTransactions/second is unusual, or just normal > activity... I think it would be unusual > 0-99 SQLtransactions/second: 650 > 100-999 SQLTransactions/sec: 19 > 1000-1999: 6 > 2000-2999: 4 > 3000-3999: 2 > 4000-4999: 3 > 5000-5999: 1 > 6000-6999: 3 > 7000-7999: 0 > 8000-8999: 2 > 9000-: 1 > > AFAIK the only thing happening to the dBs apart from getting hit by CF > is a backup of the transaction logs every 10 minutes, and a full > back-up every hour. Do the periods of high activity coincide with the transaction log backups? Jochem ~| Special thanks to the CF Community Suite Silver Sponsor - New Atlanta http://www.newatlanta.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:186336 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: sql server: SQLTransaction
We're not using client variables (default storage in cfadmin=none, clientManagement="No", no DSNs are configured to use client variables, or have the tables in them). How would you suggest i use a manual checkpoint? Just open up query analyser and run CHECKPOINT? Or place it in the code somewhere? Also, have just profiler again, over a period of about 10 minutes when there appeared to be no abnormal activity (according to CPU usage), and there are still times where the number of SQLtransactions appear v high (up to 9,000 in a second), so i'm begining to wonder if this level of SQLTransactions/second is unusual, or just normal activity... 0-99 SQLtransactions/second: 650 100-999 SQLTransactions/sec: 19 1000-1999: 6 2000-2999: 4 3000-3999: 2 4000-4999: 3 5000-5999: 1 6000-6999: 3 7000-7999: 0 8000-8999: 2 9000-: 1 AFAIK the only thing happening to the dBs apart from getting hit by CF is a backup of the transaction logs every 10 minutes, and a full back-up every hour. Any ideas of where to look next? (The underlying probem i'm trying to fix is jrpp.delayMs sometimes going through the roof for no apparent reason, and i suspect the dB, or connections to it, is in someway involved since the areas of the site which are more dB intensive are hit harder by timeouts when there is a problem) Thanks Bert On Fri, 03 Dec 2004 22:03:15 +0100, Jochem van Dieten <[EMAIL PROTECTED]> wrote: > Bert Dawson wrote: > > I've got a SQL server box that ticks along quite happily at about 15% > > CPU, but occasionally goes up to around 40% and stays there for > > anything up to a minute. During these peaks the jrpp.delayMs can start > > to climb, up to from a few seconds up to hundreds of seconds. I also > > start getting a few timeouts in the application log. (timeout is se to > > 35 seconds) > > Do you get the same behaviour when using a manual checkpoint? > > > > I ran a SQL profile trace and spotted that during the peaks there were > > massive numbers of "SQLTransaction entries": over a 2.5 minute period > > there are ussually about 12 SQLTransactions per second, but this hit a > > maximm of 16283, which sounds like a lot to me! > > Are you using client variables? > > Jochem > > ~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:186283 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: sql server: SQLTransaction
Bert Dawson wrote: > I've got a SQL server box that ticks along quite happily at about 15% > CPU, but occasionally goes up to around 40% and stays there for > anything up to a minute. During these peaks the jrpp.delayMs can start > to climb, up to from a few seconds up to hundreds of seconds. I also > start getting a few timeouts in the application log. (timeout is se to > 35 seconds) Do you get the same behaviour when using a manual checkpoint? > I ran a SQL profile trace and spotted that during the peaks there were > massive numbers of "SQLTransaction entries": over a 2.5 minute period > there are ussually about 12 SQLTransactions per second, but this hit a > maximm of 16283, which sounds like a lot to me! Are you using client variables? Jochem ~| Special thanks to the CF Community Suite Silver Sponsor - RUWebby http://www.ruwebby.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:186167 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: sql server: SQLTransaction
I do'n't know anything about the FTS service, and i don't knowingly use it, so i've stopped it. But I doubt that was the cause as there seemed to be no pattern to the "2 minutes @ 30%" periods. Also, i suspect its something to do with CF since the loginname reported in profiler is only used in the CF dsn. (i created a new user today specially for CF so that i could eliminate the possibility that someone or something else was causing the trouble.) Any more ideas of things to look for? Cheers Bert On Fri, 03 Dec 2004 12:06:52 -0500, Jerry Johnson <[EMAIL PROTECTED]> wrote: > Just as a guess, maybe a scheduled FTS catalog population? > > Jerry > > Jerry Johnson > Web Developer > Dolan Media Company > > >>> [EMAIL PROTECTED] 12/03/04 12:01PM >>> > If anyone has any ideas what sort of thing could cause just a leap > then i'd appreciate any input, or ideas of where to look next. > > > ~| Special thanks to the CF Community Suite Silver Sponsor - New Atlanta http://www.newatlanta.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:186124 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: OT: sql server: SQLTransaction
Just as a guess, maybe a scheduled FTS catalog population? Jerry Jerry Johnson Web Developer Dolan Media Company >>> [EMAIL PROTECTED] 12/03/04 12:01PM >>> If anyone has any ideas what sort of thing could cause just a leap then i'd appreciate any input, or ideas of where to look next. ~| Special thanks to the CF Community Suite Silver Sponsor - New Atlanta http://www.newatlanta.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:186108 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
OT: sql server: SQLTransaction
I've got a SQL server box that ticks along quite happily at about 15% CPU, but occasionally goes up to around 40% and stays there for anything up to a minute. During these peaks the jrpp.delayMs can start to climb, up to from a few seconds up to hundreds of seconds. I also start getting a few timeouts in the application log. (timeout is se to 35 seconds) I ran a SQL profile trace and spotted that during the peaks there were massive numbers of "SQLTransaction entries": over a 2.5 minute period there are ussually about 12 SQLTransactions per second, but this hit a maximm of 16283, which sounds like a lot to me! If anyone has any ideas what sort of thing could cause just a leap then i'd appreciate any input, or ideas of where to look next. TIA Bert dB server is Win2003 SQLserver 2k, dual xeon3.2ghz, 4gig ram. CFMX6.1 on JRun with updater, Win 2003, dual xeon 3GHz, 3.75 gig ram ~| Special thanks to the CF Community Suite Silver Sponsor - CFDynamics http://www.cfdynamics.com Message: http://www.houseoffusion.com/lists.cfm/link=i:4:186103 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
