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 • MSOLAP$SQL:Memory Cleaner Memory KB • MSOLAP$SQL:Memory >Cleaner 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 – MSOLAP$SQL:Memory > 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 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

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

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 it’s 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 • <PreAllocate> </PreAllocate> • 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/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

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? • <PreAllocate> 80 </PreAllocate> • <TotalMemoryLimit> 80 </TotalMemoryLimit> • <LowMemoryLimit> 65 </LowMemoryLimit>

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/Analysis+Services/default.aspx • Books Online • http://cwebbbi.spaces.live.com/default.aspx • WWW.SQLSENTRY.NET

37. Thank you!

38. Monitoring • Perfmon counters • Another option!