Project management database and sql server katmai new features
Download
1 / 19

Project Management Database and SQL Server Katmai New Features - PowerPoint PPT Presentation


  • 103 Views
  • Uploaded on

Project Management Database and SQL Server Katmai New Features. Qingsong Yao [email protected] Disclaimer . The content in this slides is demonstration only. Please do your own research before trying to apply either Sparse or Compression technology to your product server. Topics.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Project Management Database and SQL Server Katmai New Features' - agatha


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Project management database and sql server katmai new features

Project Management Database and SQL Server Katmai New Features

Qingsong Yao

[email protected]


Disclaimer
Disclaimer Features

  • The content in this slides is demonstration only. Please do your own research before trying to apply either Sparse or Compression technology to your product server.


Topics
Topics Features

  • Katmai Related New Features

  • A Project Management System

  • Experimental result

  • Reference


Sparse columns
Sparse FeaturesColumns

  • Sparse is purely a storage attribute

  • DDL support for specifying a column as “Sparse”

    • CREATE TABLE t (id int, sparseProp1 int SPARSE);

    • ALTER TABLE t ALTER COLUMNS sparseProp1 DROP SPARSE;

  • No Query/DML behavior changes for a sparse column

  • Column metadata will have a bit to indicate the sparse attribute

  • Storage Optimization:

    • Sparse vector design: 0 bytes stored for a NULL value

    • Have overhead for not null values (4 byte per not null column + 4 byte header)


Sparse columns restrictions
Sparse Columns Restrictions Features

  • Sparse columns cannot be part of a key in clustered index or a PK index or part of a partition key.

  • Sparse column cannot be the key column for an unique index.

  • Unique constraints are also not allowed on sparse columns

  • Sparse columns cannot be defined as "non-null" and cannot have any "default" values.

  • Rules" are not supported on sparse columns.


Filtered indices
Filtered Indices Features

  • “Filtered index” is such a mechanism that allows the table designer to define a regular index that optionally includes a simple filter (predicate) on that table to specify the qualifying rows that need to be indexed for that index.

  • Examples:

    • Create Index filtered_index on WSS.List(Author) where ListId = 5

  • Examples of valid filter expressions include

    • Listid = 10 and folderid > 20

    • Listid = 10 and folderid > 20  and folderid < 50

    • Listid in (10, 20, 30) 

    • Listid in (10, 20) and folderid in (15, 25)


Filtered indices1
Filtered Indices Features

  • Support Online Operation, Alter Index, Partition Table, Index hints, and DTA

  • Has side impact on query parameterization (because we have to do predicate matching).

  • Sparse Column and Not-null Filtered Indices like:

    • Create index on t1(c1) where c1 is not null

  • are very helpful on querying/storing “sparse” columns (no impact on query parameterization).


Data compression
Data Compression Features

  • Different Compression Types

    • Vardecimal Compression (SQL Server 2005 SP2)

    • Row compression

      • Row compression contains vardecimal compression

    • Page compression

      • Page compression contains row compression

  • Main Focus was data warehouse scenarios

    • But very useful for certain OLTP scenarios as well

  • Main goal: Enabling compression does not require application changes

  • Compression only supported in Enterprise Edition

Microsoft Confidential


Row compression
Row compression Features

  • Light-weight compression

    • Useful for certain OLTP scenarios

  • All columns stored as variable data in new record format

  • Reduce overhead per column (4-bits vs 2 bytes)

  • Store minimal number of bytes per value:

    • Leading zero bytes removed for int, smallint, tinyint, bigint, datetime, smalldatetime, money, smallmoney, real, float

    • Trailing spaces removed for char, nchar, binary

    • Decimal / Numeric vardecimal compressed (same compression as vardecimal compression)

  • NULL / 0 value take no space (besides overhead)

  • No compression for varchar, nvarchar, varbinary, text, ntext, image

Microsoft Confidential


Page compression
Page Compression Features

  • Compress all data on a single data page

  • Compress ‘similar’ column values by only storing the value once on the page instead of multiple times

  • Two page compression algorithms

    • Column Prefix

    • Dictionary

  • User cannot choose algorithm, both algorithms are always applied

  • Page Compression includes row compression

Microsoft Confidential


Topics1
Topics Features

  • Katmai Related New Features

  • A Project Management System

  • Experimental result

  • Reference


Data sources
Data Sources Features

  • Three tables store main workitem information

  • Table has 17 regular columns (have meaningful name, and always have not null values), and all other columns has predefined random name, and random data type

  • Views are defined on these columns to assign meaningful names to the columns


Column distribution information
Column Distribution Information Features

  • Column data type distribution:

  • Null value distribution


Topics2
Topics Features

  • Katmai Related New Features

  • SQL Project Management database overview

  • Experimental result

  • Reference


Summary
Summary Features

  • Using WorkItemsWere as source table , try following cases:

    • Compress table using page compression

      ALTER TABLE WorkItemsWere REBUILD PARTITION = ALL

      WITH (DATA_COMPRESSION = PAGE)

    • Compress table using row compression

      ALTER TABLE WorkItemsWere REBUILD PARTITION = ALL

      WITH (DATA_COMPRESSION = ROW)

    • Find all columns have more than 67% null, and change to sparse

      • insert into temp table

        select * into WorkItemsWere_Temp from WorkItemsWere

      • truncate data

        truncate table WorkItemsWere

      • change sparse script

        alter table WorkItemsWere alter column [Fld10004] add sparse

      • insert data back

        insert into WorkItemsWere select * from WorkItemsWere_temp

      • rebuild index

        ALTER INDEX [PK_WorkItemsWere] ON [dbo].[WorkItemsWere] REBUILD


Result
Result Features

  • Space Saving: Page Compress > Sparse > Row Compress > normal

  • Procedure sp_estimate_data_compression_savings can estimate space saving without doing the actual compression (but it is not very accurate)


Cpu overhead
CPU Overhead Features

  • The database server have 16G memory, while table WorkItemsWere (the largest table) is 4G which mean all data can be in the cache, and physical I/Os are likely be 0.

  • Sparse and Compression can save Logical I/O since they require reading less pages.

  • Sparse and Compression can increase CPU time since the data need to be uncompressed.


Table scan cpu time result
Table Scan CPU Time Result Features

Select 17 regular columns

+ 27 predefined columns (with at least 20% not null value) + 10 random predefined columns

Result shows that sparse case has less CPU Overhead. Next slide shows the reason.


Table scan cpu time result 2
Table Scan CPU Time Result (2) Features

  • Sparse does not have negative impact on regular columns.

  • Extracting Null values from sparse columns has higher CPU overhead than page compression and row compression case.

  • Page Compression and Row Compression are in table level, the CPU overhead of uncompressing Not Null values are higher.


ad