Benutzer:MovGP0/SQL/SQL Server Performance Tuning

Programmierung

T-SQL / SQL Server

T-SQL Fundamantals Select • Programming • Pitfalls • Query from Multiple Tables • Groups and Summaries • Advanced Select • Aggregation and Windowing • Insert, Update, Delete • Strings • Date and Time • Numbers • Transactions, Locking, Blocking, Deadlock • Tables • Views • Large Tables and Databases • Indexes • Stored Procedures • User-Defined Functions and Types • Triggers • Error Handling • Query Performance and Tuning • Hints • Index Tuning and Statistics • XML • Files, Filegroups, Integrity • Backup • Recovery • Principals and Users • Securables, Permissions, Auditing • Objects and Dependencies SQL Server Performance Tuning Memory Performance Analysis • Disk Performance Analysis • CPU Performance Analysis • Baseline Creation • Query Performance Metrics • Query Performance Analysis • Index Architecture and Behavior • Index Analysis • Database Engine Tuning Advisor • Key Lookups • Statistics, Data Distributuon, and Cardinality • Index Fragmentation • Execution Plan Generation • Execution Plan Cache Behavior • Parameter Sniffing • Query Recompilation • Query Design Analysis • Reduce Query Resource Use • Blocking and Blocked Processes • Causes and Solutions for Deadlocks • Row-By-Row Processing • Memory-Optimized OLTP Tables and Procedures • Database Performance Testing • Database Workload Optimization • SQL Server Optimization Checklist Service Broker Message • Contract • Queue • Service • .NET Integration • Route • Security   Datatypes • Unit Testing • Dependency Injection • Partitioning •

SQL Server Performance Tuning

SQL Server Performance Killers

• Insufficient Indexing
• Inaccurate Statistics
• Improper Query Design
• Poorly Generated Execution Plans
• Excessive Blocking and Deadlocks
• Non-set-based operations (T-SQL Cursors)
• Inappropriate Database Design
• Excessive Fragmentation
• Nonreuseable excecution plans
• Frequent recompilation of queries
• Improper Use of Cursors
• Improper configuration of the database transaction log
• Excessive use or improper configuration of tempdb

Performance Counters

Dynamic Management Objects

SELECT dmo_pc.counter_name, dmo_pc.cntr_value, dmo_pc.cntr_type
from sys.dm_os_performance_counters as dmo_pc
where dmo_pc.object_name = 'SQLServer:General Statistics'

Referenzen

• Paul Randal: Survey results: Common causes of performance problems. 6. März 2011, abgerufen am 8. März 2015 (englisch). 
• SQLIO Disk Subsystem Benchmark Tool. Microsoft, abgerufen am 8. März 2015 (englisch). 
• How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem. Microsoft, abgerufen am 8. März 2015 (englisch).