Locking and Blocking (1) – Tools

by

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.

Read more

SQL Saturday #92 @Portland

by

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)

by

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.

Read more

Procedures for API Cursor

by

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.

Read more

SQL Saturday #92 @Portland

by

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

by

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)

Read more

A Procedure for Creating Database Snapshot

by

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

by

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.

Read more

Deadlock in Pooled Connections

by

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.

Read more