Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I know nothing about SQL I'm going to put that out there. Our dev people have said this is an OS problem. Can you help me figure out where to start to determine if this is an OS issue? SQL isn't locking memory in page files apparently so we don't see a high SQLserver.exe utilization.

SQL Server 2008 R2 Microsoft Server 2008 R2 CPU - Intel Xeon x6550 - 2.93GHz (2 processors) Memory - 20GB

CPU usage- avg 89% Memory usage- avg 10%

share|improve this question
    
how much memory is SQL server configured to use? (Database Properties -> Memory ) –  Zypher May 13 '11 at 18:10
1  
Sounds a whole lot like plain old inefficient T-SQL queries. On what basis does the dev argue that there is something wrong with the OS (like did you have Server 2003 on there before and it ran fine)? Wwe have Server 2008 R2 and SQL 2008 R2 running in production and have no problems whatsoever. What's disk utilization? If it's starving itself for RAM (for whatever reason) disk IO will be through the roof. –  Chris S May 13 '11 at 18:12
    
@Zypher, Memory is set at the instance level and by default is essentially unlimited. –  Chris S May 13 '11 at 18:15
    
@Chris Yep that's why I asked ... that essentially unlimited snags alot of people .. .since it leaves 0 for the OS –  Zypher May 13 '11 at 18:22
    
disk utilization: % disk time = under 1 avg disk queue length - .004 Everything looks good physically to me. –  meep May 13 '11 at 18:25

4 Answers 4

From all the info you've posted I'm 99% sure it's just the T-SQL queries they're running. I'd bet money it's code generated queries and something is issuing a query with a thousand JOINs or similar.

Have the devs profile their queries; I'll bet one function or another takes an absolutely inordinate amount of time and it's the root cause of the CPU spike.

share|improve this answer
    
This. It's poor coding, and I've had to deal with it myself on many occasions. –  Tatas Oct 28 '11 at 16:48

You haven't mentioned the edition of SQL Server but I am guessing its Standard here. Have you enabled the trace flag 845 to use the lock pages in memory. This TF is still required i nSQL Server 2008 R2 as well and see the notes below from Aaron Bertrand.

http://sqlblog.com/blogs/aaron_bertrand/archive/2011/02/07/sql-server-2008-r2-still-requires-a-trace-flag-for-lock-pages-in-memory.aspx

Also check what else is running on the box?

share|improve this answer
    
Yes, SQL Server 2008 R2 - apache is running on there - perl - .NET Framework - they also have some custom programs running on there. –  meep May 13 '11 at 18:19
3  
@meep EEK ... you shouldn't be running anything else on that box .. –  Zypher May 13 '11 at 18:21
    
I know, it was supposed to be their development box that they made production. I don't get to choose how they use their servers unfortunately. –  meep May 13 '11 at 18:26
1  
Then you need to measure the usage of all those apps into account. Make sure you set the max server memory on that box to leave room for OS and for the other apps otherwise SQLServer will compete for resources and you don't want that to happen. Nasty scenario. –  Sankar Reddy May 13 '11 at 18:28
    
Nothing else is really using anything because it's not competing for memory. It is only currently using about 2gb of the 20gb allocated. –  meep May 13 '11 at 18:38

If the CPU utilization is caused by the sqlservr.exe process (and not other processes or the kernel - you might want to check on that using Process Explorer) and you do not see disk load, it probably is not an OS issue but simply computational load caused by the queries.

How large is the database in question?

share|improve this answer

dude if you've got 100% cpu utilization, then you are most likely missing indexes. Learn how to run the database tuning advisor in order to build optimal indexes.

Hope that helps

-Aaron Kempf MCITP: DBA

share|improve this answer
    
Thanks I will tell the developers that. –  meep Jun 1 '11 at 14:02
    
Actually he is most liekly NOT missing indices unless the db fits into memory. Because missing indices trigger table scans... unless the tables all fill in memor that is the recipe for low CPU and being IO bound. –  TomTom Jun 1 '11 at 15:54
    
oh really.. that's a SILLY answer! Lack of indexes would 100% mean that CPU gets pegged. There are practically -NEVER- enough indexes on a SQL Server database. Do you run database tuning advisor? Do you look at the dynamic management views? –  Aaron Kempf Jun 4 '11 at 2:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.