- Check indexing: Ensure that the tables being queried are properly indexed. Without proper indexing, SQL Server will need to scan the entire table to return the required results.
- Check server resources: Ensure that your server has enough resources (CPU, memory, and disk) to handle the queries. If the server is overloaded, queries will run slower.
- Check for blocking: Check if there are any blocking issues in the database. Blocking can cause queries to run slower or not run at all.
- Check for parameter sniffing: Parameter sniffing can cause a query to run slower if the parameters provided to the query are not optimal for the query plan.
- Check for statistics: Ensure that the statistics for the tables are up to date. SQL Server uses statistics to determine the best execution plan for a query.
- Check for long-running transactions: Long-running transactions can cause performance issues. Make sure that transactions are committed or rolled back as quickly as possible.
- Check for network latency: If the database server is located in a different location, network latency can cause the queries to run slower.
- Check for Missing Indexes – BUT DO NOT CREATE THESE INDEXES DIRECTLY AND COME TO CONCLUSION. Before creating any new index analyze the average estimated impact, the predicates in the queries.
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','')
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO
- Check for Duplicate Indexes, it can waste precious SQL Server resources and generate unnecessary overhead causing poor database performance.
select t1.tablename as TableName,t1.indexname as IndexName,t1.columnlist as ColumnsList,t2.indexname as IndexName,t2.ColumnList from
(select distinct object_name(i.object_id) tablename,i.name indexname,
(select distinct stuff((select ', ' + c.name
from sys.index_columns ic1 inner join
sys.columns c on ic1.object_id=c.object_id and
ic1.column_id=c.column_id
where ic1.index_id = ic.index_id and
ic1.object_id=i.object_id and
ic1.index_id=i.index_id
order by index_column_id FOR XML PATH('')),1,2,'')
from sys.index_columns ic
where object_id=i.object_id and index_id=i.index_id) as columnlist
from sys.indexes i inner join
sys.index_columns ic on i.object_id=ic.object_id and
i.index_id=ic.index_id inner join
sys.objects o on i.object_id=o.object_id
where o.is_ms_shipped=0) t1 inner join
(select distinct object_name(i.object_id) tablename,i.name indexname,
(select distinct stuff((select ', ' + c.name
from sys.index_columns ic1 inner join
sys.columns c on ic1.object_id=c.object_id and
ic1.column_id=c.column_id
where ic1.index_id = ic.index_id and
ic1.object_id=i.object_id and
ic1.index_id=i.index_id
order by index_column_id FOR XML PATH('')),1,2,'')
from sys.index_columns ic
where object_id=i.object_id and index_id=i.index_id) as columnlist
from sys.indexes i inner join
sys.index_columns ic on i.object_id=ic.object_id and
i.index_id=ic.index_id inner join
sys.objects o on i.object_id=o.object_id
where o.is_ms_shipped=0) t2 on t1.tablename=t2.tablename and
substring(t2.columnlist,1,len(t1.columnlist))=t1.columnlist and
(t1.columnlist<>t2.columnlist or
(t1.columnlist=t2.columnlist and t1.indexname<>t2.indexname))
- Ensure procedures and queries with basic information like connected module, author, description, Create Date , Modified Date, etc. à This is a best practice to do so
- Use Stored Procedures over Ad-Hoc Queries
- Define Data Types properly considering its length and type to avoid Implicit Conversion Issues. The below query will help in identifying where implicit conversion is happening.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @dbname SYSNAME
SET @dbname = QUOTENAME(DB_NAME());
WITH XMLNAMESPACES
(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
stmt.value('(@StatementText)[1]', 'varchar(max)')as SQLQuery,
upper(t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')) as ObjSchema,
Upper(t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)')) as ObjectName,
Upper(t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)')) as ColumnName,
Upper(ic.DATA_TYPE) AS ConvertFrom,
ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength,
Upper(t.value('(@DataType)[1]', 'varchar(128)')) AS ConvertTo,
t.value('(@Length)[1]', 'int') AS ConvertToLength,
Query_Plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt)
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') AS n(t)
JOIN INFORMATION_SCHEMA.COLUMNS AS ic
ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')
AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)')
AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)')
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1
- Avoid using un-necessary joins
- Avoid using un-necessary functions like left(ColumnName,2) when the same information is available in another column (Example :- Left(DistrictCode,2)=State Code ? This Column is already available)
- Avoid using too many where clause while selecting and updating for data quality check when the same data quality checks were already applied during insertion time à Recently fine tune a query which reduces the execution time from 5-7 min to ~10 Seconds
- Avoid Distinct instead use some additional field for uniqueness
- Use proper database types for the fields. If StartDate is database filed use datetime as datatypes instead of VARCHAR (20)
- Using JOIN is better for performance than using sub queries or nested queries.
- Use IF (Exists) instead of Count(*). In situation when records are present or not in a table
- Set NoCount ON” in Stored Procedures if processing rows are not required to capture
- Avoid Cast Operators à Upper, Convert, Cast etc.
- Use where clause instead of having clause.
- Avoid NOT IN, instead use a left outer join.
- If this is mandatory to use Dynamic SQL (Executing a Concatenated String), use named parameters and SP_EXECUTESQL (rather than EXEC) so you have a chance of reusing the query plan
- Avoid concurrent operations like Bulk Insert, Update and select on same object
- Tries to limit the size of transactions à Avoid using large update in one single Batch
- What to check in Execution Plans
- Check each operator cost and while working on execution plans focus Table\Index Scans\Spools\Sort Warnings\cardinality estimation warnings\sort warnings\tempdb spill over (try to avoid sort operator and save data in correct order)\key lookups\bookmark lookups
- An optimal covering\non clustered index can solve multiple issue
- While creating non clustered index be very sure what to put in index and what should be added in included columns à INCLUDE clause adds the data at the lowest/leaf level, rather than in the index tree. This makes the index smaller because it’s not part of the tree. Use Include If the column is not in the WHERE/JOIN/GROUP BY/ORDER BY, but only in the column list in the SELECT clause.
What to check in Execution Plans
- Check each operator cost and while working on execution plans focus Table\Index Scans\Spools\Sort Warnings\cardinality estimation warnings\sort warnings\TempDB spill over (try to avoid sort operator and save data in correct order) \key lookups\bookmark lookups.
- An optimal covering\non clustered index can solve multiple issue
- While creating non clustered index be very sure what to put in index and what should be added in included columns à INCLUDE clause adds the data at the lowest/leaf level, rather than in the index tree. This makes the index smaller because it’s not part of the tree. Use Include If the column is not in the WHERE/JOIN/GROUP BY/ORDER BY, but only in the column list in the SELECT clause.
Check for parameter sniffing. Parameter sniffing will usually lead to large CPU, IO and Duration
• DECLARE @cpufactor INT
DECLARE @iofactor INT
DECLARE @durationfactor INT
SET @cpufactor=100 /* 0 disables*/
SET @iofactor=100 /* 0 disables*/
SET @durationfactor = 100 /* 0 disables*/
SELECT Substring(ST.text, ( QS.statement_start_offset / 2 ) + 1, ( ( CASE
statement_end_offset
WHEN -1 THEN Datalength(ST.text)
ELSE QS.statement_end_offset
END -
QS.statement_start_offset ) / 2 ) + 1) AS statement_text,
ST.text AS 'full batch',
query_hash,
query_plan_hash,
*
FROM sys.dm_exec_query_stats AS QS
CROSS apply sys.Dm_exec_sql_text(QS.sql_handle) AS ST
WHERE @cpufactor * min_worker_time < max_worker_time
AND @iofactor * min_logical_reads < max_logical_reads
AND @durationfactor * min_elapsed_time < max_elapsed_time
- Which wait is hurting most https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
- Checking IO Latency https://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/
The above-mentioned steps will surely help in fine tuning the queries.
coresql.in 