SQL SERVER High CPU issue

[niall5098]

HI,

I have a sql server install with mem usage running at 740MB at all times. CPU can be idle until reports are run. The CPU maxes out but the mem usage stays at 740MB, i would have expected it to go up.

The max server memory is the default value of 2147483647 however there is only 2 gigs of ram on the server. If i reduce the max server memory allocation can i expect to see a server improvement?

Thanks

 

[gmmastros]

Reducing the memory allocation will not improve overall performance. If anything, it will hurt performance. Most likely, the memory usage is relatively constant because SQL Server will cache as much of the contents of your database in to memory as it can. RAM access times are MANY MANY times better than hard drive access times.

CPU can be idle until reports are run

How long does the query for the reports take to execute? If the reports return HUGE amounts of data, there is an element of.... it is what it is. However, if the reports are returning a modest amount of data, and it's taking a relatively long time to return, your problem is probably the query itself.

There are many different things that can be done to improve the performance of queries. If the query time is AWFUL, it's probably an indexing problem. If it's not an indexing problem, it's probably a sargable problem. Either way, I would look in to speeding up the report queries to solve this problem.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[niall5098]

the report returns a modest amount of data but can take 2-3 minutes, at the moment i am running profiler and perfmon to see if i can spot anything.

one thing to note, the server was restarted and the sql server memory usage start at around 120mb, it is creeping up slowly with no massive spikes in mem usage when reports are run, i have a funny feeling that it may get to 740MB and stall there again

 

[gmmastros]

What is the size of your database?

run this:

sp_helpfile



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[niall5098]

database is quite small, 600MB

 

[gmmastros]

the mem usage stays at 740MB, i would have expected it to go up.

restarted and the sql server memory usage start at around 120mb

database is quite small, 600MB

120 mb + 600 mb = 720 mb. The steady state memory usage is 740 mb. Seems about right to me.

It sounds to me like eventually the entire database is cached in to memory. Once that happens, there is no longer any need to use more memory. Of course, there are other things that get put in to memory, like the system databases (Master, TempDB, etc...) so your memory usage is greater than the size of your database, but.... once everything is cached in memory, there's still no need to cache more.

Personally, I see no reason for you to be concerned about the memory usage. Two to three minutes to run a report is another matter, one that you should devote your attention to. There are various reason why reports can be slow.

1. The report could be hitting the database numerous times, effectively slowing down your performance.
2. The tables that the report query uses may not be properly indexed.
3. The query could be poorly written so that it does not make effective use of existing indexes.

There are other reasons too, but the 3 listed above are the "usual suspects". The good news is.... with a little work, each of these things can be corrected.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[niall5098]

seems to be a known bug that is resolved with sp3

http://support.microsoft.com/kb/961811

allocated another 1gb to sql server and report time has gone from 2 mins 34 secs to 18 secs