Creating a virtual machine with Vboxmanage

Lately, I’ve been creating, deleting and recreating a lot of virtual machines with Oracle VirtualBox. So today, when I needed to start with a clean environment, I decided to create a virtual machine using the command line instead of the GUI.

Clicking through the wizard is a lot easier than creating the virtual machine from the command line, but there are some options I always change after creating the guest. For example, to install Windows, you need to mount a CD. To access other installation files on my laptop, I need to share a local folder. And I like to enable drag ‘n’ drop. So why not automate the creation process, including these settings? This is the script I created, calling VBoxManage.exe:


Update: in version 4.3.20, the program files directory has been changed from:

C:\Program Files\Oracle\VirtualBox


C:\Program Files\Oracle VM VirtualBox

Posted in Uncategorized | 1 Comment

Two wrongs make a right: tricking the query optimizer

Sometimes, the query optimizer makes mistakes. Instead of selecting the most optimal plan, a sub-optimal plan is chosen, and it is your task as a DBA to help SQL choose the right plan. In this case, the optimizer overestimated the selectivity for a WHERE clause, and my solution to this problem was to trick the optimizer into using a clustered index seek instead of a nonclustered index.

This is going to be a short post, since I can only provide partial code. The offending query joined 5 tables, each with millions of records. It had the following WHERE clause:

The query would run for minutes. Analyzing the execution plan showed that the problem was the estimation of the number of records. Based on its statistics, SQL estimated that the first WHERE clause was the most selective. Therefore, SQL started with an index on this table (the top right operator in the query plan). In reality, however, the second WHERE clause was much more selective.

Looking at the statistics, it was pretty obvious why SQL would think that the date clause was not very selective. This particular table contains years of data, but the query would only need records from the last few hours. The statistics on the table are not that granular (for more info on the granularity of statistics, see this Technet article).

At first glance, it was not so obvious why SQL would think that the first clause (LIKE ‘R%NL’) would be selective. The statistics clearly show that most values start with ‘R%’. This is, however, where SQL makes a mistake. It assumes that the combination of the two filters would be a lot more selective. Updating the statistics doesn’t solve this problem.

Having found the underlying problem, my first attempt at a solution was to split the first clause:

That didn’t solve the problem. SQL still estimated that the combination of the filters would be selective enough to start with tableA. The solution I used was to make the clause non-SARGable, by performing an operation on the column:


Normally, this is something you should definitely avoid. A non-SARGable filter eliminates the optimizers ability to estimate whether it could use an index to satisfy this query. In this case, it eliminated the optimizers abililty to make the wrong estimation! So sometimes, two wrongs make a right!




Posted in Performance | Leave a comment

Code samples for my book on Database Fundamentals (MTA 98-364)

A while ago, I’ve published my book on  Database Fundamentals for SQL Server 2008. The book covers everything you need to know to pass the Microsoft exam on this topic, the MTA 98-364. This exam is part of the Microsoft Technology Associate Program. In this post, I’ll supply links to the code samples for the book (so you don’t have to type the code yourself).

Chapter 2 code samples

Chapter 3 code samples

Chapter 4  code samples

Screenshots of the tables

Note: the code is not always exactly the same as in the book, as I’m working on my blog and rewriting the book at the same time.

Posted in MTA 98-364 | Leave a comment

Code samples for my book on Database Fundamentals (MTA 98-364), chapter 3

Chapter 3, Using Multiple Tables.



Posted in Code samples, MTA 98-364 | Leave a comment

Code samples for my book on Database Fundamentals (MTA 98-364), chapter 2

Code samples for chapter 2, using a Single Table.



Posted in Code samples, MTA 98-364 | Leave a comment

Finding the application timeout

The problem
Last week, an application started reporting SQL timeouts. By default, SQL does not have a query timeout; it is the application that decides it doesn’t want to wait any longer. The question was: how do we find out which query causes the timeout? There was no error in the SQL log, so I decided to fire up SQL Profiler.
According to this MSDN article, there is a Profiler event class called Attention that should allow you to trace these timeouts:
The Attention event class indicates that an attention event, such as cancel, client-interrupt requests, or broken client connections, has occurred. Cancel operations can also be seen as part of implementing data access driver time-outs.
I had never used this event class before, so I decided to test whether I could actually find the offending query using this method.

The solution
Let’s replicate the scenario. For that, we need a query that takes longer than the application timeout. The default .Net timeout is 30 seconds, but I’m not the patient type. So I configured Management Studio with a timeout of 5 seconds:

Execution timeout

After that, I ran the following query:

And sure enough, after 5 seconds the query timed out:


Next, I fired up Profiler, and started a new trace.

Trace properties

Running the same query proved that this event class actually does trace timeouts:

Profiler result

To make the trace more efficient, I tried to do the same using Extended Events, since the overhead of Profiler is quite high. Unfortunately though, there is no event class Attention in SQL 2008 (while the test was done in SQL 2012, the actual problem was with a 2008R2 database). But at least I could now find the problem query.

Posted in Performance | Leave a comment

The curious case of the trailing space

Some weeks ago, a developer told me he had found some strange behavior in SQL Server. Someone immediatelly yelled “bug”. Comments like this always grab my attention. More often than not, however, it’s not a bug. That also turned out to be the case here. But still, the behavior was not what I had expected, so I decided to write a blog post about it.

The scenario was this. The name of a customer had been entered twice: once with, and once without a space behind the name. Obviously, the front end application should have checked this, but alas. Now, we had two records for the same customer. When trying to correct the data in the database, the supposed bug appeared in a simple where clause. I can’t show the real data here, so I’ll use the AdventureWorks2012 sample database as an example. In the person table, there is one record for Mr. Syed Abbas:


You would expect not to find this record if you were looking for a person with last name ‘Abbas ‘. However, both queries return the same record, regardless of the number of spaces you put at the end of the string:


You can add a record for Mr. Abbas with several trailing spaces to verify that it works both ways. As it turns out, this is actually the intended behavior. According to this Knowledge Base article, the ANSI SQL-92 standard requires that when SQL compares to strings of different length, it has to pad the shortest string with spaces.

This proves that there is always something new to learn about SQL, even about something as basic as a where clause.

Posted in T-SQL | Leave a comment

Index reorg problem

Recently, we upgraded an application to a new version. The following night, our standard maintenance plan failed. The job output log file gave the following error:

Failed:(-1073548784) Executing the query “ALTER INDEX […] ON […” failed with the following error: “The index “…” (partition 1) on table “…” cannot be reorganized because page level locking is disabled.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

I’ve omitted the object names, because they are irrelevant here. Not all error messages tell you exactly what the problem is, but this one does: The index … cannot be reorganized because page level locking is disabled.

A little background information
Multiple users may require access to the same database object at the same time. This causes concurrency issues: when you’re reading a table, you don’t want somebody else to change the contents of the table while you’re reading, causing you (for example) to read the same record twice. SQL Server solves this problem with locking. There is a hierarchy of objects within the database, with the row at the lowest level and the database at the highest level. There are also different types of locks, that may or may not be compatible with each other, such as SHARED, INTENT SHARED, EXCLUSIVE. To give an example: any user that logs in to a database, will cause a SHARED lock to be placed on the database. This doesn’t block anybody else from logging in, since a SHARED lock is compatible with another shared lock. It does, however, block somebody else who tries to perform an action that requires an EXCLUSIVE lock at the database level; EXCLUSIVE locks are not compatible with other types of lock.
You can easily verify this. Open a query window and connect it to any database. Then, right click the database in the Object Explorer pane and try to rename the database. This will result in an error:

Database lock error

Locking at the page level is more complicated to demonstrate. If you want to know more about that, I will have to refer you to the excellent book by Kalen Delany, Microsoft SQL Server 2008 Internals.
For every query, SQL will automatically decide what locks need to be placed at what levels in the hierarchy. Usually, it is best to trust SQL to make this decision for you; however, there are some situations that you might want to change the default locking behavior.
So SQL decides when it is useful to lock pages of an index. When you create an index, you can specify that you don’t want to allow row locks on the index. This is the T-SQL statement that will achieve that:

Like I said, this is not something that I would recommend that you do without thorough testing; you can read more about these options in this MSDN article.
Back to the problem at hand. The error with our maintenance was caused because several indexes where made that didn’t allow page locks. One of these indexes was in the error message; I found the others with the following T-SQL statement:

Interestingly enough, three of the indexes were called “queue_secondary_index”. These were not part of the application, but instead, indexes on internal tables. Maybe a good topic for another blog post, but not related to the problem at hand, since the reorganization task skips these internal indexes.

The solution
There were three solutions to the problem:
1. Change the allow_page_locks setting back to the default permanently;
2. Change the setting back temporarily, reorganize and then change it once again;
3. Skip these indexes altogether.
According to the software manufacturer, the setting was turned off deliberately, because their tests showed a performance benefit. They advised against turning ALLOW_PAGE_LOCKS back on, even temporarily. So we choose option three: we changed our index reorg script to ignore these indexes. Problem solved.

Posted in Indexes | Leave a comment

Hello world!

This is my first blog post. I’ll skip introducing myself, because you can read more than enough about me in the section that is appropriately labeled “about me”. Instead, I’ll tell you why I’m starting this blog.
The main reason for me to start a blog is to share knowledge, and learn some more in the process. I like learning new stuff, and I like writing. So I’m combining the two: I’ll be blogging mostly about new stuff I learn. I feel that writing about a subject helps to better organize my thoughts, and fill in the gaps in my knowledge. Hopefully, I’ll get some useful feedback, and I’ll learn even more.
The subjects I’ll be blogging about will be technical. Since I’m a SQL Server professional, that will be the subject of most of my blog entries. But I also love gadgets, so I might include some reviews whenever I find a new toy to play with.

Posted in Blogging | Leave a comment