Transcript Understand how ChannelAdvisor is using SQL Server 2016 to

http://aka.ms/SQLCAT
Provide cloud-based e-commerce solutions that enable retailers and
manufacturers to integrate, manage and optimize their merchandise sales
SaaS platform from foundation
We make money when our customers do
Cost growth should be slower than revenue growth
Powers more than 3000 retailers
Customers include Macy’s, Samsung, Dell, Under Armor, Staples, Philips
Big event is “Cyber 5” – Thanksgiving through Cyber Monday
~$250 million sales
Helping the world to sell online …
Production Environment
Adoption of SQL Server 2016
117 SQL Server instances (77 running SQL Server 2016, 40 on 2014 SP2)
Primary SaaS business drivers on SQL 2016 RTM CU1
2200+ databases are consolidated
1.6M+ SQL Server transactions/sec in SQL Server environment
928 cores / 22TB RAM dedicated to SQL Server – 100% virtualized
88TB storage allocated for SQL Server data - backed by multiple Pure Storage All Flash Arrays
OLTP and DW workloads in SQL Server
OLTP for Product Listing on different marketplaces
ENTDBs - SKUs, orders, attributes etc…
Data Marts for tracking sales, auctions – trickle load leading to real-time reporting
Database sizes vary from 10GB through 8TB
Using page compression and/or columnstore wherever possible for IO and memory savings
SQL Server 2005 to SQL Server 2012
Disruptive – Backup/Restore = more downtime
Multiple rather than single event due to time constraints
Major development efforts
Deprecated feature usage and syntax
Query performance issues
“Dual mode” development to handle extended periods on multiple versions
Running on SQL Server 2016 since early 2016
Minimal disruption – single downtime events per application
Operational work only – no development work required
Single maintenance window upgrade all instances of particular type
Wanted to minimize performance impact risk
Created instance on SQL Server 2016 on new (virtual) hardware
Created Availability Group between SQL Server 2014 instance and SQL Server 2016
Query Store to de-risk query plan changes
Using Powershell allows for fast, repeatable, parallelizable upgrade process
57 database servers upgraded in a two hour maintenance window
Start at compatibility-level 120, enable Query Store, waited before switching to 130
Replacing 3rd party monitoring software with Query Store
Much easier to force plan using query store rather than using plan guide (previously)
Able to alter procedures – this was not possible with plan guides
Monitor for failed forces (sys.query_store_plan)
Adjust Query Store configuration as ideal for your
environment
All Query Store data input into PRIMARY filegroup is a
constraint
Using Policy Based Management to monitor Operations
Mode (will revert to Read Only if hit size limit)
Single database granularity can be difficult with thousands
of databases
Plan forcing will not work on table type objects without
explicitly named indexes
Implemented as schema-only (no durability) memory-optimized tables in Data Marts
Originally implemented in SQL Server 2014
Gained stability in SQL Server 2016 around storage subsystem - checkpoint and file operations
Implemented in ENTDB and Product Listing
Replace disk-based table types with memory-optimized table types for use as Table Valued Parameters
Replace temporary tables with memory-optimized tables (reduce contention in tempdb)*
*Only use of natively compiled modules was for TVF for temp table RLS
See https://msdn.microsoft.com/en-us/library/mt718711.aspx for details
CPU reduction in ENTDBs Realtime rollout gains
• 2x performance gain through reduction in
• Avg exection times down from ~75 ms to 25 ms
procedure execution times despite <1ms disk IO
• “Consistency of performance post-deployment has
• Significant reduction in CPU usage (75% decrease)
been impressive”
Avg call time/sec before memoryoptimized TVP rollout
Avg call time/sec
after rollout
--== Alter procedure to use memory-optimized TVP
ALTER PROCEDURE C2AAuctionSetup.InventoryItem_GetAvailableQtyByDistCtrForItemID02RS (
@ProfileID INT,
@ClientDistCtrIDTable MemoryOptimized.IntegerUQTVP READONLY,
@ItemID INT,
@ClientID INT = NULL,
@ProfilePartitionID TINYINT = NULL )
Use resource governor to bind resource pool to user database
Maintain memory-optimized objects in specific schema
Separate mount points or drives for memory-optimized filegroups/files
Understand differences between non-clustered indexes and hash indexes
Often a hash does not make sense
Pay attention to bucket count with hash indexes (sys.dm_db_xtp_hash_index_stats)
Use ALTER INDEX … REBUILD WITH (BUCKET COUNT = N)
Do consider that non-clustered indexes are unidirectional
Lack of cross database queries restricts broader adoption
Common design pattern entails static data in separate subscriber database
Populating UI grids for actionable analytics
Primary workload categories – Campaigns, Ad Groups and Keywords
Business Problem: Data size and complexity of queries both growing over time
Business Requirement: Page loads always must be under 3 seconds
Implemented on 15 largest tables across 7 Data Marts – all share same schema
Range in size from 50M to 8B rows
SQL Server 2014 – Fact Tables using Clustered Columnstore
SQL Server 2016 – Adding Columnstore to large dimensions (between 500 and 775 million rows)
Space Savings
11TB across all data marts
& 4TB fact table reduced
to 600GB
Performance Gains
Campaigns
14% with SQL Server 2014
Additional 75% with SQL Server 2016
against larger data volumes and more
complicated queries
Ad groups
36% with SQL Server 2014
Additional 15% with SQL Server 2016
Keywords
No major change, mostly lookups
All page loads < 3 seconds response time
SQL Server 2016
Previously had implemented to address large number of open row groups (2014)
Data load closer to real-time
Able to do online maintenance for columnstore
REORGANIZE (WITH COMPRESS ALL ROW GROUPS ON)
Allowed for removal of deleted rows and avoid degradation over time
Other Considerations
Columnstore alone may not be the right index for all workloads
Tracking row group sizes is still important (even though 2016 has some improvements here)
sys.dm_db_column_store_row_group_physical_stats now contains trim reasons
Indirect checkpoint
https://blogs.msdn.microsoft.com/sqlcat/2016/08/03/changes-in-sql-server-2016-checkpoint-behavior/
Behavior change aligned with Trace flag 1117
functionality
Gains using native COMPRESS function
Insert large text data into columns using COMPRESS(ParametersGzipped)
Computed column to CONVERT(NVARCHAR(MAX),DECOMPRESS(ParametersGzipped))
200M row 1.38TB table reduced to 170GB in size with little performance impact
Scenario: Using public cloud for Disaster Recovery
Experiences:
SEEDING_MODE=AUTOMATIC
Able to utilize an automated process to add new replicas as new servers are added
Unfortunately not integrated into powershell cmdlets
Database seeding can fail with sparse error messages
Trace flag 9567 can be used to enable compression for direct seeding
BRK3282: Dive deep into application performance using Query Store in SQL Server 2016
and Azure SQL DB (Sept 29th 4-5:15PM)
BRK3030: Review Columnstore Index in SQL Server 2016 and Azure SQL Database (Sept
27th 9-10:15AM)
BRK3103: Review In-Memory OLTP in SQL Server 2016 and Azure SQL Database (Sept 27th
10:45-12PM)
BRK3097: Explore In-Memory OLTP architectures and customer case studies (Sept 29th
2:15-3:30PM)
BRK3094: Accelerate SQL Server 2016 to the max: lessons learned from customer
engagements (Sept 30th 12:30-1:45PM)
www.microsoft.com/itprocareercenter
www.microsoft.com/itprocloudessentials
www.microsoft.com/mechanics
https://techcommunity.microsoft.com
http://myignite.microsoft.com
https://aka.ms/ignite.mobileapp