sql server amnesia · •windows 2003 •sql server 2005 •8g ram, 6g max server memory •awe,...

SQL Server Amnesia

Or, my Page Life Expectancy has fallen, and I can’t get a Memory

Grant!

© Steven Ormrod 2011, All Rights Reserved.

Photo Credit: http://www.flickr.com/photos/nizger/4795628177/

Bio

• Database Administrator for large, international food retailer

• Manage hundreds of SQL Servers

• Clustering / Consolidation / Virtualization to reduce the server footprint

• Software Developer in a previous life


Outline

• My goal is not tell you everything about SQL Server Memory

• But rather, to share some of the experiences I have had, and how that led me to learn the things I know, or think I know…

• Pass on some tips and tricks for the future

• Demos and sample code


Background

• Started as a DBA, inherited some servers…

• Users complaining that the App / DB was slow

• My suspicion: Memory or IO related

• At the time, I didn’t know much about IO

• But I did understand memory

• So I set about learning more about SQL Server memory internals…


Communication Breakdown

• One problem when dealing with multiple teams is everyone focuses on their own area, not understanding the interconnections…

• Server Admins would look at Task Manager and say ‘the server has plenty of memory’

• Storage Admins would say ‘I can login and see the C drive, so what’s the problem?’


It’s always the same…

Task Manager Seeing the C Drive


Mind the Gap

• To be fair, as a DBA you should not rely on the System Administrators or SAN Administrators to find your bottleneck

• As a DBA you play a hybrid role between the physical hardware, operating system, application code, and of course, the data

• Grow your Sphere of Influence, focus on the edges…


Sphere of Influence

Photo Credit: http://www.flickr.com/photos/timsnell/4172347565/ © Steven Ormrod 2011, All Rights Reserved.

SQL Philosophy

• Surprise – I’m not really a DBA, and neither are you

• My job is to sell tomatoes; I am there to support the core business

• Technology does not exist in a vacuum

• Don’t fall into the trap: the system would be great if only for the users / database / etc.


DBAs Selling Tomatoes

© Steven Ormrod 2011, All Rights Reserved. Photo Credit: http://www.flickr.com/photos/liralenli/1244034737/

Old Servers

• One SQL Instance per Server

• 32-bit Hardware and Software

• Windows 2003

• SQL Server 2005

• 8G RAM, 6G Max Server Memory

• AWE, PAE, 3GB, UserVA, MaxDOP = 1


Server Sprawl

• 12 Production Database Servers

• 12 Standby Database Servers

• 12 Production Application Servers

• 12 Standby Application Servers

• 12 QA Database Servers

• 12 QA Application Servers

• 13 DEV Database Servers

• 13 DEV Application Servers

For one application!

That’s 98

Servers!


Just a few servers…

© Steven Ormrod 2011, All Rights Reserved. Photo Credit: http://www.flickr.com/photos/tjblackwell/5512498243/

Clustering, Consolidation, and Virtualization Oh My!

• Moved to a Clustering and Consolidation model for Database Servers

• 4 Nodes for Production, Standby, QA / DEV

• Active/Active across nodes and PRD / SBY

• Used Virtualization for Application Servers

• 3 Servers for the VM Hosts, PRD & DEV / QA

• Initial rollout was 15 servers, much better.

• Scaled out servers as load increased


New Servers – Round One

• Multiple SQL Instances per Server

• 64-bit Hardware and Software

• Windows 2008

• SQL Server 2008

• 64G RAM, 6G Max Server Memory

• AWE, MaxDOP = 1

• We were told to keep all the settings the same


Inaugural Migration

• Flew out to Atlanta to oversee the first region migrating to the new architecture

• Asked to increase Max Server Memory

• Management was concerned with changing any settings; what if more memory made it slower?

• Seriously? Seriously?


Opportunity

• Atlanta Manager had DBA background

• New servers had 64G of RAM

• First instance to be setup

• No other instances on the hardware

• Plenty of free memory to experiment with

• Got clearance to increase the memory during the migration


Results

• Increased Max Server Memory

• Kept swallowing memory until somewhere around 24G

• Performance immediately sped up

• IOs and CPU use went down

• Page Life Expectancy went up

• Target and Total Server Memory matched up

• Pending Memory Grants went away


Not Out of the Woods Yet

• Success? Not so fast…

• After the initial migration, we reset Max Server Memory back to 6G

• But yet, they still ask me why the database is so slow…

• Argh!

• I guess we didn’t want to spend the money.


Buffer Pool Metrics

• I crunched some numbers to calculate how much we could set Max Server Memory to without having to buy more RAM

• The cluster needed to host all the Production instances with one server down

• Needed to keep ~20% free for OS, Clustering Software, SQL Instances, etc.


Excel – Buffer Pool Metrics

• We could increase the Buffer Pool to 9G

• If we want more, then we need to add RAM


Still No Dice

• As we migrated more SQL Instances into the new hardware, we experienced more performance problems

• Servers started grinding to a halt

• 3rd Party Clustering and File System were causing massive IO latency, could not scale

• After rebuilding file systems, finally given clearance to increase the memory


Life is Beautiful

• Built new cluster with new file system

• Increased Max Server Memory to 16G

• MaxDOP to 4, Cores in NUMA Node

• Got rid of CPU Throttling - CPUID

• Performance Jumped!

• Page Life Expectancy from ~50 to > 2000

• Physical IOs Dropped – Less CPU Use


Thanks for the Memory

• Purchase Order to max out Server Memory

• 144G of RAM

• Max Server Memory set to 16G

• Min Server Memory set to 6G

• After all instances are migrated and benchmarked, we will increase Max Server Memory some more


Stacks of Memory


144G of RAM


Tools for Checking Memory

• Perfmon

• Memory Pressure

• DMVs

• Buffer Pool

• Plan Cache

• Requests

• TempDB


Perfmon

• Perfmon is a great tool to see what is going on at the Server level

• It can also provide a view inside SQL Server

• The problem: there are so many counters

• The solution: learn which counters to focus on, and which to ignore

• Not a simple answer, it will change over time as your servers change, and you learn more…


Perfmon


Perfmon

• When using Perfmon look for patterns

• Does a particular counter ‘drop off a cliff’

• E.g. Page Life Expectancy suddenly drops in value, 300 to 50, 2000 to 200, etc.

• Have a general idea of what values you want to see for a given counter

• Examples: Pending Grants close to 0, Buffer Cache Hit Ratio close to 99%


Perfmon DMV

• As an alternate to using Perfmon, you can query a DMV

• sys.dm_os_performance_counters

• You may not have direct access to the server

• Maybe you want to do a quick point-in-time check

• Leverage multi-server selects to check your entire server farm


Perfmon DMV


Perfmon DMV Multi-Server


Perfmon Counters

Counters I Like

• Page Life Expectancy

• Memory Grants Pending & Outstanding

• Free MBs

• Free System Page Table Entries

• Target Server Memory

• Stolen Pages

• Page Faults

Not So Much

• Buffer Cache Hit Ratio

• Average Disk Queue Length


External versus Internal Pressure

• External Pressure comes from the OS

• Internal Pressure comes from SQL Server

• Try not to focus on this too much, one usually begets the other

• Chicken and the egg; do you need more memory, or do you need to have less data

• More of a SQL Omelette


Memory Pressure Examples

• Page Life Expectancy

• Free MBs

• Free System Page Table Entries

• Page Faults

• Target Server Memory > Total Server Memory

• Pending Memory Grants

• Lots of Physical Reads


Hard Faults


Target Server Memory

• Target Server Memory > Total Server Memory

• SQL Server wants more memory than it can get


Page IO Latch Waits

• Small Buffer Pool means lots of Physical Reads


Something Else Bubbles Up • More Memory - PageIOLatch Simmers Down

• Something Else Bubbles Up…


SQLOS DMVs

• sys.dm_os_sys_info – general info about the server and operating system

• sys.dm_os_sys_memory – memory info from the operating system, system memory state

• sys.dm_os_process_memory – SQL Server process memory, page faults


SQLOS DMVs

• sys.dm_os_nodes – information about your NUMA nodes

• sys.dm_os_memory_nodes – memory information about your NUMA nodes

• Affinity Mask can be used to link up CPU cores to NUMA Nodes


SQLOS DMVs

• sys.dm_os_memory_clerks – used to allocate memory to SQL Server from a memory node

• Look for things outside the Buffer Pool that are consuming memory; CLR, etc.


SQLOS DMVs

• sys.dm_os_memory_cache_coutners – cache information

• Breakdown of Adhoc SQL, Stored Procedures, Views, Extended SPs, Metadata, etc.


Buffer Pool

• SQL Server’s Data Cache – always reads and writes from/to the Buffer Pool

• If the page is not there, SQL has to read from disk, measured by Buffer Cache Hit Ratio

• sys.dm_os_buffer_descriptors

• Can slice and dice many ways

• By database, dirty pages, NUMA node, tables and indexes


Buffer Pool Pages and Dirty


Buffer Pool DMVs

• By stringing together these DMVs, you can figure out exactly what is in your Buffer Pool

• sys.dm_os_bufer_descriptors

• sys.allocation_units

• sys.partitions

• sys.objects

• sys.indexes


Buffer Descriptors

Allocation Units

Partitions Objects

Indexes

Allocation Unit ID

Object ID

Index ID Object ID

Type & Container ID

HoBT or Partition ID

Buffer Pool


Plan Cache

• When a query is sent to SQL Server the Command Parser generates a hash

• If no matching plan is found…

• The Query Optimizer generates an Query Plan

• This tells SQL Server an efficient (hopefully) way to satisfy your query

• The plan is stored in the Plan Cache for quicker execution the next time


Plan Cache DMVs

• sys.dm_os_memory_cache_hash_tables – contains information on each active cache in SQL Server; filter to show just those related to the Plan Cache


Plan Cache DMVs

• sys.dm_exec_cached_plans – information about the cached plans; size, use count, links to DMFs


Single-Use Adhoc Plans

• Optimize for Adhoc Workloads

• Helps to keep single-use adhoc plans from wasting your plan cache


Requests

• Requests are the RUNNABLE tasks waiting on CPU

• Current activity on your SQL Server

• Look for high CPU, IOs, Query Memory, etc.

• Order of magnitude greater than others

• Query Memory comes from the Buffer Pool

• Used for Hashes and Sorts


Requests DMVs

• sys.dm_exec_requests – similar to Query Stats DMV, but real time; look for CPU time, signal time, reads, writes, query memory, sql text, query plans, etc.

• sys.dm_exec_query_memory_grants – information on Outstanding and Pending Memory Grants; used for hashing and sorts


Requests

• Look for what stands out…

• Lots of reads, lots of waits, lots of query memory…


Memory Grants

• Extra memory that the query needs

• Used for Hashing and Sorting

• Is there a long wait for the Memory Grant?


Hairy Query

• Lots of Logical Reads turns into Physical Reads

• Turns into Disk IO, causes heavy CPU use

• Look at the Query Memory

• What is this in relation to the Buffer Pool?


TempDB

• OK, so TempDB is not technically memory

• But your developers use it like memory

• And SQL uses it like virtual memory

• Only one TempDB for the entire instance

• #TempTables and ##GlobalTempTables

• @TableVariables

• Version Store

• Hashing, Sorting, Spools, Cursors


TempDB DMVs

• sys.dm_db_file_space_usage – breakdown of version store, user objects, internal objects

• Version Store – Snapshot Isolation

• User Pages – Temp Tables

• Internal Pages – Hashes and Sorts


TempDB DMVs

• sys.dm_db_session_space_usage – aggregated by session, historical


TempDB DMVs

• sys.dm_db_task_space_usage – details for the currently running tasks

• More allocations than deallocations, then the task is consuming space


Temporarily Dirty


Closing Thoughts

• SQL Server is a Journey

• Embrace problems, they are an opportunity to learn something at a deeper level

• Holy Trinity - Learn to see the connection between CPU, IO, and Memory

• Think beyond your job description

• Focus on the edges of SQL Server to expand your Sphere of Influence


Contact Inforation

• Steven Ormrod

• [email protected]

• sqlavenger.wordpress.com

• @SQLAvenger


sql server amnesia · •windows 2003 •sql server 2005 •8g ram, 6g max server memory •awe,...

Documents