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.

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

TempDB Internals:

TempDB is very important DB in sql server, generally sql server automatically takes care of the storage management of the system, and we do not require bothering much about this storage, also now days we are having good/much resources (IO/CPU/Memory) so we don’t need to think much about the TempDB usage. But for the system which has very heavy activity and huge database size may have to consider the usage of tempDB and may be sometime tempDB goes out of disk. Especially after Sql server 2005 were there was a huge improvement at system includes versioning which requires heavy usage of tmepDB.  Hence now a days its very important for everyone to know what is tempDB stores and how it works ,Also what all general recommendation/consideration about tempDB.

TempDB has following properties:

1. This is the System database can only be “Simple” Recovery mode.
2. Every Sql server instance is having only one TempDB Database
3. Auto creates same as Model whenever sql server restarts
4. Initial size of TempDB data file is 8MB and log file is 512KB
5. Does not allow Auto shrink.
6. TempDB will have only one filegroup but can add any number of files.
7. Stores all temporary data means all data will lost once sql server stop/restarts
8. Cannot drop the tempDB database and cannot change the mode of if(readonly/emergency/Offline…)
9. Database snapshot is not supported.
10. Cannot take backup of TempDB.
11. Database checksum is not supported.
12. Objects of TempDB can be cached : if we have small temp objects that can be cached due to drop and create of frequent table (size is less then 8mb).
13. Log growth always on disk whereas for small data it keeps on memory for tempdb
14. For tempdb Log file “REDO” operation not required. After value is not logged(eg update x set y=’abc’ where y=’xyz’ here tempdb does not log ‘abc’)
15. Instance file initialization, when autogrowh happens space of growth is “Zeroed” them out. Make autogrowh fast but could cause security issue-

TempDB stores:

a)      Temporary objects

b)      Internal Objects

c) Version Stored Objects – New in sql server 2005

a) Temporary objects:

Temporary objects are objects which is created temporary using #, ## or veritable @, @@  etc.

eg. If you create a temp table #abc

create table #abc (a int,b varchar(8000),c varchar(8000))

Irrespective of the database you are using this table will create at tempDB database

select * from tempdb.sys.all_objects where name like ‘%abc%’

You will see the object name something like this

#abc________________________________________________________________________________________________________________000000000006 (xxx –connection number)tab

So always remember when you create any temporary objects there is a cost of it as they reside at tmepDB database.

b) Internal Objects:

This is very important thing and generally most of the Developers/DBA does not aware of this. Internal objects are not visible but it uses tempDB storage. When we run a query which requires temporary object to be created to perform that operation is created in tempDB.

Eg. Restart the sql services. And check the size of TempDB data files.

Generally it will be 8mb.

Now suppose you have one big table. Here I am using charge table from credit database download from www.sqlskills.com

select * from charge order by category_no

Now once you run above select statement the size of tempDB data file grows to appx 80MB (wow) …. Howz that. As “order by” clause require creating a temp object for ordering the records from charge table because category_no does not have index on it (not ordered).

There are some other operations which generates internal objects are: Hash join, hash aggregates, DBCC check, Order By etc.

c) Version Stored Objects:

Sql server 2005 has improved the engine and enables the row versioning; Following are some of the features which use row versioning.

>> Snapshot Isolation.

>>Triggers:

In previous version of Sql server 2000 and earlier triggers used to store into log file. On sql server 2005 onwards it keeps into TempDB. After trigger store Versioning.

>>Online index Build:

When online index rebuild and at the same time any DML statements happens it stores as versioning.

Recommendations:-

For Sql Server 2005 multiple data files would be good to improve the performance of TempDB.

For 2000 number of data file better to be good as number   of CPU.

Set 80% of estimated growth, and let 20% be handed by auto growth.

TempDB Shrink can be done  at startup parameter –c –f this will start the sql server with minimum tempdb size.

Errors:

1101 or 1105: no space in tempDB

3959: Version store is full (after 1101 or 1105)

3967: Version store is force to shrink

3958 or 3966: Transaction cannot find required version record.

Troubleshooting tempDB issue:

Generally TempDB issues are TempDB FULL and tempDB performance.

Generally Tempdb will have two issues

TempDB Full:

To troubleshoot the tempDB issue we have two dmvs:

Sys.dm_db.file_space_usage

Sys.dm_db.trask_space_usage

Sys.dm_db_session_space_usage

This issue occurs due to:

Query used in application requires heavy internal objects tempdb – to solve that use nested look join instead of hash join/aggregates can be achieve using creating proper indexes on the table or use hints.

Sys,Dm_os_waiting_task – resource like (2:1:%) where % 1:PFS and 3 GAMs contention issue

Also if require don’t use snapshot isolation level, and things which usages tempDB.

Hope this will help someone to understand more about tmepDB.

Thanx.

Vinay

Reference:

Practical Troubleshooting –Ken Henderson.

http://msdn.microsoft.com/en-us/library/ms345368.aspx
http://msdn.microsoft.com/en-us/library/cc966545.aspx
http://msdn.microsoft.com/en-us/library/ms175527.aspx

http://msdn.microsoft.com/en-us/library/ms190768.aspx

Continue to part 1 

the Jet ODBC driver/OLE DB Provider as 64 bit.

http://blogs.msdn.com/psssql/archive/2010/01/21/how-to-get-a-x64-version-of-jet.aspx

2010 Office System Driver Beta: Data Connectivity Components
http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

Must read Paul’s Myth for April Month.

http://www.sqlskills.com/BLOGS/PAUL/category/Misconceptions.aspx

Must read Glenns blog for April “A DMV per DAY”

http://glennberrysqlperformance.spaces.live.com/?_c11_BlogPart_BlogPart=blogview&_c=BlogPart&partqs=amonth%3d4%26ayear%3d2010

Will try to post more info here.

Thanx.

Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/blog/

http:\www.twitter.com\thakurvinay