March 17, 2008 at 10:21 am
I set up SQL 2005 instance on cluster and run it on another node. Then we prepared performance tests and run them for 12 h. After comparing results on SQL 2000 and SQL 2005 I found out that there are very strange CPU peaks around 2-3 GMT.
More details:
CPU Max 46 % (Avg 6 %)
SQL Server service is responsible for those peaks.
Exactle at that time following counters rose as well:
File Data Operations/Sec Max 4840 (Avg 144)
Log bytes flushed/sec Max 500 000 (Avg 50 000) / for tempdb and 4 test databases
Log cache reads/sec Max 16 (Avg 0,01) /for user DB
Transactions/sec Max 1300 (Avg 300)
Log flushes/sec Max 26 (Avg 609)
There is no SQL job, backup restore or OS activity.
I repeated test last weekend for 36 hours. Again two opeaks between 2-3 and nothing else.
Any ideas ?
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
March 17, 2008 at 11:33 am
Try running Profiler over that same period of time. You'll see what exactly is getting called. Be sure you capture before & after the event in case a process runs through the whole event but only peaks for a period of time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 17, 2008 at 12:15 pm
That will only show you what SQL Server is doing during that time. You should also schedule a Performance Monitor session to run during that time. Spcifically capture the Process:%ProcessorTime/All instances counter. That will show you specfically wich process is consuming the most cycles.
DAB
March 17, 2008 at 12:49 pm
True. But based on what he had posted above I assumed he was already doing that much.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 17, 2008 at 1:20 pm
and don't forget to capture CLR-stuff 😉
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 17, 2008 at 1:48 pm
If you would like to see whether or not there is any process running on your SQL Server, you may run
SELECT * FROM master..sys.sysprocesses
If you would like to see whether or not there is any process running against your OS, you may build your own perfmon counters.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply