high cpu usage
in [Programming SQL]
|
From: SqlBeginner on 16 Jul 2010 17:33 Eric based on the result i changed a SP for better performance and deployed. Now if we run your query if my fix is correct it should go off from the list asap right? or do i need to keep running for few iterations? Regards Pradeep "Eric Isaacs" wrote: > The previous version finds the slowest processes on the server that > use the most CPU cycles. I tweaked it a little after I left that > message. Now if you specify the database, this will provide the top > slowest portions of different objects (sprocs, views, triggers, etc.) > in that database. It's a little slow to run itself since it's calling > a function, but it works. > > USE databasename > > SELECT > query_stats.objectid, > OBJECT_NAME(query_stats.objectid) AS [ObjectName], > query_stats.Text, > query_stats.statement_text AS [SQLText], > SUM(query_stats.total_worker_time) / > SUM(query_stats.execution_count) AS [AverageCPUTime] > FROM > ( > SELECT > QS.* > ,ST.objectid, ST.Text, > SUBSTRING(ST.text, (QS.statement_start_offset / 2) + 1, ((CASE > statement_end_offset > > WHEN -1 THEN DATALENGTH(ST.Text) > > ELSE QS.statement_end_offset > END > - QS.statement_start_offset) / 2) + 1) AS statement_text > > FROM > sys.dm_exec_query_stats AS QS > CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST) AS > query_stats > WHERE > OBJECT_NAME(query_stats.objectid) IS NOT NULL > GROUP BY > query_stats.statement_text, > query_stats.objectid, > query_stats.TEXT > ORDER BY > SUM(query_stats.total_worker_time) / > SUM(query_stats.execution_count) DESC ; > > > -Eric Isaacs > . >
From: Eric Isaacs on 16 Jul 2010 22:17 The whole query is based on what is in the cache. If you want to determine how your new code ranks, I would suggest you clear the cache to see how evenly compares with other SQL in the cache from a common starting point. If you don't clear the cache, I really don't know if it will remain in cache or not if the base object is recompiled. My hunch would be that it would be replaced, but I haven't verified that. Execute this statement to clear and therefore reset the SQL cache... DBCC FREEPROCCACHE Clearing the cache will allow you to have a common starting point for SQL in the cache. After you clear it, give it some time and then run the test SQL again later after the cache has been rebuilt. If your code is back in the list after doing everything you can to get recode it to speed it up, it very well could be an indexing issue that you need to look into resolving. Recoding doesn't always do the trick, manytimes, you need a better index design to speed things up to sub- second results. -Eric Isaacs
From: SqlBeginner on 19 Jul 2010 10:19
Thanks for explanation Eric. Regards Pradeep "Eric Isaacs" wrote: > The whole query is based on what is in the cache. If you want to > determine how your new code ranks, I would suggest you clear the cache > to see how evenly compares with other SQL in the cache from a common > starting point. If you don't clear the cache, I really don't know if > it will remain in cache or not if the base object is recompiled. My > hunch would be that it would be replaced, but I haven't verified that. > > Execute this statement to clear and therefore reset the SQL cache... > > DBCC FREEPROCCACHE > > > Clearing the cache will allow you to have a common starting point for > SQL in the cache. After you clear it, give it some time and then run > the test SQL again later after the cache has been rebuilt. If your > code is back in the list after doing everything you can to get recode > it to speed it up, it very well could be an indexing issue that you > need to look into resolving. Recoding doesn't always do the trick, > manytimes, you need a better index design to speed things up to sub- > second results. > > -Eric Isaacs > . > |