Valuable SQL Server Performance Tuning tips, tutorials, how-to’s, scripts, and more for SQL Server DBAs.
Источник: SQL Server Performance Tuning Tips
Очень любопытный сайт, прекрасный набор ценных советов в плане производительности. Большая часть давно известна, но некоторые вещи любопытны.
Configuration
- Identify SQL Servers with inefficient power plans using Policy Based Management
- SQL Server Simple and Forced Parameterization
- Using DBCC DROPCLEANBUFFERS When Testing SQL Server Performance
CPU
- Find out which SQL Server instance is consuming most of the CPU
- How to find out how much CPU a SQL Server process is really using
- How to Identify SQL Server CPU Bottlenecks
- Testing SQL Server Query Performance Using Different Levels of Parallelism
Development
- Avoid SQL Server functions in the WHERE clause for Performance
- Collecting and Storing Poor Performing SQL Server Queries for Analysis
- Convert Implicit and the related performance issues with SQL Server
- Do SQL Server User Defined Datatypes (UDT) affect performance?
- Improving SQL Server performance when using table variables
- Removing Function Calls for Better Performance in SQL Server
- Speed up SQL Server queries with PREFETCH
- SQL Server 2008 64bit Query Optimization Trick
- SQL Server performance tuning for each layer of an application
- Trick to Optimize TOP clause in SQL Server
- Using schema binding to improve SQL Server UDF performance
Hints
- Disabling SQL Server Optimizer Rules with QUERYRULEOFF
- Enabling SQL Server Trace Flag for a Poor Performing Query Using QUERYTRACEON
- Optimize Parameter Driven Queries with SQL Server OPTIMIZE FOR Hint
- Why the SQL Server FORCESCAN hint exists
Indexing
- Identify SQL Server Query Plans with Scans on Nonclustered Hash Indexes
- SQL Server Performance Tuning with Hypothetical Indexes
- SQL Server Sargability – Queries on an Indexed Table Sometimes have Slow Performance
- SQL Server Schema Binding and Indexed Views
IO
- Gather IO statistics down to the SQL Server database file level
- Getting IO and time statistics for SQL Server queries
- How to Identify IO Bottlenecks in MS SQL Server
- Perfmon Counters to Identify SQL Server Disk Bottlenecks
Locking
- Finding SQL Server Deadlocks Using Trace Flag 1222
- Identifying Key and RID Lookup Issues and How to Resolve
- Remove some SQL Server blocking issues with the NOLOCK hint
Memory
- How to Identify Microsoft SQL Server Memory Bottlenecks
- Tracking Query Statistics on Memory Grants and Parallelism in SQL Server 2016
- Troubleshooting SQL Server RESOURCE_SEMAPHORE Waittype Memory Issues
Parameter Sniffing
Perfmon
- Automate Performance Monitor Statistics Collection for SQL Server and Windows
- Collecting performance counters and using SQL Server to analyze the data
- Correlating Performance Monitor and SQL Server Profiler Data
- Creating SQL Server performance based reports using Excel
- How To Collect Performance Data With TYPEPERF.EXE
- Setup Performance Monitor to always collect SQL Server performance statistics
- SQL Server Database Specific Performance Counters
- System Monitor (Perfmon) Counters for SQL Server 2005
- Trending Buffer Pool Performance Using DMV sys.dm_os_performance_counters
- Windows Reliability and Performance Monitor to troubleshoot SQL Server
Plan Cache
- Always have a good plan! What’s in your SQL Server Plan Cache?
- Analyzing SQL Server Plan Cache Performance Using DMVs
- Analyzing the SQL Server Plan Cache
- Troubleshooting Excess Compilations in SQL Server Using the Plan Cache and PowerShell
Processes
Servers
Shrink
SQLDIAG
- Introduction to SQLDIAG for SQL Server Performance Monitoring and Tuning
- Tool to help you analyze SQL Server SQLDIAG and PSSDIAG output
Statistics
- How to create a SQL Server Clone Statistics Only Database
- Interesting example of statistics and index usage on a SQL Server computed column
- Issues Caused by Outdated Statistics in SQL Server
- NORECOMPUTE option of UPDATE STATISTICS in SQL Server
- Performance Advantages of SQL Server Filtered Statistics
Tools
- Built in Performance Reports in SQL Server 2005
- Creating a Centralized Performance Collection Solution for SQL Server
- Custom Solution to Measure SQL Server Query Performance Improvements
- More intuitive tool for reading SQL Server execution plans
- Quickly Pinpoint SQL Server Performance Issues with SolarWinds Database Performance Analyzer
- SQL Server Monitoring with SolarWinds Server and Application Monitor
- SQL Server Performance Monitoring Tools
Transaction Logs
Troubleshooting
- First Steps for SQL Server Performance Troubleshooting
- SQL Server stored procedure runs fast in SSMS and slow in application
- Troubleshooting Performance Problems in SQL Server 2005
- Tuning SQL Server Code with T-SQL Commands
- Usage of DBCC OPTIMIZER_WHATIF for SQL Server Query Tuning
Tuning Advisor
- Error running the SQL Server Database Tuning Advisor for non SA users
- SQL Server Database Engine Tuning Advisor for Performance Tuning
Tutorials
- SQL Server Graphical Query Plans Tutorial
- SQL Server Query Performance Guidelines Tutorial
- Techniques For Improving SQL Query Performance – Indexing, Parameterization and Partitioning
Wait Types