Building the Fastest SQL Servers

1. DBI328 Building the Fastest SQL Servers Brent Ozar Microsoft Certified Solutions Master (MCSM) Brent Ozar PLF, LLC

2. www.BrentOzar.com BrentO@BrentOzar.com @BrentO BrentOzar

3. Photo Licensed with Creative Commons: http://www.flickr.com/photos/philly22/4279056817/

4. How I Built SQL Servers • How I Thought I Did It: • Carefully baseline application to determine load patterns • Hand-select components perfectly matched to application needs • Go live with a faster, more reliable environment

5. Photo Licensed with Creative Commons: http://www.flickr.com/photos/dangoodphoto/5202499523/

6. How I Built SQL Servers • How I Thought I Did It: • Carefully baseline application to determine load patterns • Hand-select components perfectly matched to application needs • Go live with a faster, more reliable environment • How I Actually Did It: • Took a few rush measurements with Perfmon at random times • Tried to figure out what parts were right • Go live without testing • Somebody changes the app and my work was useless anyway

7. Photo Licensed with Creative Commons: http://www.flickr.com/photos/icathing/26603225/

8. How Microsoft Designs SQL Server Appliances • Systematically review thousands of SQL Servers • Distill use cases down to a few common patterns • Choose hardware components that are very likely to work great for those patterns • Publish an incredibly detailed test checklist to make sure the hardware is working as designed

9. How Microsoft Designs SQL Server Appliances • Systematically review thousands of SQL Servers • Distill use cases down to a few common patterns • Choose hardware components that are very likely to work great for those patterns • Publish an incredibly detailed test checklist to make sure the hardware is working as designed Let’s steal their hard work.

10. Session Agenda • Define common SQL Server use patterns • Understand the right hardware for a pattern • Recognize the server designs we can reuse • Learn how to test our own hardware

11. Define Common SQL Use Patterns

12. OLTP: Transactional Processing • How It’s Accessed:Inserts, Updates, Deletes throughout the day • How It’s Stored:Large numbers of tables with lots of complex relationships, but usually under a terabyte of data. • Examples:Web sites, ecommerce, payroll

13. Data Warehousing • How It’s Accessed:Loaded in short windows overnight,then read-only with big reads through the day for reports • How It’s Stored:Just a few tables, but many historical records in each table, and often over a terabyte of data. • Examples:10 years of sales history, stock prices, patient history

14. The Real Difference • An OLTP user says, “I need to fetch Mr. Jones’s order. This transaction takes 3 seconds, but I want it to finish instantly.” • A data warehouse user says,“I need all sales data for bicycles from last year. This report takes 15 minutes, but I want it to finish in 30 seconds.”

15. Understand The Right Hardware for Pattern

16. SQL Server Hardware At Its Simplest Memory(64GB) OLTP scenario: 50GB table. Drives(100GB) CPU End User

17. The Right Hardware for the Fastest OLTP • Instant queries = cache all data in memory • Maximize memory size • Minimize data size (drop extra indexes, right-size fields) • Instant transactions = blazing fast log file • SSD RAID 10 for multiple databases • Can get away with dedicated RAID 10 magnetic for a single DB • Avoiding locking issues = blazing fast TempDB with RCSI • Read Committed Snapshot Isolation

18. I Didn’t Say Blazing Fast Data Drives. • Once the data’s in cache, data drive speed rarely matters. • SQL Server restarts will mean slow performance • Aggressively monitor data size, memory size • When you run out, things get ugly fast • Note: this session is about building really fast servers,not really easy servers. Easy = RAID 10 everything.

19. SQL Server Hardware At Its Simplest Memory(256GB) Data warehouse scenario: 500GB table. Drives(1TB) CPU End User

20. The Right Hardware for the Fastest DWs • Instant queries = cache all data in memory (we can’t) • Maximize memory size • Minimize data size (drop extra indexes, right-size fields) • Maximize storage throughput to retrieve what isn’t cached • Instant transactions = blazing fast log file • SSD RAID 10 for multiple databases • Can get away with dedicated RAID 10 magnetic for a single DB • Avoiding locking issues = blazing fast TempDB with RCSI • Read Committed Snapshot Isolation

21. Recognize Server Designs We Can Reuse

22. Photo Licensed with Creative Commons: http://www.flickr.com/photos/leejordan/2484541616/

23. Download: BrentOzar.com/go/fastdw

24. Fast Track Data Warehouse Reference Arch. • Microsoft provides sample framework for vendors • Vendors like Dell, HP, IBM provide specific server, storage, switching parts as an approved whole • Microsoft gives you the testing instructions for validation • Vendors provide the proof, and you re-validate it • You can use this same knowledge with your gear too

26. Sample Fast Track Reference Architecture • Dual Xeon processors, 6 cores each: MCR 3,168 MB/sec • Four 8Gbps Host Bus Adapters (HBAs):Delivers 3,123 MB/sec • 48 15k drives in multiple RAID 5s:Delivers 3,146 MB/sec • Notice the balance

27. SQL Server Can Handle More Than You Think • Maximum Consumption Rate (MCR):How fast each core can consume data from storage • Starting point: 200MB/sec of sequential reads per core • Dual socket, quad core: 1,600MB/sec • Four socket, quad core: 3,200MB/sec • Four socket, ten core: 8,000MB/sec

28. Your Hardware Does Less Than You Think From BrentOzar.com/posters

29. Typical Storage Throughput

30. New SSDs Do More Than You Think

31. Learn How to Test Our Own Hardware

32. Photo Licensed with Creative Commons: http://www.flickr.com/photos/boostinchick/4956027368/

33. Test Storage Quickly with CrystalDiskMark • Free tool with instructions:BrentOzar.com/go/cdm • Pick 5 tests, 4000MB testfile, drive letter • Sequential:roughly akin to backups, large table scans • 4K QD32:vaguely similar to activeOLTP server or TempDB

34. Test Storage Slowly with SQLIO • Free tool with instructions:BrentOzar.com/go/sqlio • Lots of possible options,collect the whole set • Use a test file larger than yourSAN’s cache (say, 20GB) • Don’t run this on a live server

35. Your Goals • Test with CrystalDiskMark to get a quick idea • Try two simultaneous CrystalDiskMark tests against two different drive letters to see if your multipathing works • When that works, amp up to SQLIO and really push it • Prove the hardware can achieve the Maximum Consumption Rate before you bother installing SQL Server

36. Remember Storage Isn’t the Only Solution

37. How to Reduce Storage Throughput Needs • Keep memory free for SQL Server data caching • Design and maintain your indexes • Compress data • Use partitioned views to segment old archive data into tables with 100% fill factor, perfect statistics, read-only filegroups • Consider columnstore indexes for reporting queries • Merry-go-round scans with SQL Server Enterprise Edition

38. Merry-Go-Round Scans in Enterprise Edition tblSales – 1,000GB Brent’s Query

39. Merry-Go-Round Scans in Enterprise Edition tblSales – 1,000GB Brent’s Query

40. Merry-Go-Round Scans in Enterprise Edition tblSales – 1,000GB Brent’s Query Dandy’s Query

41. Merry-Go-Round Scans in Enterprise Edition tblSales – 1,000GB Brent’s Query Dandy’s Query

42. Merry-Go-Round Scans in Enterprise Edition tblSales – 1,000GB Brent’s Query Dandy’s Query

43. Merry-Go-Round Scans in SQL Server Type R tblSales – 1,000GB Dandy’s Query Dandy’s Query

44. What We Covered • Defined common SQL Server use patterns • OLTP: “I want the query to finish instantly.” • DW: “I want the query to finish in 30 seconds.” • Understood the right hardware for a pattern • OLTP: Cache it all in memory • DW: Get blazing fast • Recognized the server designs we can reuse • DW: MS Fast Track Data Warehouse Reference Architecture • Learned how to test our own hardware and compare it • Remembered that storage isn’t the only solution

45. Photo Licensed with Creative Commons: http://www.flickr.com/photos/atxjen/4014857345/

46. Track Resources Hands-On Labs @sqlserver @TechEd_NA #msTechEd SQL Server 2012 Eval Copy Get Certified! mva Microsoft Virtual Academy

47. Resources Learning TechNet • Connect. Share. Discuss. • Microsoft Certification & Training Resources http://northamerica.msteched.com www.microsoft.com/learning • Resources for IT Professionals • Resources for Developers • http://microsoft.com/technet http://microsoft.com/msdn

48. Required Slide Complete an evaluation on CommNet and enter to win!

49. MS Tag Scan the Tag to evaluate this session now on myTechEd Mobile

50. © 2012 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.