PASS 2015 10-27-30 Seattle WA

Pre-Con Day 2 – SQL Server Internals – The Practical Angle

Dmitri Korotkevitch

Data Storage 101

Data Pages

IN-ROW means that the entire row of data can fit in 1 datapage, counting for all varchars etc.
LOB: variable data > 8000 bytes (it won’t fit in a single data page)
ROW-OVERFLOW variable-length data that is less than 8000 bytes but could not fit in-row on a single data page.

ROW COMPRESSION is not really compression but an alternate page-row format
PAGE COMPRESSION is true compression

TODO: Understand more on IAM, GAM & SGAM pages. See:
http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-record/
http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/
http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-an-extent/
http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-iam-pages-iam-chains-and-allocation-units/
http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-gam-sgam-pfs-and-other-allocation-maps/

http://www.practicalsqldba.com/2012/09/sql-server-understanding-gam-and-sgam.html

NOTE: From SQLSkills: A data record always stores all columns from a table row – either by-value or by-reference.
If any columns are for LOB data types (text, ntext, image, and the new LOB types in SQL Server 2005 – varchar(max), nvarchar(max), varbinary(max), XML), then there’s a pointer stored in the data record which points to a text record on a different page (the root of a loose tree that stores the LOB value). Exceptions to this are when the schema has been set to store LOB columns ‘in-row’ when possible. This is when a LOB value is small enough to fit within the size limits of a data record. This is a performance benefit as selecting the LOB column does not require an extra IO to read the text record.

–/
In SQL server there are two types of extents:

Uniform Extent: These are the extents owned by single user objects. All 8 pages of these extents can be used by a single object, the owning object.

Mixed Extent:These are the extents owned by multiple user objects. There is a possibility of each page in this extent, that might be allocated to 8 different user objects. Each of the eight pages in the extent can be owned by different objects.
/–

Compression

ROW and PAGE COMPRESSION only work on IN-ROW data pages. verify: it does not compress LOB or ROW-OVERFLOW pages types)

SARG (Seekable ARGuments) result in an Index Seek task in the execution plan, where as Non-SARGable predicates result in an Index Scan task) The SARGable predicate referenced column needs to be the left-most column on the NC index to be SEEKable.

SARGable

Non-SARGable

NOTE: The plan that the Query Optimizer generates depends heavily on IO cost calculated.
NOTE: In the case of NC index usage in the plan, IO depends greatly on the number of ‘Key Look-up’ tasks (A Key Lookup operator (there are two, RID and Key) is required to get data from the heap or the clustered index, respectively, when a non-clustered index is used, but is not a covering index.)

Filtered Index – a small index that has a lower maintenance cost.

NOTE: When creating UDFs, use WITH SCHEMABINDING to make it a bit fast and avoid spooling tasks in the execution plan. (verify)

NOTE: Table Variables do not use statistics. They are also transaction-unaware.
NOTE: Using OPTION (RECOMPILE) will cause SQL server to better estimate the rows. Without it, SQL Server always uses 1 row when generating the execution plan for a query that uses Table Variables.

NOTE: CLR functions are faster than UDFs. CLR Sproc calls are MUCH slower than T-SQL. CLR MANY MANY times faster at operating on XML that T-SQL.

NOTE: Inaccurate statistics leads to incorrect cardinality estimates which lead to poorer execution plans (incorrect index choices, join types and memory grants)

NOTE: Best practice is to use short multiple transactions when possible.

TODO: Practice trouble-shooting wait states by googling the top wait states and trying to understand why they are there.

Views – 2469