Memory management in sql server analysis services
Download
1 / 38

Memory Management in SQL Server Analysis Services - PowerPoint PPT Presentation


We will discuss methods used to manage memory in SQL Server Analysis Services ... SQL CAT Tech Notes. Running Microsoft SQL Server 2008 Analysis Services on Windows Server ...

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha

Download Presentationdownload

Memory Management in SQL Server Analysis Services

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Memory management in sql server analysis services l.jpg

Memory Management in SQL Server Analysis Services

Steve Wright

Director of Product Support

SQL Sentry, Inc.


About the presenter l.jpg

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


About this presentation l.jpg

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.


Ssas memory categories l.jpg

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.


Measuring ssas memory l.jpg

Measuring SSAS Memory

  • MSOLAP$SQL:Memory Cleaner Memory KB

  • MSOLAP$SQL:Memory >Cleaner Memory (non)shrinkable KB


Ssas memory limits l.jpg

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


Memory lowmemorylimit l.jpg

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


Memory totalmemorylimit l.jpg

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.


Total memory limit cont l.jpg

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


Behavior low limit reached l.jpg

Behavior – Low Limit Reached

  • Cleaner threads start moving data out of memory (non-aggresively)

  • Perfmon – MSOLAP$SQL:Memory > Cleaner Memory Shrunk KB/sec


Behavior total limit reached l.jpg

Behavior – Total Limit Reached

  • Cleaner goes into crisis mode

  • More aggressive cleanup

  • More threads spawned

  • Dramatic performance impact


Behavior between low and total l.jpg

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.


Ssas memory management l.jpg

SSAS Memory Management

  • Self governing – no consideration for low physical memory conditions

  • Windows File Cache

    • Unlike SQL Server, SSAS DB’s 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


Monitoring perfmon l.jpg

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


Monitoring example l.jpg

Monitoring Example


The big picture l.jpg

The Big Picture


Graphical anomaly background l.jpg

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


Common issues graphical anomaly l.jpg

Common Issues – Graphical Anomaly?


Graphical anomaly the investigation l.jpg

Graphical Anomaly – The Investigation


Graphical anomaly the investigation20 l.jpg

Graphical Anomaly – The Investigation


Graphical anomaly a comparison l.jpg

Graphical Anomaly – A Comparison

Before

After


The investigation continues l.jpg

The Investigation Continues


The investigation continues23 l.jpg

The Investigation Continues


The investigation continues24 l.jpg

The Investigation Continues

  • Almost all time spent on non-cached Storage Engine operations (SE non-cached)

  • Every time query needs data it’s not found in the FE Cache

  • FE requests from SE

  • SE unable to find in its cache

  • Must go to file system


Caught red handed l.jpg

Caught Red Handed!


Caught red handed26 l.jpg

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.


Confirmation l.jpg

Confirmation


Could have been worse l.jpg

Could have been worse!


Scenario 2 l.jpg

Scenario #2

Preallocation


Memory preallocation l.jpg

Memory Preallocation

  • Introduced with SQL 2005 SP2 due to limitations with memory allocations in Windows Server 2003

  • Edit in msmdsrv.ini

  • <PreAllocate> </PreAllocate>

  • Preallocates % of physical memory on SSAS startup


Sql cat tech notes l.jpg

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/16/running-microsoft-sql-server-2008-analysis-services-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


Sql cat report summary l.jpg

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


Points to consider l.jpg

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)


Preallocate pitfall l.jpg

PreAllocate Pitfall?

  • <PreAllocate> 80 </PreAllocate>

  • <TotalMemoryLimit> 80 </TotalMemoryLimit>

  • <LowMemoryLimit> 65 </LowMemoryLimit>


Preallocate pitfall35 l.jpg

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


Resources l.jpg

Resources

  • http://sqlcat.com/tags/Analysis+Services/default.aspx

  • Books Online

  • http://cwebbbi.spaces.live.com/default.aspx

  • WWW.SQLSENTRY.NET


Thank you l.jpg

Thank you!


Monitoring l.jpg

Monitoring

  • Perfmon counters

  • Another option!


ad
  • Login