Monday, November 18, 2013

1 SQL Server Large Page

SQL Server is also the windows application. The internal memory allocation is calling the standard windows API. The normal page size for windows memory is 4KB on 64-bit windows OS. To make the memory management more efficiency, we can enable the large page allocation for SQL server. The large page is 2MB. 

To enable the large page, here are some prerequisite needs to be meet first: 

 

  • SQL Server enterprise  Edition
  • Host has at least 8GB or more ram.
  • LPIM privilege is set for the user account

There is another trace flag 834. In MSFT KB article, 834 is to enable the large page for buffer pool. It is one of the approach when tuning the SQL Server performance in large data warehouse environment.

Lets take a look those settings and how it show up for SQL server.  

Large Page allocation with trace flag 834

We can see lock page message in the SQL server error log.

In 2008R2 , without the trace flag 834

2013-11-16 05:57:16.04 Server      Large Page Extensions enabled.
2013-11-16 05:57:16.04 Server      Large Page Granularity: 2097152
2013-11-16 05:57:16.05 Server      Large Page Allocated: 32MB
2013-11-16 05:57:16.50 Server      Using locked pages for buffer pool.

select large_page_allocations_kb,locked_page_allocations_kb from sys.dm_os_process_memory
 
select type,awe_allocated_kb,page_size_bytes from sys.dm_os_memory_clerks
where type like 'MEMORYCLERK_SQLBUFFERPOOL' and memory_node_id=0
 
select bpool_committed,bpool_commit_target,bpool_visible from sys.dm_os_sys_info
 
 
 
select * from sys.dm_os_performance_counters
where object_name like '%Memory%'

image

Monday, November 4, 2013

0 LPIM again

We know LPIM privilege is required for LPIM and large page for SQL server but how does SQL server know your account has the privilege set in the local security policy.

The answer is AdjustTokenPrivileges

 

image

I have not find the way to determine the value for the function return. The same call stack is present whether the LPIM privilege is set or not . This is reasonable because either way, SQL server needs to check the privilege.

The another difference shows in the error log.

When LPIM is enable :

2013-11-04 20:15:24.71 Server      Using locked pages in the memory manager.

When LPIM is not enable:

2013-11-04 20:30:44.16 Server      Using conventional memory in the memory manager.

When Host has more than 8GB memory, Enterprise edition SQL server and LPIM privilege. 

2013-11-04 20:50:20.91 Server      Using locked pages in the memory manager.
2013-11-04 20:50:21.31 Server      Large Page Allocated: 32MB

Wednesday, October 30, 2013

0 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.

image

Tuesday, October 29, 2013

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. 

image

We will need to admin privilege to execute the application.

Monday, October 28, 2013

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

image

Saturday, October 26, 2013

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.

image

SQL Server error log

 

image

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

image

VMMAP

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

image

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.

image

Here is the screenshot from SQL Server 2012 from SSMS.

image

Here is the screenshot from SQL Server 2008R2 from SSMS

image

 

Reference

Friday, October 25, 2013

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.

image

Thursday, October 24, 2013

12 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/ 

image

Tuesday, October 22, 2013

1 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

image

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.

image

Sunday, October 13, 2013

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.

1)

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

1) https://www.dokuwiki.org/devel:templates

Thursday, September 26, 2013

2 SQL Server memory manager

SQL Server 2005/2008/2008R2
SQL server has 2 Memory Manager. Buffer Pool and Memory manager. Buffer Pool manage the single page , the primary consumer for the single pages are Data Buffer and procedure cache. There are other components not data buffer also use single page, it is call stolen page.  When we configure SQL server MAX Memory, we actually only setup the the Max Size of the Buffer Pool ( Single page) only. When SQL Server needs multi pages ( page more than 8K), it switch to new implement memory manger.  components outside of single pages are CLR, MPA, DWA and TS.
SQL Server 2012
Only one memory manager that mange all pages, including Buffer Pool. CLR does not directly manage by memory manager but track through the VAS allocator. components outside of memory manager are DWA and TS.




Sunday, September 22, 2013

0 NUMA ( Non Unify Memory Access) & SQL Server

The hardware trend is to increase the number of CPU ( physical or logical) not to increase the CPU frequency speed. All the CPU require access the memory through the system bus ( memory controller) . However, the more CPU needs to go through the system bus, the more contention would happen. One solution is to increase the speed of the system bus. NUMA is born to solve the solution in different way.

A connection uses any NUMA node.

NUMA is group the CPU and memory to the Node ( NUMA node). Each NUMA node can have its set of the CPUs , system bus and memory. The basic hardware is has at least 2 NUMA nodes but recently hardware trend can have more than 2 nodes as well.

1 NUMA Support MSSQL Server in AWS

I like to test my lab environment with NUMA. However, since it is not possible to simulate that in VMWare and getting my own physical hardware is too expensive. I have been thinking about using AWS.

After truing out different configuration, I finally got what I need with cr1.8xlarg

Here is the coreinfo output:

image

Saturday, September 21, 2013

1 Unattended install SQL Server 2008R2

Here are some notes for unattended installation of SQL Server 2008R2.

  • OS: Windows Server 2008R2
  • SQL Server version:  SQL Server 2008R2 Enterprise Edison

 

Enable dotNet feature

image

0 How to Fix Admin share access error

I have issue access the admin share on Windows server 2008R2 in the domain environment .

The error message is similar to the below screenshot.

image

Many people suggest to enable below 2 keys and reboot the computer.

reg add HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\LanmanServer\Parameters /v AutoShareServer /t REG_DWORD /d 1
 
reg add HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\system\ /v LocalAccountTokenFilterPolicy /t REG_DWORD /d 1

I tried the above approach but does not make much difference.

It turns out that somehow didn’t enable the “File and Printer Sharing for Microsoft Networks” in the network card. After I enable it, the admin share works fine without issue.

 

image

Friday, September 20, 2013

0 Fixed “The trust relationship between this workstation and the primary domain failed”

 

I am messing around with my VM. One of thing I did was clone the VM to another server.

The VM already has join the domain. However, once the new VM has join the domain with new computer name.

When I tried to login the the old VM, I received below error :

The trust relationship between this workstation and the primary domain failed

image

After goggling the error, I found the solution from here .

The solution is login to the host with the local account and update the domain account password.

NETDOM RESETPWD /Server:<domain controller> /UserD:<domain admin account> /PasswordD:<password>
 
NETDOM RESETPWD /Server:SGC /UserD:PO /PasswordD:XXXXX

0 Setup Kerberos connection to SQL Server

Kerberos is popular security best practice.  In this post, I am going to work through how to setup the Kerberos connection for SQL Server.

Prerequisite:

Windows domain controller setup and have SQL Server on the same domain but in different host.

Domain Controller KDC

Make sure the KDC is running in the DC.

image

Make sure the FQDN is pintable from client

In my lab case, the FQDN is sgc1.stargate.com , We basically need to use the pingable name for the SPN.

If the pingable name is SGC1 only, than the SPN must to use the SGC1.

If the pinable name is SGC1.stargate.com. the SPN must be use SGC1.stargate.com

If both are pingable, than we have to use the FQDN which is SGC1.stargate.com

Tuesday, September 17, 2013

0 SSDT

Install SSDT

The installation is base on the ISO image. I don’t have Visual Studio installed on the host before installing SSDT.

image

image

Once it is installed, you will see the Visual Studio 2012

image

The SSDT coming with localDB, which is the local sql server database ( not express version).

image

Within the SSDT, we only have SQL Server database project for now.

image

Install SSDT-BI

SSDT-BI installation is much more like SQL Server setup .

image

image

image

image

image

Conclusion

After you install both, SSDT and SSDT-BI , you will find the startup menu has been changed. as below :

The additional SQL Server Data Tools for Visual Studio has been added.  I would expect this would be added right after the SSDT install not SSDT-BI.  Both Startup has the BI templates.

image

It turns out.

  1. You only get the “SQL Server Data Tools for visual Studio” when you install SSDT-BI.
  2. You would get Visual Studio 2012 by either install SSDT or SSDT-BI. But the Database project would only be available after you install SSDT.
  3. After you install both, both Visual Studio 2012 and SQL Server Data tools for Visual Studio would have all the projects.

 

I really think this cause quite of confusion, SQL server team should just remove the short cut of Visual Studio 2012 and Use “SQL Server Data Tools for Visual studio” for both product.

 

Reference

http://msdn.microsoft.com/en-us/data/hh297027

0 Disable the Enhance Security in IE

I am using Windows Server as my day to day operation system. One of the security feature on the server is IE would keep pop up the security warming.

image

It is true that we “normally” would not use the server OS for internet browsing  but in my case, this is what I want to be. The way work around it is not to use IE but even so, it is possible other application could trigger that ( such as Windows Live writer ).

0 Change the Windows Live Writer Default Post location

I am using the Windows Live writer to write my blog post. By Default, the post is store under C:\Users\%USERNAME%\Documents\My Weblog Posts

Because I like to use Sky drive to backup my Post, I like to change my default post location to the child directory under the sky drive.

The way to achieve this is to add the additional register key under HKEY_CURRENT_USER\Software\Microsoft\Windows Live\Writer

The Key string name is PostDirectory and the Value is where you want it goes.

image

Once you restart the live writer, it will create 2 child folders , Drafts and Recent Posts.

Thursday, September 12, 2013

1 Import WIM to VMWare

Recently, I have the need to import the WIM image file to VMWare.

Here are the steps how I achieve this.

 

Convert WIM to VHD

This can be easily done by using Convert-WindowsImage.ps1.

The script can be down load from Microsoft ( http://gallery.technet.microsoft.com/scriptcenter/Convert-WindowsImageps1-0fe23a8f ) and only executable on Win8/Win2012 .

C:\Users\PO\Downloads\Convert-WindowsImage.ps1  -SourcePath D:\win7_64.wim -Edition 1

image

At this Point, you get the VHD file and you can open it in Virtual Box or Mount to the disk. But what I really want is to import into VMWare . Hence I need to do another conversion .

Saturday, September 7, 2013

0 SQL Server Performance Dashboard

SQL Server performance dashboard are the step of reports that will give user the overview of the SQL Server performance .

You can download the installer from here.

image

Wednesday, September 4, 2013

0 SQL Server 2012 Sample database

This post is to illustrate how to install sample database for SQL Server 2012.

  • Adventure Work 2012
  • Adventure Work 2012 DW
  • Pubs
  • Northwind

AdventureWorks2012

Down load from here.

image

CREATE DATABASE MyAdventureWorks 
    ON (FILENAME = 'E:\DATA\MSSQL11.MSSQL2012\MSSQL\DATA\AdventureWorks2012_Data.mdf'), 
    (FILENAME = 'E:\DATA\MSSQL11.MSSQL2012\MSSQL\DATA\AdventureWorks2012_log.ldf') 
    FOR ATTACH; 

 

adventure work DW 2012

Down load from here.

image

The file for this database is one single MDF file. so we need to use ATTACH_REBUILD_LOG;

CREATE DATABASE MyAdventureWorksDW
    ON (FILENAME = 'E:\DATA\MSSQL11.MSSQL2012\MSSQL\DATA\AdventureWorksDW2012_Data.mdf')
    FOR ATTACH_REBUILD_LOG; 

Tuesday, September 3, 2013

4 SQL Server 2012 Express includes full SSMS function

SQL Server management Studio is the primary tool that use against SQL Server.

It is not difficult to find the comparison between SQL Server Edition and the Free express version. However, it is not so much discussion for the management tools itself.

We know that the SSMS express version looks like a full version of the SSMS but in fact it actually not the full version and lack many functionality. In order to get the full version of the SSMS, we have to install it from the license media.

Good news is , it is no longer the case with SQL Server 2012 SP1.

With SQL Server 2012 SP1, the full feature of the SSMS is free to download.  Here. The binary name is SQLManagementStudio_x64_ENU.exe. The Size is over 900MB which is significant large than the RTM version here ( 600 MB).

image

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates