Here are some things to look at when you try to improve query performance , lets start with what each of the terms that show in the query plan means
index seek – reads portion of the index which contains the indexed data
index scan – reads the entire index for the needed data
table scan – read the entire table for the needed data
key lookup – looks up value row by row , this happens when the index seek does not have enough information , so it needs to lookup based on the key from the clustered index, this is an expensive operation. One option is to add the missing columns to the index , so that index seek would take care of it.
Table valued functions – these functions return tables , think of it as views that accept parameters – these are good for low row counts but could affect performance, the stats are not available to the optimizer so test the performance when using these.
set showplan_all on – shows execution plan as text
set statistics io, time on – gets more details in the sql execution plan – you dont have to over the step
use the include syntax to add more columns to the index , so you can reduce the key lookup to index seek – this goes in the index key column and the included column.
nested loops – performs inner , outer ,semi and anti semi join . perform search on the inner table for each row of the outer table
Hash match – creates a hash for required cols for each row
other operations – sort
query store – data collection tool , shows queries that have regressed
alter database yourdb SET QUERY_STORE = ON
…SET QUERY_STORE ( OPERATION_MODE = READ_WRITE)
…SET COMPATABILITY_LEVEL = 100
Use the option within stored procedure
fragmentation greater than 30% then rebuild index
look at sys.database_files
sys.dm_db_file_space_usage
sys.dm_db_log_space_usage
sys.dm_exec_query_stats
sys.dm_exec_sessions
sys.dm_exec_connections
sys.dm_db_index_physical_stats
if user scans, user seeks ,system scan , seeks are 0 , its good candidate to drop
sys.dm_db_index_operational_stats
sys.dm_db_index_usage_stats