I am going to start a series of posts to talk about Locking and blocking in SQL Server, which is one of my favorate topics. As a SQL Server developer, many professionals are more focusing on programming languages, database designs, features (like Service Broker), writting SQLs to effectively manipulate data and process designs. They’re all correct. But one more thing is quite frequently getting neglected is locking. Especially after SQL Server 2005, Snapshot isolatioin level is used more frequent than others. It seems like SQL Server will never locked. Actually, that’s not the fact. Nothing has been changed from locking perspective before and after version 2005. After having a fully understanding of locking and blocking mechanism, you will find out you can easily write a high concurrency application, prevent excessive blocking, and design more sophisticated concurrent SQL process controls.
John H
SQL Saturday #92 @Portland
SQL Saturday 92 Portland Oct 8, 2011 Great experience of presenting one of my favorite, Multi-threading TSQL. About 20 folks attended the session. Thanks for that. Here you can download the slide deck for my session. Alternatively, You can download them from SQL Saturday’s website http://www.sqlsaturday.com/92/schedule.aspx
Restore Database With Partitions Excluded (or Selected Partitions)
Refreshing QA environment is not an easy work when your backup files are terabytes in size. The challenges are not only on space but also on time. Some tables are never envolved into QA phase. Restoring them will waste of your time and space. If you put tables in different file groups, then you would have the option to not restore them. If they are accessed by QA test, you can rename existing corrupted table (file groups are not restored) and create an empty table to make the logic correct. Now, if there is a huge table holding the historical data for the system, QA only need the latest 2 partitions of worth of data for testing, most of the DBAs in this case will restore entire database fist, switch useless partions out of the table, drop the table and file group. What if you have space limitation that only allow you restore part of the database? The solution would be
- Restore database with primary file group and desired file groups
- create table empty table which has the same structure as original, including the partition scheme
- Swith the partition from existing table to the empty table
- Rename tables
That’s a big work especially when you have hundreds of partitions across multiple tables. Here I post my script for that. It supports full, differential, log, and point in time retore, as well as multiple partitions over tables.
Procedures for API Cursor
SQL Server API cursor was designed for data access components years ago. It’s sharing the same framework of T-SQL cursors with more powerful features, such as create cursor over a return set from a procedure. This article lists procedures for API cursor. They are undocumented and deprecated. Please use them in caution. The major part of this article is copied it from http://jtds.sourceforge.net/apiCursors.html.
SQL Saturday #92 @Portland
SQL Saturday 92 Portland Oct 8, 2011 I am going to present “Multi-threading TSQL” in Portland Oct 8th, 2011, http://www.sqlsaturday.com/92/eventhome.aspx. This session will tell you how to use pure T-SQL to implement parallel SQL processing with complex ordering, such as how to start 2 threads at beginning, synchrinoze them then start another n threads for … Read more
SQL Server Transaction Log Buffers
Transaction log is a very important component in SQL Server to keep transaction durable even in an event of system failure. It’s generated while a modification taking place in most of data pages in the memory that eventually being written into data files within a database. Inserting, deleting and updating DML operations will generate transaction logs. DDL operations, such as alter table, create procedure, which cause changes to the system tables, will generate transaction logs as well. Transaction logs are generated by transactions. Any data modification to persistent data in the data files will be at least implicitly involved in a transaction. As far as I know, table variables are not transactional even though SQL Server actually writes the data in table variable into the data files of tempdb. (this is an reason why table variable is faster)
A Procedure for Creating Database Snapshot
Database snapshot is a great SQL Server built-in feature serving many situations for programmers and DBAs to quickly create a read only copy of a operational database. Sometime, creating a database snapshot is a daunting work especially when you have hundreds database files. Here I give you the code to generate database snapshot automatically with … Read more
Cold and Hot Backup
There are always a chance databases suffering from severe system failure and hence causes database corruption, for instance, memory failure, hard drive failure, power failure and so on. Once the database gets corrupted, it’s almost hopeless to recover it without backup. So it’s very important to backup the databases for any database systems. There are two big categories in backup world – cold backup and hot backup.
Deadlock in Pooled Connections
When we talk about deadlock, most of time we thought it’s caused by incorrect resource accessing. That’s correct. Then you dive into your application code and hopefully find a root cause in T-SQL. In some situation, the dead lock happens in a random manner. The place even you think that it’s impossible to get a deadlock gets deadlock. In this case, you need to check whether you are using pooled connection properly.
New Home
I really missed updating my dormant blog, http://sqlx.blogspot.com for long time. I can’t leave without it. Finally, I managed to setup my home on my own server. I will be writing my blog here. All my blog posts in http://sqlx.blogspot.com will be gradually moved to here. I am excited. Here is the picture from the celebration of my blog