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 (<cfif something>and someCol=whatever</cfif>), 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 +0000, 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
