SoHelpMeCodd (11/27/2015)

---

Welsh Corgi (11/25/2015)

---

The following example baffles me about Indexes Where it uses a date file in the indexes and it talks about aligned indexes.

It does not work but it is similar to what I want to do but archive by year.

http://sqlfool.com/2008/11/102/

http://sqlfool.com/2008/11/102/">

http://sqlfool.com/2008/11/102/

I found https://msdn.microsoft.com/en-us/library/aa964122(SQL.90).aspx (even though old) and http://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/PartTableAndIndexStrat.docx to be enlightening (even though much longer to read :). Alignment is covered in more detail by them.

What http://sqlfool.com/2008/11/102/ does not cover well (IMHO) is the sliding window scenario. The left-most partition of the sliding window needs to be empty, which for [datetime] a useful partitioning value is '1753-01-01 00:00:00.000' (instead of a CHECK constraint).

When partitioning a table, the primary key's partitioning column can be in *any* ordinal position within the soon-to-be-composite primary key. If the partitioning column is used by no query, put the partitioning column in the *last* ordinal position. Whether indexes should also be aligned to a partition depends upon conditions broadly described by https://msdn.microsoft.com/en-us/library/ms190787.aspx. But ... when indexes are *not* aligned, the time spent switching the clustered index (table) to an archive partition will include time spent ghosting rows|pages|allocation pages. That additional time is rooted an IO cost - it will introduce an exclusive lock durations (on non-aligned indexes) *during* a partition switch. But if all indexes are aligned, a switch involves nothing more than a brief elevations Schema Modification locks (during which members of sys.partitions are updated). Because the partitioning column must be a key in the primary key, foreign keys also need to be aligned (for fastest switches), unless you can afford exclusive lock time upon the children (to perform a traditional delete/truncate). Even if pre-deleting is possible the FK will need to be checked during the switch. If the children cannot be quickly (painlessly) pre-deleted, foreign keys will have to be disabled and re-enabled during the switch (with the foreign keys being switched at the "same" time). If you want the re-enabled foreign keys to be trusted by the optimizer, WITH CHECK CHECK would have to be used, which can be loosely called "a postponed IO cost".

I considered implementing partition switching for my environments. Its biggest table also concerns transactions, with relationships similar to those within xactControlPoint. XactControlPoint appears to be more normalized than my table (I am jealous:)). What turned me away from partition switching was the need to manage foreign keys. I was not very concerned about the performance impact that of slightly (at best) increased index depths (due to addition of partitioning column) and somewhat larger index sizes (due to a wider clustered index key). But I was concerned with the performance impact of pre-deleting children, or, the impact of waiting for FK checks during a switch. My foreign key concerns are also rooted in my not being in control of FK/PK references (the database is continuously being updated by ~100 developers).

My consideration of sliding windows partitioning was not driven by a need to increase delete performance or improve query performance. It was instead driven by my need to not significantly impact *existing* performance. And that consideration was dictated by my employer's concern about the cost of leased disk space (sigh). If being driven by a need to increase *current* performance, a B-Tree's depth resilience (to a switch or a delete) is situational == YMMV == test :), but partitioning tables are not likely to be the droids you are looking for:Whistling:.