You’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.
SQL 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.
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.
Dynamic 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.
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.
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:
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: