When you configure the memory and on your system if other applications are also present, in such cases specially for 64 bit operating system the de-allocation of memory may impact huge performance issue. so lock page memory is useful here as to prevent/reserve the memory for Sql server.
1. Run “secpol.msc” from windows run dialog -Local Security Setting

2. Expand “Local Policies” in the left pane

3. Click “User Rights Assignment”

4. Double-click “”Lock Pages in Memory”

5. In the “Local Security Setting” page, please check if the account used by SQL Server in the list box

Following is the link which shows how to configure Lock page memory.

http://www.sqlservercentral.com/blogs/sqldbauk/archive/2010/06/25/lock-pages-in-memory.aspx

HTH
Vinay

Important: System Objects/Views/DMV/DMF

This is the some of the list of System Objects/Views/DMV/DMF.

Catalog View:

All system information is stored on catalog views which look like

Sys.xxx

Eg. Sys.databases

Compatibile views:

This are views to manage old compatibility

Select * from master.dbo.sysprocesses

Server Side DMV

1. Sys.dm_exec* (query exection)
2. Sys.dm_io* (io level)
3. Sys.dm_db* (database level  like index….)
4. Sys.dm_tran* (transaction level)
5. Sys.dm_os* (os level)

Component level DMV:

1. Sys.dm_Repl* (replication)
2. Sys.dm_broker* (service broker)
3. Sys.dm_fts* (full Text Search)
4. Sys.dm_clr*( CLR)

—————————– Understand the dmv:

Require permission as view database stats or view server stats

1.  select count(1) from sys.all_objects –1841

2.  select * from sys.databases

3.  select count(*) from sys.system_objects –1763

4.  select * from sys.sysobjects –- show all the objects in present database

5.  select * from sys.system_sql_modules where object_id=object_id(’sys.sysobjects’)—-shows code for system objects

6.  select * from sys.dm_exec_sessions -– like sysprocesses info

7.  select * from sys.dm_exec_requests order by logical_reads desc -– like sysprocesses info

select * from sys.dm_exec_requests r cross apply sys.dm_exec_sql_text(r.sql_handle) where session_id>50

8.  select * from sys.dm_os_waiting_tasks  — current waits

9.  select * from sys.dm_os_wait_stats  — report for waits

10.select * from sys.dm_os_latch_stats –report for latchs

11.select db_name(database_id),* from sys.dm_io_virtual_file_stats(NULL , NULL )

order by io_stall desc

12.select * from sys.dm_os_wait_stats

13.select * from sys.dm_db_index_operational_stats(db_id(),NULL,NULL,NULL)

14.select * from sys.dm_db_partition_stats

15.select * from sys.dm_exec_query_stats

Sys.dm_exec_query_stats:

Select * from Sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(qs.sql_handle)

cross apply sys.dm_exec_query_plan(qs.plan_handle)

16.select * from sys.dm_db_index_usage_stats

17.select object_name(object_id),* from sys.dm_db_index_physical_stats (

db_id(),null,null,null,default)  — index fragmentation information

18.select * from sys.dm_os_latch_stats

19.select * from sys.dm_db_missing_index_group_stats

1. select * from sys.dm_exec_query_optimizer_info
2. select * from sys.dm_exec_cached_plans

22.select * from sys.system_internals_allocation_units  — inside information about object first root and next page address.

1. select * from sys.system_internals_partitions  –more inforation abt views useful for join of system_internals_allocation_units

22, 23 used to be achieve using DBCC IND for DBCC Page() with dbcc traceon(3604)

1. select * from sys.dm_io_pending_io_requests  — any pending io
2. select * from sys.dm_tran_locks  –lock info
3. select * from sys.dm_tran_active_transactions
4. select * from sys.dm_os_sys_info  –sys info
5. select * from sys.dm_os_performance_counters  –perfmon info
6. select * from sys.dm_os_schedulers  –CPU info
7. select * from sys.dm_os_ring_buffers  –memory
8. select * from sys.dm_os_buffer_descriptors  –memory

32.select * from sys.dm_os_memory_cached_counters  –Sql server 2008

33.select * from sys.dm_os_sys_memory –Sql server 2008

DBCC MEMORYSTATS

Thanx.

Vinay

Reference:

http://technet.microsoft.com/en-us/library/bb838723(office.12).aspx

Introduction to Memory configuration

Yesterday I was discussion with one of my friend on memory management and internals to expertise in sql server. So started working more on it and found very fundamental information everyone should aware of it. Memory management for MS sql server is very important. We should have a basic knowledge of memory information, how it works and how to configure to effectively usage of it. Memory is a common resource of the system.

Memory stores everything about sql server.

For 32 Bits system(x86):-

Memory has a limit of 4GB x86 (32bit 2³²) of VAS (Virtual Address Space) which divides as 64KB for Null blocks which stores NULL Pointers, 2GB is for USER MODE and 2 GB for Kernel Mode. This is the default behavior of the memory configuration.

We can increase the USER mode to 3GB by adding /3GB option with /USERVA at BOOT.ini file till Windows server 2003 and earlier version, for Windows server 2008 is BCDEdit.exe. This will be good for USERMODE but restricting KERNEL mode to 1GB which may impact performance somewhere.

What if we wanted to use more than 4GB of memory? For Windows to recognize more than 4GB of memory we have to enable PAE option. PAE option will increase the system to recognize more than 4GB of memory, which is up to 64GB for x86.

Now our system has more than 4GB of memory, but for MS Sql server to use more than 3GB of memory requires to enable AWE configuration option. Using AWE option Sql server will recognize that extra memory enabled by PAE. This extra memory is only good for database cache pages not procedure cache.

USER Mode of Memory is divided into two parts:

1. MemToLeave (Stack Size  * Max Worker Thread ) + (-g startup option)

Default values

Stack size =512k

Max worker thread = 256k

-g option =256mb (we can change this)

1. Buffer Pool (USERMode (2GB) – MemToLeave).

For 64 Bits system:-

The system is having same partitions but here the system has huge capacity for the system. 2 64. so here USER MODE could be 2GB to 8TB which is very high and generally nobody uses this. And Kernel Mode uses up to 8TB, means for 64 Bit system there is no upper limit for memory. Hence no need for PAE option. But we can use AWE ability to increase the VAS is used.

WOW (Windows on windows)

System, which has 32 Bit Sql server on 64 Bit Windows system(WOW). We can have 4GB of USER Mode space. And still use AWE for extra USER Memory.

Additional information:

Buffer Pool Stores:

>>  Sql server connection requires 32 bits of Memory

>>  Lock requires 96 Bytes of memory.

>>  Data Pages

>>  Compile plan

>> Execution Plan

>> Workable

As this is my first blog exclusive on memory, will try to write some more blogs on Memory in future.

Thanx.

Vinay

http://vinay-thakur.spaces.live.com/blog/

http://rdbmsexperts.com/Blogs/

https://twitter/thakurvinay

Reference:

http://blogs.msdn.com/slavao/archive/category/9005.aspx

Bob Ward’s PASS Webcast.