3 good and not that good things about Live Query Statistics

LQS_LogoSQL Server Management Studio 2016 provides the ability to view the live execution plan of an active query. This live query plan is a real-time insight into the query execution process. And, since the data is available in real-time without needing to wait for the query to complete, these execution statistics are extremely useful for debugging query performance issues.

Continue reading “3 good and not that good things about Live Query Statistics”

Finally: TRUNCATE partition in SQL Server

TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. That is what the documentation says and that is what we daily use in our scripts, jobs and SSIS packages. In SQL Server 2016,  such operation can be done on the partition level as well, giving extra flexibility and convenience. TRUNCATE has nearly no resources cost for the system during the execution,  it also require fewer locks than comparable DELETE statement.

Continue reading “Finally: TRUNCATE partition in SQL Server”

SQL Server 2016. Dynamic Data Masking

Dynamic Data Masking LogoDynamic data masking is another new feature exposed in upcoming SQL Server 2016. It helps prevent unauthorized access to sensitive data by giving customers possibility to mask sensitive information with a low impact to existing applications.Official documentation is pretty clear about the usage and can be a good beginning point. However, I would like to bring also some light into the aspects  that are not covered in the manual.

Continue reading “SQL Server 2016. Dynamic Data Masking”

SQL Server 2016: Temporal tables. Comments on behavior

In this post I would like to highlight couple of behavioral aspects of temporal tables which was observed during test drives. Firstly, is a demonstration of  how temporal tables queried by exposing live query statistics of the SELECT. The other item is about how system versioning tracks updates for cases when values are really not changed.

Disclaimer

The highlighted results are based on pre-release version. Behavior described here is a subject for possible change. Version of SQL Server:

-- Microsoft SQL Server 2016 (CTP2.1) - 13.0.300.44 (X64)
SELECT @@VERSION

 

Query plan of the SELECT and Live Query Statistics

New SQL Server Management Studio feature, called Live Query Statistics, used to capture real flow of query execution. Following animated GIF file shows that current and history data retrieved sequential way within a query, even if query plan uses parallelism.

The picture is clickable and overall duration is about 20 seconds:

01.  Query plan of the SELECT. Live Query Execution
01. Query plan of the SELECT. Live Query Execution. Duration: 20 seconds

This behavior is not actually an issue, however it shows possible room for improvement.

False updates or when column values are updated, but keep still original values

During tests and benchmarks I noticed default behavior, which can be really undesirable in some scenarios – all updates are logged to a history table, even if values were updated to the same values. Short demo:

Continue reading “SQL Server 2016: Temporal tables. Comments on behavior”

SQL Server 2016: Temporal tables. Indexing. Part III. Enlarged dataset

Temporal Tables is an important and anticipated feature of upcoming SQL Server 2016, it definitely worth series of blogs and articles which I and other bloggers written recently. One of the aspects last weeks covered on this site is a performance. This time I would like to go even further by enlarging 10x times the dataset and provide brief execution results.

Changes in benchmarking

Benchamrks based on the approach used in previous posts, but with some key differences:

  • Number of rows in dbo.Product increased from 1 000 000 to 10 000 000
  • DataFiller column datatype narrowed from CHAR(1000) to CHAR(100)
  • DMLs Insert/Update batch size increased from 100 000 to 1 000 000 rows. Therefore, history table holds rows of two UDDATE batches and two of DELETEs

Queries remained the same as in previous benchmarks. They also available on a github for tests reproduction.

Results

Table 1. Disk space usage per object type in megabytes:

Current table History table Total
Raw data (uncompressed rowstore) 2557 1199 3756
Scenario 1: rowstore objects (default) 2556 453 3009
Scenario 2: rowstore current and columnstore history 2556 100 2656
Scenario 3: columnstore objects 223 98 321

Continue reading “SQL Server 2016: Temporal tables. Indexing. Part III. Enlarged dataset”