July 2005 - Posts

I just came across another handy tool that's installed on Windows Server 2003 (I assume it's on Windows 2000/XP too) by default. Using waitfor.exe you can coordinate the execution of two (or more) parallel batches running on the system where these need to wait for each other. The use of it looks like this:

Batch A:

>waitfor Test /T 60

SUCCESS: Signal received.

Batch B:

>waitfor /SI Test

In this codesnippet, batch A waits for the Test signal for 60 seconds. Party B signals the Test event when needed. If a timeout occurs, the following message will be displayed on A:

ERROR: Timed out waiting for 'Test'.

Del.icio.us | Digg It | Technorati | Blinklist | Furl | reddit | DotNetKicks

If you want to perform basic disk wiping to clean free space on your harddrive, you can use the cipher tool which is included in the Windows OS installation (as from Windows 2000 SP3 on; for earlier Windows 2000 pre-SP3, download it over here or better install SP4). It's not supported on Windows XP Home however. Output looks as follows:

C:\>cipher /w:c
To remove as much data as possible, please close all other applications while
running CIPHER /W.
Writing 0x00
................................................................................
.....................
Writing 0xFF
................................................................................
....................
Writing Random Numbers
................................................................................
....................

As you can see, the syntax includes a /W flag to turn on the wiping mode. As an argument you do specify the volume drive letter or a specific directory. I'm using cipher /w:c to clean all free space on the harddrive. Notice that during executing you can get "Low Disk Space" messages (information -> warning -> critical) because of the wipe data that's being written temporarily to the disk.

The wiping mode of cipher works in three stages: first it writes 0x00 (all zeros), then 0xFF (all ones) and finally random numbers. Use this too to make sure deleted data is really deleted and unavailable, which is important when you want to sell (or throw away) your computer (or the harddisk in it), or when your machine is stolen some day. Use it when you have misused EFS as well. With misuse I mean using EFS to encrypt individual files instead of folders, because EFS creates plaintext copies (for recovery purposes) in that case (thus, when encrypting individual files).

For more complex (and secure) wiping algoritms and support for non-NTFS volumes, you can use other tools instead (e.g. R-Wipe & Clean which includes Department-of-Defense approved algorithms, that take longer but are more secure too). Search results for "disk wipe" can be found here.

Del.icio.us | Digg It | Technorati | Blinklist | Furl | reddit | DotNetKicks

It seems that there is still some use for Scroll Lock on today's systems, which is to generate STOP 0x000000E2, the end-user manually generated Windows crash (h). How to enable this "feature"?

  1. In the registry, go to HKLM\SYSTEM\CurrentControlSet\Services\i8042prt\Parameters
  2. Add a DWORD value called CrashOnCtrlScroll with the value of 1
  3. Restart the computer

When the system has restarted and you want to see a nice blue screen popping up (causing a memory.dmp to be created, that's the official reason why it's here), hold the right CTRL key and press SCROLL LOCK twice.

First I thought this was the key sequence pressed accendentally while showing Windows 98 PnP capabilities some time ago (some of you will remember this), but actually it seems to be supported only on Windows 2000 and higher. I don't know yet this is still supported in Vista :-). Come on, a little kidding should be possible nowadays when we don't see BSoDs popping up anymore spontaneously ;-).

Del.icio.us | Digg It | Technorati | Blinklist | Furl | reddit | DotNetKicks

Introduction

Part 4 of my CLR Hosting journey: scheduling and threading. Today's applications typically rely on threading primitives to perform multiple tasks concurrently. Schedulers are needed in the operating system (and on other places, as I'll show later) to plan the execution of these tasks, because just one task can run at a time on one processor. The CLR, being a layer on top of the Windows OS, has support for threading too (luckily). In the previous versions of the CLR, threading was handled by the operating system directly because the mscorwks.dll (or the server builds) did send calls for threading stuff to the Win32 API directly. Just as it was the case with memory management, the CLR in v2.0 of the .NET Framework has an intermediate layer to forward threading-calls to the host for further processing. The Default CLR Host will call into the Win32 API to perform all of the threading/scheduling, whileas other hosts can choose to take responsibility over this. Again, SQL Server 2005 was the key influence for the CLR team to adapt the design of it to allow customization by hosting applications. The stuff that's covered includes:

  • Task creation and management
  • Thread pool support
  • Synchronization mechanism support (e.g. mutex, semaphore, etc)

In this post, I'll start with the needs of SQL Server 2005 concerning the CLR's scheduling/threading mechanisms. Once I've explained this, we'll jump into mscoree again to explain how a CLR Host can take advantage of the various APIs to control scheduling and threading stuff in pretty much detail.

 

Threading and scheduling in SQL Server

The basics

It's clear that a database server such as SQL Server should be capable of doing a lot of work at (virtually) the same time. In a multi-user database environment, it's key to achieve a great performance and scalability in order to be competent with other vendors. This brings us to the need for an efficient scheduling mechanism to allow the database engine to do various things at a time, in order to server users a fast as possible to process their requests. SQL Server uses a mutli-threading model to accomplish this goal, while running in one single process. This means there is one process with multiple thread and one single memory address space, therefore eliminating all stuff around shared memory. Now, okay, we do have multiple threads, but how are those threads put on the processor to perform work? That's where the User Mode Scheduler comes into play. The User Mode Scheduler (further abbreviated to UMS) is a key component of SQL Server that gives the SQL OS (that's the core engine of SQL Server in relation to resource allocation and control) more control over how threads (and fibers, see further) are scheduled. Every processor has one instance of the UMS associated with it (well, that's only true for the processors that SQL Server is allowed to use, which is configurable trough the affinity mask).

So, we know there are threads being used by SQL Server. Next, you should know that these threads are grouped into thread pools which are dedicated for various kinds of operations. First of all, there is a series of threads that are out there to support the basic core tasks of the SQL Server database engine, such as the lazywriter, the log writer (which writes out log caches - in-memory representations of transaction log entries - to disk based on a flushQueue and a freeQueue to return written-to-disk-caches to), cleanup threads, etc. Beside its own housekeeping, SQL Server should also be able to process requests from users and client applications to retrieve/manipulate data (that's what a database is for, right?). In order to do this, there's a pool of so-called worker threads. Clients submit requests to the SQL Server database through the Net-Library up to the User Mode Scheduler before it reaches the Relational Engine. This is where the UMS kicks in to assign a worker thread to the incoming request for further processing. This request submission is done through an IO completion port through which it's queued up in a completion queue. There are various actions the scheduler can take to assign a thread: either it can take an available thread from the pool, or it can create a new thread (there is a configurable upper limit called max worker threads that defaults to 255 which should be okay), put it in the pool and assign it to the client's request for further processing. The assigned worker thread stays alive and bound to the user's request till completion of the request. Worker threads are divided among the different UMS schedulers that are running on the machine (remember, one for each processor).

Threads versus fibers

SQL Server support two modi which it can run in. The first one is the default and is called "thread mode", the other one is called "fiber mode". In order to understand the difference, let's explain the difference between threads and fibers:

  • Threads are kernel-mode objects known by the OS and act as the default unit of work which can be put on the scheduler. At regular times the (preemptive) scheduler of the Windows OS starts its mission to take a thread off the processor (or off on of the processor on an SMP machine), find another thread (by scanning the list of threads and executing some thread election algorithm, based on thread priorities, credits, etc) and put that thread on the processor to start executing. Switching between threads is called on context-switch and has a relatively high overhead on the system.
  • Fibers are often called lightweight threads and are living for 100% in user mode, so the Windows OS's scheduler doesn't even know about their existence. Code running in user mode is responsible for all of the scheduling work for the fibers to run.

Fiber mode (or lightweight pooling) looks promising but there are a couple of drawbacks, one of those being the fact that not all of the functionality (e.g. SQLMail - if you should use that - and SQLXML) does work in fiber mode. The key recommendation however is to avoid fibers if you can. Tempting as the checkbox in the SQL Server configuration might be, it's generally spoken not a good idea to enable fiber mode (see MSDN too on http://msdn.microsoft.com/library/en-us/dnsqldev/html/sqldev_02152005.asp?frame=true). One scenario where fibers might be advisible is when the overhead of context switching is so high that it starts to hurt other work that needs to be done by the server.

The User Mode Scheduler

In this paragraph, I'll explain the basics of the User Mode Scheduler. If you want to know more about it, I recommend you to take a look at the book "The Guru's Guide to SQL Server Architecture and Internals" by Ken Henderson, chapter 10. First of all there's the UMS thread running on every processor that can be used by SQL Server. There's only one such thread per processor. It's the task of the OS to schedule this thread on any of the available processors. When you do configure an affinity mask you tell SQL Server which CPUs it can use which is likely not a good idea because it causes overhead on the UMSs to schedule their threads on the same processor every time (the OS simply is prevented from choosing any free processor to put the UMS' thread on).

All code running in SQL Server (because of user requests for example, performing transactions, queries, etc) is scheduled by the UMS. If there's non-SQL Server code that has to be run, the UMS will keep its hands off it and leave it to the OS's scheduler to perform the work (e.g. extended procedures). The reason to introduce a UMS to control the scheduling originates from the SQL Server 7.0 timeframe where it became clear that relying on the underlying OS to perform all of the scheduling was not flexible enough and the scalability potential of the product was hindered because of this.

The UMS lives in a file called ums.dll inside the Binn folder of your SQL Server installation. The overall goal of the UMS is to avoid wasting processor cycles because of things such as context switches and a thread switching between user and kernel mode. The UMS contains a subset of the functionality of the Win32 threading functionality, containing only those things that are relevant for SQL Server. When the UMS performs its work, it calls into the Win32 functions to do further work, after controlling SQL Server's needs to achieve high scalability and performance. Actually the UMS is nothing more than a thin layer between the OS and the database engine when it comes down to scheduling and threading.

Now, the big difference between SQL Server's scheduling and the scheduling implemented by the Windows OS. SQL Server's UMS performs so-called cooperative scheduling whileas the Windows OS (since NT) works based on preemptive scheduling. What do these two terms mean?

  • Preemptive scheduling: The scheduler doesn't trust anyone and kicks in at regular times to take a currently running thread from the processor it was assigned to, to save that thread's state (stack, program counter, registers, etc), find another thread to run and put that thread on the processor by restoring its state and waking it up. This way, no single program or thread can monopolize the processor which can bring down the computer (in a sense that no-one else is allowed to run, e.g. explorer.exe would starve, the OS seems to hang).
  • Cooperative scheduling: "We're friends, aren't we?" The UMS knows it can trust the threads that are part of the game and that these threads will voluntarily yield. As the matter in fact, all of the thread's code inside SQL Server is implemented by the SQL Server team, so it's possible to know the behavior of it. However, if there is one single thread that does not yield (and thus acts as a bad guy), the whole database system will be hurt because of this.

The UMS uses the Win32 API to reach its goal on an underlying preemptive OS. The basic mechanism it employs is the following. Every thread running in a UMS has an event object that can be signaled. When the corresponding UMS for the thread does not want the thread to be scheduled (cf. worker thread pool etc), it asks that thread to start an infinite wait on its event object by calling WaitForSingleObject. Because of this, the OS will judge that this thread is waiting and can't possibly do anything, therefore bypassing it when looking (in a preemptive manner) for a thread to be put on the processor to start its work. At a certain point in time, the UMS might want the thread to start doing something and signals the event object of that thread. Because of this, the WaitForSingleObject call ends and the thread comes alive. Now, the Windows scheduler will see that the thread is alive and kicking and ready to do some work, so it can be selected by the scheduler to run on some processor. In order to avoid context switches and swapping on processors, the UMS will attempt to keep the number of viable threads as low as possible, ideally having only one thread per processor. That way, the Windows OS has no choice but to select that "dedicated to a given processor" thread.

To run extended procedures, the UMS can't even think of controlling it in a cooperative scheduled fashion because the code in the extended procedure can't be trusted and it can't even indicate to yield. Because of this, preemptive scheduling for extended procedures (and other sort of stuff such as OAs, debugging, distributed queries, etc) is used. On its turn, this causes a drop on scalability and concurrency because the UMS isn't able anymore to serve a lot of requests on a limited set of workers.

An explanation of the UMS scheduler itself and all of the associated lists would bring us too far. Instead, I'll just mention these and give a short one-line description:

  • Worker list - contains all of the available UMS workers, encapsulating a fiber or thread
  • Runnable list - all of the UMS workers ready to execute but waiting to be signaled by the UMS (which is done indirectly by another worker calling yield)
  • Waiter list - UMS workers waiting for a resource; another worker that holds the resource is responsible to signal the waiting worker by scanning the waiter list
  • I/O list - list containing outstanding asynchronous I/O requests waiting for completion; any yielding worker has to walk through this list to check for completed asynchronous I/O operations and to wake the corresponding worker
  • Timer list - list with timed work requests; any yielding worker has to walk through this list to check for expired timer requests and to wake the corresponding worker

As you can see, voluntarily thread yielding is a key job for each of the workers.

You can check the statistics of the UMS by calling DBCC SQLPERF(umsstats):

Statistic                        Value                   
-------------------------------- ------------------------
Scheduler ID                     0.0
  num users                      16.0
  num runnable                   0.0
  num workers                    12.0
  idle workers                   5.0
  work queued                    0.0
  cntxt switches                 15491.0
  cntxt switches(idle)           18763.0
Scheduler Switches               0.0
Total Work                       11951.0

(10 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Note, the results shown above are these from a system that has just been started a couple of minutes ago. On a system that's up and running for a longer time, you should see far higher numbers for the total work and the context switches.

 

CLR Hosting

In this first post about scheduling and threading in the CLR Hosting APIs I'll give a brief overview of the basic concepts and API functions you should know about. In the next posts of CLR Hosting part 4, I'll dig into more details by taking a look with you at the CoopFiber sample that's included in the .NET Framework v2.0 SDK. More information about this sample can also be found on Robert "Dino" Viehland's blog. Dino's working on the CLR team as an SDE/T and has been posting about this sample too in the past.

Tasks

Okay, so now how does SQL Server 2005 can make sure that cooperative scheduling can be combined with the CLR integration stuff. Before v2.0 of the CLR, the CLR was only capable of working on a preemptively scheduled operating system layer. The hosting APIs did not provide a means to put the CLR on top of a cooperatively scheduled "host". As a basic notion, the CLR Hosting API provides the notion of a task which is the abstraction of an underlying unit of execution, being either a thread or a fiber (see above for the explanation of both terms). Based on the mode SQL Server 2005 is running in, the notion of a task is mapped either to a thread or a fiber on the OS.

Basically, there are 4 interfaces:

  • IHostTaskManager - interface to create your own task manager that the CLR should obtain a reference of through the IHostControl::GetHostManager method as explained in previous posts
    • This interface provides support to create tasks, perform switching between tasks, putting a task asleep, indicate managed/unmanaged code transitions, etc
  • ICLRTaskManager - the IHostTaskManager interface contains a method called SetCLRTaskManager, which is used by the CLR to pass in an instance of an ICLRTaskManager object; this is the side of the CLR in relation to task management
    • In here you'll find a limited set of functionality that's similar to the functionality in the IHostTaskManager. I'll cover this further in this post.
  • IHostTask - the host's notion of a task
    • Used to start, stop, join, alert tasks and to get/set priorites.
  • ICLRTask - the CLR's notion of a task; as with the ICLRTaskManager, the CLR provides the IHostTask instance with an instance of ICLRTask through a method called SetCLRTask
    • This interface contains methods to communicate to the CLR, for example to notify it that the host is scheduling/unscheduling a task (SwitchIn/SwitchOut methods). Other functionality includes yielding (cf. SQL Server elaboration on cooperative scheduling), an ExiTask function and functions to Abort and RudeAbort (the diffence will be explained later) a task. There are also functions for statistics information (memory statistics, number of held locks).

A task has a lifecycle that you should understand in order to implement the IHost* interfaces correctly. Let's show such a lifecycle:

  1. A task is created and starts its live in an unscheduled state. The CLR calls the IHostTaskManager::CreateTask method and creates an instance of ICLRTask which is passed to the IHostTask through the SetCLRTask method.
  2. The task is started by calling IHostTask::Start.
  3. The host decides to schedule the task. Using the ICLRTask reference it got in step 1, it calls SwitchIn to tell the CLR that the task will be scheduled.
  4. Now the task is up and running.
  5. When the host decides to switch out the task, it can call the SwitchOut method on the ICLRTask object, in a similar way as explained in step 3. The host can decide this based on various conditions, including I/O completion (e.g. relation to the I/O List in the UMS of SQL Server) or synchronization blocking (e.g. access to a resource protected with mutex/semaphore).
  6. Later on, the task can be scheduled again, using SwitchIn as explained in step 3. However, this time it might end up on another physical OS thread (when being mapped to threads). The CLR can indicate thread affinity however when it needs this for the particular thread. It does this by calling BeginThreadAffinity and EndThreadAffinity on the IHostTaskManager. In that case, the host must reschedule the task on the same OS thread.
  7. A last situation occurs when the task has completed its work. In that case, the host calls ICLRTask::ExitTask to notify the CLR about this.

Aborts and rude aborts will be covered later. Notice however that these are communicated to the CLR too by the host. This stuff has to do with finalization as I'll explain later on in further posts. Finally, there is a Reset method on ICLRTask too, to clean up the task and to enabe it to be reused by the CLR in the future (rather than calling ExitTask to destroy the task on the CLR level).

Note: Beside of threads created through the BCL functions (and later on, redirected to the host to perform the actual work) such as the Thread class, the CLR creates some management threads when it's loaded through the Win32 API always. These threads are responsible for various tasks such as garbage collection (gc and finalizer), debugging (debugger helper thread), thread pool access control (timer, gate, worker, waiter and I/O completion thread), internal timing.

Managed/unmanaged code execution transitions

Transitions between unmanaged code and managed code (in both directions) can be intercepted by providing a host manager for scheduling too. Examples include the use of COM interop to call unmanaged code from within a managed code context and the use of function pointers (in managed code that corresponds to a delegate) to call back into managed code by using such a pointer that was marshaled to native code. Hosts that use cooperative scheduling (such as the SQL Server 2005 CLR Host) need to know when execution leaves managed code, because from that point on, threading is no longer under the control of the CLR. Therefore, the host can't control the scheduling anymore (refer to the voluntary yielding of threads where the mechanism of cooperative scheduling is built on), so it has to grant the thread to be scheduled preemptively by the OS to continue its work.

In order to support those scenarios, the CLR calls the host ot notify the host about these managed/unmanaged code transitions using four methods on the IHostTaskManager object:

  • LeaveRuntime - we're going to unmanaged code now (preemptive scheduling required)
  • EnterRuntime - we're back in managed code, under the control of the CLR (cooperative scheduling possible)
  • ReverseEnterRuntime - unmanaged native code calls back to managed code through a function pointer
  • ReverseLeaveRuntime - the managed code execution initiated by unmanaged code (see previous bullet) stops executing, causing a transition to unmanaged code again

Leave* and Enter* methods are always paired.

 

To be continued

In the next subpart of CLR Hosting part 4, I'll cover the stuff around synchronization management and thread pooling. After having covered this stuff, I'll take a look at the CoopFiber sample together with you.

Del.icio.us | Digg It | Technorati | Blinklist | Furl | reddit | DotNetKicks

Introduction

While preparing some stuff around SQL Server 2005, I thought it was the ideal occasion to do some blog posts about the SQL Server 2000/2005 architecture and several concepts. In this post I'll cover transaction isolation levels, later I'll post more about transactions while talking about the new System.Transactions namespace. However, let's start by taking a look at transaction stuff inside the database itself.

 

Transactions: a short definition

Tons of people have given a definition of a transaction before me, so I won't cover this in much detail. I'll just try to give a definition for a transaction in order to create some context for further elaboration. Basically, a transaction is a set of statements (in our case database statements) that have to be executed in an atomic fashion (either all of the statements succeed, or all of them fail together). Next, there's the consistency property, which means that a transaction should bring the system from one consistent state to another consistent state. During the transaction, isolation has to be maintained. This means a transaction is out there on its own, it can't see what others are doing. Locking will be used to accomplish these requirements, as I'll explain in a moment. Last but not least, there's the durability property. A transaction can finish either by a commit (changes are allowed) or a rollback (changes are rolled back to the last consistent state). Durability means that when a transaction is committed, the changes made to the database should be persistent even a disaster occurs one processor cycle after the transaction commit.

The grouping of these four properties is abbreviated as the ACID properties. Transactions play a central role in database technology, especially in multi-user environments. A classic example of a transaction is the work that has to be done to transfer money from one bank account to the other. It's atomic, because the money has to be deleted from account number one and has to be added to account number two in an atomic way. It's consistent because we don't want to loose or create any money (the total sum of money aften and before the transfer has to be the same). It's isolated because others should not see an intermediate state when the money has left account number one and is not on the second account yet. Last, it's durable because we want to keep the committed state forever (on a database restart, the money should not be back on the old account).

 

SQL Server 2000 transaction isolation modes

In my definition of transactions, I mentioned the isolation property. The fact that transactions can't see anything from each other is simply an overstatement (or maybe it can even be called overengineering) in a lot of scenarios. Basically, there is one big tradeoff to be made: the level of concurrency versus the level of isolation. The higher the throughput you want to reach, the lower the level of isolation should be. Why is this? In one word, locking. In order to make sure others can't perform certain operations on certain objects (I'm keeping this vague intentionally for the moment) you should get a certain lock on these certain objects. Examples are shared locks and exclusive locks in database technology.

In SQL Server, a shared lock is acquired when data is read. More than one party can get a shared lock on the data, but nobody can get an exclusive lock on the data while there's still a shared lock on it. An exclusive lock is the result of a data modification (e.g. insert, update, delete). What's in a name? An exclusive lock is granted exclusively to one party (one process). Other parties can't get any type of lock on the data while there is such an exclusive lock on it. When the transaction completes, the lock can be released.

One other thing before we jump into transaction isolation modes. The SQL Server database engine contains a transaction manager which is responsible for everything around transactions, including cross-db transactions, distributed transactions, the support of savepoints, nested transactions, isolation level support, etc. A last concept you should have in mind is the concept of write-ahead logging. Simply stated, write-ahead logging ensures that rolling back work in process and rolling forward committed work to be written to the physical data pages is always possible. The idea is that the transaction log is populated with the changes that a transaction has made before the transaction is acknowledges as being committed. The system can't simply continue until the transaction's work has been logged to the transaction log (that is, synchronously).

Now back to the isolation modes. SQL Server 2000 has four different lock modi, as I'll explain now.

Uncommitted read

The weakest transaction isolation level is called uncommitted read. This means a transaction can read any data that's currently living on a data page, being committed or not. In fact, there is no isolation at all. There are simply no locks whatsoever when running in this mode, even no shared locks when reading data. So, you don't issue locks that can affect others and you don't have to wait for locks yourself either. Although this behavior affects concurrency positively, it's not the desired behavior for e.g. the financial application (money can get lost or can be created, because transactions can read intermediate states of other transactions, breaking the isolation). In data analysis scenarios this can be useful nevertheless.

Committed read

This is the default level. Data which has been changed can't ever be read by another transaction when it's not yet committed. During the data change (before the commit) an exclusive lock is held on the data. Other transactions have to wait for this lock to be released (and to acquire a shared lock) in order to continue. Also, data that is being read needs to hold a shared lock, because this blocks others from changing that data till the lock is released. However, shared locks can be released before the transaction is committed, when the read of the data has completed. This introduces the concept of non-repeatable reads. Basically this means that a transaction that is reading the same data more than once during the transaction can see different data on every read (because the shared lock doesn't need to be held in between two reads of the same data). Another concept is that of phantoms, which are new rows that appear in a table (or view) during a transaction. So, when you issue the same query more than once, new rows may have slipped in.

Repeatable read

As the name already tells us, this level ensures no non-repeatable reads can occur. The idea is that a shared lock is held till the transaction comes to an end, which negatively affects the overall performance because of possible lock contention (data which you are locking by a shared lock during a long transaction can't be modified by another transactions/user for the duration of your transaction; so it's key to reduce the length of transactions as much as you can). Therefore you can issue the same query multiple times in the context of the same transaction without having to worry about possible changes to the data you retrieved. However, phantoms still can occur.

Serializable

The summum of transaction isolation is called serializable. Basically serializable means that the result of running more than one serializable transaction concurrently is the same as running those transactions in a serialized fashion, one at a time. Next to the properties of repeatable read, phantoms can't occur too. This is done by locking on a higher level than the row/record level, e.g. the table level. The reason for this approach is the fact that we need to make sure no new rows can be added to the result sets we're using for the duration of our transaction. Assume you're querying a table for all registered members born between 1983 and 1985. If you do query this data for a second time in a serialized transaction, you don't want any phantom row to slip in. To accomplish this, SQL Server will acquire a key-range lock on the data(range) so that's impossible for another transaction to add rows to this data set until the lock is released. There are nine subtypes of key-range locks in SQL Server 2000, more info can be found in the Books Online.

 

How to set the transaction isolation level?

Of course, that's the last thing you should know in order to start playing around with transaction isolation levels. To set the isolation level, use the SET TRANSACTION ISOLATION LEVEL T-SQL statement. You can specify SERIALIZABLE, READ COMMITTED, REPEATABLE READ and READ UNCOMMITTED. More information can be found on MSDN.

 

What about SQL Server 2005?

The answer is simple: there are a couple of new modi concerning transaction isolation levels, called snapshot isolation. I won't cover this myself, but rather I want to point to an extract of the book "A First Look at SQL Server 2005 for Developers" which is available on http://www.informit.com/articles/article.asp?p=327394&seqNum=2&rl=1.

Del.icio.us | Digg It | Technorati | Blinklist | Furl | reddit | DotNetKicks

I just launched my VPC with the beta 2 bits of the .NET Framework v2.0 (it's currently not on my primary OS installation as that one is currently under high other-beta-pressure, but things will change soon when I receive a new harddisk which I'll partition in a few OS install partitions, including one for Vista beta 1) and did check the mscoree.idl file for the changes to the IHostMemoryManager I told you about in the previous part 3 post of my CLR Hosting series. Here are the renewed interfaces:

[
    uuid(7BC698D1-F9E3-4460-9CDE-D04248E9FA24),
    version(1.0),
    helpstring("Host memory manager"),
    pointer_default(unique),
    local
]
interface IHostMemoryManager_DeleteMe : IUnknown //[BDS] Note: this is the interface I've shown you previously
{
    HRESULT CreateMalloc([in] BOOL fThreadSafe,
                         [out] IHostMalloc **ppMalloc);

    HRESULT VirtualAlloc([in] void*       pAddress,
                         [in] SIZE_T      dwSize,
                         [in] DWORD       flAllocationType,
                         [in] DWORD       flProtect,
                         [in] EMemoryCriticalLevel eCriticalLevel,
                         [out] void**     ppMem);

    HRESULT VirtualFree([in] LPVOID      lpAddress,
                        [in] SIZE_T      dwSize,
                        [in] DWORD       dwFreeType);

    HRESULT VirtualQuery([in] void *     lpAddress,
                         [out] void*     lpBuffer,
                         [in] SIZE_T     dwLength,
                         [out] SIZE_T *  pResult);

    HRESULT VirtualProtect([in] void *       lpAddress,
                           [in] SIZE_T       dwSize,
                           [in] DWORD        flNewProtect,
                           [out] DWORD *     pflOldProtect);

    HRESULT GetMemoryLoad([out] DWORD* pMemoryLoad,
                          [out] SIZE_T *pAvailableBytes);

    HRESULT RegisterMemoryNotificationCallback([in] ICLRMemoryNotificationCallback * pCallback);
}

typedef enum
{
    MALLOC_THREADSAFE = 0x1,
    MALLOC_EXECUTABLE = 0x2,
} MALLOC_TYPE;

[
    uuid(7BC698D1-F9E3-4460-9CDE-D04248E9FA25),
    version(1.0),
    helpstring("Host memory manager"),
    pointer_default(unique),
    local
]
interface IHostMemoryManager : IUnknown //[BDS] Note: this is the renewed interface
{
    HRESULT CreateMalloc([in] DWORD dwMallocType, //[BDS] Note: change I mentioned previously too
                         [out] IHostMalloc **ppMalloc);

    HRESULT VirtualAlloc([in] void*       pAddress,
                         [in] SIZE_T      dwSize,
                         [in] DWORD       flAllocationType,
                         [in] DWORD       flProtect,
                         [in] EMemoryCriticalLevel eCriticalLevel,
                         [out] void**     ppMem);

    HRESULT VirtualFree([in] LPVOID      lpAddress,
                        [in] SIZE_T      dwSize,
                        [in] DWORD       dwFreeType);

    HRESULT VirtualQuery([in] void *     lpAddress,
                         [out] void*     lpBuffer,
                         [in] SIZE_T     dwLength,
                         [out] SIZE_T *  pResult);

    HRESULT VirtualProtect([in] void *       lpAddress,
                           [in] SIZE_T       dwSize,
                           [in] DWORD        flNewProtect,
                           [out] DWORD *     pflOldProtect);

    HRESULT GetMemoryLoad([out] DWORD* pMemoryLoad,
                          [out] SIZE_T *pAvailableBytes);

    HRESULT RegisterMemoryNotificationCallback([in] ICLRMemoryNotificationCallback * pCallback);

    HRESULT NeedsVirtualAddressSpace(
        [in] LPVOID startAddress,
        [in] SIZE_T size
        );

    HRESULT AcquiredVirtualAddressSpace(
        [in] LPVOID startAddress,
        [in] SIZE_T size
        );

    HRESULT ReleasedVirtualAddressSpace(
        [in] LPVOID startAddress
        );

}

As you can expect, the first one (suffixed with _DeleteMe) will be removed later on, as mentioned somewhere else in the IDL too:

//!!! Delete this one only after SQL integrates Whidbey beta2 bits.
// IID IHostMemoryManager_DeleteMe : uuid(7BC698D1-F9E3-4460-9CDE-D04248E9FA24)
cpp_quote("EXTERN_GUID(IID_IHostMemoryManager_DeleteMe, 0x7BC698D1, 0xF9E3, 0x4460, 0x9C, 0xDE, 0xD0, 0x42, 0x48, 0xE9, 0xFA, 0x24);")

Developer's notes everywhere, that's why I like beta software :-). You can even find places with DeleteMe2 suffixes for the moment :o. It also shows the versioning hell of pre-.NET stuff.

Del.icio.us | Digg It | Technorati | Blinklist | Furl | reddit | DotNetKicks

Well, that's a little exaggerated, but nevertheless I love chkdsk. A couple of minutes ago I plugged in my external 160GB harddrive on the firewire port of my laptop to run some VPCs for demo preparation. However, only a couple of folders showed up in Windows Explorer, all the rest was gone. And a little nice cute balloon popped up in my system tray telling me about volume corruption. First thought: this is a big bad dream :s.

However, chkdsk /f did the trick. It was the first time I saw chkdsk with so much output:

C:\Documents and Settings\Administrator>chkdsk m: /f
The type of the file system is NTFS.
Volume label is Matrox.

CHKDSK is verifying files (stage 1 of 3)...
File verification completed.
CHKDSK is verifying indexes (stage 2 of 3)...
Correcting error in index $I30 for file 5.
Correcting error in index $I30 for file 5.
Sorting index $I30 in file 5.
Index verification completed.
CHKDSK is recovering lost files.
Recovering orphaned file SYSTEM~1 (27) into directory file 5.
Recovering orphaned file System Volume Information (27) into directory file 5.
Recovering orphaned file VIRTUA~1 (39) into directory file 5.
Recovering orphaned file Virtual PC Images (39) into directory file 5.
Recovering orphaned file Vsbeta.zip (867) into directory file 5.
Recovering orphaned file SQLSER~1 (868) into directory file 5.
Recovering orphaned file VS2005~1 (1787) into directory file 5.
Recovering orphaned file VS2005 + SQL2005 Beta 2 (1787) into directory file 5.
Recovering orphaned file Test (6111) into directory file 5.
Recovering orphaned file test.vhd (6201) into directory file 5.
Recovering orphaned file VISUAL~1 (12926) into directory file 5.
Recovering orphaned file Visual Studio Projects (12926) into directory file 5.
Recovering orphaned file SYSTEM~2 (22003) into directory file 5.
Recovering orphaned file system backup 21 sep 04 (22003) into directory file 5.
Recovering orphaned file TimeZone.msi (102917) into directory file 5.
Recovering orphaned file VS2005~1.ZIP (102918) into directory file 5.
Recovering orphaned file VS2005EE_EVAL.zip (102918) into directory file 5.
Recovering orphaned file sqlxml.msi (102929) into directory file 5.
Recovering orphaned file VPC-AV~1 (102938) into directory file 5.
Recovering orphaned file VPC - Avalon CTP November 2004 (XP SP2) (102938) into d
irectory file 5.
Recovering orphaned file SQLSER~2 (102993) into directory file 5.
Recovering orphaned file SYSTEM~3 (103719) into directory file 5.
Recovering orphaned file SystemFolder (103719) into directory file 5.
Recovering orphaned file srsp1.exe (103934) into directory file 5.
CHKDSK is verifying security descriptors (stage 3 of 3)...
Security descriptor verification completed.
CHKDSK is verifying Usn Journal...
Usn Journal verification completed.
Correcting errors in the Volume Bitmap.
Windows has made corrections to the file system.

 160079660 KB total disk space.
 157652756 KB in 91458 files.
     31184 KB in 12001 indexes.
         0 KB in bad sectors.
    177724 KB in use by the system.
     65536 KB occupied by the log file.
   2217996 KB available on disk.

      4096 bytes in each allocation unit.
  40019915 total allocation units on disk.
    554499 allocation units available on disk.

As you can see, it can actually fix things too. Wow!

Now, the moral of the story. Never ever enable the following things on removable harddrives:

  • Optimize for performance

    This setting enables write caching in Windows to improve disk performance. To disconnect this device from the computer, click the Safely Remove Hardware icon in the taskbar notification area.
    • Enable write caching on the disk

      This setting improves disk performance, but a power outage or equipment failure might result in data loss or corruption.
      • Enable advanced performance

        Recommended only for disks with a backup power supply. This setting further improves disk performance, but it also increases the risk of data loss if the disk loses power.

Guess what, I marked all checkboxes some time ago to speed up my machine when working in VPCs all the time. I should have been warned by all these nice messages. Maybe it would be even better if Windows would pop up a message box with an exclamation mark and a default button set to 'No'. Or just prohibit this on external harddrives. Now, I did revert the option to

  • Optimize for quick removal

    This setting disables write caching on the disk and in Windows, so you can disconnect this device without using the Safe Removal icon.

Well, this description is a little ameliorated I guess, so I still do use the safe removal icon for hardware removal. Most important however is the fact it will safe me from painful chkdsk experiences (I hope).

Del.icio.us | Digg It | Technorati | Blinklist | Furl | reddit | DotNetKicks

Well, the title should be clear. You can grab the bits over here. One extract from the website:

This Beta1 "RC" release supports Visual Studio 2005 Beta2 and the .NET Framework 2.0 beta 2.  There is also an updated WinFX SDK, including documentation, samples and tools available for this release. ... Supported Operating Systems: Windows Server 2003; Windows XP Service Pack 2

The WinFX SDK Beta1 RC can be downloaded over here. Both files are ISOs, so you can use the VCD ISO tool from MSDN to mount it to a drive letter, or you can burn it to a cd-rom, or you can attach it to a virtual machine in Virtual PC or Virtual Server directly.

Ready for a download of over half a gig? Well, you should to become a real Indigoing Avaloner guy :-).

Del.icio.us | Digg It | Technorati | Blinklist | Furl | reddit | DotNetKicks

In my spare free time, I'm doing some Exchange Server 2003 administration too. Currently I'm performing a defrag of the Exchange mailbox store. In this post I'm listing the steps to take in order to do this. Reason: personal reference, share concrete admin task procedures with the community.

  1. In Exchange System Manager, navigate to the Servers, <name of server>, First Storage Group, Mailbox Store node.
  2. Right click this node and choose to dismount the store. Note: the mailboxes will become available when doing this.
  3. Go to the command line, navigate to the folder where the .EDB file of the mailbox store lives. You can find this through the properties of the mailbox store, tab Database, textboxes Exchange database (contains the mailboxes) and Exchange streaming database (contains additional information, e.g. attachments for use with IMAP4 and POP3, needed for the support of non-MAPI clients).
  4. Run eseutil /d priv1.edb, where priv1.edb is the edb file of the mailbox store and eseutil is found in the bin folder of the Exchange installation folder (c:\program files\exchsrv).
  5. When the defrag is running, a set of temporary files will be created. When the defrag is successful, the original files will be replaced by the newly created (defragged) files.
  6. Now it's stafe to mount the store again.

Additionally, I'm executing an integrity check using eseutil /g between step 5 and 6.

Del.icio.us | Digg It | Technorati | Blinklist | Furl | reddit | DotNetKicks

If you want to get some attention by showing up with a Windows installation booting without the classic Windows Logo, you can enable the option SOS in the boot.ini file. This causes the NT4 approach to be taken, i.e. just displaying the version number of the OS, the number of processors and the amount of physical memory installed in the box. A sample of my current boot.ini file is shown below:

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /NoExecute=OptOut /SOS

The NoExecute flag is new to SP1 of W2K3 (and SP2 of Windows XP) and is used to configure the DEP (data execution prevention) feature of the OS.

Del.icio.us | Digg It | Technorati | Blinklist | Furl | reddit | DotNetKicks

More Posts « Previous page - Next page »