2 What function SQL Server use to allocate the memory inside the engine?

Many of the SQL Server blog mention SQL Server use VirtualAlloc() to allocate the memory. Many of them also mention when use AWE or LPIM in SQL 2012, SQL server use AllocateUserPhysicalPages to allocate the page outside the working set to prevent the memory being page out.

In PASS SUMMIT 2012, Bob Ward show how to use WinDbg to track the VirtualAlloc, this trigger my curiosity. Can I also see  AllocateUserPhysicalPages in the call stack? The answer should be yes, but the journal takes longer than I thought.

Here are my Lab environment:

Lab Setup

• VM1: 64-bit Windows 2008R2 , SQL Server 2012
• VM2: 64-bit Windows 2008R2, SQL Server 2008R2

Both Hosts, I have LPIM enable . You can refer here for how to enable LPIM  I can confirm the LPIM is enable from error log.

virtualalloc

Here are the steps I find the VirtualAlloc call.

• Open command line window with administrator privilege
• Start Wingbg as below:

set path=%path%;C:\Program Files (x86)\Windows Kits\8.1\Debuggers\x64

cd C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn

windbg -y srv*c:\public_symbols*http://msdl.microsoft.com/download/symbols sqlservr.exe -sMSSQLSERVER

and type , bp is to set the break point,

bp kernelbase!VirtualAlloc

and hit g until it hit the break point and hit k to print the call stack.

0 Use Debug Diagnostics tool to analysis the memory dump

Debug diagnostic tool is the application that we can use to analysis the memory dump.

This application can not be used to analysis the memory dump file that is generate by sqldumper.

This application can be download from here.

Installation process is very straight forward. 

We will need to admin privilege to execute the application.

1 SQL Server error 1204

When we see the error 1204 in the SQL Server error log, it indicate we run our of locks in the SQL server.

The error message looks like this

The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration

We need to closely monitor number of the locks being used. If SQL server runs out of locks, the query would fail. Then we need to exam whether SQL server has sufficient memory to let the memory manager to manager the lock or there are query that may use excessive locks . In the later case, we may need to check exam the query plan to optimize the query whenever possible .

 

How to check the number of locks SQL server is currently using?

select request_session_id,  resource_type,db_name(resource_database_id),

resource_associated_entity_id,

request_mode,request_type,

request_status

from sys.dm_tran_locks

0 Enable Lock Page in Memory (LPIM) for SQL Server 2012

SQL Server 2012 64-bit use AWE API for LPIM. Even AWE is no longer needed on the 64-bit because the VAS on the 64-bit is significant larger ( 8TB), however, SQL server engineer found to use the AWE API for LPIM could get the performance gain for SQL server memory allocation. ( http://blogs.msdn.com/b/slavao/archive/2005/04/29/413425.aspx) .

When AWE enable ( in any version) , the memory that is allocated through AWE API would not be part of the working set memory hence, it is “LOCK” . This is the reason that LPIM privilege needs to be set on the SQL server account.

Grant the LPIM privilege

Add the SQL server account to the Lock Page in memory policy through gpedit.msc.

SQL Server error log

 

DMV

 

select osn.node_id, osn.memory_node_id, osn.node_state_desc, omn.locked_page_allocations_kb

from sys.dm_os_memory_nodes omn

inner join sys.dm_os_nodes osn on (omn.memory_node_id = osn.memory_node_id)

where osn.node_id <> 64

select large_page_allocations_kb,locked_page_allocations_kb

from sys.dm_os_process_memory

VMMAP

VMMAP would also show the locked Working set. the number is the same as the result we see in the DMV.

No More AWE in SQL 2012

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62
The configuration option 'awe enabled' does not exist, or it may be an advanced option.

Here is the screenshot from SQL Server 2012 from SSMS.

Here is the screenshot from SQL Server 2008R2 from SSMS

 

Reference

• How to enable the "locked pages" feature in SQL Server 2012: http://support.microsoft.com/kb/2659143
• Support for Locked Pages on SQL Server Standard Edition (64-bit) systems: http://support.microsoft.com/kb/970070

0 Windows Debug

WinDbg is well known windows debug tool that can use to debug the user mode and kernel mode application.

Download

Windows Debug , AKA WinDbg . WinDbg is part of Windows SDK and can be download from below link.

http://msdn.microsoft.com/en-US/windows/desktop/bg162891

I like to download all the components and use them to install in the multiple machine.

16 HammerDB : Load Testing

HammerDB is well known load testing application. Formally known as Hammerora.

You can download the latest version from SourceForge . http://hammerora.sourceforge.net/ 

4 How to check index creation date

I want to write the query to display the index within the database and its creation date.

Here is the query:

SELECT object_name(i.object_id) as TableName, i.object_id, i.name, i.type_desc,o.create_date,o.type,i.is_disabled

FROM   sys.indexes i

        INNER JOIN sys.objects o ON i.object_id = o.object_id

 WHERE o.type NOT IN ('S', 'IT')

 and o.is_ms_shipped = 0 

 and i.name is not null

ORDER BY create_date DESC

We can test it with below cases:

-- clean the env

IF OBJECT_ID('v1', 'v') IS NOT NULL

  DROP VIEW v1

IF OBJECT_ID('t1', 'U') IS NOT NULL

  DROP TABLE t1

IF OBJECT_ID('t2', 'U') IS NOT NULL

  DROP TABLE t2

IF OBJECT_ID('t3', 'U') IS NOT NULL

  DROP TABLE t3

IF OBJECT_ID('t4', 'U') IS NOT NULL

  DROP TABLE t4

IF OBJECT_ID('t5', 'U') IS NOT NULL

  DROP TABLE t5

-- Create the Heap table

CREATE TABLE t1 (

    c1 nchar(10) NULL,

    c2 int  primary key

    )  

-- create primary key inline in the table DDL

CREATE TABLE t2 (

    c1 nchar(10) NULL,

    c2 int  primary key

    )

-- create primary key out side of table DDL 

CREATE TABLE t3 (

    c1 nchar(10) NULL,

    c2 int NOT NULL ,

    )  

ALTER TABLE t3 

ADD CONSTRAINT PK_t3 PRIMARY KEY CLUSTERED (c2);

-- create index without constrain  

CREATE TABLE t4 (

    c1 nchar(10) NULL,

    c2 int  ,

    )  ;

Create clustered index  idx1_t4 on t4(c2);

Create nonclustered index  idx2_t4 on t4(c1);

-- create index view

create table t5 (

    c1 char(20),

    c2 int not null

)

create view dbo.v1 with SCHEMABINDING

as  select c1,c2 from dbo.t5

create unique clustered index idx_tv1 on v1(c2);

After all the tables are created, we run the query within the database. Here is the result

I also tried to run the query in the adventure work sample database. Interestingly, the table value function also consider as index and show up in the sys.indexes table.

0 How to fix TOC for mnml-blog template in Dokuwiki

I have decided to use dokuwiki for my new blog system. mnml-blog template is very clean and looks exactly like what I am looking for. However, I have struggle with the TOC display issue for this particular template.

Environment

• dokuwiki version: 2013-05-10a “Weatherwax”

• mnml-blog : 2013-02-20

Solution

The TOC display fine in default and other template. After exhaust all the solution I know, I decided to check the dokuwiki document to see what function code generate the TOC.

By default, the tpl_content() function will take care of displaying a Table of Contents itself, prepending it to the actual page content. If your template uses a sidebar or other more complex layout you may want to place the TOCindependently from the page content. This can be done with the tpl_toc() function. When using it, it is important to disable automatic TOC placement by passing the argument false to the tpl_content() function.

¹⁾

Bingo, I decide to grep the mnml-blog and yes, I can not find any code refer to the tpl_toc.

I decided to edit main.php and add my code below the <div class="page">

       <?php
           $toc = tpl_toc(true);
           if ($toc){
                echo $toc;
           } 
        ?>

Wow … The TOC display right away.

I know this is probably not the good solicitous but it works

¹⁾ https://www.dokuwiki.org/devel:templates