SQL Server optimization stuff you wont find on

SQL Server optimization stuff you won’t find on Google. . . (Yet) André Kamman Henk van der Valk

About the speakers - André • Specialized in …nothing really. • DBA managing 400+ SQL Server instances • Troubleshooting a lot • Over 20 years in IT • Co-founder and Chapter leader for Dutch PASS Chapter • Organizing committee for PASS European Conference © 2008 Unisys Corporation. All rights reserved. Page 2

About the speakers - Henk • Co-Founder Unisys ES 7000 Performance Centers (2001) • Deals with the largest & most demanding IT environments in the world • Performance troubleshooter & Workload optimizer • Participates in Dutch SQLPass & Performance SIG • 23+ years into computers… • ETL World Record (SQL 2008 launch) – 1+ Terabyte loaded within 30 minutes ~ 4 mill rows/sec - 600 MB/sec

Agenda • More about Henk and the Unisys Performance Lab • "What does a 96 core box look like ? “ • "Lessons Learned, not just for the vendor" • “ETL World Speed Record“ • Enterprise BI - Customer case • "Stop Guessing !" • "OMG: Now that's fast !" Session Code - Session Title

What does a 96 core box looks like? ES 7000 Evolution Mainframe Architecture – Performance – Reliability – Modularity – Lower TCO – Agility 1999 Intel® Pentium® III Xeon® processor (550 Mhz) ES 7000/100 & 200 32 cpu’s /32 GB 2008 Intel® Xeon® processor MP ES 7000/500 3. 4 Ghz/64 GB Intel Xeon processor MP Intel Xeon processor EX ES 7000/one 64 cores /256 GB (128 with HT) ES 7000 Model 7600 R 96 cores 1 TB RAM Page 5

What does a 96 core box look like ? © 2008 Unisys Corporation. All rights reserved. Page 6

World-Record-Breaking Gaming Event • Unisys ES 7000 Server Powers World`s Largest LAN Party and Computer Festival Hosts Highest Number of Online Players Ever Recorded on a Single Game Server Dec 2, 2004 1, 160 video game players © 2008 Unisys Corporation. All rights reserved. Page 7

SQL Server Integration Services Record ETL Performance 1. 18 TB of data loaded in 29 min 54 sec • Data model reflects a wholesale supplier data warehouse • Data is read from text files, sent over network, and stored in a single database image • SSIS runs on source systems, reflecting a distributed environment • Built upon – SQL Server 2008 February CTP – Windows Server 2008 Datacenter

SAN configuration EMC Clariion CX 3 -80 as used for ETL World Record • Single rack with 165 spindles – 146 GB / 15 krpm 4 Gbit – 16 GB cache – 24 LUNS , 2 spindle Raid 1 – 6 Metaluns , 4 (1+1 Raid 1)

Truly Scalable… 4 Million rows/sec ~600 MB/sec

SSIS Base package – Data Flow • Data types sharpened © 2008 Unisys Corporation. All rights reserved. Page 11

SSIS - IO tuning • Observation: SSIS – 14 K Reads vs 465 K writes SQL - 465 K Reads vs 8800 Writes (128 KB IO Read) (256 KB IO Write) ->> Time to tune Data transport between SSIS and SQL!

Tip: Increase Network packet size • SSIS: Connection Mgr. Packet Size from 0 into 32 K

Result: 16% faster • 465000 writes down to 58000 write IOs

Enterprise - high Speed DSI Solid State Storage •

SQL 2008 R 2 96 Parallel Bulk Inserts: • 96 Core ES 7000 with DSI Solid State disks • From 4 Million rows/sec -> 14+ Million rows/sec.

Speeding up table scan operations • What should we measure? • Tools of the trade : 1) Windows Performance Monitor – Logical Disk : Avg. Disk Bytes/Read – Logical Disk: Disk Read Bytes/sec – SQL Buffer Manager: Page Lookups/sec (8 KB pages each) 2) SQL waitstats : SELECT * FROM sys. dm_os_wait_stats ORDER BY wait_time_ms DESC © 2008 Unisys Corporation. All rights reserved. Page 17

Non optimized Index: Read block sizes may vary • select COUNT (*) from dbo. LINEITEM • Direct correlation between blocksize and throughput © 2008 Unisys Corporation. All rights reserved. Page 18

Speeding up table scan operations TIP 1: Build an Index with Option (DOP 1) brings 512 KB Read IOPS ü Tradeoff. . . ü Index build time © 2008 Unisys Corporation. All rights reserved. Page 19

3) Implementing Table Hash Partitioning hash • Create filegroups hold the partitions – Equally balance over LUN using optimal layout – Use CREATE PARTITION FUNCTION command – Partition the tables into #cores partitions (96 in our case) • Use CREATE PARTITION SCHEME command 0 1 2 3 4 5 6 – Bind partition function to filegroups • Add hash column to table (tinyint, just one byte per row) – Calculate a good hash distribution – For example, use hashbytes with modulo • Rebuild the clustered index on the table on the partition scheme 93 94 95

Hashing • Optimal : Number of partitions == Number of Cores • The ps_hash 96() partition function has 96 partitions • The hash value is created in such a way that there is a near equal number of rows in all partitions ü Partion the table by the Hash column ü Re-Index each partition with DOP 1

Hashing INSERT INTO SUPPLIER_Hash 96 Key WITH (TABLOCK) SELECT --[SK_Supplier] => Identity column , [S_SUPPKEY] , [S_NATIONKEY] , [S_NAME] , [S_ADDRESS] , [S_PHONE] , [S_ACCTBAL] , [S_COMMENT] , ABS (hashbytes ('MD 5', CAST (S_SUPPKEY AS VARCHAR )) % 96) FROM dbo. SUPPLIER = 96 almost

Double check partitioned data for equal distribution afterwards • Select * from sys. partitions where object_name(object_id)='LINEITEM_HASH 96 key' Session Code - Session Title Kick off

Table Scan Optimization Results üTable partitioning with Hashing speeds up reading from Disk & Memory üAdd Hash key for maximum. performance 400 Million Rows / 46 Gbyte Reading from Disk Scanspeed Reading from Memory Duration (Sec) (MB/sec) avg. Duration (millsec) Out of the BOX : (1 File 2 spindles RAID-1) 2 Files (1 File 2 spindles RAID-1) 1 FG-48 FILES / 24 LUNS dbo. LINEITEM_DOP 96 Non Partitioned table dbo. LINEITEM_DOP 1 dbo. LINEITEM_Hash 96_rev 202 250 6812 370 ! c i g a M (MB/sec) 6800 82 570 6768 6800 74 575 6762 6800 r e t s a f -10 x 3 x dbo. LINEITEM_Hash 96 Key_SSD Memory Scanspeed 38 19 1050 3200 r e t s a 6 x f 1811 42775

4) Disable Memory Prefetching (BIOS setting)

Stop Guessing! Get your Workload baseline right It helps understanding your business workload • How much is processed by SQL data daily ? • “Pre & Post virtualization” statistics I hear this strange sound I never heard before. . I’m sure it’s the engine! • Trending • Workload Capacity planning Tools to the trade – SQL DMV’s • Waitstats & Filestats – Perfmon counters Session Code - Session Title

SQL Quick Scan Surfaces Customer Goal Others…. comparisons SQL Core Engine – (Overall & Real time) Statistics Basic Configuration Waits Spinlocks Latches Memory Top Execution statements Locking Security Workload Table Sizes Table Usage Row Mod Index Usage File Disk. IO statistics Operations Databases Infrastructure & Operating System CPU Disk Network Memory Page 27

Realtime check for Pending Ios / msec sys. dm_io_pending_io_requests (Transact-SQL) http: //msdn. microsoft. com/en-us/library/ms 188762. aspx sys. dm_io_virtual_file_stats (Transact-SQL) http: //msdn. microsoft. com/en-us/library/ms 190326. aspx

DWH Proof of Concept for Customer • Large European supermarket chain – All products sold since 2002 – Final solution: 360 Billion rows at bon-level – 600+ users – All should have ad-hoc access via Pro. Clarity and Excel – 30+ TB data – Competition both picked Oracle… – POC Duration : 3 weeks

SSAS 2005 optimizations Obtain maximum cube processing throughput by: – Partition your data – Find Maximum no. of rows/sec processed for each partition – Determine Maximum rows/ sec throughput overall – Gradually increase No. of partitions being processed & increase No of CPU’s/Cores

16 / 32 / 64 Core – Scale Up optimizations - Check the Process. Index phase: default only 3 partitions are in processed simultaneous - With Msmdsrv. ini changes 14+ active partitions in parallel are processed - Money data type 13% improvement

Example - Processing details & results (32 Cores) • Total # Rows: 1323338880 • Partitions: 32 • Parallel tasks: 32 • Duration 21 min 04 sec • Throughput 1, 046, 945 rows /sec (Process Full) © 2008 Unisys Corporation. All rights reserved. Page 32

32 Cores & 100% © 2008 Unisys Corporation. All rights reserved. Page 33

SQL Shared memory - LPC connections on 64 cores • 51 partitions / 2. 1 billion rows with LPC connections instead of TCP/IP: 19 minutes 8 sec • == 1, 821, 305 avg rows/sec (Process FULL)

16 /32 /64 Core Scale Up optimizations - Check the Process. Index phase: default only 3 partitions are in processed simultaneous - With these Msmdsrv. ini changes 14 active partitions in parallel are processed Parameter Optimized Original value Coordinator. Execution. Mode -8 -4 MemoryLow. Memory. Limit 70 75 Max. CPUUsage 0. 8 0. 5 OLAPProcessAggregation. Memory. Limit. Min 1 10 OLAPProcessAggregation. Memory. Limit. Max 5 80 OLAPProcessDatabase. Connection. Pool. Max 64 50 Thread. PoolProcessMax. Threads 128 64

Impression of SSAS 2005 Peak Cube Processing performance 5+ million rows read/sec SSAS writes 15. 5 Mill rows/sec!

International Oil Company 24 x 7 Biz. Talk Workload optimization Challenge • Can the organisation’s Biz. Talk platform cope with 30% SAP workload increase ? • Only CPU / Network load was tracked Unisys Solution • 2 week Onsite review & Analysis Quickscan Results Workload Analysis / overview of todays capacity Performance tuning advice on all layers implemented Succesful SAP integration Unisys Profile & Technology Center Page 37

Putting SQL 2008 R 2 to the test 80 Mill. 400 Mill. 150 Mill. 200 Mill. 10 K. Rows SELECT top 1000000 /* Surrogate Key lookups */ ISNULL(P. SK_Part, -1) AS SK_Part , ISNULL(C. SK_Customer, -1) AS SK_Customer , ISNULL(S. SK_Supplier, -1) AS SK_Supplier , ISNULL(CL. SK_Clerk, -1) AS SK_Clerk /* Dates */ , CAST(CONVERT(CHAR(8), O. O_ORDERDATE, 112) AS INT) AS SK_Order. Date , CAST(CONVERT(CHAR(8), L. L_SHIPDATE, 112) AS INT) AS SK_Ship. Date , CAST(CONVERT(CHAR(8), L. L_COMMITDATE, 112) AS INT) AS SK_Commit. Date , CAST(CONVERT(CHAR(8), L. L_RECEIPTDATE, 112) AS INT) AS SK_Receipt. Date /* Measures */ , L. L_Quantity AS Quantity , L. L_TAX AS Tax , L. L_DISCOUNT AS Discount , L_EXTENDEDPRICE AS Price FROM ORDERS O INNER JOIN LINEITEM L ON O. O_ORDERKEY = L. L_ORDERKEY LEFT JOIN CUSTOMER C ON O. O_CUSTKEY = C. C_CUSTKEY LEFT JOIN PART P ON L. L_PARTKEY = P. P_PARTKEY LEFT JOIN SUPPLIER S ON S. S_SUPPKEY = L. L_SUPPKEY LEFT JOIN CLERK CL ON O. O_Clerk = CL. CL_CLERK where sk_part = '999999' OPTION ( MAXDOP 96, LOOP JOIN )

OMG Session Code - Session Title

Additional Resources • My Blog: http: //andrekamman. com/ • The Data Loading Performance Guide – http: //msdn. microsoft. com/en-us/library/dd 425070. aspx • ETL World record – http: //msdn. microsoft. com/en-us/library/dd 537533. aspx • Dynamic Solutions SSD Storage Solutions – Flash card: http: //www. dynamicsolutions. com/main-menu/dsi 3020 – DDR: http: //www. dynamicsolutions. com/main-menu/dsi 3400 – FLASH: http: //www. dynamicsolutions. com/main-menu/dsi 3500 • Unisys ES 7000 Enterprise Servers – http: //www. unisys. com/products/enterprise__servers/high_d_end__servers/models/index. htm Session Code - Session Title

Europe’s Premier Community SQL Server Conference

Up and coming events. . November 2009 Tuesday 24 th London – Storage and Query Optimisation - www. sqlpass. org. uk Thursday 26 th London – SQL Internals and MS BI – SQLServer. FAQ. com Thursday 26 th London – Looking at newsgroups for Info – SQLServer. FAQ. com December 2009 Thursday 3 rd Tuesday 8 th Wednesday 9 th Thursday 10 th Tuesday 15 th Thursday 17 th Online – Power. Up with SQL Server (see next slide) Cork – Query Optimisation – MTUG. ie Dublin – Query Optimisation – Irish. Dev. com Leeds – Service Broker and Powershell – SQLServer. FAQ. com Cambridge – Christmas Special down the pub – SQLSocial. com Manchester – SQL Server Internals – SQLServer. FAQ. com

Time to Power Up with SQL Server within your organization! Who Should Attend: -Database administrators -Application developers/programmers -Database developers - IT Professionals responsible for SQL Server management Sign Up Today at http: //tinyurl. com/Power. Up. SQLServer During the day's presentations by SQL Server experts, Greg Low, Javier Loria, and Niels Berglund, you'll get detailed information and time to ask your questions. Three sessions, directly from your own computer: - Understanding Query Plan Caching in SQL Server - SQL Server High Availability - SQL Server Performance

It’s party time! Coming up next in the Atrium: Time to relax and let your hair down. Rockband Table football Air Hockey and more Don’t forget speakers and sponsors have drinks vouchers

We hope you had a great conference! See you at the next one in the Spring Thank you