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 |
|
keithc
Starting Member
35 Posts |
 Posted - 2004-11-23 : 09:42:31
|
| Have a sql box that is using about 2gb physical mem and has 1gb free. Problem is there is a reoccurring CPU spike that hits 99% and lasts for 10 seconds and really slows everything down, like a hang. This box was upgraded from 7.0. Any ideas? Am thinking of running a trace but am not sure what columns to choose from |
|
|
keithc
Starting Member
35 Posts |
Posted - 2004-11-23 : 09:58:00
|
| Just to add to this. I was able to capture a call to a stored procedure that had 2 million+ reads with a duration of like 13000 within profiler and there are 2 calls to he proc so its actually like 4 million reads with duration 26000. I beleive this could be causing the spike but what doesnt make sense is why would this same proc have no problem in 7.0 and now have issues in 2000 sp3. The amt of reads suggest to me that for whatever reason the called proc is doing table scans. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-26 : 02:39:49
|
| I would suggest puttingSET STATISTICS IO ON; SET STATISTICS TIME ON... put query hereSET STATISTICS IO OFF; SET STATISTICS TIME OFFaround the query, in [a copy of] your SProc, and running it from QA to see how long it takes.I've been trying to isolate some server-liker activity recently. My approach was to use the PROCESS (not ProcessOR) in Performance Monitor and to choose All Instances of some indicators I was interested in - CPU %age in your case. That will tell you whether it is SQL, or something else, that is causing the grief.Kristen |
|
|
|
keithc
Starting Member
35 Posts |
Posted - 2004-11-26 : 15:31:10
|
| Sorry for the late response here Kristen it was Backup exec causing the spikes. The proc is still at issue and the 3rd party vendor recognized it as such so eventhough it was backupexec causing problems I did find issue with the proc. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-26 : 23:30:10
|
| Are you backing up the SQL DB direct to tape then?Kristen |
|
|
|
keithc
Starting Member
35 Posts |
Posted - 2004-11-27 : 09:58:09
|
| Not 100% sure as I found of the backupexec cpu usage through another tech. I do know it is an auto loader. Is it reccomened to backup to disk first then to tape? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-27 : 12:01:24
|
Better to tape first, then disk, yes.Better to disk first, then tape, yes. Unless you don't have enough space.Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-27 : 12:26:24
|
| >> Better to tape first, then disk, yes. Unless you don't have enough space.That was meant to the other way round I think :).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-28 : 04:30:33
|
| "That was meant to the other way round I think"Tired, drunk or both. Thanks Nigel.Perhaps I should have put "Discuss" after my original post!Kristen |
|
|
|
|
|
|
|
|
SQL 2000 sp3 Cpu spiking to 99%