March 20, 2008 at 2:34 am
Hi every one.
I have a problem.
I have SQL Server 2005 Standart Editon x86.
Memory: 8 GB
CPU: 8
It's a report server.
All queries rum from application server.
Every day from 09:00 to 11:00 cpu utilisation is 100%
In sysprocesses table many rows have SOS_SCHEDULER_YIELD value in lastwaittime column.
80536000x00000SOS_SCHEDULER_YIELD
80471600x00000SOS_SCHEDULER_YIELD
80602400x00000SOS_SCHEDULER_YIELD
80401600x00000SOS_SCHEDULER_YIELD
80351600x00000SOS_SCHEDULER_YIELD
80420800x00000SOS_SCHEDULER_YIELD
80602000x00000SOS_SCHEDULER_YIELD
80602800x00000SOS_SCHEDULER_YIELD
From sp_who2 results show this picture:
80RUNNABLE
80RUNNABLE
80RUNNABLE
80RUNNABLE
80RUNNABLE
80RUNNABLE
80RUNNABLE
After 11:00 o'clock the same processes show this picture:
from sysprocesses:
80000x00000MISCELLANEOUS
sp_who2:
85 sleeping
Can any one help me?
March 20, 2008 at 5:31 am
The last wait time is a scheduler yield, but from what I can see, the curent wait type is 0 (0x000) and the wait time is 0. Hence the process is running, not waiting.
What I would suggest is that you run profiler over the period. Trace the events T-SQL:StmtCompleted and SP:StmtCompleted. Make sure you include the CPU column. Look for the statements with the highest CPU usage and see if you can optimise them. Most likely by fixing the code, sometimes by fixing the indexing.
You may also be able to use the following to identify queries using a lot of CPU. If you're using SP, you can uncomment the line to get the name of the stored proc. If not, you'll have to check the DBID and then use object_name within that database
select top 50
st.objectid, st.dbid,
-- object_name(st.objectid, st.dbid), -- uncomment this line if you're using SQL 2005 SP2
total_worker_time/execution_count AS AverageCPUTime,
CASE statement_end_offset
WHEN -1 THEN st.text
ELSE SUBSTRING(st.text,statement_start_offset/2,statement_end_offset/2)
END AS StatementText
from
sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY AverageCPUTime DESC
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 20, 2008 at 6:03 am
evgkushnir (3/20/2008)
Hi every one.I have a problem.
I have SQL Server 2005 Standart Editon x86.
Memory: 8 GB
CPU: 8
It's a report server.
All queries rum from application server.
Every day from 09:00 to 11:00 cpu utilisation is 100%
In sysprocesses table many rows have SOS_SCHEDULER_YIELD value in lastwaittime column.
80536000x00000SOS_SCHEDULER_YIELD
80471600x00000SOS_SCHEDULER_YIELD
80602400x00000SOS_SCHEDULER_YIELD
80401600x00000SOS_SCHEDULER_YIELD
80351600x00000SOS_SCHEDULER_YIELD
80420800x00000SOS_SCHEDULER_YIELD
80602000x00000SOS_SCHEDULER_YIELD
80602800x00000SOS_SCHEDULER_YIELD
From sp_who2 results show this picture:
80RUNNABLE
80RUNNABLE
80RUNNABLE
80RUNNABLE
80RUNNABLE
80RUNNABLE
80RUNNABLE
After 11:00 o'clock the same processes show this picture:
from sysprocesses:
80000x00000MISCELLANEOUS
sp_who2:
85 sleeping
Can any one help me?
Hi,
This sounds like a scheduled job, which is pressure CPU to a point, but as GilaMonster already mentioned, its running fine. I suspect you may have lots of table\index scans and\or excessive joins for report queries. Has this been an issue long-term or has it just recently surfaced?
Thanks,
Phillip Cox
MCITP - DBAdmin| MCTS - SQL Server 2005
March 21, 2008 at 11:41 am
You can also use dbcc inputbuffer() to see executing code associated with a particular spid.
Are you seeing any blocking?
Definitely looks like someone or something is running some massive code.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 1, 2008 at 6:40 pm
My 64bit SQL 2005 Cluster can go AWOL on a specific queries. 100% CPU, IO and CPU at a virtual stand-still, and SOS_SCHEDULER_YIELD dominates exclusively. No real work was being performed.
Classic feedback on blogs regarding this problem point to CPU pressure and in my case with my monster DELL 580L, no indication that CPU was a problem. Just by executing my statement I could instantaneously send the CPU from ~8% to 100% !!!! After much playing around I discovered that my query went AWOL when executing part of the WHERE clause which included a check for NULL (ApplicationNbr IS NULL). Moving this statement around within the WHERE and 2 LEFT JOIN clauses would not help. Tried DBCC FREERPOCCACHE AND DROPCLEANBUFFERS, no luck ! Tried breaking all connections and reconnected with both a WINDOWS and then SQL Server login, no luck ! My buddy next to me executed the same statement and it executed in seconds !!! Eventually, after my tinkering, my buddies query finally began to misbehave exactly as it was for me. I need to mention my
indexes are either rebuilt (fragmentation > 30%) or reorg' (< 30%) every night and many tables had there stats updated after each fresh data load.
SOLUTION: UPDATE STATISTICS on key table used within the query. My query returned in < 10 seconds where before I was killing it after 10, 20, 30 minutes (my tables were completely cached in memory) SOS_SCHEDULER_YIELD gone, query executing normal, PAGEIOLATCH waits everywhere... yea !!!
What I believe was happening is that SQL Server chose execution plans that were absolutely lemon when passed to the UMS Scheduler. Like a dog chasing its tail, the execution went to the runnable queue, pre-emptively yielded to another thread to only be placed back on the bottom of the runnable queue.... and around around around she went.... no work done.. just merry-go-round with the scheduler/CPU.. all 8 physical, 16 logical total !!! Hmmmm... this explains growing CPU wait time and non-accruing logical IO/physical IO activity.
For many of you suffering - and there are quite a few- I hope this solution solves your problem.
-CqlBoy
CQLBoy
October 1, 2008 at 9:10 pm
blake colson (10/1/2008)
SOLUTION: UPDATE STATISTICS on key table used within the query. My query returned in < 10 seconds where before I was killing it after 10, 20, 30 minutes (my tables were completely cached in memory) SOS_SCHEDULER_YIELD gone, query executing normal, PAGEIOLATCH waits everywhere... yea !!!
What I believe was happening is that SQL Server chose execution plans that were absolutely lemon when passed to the UMS Scheduler. Like a dog chasing its tail, the execution went to the runnable queue, pre-emptively yielded to another thread to only be placed back on the bottom of the runnable queue.... and around around around she went.... no work done.. just merry-go-round with the scheduler/CPU.. all 8 physical, 16 logical total !!! Hmmmm... this explains growing CPU wait time and non-accruing logical IO/physical IO activity.
For many of you suffering - and there are quite a few- I hope this solution solves your problem.
-CqlBoy
All I can say is thank you, thank you, thank you! This solution resolved same issue I've battled for hours today.
Joseph
October 2, 2008 at 5:46 am
blake colson (10/1/2008)
SOLUTION: UPDATE STATISTICS on key table used within the query. My query returned in < 10 seconds where before I was killing it after 10, 20, 30 minutes (my tables were completely cached in memory) SOS_SCHEDULER_YIELD gone, query executing normal, PAGEIOLATCH waits everywhere... yea !!!
Bad statistics result in bad execution plans. It's often as simple as that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 2, 2008 at 10:11 am
Blake, you should consider disabling hyperthreading (you mentioned 8 physical, 16 logical cores). HT often leads to worse performance not better in SQL Server environments. Test with your data and apps to see.
Whether or not you disable HT, also look at lowering max deg of parallelism to 4+- to see if that offers improved performance and fewer CXPACKET waits if you are having those. If the system is a well-tuned OLTP one consider setting maxdop to 1 (MS best practice).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 5, 2010 at 8:44 pm
Reducing MaxDop is a classic internet text book/bandaid response which 99.999% results in unacceptable results. If you're messing with maxdop, then I'd say you're short on experience and plenty on reading. No substitute. Rewriting the query and either adding or utilizing existing indexes is the preferred solution. A good working knowledge of the disk subsystem and its interaction with SQL Server is undervalued. Read everuthing Bob Dorr wrote..... and then prepare for a deeper understanding. -CQLBoy
CQLBoy
June 6, 2010 at 3:13 am
Two-year-old-thread alert.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2011 at 8:26 am
blake colson-405790 (6/5/2010)
Reducing MaxDop is a classic internet text book/bandaid response which 99.999% results in unacceptable results. If you're messing with maxdop, then I'd say you're short on experience and plenty on reading. No substitute. Rewriting the query and either adding or utilizing existing indexes is the preferred solution. A good working knowledge of the disk subsystem and its interaction with SQL Server is undervalued. Read everuthing Bob Dorr wrote..... and then prepare for a deeper understanding. -CQLBoy
CQLboy, sorry, but your reply displays firstly, a total disregard for professional curtsey and secondly, what you accuse your peers of, a lack of both experience and knowledge of SQL server.
March 30, 2011 at 1:42 pm
Iain, please do not respond to years old threads like this. Thanks in advance!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 14, 2011 at 2:48 pm
SQL server does not do math(graphics, cure for cancer) it does IO. I have argued this with Microsoft many times when they say yeah but our indexes use parrell plans blah blah. I say prove to me that setting the max dop to anything but 1 to disable max dop does anything bug increase performance by 20%. I have yet to be proven wrong. As always I will read anything you can provide to prove me wrong.
July 14, 2011 at 3:04 pm
Here you go: http://sqlblog.com/blogs/paul_white/archive/2011/07/07/bitmap-magic.aspx
A parallel query that runs faster than the serial query and uses less CPU. You're welcome.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 2, 2011 at 2:11 pm
I would like to do some testing but I get this call often. Server a Runs slower than server b with the same query blah blah blah. Those queries are failing a lot of the basic rules.
1. Never use a heap because it leads to table scans which causes blocking, leaf level fragmentation, locks the entire table preventing inserts updates ect.
2. It is a laptop not a server that is not built for threading and not using storage. Single user the article would be correct but with a bunch of users not sure it would still be the same.
3. What happens when you through 1000 users doing other things to the database, inserts updates and deletes backups ect. Point being never base server performance on a single query or just a short amount of time.
4. I do not see stored procedures with stats being updated.
My thought is that when you through a bunch of users on a box they are all going to have to wait for that query plan to built then executed. So you will see huge waits with the other queries waiting to be run and you will not see this query fail but the queries waiting in line behind it.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply