Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

CPU tops out on SQL 7 when nothing is happening

Posted on 1999-07-12
16
Medium Priority
?
297 Views
Last Modified: 2008-03-04
After and upgrade from SQL 6.5 to 7.0(SP1), we experience CPU cycles topping out when nothing is happening (there are about 150 active connections, but they are sleeping).  I have rebuilt all of the indexes and we have taken out the use of temp tables whenever possible.  Any ideas as to why this happens?
0
Comment
Question by:stevenvanooyen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +6
16 Comments
 

Expert Comment

by:whirly
ID: 1096306
Steven,

How long does it take for the CPU cycles to top out? Is it straight away or over a period of time?
0
 

Author Comment

by:stevenvanooyen
ID: 1096307
First thing this morning, it looked good cruising along at 10-20% on both processors.  It does tend to spike and stay up.  You would think that this is a process running, but when we look in the sysprocesses for processes that are not sleeping or background, we often see nothing and the processors remain topped out for a period of time.  We have taken out almost every use of a #tmp table.  This hasn't been easy, but it seems to have helped.  In running the index analysis tool, it has failed to introduce any new indexes.
0
 
LVL 7

Expert Comment

by:simonsabin
ID: 1096308
I don't want to point out the obvious but from experience do you have a screen saver running. We had a 3d thing and could not figure why we always just missed what was causing the peak. Of course when we got and pressed a key the screen saver switched off.

0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 

Author Comment

by:stevenvanooyen
ID: 1096309
No screensaver active on this machine
0
 
LVL 5

Expert Comment

by:Mujeeb082598
ID: 1096310
Hi :)

What else is running this server. Also while the cpu is topping out is any pageing going on or can u hear your hard disk running as well, Maybe SQLServer releasing memory back to system which it is not using or NT might be swaping the inactive processes to swap file for freeing the physical memory.
0
 
LVL 7

Expert Comment

by:spiridonov
ID: 1096311
Leave  Performance monitor running  for a day collecting data  on Processor,SQL Server and Disk related objects and saving it  to log file. Then you will be able to analyse system activity at processor peak times.
0
 

Author Comment

by:stevenvanooyen
ID: 1096312
There is nothing else running on the server except PCAnywhere and I have monitored that to insure that it isn't the item doing the processing.  The processor usage is definately coming from SQL Server.  There is no NT paging going on and I have set the memory to be static, so SQL isn't swapping it with NT.  I have 768MB of 1GB allocated to SQL Server.  I have closely monitored the disk usage during times before, during, and after high cpu utilization.  There is almost no disk activity during these times.
0
 
LVL 4

Expert Comment

by:MFK
ID: 1096313
Sometimes,according to MS,if the "auto create statistics" options is set to true then the CPU usage beocmes excessive. Check this and are u running any queries or something when this happens?
0
 
LVL 9

Expert Comment

by:david_levine
ID: 1096314
Is the high utilization a spike or constant? If spiking, is it happening at a constant interval? You can use perf monitor to watch this.

David
0
 

Author Comment

by:stevenvanooyen
ID: 1096315
We have monitored this extensively wtih both Permon and Profiler.  We notice it at different points during the day.  I will have to look more closely, but it is possible that it runs after large updates or inserts which would lead me to believe that MFK's comment may help.  
0
 
LVL 9

Expert Comment

by:david_levine
ID: 1096316
A high utilization isn't necessarily a problem. If there's no activity and then all of a sudden there's a high usage bcp or insert running, a "spike" to high utilization is fine. Also, it might be a transaction log dump running at the same time?

Regardless, if performance (slow or blocking) isn't suffering (for other users), it might not be something to concern yourself with.

David
0
 

Author Comment

by:stevenvanooyen
ID: 1096317
The high CPU utilization is accompanied by slow query times.  Queries that take just 1-2 seconds normally can take as long as 15 min. during these CPU plateaus.  I have monitored for blocking and there hasn't been any when this is happening.  During the plateaus, we look for active user connections, but find that, most of the time, all of the connections are sleeping.  In those times when they are not, the transactions being performed have been very small.
0
 
LVL 3

Accepted Solution

by:
Pegasus100397 earned 800 total points
ID: 1096318
Sounds like a recursive trigger (trigger "triggering" the trigger over and over) OR an infinte-loop stored procedure that's being invoked several times over the course of the day. Had the same thing happen to us. Reboot and it's fine but every time the stored procedure was hit it went into an infinite loop. After being instantiated a couple of hundred times the processor utilization went off the charts and everything ground to a halt. Watch the users operations carefully and you should be able to track it down.

Good luck with your project!
Pegasu
0
 
LVL 4

Expert Comment

by:MFK
ID: 1096319
When u say queries, what type of sql are u using in the queries? Could u give a statement as an example..
0
 

Author Comment

by:stevenvanooyen
ID: 1096320
We are currently running both OLTP and reporting functions on this server (as we were on 6.5).  So, there are all types of transactions.  The following is a report that took less than 5 second on 6.5 and now takes as long as 15 min. on 7.0.  We have since discovered that the SQL engine is improperly using nested loops to resolve the query.  By adding the other field from our PK, we were able to take the query down to under 2 seconds and the nested loops went away.  
The example is taken out of a Stored Procedure for testing purposes.

Example:  SELECT ll.LoanNbr  AS LoanNbr
   FROM CFSReport.dbo.tmpLabelBatch ll (nolock),
        Borr b (nolock),
        Prop p (nolock),
        ST (nolock),
        CHEC c (nolock),
            Loan l (nolock),
            Loan2 l2 (nolock),
            Branch br (nolock),
            Region r (nolock),
            LoanDate2 ld2 (nolock),
            LoanDate1 ld1 (nolock)
  WHERE ll.UserID   =  'sa'
    AND b.LoanNbr   =  ll.LoanNbr
    AND b.SID       =  'NV'
    AND b.SeqNbr    =  1
    AND p.LoanNbr   =  b.LoanNbr
      AND p.SID       =  b.SID
    AND st.STID     =* p.STID
    AND c.LoanNbr   =* b.LoanNbr
      AND c.SID       =* b.SID
    AND l.LoanNbr   =  b.loannbr
      AND l.SID       =  b.SID
    AND l2.LoanNbr  =  b.LoanNbr
      AND l2.SID      =  b.SID
      AND ld2.LoanNbr =  b.LoanNbr
      AND ld2.SID     =  b.SID
      AND ld1.LoanNbr =  b.LoanNbr
      AND ld1.SID     =  b.SID
    AND br.BranchID =  l.BranchID
    AND r.regionid  =  br.regionid
  ORDER BY ll.EntryOrder

0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6873067
GREETINGS!

This question was awarded, but never cleared due to the JSP-500 errors of that time.  It was "stuck" against userID -1 versus the intended expert whom you awarded.  This corrects the problem and the expert will now receive these points; points verified.

Please click on your Member Profile and select "View Question History" to navigate through any open or locked questions you may have to update and finalize them.  If you are an EE Pro user, you can also choose Power Search to find all your open questions.

This is the Community Support link, if help is needed, along with the link to All Topics which reflects many TAs recently added.

http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
http://www.experts-exchange.com/jsp/zonesAll.jsp
 
Thank you,
Moondancer
Moderator @ Experts Exchange
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question