The KeyConvert impact on the Vision database

I was working with a fellow business analyst the other day who was investigating the impact of running a keyconvert on our production database during business hours. She was asking me alot of questions that I could not answer. What exactly is locked, what will cause blocking and so forth. So we set up a test on our developer database server and I will discuss the results below.

While profiling the Vision database during the keyconvert, we encountered many different lock types and lock modes. I will discuss each below. It's important to note that depending on what kind of key convert you run, you many encounter different types of locking and different kinds of lock modes.

Lock Types

11 - METADATA
A lock on a part of the catalog.

5 - OBJECT
A lock on a table or index. An object lock can be acquired on any object existing in sys.all_objects.

6 - PAGE
A lock on a data page in the database. All data in the database exists on an 8kb page.

7 - KEY
A lock on a row in an index.

9 - RID
A lock on a given row in a heap (table with no clustered index).

2 - DATABASE
Any activity on a database causes a database lock. This prevents the database from being dropped, restored or taken offline.

Lock Modes
From a high level, there is a naming convention in the locking mode definition. Below are some of the relevant modes and their definitions.

• Shared (S) Used for read operations like the SELECT statement. No data is changed. It's important to note that the data cannot be changed until the SELECT statement releases it.
• Update (U) Used on resources that can be updated. Helps prevent deadlocking with sessions or trying to read and change data on the same piece of information.
• Exclusive (X) Used for data-modifications like the INSERT, UPDATE and DELETE statements. This mode prevents multiple resources trying to modify the same data at the same time.
• Intent Used establishes a locking hierarchy. The intent locks are intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX).
• Schema Used when an operation is dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S).
• Key-range protects a range of rows read by a query when the serializable transaction isolation level is used. This mode protects the range to make sure that other transactions can't insert rows that would qualify for the queries of the serializable transaction.

Below are the locking modes we encountered during our key convert process.

7=Intent Update Lock (LCK_M_IU)
4=Update Lock (LCK_M_U)
1=Schema Stability lock (LCK_M_SCH_S)
0=NULL - Compatible with all other lock modes (LCK_M_NL)
3=Shared Lock (LCK_M_S)
8=Intent Exclusive Lock (LCK_M_IX)

So I have documented all of these technical definitions and codes, but have not yet committed to discussing what the basic affects are of running a keyconvert on a production database during business hours.

In short, don't run a keyconvert on a production database during business hours. Ideally, do it over a weekend where you can eliminate any usage on the system via normal IT down time or to ensure international usage is limited as much as possible.

In a future post, I hope to document more specific examples outline the KeyConvert impact on Interactive Billing, Actuate/SSRS reporting and simple info center management.

Enjoy.....