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…

2

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…

4

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…

1

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…

1

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…

1

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…


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…

0

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…

0

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…

6

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…

0