Transcript Stuff you won’t find on Google... Yet

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 ES7000 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?
ES7000 Evolution
Mainframe Architecture – Performance – Reliability – Modularity – Lower TCO – Agility
1999
Intel® Pentium® III
Xeon® processor
(550Mhz)
ES7000/100 & 200
32cpu’s /32GB
2008
Intel® Xeon®
processor MP
ES7000/500
3.4Ghz/64GB
Intel Xeon
processor MP
Intel Xeon
processor EX
ES7000/one
64 cores /256GB
(128 with HT)
ES7000
Model 7600R
96 cores
1TB 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 ES7000 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 CX3-80 as used for
ETL World Record
• Single rack with 165 spindles
– 146 GB / 15krpm 4Gbit
– 16 GB cache
– 24 LUNS , 2 spindle Raid1
– 6 Metaluns , 4 (1+1 Raid1)
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 – 14K Reads vs 465K 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 32K
Result: 16% faster
• 465000 writes down to 58000 write IOs
Enterprise - high Speed DSI Solid State Storage
•
SQL2008R2
96 Parallel Bulk Inserts:
• 96 Core ES7000 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 (8KB 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
TIP1: Build an Index with Option (DOP 1) brings 512KB 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_hash96() 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 DOP1
Hashing
INSERT INTO SUPPLIER_Hash96Key WITH (TABLOCK)
SELECT
--[SK_Supplier] => Identity column ,
[S_SUPPKEY]
,[S_NATIONKEY]
,[S_NAME]
,[S_ADDRESS]
,[S_PHONE]
,[S_ACCTBAL]
,[S_COMMENT]
,ABS (hashbytes ('MD5', 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_HASH96key'
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
Out of the BOX :
(1File 2spindles RAID-1)
2 Files
(1File 2spindles RAID-1)
1FG-48 FILES / 24 LUNS
dbo.LINEITEM_DOP96
Non Partitioned table
dbo.LINEITEM_DOP1
dbo.LINEITEM_Hash96_rev
dbo.LINEITEM_Hash96Key_SSD
Reading from
Disk
Disk
Scanspeed
Reading from
Memory
Memory
Scanspeed
Duration
(Sec)
(MB/sec) avg.
Duration
(millsec)
(MB/sec)
202
250
6812
370
6800
6800
82
570
6768
6800
74
575
6762
6800
38
19
1050
3200
1811
1811
42775
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
• Trending
I hear this strange sound I
never heard before..
I’m sure it’s the engine!
• Workload Capacity planning
Tools to the trade
– SQL DMV’s
• Waitstats & Filestats
– Perfmon counters
Session Code - Session Title
SQL Quick Scan Surfaces
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 DiskIO 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/ms188762.aspx
sys.dm_io_virtual_file_stats (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms190326.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 ProClarity 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 ProcessIndex 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
21min 04sec
• 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 ProcessIndex 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
CoordinatorExecutionMode
-8
-4
Memory\LowMemoryLimit
70
75
MaxCPUUsage
0.8
0.5
OLAP\Process\AggregationMemoryLimitMin
1
10
OLAP\Process\AggregationMemoryLimitMax
5
80
OLAP\Process\DatabaseConnectionPoolMax
64
50
ThreadPool\Process\MaxThreads
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
24x7 BizTalk Workload optimization
Challenge
• Can the organisation’s BizTalk 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 SQL2008 R2 to the test
80Mill.
400Mill.
150Mill.
200Mill.
10Mill.
10K. 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_OrderDate
, CAST(CONVERT(CHAR(8), L.L_SHIPDATE, 112) AS INT) AS SK_ShipDate
, CAST(CONVERT(CHAR(8), L.L_COMMITDATE, 112) AS INT) AS
SK_CommitDate
, CAST(CONVERT(CHAR(8), L.L_RECEIPTDATE, 112) AS INT) AS
SK_ReceiptDate
/* 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/dd425070.aspx
• ETL World record
– http://msdn.microsoft.com/en-us/library/dd537533.aspx
• Dynamic Solutions SSD Storage Solutions
– Flash card: http://www.dynamicsolutions.com/main-menu/dsi3020
– DDR: http://www.dynamicsolutions.com/main-menu/dsi3400
– FLASH: http://www.dynamicsolutions.com/main-menu/dsi3500
• Unisys ES7000 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 24th
London – Storage and Query Optimisation - www.sqlpass.org.uk
Thursday 26th
London – SQL Internals and MS BI – SQLServerFAQ.com
Thursday 26th
London – Looking at newsgroups for Info – SQLServerFAQ.com
December 2009
Thursday 3rd
Tuesday 8th
Wednesday 9th
Thursday 10th
Tuesday 15th
Thursday 17th
Online – PowerUp with SQL Server (see next slide)
Cork – Query Optimisation – MTUG.ie
Dublin – Query Optimisation – IrishDev.com
Leeds – Service Broker and Powershell – SQLServerFAQ.com
Cambridge – Christmas Special down the pub – SQLSocial.com
Manchester – SQL Server Internals – SQLServerFAQ.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/PowerUpSQLServer
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