SQL performance tuning checklist
1 Sanity check
2 Join statements
3 Select Statement improvement
4 Error handle
5 Create table
6 Order by clause
7 Temp objects
8 Null Handling
Checklist items
1 Is ISG Application Naming convention followed? With necessary comments provided
2 Is unit testing result provided with expected functionality output?
3 Is sensitive datas scrambled in query output (Test environment)?
4 Is limited number of temp table used in places to filter record and join to next table
5 Is the query creating more rows than expected?
6 Generate Actual Execution Plan result attached?
7 Is SET NOCOUNT ON for stored procedure performing a lot of updates/deletes/inserts?
8 use DDLs first at the top section of SP, than use DMLs in below section
9 Is cursor not used / if yes remove and suggest for join
10 fields that are not defining actual JOIN predicate should be pushed to WHERE
11 Remove complex business logic in join queries
12 Avoid distinct in full join( use Rank, Rownumber)
13 Add common column in table to avoid join
14 Is Database Engine Tunin Advisor suggest any create / remove index?
15 Is On clause used in join conditions instead of where clause
16 Is select * avoided and needed column is selected to pass to front-end
17 Is select narrowed down (Where clause used)?
18 Select with NOLOCK on table with low frequent updates / insert / delete
19 Use “WHERE EXISTS (SELECT * FROM <table>)” while evaluating Sub-Queries, and use “IN()” only while dealing with constant values.
20 sub-queries as part of SELECT, re-write using JOIN or CROSS APPLY
21 accessing same table more than once in a sub-query consider replacement with CTE instead
22 Is Try Catch block used to handle error? Error functions ERROR_NUMBER, ERROR_LINE, ERROR_MESSAGE, ERROR_SEVERITY, ERROR_PROCEDURE and ERROR_STATE
23 usage of IF @@ERROR, GOTO to be avoided
24 Avoid using LOWER or UPPER built-in functions when trying to achieve case-insensitive compare; specify COLLATE SQL_Latin1_General_CP1_CI_AS explicitly instead
25 Is correct type & nominal length provided for columns?
26 IN (…) or NOT IN (…), consider replacing with straight JOIN and/or IF EXISTS
27 Use SET command to assign value to variables
28 usage of temp tables vs. table variables (if expect row is more than 200 to be temporarily stored – use local temp table; less than 200 – consider table variable
29 If SP returns multiple datasets – split into multiple SPs
30 SP should not use global ## temp tables
31 Avoid data being implicitly converted between types. Implicit conversions can have unexpected results, such as truncating data or reducing performance.
32 Verify temp table columns of “varchar/nvarchar” data type – they should be created with “collate database_default” modifier to avoid errors if joins are performed on them in case-sensitive databases
33 Verify usage of temp table columns – i.e. if columns are not subsequently used – remove them.
34 Verify temp table columns data types – they should match the data type of base tables you’re populating them from.
35 Use COALESCE() instead of ISNULL(), ensure COALESCE is always used to handle records which in fact have NULLs.
36 Never use ISNULL() on the BIT data type, as it only accepts 1/0/NULL as the possible values.
37 use TRY_PARSE() instead of ISNUMERIC()
38 Avoid sub-queries, use CTEs for better code manageability and readability
39 Handle Divide-by-zero errors the columns/variables occurring in denominator.
40 Any temporary objects used, if yes make sure these objects closed / removed once their usage is done.
41 Avoid triggers for better data flow and reduce overhead, use Output clause which can be controlled easily
42 Ensure a column in each table is designated as the primary key and select an appropriate data type such as an integer
43 Foreign Keys - Validate all of the foreign keys are established to maintain referential integrity among your tables to ensure the proper relationships are maintained.
44 GROUP BY <number> clausedeprecated, use proper GROUP BY <column>
45 Use default order of data, minimize order by statement use column instead of number to order
46 record existence should be coded using IF EXISTS (…), not IF SELECT COUNT(*)>0
47 Check for scalar UDF usage in select and where clause - number of function calls to be minimized
48 Verify usage of UNION vs. UNION ALL; if you’re bringing different record types that are already unique, use UNION ALL
49 Verify usage of DISTINCT – are duplicate rows coming as a result of missing JOIN predicate or data quality issue
50 UNION, DISTINCT, GROUP BY may require SQL Server to perform data sorting in memory, and this could lead to performance degradation
51 SQL Server is not suited for intense string manipulations – it should be handled in application side
52 Avoid unnecessary loops for Email data, email to be moved to front-end
53 Using parentheses, even when they are not required, can improve the readability of queries
1 Sanity check
2 Join statements
3 Select Statement improvement
4 Error handle
5 Create table
6 Order by clause
7 Temp objects
8 Null Handling
Checklist items
1 Is ISG Application Naming convention followed? With necessary comments provided
2 Is unit testing result provided with expected functionality output?
3 Is sensitive datas scrambled in query output (Test environment)?
4 Is limited number of temp table used in places to filter record and join to next table
5 Is the query creating more rows than expected?
6 Generate Actual Execution Plan result attached?
7 Is SET NOCOUNT ON for stored procedure performing a lot of updates/deletes/inserts?
8 use DDLs first at the top section of SP, than use DMLs in below section
9 Is cursor not used / if yes remove and suggest for join
10 fields that are not defining actual JOIN predicate should be pushed to WHERE
11 Remove complex business logic in join queries
12 Avoid distinct in full join( use Rank, Rownumber)
13 Add common column in table to avoid join
14 Is Database Engine Tunin Advisor suggest any create / remove index?
15 Is On clause used in join conditions instead of where clause
16 Is select * avoided and needed column is selected to pass to front-end
17 Is select narrowed down (Where clause used)?
18 Select with NOLOCK on table with low frequent updates / insert / delete
19 Use “WHERE EXISTS (SELECT * FROM <table>)” while evaluating Sub-Queries, and use “IN()” only while dealing with constant values.
20 sub-queries as part of SELECT, re-write using JOIN or CROSS APPLY
21 accessing same table more than once in a sub-query consider replacement with CTE instead
22 Is Try Catch block used to handle error? Error functions ERROR_NUMBER, ERROR_LINE, ERROR_MESSAGE, ERROR_SEVERITY, ERROR_PROCEDURE and ERROR_STATE
23 usage of IF @@ERROR, GOTO to be avoided
24 Avoid using LOWER or UPPER built-in functions when trying to achieve case-insensitive compare; specify COLLATE SQL_Latin1_General_CP1_CI_AS explicitly instead
25 Is correct type & nominal length provided for columns?
26 IN (…) or NOT IN (…), consider replacing with straight JOIN and/or IF EXISTS
27 Use SET command to assign value to variables
28 usage of temp tables vs. table variables (if expect row is more than 200 to be temporarily stored – use local temp table; less than 200 – consider table variable
29 If SP returns multiple datasets – split into multiple SPs
30 SP should not use global ## temp tables
31 Avoid data being implicitly converted between types. Implicit conversions can have unexpected results, such as truncating data or reducing performance.
32 Verify temp table columns of “varchar/nvarchar” data type – they should be created with “collate database_default” modifier to avoid errors if joins are performed on them in case-sensitive databases
33 Verify usage of temp table columns – i.e. if columns are not subsequently used – remove them.
34 Verify temp table columns data types – they should match the data type of base tables you’re populating them from.
35 Use COALESCE() instead of ISNULL(), ensure COALESCE is always used to handle records which in fact have NULLs.
36 Never use ISNULL() on the BIT data type, as it only accepts 1/0/NULL as the possible values.
37 use TRY_PARSE() instead of ISNUMERIC()
38 Avoid sub-queries, use CTEs for better code manageability and readability
39 Handle Divide-by-zero errors the columns/variables occurring in denominator.
40 Any temporary objects used, if yes make sure these objects closed / removed once their usage is done.
41 Avoid triggers for better data flow and reduce overhead, use Output clause which can be controlled easily
42 Ensure a column in each table is designated as the primary key and select an appropriate data type such as an integer
43 Foreign Keys - Validate all of the foreign keys are established to maintain referential integrity among your tables to ensure the proper relationships are maintained.
44 GROUP BY <number> clausedeprecated, use proper GROUP BY <column>
45 Use default order of data, minimize order by statement use column instead of number to order
46 record existence should be coded using IF EXISTS (…), not IF SELECT COUNT(*)>0
47 Check for scalar UDF usage in select and where clause - number of function calls to be minimized
48 Verify usage of UNION vs. UNION ALL; if you’re bringing different record types that are already unique, use UNION ALL
49 Verify usage of DISTINCT – are duplicate rows coming as a result of missing JOIN predicate or data quality issue
50 UNION, DISTINCT, GROUP BY may require SQL Server to perform data sorting in memory, and this could lead to performance degradation
51 SQL Server is not suited for intense string manipulations – it should be handled in application side
52 Avoid unnecessary loops for Email data, email to be moved to front-end
53 Using parentheses, even when they are not required, can improve the readability of queries
No comments:
Post a Comment