General hints
For execution plan graph:
- Find operators with high estimated cost within a query.
- Unless there is no filtering generally table or index SCAN is bad. Maybe you need to provide clustered index.
- Thick lines of data in execution plan maybe bad. Especially if they end up to result.
- Missing index warnings
Table scan - bad
Clustered index scan - better -> occurs when there is no WHERE
Clustered index seek - best -> occurs when there is clustered index
Non-clustered index is same idea. Scan - bad -> seek good.
When creating composite indexes sometimes mere change of places of columns in index will hint SQL to use created non-clustered index and perform Seek.
Observe logical reads. When using seek it should go dramatically down since using B+ tree.
Bottom line is to observe WHERE expression and play with indexes.
Key lookup is bad -> use covering index or included columns index
Use
SARGable predicates (WHERE or ON expression etc.)- don't use UDF's or LIKE. If not sure take a look at execution plan. If you have index on columns and Index seek is not used then you are NOT using SARGable predicate Also look for mentioning of RESIDUAL predicate. If it is mentioned in JOIN operators or elsewhere then you are not using SARGable predicate. Optimizer will always try to "push" your predicates on index level and perform seek. These will then be seek predicates.
Execution plan - conversions
When you observe any kind of conversion in "Predicate" section of info for operator (Scan, Seek, Join) you should research. Key problem is that optimizer will NOT use SEEK on existing index but SCAN.
Either you used UDF explicitly or query optimizer concluded it should convert something implicitly.
For example: SELECT Title FROM Books WHERE Id = '112'
Since Id is INT implicit conversion occurs and index cant be used.
UDF functions
Use of system or custom UDF brings performance down. Solution is to create computed column with UDF function and nonclustered index on it.
Use most selective column as first column in index!
Dynamic SQL
sp_executesql - execution plan is cached, parametrized
exec - not cached, not safe
Don't use * in Views !
CREATE VIEW ProductVIEW
AS
SELECT * FROM Product
ALTER TABLE PRODUCT
ADD DUMMY VARCHAR(50)
--Dummy is missing !
SELECT * From ProductVIEW
--Must explicitly refresh view
EXEC sp_refreshview 'ProductVIEW'
SQL Server performance
Activity monitor -> waits & recent expensive queries
Look for blocks and deadlocks.
Columnstore indexes
Introduced in 2012 only as nonclustered. In 2014 added support for clustered.
Use them for scenario with heavy READ and Scan operation. It has penalty for CRUD.
Must be dropped for clustered and recreated.
Logical reads vs physical reads
Logical is accessing pages from memory while physical is reading from HD and is MUCH more expensive.
Every l
ogical read is reading one complete page.
Use these two to get exact time and resources used to execute query
SET STATISTICS IO ON/OFF
SET STATISTICS TIME ON/OFF
; primary we use these to measure logical reads.
SET SHOWPLAN_XML ON - useful if you need to search for some text since this dumps in XML.
Usefull toll for SSMS to analyze execution plan:
https://www.sentryone.com/plan-explorer
To get estimated and actual rows returned you can use this:
SET STATISTICS PROFILE ON
....
SET STATISTICS PROFILE OFF
Estimate vs Actual
You want to compare estimates to actual rows returned in order to pinpoint bad statistics on certain database element. This will result in poor execution plan and poor performance.
Frequency of updating statistics can be adjusted to resolve this issue.
UDF's can't be estimated by query optimizer ! They spoil info about time consumed and relative batch. Use SET STATISTICS IO ON instead.
Memory grant
For some operators data has to be cached and extra memory is required. Optimizer tries to estimate and grant memory for operator. Example is Hashed match and Sort. You'll observe this info in SELECT (most left operator). It's not desirable to have under-estimates or over-estimates. Under-estimate for memory can result in accessing TEMPDB and destroy performance. Over-estimate will take too much resources and degrade concurency.
Hashed match, Sort - usual culprit for memory consumption
Helper to list all existing indexes on table: exec
sp_helpindex 'TABLENAME'
Join operator "Nested loop"
Top (on graph up) is outer table. Each row in outer is iterated over every element on inner, bottom (on graph down). Optimizer will choose optimal (smaller) table (rowset) as outer table. Order of JOIN is IGNORED! There are hints to force order but it's best to ignore them.
This type of join is generally expensive. It happens when optimizer doesn't have optimized sorted input for JOIN so it decides to iterate. Observe both outer and inner table and see if you can create index to cover this join.
Watch for index scan in inner table especially for under-estimate of CE.
Join operator "Merge join"
Generally this is optimal type of join. It's low on memory. Optimizer found indexed, sorted inputs for outer and inner table. Sometimes optimizer may create sorting on the fly to support Merge join. If statistics are ok then its probably the best way to join. If we know better we could force Nested loop. Again better approach is to look at stats and indexes and try to help optimizer instead of forcing join operator.
Watch for spill over in TEMPDB during injected sort.
Cardinality estimation (CE)
In short CE is logic used to calculate query plan based on statistics. CE is one of major differences between SQL versions. Using different compatibility level will probably result in different CE performance.
https://docs.microsoft.com/en-us/sql/relational-databases/performance/cardinality-estimation-sql-server
In practical sense, as above article elaborates, we need to make sure that CE works fine. Put it simply if you observe in estimated query plan
estimated rows or memory grant that is way off from
actual rows and memory you have problem. Solution is usually in dealing with statistics.
Statistics
SQL is auto-updating stats when CRUD operation happens in these case:
As data changes in your tables, the statistics – all the statistics – will be updated based on the following formula:
When a table with no rows gets a row
When 500 rows are changed to a table that is less than 500 rows
When 20% + 500 are changed in a table greater than 500 rows
Here is how you can show statistics for an index:
exec sp_helpindex 'employee'
go
dbcc show_statistics (employee, 'PK__Employee__7AD04FF17E197905')
go
Here is how you can force full stats rebuild:
update statistics Employee
with fullscan
Watch it! Fullscan takes time and resources and it is auto executed during index rebuild.
Never update stats AFTER index rebuild since it will degrade already performed fullscan action during rebuild.
You can turn off, change frequency or sampling size for auto stats.
Good FAQ on stats:
https://www.red-gate.com/simple-talk/sql/performance/sql-server-statistics-questions-we-were-too-shy-to-ask/#1
Sort operator
Query optimizer will try to avoid explicit sorting. Usually it points to problem. Inspect is ORDER BY required and can you create or update existing index to support sorting.
ORDER BY can be very expensive!
Join operator "Hash join"
Memory consuming. So called stop & go operation. Selected for unsorted input. Optimizer assumes that injecting sorting and resorting to nested loop wan't help in overall cost so it resorts to hash match. In first phase on outer table hash keys are created based on row values. Then these hash keys are one by one matched to hashes created on fly in inner table. First phase is called "Build" and second is "Probe".
Watch for "fat" outer table. Optimizer should know to choose proper "thinner" candidate for inner table. If fails look into it.
Also pay attention to spill over to disk. From SQL 2012 you'll see a notification in execution plan.
If you use older versions checkout SQL Trace and Extended Events. Spill over is major issue since it indicates that physical reads from TEMPDB occured. Cause of problem is in CE.
Parallelism
You can hint that you don't want it using DOP hint (degree of parallelism). Not good or bad. It consumes resources.
Parameter sniffing
Related to executing stored procedures with parameters. Optimizer creates cached plan for value of parameter and then uses the same when stored proc gets executed with different value for parameter.
In some cases this results in long execution of stored proc for some parameter values. Why? Imagine table with 10 million rows. If you try to filter by parameter using stored proc 1000 rows then plan will be optimized expecting 1000 rows. When afterwards you use it to filter parameter value that should return 1 million rows another query plan would be more suitable but query optimizer won't create it. It will use existing cached that was optimized for 1000 rows.
https://www.mssqltips.com/sqlservertip/3257/different-approaches-to-correct-sql-server-parameter-sniffing/
