تنظیم Ram برای SqlServer

[karimh]

سلام دوستان

من توی یه آزمون به یه سوالی برخورد کردم که جواب کامل اونو نمیدونم می شه کمکم کنید؟

متن سوال دقیقا این بود:

سروری وجود دارد که دارای 8 گیگا بایت Ram می باشد ولی SqlServer از 2 گیگا بایت بیشتر نمی تواند استفاده کند مجموعه دستوراتی که باعث می شود از حداکثر 7 گیگا بایت استفاده شود چه می باشند؟ چرا 8 گیگا بایت را نباید تماما به SqlServer اختصاص داد؟

مرسی :sunglass:

[moh_moh_ab]

با سلام و درود
اساساً sql حافظه مورد نیاز خود را از حافظه فوقانی سیستم که مقدار آن هم زیاد نیست تامین می کند سوالی که از شما شده جنبه علمی و کاربردی ندارد . :sunglass:

[moh_moh_ab]

با سلام و درود
اساساً sql حافظه مورد نیاز خود را از حافظه فوقانی سیستم که مقدار آن هم زیاد نیست تامین می کند سوالی که از شما شده جنبه علمی و کاربردی ندارد . :sunglass:

[karimh]

اما کسی که این سوال رو پرسیده بود مدرک مایکروسافت خودشو با رتبه ممتاز گرفته و بعید میدونم سوالش این قدر بی ربط باشه. :kaf:

منظور شما از حافظه فوقانی سیستم چیه؟ :گیج:

[hmm]

منظور شما از حافظه فوقانی سیستم چیه؟
حافظه بیشتر از حافظه اصلی رو حافظه فوقانی میگن
تا اونجایی که میدونم sql server از تمام حافظه آزاد استفاده میکنه و در موقعی که سیستم عامل با کمبود حافظه روبرو میشه دوباره حافظه رو آزاد میکنه یعنی یه تعامل دوطرفه با سیستم عامل داره
ولی در bol نوشته بود بیشتر از 2g رو نمیتونه مدیریت کنه
حالا قضیه این 7g چیه نمیدونم
:sorry:

[AminSobati]

ولی در bol نوشته بود بیشتر از 2g رو نمیتونه مدیریت کنه
باید AWE رو فعال کنین برای دسترسی به حافظه بیشتر

چرا 8 گیگا بایت را نباید تماما به SqlServer اختصاص داد؟
اگر سرور شما فقط در اختیار SQL Server هست چرا نباید این اجازه رو بهش داد؟ مگر اینکه نرم افزارهای دیگه ای هم روی این سرور باشند که حافظه قابل توجهی نیاز دارند.
دیدن این لینک هم بد نیست:
http://www.barnamenevis.org/forum/viewtopic.php?t=25669

مجموعه دستوراتی که باعث می شود از حداکثر 7 گیگا بایت استفاده شود چه می باشند؟
EXEC SP_CONFIGURE

[jk]

Configuration Steps
1. Assuming 8GB of physical memory, after adding the extra RAM, and prior to rebooting the server, your boot.ini
should contain both the “/3GB /PAE” switches. Not having /3GB in your boot.ini will translate to 2GB of RAM
reserved for the operating system, instead of 1GB remaining free with the “/3GB” switch. The “/PAE” switch is
required if you want SQL Server to support more than 4GB of RAM.
2. Make sure that the SQL Server service account has been granted “Lock Pages in Memory”) privileges. Just
because your service account is a member of the administrators group does NOT mean that it has this policy
setting already. I configured this setting by selecting Start | Run | and typing gpedit.msc. I selected OK to launch
the Group Policy editor. I expanded Computer Configuration | expanded Windows Settings, Security Settings,
Local Policies, and then clicked User Rights Assignments. In the Policy pane (on the right), I double clicked “Lock
pages in memory”, and added the SQL Server service account used to run the SQL Server service. For Domain
member machines, be sure that no security policies at the site, domain, or organization unit overwrite your policy
change. Also, the policy change does not affect permissions of the service account until the SQL Server service
is restarted. But do not restart the service yet!
3. In Query Analyzer, connected as sysadmin for your SQL Server instance. Enable AWE by executing the
following script:


sp_configure ‘show advanced options’, 1
RECONFIGURE
GO
sp_configure ‘awe enabled’, 1
RECONFIGURE
GO


This setting does not take effect until you restart the SQL Server instance – but do not do it yet – there is more!
4. Once AWE is enabled, SQL Server will no longer dynamically manage memory. SQL Server will grab all
available physical memory, leaving 128MB or less for the OS and other applications to use. This underscores the
importance of setting a max server memory amount that SQL Server should be allowed to consume. Determine
this upper limit based on memory consumption of other applications on your server. Also note that a lower limit
(min server memory) is no longer relevant in the context of AWE.
In this example, to enable 7GB as the maximum SQL Server memory allowed to be consumed, issue the following
command:

sp_configure ‘max server memory’, 7168
RECONFIGURE
GO
sp_configure ‘show advanced options’, 0
RECONFIGURE
GO

5. NOW reboot your machine (assuming you have not rebooted since reconfiguring the boot.ini file). If you have
already rebooted after configuring the boot.ini file, you need only restart the SQL Server instance.
6. After the restart, check the SQL Log in Enterprise Manager right away. The most recent startup log should
contain the words “Address Windowing Extensions enabled” early in the log. If you didn’t do it right, the log should
say, “Cannot use Address Windowing Extensions because…”. The reasons for this message will be noted, such
as not assigning “lock pages in memory”.

[emdadgar2]

Configuration Steps
1. Assuming 8GB of physical memory, after adding the extra RAM, and prior to rebooting the server, your boot.ini
should contain both the “/3GB /PAE” switches. Not having /3GB in your boot.ini will translate to 2GB of RAM
reserved for the operating system, instead of 1GB remaining free with the “/3GB” switch. The “/PAE” switch is
required if you want SQL Server to support more than 4GB of RAM.
2. Make sure that the SQL Server service account has been granted “Lock Pages in Memory”) privileges. Just
because your service account is a member of the administrators group does NOT mean that it has this policy
setting already. I configured this setting by selecting Start | Run | and typing gpedit.msc. I selected OK to launch
the Group Policy editor. I expanded Computer Configuration | expanded Windows Settings, Security Settings,
Local Policies, and then clicked User Rights Assignments. In the Policy pane (on the right), I double clicked “Lock
pages in memory”, and added the SQL Server service account used to run the SQL Server service. For Domain
member machines, be sure that no security policies at the site, domain, or organization unit overwrite your policy
change. Also, the policy change does not affect permissions of the service account until the SQL Server service
is restarted. But do not restart the service yet!
3. In Query Analyzer, connected as sysadmin for your SQL Server instance. Enable AWE by executing the
following script:


sp_configure ‘show advanced options’, 1
RECONFIGURE
GO
sp_configure ‘awe enabled’, 1
RECONFIGURE
GO


This setting does not take effect until you restart the SQL Server instance – but do not do it yet – there is more!
4. Once AWE is enabled, SQL Server will no longer dynamically manage memory. SQL Server will grab all
available physical memory, leaving 128MB or less for the OS and other applications to use. This underscores the
importance of setting a max server memory amount that SQL Server should be allowed to consume. Determine
this upper limit based on memory consumption of other applications on your server. Also note that a lower limit
(min server memory) is no longer relevant in the context of AWE.
In this example, to enable 7GB as the maximum SQL Server memory allowed to be consumed, issue the following
command:

sp_configure ‘max server memory’, 7168
RECONFIGURE
GO
sp_configure ‘show advanced options’, 0
RECONFIGURE
GO

5. NOW reboot your machine (assuming you have not rebooted since reconfiguring the boot.ini file). If you have
already rebooted after configuring the boot.ini file, you need only restart the SQL Server instance.
6. After the restart, check the SQL Log in Enterprise Manager right away. The most recent startup log should
contain the words “Address Windowing Extensions enabled” early in the log. If you didn’t do it right, the log should
say, “Cannot use Address Windowing Extensions because…”. The reasons for this message will be noted, such
as not assigning “lock pages in memory”.


آقا چرا بعضی ها اصرار دارند msdn را کپی پیست کنند؟
لطفا برای بهره برداری بیشتر همه به جای کپی اصل مطلب انگلیسی، ترجمه با خلاصه آن را یادداشت کنید زیرا مطمئنا خود میکروسافت بهتر توضیح داده!
با عرض پوزش