Index ms slow sql updating dating or exclusive
(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.) In today’s blog posting I want to demonstrate how missing indexes on tables can cause deadlocks in SQL Server.
For the preparation of the test scenario the following code creates 2 tables and populates both tables with 4 records.
For the purposes of my presentations, I’ve been using Adam Machanic’s (b|t) Make Big Adventure Works script, which you can download here.
Specifically, the table I’m working with is the Big Transaction table, which is about 1.1 GB uncompressed and consists of 31,263,601 rows.
The reason for this is that for the purposes of bulk insert the load process has been extremely optimized.
Read Performance Just in case you aren’t up to speed on columnstore indexes they do offer much better read performance over traditional compression—this is outside the scope of the post, but running a test query to pull average transaction value for a given date, this graph shows the comparison between page compression and columnstore.
I know you can’t see the elapsed time for the columnstore query in the chart—it’s 227 milliseconds (ms), as opposed to 5627 ms for the page compressed version of the query.
From the Microsoft SIGMOD white paper on the subject: Given the significant performance advantage of the bulk insert API over traditional insert statements, the query execution engine will internally use the bulk insert API for “insert into …
select from …” statements that are targeting a column store.