Performance Tuning And Optimization Microsoft SQL Database

Performance Tuning And Optimization Microsoft SQL Database

• 1.
  By Raluca Marcu Performance TuningAnd Optimization Microsoft SQL Database
• 2.
  About me… Education &Certifications Experience - MSSQL DBA & Dev - 6+ years Contact
• 3.
  Performance Issues Logical Optimization AdministrativeOptimization  System Configurations  Capacity Planning  Monitoring & Maintenance – Indexes & Statistics Maintenance  DB Architecture  DB Design  Coding – Refactoring TSQL  Common Symptoms  Common Causes  Tools to use
• 4.
  Performance Issues -Common Symptoms CPU bottlenecks – Identify occasional peaks in processor vs. processor is constantly under pressure Memory bottlenecks – Can result in slow application responsiveness, overall system slowdown, or even application crashing Network bottlenecks – Hard to recognize and easy to confuse I/O bottlenecks – Manifested through long response times, application slowdowns and tasks time-outs Slow running queries
• 5.
  Performance Issues -Common Causes CPU bottlenecks – Insufficient hardware resources – Badly designed processes -> Query tuning, improving execution plans, and system reconfiguration Memory bottlenecks – Limitations in available memory and memory pressure – Poor indexing (table scans) Network bottlenecks – Overload on a server or network -> data cannot flow as expected I/O bottlenecks – Caused by slow hardware used, bad storage solution design, configuration – Unnecessary requests made by a database -> affects I/O traffic – Frequent index scans, inefficient queries, and out of date statistics Slow running queries – Missing indexes, poor execution plans, bad application and schema design
• 6.
  Performance Issues –Tools to use: DMVs Processor Pressure -> sys.dm_os_performance_counters • counter type -> values shown are cumulative since the last SQL Server start => re-run in about 10 seconds • Batch Requests/sec -> depends on hardware used -> should be under 1000 • SQL Compilations/sec -> less than 10% of Batch Requests/sec • SQL Re-Compilations/sec -> less than 10% of SQL Compilations/sec • Checkpoint pages/sec and Lazy writes/sec -> indicate whether dirty pages are flushed to disk too often • Lazy Writes/sec -> value should be below 20; value is constantly above => check Page Life Expectancy -> Values < 300 seconds => memory pressure
• 7.
  Performance Issues –Tools to use: DMVs CPU performance bottlenecks -> sys.dm_os_wait_stats
• 8.
  Performance Issues –Tools to use Performance Monitor Counters Description Recom mende d value Issue Processor Time CPU Time <80% constantly higher than 80% => processor is under pressure Memory Available KB Available Memory > 200 MB < 100 MB for long time => insufficient memory on the server Pages/sec shows the rate at which the pages are written from disk to RAM and read from RAM to disk > 50 >50 => intensive memory activity and possible overhead and memory pressure Buffer Cache Hit Ratio  shows the ratio of the data pages found and read from the SQL Server buffer cache and all data page requests > 90 If a page doesn’t exist in the buffer cache, it has to be read disk, which downgrades performance Average Disk Queue Length the average number of I/O operations that are waiting to be written to or read from disk and the number of currently processed reads and writes < 2 per individu al disk higher values => I/O bottlenecks Average Disk Sec/Read the average time in seconds needed to read data from disk < 8ms > 20ms => serious I/O issue Average Disk Sec/Write the average time in seconds needed to write data to disk < 1ms > 4ms => bad performance
• 9.
  Performance Issues –Tools to use SQL Server Profiler – Which queries, functions and stored procedures were executed – Track deadlocks
• 10.
  Administration Optimization –System Configurations ■ Install SQL Server on a 64bit system (never 32bit) ■ Max Server Memory – 80% -> 90% ■ Cost Threshold for Parallelism – between 20 and 50 ■ Transactional Log -> VLFs, default value, default auto- growth, maintenance, shrink
• 11.
  Administration Optimization –Capacity Planning ■ Random I/O separated from Sequential I/O ■ Separate large indexes and/or specific tables to separate disks ■ Data partitioning ■ Database auto-growth
• 12.
  Monitoring & Maintenance– Indexes & Statistics Maintenance ■ Maintenance Solution – Ola Hallengren (Free Solution) ■ Indexes ■ Statistics - DBCC SHOW_STATISTICS ■ Backup & Recovery Strategies – RPO & RTO – Always -> Create the recovery strategy and then work towards the Backups
• 13.
  Logical Optimization –DB Architecture ■ Data access – Proper indexing and defragmentation - Database Tuning Advisor's – Move TSQL code from app into db – no ORMs – Identify inefficient TSQL, re-factor ■ Scale-up vs scale-out: – https://www.youtube.com/watch? v=kZOREEkYJKs
• 14.
  Logical Optimization –DB Design 1NF (first Normal Form) Eliminate duplicative columns from the same table. Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key). 2NF (second Normal Form) Meet the requirements of 1NF. Remove subsets of data that apply to multiple rows of a table and place them in separate tables. Create relationships between these new tables and their predecessors through the use of foreign keys. 3NF (third Normal Form) Meet the requirements of 2NF. Remove columns that are not dependent upon the primary key. Normalization • 5 (+1) normal forms, noted 1NF to 5NF (+ 3.5NF or BCNF) • 3NF – eliminate redundant data, ensure data integrity and atomicity, eliminate data modification anomalies
• 15.
  Logical Optimization –DB Design De-Normalize: – Performance – in practice a fully normalized database may incur costly queries for assembling the required data. – Complexity – fully normalizing a database may lead to a big number of tables, making the database difficult to understand and maintain. – Project requirements – for some specific project types the effort of elaborating a good design is not justified. Caution: when de-normalizing a database, extra care must be taken about data integrity.
• 16.
  Logical Optimization –Coding ■ Query Plan: – Actual Query Plan vs. Estimated Query Plan – DBCC FREEPROCCACHE – Live example
• 17.
  Logical Optimization –Coding ■ Query Plan: – Statistics & Indexes
• 18.
  Logical Optimization –Coding
• 19.
  Logical Optimization –Coding Best Practices Triggers – Avoid using them – Never use the same trigger for different triggering events (Insert, Update, Delete) Views – Use SCHEMABINNDING option -> users will not modify the tables schema – Use views for writing queries that access columns from multiple tables Transactions – Handle errors (Try-Catch Block) – Try to avoid nested transactions – Reduce the time period of resource locking (begin -> commit/rollback fast) Stored procedures – Do not use "SP_XXX" as a naming convention – Use "Set Nocount On" to eliminate extra network trip – Use WITH RECOMPILE clause (EXECUTE statement-first time) when the index structure changes
• 20.
  Thank you… Q &A Time!
• 21.
  Q&A… 1. Big dataSQL server 2. How can get the best performing in .Net MVC 3. The Cluster deployment model for SQL? Optimal model with a hardware resources sufficient for the problems need large data storage, much access read-write – cluster failover 4. According to my experience there is a pretty common problem during removal must anticipate that retrieve data for a list such as a list of products. Here the problem is that we often have to run through a loop to get the data for each product and information relating to the product eg price, variant, size, images .... This will create numerous query. The usual way to do it will not always try to put away once all the data of all such products take the price of all products one by one, taking all the variant of a turn and when I need to show to get this data from memory without recall db This will limit the number of queries to the database. But to do so beautiful, are not a simple problem? Speakers said there are ways to do this are not you?