Memory Management in SQL Server Analysis Services - PowerPoint PPT Presentation

About This Presentation
Title:

Memory Management in SQL Server Analysis Services

Description:

WWW.SQLSENTRY.NET. About this presentation... Technical Reviewers: Brad Daniels, Lindsey Allen, John Desch, Wayne Robertson, Kevin Cox ... – PowerPoint PPT presentation

Number of Views:214
Avg rating:3.0/5.0
Slides: 39
Provided by: stevenw154
Category:

less

Transcript and Presenter's Notes

Title: Memory Management in SQL Server Analysis Services


1
Memory Management in SQL Server Analysis Services
  • Steve Wright
  • Director of Product Support
  • SQL Sentry, Inc.

2
About the Presenter
  • Steve Wright Director of Product Support for
    SQL Sentry Inc.
  • Headquartered in Huntersville, NC
  • SQL Sentry provides tools to monitor your entire
    Microsoft BI Platform
  • WWW.SQLSENTRY.NET

3
About this presentation
  • We will discuss methods used to manage memory in
    SQL Server Analysis Services
  • Expected behavior when applying these methods.
  • How to monitor that behavior
  • Examples of Issues when these methods are
    mis-applied.

4
SSAS Memory Categories
  • Shrinkable
  • Easily reduced and returned to the OS
  • SE and FE caches
  • Nonshrinkable
  • Used for more essential system-related activites
  • Memory Allocators
  • Metadata Objects
  • Not easily reduced.

5
Measuring SSAS Memory
  • MSOLAPSQLMemory Cleaner Memory KB
  • MSOLAPSQLMemory gtCleaner Memory (non)shrinkable
    KB

6
SSAS Memory Limits
  • Used to determine how to allocate and manage
    internal memory
  • Configurable with SSAS properties with SSMS or
    edit msmdsrv.ini
  • Memory\LowMemoryLimit
  • Memory\TotalMemoryLimit
  • Refers to physical memory on the server

7
Memory\LowMemoryLimit
  • Default Value 65 in SSAS2008, 75 in SSAS2005
  • Percentage if between 0 and 100.
  • Bytes if greater than 100.
  • Refers to total physical memory on machine

8
Memory\TotalMemoryLimit
  • Default Value 80
  • Percentage if between 0 and 100.
  • Bytes if greater than 100.
  • Total amount of memory the SSAS
    process(msmdsrv.exe) can consume.

9
Total Memory Limit cont.
  • If set to 0, SSAS will use no memory for caching
  • Total available to SSAS depends on platform
  • 32-bit limited to 3GB with /Gb switch enabled
  • 64-bit provides all memory available

10
Behavior Low Limit Reached
  • Cleaner threads start moving data out of memory
    (non-aggresively)
  • Perfmon MSOLAPSQLMemory gt Cleaner Memory
    Shrunk KB/sec

11
Behavior Total Limit Reached
  • Cleaner goes into crisis mode
  • More aggressive cleanup
  • More threads spawned
  • Dramatic performance impact

12
Behavior Between Low and Total
  • Economic memory management
  • Memory Price per KB
  • Depends on amount available based on limits
  • Memory is free and the livin is easy under the
    Low Limit!
  • Memory price begins to increase as total memory
    increases between Low and Total.

13
SSAS Memory Management
  • Self governing no consideration for low
    physical memory conditions
  • Windows File Cache
  • Unlike SQL Server, SSAS DBs are collection of
    files on files system and use file system cache
  • SSAS data may be loaded in RAM as file cache
  • Not part of SSAS process memory or limits

14
Monitoring - Perfmon
  • Memory Usage KB
  • Memory Limit Low(High) KB
  • Cleaner Memory KB
  • Cleaner Memory (Non)Shrinkable KB
  • Cleaner Memory Shrunk KB
  • Do NOT rely on Task Manager

15
Monitoring Example
16
The Big Picture
17
Graphical Anomaly - Background
  • SQL Sentry Customer Financial Services
  • Beta tester of SQL Sentry Performance Advisor for
    Analysis Services
  • Eager to test as they were suffering from SSAS
    performance issues
  • Scheduled Microsoft to come on-site to help

18
Common Issues Graphical Anomaly?
19
Graphical Anomaly The Investigation
Memory Limit Setting Configured Value Perf Counter Value (KB)
Memory\LowMemoryLimit 2097152 8192
Memory\TotalMemoryLimit 13531488 13312
20
Graphical Anomaly The Investigation
21
Graphical Anomaly A Comparison
Before
After
22
The Investigation Continues
23
The Investigation Continues
24
The Investigation Continues
  • Almost all time spent on non-cached Storage
    Engine operations (SE non-cached)
  • Every time query needs data its not found in the
    FE Cache
  • FE requests from SE
  • SE unable to find in its cache
  • Must go to file system

25
Caught Red Handed!
26
Caught Red Handed!
  • Total Queries shows how many times storage engine
    went to the file system to read data from a
    partition or aggregation.
  • Want this to be as close to 0 as possible.
  • Exceptions are when Agg is first hit and loaded
    into cache or cache is cold.

27
Confirmation
28
Could have been worse!
29
Scenario 2
  • Preallocation

30
Memory Preallocation
  • Introduced with SQL 2005 SP2 due to limitations
    with memory allocations in Windows Server 2003
  • Edit in msmdsrv.ini
  • ltPreAllocategt lt/PreAllocategt
  • Preallocates of physical memory on SSAS startup

31
SQL CAT Tech Notes
  • Running Microsoft SQL Server 2008 Analysis
    Services on Windows Server 2008 vs. Windows
    Server 2003 and Memory Preallocation Lessons
    Learned
  • http//sqlcat.com/technicalnotes/archive/2008/07/1
    6/running-microsoft-sql-server-2008-analysis-servi
    ces-on-windows-server-2008-vs-windows-server-2003-
    and-memory-preallocation-lessons-learned.aspx
  • Author Carl Rabeler Contributors Eric
    Jacobsen, Thomas Kejser Technical Reviewers
    Brad Daniels, Lindsey Allen, John Desch, Wayne
    Robertson, Kevin Cox

32
SQL CAT Report Summary
  • SSAS 2008 runs equally well on Windows 2008 with
    or without preallocation
  • SSAS 2008 runs substantially better on Windows
    2003 WITH preallocation
  • Essentially equal to Windows 2008

33
Points to Consider
  • Be sure to set low enough so sufficient memory
    remains for other processes and avoid paging.
  • Be sure to set high enough for it to help SSAS
  • Use peak value for memory counters as guideline
  • Also applies to SSAS 2005 (no change in code)

34
PreAllocate Pitfall?
  • ltPreAllocategt 80 lt/PreAllocategt
  • ltTotalMemoryLimitgt 80 lt/TotalMemoryLimitgt
  • ltLowMemoryLimitgt 65 lt/LowMemoryLimitgt

35
PreAllocate Pitfall?
  • PreAllocate does not override memory limits
  • Memory cleaner acts as if all preallocated memory
    is in use.
  • In this case PreAllocate TotalMemoryLimit
  • Result cleaner goes into crisis mode
  • Performance suffers drastically

36
Resources
  • http//sqlcat.com/tags/AnalysisServices/default.a
    spx
  • Books Online
  • http//cwebbbi.spaces.live.com/default.aspx
  • WWW.SQLSENTRY.NET

37
Thank you!
38
Monitoring
  • Perfmon counters
  • Another option!
Write a Comment
User Comments (0)
About PowerShow.com