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

Today while being on forum found this information so though to blog for future reference. thanx Uri Dimant for sharing this. 

WITH cte 

AS 

( 

SELECT object_name(ic.object_id) as object_name , index_name = i.name,
‘column’ = c.name,
        ‘column usage’ = CASE ic.is_included_column
                    WHEN 0 then ‘KEY’
                    ELSE ‘INCLUDED’
        END
FROM sys.index_columns ic JOIN sys.columns c
    ON ic.object_id = c.object_id
    AND ic.column_id = c.column_id
JOIN sys.indexes i
    ON i.object_id = ic.object_id
    AND i.index_id = ic.index_id 

) SELECT * FROM cte WHERE object_name =’tablename’ 

——————————– 

Below is the query which give information about parallel queries: 

–This first thing to check if CPU is at 100% is to look for parallel queries: 

– Tasks running in parallel (filtering out MARS requests below): 

select * from sys.dm_os_tasks as t  

 where t.session_id in ( select t1.session_id from sys.dm_os_tasks as t1  group by t1.session_id having count(*) > 1  

 and min(t1.request_id) = max(t1.request_id)); 

– Requests running in parallel: 

 select * from sys.dm_exec_requests as r  

 join (select t1.session_id, min(t1.request_id) 

 from sys.dm_os_tasks as t1  

 group by t1.session_id  

 having count(*) > 1  

 and min(t1.request_id) = max(t1.request_id) 

 ) as t(session_id, request_id) 

 on r.session_id = t.session_id  

 and r.request_id = t.request_id; 

 Thanx. 

Vinay 

Reference: 

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/383c0712-9416-4e23-9f45-d74148202596 

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/e5a72c22-56c9-420a-bf2f-05b834665e41 

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.

continue with performace tunning stuff, I would like to make a checklist (kind of) for general performance tunning or dba checklist.

I highly recommend to read my earlier post on performance tunning here

Performance Monitoring (Activities):

1. Dashboard report (2005) Data collector (2008)
2. Performance Monitor (Counters-CPU/Memory/IO/Network/Sql Level)
3. Profiler Utility trace/ Server Side trace….. (can insert into table and analyze)
4. Periodically snapshot for Stats (waits and file stats) and other DMV’s
5. Using DMV to find the query which needs to optimize.
6. Use of Database Tuning Advices
7. Analyze the query compilation plan and time for compilation/execution
8. Check the index /statistics
9. Check the tempDB bottleneck

Other DBA checks:

1. System configuration
2. Check jobs – Backup/reindex/statistics
3. Check disk space
4. Check Error log (can insert into table and analyze)
5. Check Event Viewver

Other checks specific to the components like High Availability and other components is not discuss here.

Will blog in detail of each one.  also try to keep  updated this blog as and when require…. appreciate your comments on this. please help me to make this list better.

Thanx.

Vinay

I was going through my blog and realize that I have not done much on performance tuning, which is one of my favorite topics. so initialed the new category as performance tuning, Will try to cover most of this stuff on performance tuning.

Long back I blog on performance tuning here, which covers performance consideration for sql server 2000 that will work for newer version of sql server as well like sql server 2005 onwards.

As the first blog on performance tunning, would like to request each individual who want to learn more about performance tunning must read white papers on performance tunning –

Appendix A: Monitoring SQL Server health

http://technet.microsoft.com/en-us/library/bb838723.aspx

here this whitepaper shows the performance issue and how to troubleshoot:

CPU Bottleneck

Memory Bottleneck

IO Bottleneck

Blocking

Once you go through these links you will get confidence on troubleshooting any performance related problem.

Keep looking for this category, will write very important stuff about performance tunning.

Happy Learning.

Thanx.

Vinay

http://rdbmsexperts.com/Blogs/

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

http:\www.twitter.com\thakurvinay