Probably your most effective tool to debug this is going to be the sql profiler. I would do a google on something like "sql profiler optimize long running query" to get some ideas on how best to set up the trace. With that tool, you can have sql keep a log of all kinds of statistics, and then use the Index Tuning Wizard to analyze the trace. I've seen a single index change make a query that would churn away for minutes (using 100% CPU), go down to something that ran in under a second. Also, the query optimization routines that SQL Server uses are not always perfect...and I've had them change query times drastically from SP to SP (especially when we were using SQL 7)....so it's a good idea to run traces like this regularly, and make sure that your indexes are optimized.
Make sure you run the tools on another machine...as there is quite a bit of overhead associated with them. On 3/21/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Hi everybody, we have a MS-SQL-7 server piking (+ plateaus) to 100% CPU. > > It is caused by mssqlserver.exe. How can we find out, what database access is > causing this, > since I have about 20 DBs running on them ? All DBs are powering > ColdFusion shop-systems. > > We have Win2000 server, SP4. > > Thanks for ideas. > > Uwe > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:235856 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
