Benchmark servers remotely with SQLIO and PowerShell

PowershellYou’ve got a zoo of SQL Server instances and have to establish a baseline of IO subsystem performance?  Perhaps, you need to run IO benchmarks on a scheduled basis on IaaS servers just to see a trend over the time? Or, simply, you tasked to configure yet another SQL instance and would like to figure out what the offered hardware is capable? For all those calls the PowerShell automation is a great point to start, that can evolve infinitely. In this post I going to reveal a story of mixing Powershell, PsExec, SQLIO and Invoke-Sqlcmd together to achieve the goals.

Continue reading “Benchmark servers remotely with SQLIO and PowerShell”

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”