Nye teknikker for SQL Server Optimalisering

1. Nye teknikker for SQL Server Optimalisering Jon Jahren Produktsjef, Microsoft Norge jonjah@microsoft.com

2. Agenda • New and changed optimization features with SP2 • Best Practices Analyzer • DMV & DMVStats • Performance Dashboard • Plan Guides • LINQ • VSTS DBPro, aka “Datadude”

3. What Has Changed in SP2? • Customer Pain Points • Difficulty administrating multiple instances • Desire to customize Management Studio • Need for a best practices tool • SQL Server 2000 “take away” features • 3 New Features • Multi-instance administration • Custom Reports in Management Studio • SQL Server 2005 Best Practices Analyzer • VarDecimal storage format (EE) • 2 major improvements • Script Generation • Copy Database

4. Management Studio Reports • Custom reports • Open your own reports without installing SSRS • Standard Reports improved scalability • New Disk Usage reports have been tested on SAP • Improved discoverability • Reports can be accessed from Object Explorer context menu • Improved usability • Open reports in new window • Compare reports • List of most recently used

5. Best Practices Analyzer • Reduces time spent in diagnosing root cause of problems • Scans multiple servers remotely • Helps identify potential problems as defined by Microsoft • Educates users on best practices • Provides interactive reporting • Each rule has corresponding article

6. Better Together: System Center and SQL Server The SQL Server 2005 Management Pack monitors performance and availability of: • SQL Server agent • Database health and transaction log free space • SQL Server clustering and named instances • Local and remote connectivity • Security • SQL Server replication

7. Better Together: System Center and SQL Server • Management Pack Benefits: • Granular discovery, monitoring and reporting of SQLServer components • Client perspective of SQL database availability • Database space and growth monitoring • Replication discovery and monitoring • SQL agent job discovery and monitoring • Blocking processes, long running jobs and generalperformance monitoring of SQL • SQL Configuration monitoring • Granularity to change monitoring behavior on a perobject level

8. Troubleshooting: SQL2005 Perfmon SQL Trace (profiler) DBCC DMVs and DMFs DAC (Dedicate Admin Connection) DTA (Database Tuning Advisor) Physical Dump

9. Dynamic Management Views Also known as DMVs Expose server state as a relational rowset State is generally in memory (not persisted) Not new. DMVs in SQL2000 (sysprocesses) Low overhead (approx 2%) Many DMVs expose information that needs to be maintained anyway What’s new for SQL2005? Many more DMVs and a new framework

10. General Server DMVs and DMFs Server level dm_exec_* Execution of user code and associated connections dm_os_* low level system (server-wide) info such as memory, locking & scheduling dm_tran_* Transactions & isolation dm_io_* Input/Output on network and disks dm_db_* Databases and database objects Component level dm_repl_* Replication dm_broker_* SQL Service Broker dm_fts_* Full Text Search dm_qn_* Query Notifications dm_clr_* CLR execution of managed code

11. DMV Examples sys,.dm_io_virtual_file_stats Sys.dm_os_scheduler Sys.dm_tran_active_transactions Sys.dm_exec_query_stats sys,.dm_exec_requests Sys.dm_db_index_usage_statistics sys.dm_db_index_operational_stats Sys.dm_os_wait_stats

12. How to use DMVs Challenge Need to know a lot about SQL Internals Need to apply a specific methodology to identify problems Solution Use scripts for common performance issues Use DMVstats Tool

13. What is DMVstats? DMV collection, analysis, and reporting application Data collection managed by SQLAgent jobs DMVstatsDB - performance data warehouse repository of DMV statistics Analysis and reporting provided by DMVstats Reports

14. DMVstats Objectives Provide Expert Guidance from the experience, lessons learned, and best practices from Microsoft's SQL Server Development Customer Team. Methodology: Provides performance analysis by using the proven Waits and Queues Educate user

15. SQL Server Best Practices Site • 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/

16. Hints • SQL Server has three types are hints • JOIN hint • Use loop, hash, merge to join table • Can specify side for REMOTE join • QUERY hint • Many of these • TABLE hint • Use table scan • Use one or more indexes • Locking hints

17. Query Hints • { HASH | ORDER } GROUP • { CONCAT | HASH | MERGE } UNION • { LOOP | MERGE | HASH } JOIN • FAST number_rows • FORCE ORDER • MAXDOP number_of_processors • OPTIMIZE FOR ( @variable_name = literal_constant [ ,…n ]] • PARAMETERIZATION { SIMPLE | FORCED } • RECOMPILE • ROBUST PLAN • KEEP PLAN • KEEPFIXED PLAN • EXPAND VIEWS • MAXRECURSION number • USE PLAN N'xml_plan'

18. Beyond Query Hints • Query hints cannot be applied if you don’t have access to the source code • Dynamically generated • Purchased package • Query hints tie the hint to the source code • No straightforward way to disable • Always using procedures helps this • Enter plan guides…

19. Plan Guide • Plan guide is a named database object • Only available in Standard and Enterprise • Associates a hint with a query • Lives at database scope • Defined and maintained through system procs • sp_create_plan_guide • Can be enabled or disabled • sp_control_plan_guide • sp_create and sp_control flush appropriate query cache entries • sys.plan_guides holds plan guide metadata

20. Creating and Using Plan Guides • Query must match exactly • Including whitespace and CR/LF • Use profiler to capture query • Right click and choose Extract Command • Paste command into sp_create_plan_guide • Use XML query plan to ensure its working • Plan guide used is in query plan • Enabling plan guides clears plan cache

21. Plan Guides and Existing Hints • If the existing query uses a hint, the hint text must be specified in the plan guide • The plan guide hint(s) takes the place of the hint specified in the query • You can include the original hint in the plan guide if additive hints are desired

22. Plan Guides Best Practices • Plan guides "pin" an object, i.e. can't change until you drop the plan guide • Plan guides can be done anywhere you use a query hint • SELECT, INSERT, UPDATE, DELETE • Plan forcing - only SELECT, SELECT INTO • Text must match character for character • Cannot be used with encrypted objects or DDL triggers • A "few dozen" queries is limit of plan guides • Do not hand-edit XML showplans

23. Plan Forcing • Specified via USE PLAN query hint • Capture XML showplan • Paste into sp_create_plan_guide • Or paste into query directly • Plan guide not required • Make sure all single quotes are escaped • Plan forcing works with • SELECT • SELECT INTO

24. Plan Forcing Considerations • Isn’t plan forcing a security problem? • No, it must be • A plan that matches the query • A plan that the optimizer would normally consider • Is plan forcing faster because plan doesn’t need to be chosen? • No • The plan must be parsed and checked • It’s actually a little slower (optimization times are slightly increased) • Should I turn off statistics if I force plans? • No, stats are used for memory estimates

25. <book> <title/> <author/> <year/> <price/> </book> Relational Objects XML The LINQ Project C# 3.0 VB 9.0 Others… .NET Language Integrated Query LINQ toObjects LINQ toDataSets LINQ toSQL LINQ toEntities LINQ toXML

26. LINQ to SQLAccessing data today Queries in quotes SqlConnection c = new SqlConnection(…); c.Open(); SqlCommand cmd = new SqlCommand( @"SELECT c.Name, c.Phone FROM Customers c WHERE c.City = @p0"); cmd.Parameters.AddWithValue("@p0", "London“); DataReader dr = c.Execute(cmd); while (dr.Read()) { string name = dr.GetString(0); string phone = dr.GetString(1); DateTime date = dr.GetDateTime(2); } dr.Close(); Loosely bound arguments Loosely typed result sets No compile time checks

27. LINQ to SQLAccessing data with LINQ Classes describe data public class Customer { … } public class Northwind : DataContext { public Table<Customer> Customers; … } Tables are like collections Strongly typed connections Northwind db = new Northwind(…); var contacts = from c in db.Customers where c.City == "London" select new { c.Name, c.Phone }; Integrated query syntax Strongly typed results

28. LINQ to SQL Architecture Application from c in db.Customers where c.City == "London" select c.CompanyName db.Customers.Add(c1); c2.City = “Seattle"; db.Customers.Remove(c3); LINQ Query Objects SubmitChanges() LINQ to SQL SQL Query Rows DML or SProcs SELECT CompanyName FROM Cust WHERE City = 'London' INSERT INTO Cust … UPDATE Cust …DELETE FROM Cust … SQL Server

29. LINQ Entity Framework

30. Other names you might hear: • Data Dude • K2 • TSData • DBPro

31. Data Dude Features • Incorporate the Database Professional into the software lifecycle and provide them with a foundation for change management and process integration. • Change Management • Project Based Development • Project Model in Visual Studio with Support for Team Collaboration via Team Foundation Server Automated Change Support • Rename Refactoring, Schema & Data Comparison Tools, and Version Control • Database Unit Testing • Leverages the existing Test Project Infrastructure with support for data generation • Build / Deployment • MSBuild and Team Build integration

32. Workflow Writes Tests Writes DB Code Refactors Runs Tests Checks In Works with other developers to integrate Creates New DB Project Reverse Engineers DB to Project Creates Data Generation Plan Team Database Development with Data Dude Reviews Changes Compares Updates to Production Builds Deploy Package Deploys to Production Manage Develop Deploy DBA DB DEVELOPER DBA

33. Schema Change now managed in VSTS and TFS Production Database is now “One version of the truth” only for Data DBA doesn’t have access to changes until he/she has deploy or reject choice “One Version of the truth for Schema” is kept under Source Control Changes can be rolled out in a scheduled, managed way Scripts allow administrators to manage change updates Production Database SQL Server 2005 Management Studio Schema “One Version of the Truth” for Schema • Offline • Under Source Control Conceptual Overview Tuning Monitoring “One Version of the Truth” for Data Schema Changes

34. Data Generation • Rich engine allows for sample data generation • Useful because it’s often not feasible (or even legal) to use production data • Allows for repeatable test scenarios • Support for different types of generators • Simple for standard data types • Complex for regular expressions, foregin key lookup, data bound • And yes, you can write your own

35. Agenda • New and changed optimization features with SP2 • Best Practices Analyzer • DMV & DMVStats • Performance Dashboard • Plan Guides • LINQ • VSTS DBPro, aka “Datadude”