performance problem - sql server 2005 x64 version - memory allocation - need more information

Question

performance problem - sql server 2005 x64 version - memory allocation - need more information

Parthipan-244146

SSCrazy

Points: 2178
More actions
July 6, 2007 at 3:15 pm

#372756

Hi All,
Our server is having x64 sql server 2005 with build no: 2047.
Processor: AMD opteron processor(2 in numbers) 2.61GHZ           Memory: 4.83GB
In sql server setting,
min memory = 0MB       Max memory = 2147483647MB
The server was rebuilded from earlier crash. Some days back users were saying the sql agent jobs and sql server performance was very low. We checked in the task manager sql server.exe was using 63MB and overall PF utilization was 4.3GB. We also setup performance counter and found the sql server counters like buffer cache hit ratio were normal.
Then found an article saying that in 64 bit version AWE option is not needed and need to add 'local pages in memory' option inorder to make the sql server running out of paging with OS. We enabled 'local pages in memory' for the sql server account and rebooted server.
After enabling 'local pages in memory' in  sql server log we found the below messages,
2 processor(s) and 4952 MB RAM detected
Earlier memory information is not captured in sql server log.
Now in task manager sql server.exe is using 85MB and PF utilization is 670MB. PF utilization is dropped very much from 4.5GB to 670MB.
Below is the boot.ini info,
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003 Standard x64 Edition" /noexecute=optout /fastdetect
We didn't enable /3b and /pae. we are not sure sql server is using maximum memory effectively and the performance of the sql server is improved or not.
Anyone please help whether that the memory is apt for the server performance and also inform me that we are missing something.

Viewing 15 posts - 1 through 15 (of 26 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

I don't know how you're measuring memory but you can't use task manager. If you're enabled lock pages in memory you MUST set the max memory on the sql server to avoid sql server grabbing it all and performance degrading. I'd probably set it to around 3.5gb and see how things go.

use

select

* from sys.dm_os_performance_counters

to check your memory usage.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 2

do not do /3gb or PAE on 64 bit since it's not needed

check your paging file settings. i found that on windows 2003 x64 the default is 2GB - 4GB which is very bad. change it to system managed or 1.5 times the physical RAM to twice the physical RAM.

i left SQL 2005 to manage memory on our 64 bit servers and no problem. even in a cluster we had an unplanned failover and the other node automatically adjusted the memory for both instances. we are running 32GB on each node. SQL grabs everything except for 300MB or so and all is well.

BOL and MS says to use lock pages in memory only after testing since it's beneficial only in certain situations.

64bit is a new flat memory model and new ways of thinking and managing memory. AWE was a 32bit hack and isn't really needed in 64 bit because a process can access some obscene amount of memory. In win2003 Enterprise i think it's like 2TB

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Check the following perfmon counters:

Buffer cache hit ratio (should be above 90%)

Pages/sec (The lower the better)

Lazy writes/sec (should be lower than checkpoint. Lazy writer writes pages out due to memory pressure)

Checkpoint pages/sec

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Parthipan-244146 SSCrazy Points: 2178 More actions · Answer 4

Hi All,

Thank you for all of your valuable techincal inputs.

Now we have disabled the 'lock pages in memory' and setup the performance counters in order to compare any performance improvement is happening by enabling 'lock pages in memory' and also by setting Min and Max memory option to apt value.

I collected the memory related counters by running sys.dm_os_performance_counters after disabling the 'lock pages in memory' and below are the outputs.

Someone please check and confirm us for any abnormal counter value and also advice us how we can troubleshoot.

Object_name	counter_name	instance_name	cntr_value	cntr_type
SQLServer:Buffer Manager

Buffer cache hit ratio 1690537003264

SQLServer:Buffer Manager Buffer cache hit ratio base 16921073939712

SQLServer:Buffer Manager Page lookups/sec 36866692272696576

SQLServer:Buffer Manager Free list stalls/sec 0272696576

SQLServer:Buffer Manager Free pages 19565792

SQLServer:Buffer Manager Total pages 3062465792

SQLServer:Buffer Manager Target pages 46256265792

SQLServer:Buffer Manager Database pages 2421065792

SQLServer:Buffer Manager Reserved pages 065792

SQLServer:Buffer Manager Stolen pages 621965792

SQLServer:Buffer Manager Lazy writes/sec 0272696576

SQLServer:Buffer Manager Readahead pages/sec 17235272696576

SQLServer:Buffer Manager Page reads/sec 24112272696576

SQLServer:Buffer Manager Page writes/sec 1065272696576

SQLServer:Buffer Manager Checkpoint pages/sec 168272696576

SQLServer:Buffer Manager AWE lookup maps/sec 0272696576

SQLServer:Buffer Manager AWE stolen maps/sec 0272696576

SQLServer:Buffer Manager AWE write maps/sec 0272696576

SQLServer:Buffer Manager AWE unmap calls/sec 0272696576

SQLServer:Buffer Manager AWE unmap pages/sec 0272696576

SQLServer:Buffer Manager Page life expectancy 59799665792

SQLServer:Memory Manager Connection Memory (KB) 24065792

SQLServer:Memory Manager Granted Workspace Memory (KB) 065792

SQLServer:Memory Manager Lock Memory (KB) 423265792

SQLServer:Memory Manager Lock Blocks Allocated 2205065792

SQLServer:Memory Manager Lock Owner Blocks Allocated 2205065792

SQLServer:Memory Manager Lock Blocks 165792

SQLServer:Memory Manager Lock Owner Blocks 165792

SQLServer:Memory Manager Maximum Workspace Memory (KB) 268844065792

SQLServer:Memory Manager Memory Grants Outstanding 065792

SQLServer:Memory Manager Memory Grants Pending 065792

SQLServer:Memory Manager Optimizer Memory (KB) 28065792

SQLServer:Memory Manager SQL Cache Memory (KB) 172065792

SQLServer:Memory Manager Target Server Memory (KB) 370049665792

SQLServer:Memory Manager Total Server Memory (KB) 24499265792

BR,

Parthipan

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

I collected the memory related counters by running sys.dm_os_performance_counters after disabling the 'lock pages in memory' and below are the outputs.

Something odd there. You have buffer cache hit ratio at 1690. That should be a percentage, ie between 0 and 100.

Use Performance monitor rather. Monitor for a day or so at minute intervals, then you can get a good look at peaks and dips and averages, rather than a point-in-time that you have here

Couple things I did notice that don't look good. The stolen pages is > 0. That means that pages are been stolen from one buffer for another.

The target and total server memory are very different. Target is what SQL's trying to get the memory to, total is what it has.

Are there other apps on this server?

Check through the error log, see if you have any entries that mention that SQL's memory is getting paged out.

I notice that your max and min memory is not set. Personally I like to set those so that I know how much memory SQL can take. I had a case recently where a 2005 installation on an Itanium allocated more and more memory until there was <200 MB left for the OS.

If SQL is the only app on this server, then I'd set the max memory to something like Total Server Memory-2GB, and set the min memory to a value a little lower. If there are other apps on the server, then you'll need to set the memory so that other apps have what they need.

btw, on 64 bit SQL, Microsoft recommends the use of the lock pages.

http://blogs.msdn.com/slavao/archive/2005/08/31/458545.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Parthipan-244146 SSCrazy Points: 2178 More actions · Answer 6

We already ran performance monitor on the below counters and the value are

SQL Server Buffer: Buffer Cache Hit Ratio - 99% normal

Memory\Available Bytes: The server has an average of 350MB free physical memory at all the time and it is normal.

Memory: Pages/sec: The paging value is 0-20 most of the time from statistical data but intermediate high value.

Processor: % Processor Time : The value is around 10 and it is normal

As you have suggested we have set the minimum and maximum value for the memory value in the sql server properties and have to check the performance of the server.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

What did you set the max to?

Those figures look fine. The only thing I might do with that is to drop SQL's memory slightly so there was more free for the OS's use, if needed

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Parthipan-244146 SSCrazy Points: 2178 More actions · Answer 8

In the sql server properties the memory values are set as,

min memory = 0MB Max memory = 2147483647MB

Please suggest us how much value we can keep in Min and max memory to improve the performance of the server.

Parthipan-244146 SSCrazy Points: 2178 More actions · Answer 9

I checked the event log in the server and can able to see information of hanging of sql management studio and sql configuration tool regularly with the following information,

Hanging application SqlSAC.exe, version 9.0.2047.0, hang module hungapp, version 0.0.0.0, hang address 0x00000000.

Hanging application SqlWb.exe, version 2005.90.1399.0, hang module hungapp, version 0.0.0.0, hang address 0x00000000.

Did anyone faced this error before and I cannot able to find out why the application are hanging.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Don't know about why they are hanging, but why do you have client tools installed on a server, any why are they been used on the server?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 11

we always put client tools on the server because our datacenter is 20 miles and two routers away. if we have to run a big query for some reason we run it on the server via terminal services so we don't clog up the T1 with the data

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 12

if you're running 64bit you must set the max memory in sql server, otherwise you'll have real problems. I can't imagine why anyone would suggest you disable lock pages in memory - please set that back.

As previously pointed out you can't apply the same thoughts to 64bit as 32bit.

Enable Lock Pages in memory
Set maximum memory for SQL Server
Set the o/s to be optimised for programs/perfromance not system cache

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 13

i'll be changing things on a QA server and will set this there to see what effect it has

Parthipan-244146 SSCrazy Points: 2178 More actions · Answer 14

Colin,

We have enabled the lock pages in memory option.

Can you suggest me how much min and max memory values should I configure for the sql server.

As of now

Memory: 4.83GB

In sql server setting,

min memory = 0MB Max memory = 2147483647MB

How can I experience the change in performance of the server after making the above change.

BR,

Parthipan