THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search


A blog for members of the SQL Server SQLOS team to share information and get your feedback.

  • Lock Pages in Memory - not deprecated in Denali

    Please note an error in the SQL 2012 documentation for Lock Pages in Memory which indicates that it's deprecated. It's not. Locking pages in memory is a useful feature and there are no plans to deprecate it. It looks like it was accidently included on this page when we made changes to deprecate AWE.

    So.. keep using it :-) The documentation has been fixed and should appear in Books Online in the next refresh by mid-April.



    Originally posted at
  • When was sys.dm_os_wait_stats last cleared?

    The sys.dm_os_wait_stats DMV provides essential metrics for diagnosing SQL Server performance problems. Returning incrementally accumulating information about all the completed waits encountered by executing threads it is a useful way to identify bottlenecks such as IO latency issues or waits on locks. The counters are reset each time SQL server is restarted, or when the following command is run:

    DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);

    To make sense out of these wait values you need to know how they change over time. Suppose you are asked to troubleshoot a system and you don't know when the wait stats were last zeroed. Is there any way to find the elapsed time since this happened?

    If the wait stats were not cleared using the DBCC SQLPERF command then you can simply correlate the stats with the time SQL Server was started using the sqlserver_start_time column introduced in SQL Server 2008 R2:

    SELECT sqlserver_start_time from sys.dm_os_sys_info

    However how do you tell if someone has run DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR) since the server was started, and if they did, when? Without this information the initial, or historical, wait_stats have less value until you can measure deltas over time.

    There is a way to at least estimate when the stats were last cleared, by using the wait stats themselves and choosing a thread that spends most of its time sleeping. A good candidate is the SQL Trace incremental flush task, which mostly sleeps (in 4 second intervals) and in between it attempts to flush (if there are new events – which is rare when only default trace is running) – so it pretty much sleeps all the time. Hence the time it has spent waiting is very close to the elapsed time since the counter was reset. Credit goes to Ivan Penkov in the SQLOS dev team for suggesting this.

    Here's an example (excuse formatting):

    144 seconds after the server was started:

    select top 10 wait_type, wait_time_ms from sys.dm_os_wait_stats order by wait_time_ms desc

    wait_type                                                               wait_time_ms

    XE_DISPATCHER_WAIT                                      242273
    LAZYWRITER_SLEEP                                          146010
    LOGMGR_QUEUE                                                145412
    DIRTY_PAGE_POLL                                             145411
    XE_TIMER_EVENT                                               145216
    REQUEST_FOR_DEADLOCK_SEARCH             145194
    SLEEP_TASK                                                        73359
    BROKER_TO_FLUSH                                           73113

    (10 rows affected)


    DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR)"

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    After 8 seconds:

    select top 10 wait_type, wait_time_ms from sys.dm_os_wait_stats order by wait_time_ms desc

    wait_type                                                                 wait_time_ms

    REQUEST_FOR_DEADLOCK_SEARCH              10013
    LAZYWRITER_SLEEP                                           8124
    LOGMGR_QUEUE                                                 7579
    DIRTY_PAGE_POLL                                              7532
    XE_TIMER_EVENT                                                5007
    BROKER_TO_FLUSH                                            4118
    SLEEP_TASK                                                         3089
    SOS_SCHEDULER_YIELD                                   27

    (10 rows affected)


    After 12 seconds:

    select top 10 wait_type, wait_time_ms from sys.dm_os_wait_stats order by wait_time_ms desc

    wait_type                                                                  wait_time_ms

    REQUEST_FOR_DEADLOCK_SEARCH               15020
    LAZYWRITER_SLEEP                                            14206
    LOGMGR_QUEUE                                                  14036
    DIRTY_PAGE_POLL                                               13973
    XE_TIMER_EVENT                                                 10014
    SLEEP_TASK                                                          7207
    BROKER_TO_FLUSH                                             7207
    SOS_SCHEDULER_YIELD                                     28

    (10 rows affected)

    It may not be accurate to the millisecond, but it can provide a useful data point, and give an indication whether the wait stats were manually cleared after startup, and if so approximately when.

    - Guy



    Originally posted at
  • New SQLOS features in SQL Server 2012

    Here's a quick summary of SQLOS feature enhancements going into SQL Server 2012. Most of these are already in the CTP3 pre-release, except for the Resource Governor enhancements which will be in the release candidate. We've blogged about a couple of these items before. I plan to add detail. Let me know which ones you'd like to see more on:

    - Memory Manager Redesign:

    Predictable sizing and governing SQL memory consumption:

    sp_configure ‘max server memory’ now limits all memory committed by SQL Server
    Resource Governor governs all SQL memory consumption (other than special cases like buffer pool)

    Improved scalability of complex queries and operations that make >8K allocations

    Improved CPU and NUMA locality for memory accesses

    Single memory manager that handles page allocations of all sizes

    Consistent Out-of-memory handling & management across different internal components

    - Optimized Memory Broker for Column Store indexes (Project Apollo)

    - Resource Governor

    Support larger scale multi-tenancy by increasing Max. number of resource pools20 -> 64 [for 64-bit]

    Enable predictable chargeback and isolation by adding a hard cap on CPU usage

    Enable vertical isolation of machine resources

    Resource pools can be affinitized to individual or groups of schedulers or to NUMA nodes

    New DMV for resource pool affinity

     - CLR 4 support, adds .NET Framework 4 advantages

    - sp_server_dianostics

    Captures diagnostic data and health information about SQL Server to detect potential failures
    Analyze internal system state

    Reliable when nothing else is working

    - New SQLOS DMVs (in 2008 R2SP1)

    SQL Server related configuration - New DMV

    OS related resource configuration
    New DMVs

    XEvents for SQL and OS related Perfmon counters

    Extend sys.dm_os_sys_info

    See previous blog posts here and here.

    - Scale / Mission critical

    Increased scalability: Support Windows 8 max memory and logical processors

    Dynamic Memory support in Standard Edition - Hot-Add Memory enabled when virtualized

    - Various Tier1 Performance Improvements, including reduced instructions for superlatches.

    Originally posted at
  • SQL Server with Hyper-V Dynamic Memory - Best Practices white paper

    A new Microsoft white paper has been published entitled Running SQL Server with Hyper-V Dynamic Memory - Best Practices and Considerations. This paper, based on performance testing in the SQL Cat teams labs, provides advice on best practices for SQL Server running with Hyper-V Dynamic Memory, and is recommended reading for anyone considering the cost savings of deploying SQL Server in a virtualized environment which implements Dynamic Memory.

    You can download the white paper here.


    Originally posted at
  • SQL Server Virtualization, Consolidation and Private Cloud Resources

    We've been seeing a lot of interest in optimizing SQL Server for virtualization private cloud deployment lately. Below is an attempt to put useful links to recent SQL Server virtualizaiton, consolidation and private cloud resources in one place, divided into a range of topics such as benefits, comparisons, products and tools, planning and what to consider. These originally appeared in the TechEd breakout session: Microsoft SQL Server in Virtualization in Private Cloud:

    Topic Resource (Why consolidate)

    Benefits on SQL Server consolidation/virtualization

    SQL Server 2008 R2 Consolidation Datasheet

    SQL Server 2008 Consolidation Whitepaper

    SQL Server 2008 R2 Virtualization Datasheet

    SQL Server 2008 R2 Licensing and website

    Support policy for SQL Server virtualization
    SQL Server Consolidation Guidance

    Topic Resource (Comparison)


    Cost Comparison Calculator

    Hyper-V Calculator

    Why Hyper-V for existing VMware customers?

    Hyper-V vs. VMware operational cost comparison

    Hyper-V vs. VMware cost comparison white paper

    Hyper-V vs. VMware cost comparison video

    Hyper-V vs. VMware ongoing cost comparison

    Debunk VMware cost calculator from Taneja Group

    Hyper-V is the best virtualization for SQL Server

    Topic Resource (How to plan and deploy



    Windows Server Virtualization Guide

    Hyper-V VM Processor Compatibility


    Partner resources


    SQL Server Consolidation on IBM x3850 servers

    NetApp SQL Server Consolidation/Virtualization




    Consolidation Planning Tool for SQL Server

    MAP Toolkit

    Offline Virtual Machine Servicing Tool 2.1


    Plan and deploy

    Consolidation using SQL Server 2008

    SQL Server Virtualization with Hyper-V

    Managing SQL Server Virtual Appliances

    Analysis Services Consolidation Guidance

    Onboarding SQL Server Private Cloud Environment

    Topic Resource (What to consider)



    Hyper-V Security Guide




    Hyper-V Live Migration Architecture


    Resource management


    Server Memory Options

    Affinity mask Option

    Resource Governor Whitepaper

    Managing SQL Server with Resource Governor

    Support Statement for Dynamic Memory in SQL Server Virtualization




    System Center VMM Self-Service Portal 2.0

    P2V in System Center VMM

    Virtualization with Hyper-V

    How to: Extract a DAC


    Storage requirements


    Data Compression Strategy for SQL Server 2008

    SQL Server Compression Datasheet

    SQL Server Compression Whitepaper

    Unicode compression in SQL Server 2008 R2

    Pre-deployment I/O Best Practices

    Disk Alignment Best Practices for SQL Server


    Performance and scalability

    SQL Server in Hyper-V Best Practice

    High Performance SQL Server on Hyper-V

    Virtual Hard Disk Performance White Paper

    Hyper-V R2 Performance By ESG

    Hyper-V R2 SP1 Workload Performance By ESG

    Any recommendations? Let me know.

    - Guy

    Originally posted at
  • More on DMV changes in SQL 2008 R2 SP1

    Following our last post by Mehmet about new support and troubleshooting DMV’s, here are additional details on supportability DMV changes in SQL 2008 R2 SP1 and how you might use them. These changes are also available in “Denali”, the upcoming major release of SQL Server. With SQL 2008 R2 SP1 coming up soon, please give them a try and let us know what you think.

    We’ll take a peek at these DMV changes as well as talk about in what circumstances these DMVs would come in handy. Note that all DMVs/DMF documented below require VIEW SERVER STATE permission.

    1. Extended sys.dm_exec_query_stats with 4 new columns (total/last/min/max_row)

    sys.dm_exec_query_stats is a very widely used DMV that provides  useful information in analyzing query performance. To troubleshoot long running queries, it is also helpful to have total/min/max/last row counts information in order to separate queries that are simply returning a large number of rows from those problematic ones due to, say, a missing index or  a bad query plan.

     2. sys.dm_os_volume_stats(f.database_id, f.file_id)

    This is a new DMF (Dynamic Management Function) that helps check the free space on the partitions the SQL server instance resides on. The catalog view sys.database_files provides stats such as size per database file, however, without information about free space on the partition, the information is less actionable – a database file cannot autogrow even when the size is small if there is not sufficient space left on the partition.

    For example, you can use the following T-SQL statements to get the stats for the current database.

    select database_id, f.file_id, total_bytes, available_bytes from sys.database_files as f cross apply sys.dm_os_volume_stats(DB_ID(), f.file_id)

    DMF definition:





    int, not null

    ID of the database


    int, not null

    ID of the file



    Mount point at which the volume is rooted



    OS volume ID



    Logical volume name



    Type of file system volume (e.g., NTFS, FAT, RAW)



    Total size in bytes of the volume



    Available free space on the volume



    Does this volume support OS compression?



    Does this volume support alternate streams



    Does this volume support sparse files?



    Is this volume currently marked read_only?



    Is this volume currently compressed?

    3. sys.dm_os_windows_info

    This new DMV provides information on the OS the SQL Server instance is running on, specifically including the following:

    • Windows Release
    • Windows Service Pack Level
    • Windows SKU
    • OS language version

    DMV definition:

    Column name

    Column type


    nvarchar (256)







     Without this DMV, it is very difficult to get such information without calling into Windows APIs.

    It’s also worth pointing out that a related change we made to existing DMV sys.dm_os_sys_info is to add two new columns (virtual_machine_type, virtual_machine_type_desc) to provide information when the SQL Server instance is running in a virtual machine environment.

     4. sys.dm_server_registry

    This DMV provides registry key information related to the overall configuration/installation of the SQL Server instance.

    DMV definition:

    Column name

    Column type


    nvarchar (256)


    Nvarchar (256)



     The following groups of registry keys are covered in this DMV:

    1)      To help find out what SQL Server services are available on the host machine

    • HKLM\SYSTEM\CurrentControlSet\Services\MSSQLServer\ObjectName
    • HKLM\SYSTEM\CurrentControlSet\Services\MSSQLServer\ImagePath
    • HKLM\SYSTEM\CurrentControlSet\Services\MSSQLServer\Start

    2)      To help find out what SQL Agent services are available on the host machine

    • HKLM\SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT\ObjectName
    • HKLM\SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT\ImagePath
    • HKLM\SYSTEM\CurrentControlSet\Services\ SQLSERVERAGENT\Start
    • HKLM\SYSTEM\CurrentControlSet\Services\ SQLSERVERAGENT\DependOnService

    3)      To find out the current version of SQL Engine

    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ CurrentVersion

    4)      To help with instance detectability, etc. the following registry keys are helpful

    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ Parameters

    5)      To help troubleshoot connectivity issues (intermittent connections, high latency, etc.) that may be a result of misconfiguration

    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ SuperSocketNetLib\ AdminConnection\TCP
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ SuperSocketNetLib\ Np
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ SuperSocketNetLib\ Sm
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ SuperSocketNetLib\ TCP\ IP1… IPAll
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ MSSQLServer\ SuperSocketNetLib\ Via

    6)      To help troubleshoot application issues due to incorrect settings in SQLServerAgent

    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ SQLServerAgent\ErrorLoggingLevel
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ SQLServerAgent\JobHistoryMaxRows
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ SQLServerAgent\JobHistoryMaxRowsPerJob
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLxx.MSSQLSERVER\ SQLServerAgent\WorkingDirectory

     5. sys.dm_server_services

    This DMV provides information about three services related to SQL Server, namely SQL Server, SQL Agent and Fulltext (only available as of Denali). The DMV includes the following information about the current SQL instance:

    Column name

    Column type


    nvarchar (256)










    nvarchar (256)




    nvarchar (256)


    nvarchar (256)




    nvarchar (256)

    6. sys.dm_server_memory_dumps

    This DMV provides information on memory dumps that have been generated as a result of a recent crash, for instance. Dump type may be minidump, all-thread dump or full dump.

    DMV definition:

    Column name

    Column type


    nvarchar (256)






    Thanks for reading this far. We’d love to hear your feedback once you get a chance to try them out.


    -Xin Jin

    Originally posted at
  • New DMVs in Denali

    This article is about some of the new DMV’s that are newly added to next version of SQL Server. These are also available SQL Server 2008 R2 SP1. They are very helpful to get information about the host operating system that the SQL Server runs on, status of the server and even the memory dumps that are created by the server and registry keys that the server uses.

    To read all mentioned DMV’s the user must have VIEW SERVER STATE permission.

    sys.dm_os_windows_info:  This DMV returns the OS related information to the user. It is good to know the properties of hosting server.


    • windows_release:
    • windows_service_pack
    • windows_sku
    • os_language_version

    sys.dm_server_registry:  This DMV returns some of the registry keys that the server uses.


    • registry key
    • value_name
    • value_data        

    sys.dm_server_services: This DMV provides useful information about the SQL Server, SQL Agent and SQL FullText service related with the running server. This DMV is very helpful to track the servers status and startup configuration.


    • service_name
    • startup type
    • startup_type_desc
    • status
    • status_type_desc
    • process_id
    • last_startup_time
    • service_account
    • filename
    • is_clustered
    • cluster_nodename

     sys.dm_server_memory_dumps: This DMV returns the dumps that are generated by the server.


    • filename:
    • size_in_bytes
    • creation_time

     If you need more detailed information on any of those, please reply to this post or send email

    Originally posted at
  • SQL Server and Hyper-V Dynamic Memory Part 3

    In parts 1 and 2 of this series we looked at the basics of Hyper-V Dynamic Memory and SQL Server memory management. In this part Serdar looks at configuration guidelines for SQL Server memory management.

    Update 8/2/11: please also refer to the recently published white paper: Running SQL Server with Hyper-V Dynamic Memory - Best Practices and Considerations.

    Part 3: Configuration Guidelines for Hyper-V Dynamic Memory and SQL Server

    Now that we understand SQL Server Memory Management and Hyper-V Dynamic Memory basics, let’s take a look at general configuration guidelines in order to utilize benefits of Hyper-V Dynamic Memory in your SQL Server VMs.


    Host Operating System Requirements

    Hyper-V Dynamic Memory feature is introduced with Windows Server 2008 R2 SP1. Therefore in order to use Dynamic Memory for your virtual machines, you need to have Windows Server 2008 R2 SP1 or Microsoft Hyper-V Server 2008 R2 SP1 in your Hyper-V host.

    Guest Operating System Requirements

    In addition to this Dynamic Memory is only supported in Standard, Web, Enterprise and Datacenter editions of windows running inside VMs. Make sure that your VM is running one of these editions. For additional requirements on each operating system see “Dynamic Memory Configuration Guidelines” here.

    SQL Server Requirements

    All versions of SQL Server support Hyper-V Dynamic Memory. However, only certain editions of SQL Server are aware of dynamically changing system memory. To have a truly dynamic environment for your SQL Server VMs make sure that you are running one of the SQL Server editions listed below:

    ·         SQL Server 2005 Enterprise

    ·         SQL Server 2008 Enterprise / Datacenter Editions

    ·         SQL Server 2008 R2 Enterprise / Datacenter Editions

    Configuration guidelines for other versions of SQL Server are covered below in the FAQ section.

    Guidelines for configuring Dynamic Memory Parameters

    Here is how to configure Dynamic Memory for your SQL VMs in a nutshell:

    Hyper-V Dynamic Memory Parameter


    Startup RAM

    1 GB + SQL Min Server Memory

    Maximum RAM

    > SQL Max Server Memory

    Memory Buffer %


    Memory Weight

    Based on performance needs


    Startup RAM

    In order to ensure that your SQL Server VMs can start correctly, ensure that Startup RAM is higher than configured SQL Min Server Memory for your VMs. Otherwise SQL Server service will need to do paging in order to start since it will not be able to see enough memory during startup.

    Also note that Startup Memory will always be reserved for your VMs. This will guarantee a certain level of performance for your SQL Servers, however setting this too high will limit the consolidation benefits you’ll get out of your virtualization environment.

    Maximum RAM

    This one is obvious. If you’ve configured SQL Max Server Memory for your SQL Server, make sure that Dynamic Memory Maximum RAM configuration is higher than this value. Otherwise your SQL Server will not grow to memory values higher than the value configured for Dynamic Memory.

    Memory Buffer %

    Memory buffer configuration is used to provision file cache to virtual machines in order to improve performance. Due to the fact that SQL Server is managing its own buffer pool, Memory Buffer setting should be configured to the lowest value possible, 5%. Configuring a higher memory buffer will prevent low resource notifications from Windows Memory Manager and it will prevent reclaiming memory from SQL Server VMs.

    Memory Weight

    Memory weight configuration defines the importance of memory to a VM. Configure higher values for the VMs that have higher performance requirements. VMs with higher memory weight will have more memory under high memory pressure conditions on your host.

    Questions and Answers

    Q1 – Which SQL Server memory model is best for Dynamic Memory?

    The best SQL Server model for Dynamic Memory is “Locked Page Memory Model”. This memory model ensures that SQL Server memory is never paged out and it’s also adaptive to dynamically changing memory in the system. This will be extremely useful when Dynamic Memory is attempting to remove memory from SQL Server VMs ensuring no SQL Server memory is paged out.

    You can find instructions on configuring “Locked Page Memory Model” for your SQL Servers here.

    Q2 – What about other SQL Server Editions, how should I configure Dynamic Memory for them?

    Other editions of SQL Server do not adapt to dynamically changing environments. They will determine how much memory they should allocate during startup and don’t change this value afterwards. Therefore make sure that you configure a higher startup memory for your VM because that will be all the memory that SQL Server utilize

    Tune Maximum Memory and Memory Buffer based on the other workloads running on the system. If there are no other workloads consider using Static Memory for these editions.

    Q3 – What if I have multiple SQL Server instances in a VM?

    Having multiple SQL Server instances in a VM is not a general recommendation for predictable performance, manageability and isolation. In order to achieve a predictable behavior make sure that you configure SQL Min Server Memory and SQL Max Server Memory for each instance in the VM. And make sure that:

    ·         Dynamic Memory Startup Memory is greater than the sum of SQL Min Server Memory values for the instances in the VM

    ·         Dynamic Memory Maximum Memory is greater than the sum of SQL Max Server Memory values for the instances in the VM

    Q4 – I’m using Large Page Memory Model for my SQL Server. Can I still use Dynamic Memory?

    The short answer is no. SQL Server does not dynamically change its memory size when configured with Large Page Memory Model. In virtualized environments Hyper-V provides large page support by default. Most of the time, Large Page Memory Model doesn’t bring any benefits to a SQL Server if it’s running in virtualized environments.

    Q5 – How do I monitor SQL performance when I’m trying Dynamic Memory on my VMs?

    Use the performance counters below to monitor memory performance for SQL Server:

    Process - Working Set: This counter is available in the VM via process performance counters. It represents the actual amount of physical memory being used by SQL Server process in the VM.

    SQL Server – Buffer Cache Hit Ratio: This counter is available in the VM via SQL Server counters. This represents the paging being done by SQL Server. A rate of 90% or higher is desirable.


    These blog posts are a quick start to a story that will be developing more in the near future. We’re still continuing our testing and investigations to provide more detailed configuration guidelines with example performance numbers with a white paper in the upcoming months.

    Now it’s time to give SQL Server and Hyper-V Dynamic Memory a try. Use this guidelines to kick-start your environment. See what you think about it and let us know of your experiences.

    - Serdar Sutay

    Originally posted at
  • SQL Server and Hyper-V Dynamic Memory Part 2

    Part 1 of this series was an introduction and overview of Hyper-V Dynamic Memory. This part looks at SQL Server memory management and how the SQL engine responds to changing OS memory conditions.


    Part 2: SQL Server Memory Management

    As with any Windows process, sqlserver.exe has a virtual address space (VAS) of 4GB on 32-bit and 8TB in 64-bit editions. Pages in its VAS are mapped to pages in physical memory when the memory is committed and referenced for the first time. The collection of VAS pages that have been recently referenced is known as the Working Set.


    How and when SQL Server allocates virtual memory and grows its working set depends on the memory model it uses. SQL Server supports three basic memory models:


    1. Conventional Memory Model


    The Conventional model is the default SQL Server memory model and has the following properties:

    - Dynamic - can grow or shrink its working set in response to load and external (operating system) memory conditions.

    - OS uses 4K pages – (not to be confused with SQL Server “pages” which are 8K regions of committed memory).

    - Pageable
    - Can be paged out to disk by the operating system.


    2. Locked Page Model

    The locked page memory model is set when SQL Server is started with "Lock Pages in Memory" privilege*. It has the following characteristics:

    - Dynamic - can grow or shrink its working set in the same way as the Conventional model.

    - OS uses 4K pages

    - Non-Pageable – When memory is committed it is locked in memory, meaning that it will remain backed by physical memory and will not be paged out by the operating system.

    A common misconception is to interpret "locked" as non-dynamic. A SQL Server instance using the locked page memory model will grow and shrink (allocate memory and release memory) in response to changing workload and OS memory conditions in the same way as it does with the conventional model.


    This is an important consideration when we look at Hyper-V Dynamic Memory – “locked” memory works perfectly well with “dynamic” memory.


    * Note in “Denali” (Standard Edition and above), and in SQL 2008 R2 64-bit (Enterprise and above editions) the Lock Pages in Memory privilege is all that is required to set this model. In 2008 R2 64-Bit standard edition it also requires trace flag 845 to be set, in 2008 R2 32-bit editions it requires sp_configure 'awe enabled' 1.


    3. Large Page Model

    The Large page model is set using trace flag 834 and potentially offers a small performance boost for systems that are configured with large pages. It is characterized by:

    - Static - memory is allocated at startup and does not change.

    - OS uses large (>2MB) pages

    - Non-Pageable

    The large page model is supported with Hyper-V Dynamic Memory (and Hyper-V also supports large pages), but you get no benefit from using Dynamic Memory with this model since SQL Server memory does not grow or shrink. The rest of this article will focus on the locked and conventional SQL Server memory models.


    When does SQL Server grow?

    For “dynamic” configurations (Conventional and Locked memory models), the sqlservr.exe process grows – allocates and commits memory from the OS – in response to a workload. As much memory is allocated as is required to optimally run the query and buffer data for future queries, subject to limitations imposed by:


    - SQL Server max server memory setting. If this configuration option is set, the buffer pool is not allowed to grow to more than this value. In SQL Server 2008 this value represents single page allocations, and in “Denali” it represents any size page allocations and also managed CLR procedure allocations.


    - Memory signals from OS. The operating system sets a signal on memory resource notification objects to indicate whether it has memory available or whether it is low on available memory. If there is only 32MB free for every 4GB of memory a low memory signal is set, which continues until 64MB/4GB is free. If there is 96MB/4GB free the operating system sets a high memory signal. SQL Server only allocates memory when the high memory signal is set.


    To summarize, for SQL Server to grow you need three conditions: a workload, max server memory setting higher than the current allocation, high memory signals from the OS. 


    When does SQL Server shrink caches?

    SQL Server as a rule does not like to return memory to the OS, but it will shrink its caches in response to memory pressure. Memory pressure can be divided into “internal” and “external”.


    - External memory pressure occurs when the operating system is running low on memory and low memory signals are set. The SQL Server Resource Monitor checks for low memory signals approximately every 5 seconds and it will attempt to free memory until the signals stop.


    To free memory SQL Server does the following:

    ·         Frees unused memory.

    ·         Notifies Memory Manager Clients to release memory

    o   Caches – Free unreferenced cache objects.

    o   Buffer pool - Based on oldest access times.


    The freed memory is released back to the operating system. This process continues until the low memory resource notifications stop.


    - Internal memory pressure occurs when the size of different caches and allocations increase but the SQL Server process needs to keep its total memory within a target value. For example if max server memory is set and certain caches are growing large, it will cause SQL to free memory for re-use internally, but not to release memory back to the OS. If you lower the value of max server memory you will generate internal memory pressure that will cause SQL to release memory back to the OS. 


    Memory pressure handling has not changed much since SQL 2005 and it was described in detail in a blog post by Slava Oks.


    Note that SQL Server Express is an exception to the above behavior. Unlike other editions it does not assume it is the most important process running on the system but tries to be more “desktop” friendly. It will empty its working set after a period of inactivity.


    How does SQL Server respond to changing OS memory? 


    In SQL Server 2005 support for Hot-Add memory was introduced. This feature, available in Enterprise and above editions, allows the server to make use of any extra physical memory that was added after SQL Server started. Being able to add physical memory when the system is running is limited to specialized hardware, but with the Hyper-V Dynamic Memory feature, when new memory is allocated to a guest virtual machine, it looks like hot-add physical memory to the guest. What this means is that thanks to the hot-add memory feature, SQL Server 2005 and higher can dynamically grow if more “physical” memory is granted to a guest VM by Hyper-V dynamic memory.


    SQL Server checks OS memory every second and dynamically adjusts its “target” (based on available OS memory and max server memory) accordingly.


    In “Denali” Standard Edition will also have sqlserver.exe support for hot-add memory when running virtualized (i.e. detecting and acting on Hyper-V Dynamic Memory allocations).


    How does a SQL Server workload in a guest VM impact Hyper-V dynamic memory scheduling?


    When a SQL workload causes the sqlserver.exe process to grow its working set, the Hyper-V memory scheduler will detect memory pressure in the guest VM and add memory to it. SQL Server will then detect the extra memory and grow according to workload demand. In our tests we have seen this feedback process cause a guest VM to grow quickly in response to SQL workload - we are still working on characterizing this ramp-up. 


    How does SQL Server respond when Hyper-V removes memory from a guest VM through ballooning?


    If pressure from other VM's cause Hyper-V Dynamic Memory to take memory away from a VM through ballooning (allocating memory with a virtual device driver and returning it to the host OS), Windows Memory Manager will page out unlocked portions of memory and signal low resource notification events. When SQL Server detects these events it will shrink memory until the low memory notifications stop (see cache shrinking description above). 


    This raises another question. Can we make SQL Server release memory more readily and hence behave more "dynamically" without compromising performance? In certain circumstances where the application workload is predictable it may be possible to have a job which varies "max server memory" according to need, lowering it when the engine is inactive and raising it before a period of activity. This would have limited applicaability but it is something we're looking into.


    What Memory Management changes are there in SQL Server “Denali”?


    In SQL Server “Denali” (aka SQL11) the Memory Manager has been re-written to be more efficient. The main changes are summarized in this post. An important change with respect to Hyper-V Dynamic Memory support is that now the max server memory setting includes any size page allocations and managed CLR procedure allocations it now represents a closer approximation to total sqlserver.exe memory usage. This makes it easier to calculate a value for max server memory, which becomes important when configuring virtual machines to work well with Hyper-V Dynamic Memory Startup and Maximum RAM settings.


    Another important change is no more AWE or hot-add support for 32-bit edition. This means if you're running a 32-bit edition of Denali you're limited to a 4GB address space and will not be able to take advantage of dynamically added OS memory that wasn't present when SQL Server started (though Hyper-V Dynamic Memory is still a supported configuration).


    In part 3 we’ll develop some best practices for configuring and using SQL Server with Dynamic Memory.


    - Guy

    Originally posted at
  • SQL Server and Hyper-V Dynamic Memory - Part 1

    SQL and Dynamic Memory Blog Post Series


    Hyper-V Dynamic Memory is a new feature in Windows Server 2008 R2 SP1 that allows the memory assigned to guest virtual machines to vary according to demand. Using this feature with SQL Server is supported, but how well does it work in an environment where available memory can vary dynamically, especially since SQL Server likes memory, and is not very eager to let go of it? The next three posts will look at this question in detail.

    In Part 1 Serdar Sutay, a program manager in the Windows Hyper-V team, introduces Dynamic Memory with an overview of the basic architecture, configuration and monitoring concepts. In subsequent parts we will look at SQL Server memory handling, and develop some guidelines on using SQL Server with Dynamic Memory.


    Part 1: Dynamic Memory Introduction


    In virtualized environments memory is often the bottleneck for reaching higher VM densities. In Windows Server 2008 R2 SP1 Hyper-V introduced a new feature “Dynamic Memory” to improve VM densities on Hyper-V hosts. Dynamic Memory increases the memory utilization in virtualized environments by enabling VM memory to be changed dynamically when the VM is running.


    This brings up the question of how to utilize this feature with SQL Server VMs as SQL Server performance is very sensitive to the memory being used. In the next three posts we’ll discuss the internals of Dynamic Memory, SQL Server Memory Management and how to use Dynamic Memory with SQL Server VMs.


    Memory Utilization Efficiency in Virtualized Environments


    The primary reason memory is usually the bottleneck for higher VM densities is that users tend to be generous when assigning memory to their VMs. Here are some memory sizing practices we’ve heard from customers:


    ·         I assign 4 GB of memory to my VMs. I don’t know if all of it is being used by the applications but no one complains.

    ·         I take the minimum system requirements and add 50% more.

    ·         I go with the recommendations provided by my software vendor.


    In reality correctly sizing a virtual machine requires significant effort to monitor the memory usage of the applications. Since this is not done in most environments, VMs are usually over-provisioned in terms of memory. In other words, a SQL Server VM that is assigned 4 GB of memory may not need to use 4 GB.


    How does Dynamic Memory help?


    Dynamic Memory improves the memory utilization by removing the requirement to determine the memory need for an application. Hyper-V determines the memory needed by applications in the VM by evaluating the memory usage information in the guest with Dynamic Memory. VMs can start with a small amount of memory and they can be assigned more memory dynamically based on the workload of applications running inside.


    Overview of Dynamic Memory Concepts


    ·         Startup Memory: Startup Memory is the starting amount of memory when Dynamic Memory is enabled for a VM. Dynamic Memory will make sure that this amount of memory is always assigned to the VMs by default.  

    ·         Maximum Memory: Maximum Memory specifies the maximum amount of memory that a VM can grow to with Dynamic Memory.

    ·         Memory Demand: Memory Demand is the amount determined by Dynamic Memory as the memory needed by the applications in the VM. In Windows Server 2008 R2 SP1, this is equal to the total amount of committed memory of the VM.

    ·         Memory Buffer: Memory Buffer is the amount of memory assigned to the VMs in addition to their memory demand to satisfy immediate memory requirements and file cache needs.



    Once Dynamic Memory is enabled for a VM, it will start with the “Startup Memory”. After the boot process Dynamic Memory will determine the “Memory Demand” of the VM. Based on this memory demand it will determine the amount of “Memory Buffer” that needs to be assigned to the VM. Dynamic Memory will assign the total of “Memory Demand” and “Memory Buffer” to the VM as long as this value is less than “Maximum Memory” and as long as physical memory is available on the host.


    What happens when there is not enough physical memory available on the host?


    Once there is not enough physical memory on the host to satisfy VM needs, Dynamic Memory will assign less than needed amount of memory to the VMs based on their importance. A concept known as “Memory Weight” is used to determine how much VMs should be penalized based on their needed amount of memory. “Memory Weight” is a configuration setting on the VM. It can be configured to be higher for the VMs with high performance requirements. Under high memory pressure on the host, the “Memory Weight” of the VMs are evaluated in a relative manner and the VMs with lower relative “Memory Weight” will be penalized more than the ones with higher “Memory Weight”.


    Dynamic Memory Configuration


    Based on these concepts “Startup Memory”, “Maximum Memory”, “Memory Buffer” and “Memory Weight” can be configured as shown below in Windows Server 2008 R2 SP1 Hyper-V Manager. Memory Demand is automatically calculated by Dynamic Memory once VMs start running.



    Dynamic Memory Monitoring


    In Windows Server 2008 R2 SP1, Hyper-V Manager displays the memory status of VMs in the following three columns:





    ·         Assigned Memory represents the current physical memory assigned to the VM. In regular conditions this will be equal to the sum of “Memory Demand” and “Memory Buffer” assigned to the VM. When there is not enough memory on the host, this value can go below the Memory Demand determined for the VM.

    ·         Memory Demand displays the current “Memory Demand” determined for the VM.

    ·         Memory Status displays the current memory status of the VM. This column can represent three values for a VM:

    o   OK: In this condition the VM is assigned the total of Memory Demand and Memory Buffer it needs.

    o   Low: In this condition the VM is assigned all the Memory Demand and a certain percentage of the Memory Buffer it needs.

    o   Warning: In this condition the VM is assigned a lower memory than its Memory Demand. When VMs are running in this condition, it’s likely that they will exhibit performance problems due to internal paging happening in the VM.


    So far so good! But how does it work with SQL Server?


    SQL Server is aggressive in terms of memory usage for good reasons. This raises the question: How do SQL Server and Dynamic Memory work together?

    To understand the full story, we’ll first need to understand how SQL Server Memory Management works. This will be covered in our second post in “SQL and Dynamic Memory” series.

    Meanwhile if you want to dive deeper into Dynamic Memory you can check the below posts from the Windows Virtualization Team Blog:






    - Serdar Sutay 


    Originally posted at
  • What causes Multi-Page allocations?

    Writing about changes in the Denali Memory Manager In his last post Rusi mentioned: " In previous SQL versions only the 8k allocations were limited by the ‘max server memory’ configuration option.  Allocations larger than 8k weren’t constrained."

    In SQL Server versions before Denali single page allocations and multi-Page allocations are handled by different components, the Single Page Allocator (which is responsible for Buffer Pool allocations and governed by 'max server memory') and the Multi-Page allocator (MPA) which handles allocations of greater than an 8K page. If there are many multi-page allocations this can affect how much memory needs to be reserved outside 'max server memory' which may in turn involve setting the -g memory_to_reserve startup parameter. We'll follow up with more generic articles on the new Memory Manager structure, but in this post I want to clarify what might cause these larger allocations.

    So what kinds of query result in MPA activity? I was asked this question the other day after delivering an MCM webcast on Memory Manager changes in Denali. After asking around our Dev team I was connected to one of our test leads Sangeetha who had tested the plan cache, and kindly provided this example of an MPA intensive query:

    A workload that has stored procedures with a large # of parameters (say > 100, > 500), and then invoked via large ad hoc batches, where each SP has different parameters will result in a plan being cached for this “exec proc” batch. This plan will result in MPA.


    Exec proc_name @p1, ….@p500

    Exec proc_name @p1, ….@p500




    Exec proc_name @p1, ….@p500



    Another workload would be large adhoc batches of the form:

    Select * from t where col1 in (1, 2, 3, ….500)

    Select * from t where col1 in (1, 2, 3, ….500)

    Select * from t where col1 in (1, 2, 3, ….500)


    In Denali all page allocations are handled by an "Any size page allocator" and included in 'max server memory'. The buffer pool effectively becomes a client of the any size page allocator, which in turn relies on the memory manager.

    - Guy

    Originally posted at
  • SQL Server Memory Manager Changes in Denali

    The next version of SQL Server will contain significant changes to the memory manager component.  The memory manager component has been rewritten for Denali.  In the previous versions of SQL Server there were two distinct memory managers.  There was one memory manager which handled allocation sizes of 8k or less and another for greater than 8k.  For Denali there will be one memory manager for all allocation sizes.


    The majority of the changes will be transparent to the end user.  However, some changes will be visible to the user.  These are listed below:

    ·         The ‘max server memory’ configuration option has new lower limits.  Specifically, 32-bit versions of SQL Server will have a lower limit of 64 MB.  The 64-bit versions will have a lower limit of 128 MB.

    ·         All memory allocations by SQL Server components will observe the ‘max server memory’ configuration option.  In previous SQL versions only the 8k allocations were limited the ‘max server memory’ configuration option.  Allocations larger than 8k weren’t constrained.

    ·         DMVs which refer to memory manager internals have been modified.  This includes adding or removing columns and changing column names.

    ·         The memory manager configuration messages in the error log have minor changes.

    ·         DBCC memorystatus output has been changed.

    ·         Address Windowing Extensions (AWE) has been deprecated.


    In the next blog post I will discuss the changes to the memory manager DMVs in greater detail.  In future blog posts I will discuss the other changes in greater detail.


  • Better documentation for tasks waiting on resources

    The sys.dm_os_waiting_tasks DMV contains a wealth of useful information about tasks waiting on a resource, but until now detailed information about the resource being consumed - sys.dm_os_waiting_tasks.resource_description - hasn't been documented, apart from a rather self-evident "Description of the resource that is being consumed."


    Thanks to a recent Connect suggestion this column will get more information added. Here is a summary of the possible values that can appear in this column - Note this information is current for SQL Server 2008 R2 and Denali:


    Thread-pool resource owner:
    •       threadpool id=scheduler<hex-address>

    Parallel query resource owner:
    •       exchangeEvent id={Port|Pipe}<hex-address> WaitType=<exchange-wait-type> nodeId=<exchange-node-id>

    Exchange-wait-type can be one of the following.
    •       e_waitNone
    •       e_waitPipeNewRow
    •       e_waitPipeGetRow
    •       e_waitSynchronizeConsumerOpen
    •       e_waitPortOpen
    •       e_waitPortClose
    •       e_waitRange

    Lock resource owner:
    <type-specific-description> id=lock<lock-hex-address> mode=<mode> associatedObjectId=<associated-obj-id>
    <type-specific-description> can be:
    • For DATABASE: databaselock subresource=<databaselock-subresource> dbid=<db-id>
    • For FILE: filelock fileid=<file-id> subresource=<filelock-subresource> dbid=<db-id>
    • For OBJECT: objectlock lockPartition=<lock-partition-id> objid=<obj-id> subresource=<objectlock-subresource> dbid=<db-id>
    • For PAGE: pagelock fileid=<file-id> pageid=<page-id> dbid=<db-id> subresource=<pagelock-subresource>
    • For Key: keylock  hobtid=<hobt-id> dbid=<db-id>
    • For EXTENT: extentlock fileid=<file-id> pageid=<page-id> dbid=<db-id>
    • For RID: ridlock fileid=<file-id> pageid=<page-id> dbid=<db-id>
    • For APPLICATION: applicationlock hash=<hash> databasePrincipalId=<role-id> dbid=<db-id>
    • For METADATA: metadatalock subresource=<metadata-subresource> classid=<metadatalock-description> dbid=<db-id>
    • For HOBT: hobtlock hobtid=<hobt-id> subresource=<hobt-subresource> dbid=<db-id>
    • For ALLOCATION_UNIT: allocunitlock hobtid=<hobt-id> subresource=<alloc-unit-subresource> dbid=<db-id>

    <mode> can be:
    • Sch-S
    • Sch-M
    • S
    • U
    • X
    • IS
    • IU
    • IX
    • SIU
    • SIX
    • UIX
    • BU
    • RangeS-S
    • RangeS-U
    • RangeI-N
    • RangeI-S
    • RangeI-U
    • RangeI-X
    • RangeX-S
    • RangeX-U
    • RangeX-X

    External resource owner:
    •       External ExternalResource=<wait-type>

    Generic resource owner:
    •       TransactionMutex TransactionInfo Workspace=<workspace-id>
    •       Mutex
    •       CLRTaskJoin
    •       CLRMonitorEvent
    •       CLRRWLockEvent
    •       resourceWait

    Latch resource owner:
    •       <db-id>:<file-id>:<page-in-file>
    •       <GUID>
    •       <latch-class> (<latch-address>)


    Further Information

    Slava Oks's weblog: sys.dm_os_waiting_tasks. Identifying Blocking Using sys.dm_os_waiting_tasks - Ken Henderson


    - Guy

More Posts « Previous page
Privacy Statement