This Saturday was SQL Saturday for Raleigh. Attendance was strong, classrooms were filled, and the event was a success. When I attend these events it amazes me how strong the SQL community is. Speakers travel from states away, volunteers setup and plan the event, vendors sponsor the event and meet at their booths with participants, and people come to learn on an Saturday.
That really says something about SELECT individuals who INNER JOIN with each other ON a Saturday WHERE the goal is to learn from each other as a GROUP BY sharing knowledge we are lucky enough HAVING such a great community. I’ll stop now in ORDER to show some pictures of day!
Using T-SQL to insert, update, or delete large amounts of data from a table will results in some unexpected difficulties if you’ve never taken it to task.
Let’s say you have a table in which you want to delete millions of records. If the goal was to remove all then we could simply use TRUNCATE. However, if we want to remove records which meet some certain criteria then executing something like this will cause more trouble that it is worth.
Why not run the following in a production environment?
Simple delete - no concern for size
Transact-SQL
1
2
3
DELETE
FROMmyTable
WHEREcolumnA='whatever';
Transaction log growth – expect a large transaction like this to cause the transaction log to expand. Calls to expand the database files are some of the most expensive operations SQL Server makes to the OS. Hopefully the initial sizing was good and the growth settings are reasonable.
Blocking – concurrency is going to increase the likelihood of lock escalation. That will lead to the possibility of excessive blocking.
Rollbacks – if the SPID is killed then the transaction will enter a rollback state. For very large transactions this can take a lot of time. Don’t even think about killing or stopping SQL Services during a rollback unless you want a corrupt database!
What can we do to improve this?
We follow an important maxim of computer science – break large problems down into smaller problems and solve them.
We break up the transaction into smaller batches to get the job done. Each of the above points can be relived in this manner. Let’s take a look at some examples.
This year marks the end of life for Microsoft SQL Server 2005. Remember Query Analyzer? Enterprise Manager? In the dark times before BIDS and the Visual Studio shell we called Management Studio emerged, we had to manage and develop the database using a crude form of an MMC snap-in and a text editor whose power rivaled that of WordPad.
I made the journey to Charlotte for SQL Saturday this year. It was a solid event with good attendance, interesting speakers and topics, and in a nice facility.
