Monday, April 26, 2010

Slide Deck and Scripts for the SQL Saturday 44 Locking, Blocking, Deadlocking and Isolation Levels Session

The Powerpoint 2007 slide deck can be downloaded here:
Slide deck

The following zip files contains the scripts used to show read committed not taking shared locks, the READ UNTIL pattern, a Profiler template to use for doing lock research, and a few other random scripts:
Scripts

The code for the Blocking Analyzer will be available at a later date.

Friday, April 23, 2010

SQL Saturday 44 Presentation

Tomorrow is SQL Saturday 44 in Huntington Beach, California. I will be giving a presentation focused on locking. I will be posting the slide deck and SQL scripts later in the weekend or early next week.

Tuesday, March 30, 2010

Modifying System Tables in SQL Server 2005 and 2008

Since SQL Server 2005, the user-accessible metadata tables have been replaced with new catalog views, and the metadata tables have been made inaccessible through normal means. In this post, I am going to show you not only how to query these new system tables, but also how to modify them. In addition to the new system tables, raw Service Broker queues are also hidden away in new internal tables.

To start, create a new test database in SQL Server 2005 or 2008. Then, run the following query in your new database:

select * from sys.objects where type in ('S', 'IT');
Query 1.

You will see around 50 rows in SQL Server 2008, with the first few rows below:


 Figure 1.


Notice that they all have a schema_id of 4. Query sys.schemas and you will see that schema_id 4 is sys.

Now, let's try to select from one of these system tables:

select * from sys.sysschobjs;
Query 2.


You will get the following error:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.sysschobjs'.

If you try to select from one of the internal tables, you'll receive the same error.

Going back to the query of sys.objects, turn on Include Actual Execution Plan, re-run the statement and look at the execution plan:
Figure 2.

Zooming in on the top right shows the following:
Figure 3.

Here we have a clustered index scan on the sysschobjs table. Looking back at Figure 1, notice that this is one of the system tables.

Now, for reference, create a sample table to see how it shows up in sys.objects:
CREATE TABLE dbo.MyTable (
ColumnOne int NOT NULL,
ColumnTwo int NOT NULL
);
Query 3.

Query sys.objects again. Notice that the new table has a type of 'U', and of course, you can select from it without error.

To gain read-only access to system and internal tables simply requires use of the Dedicated Administrator Console (DAC). First, connect to your database using the DAC. To do this from SQL Server Management Studio, go to File -> New -> Database Engine Query. Put ADMIN: in front of your instance name and click Connect. Change to the your test database using the USE command. Now run your select query against sys.sysschobjs. You will see something a bit different from sys.objects:

Figure 4.

This table contains less descriptive column names, does not contain the type_desc column, and inludes some internal status values.

Let's find the real column metadata for dbo.MyTable. First, query sys.tables to get the object_id for the table. In my case, it was 2105058535. Then, query sys.columns for that object_id and view the actual execution plan:
select * from sys.columns where object_id = 2105058535;
Query 4.

Figure 5.

We can see here that the column metadata is stored in table syscolpars. Do a quick select against it to get familiar with its column names. You will notice that object_id from sys.columns is replaced by id in this table. Query sys.syscolpars with id equal to your user table's object_id. You will see something like the following:

Figure 6.

For sake of argument, let's say we needed to correct a value in one of the system tables. Say we need to change the name of a column in a table and we had no other way of doing that (i.e. forget about sp_rename). Try to rename ColumnOne to NewColumnOne using a regular update statement:
UPDATE
sys.syscolpars
SET
name = N'NewColumnOne'
WHERE
id = 2105058535 AND
colid = 1;
Query 5.

You will receive the following error:
Msg 259, Level 16, State 1, Line 2
Ad hoc updates to system catalogs are not allowed.

At this point, it might seem as if all is lost. But alas, Microsoft understands that in some cases, it will be necessary to correct problems in these system tables. However, they severly restrict the ability to do so. One can speculate that they have restricted access to prevent metadata corruption. Another performance related reason will become apparent later.

So, how do we go about modifying a system table? To do so, the SQL Server instance must be run in single-user mode and the DAC must be used. (Note: Running the instance in single-user mode is not the same as changing a database to single-user mode.) To run the instance in single-user mode, stop your instance using NET STOP on the command-line, the Services MMC snap-in, or any other way. Then, open a command prompt and navigate to the folder holding your instance's sqlservr.exe executable. My instance's sqlservr.exe file was located at "C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn". The location to your executable can be found by looking in the Services MMC snap-in, under Properties ->  Path to executable. Once in the proper fulder, run the following command:
sqlservr.exe -m

Note: If you are running on Windows 7, make sure you run an administrator mode command prompt or you will get missing path errors.

During startup, you will see a variety of information displayed to the console. It is similar to the information you can see in the SQL Server Error Log during startup, and is related to settings and standard startup database recovery. Buried in the output will be an informational message about running in single-user mode.

Now that our instance is back up, connect to it using a non-DAC connection. Try running Query 2. You will get the same invalid object error. Now, close that connection and reconnect using the DAC. Re-running Query 2 will succeed. Now, lets re-run our update to the columns related system table and see what happens. Checking the Messages tab in the output shows the following:
(1 row(s) affected)

(1 row(s) affected)
Warning: System table ID 41 has been updated directly in database ID 11 and cache coherence may not have been maintained. SQL Server should be restarted.

Go to your command prompt window and notice that the same warning has been displayed there. Looking back a few paragraphs, we can now see how these new system tables may have some kind of performance implication, as caching is almost always used to improve performance. It is possible that Microsoft removed raw access to these tables in order to perform some new, undisclosed type of metadata caching to increase overall SQL Server performance.
 
Go ahead and query sys.columns again using Query 4. The column has been renamed. Now run the following query against your user table:
select * from dbo.MyTable;
Query 6.

It now shows the new column name.
 
Jailbreaking
 
In a future post, I will attempt to describe a way to jailbreak a database so that simple sa connections can be used to modify the system tables in an ad-hoc manner. My current jailbreaking techniques are incomplete and lead to too many undesired side-effects.