DELETE and Non Clustered Indexes

I’ll start off with a disclaimer: I’m going to tell you about something that happened in a specific system Environment. There’s no such thing as  a general advice you can build on this specific scenario. I’m just posting it because I was myself surprised by what order of magnitude I was able to speed up a specific query by slightly removing some of the work in the execution plan.

The other day I helped troubleshooting a database system. In a table with some 400 million records, a subset (50-60 million records) were to be deleted. The application kept timing out on this delete operation so I adviced the developer to split the delete operation into smaller chunks. I even helped writing a T-SQL script to perform the delete in one million row chunks. The script was pretty basic – a WHILE-loop which checked if any rows fulfilling the WHERE-condition of the delete was left in the table, and inside the loop a DELETE TOP(one million) followed by an explicit checkpoint.

Continue reading “DELETE and Non Clustered Indexes”

Impressions from Sql Saturday 433

I’m currently on a train from Gothenburg back home to Enköping. I have attended my first Sql Saturday (thank’s Mikael Wedham and crew for a great event!). I also did my first ever public SQL presentation at the event – a session about SQL Server partitioning.

The presentation and demo scripts can be downloaded from http://www.sqlsaturday.com/433/Sessions/Details.aspx?sid=38722

Continue reading “Impressions from Sql Saturday 433”

SQL Saturday 433, Gothenburg

On September 5th, the first ever SQL Saturday in Sweden is held, in my favourite Swedish city Gothenburg. SQL Saturday Conferences are held all over the World and this first ever Swedish SQL Saturday event is the 433rd. And yeah, the Conference is for free. A full day of free training. If you are in the neighbourhood, you do want to be there. Check out the sessions and register here.

I’m very proud to have been selected on out of 24 speakers. My session – “Eight hours of work in 20 minutes” – is a case study of how a data load has evolved, from basically SSIS-loading data into a table, through some index maintenance as part of the data load, into table partitioning. The line-up makes me somewhat nervous, but it will be great fun to make a public appearance. Old friends showing up at the event makes it even better.

This is the first post on this blog. The future posts will be mostly about T-SQL.