SQL Server Performance Tuning Tips
Configuration
- Identify SQL Servers with inefficient power plans using Policy Based Management
- Improve SQL Server Table Variable Performance Using Trace Flag 2453
- Optimize for ad hoc workloads - at the database level - in SQL Server
- Set Statistics Time Examples for Tuning SQL Server Queries
- 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
Data
- Optimize Moving SQL Server Data From One Table to Another Table
- SQL Server Implicit Conversions Performance Issues
Data Types
Delete
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 Impact of Querying Unnecessary Columns
- 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
- How to Force a Parallel Execution Plan in SQL Server 2016
- 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
Monitoring
Overview
Parameter Sniffing
- Analyzing SQL Server Performance Impact of Parameter Sniffing
- How to find compiled parameter values for SQL Server cached plans
- Using a SQL Server Explain Plan to View Cached Query Plan Parameters in Management Studio
Perfmon
- Automate Performance Monitor Statistics Collection for SQL Server and Windows
- Automatically Generate Performance Counter Graphs with SQL Server 2016 and R
- 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
- Trending Buffer Pool Performance Using DMV sys.dm_os_performance_counters
- Windows Reliability and Performance Monitor to troubleshoot SQL Server
Performance
- Measure Delayed Durability impact in SQL Server 2016 and later
- Performance Tuning SQL Server Query without Execution Plan
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
- Different Ways to Flush or Clear SQL Server Cache
- Troubleshooting Excess Compilations in SQL Server Using the Plan Cache and PowerShell
Processes
Scripts
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 Incorrect SQL Server Table Statistic Estimates Can Cause Slow Query Execution
- 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
Storage
T-SQL
- Issue with UNION and columns with mismatched data types in SQL Server
- SQL Server Performance Comparison of TOP vs ROW_NUMBER
Temp Tables
TempDB
Tools
- Built in Performance Reports in SQL Server 2005
- Collect SQL Server Performance Counter Sets Using Logman
- Create a Process Dump of SQL Server using SqlDumper.exe Tool
- 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
- SQL Server Monitoring with SolarWinds Server and Application Monitor
- SQL Server Performance Monitoring Tools
- Using SolarWinds Database Performance Analyzer to Diagnose Performance Issues
Trace Flags
Transaction Logs
- Find Currently Executing SQL Server Queries Consuming Transaction Log Space
- Reduce SQL Server WRITELOG waits using Delayed Durability
- Troubleshooting SQL Server Transaction Log Related Wait Types
Troubleshooting
- Clone a SQL Server Database Using DBCC CLONEDATABASE
- Database Cloning in SQL Server for Troubleshooting and Diagnostics
- DBCC CLONEDATABSE Enhancements in SQL Server 2016 SP1
- Finding what SQL Server Trace Flags were enabled for a query
- First Steps for SQL Server Performance Troubleshooting
- SQL Server DBCC CLONEDATABASE Example
- 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
- Introduction to SQL Server Query Optimization with Database Engine Tuning Advisor
- SQL Server Database Engine Tuning Advisor for Performance Tuning
Tutorials
- SQL Server Query Performance Guidelines Tutorial
- Techniques For Improving SQL Query Performance - Indexing, Parameterization and Partitioning
Update
Wait Types
