DaveWentzel.com            All Things Data

Most Common Icons, Their Meanings, and Performance Implications

 

Sample Data Scripts
drop table emp
go

drop table dept
go

create table dept(deptid int primary key clustered, deptname varchar(10))
go
create table emp(empid int primary key clustered, empname varchar(10), deptid int references dept(deptid))
go
insert dept values(1, 'HR')
go
 
 
 
 
 
Assert
 
 
Acts just like an assertion in a standard programming language.
Verifies things like RI and check constraints
No real performance issues with Asserts
insert emp select 1, 'Wentzel' , 1
Assert occurs because we need to verify the FK exists
 
 
 
 
 
 
Compute Scalar
converting values, concatenating values
generally these aren't an issue, but is worth investigating if you have CPU performance issues, or if you are performing a lot of these operations
 
 
 
 
Clustered Index Delete/Insert/Update
 
--For Deletes, rows are deleted from the CI based on the Argument column.  May have a WHERE:() predicate
--For Inserts a SET:() predicate is shown that sets each column’s values
--For Updates, may have a WHERE:() and SET:() predicate
--Index Delete/Insert/Update
--Performs the same function, but for non-clustered indexes
--Has the same graphical icon as well
--Performance Ramifications
     --None really, if you have indexes, these operations must occur
     --Look for excessive numbers of Index operations.  Do you need all of these indexes?  Each CRUD operation requires additional I/Os that may not be desired. 
 
Table/Delete/Insert/Update
 
--Works exactly like CI Delete/Insert/Update except on “heap” tables
--Performance Ramifications
     --None really, the operations cannot be avoided
     --ALWAYS consider changing heaps to clustered indexed tables
     --Subsequent I/Os for CRUDs will likely decrease

 
Filter
--Scans the input rows and returns only those the satisfy the filter predicate in the Argument column
--Performance Ramifications:
     --Filtering is good
     --Filtering early is better
     --Make WHERE and ON clauses as exact as possible
     --If possible, move conditions to ON instead of WHERE
     --Need an example here
 
Spool Operators
  • Table Spool/Index Spool/Row Count Spool
  • Eager spool is a  blocking operator (Blocking and non-blocking Operators)...this means the spool must be created and fully populated before the next step in the pipeline can begin execution.  Lazy spool is not.  EAGER Spools, Lock Escalations, and Optional Parameters.  Eager Spools in Query Plans and the Halloween Problem
  • You will see these whenever the query engine determines it needs a read-consistent view of the data (such as performing a calculation in an UPDATE statement).
  • The spool table is a hidden tempdb table that holds each row of an intermediate step of a statement.
  • The index spool is a temporary index created in tempdb. 
  • Exists only for the lifetime of the query.
  • You will also see these in CTEs that use recursion.  It provides the looping mechanism to the query. 
  • Rewinding and rebinding
  • Performance Ramifications:
    •  Rebind is worse than rewind
    •  Rewind can use less I/O than rescanning the table or index  (Rewinds and Rebinds)
    •  Indexing or a better WHERE condition will help
    •  Row Count Spool is better than table spool because actual data is not returned, such as when using EXISTS
 
Cursor Operators
--Here the graphical plan may not give you enough information to go on
--Sp_describe_cursor
--Avoid tempdb operations and worktables
--Avoid refresh
--Dynamic operations

 
 
 
 

Add new comment