PASS 2015 – Seattle WA – Pre-Con Day 1 – Pinal Dave

Oct 26, 2015
Lincoln Bovee'

PASS 2015 10-26-30 Seattle WA

Pre-Con Day 1 – 42 Tips to Performance Tuning, Optimization and Everything

Pinal Dave

TODO:

  • Write missing index script (DMV)
  • Write unused index script (DMV)

    • NOTE: Creating an NC index is slower than just a an INSERT into a table because you have to read the columns from the entire table before it can write all the coloumns in the new index.

    NOTE: DELETEs and UPDATEs can also be faster *with* indexes.

    NOTE: scanning just the NC for data when it does not have to touch the table will always be faster than when scanning a CL index for the same data (i.e. the ID column)

    Newsletter: http://bit.ly/sqlnow

    NOTE: Generally create the index for filtering and predicates, and INCLUDE columns only for selecting.

    TODO: Write script to identify and remove duplicate indexes

    NOTE: using a function, ANY SQL function (intrinsic or developed) with affect performanc when used on a column, NOT when used with a fixed value (i.e WHERE DATEDIFF(MONTH, NOW()) > 0). It will often cause the execution plan to perform a table scan even though there si a good index on the table(s).

    TODO: Create an NC that includes a computed column. It will return a index seek on a select where the WHERE statement used a LTRIM(RTRIM()) function that was moved to the header as a DECLARE..SET.

    TODO: look at https://msdn.microsoft.com/en-us/library/ms188281.aspx SORT_IN_TEMPDB for our new servers.

    NOTE: If you have statistics off, SQL Server will show an warning icon over the clustered index scan in the execution plan what will display that is has not statistics on that table, and will not be a very performant plan.

    NOTE: if 20% of the data in a given table has been updated since last stats update, this stat will trigger SQL Server will generate new statistics.

    NOTE: Cardinality definition: In the context of databases, cardinality refers to the uniqueness of data values contained in a column. High cardinality means that the column contains a large percentage of totally unique values

    TODO: Try adding the follwing after a query to see what it does: SELECT * FROM Person.Person OPTION (querytraceon 9481)

    NOTE: OPTION (querytraceon 8649) forces the query to only use 1 CPU, should be same as OPTION (MAXDOP 1)

    TODO: User Custom Counters in perfmon are cool. Look at them

    NOTE When using UDFs on SELECT queries, use WITH SCHEMABINDING, much faster.

    Views – 2273