Yet another SQL Server enthusiast

2017-07-17

SQL Server partition elimination – a simple example

Filed under: Uncategorized — sa @ 11:07 AM

On a partitioned table, partition elimination (ref #1 – section Partition Eliminationref #1 – section Partition Elimination, ref #2) is an query optimization

  1. Available to predicates defined on partitioning key columns (DateKey in our example) and
  2. It allows to avoid a full table scan/full [clustered] index scan
  3. By scanning records from one or more partitions.

Example:

sql.server.partition.elimination

In this case

  • DateKey is the partitioning key of dbo.FactOnlineSales_Archive table and
  • WHERE clause includes one predicate defined on this column (DateKey = <constant>).

Execution plan shows how partition elimination works: Seek Predicates (green box) property of Clustered Index Scan includes one predicate (PtnId1000 = …) which is going to do the partition elimination (it limits the Scan operator to a single partition in this case). Also, please note the Predicate property (red box) of the same Scan operator which is going to filter all records from current partition in order to count only records for DateKey = ‘2007-01-04’.

2017-07-12

A.D.C.E.S. Meetup 2017-07-11

Filed under: Uncategorized — sa @ 8:42 PM

Download files

 

2017-07-10

SQL Server Management Studio v17 – three keyboard shortcuts

Filed under: Uncategorized — sa @ 11:37 PM

1. Line swapping

Keyboard shortcut: Alt + Up / Down (new in SSMS v17)

Description: It swaps current line and above (Up) or bellow (Down) line

Example: Ctrl + Up

Alt   UporDown 

2. Vertical selection

Keyboard shortcut: {Alt + Shift + Up / Down or Shift + Alt + Up / Down or Alt + Mouse Selection} + [Typing chars]

Description: It selects source code on columns

Example: Shift + Alt + Down (x4) + (‘

Alt  Shift   UporDown

3. Clipboard cycling

Keyboard shortcut: Alt + Shift + V or Ctrl + Shift + Ins

Description: It cycling backwards  through selected text in clipboard

Example: FactSales, INSERT, UPDATE and also DELETE were copied (Ctrl + C) into clipboard and then Alt + Shift + V (x4)

Ctrl   Shift   V

2017-06-27

Filegroup restoring and database consistency @ sqlservercentral.com

Filed under: Uncategorized — sa @ 10:16 PM

New article published on sqlservercentral.com: [Filegroup restoring and database consistency]

2017-05-08

RoSQL 2017-05-06 Table Partitioning

Filed under: Uncategorized — sa @ 9:32 AM

 

Download *.sql scripts

2017-05-02

Moving LOB data to another filegroup

Filed under: Uncategorized — sa @ 9:19 AM

Note #1: This post is (more or less) a reminder for me.

Note #2: Original solutions are described here and here.

The problem (and a partial solution)

Sometimes, within a SQL Server database, we have to move data to another filegroup. In such cases, the simplest solution is to use CREATE INDEX statement with the ON NewFileGroup clause and also with DROP_EXISTING = ON thus:

image

This solution is available for both clustered and non-clustered indexes. In this cases, SQL Server will rebuild current index using the new filegroup (ON NewFileGroup). The drawback of this solution is that Database Engine will not move also LOB data (Large OBject data types include: old data types [n]text, image but also the new data types [n]varchar(max), varbinary(max), xml, CLR, geometry, geography, sql_variant) to new filegroup. The [main] reason of this behavior can be seen in CREATE TABLE syntax:

image

The CREATE TABLE statement includes the TEXTIMAGE_ON clause what allows us to define which filegroup should be used to store LOB data. Above solution will move only row data without moving LOB data (pages) into new filegroup. So, LOB data remain within old (TEXTIMAGE_ON) filegroup.

Note #3: For heap tables, one solution is to create a clustered index on the new filegroup and then drop it. After dropping clustered index,  the LOB data remain [stored] within new filegroup.

Final solution

The final solution is to recreate clustered index using a partition scheme. In this case, the behavior of SQL Server Database Engine is to move to target filegroup(s) (defined by partition scheme) both ROW and LOB data pages:

image

In order to create partition scheme we need a partition function. The main difference between original solution and this one is that in this case, I’ve used an empty partition function.

Demo

Initial status of ProductPhoto table (AdventureWorks2014 database):

image

Partition function (empty partition scheme), partition scheme (with all “partitions” assigned to [UserObjects] filegroup) + ProductPhoto table is recreated using the new partition scheme:

image

After these operations, the new status of ProductPhoto table is

image

but current table is partitioned. In order to remove partitioning (partition scheme) of current table the simplest solution is to recreate (again) the main table structure (clustered index) on the new filegroup:

image

And the final status of ProductPhoto table (unpartitioned) is:

image

2016-06-05

Trolling Ora

Filed under: Uncategorized — sa @ 3:08 PM

CREATE DATABASE Scott;

GO

SQL Server on Linux Ubuntu - First preview

SQL Server on Linux Ubuntu – First preview

Source

2016-03-16

ITdevConnect 2016

Filed under: Uncategorized — sa @ 1:11 AM

ITdevConnect

ITDC16

2015-06-22

SELECT {d ‘2015-06-20’} [RoSQL Presentation]

Filed under: sql server — sa @ 6:58 AM

2015-06-21

When a query plan goes bad

Filed under: sql server — sa @ 9:03 PM

Source: http://dbareactions.com/post/122027631923/when-a-query-plan-goes-bad

Next Page »

Blog at WordPress.com.