Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Why SQL Server enable Large Pages? Expand / Collapse
Author
Message
Posted Wednesday, February 27, 2013 12:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:50 AM
Points: 126, Visits: 1,091
From SQL Server error log, i found following information:

[highlight=#ffff11]2009-06-04 12:21:08.16 Server Large Page Extensions enabled.
2009-06-04 12:21:08.16 Server Large Page Granularity: 2097152
2009-06-04 12:21:08.21 Server Large Page Allocated: 32MB [/highlight]

It seems my SQL Server enabels large pages. As my database is OLTP and enable large pages may hurt performance, so i want to turn it off.

My SQL Server is 2005 Enterprise(64 bit) and has 12GB RAM. Service account is local system, trace flag 834 is turned off.

Is there a way to make SQL Server not enable large pages? Currently i don't want to change my service account.

Thanks in advance?



[li][/li]
Post #1424366
Posted Wednesday, February 27, 2013 12:59 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
It's not using large pages, don't worry about that. Without traceflag 834 SQL won't use large pages, it enables the extensions, but that's all.

http://blogs.msdn.com/b/psssql/archive/2009/06/05/sql-server-and-large-pages-explained.aspx <- explained there why you see that entry in the error log.

When SQL's actually using large pages, your error log entry looks like:

2009-06-04 14:20:31.13 Server Large Page Extensions enabled.
2009-06-04 14:20:31.13 Server Large Page Granularity: 2097152
2009-06-04 14:20:31.14 Server Large Page Allocated: 32MB
2009-06-04 14:20:40.03 Server Using large pages for buffer pool.
2009-06-04 14:27:56.98 Server 2048 MB of large page memory allocated.





Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1424369
Posted Wednesday, February 27, 2013 1:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:50 AM
Points: 126, Visits: 1,091
Thanks Gail. When i query memory utilization by SQL Server, i found AWE allocated about 2GB, working set only taken 88MB ,is it normal?

Query AWE allocated memory:

select
sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
from
sys.dm_os_memory_clerks



[li][/li]
Post #1424374
Posted Wednesday, February 27, 2013 1:12 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
How are you seeing the working set? Let me guess, task manager?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1424377
Posted Wednesday, February 27, 2013 1:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:50 AM
Points: 126, Visits: 1,091
yes, from task manager.

[li][/li]
Post #1424378
Posted Wednesday, February 27, 2013 1:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 40,615, Visits: 37,081
Never use task manager to check SQL's memory as it will display incorrect values if you have locked pages enabled (Task manager limitation)


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1424383
Posted Wednesday, February 27, 2013 1:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:50 AM
Points: 126, Visits: 1,091
got it. thanks so much.

[li][/li]
Post #1424385
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse