Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2008-03-11 : 07:08:22
|
| Hey guys. I feel like the prodigal son that has returned. It's been while!In your experience, what is the most like kind of cause to the cpu graph sitting quite high most of the time along with a constant second graph line in red at around the 10% line.I feel that my aim should be to get rid of the red line alltogether. I've got a few ideas, but I don't know for certain how to target that particular symptom.Any suggestions? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-11 : 07:43:12
|
Graph line in task manager or performance counters?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2008-03-11 : 08:00:27
|
| task managerI'm currently looking at this (thanks ken henderson):select top 20left(p.cacheobjtype + ' ('+p.objtype+')',35) as cacheobjtype,p.usecounts,p.size_in_bytes/1024 as size_in_kb,stat.total_worker_time/1000 as tot_cpu_ms,stat.total_elapsed_time/1000 as tot_duration_ms,stat.total_physical_reads,stat.total_logical_writes,stat.total_logical_reads,left (case when pa.value=32767 then 'ResourceDb' else isnull(DB_Name (convert(sysname,pa.value)),convert(sysname,pa.value)) end, 40) as DBName,sql.objectid,convert (nvarchar(50),case when sql.objectid is null then null else replace (replace(sql.[text],char(13),' '),char(10),' ') end) as procname,replace (replace(substring(sql.[text],stat.statement_start_offset/2+1,case when stat.statement_end_offset=-1 then len(convert(nvarchar(max),sql.[text])) else stat.statement_end_offset/2-stat.statement_start_offset/2+1 end),char(13),' '),char(10),' ' ) as stmt_textfrom sys.dm_exec_cached_plans pouter apply sys.dm_exec_plan_attributes (p.plan_handle) painner join sys.dm_exec_query_stats stat on p.plan_handle=stat.plan_handleouter apply sys.dm_exec_sql_text (p.plan_handle) as sqlwhere pa.attribute='dbid'order by tot_cpu_ms descdont know if i'm barking up the wrong tree, but it seems most likely to be caused by expensive queries, so i'm using the procedure cache to find out - even though that's not really what it's main purpose was... |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-11 : 08:05:58
|
Run SQL Profiler for a while. Then you can see which queries that takes much resources regards to cpu, duration and reads.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2008-03-11 : 08:15:40
|
| been there, done that.there's too much data to analyse.i think this approach by Ken gives you a better chance of finding what has caused the problem over a long period of time. beccause it gives you figures about how often something is run and what the total cpu time is it has taken up since the stats were last flushed. it's highlighted some interesting bits of info. i'm gonna see if i can address the queries and see if it makes a difference...i'll keep all posted as to my success/failure*Update*I have managed to come up with significant performance improvements to virtually all of the top 20 worst offenders. When the next software release goes out in a few weeks time, we'll see how it goes.There'll of course be a new top 20 list to look at...My suspicion has been verified though I think: It's frequency of calling and not the duration of execution that has been the greater contributor to the red-lining. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-11 : 22:28:46
|
| Red line is kernel time. |
|
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2008-03-13 : 06:44:38
|
| Thanks rmiao, I've learnt something.Any ideas on reducing kernel time?Or should that not be the aim? Am I barking up the wrong tree? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-03-13 : 23:02:25
|
| You can't, it's time used by os. |
|
|
|
|
|
|
|
|
red line on cpu graph