라이프로그


The Curious Case of the Dubious Deadlock and the Not So Logical Lock DB창고


Lock pages in memory Setting for 64 bit Standard Edition of SQL Server DB창고

Due to over-whelming customer demand for the "Lock pages in memory" support for the Standard Edition of SQL Server 2005 and 2008, Microsoft has released Cumulative Updates that will help SQL Server 64 bit Standard Edition to "lock pages".

This feature was already available on the Enterprise and Developer Edition. Enabling "Lock pages in memory" enables the SQL Server to use the AWE APIs, thereby avoiding potential performance issues due to trimming of the working set.

"Lock pages in memory" comes as a trace flag that can be enabled on the following cumulative updates:
CU2 for SQL Server 2008 SP1 =>
http://support.microsoft.com/kb/970315/en-us
CU4 for SQL Server 2005 SP3 => http://support.microsoft.com/kb/970279/en-us

How to enable "Lock pages in Memory" at the Windows level:

  1. On the Start menu, click Run. In the Open box, type gpedit.msc. The Group Policy dialog box opens.
  2. On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.
  3. Expand Security Settings, and then expand Local Policies.
  4. Select the User Rights Assignment folder. The policies will be displayed in the details pane.
  5. In the pane, double-click Lock pages in memory.
  6. In the Local Security Policy Setting dialog box, click Add.
  7. In the Select Users or Groups dialog box, add the account that runs the SQL Server Service.
  8. Restart the machine for these changes to take effect.

If you are running an Enterprise or Developer Edition of SQL Server 2005 or 2008, please stop here. The rest of the steps are for the Standard Edition Only.

  1. Ensure that the build of SQL Server 2008 is Cumulative Update 2 on Service Pack 1. Preferably, run the "select @@version" command against the SQL Server Instance and verify that the build is 10.00.2714.00. In case of SQL Server 2005, please verify that the build is 9.00.4226.
  2. Now  set  the  Trace  Flag  845 as a  startup trace flag. This can be done by adding
    -T845 to the startup parameters from the SQL Server Configuration Manager. Please refer to the screenshot below:

    SQLConfigManager
  3. Restart the SQL Server Service.
  4. Verify that the following message is written in the SQL Server ErrorLog file at startup:
            Using locked pages for buffer pool

Note: Enabling Locked Pages may have a negative performance impact on your system performance. Please consider all potential effects before you use this option.
Enabling this trace flag on the Enterprise Edition or the Developer Edition has no effect.

Additional Readings:
How to configure SQL Server to use more than 2 GB of physical memory
Enabling Memory Support for Over 4 GB of Physical Memory
How to reduce paging of buffer pool memory in the 64-bit version of SQL Server
SQL Server Working Set Trim Problems? - Consider...
Support for Locked Pages on SQL Server 2005 Standard Edition 64-bit systems and on SQL Server 2008 Standard Edition 64-bit systems

Have a very good day!!!

Disclaimer: All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.

 

Read the complete post at http://blogs.msdn.com/suhde/archive/2009/05/20/lock-pages-in-memory-now-available-for-standard-edition-of-sql-server.aspx




즐겨찾기


Database
-----------------------------------------------------
MSDN 포럼

SSAS-Info

SQLServer Connect

SQLServer Central

Your community knowledge base Powered by Quest Software

Simple-Talk

SQL Tips

Journey to SQL Authority with Pinal Dave

MSSQL Tips

참고하기
-----------------------------------------------------


기타
-----------------------------------------------------
한인 변호사가 운영하는 한국에 대한 Q&A 블로그

아이피에 대한 모든 정보를 확인 해보자


날짜 구하기 DB창고

--지난달의마지막날구하기

selectdateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))

 

--지난해의마지막날구하기

selectdateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))

 

--현재달의첫째날구하기

selectDATEADD(mm,DATEDIFF(mm,0,getdate()),0)

 

--현재달의마지막날구하기

selectdateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0))

 

--현재해의마지막날구하기

selectdateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))

 

--현재달의첫째주월요일구하기

selectDATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())),0)

 

--현재주의월요일구하기

selectDATEADD(wk,DATEDIFF(wk,0,getdate()),0)

 

--현재해의첫째날구하기

selectDATEADD(yy,DATEDIFF(yy,0,getdate()),0)

 

--현재분기의첫째날구하기

selectDATEADD(qq,DATEDIFF(qq,0,getdate()),0)

 

--현재날의자정구하기

selectDATEADD(dd,DATEDIFF(dd,0,getdate()),0)

 


How to get Client IP Address in SQL Server 2005/2008 DB창고

 

While executing some query, sometimes it is required to have the Client's IP Address who is executing this Query.

SELECT client_net_addressFROM sys.dm_exec_connectionsWHERE session_id = @@SPID;

We can also put this Query into some function which can be used further.
CREATE FUNCTION [dbo].[GetCurrentIP] ()RETURNS varchar(255)ASBEGIN
DECLARE
@IP_Address varchar(255);
SELECT @IP_Address = client_net_address FROM sys.dm_exec_connections
WHERE Session_id = @@SPID;
Return @IP_Address;END
http://arsalantamiz.blogspot.com/2008/06/how-to-get-client-ip-address-in-sql.html

1 2 3 4 5 6 7 8 9 10 다음