June 19, 2016 at 11:27 pm
When tuning a SQL query I don't bother with the Estimated Execution Plan. Instead I would use Actual Execution Plan to identify bottle necks and look for Table Scan (which can slow down a query execution).
Please list all the things that you will personally do to remedy a slow query.
June 20, 2016 at 8:43 am
here's some of the things i could think of off the top of my head:
1. Is the query itself SARG-able?( search arguments in WHERE statements will use an index?)
look for performance killers like:
a. like '%searchterm%',
b. CONVERT(varchar,somedatecolumn) = '2016-05-05' (function on column name)
c. ISNULL(SomeColumn,0) > 0 (function on column name where isnull not needed)
d. NVarcharColumn='SomeString' (implict conversion on column)
e. No WHERE statement (SELECT the whole table?)
f. Query uses a cursor instead of a set based query.
2. is the query using a catch all query?
a. WHERE (ProductID = @Product Or @Product IS NULL) AND (ReferenceOrderID = @OrderID OR @OrderID Is NULL)
b. WHERE 1 = CASE WHEN ProductID = @Product THEN 1 WHEN @Product IS NULL THEN 1 ELSE 0 END
3. Does the query use a scalar function to lookup or modify data? can it be change dot an inline table vlaue function?
a. SELECT dbo.ProperCase(Firstname)
4. Does the query use a table variable, instead of a #temp table? does it receive multiple rows?
5. Does the query use a table variable or #temp table, which can be replaced by a CTE to avoid double reads?
5. does the query contain an inline subquery featuring top 1?
a. SomeColumn = (SELECT TOP 1 FROM OTHERTABLE WHERE ...)
6. does the query select */lots of columns that are not consumed downstream?
7. Is the table a heap? is there a clustered index on the table?
8. Is there an index on this table that is used for this query?
9. Are there missing indexes, or other indexes that can be modified to address this query?
10. Does the query select a millionbillion Rows?
11. Does the query contain an ORDER BY that can be eliminated/unneeded?
12. Doe the JOINs need indexes on the foreign keyed columns?
13. Are there unnecessary joins on table that are not used? is there a join that can be changed to an IF EXISTS instead?)
Lowell
June 20, 2016 at 9:00 am
Estimated plan can be perfectly adequate. Only time you need the actual plan is if you're looking at the row estimates and whether they're wrong. For just finding table/index scans and inefficient use of an index (predicates), the estimated plan's sufficient.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 20, 2016 at 9:30 am
In addition to table scans, another common problem is hash match joins, which occur when SQL Server builds up an index on the fly, consuming time and tempdb storage, when joining two large tables, because no suitable index exists to facilitate a merge join or nested loop. It could also be that a suitable index does exists, but stale table statistics cause the optimizer not to leverage it. Also, SELECT * or needless usage of SELECT TOP or ORDER BY clause are also common and conspicuous causes for poor performance.
But I generally don't hunt for specific issues unless I'm working in an organization where that specific type of issue has proven itself to be a common problem. I reccomend the following eBook, so you know what to look for in a specific query plan. It could be any one (or a handful) of 100 issues.
SQL Server Execution Plans, Second Edition, by Grant Fritchey
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply