July 6, 2007 at 3:11 pm
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.
July 9, 2007 at 12:39 am
You need to include the /3GB switch so that sql server can utilise more than 2GB of RAM else it will use only the default 1.7GB that it can use.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 9, 2007 at 9:53 am
Hi,
Is this necessary to enable /3gb option in boot.ini file in 64bit version.
can AWE option can make the sql server to use more than 3gb of physical memory.
But i read from an article that in 64bit version awe option is not needed to enable and it is better to enable lock pages in memory option.
We enabled lock pages in memory and can some one confirm that as of now how much memory sql server can use.
BR,
Parthipan
July 10, 2007 at 4:42 am
Wat's the edition of SQL server. If it is Standard Edition it cannot go beyond 2GB. Where as on Enterprise edition can take more than 2 gb of ram if added with /3gb switch.
July 10, 2007 at 6:56 am
SQL Server 2005 standard edition has no RAM max:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
(Well the max of the OS)
July 10, 2007 at 12:12 pm
Hi Parthipan,
There seems to be some confusion over 32-bit vs. 64-bit architectures. You do not want to do either 3gb or PAE. There is not a 4 GB virtual memory limitation with 64 bit architecture, which 3GB would change. Also, PAE allows for 36-bit memory addressing, which is only relevant on 32-bit processor architectures. With 4 GB of RAM, try setting a min memory setting and see if SQL picks it up. Other things you may want to look at is the physical memory used in the OS as opposed to what it shows in the task manager processes part. You may find that SQL is taking more memory than it shows, due to the type of memory allocation it does. Other than that, I don't believe you are missing anything...
Thanks,
Eric
July 10, 2007 at 7:09 pm
Hi All,
Thank you for your valuable inputs.
what is the minimum memory can we set in the sql server properties. As of now we configuref
min memory = 0MB Max memory = 2147483647MB and Total Physical RAM=4.83GB
and in the task manager sql server.exe is taking 85MB memory and 670MB of PF utilization.
Eric,
Please enlighten me what are the memory parameters I have to look in to it.
BR,
Parthipan
July 12, 2007 at 8:49 am
Whew, the flood and drought of on-call! There isn't too much to set up in SQL '05 64-bit. As long as you can lock pages inmemory, only max and min server memory settings really seem relevant. I'm not sure which DM it is, but I would check out the memory related DM views, too. As for memory settings, try setting a min server memory (of say, 1 GB), restart SQL and see if it picks up in the SQL log.
BTW, what kind of perf problems are you having at this point?
Thanks,
Eric
July 12, 2007 at 10:39 am
Hi All,
Thank you for all of your valuable techincal inputs.
Eric,
As an administrator I cannot able to find out any difference in performance of the server. But the users scheduled some application jobs which is taking much time for completion. They are comparing with earlier setup(before server crash) in which the server performance was 4 times better than the current setup.
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 | cntr_value | cntr_type |
| SQLServer:Buffer Manager | Buffer cache hit ratio | 1690 | 537003264 |
| SQLServer:Buffer Manager | Buffer cache hit ratio base | 1692 | 1073939712 |
| SQLServer:Buffer Manager | Page lookups/sec | 36866692 | 272696576 |
| SQLServer:Buffer Manager | Free list stalls/sec | 0 | 272696576 |
| SQLServer:Buffer Manager | Free pages | 195 | 65792 |
| SQLServer:Buffer Manager | Total pages | 30624 | 65792 |
| SQLServer:Buffer Manager | Target pages | 462562 | 65792 |
| SQLServer:Buffer Manager | Database pages | 24210 | 65792 |
| SQLServer:Buffer Manager | Reserved pages | 0 | 65792 |
| SQLServer:Buffer Manager | Stolen pages | 6219 | 65792 |
| SQLServer:Buffer Manager | Lazy writes/sec | 0 | 272696576 |
| SQLServer:Buffer Manager | Readahead pages/sec | 17235 | 272696576 |
| SQLServer:Buffer Manager | Page reads/sec | 24112 | 272696576 |
| SQLServer:Buffer Manager | Page writes/sec | 1065 | 272696576 |
| SQLServer:Buffer Manager | Checkpoint pages/sec | 168 | 272696576 |
| SQLServer:Buffer Manager | AWE lookup maps/sec | 0 | 272696576 |
| SQLServer:Buffer Manager | AWE stolen maps/sec | 0 | 272696576 |
| SQLServer:Buffer Manager | AWE write maps/sec | 0 | 272696576 |
| SQLServer:Buffer Manager | AWE unmap calls/sec | 0 | 272696576 |
| SQLServer:Buffer Manager | AWE unmap pages/sec | 0 | 272696576 |
| SQLServer:Buffer Manager | Page life expectancy | 597996 | 65792 |
| SQLServer:Memory Manager | Connection Memory (KB) | 240 | 65792 |
| SQLServer:Memory Manager | Granted Workspace Memory (KB) | 0 | 65792 |
| SQLServer:Memory Manager | Lock Memory (KB) | 4232 | 65792 |
| SQLServer:Memory Manager | Lock Blocks Allocated | 22050 | 65792 |
| SQLServer:Memory Manager | Lock Owner Blocks Allocated | 22050 | 65792 |
| SQLServer:Memory Manager | Lock Blocks | 1 | 65792 |
| SQLServer:Memory Manager | Lock Owner Blocks | 1 | 65792 |
| SQLServer:Memory Manager | Maximum Workspace Memory (KB) | 2688440 | 65792 |
| SQLServer:Memory Manager | Memory Grants Outstanding | 0 | 65792 |
| SQLServer:Memory Manager | Memory Grants Pending | 0 | 65792 |
| SQLServer:Memory Manager | Optimizer Memory (KB) | 280 | 65792 |
| SQLServer:Memory Manager | SQL Cache Memory (KB) | 1720 | 65792 |
| SQLServer:Memory Manager | Target Server Memory (KB) | 3700496 | 65792 |
| SQLServer:Memory Manager | Total Server Memory (KB) | 244992 | 65792 |
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply