SEAS – 2006 One Year Later - What We Have Learned ?

1. SEAS – 2006One Year Later - What We Have Learned ? Mark Souza Director of Program Manager SQL Server team

2. SQL Server Product group: Customer and Partner teams • Customer Advisory Team - SQLCAT • http://blogs.msdn.com/sqlcat • Works with largest , most sophisticated customer deployments in the world • External ISV Team • http://blogs.msdn.com/mssqlisv • Works with strategic SQL Server ISVs and verticals (Healthcare, Finance and Manufacturing) • SQL Server development Customer Lab • Host many of these great customers and partners in B35 • Internal SQLCAT Partner team • Works with strategic Microsoft products using / shipping with SQL Server – Office, Windows, MBS, BizTalk, etc. • Best Practices team • Harnesses learnings from entire unit and produces deep technical outputs to SQL Server community • http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx

3. SQL Server Best Practices Site Announcing today on TechNet Get the real-world guidelines, expert tips, and rock-solid guidance to take your SQL Server implementation to the next level. http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx Contents Technical Whitepapers ToolBox Top 10 Lists Ask a Question Other Resources SQLCAT Blog: http://blogs.msdn.com/sqlcat/ SQL ISV PM Blog: http://blogs.msdn.com/mssqlisv/

4. Session Objective • Share our customer and partner experiences over the past year • Share some excitement about SQL Server deployments • Share some surprise learnings good and challenging • Derived from direct deep technical touch of customer applications • Sometimes features do exactly what you designed them to do and other times they don’t

6. Across the Product – Lessons Learned 6

7. There is no application in the world that can’t be implemented successfully on SQL Server 2005 • Core Trading systems • Replacing Tandem Non-Stop SQL systems • 10000(s) thousands to 100000(s) thousands database statements/second • Core Banking systems • Core Communication systems • Running one application on (8) 64-way Superdomes with SQL Server • Data Warehouse/VLDB • 20TB + application in production today

8. Excitement in the Pipeline • Currently designing a 270 TB Data Warehouse system • Across multiple nodes • Loads 3TB/day • Mainframe migrations • Claims Processing • Airline Reservation systems • Replacing one of the largest SAP/Oracle deployments in the world with SAP/SQL Server • Internal Microsoft Marketing Data Warehouse – 262 TB in 2 years

9. Example: Real Customer Design of 100TB+

10. Microsoft: Information Consolidated Event Management - 20 TB on 4 way! • Customer – Information Security Team • System used for: • Gathering data from 85+ MS proxy servers around the world • Rapid Security Analysis / Queries • Proxy performance analysis • Troubleshooting • Current System Description • 4 –way single core 2.2 GHz HP Proliant DL 585 G1, X-64 with 8 GB RAM • Database size is currently 20 TB and growing, hosted on 42 TB EMC Clarion • Loading daily ½ TB data daily average with peaks close to 1 TB • Up to 10 concurrent users executing ad hoc queries and reports • SSIS, RS and the DW on the same box

11. SQL Server 2005: Some nice surprises • ISVs adopting SS2005 faster than any prior release: • SAP in just 6 weeks • exploiting new features: snapshot isolation, plan guides, partitioning, etc. • Improved serviceability with DMVs • Example: Are my indexes being used? Declare @dbid int Select @dbid = db_id('Northwind') Select objectname=object_name(i.object_id), indexname=i.name, i.index_id from sys.indexes i, sys.objects o where objectproperty(o.object_id,'IsUserTable') = 1 and i.index_id NOT IN (select s.index_id from sys.dm_db_index_usage_stats s where s.object_id=i.object_id and i.index_id=s.index_id and database_id = @dbid ) and o.object_id = i.object_id order by objectname,i.index_id,indexname asc

12. SQL Server 2005: Some other surprises • Expectations don’t always align • Expectation is online utilities are 100% online • Online index requires schema lock at beginning and end • Online restore may require database lock at beginning • Expectation: Partitioning provides transparent offline/online access and maintenance • Expectation: 64bit performance always better • Expectation: Upgrade to SQL Server 2005 provides better performance

13. SQL Server 2005 – Some other surprises • Management studio takes some getting used to • A more sophisticated optimizer can mean more optimization time which can mean an increase in CPU • 100% of the new SQL Server 2005 query plans are not better than SQL Server 2000 • Not all components made it easy to upgrade (SSIS, SSRS) • SSAS re-design at high end may be recommended

14. The Upgrade Experience 14

15. SQL Server 2005 Upgrade – Some nice Surprises Upgrade does work Customer like having choices Side-by-Side vs. In-Place Backup vs. Detach/Attach Customers and ISVs seem to be adopting SQL Server 2005 at the high end faster than in previous releases. Upgrade Advisor helps validation BOL Upgrade related sections very thorough Frequent BOL updates are appreciated. 15

16. SQL Server 2005 Upgrade – Some other Surprises Overall Performance of customer/partner application after upgrade has been worse with SS2K5 in some cases See next slide General BI upgrade experience Tough decision for customers – Should they re-write their DTS packages or run in DTS mode in SS2005? Customer may be better off running in SS2K DTS mode DTS mode doesn’t run on IA64 SSAS on the high end recommends redesign in many cases Upgrade wizard is limited 16

17. Upgrade Lessons Learned: Overall SQL Server 2005 Performance After in Place Upgrades The lesson learned Upgrade to SQL Server 2005 on same the hardware can perform slower than SQL Server 2000 (without exploitation of new features) Major reasons seem to be: Query Plan changes (roughly 70-80% of the performance issues) Higher CPU utilization Memory Management Resolution/Workaround A lot of hard tuning work from CSS, SQL Customer/Partner team, SQL Development, MCS SP2 addressing cache memory management and SSAS performance New improvement in motion to address plan stability in Katmai 17

18. Upgrade lesson learned: High Cost of Name Resolution The lesson learned Upgrade from SQL Server 2000 to SQL Server 2005 performance can be slower 10-15% if user schema different than database schema user is accessing. Example: DBO Workload Characteristics OLTP, Sequential batches Many dynamic sql executions The higher cost of parsing and compiling is identified as the cause. Milliseconds on every query add up to be 10-15% overall slowdown. Resolution/Workaround Upgraded databases set the default_schema for the user to be the username to preserve SQL Server 2000 behavior. Most objects happened to reside in dbo… Setting the default_schema to dbo, improved parse and compile time dramatically for the app. Small correction needed to the rest of the app. 18

19. The High Volume OLTP Experience 19

20. SQL Server 2005 OLTP – Some nice surprises • Statement level recompile • Compilation was for entire Stored Procedure in SS2000 • Forced parameterization • The varying nature of input parameters makes it tough to find the right plan with SQL Server 2000 • NUMA • Clear choice - beyond 8 way shows difference. (lower CPU utilization and less memory used) • DB Mirroring has minimum performance impact • If the connection is fast and mirror IO subsystem can keep up. 20

21. SQL Server 2005 OLTP – Some nice surprises • CLR could improve performance • If used correctly: Leverage development language strengths over TSQL (reduction in lines of code) • Online indexing • It is much better than offline for concurrency • DBCC improvements (Showcontig) • Doesn’t need S lock anymore! • Supportability • DMVs, Trace (blocking can be reported) 21

22. SQL Server 2005 OLTP – Some other surprises • Query optimizer behavior changes • Good plan in SS2000 no longer in SS2005 • Intra-partition parallelism not supported • Change file group to read-only requires exclusive database access • Insert Performance and page split • Page split behavior changed to accommodate update and delete and causes slight degradation in insert. Not noticeable unless 1000(s)-10000(s) / second • Some schema changes block table access • E.g. adding new not-null column 22

23. SQL Server 2005 OLTP – Some other surprises • Updates on partitioned table are more expensive • Updates statistics on partition not supported • Modifications are maintained per partition and then added on each update to find out if recompile is needed • You can turn auto update statistics off to avoid but be careful because statistics depend on you manually • User schema separation may have performance impact • If user is not dbo or 2 part name is NOT specified for the object • Plan cache size performance impact • Storing zero cost plans in SQL Server 2005 • The memory allocation for plans are much larger especially with 64 bit • Searching 20+ GB of plan cache will take longer 23

24. SQL Server 2005 OLTP: Page Split and Insert Performance • The lesson learned • Heavy insert (15000 batch req/sec) to the same clustered index could experience some blocking by root pages split • Performance regression compared to SQL2000 under the same condition • Resolution/workaround • Application changes: • Change the clustered index to non-monotonically increasing value . • Change the workload to avoid high concurrent insert into the same clustered index table • Working on fix 24

25. The Data Warehouse Experience 25

26. SQL Server 2005 Data Warehouse – Some nice Surprises Partitioning Wide acceptance for scalability and manageability Piece meal backup/restore Great for scalability Analysis Services Key new features Better memory management and multi-instances Data Source Views and Unified Data Model Process partitions in parallel by default Proactive caching Many monitoring tools (profiler, more perfmon counters) Business Intelligence Studio 26

27. SQL Server 2005 Data Warehouse – Some nice Surprises Reporting Services key new features Support for MDX query builder Report builder, including Oracle direct support SSIS key new Features Control vs. data flow XML import task File Management tasks Better parallelism and memory management Meta data foundation for data lineage 27

28. SQL Server 2005 Data Warehouse – Some other Surprises Analysis Services Design High learning curve for SQL Server 2000 AS users at high end Deployment for large scale BI Customers want more sizing and load estimating tools Customers want an easier way to tune the server settings Management for SSAS Not as clear backup and restore strategy Scale out is a lot of work to set up Customers want more serviceability for aggregation planning and management Customer would like to view processing progress Performance – Challenging at times to do capacity planning for predictable multi-user performance levels Optimization options are not clearly visible and recognized by the AS users Not yet fully NUMA aware 28

29. SQL Server 2005 Data Warehouse – Some other Surprises Reporting Services Has had a couple issues with rendering export to Excel and PDF Upgrade for many SS2000 Reports a lot of work SQL Server Customers want automatic partition maintenance. Single DDL such as Drop partition vs manual running multiple DDL commands Many of the Oracle and Teradata 64-bit providers are not as well performing as you would like 29

30. Data Warehouse lessons learned - Star Joins The lesson learned Star Queries using efficient NonClustered Index seeks may be avoided in favor of a less efficient Clustered Index scans of large fact tables. Resolution/Workaround Index hints, Index Views 2 future improvements planned Indexing and index lookups when combined selectivity is high. Improving heuristics based on actual customer scenarios. Using bitmap filtering more aggressively. Helpful in medium selectivity scenarios. 30

31. Data Warehouse lessons learned: SSAS NUMA Memory Usage With PreAlloc The lesson learned: Operating system assigns threads to first NUMA node, and all allocations come from that node (Virtual Alloc). Later the node runs out of memory and all allocations are very slow. During processing server seems to hang on virtual alloc Usually on NUMA and large number of processors with large amount of data Resolution/Workaround: Solution introduced in SP2 for NUMA problem, customers need to call PSS/CSS. PreAlloc is a new configuration entry 31

32. Data Warehouse Lessons Learned:Lack of Intra-partition Parallelism • The lesson learned • Non-partitioned tables – degree of parallelism is determined by the number of available CPUs and DOP configuration • Partitioned tables – parallelism is driven by number of accessed partitions and limited by the number of CPUs • Except when we end up accessing only single partition and this is known at compile time • The worst case query that touches 2 partitions on 64-way machine => DOP = 2 • If the same query touches only 1 partition => DOP = 64 • Often happening on tables partitioned on dates when the query contains WHERE clause with BETWEEN two dates 32

33. Result Gather Streams Join Redistribute Parallel scan of order table “ord” where ord.no between (1237 and 1895) Lack of Intra-partition Parallelism Plan for Non-partitioned Table, DOP=4 SELECT ……FROM lin, ordWHERE lin.ordno=ord.no AND lin.ordno between (1237 and 1895) Parallel scan of lineitem table “lin” where lin.ordno between (1237 and 1895) 33

34. Result Gather Streams AND lin.ordno between (1237 and 1895) O1 L1 O2 L2 O3 L3 L4 O4 L5 O5 L6 O6 L7 O7 Lack of Intra-partition Parallelism4Plan for Partitioned Tables with 100 Partitions SELECT ……FROM lin, ordWHERE lin.ordno=ord.no Join … 34

35. Lack of Intra-partition Parallelism • Resolution/Workaround to avoid DOP=2(if needed) • Split the query to multiple sections, each working on single partition and then UNION ALL the result • SELECT ……FROM lin, ordWHERE lin.ordno=ord.noAND lin.ordno between 12345 and 16000UNION ALLSELECT ……FROM lin, ordWHERE lin.ordno=ord.noAND lin.ordno between 16001 and 18954 • On 4-way machine - each branch of the UNION ALL is run with DOP = 4 (if parallel plan is chosen by the optimizer for the branch) 35

36. The High Availability Experience 36

37. SQL Server 2005 High Availability – Some nice Surprises • DB Mirroring • Decision – shipping with SP1 a good one • Starting to get a lot of deployments • Peer to Peer Replication • Availability for reporting on almost real time data • Partitioning: very popular • Sliding window experience • Ability to add/delete large number of rows quickly • ISV Adoption – essential for SAP BI deployment in high end • On-line utilities: positive availability impact • No or minimal batch window in most mission critical applications • Always On Brand and Storage Partners • Ability to respond to some of customer’s support questions 37

38. SQL Server 2005 High Availability – Some other Surprises • Some very limited HA scenario(s) are challenging to meet • Restore Filegroup with concurrency • Optionally, restrict Partition access is desired at times • May need multiple technologies to achieve HA goals • Sometimes leads to third party solutions because of their simplicity in deployment and administration • A single SAN-based solution may be preferred over multiple solutions needed from SQL Server 2005 – example: Database Mirroring and Log Shipping • Difficult to keep Logins, jobs, etc. in synch on for second copy – database is not self contained • SSAS/SSIS have comparatively limited HA support due to limited Data Mirroring support 38

39. High Availability Lessons Learned: Restore Filegroup with Concurrency • The Lesson learned • Restoring a filegroup requires termination of all concurrent activity in the entire database • Problem for high availability in OLTP • Restoring filegroups requires a database lock • Resolution/Workaround • DBA writes scripts to create a temporary work database (Work), then restores the secondary filegroup to Work • After restoring and rolling forward in Work, DBA must copy the data from the table in the restored filegroup to the production DB filegroup • Process more complex for Partitioned tables • Multiple switches required 39

40. The ISV Experience 40

41. SQL Server 2005 ISV – Some nice Surprises • Good Adoption • Many ISVs already support SQL 2005 • ISVs Love Application Transparent Features • Read Committed Snapshot Isolation, Forced Parameterization and Plan Guides very well received • Increased Enterprise readiness with table partitioning • Less Time to Diagnose Problems • DMV’s (query stats, missing indexes) • Less Disparity Between Database Vendors • MARS, Snapshot Isolation • ‘Oracle-based’ ISVs porting apps to SQL 2005 faster and with less effort • 64-bit: Enabler of Massive Scalability • Transparent 32-bit to 64-bit transition

42. SQL Server 2005 ISV – Some other Surprises • Overall Performance may not be equal to SQL Server 2000 on some upgrades • Parameterized Plan Caching Still Causing Execution Issues • Oracle 64-bit Data Provider slow • Expectation that 64-bit is Generally Much Faster

43. ISV Experience lessons learned: Procedure Cache Growth • The lesson learned • Performance impact on certain high-end workloads • One challenge has been SQL 2005 plan cache changes resulting in • Out of Memory errors • Worse performance on 64-bit vs. 32-bit platform • Worse performance on SQL 2005 vs. SQL 2000 • Resolution/Workaround • SP2 addresses 4 main areas: • We evict plans much faster • We cap the procedure cache much more aggressively on high-end machines leaving more memory for data pages • Certain zero cost plans do not get cached at all • We are also adding fairness in NUMA configurations for cleanup

44. ISV experience lessons learned: Parameter Sniffing • The lesson learned • ISVs generate generic SQL based on application end-user input • Parameterized queries are generally used to aviod excessive compilation against all database platforms • First invocation with non-representive parameter values can cause major problems • Resolution/Workaround • No bulletproof solution – just be cognizant. • Discussing future resolutions in development

45. Miscellaneous

46. SQL Server 2005 Misc – Some nice Surprises • 64-bit • Field acceptance is accelerating • x64 (WoW & native), IA64 Native, NUMA aware • Great performance results • Benchmarks (TPC-C, SAP SD), Customers • Code-compatibility with 32-bit • TSQL, on-disk format, backup/restore • Service Broker – great first start, good performance, good documentation and examples • CLR Performance (when used for right solution) • CLR flexibility for real world usage • XML • Data Type foundation • FOR XML query improvements • XPATH and XQUERY extensions available

47. SQL Server 2005 Misc – Some other Surprises • The lesson learned • Is 64 bit always faster than 32bit? • Perception that 64-bit is “always faster” (not true) • Magnified certain RTM problems • Plan caching problems) • Tools and debugging on IA64 • Some Partners not supporting 64-bit yet • ISV’s slow adoption due to customer confusion • Controlling Foreign memory allocations • Resolution/Workaround • Some customers run 32-bit SQL Server on 64-bit OS (x64)

48. Summary Based on real Customer Workload Based mostly on high end workload