Robert's technology blog

Creating a virtual machine with Vboxmanage

Posted on July 15, 2014 by Robert

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:

REM General

SET "vmname=DC01"
SET "memory=2048"
SET "description=Windows 2008 64 bit domain controller"


"C:\Program Files\Oracle\VirtualBox\Vboxmanage" createvm --name %vmname% --register --ostype Windows2008_64
"C:\Program Files\Oracle\VirtualBox\Vboxmanage" modifyvm %vmname%--clipboard bidirectional 
"C:\Program Files\Oracle\VirtualBox\Vboxmanage" modifyvm %vmname%--draganddrop hosttoguest
"C:\Program Files\Oracle\VirtualBox\Vboxmanage" modifyvm %vmname%--description %description%

REM System
"C:\Program Files\Oracle\VirtualBox\Vboxmanage" modifyvm %vmname% --memory %memory%
"C:\Program Files\Oracle\VirtualBox\Vboxmanage" modifyvm %vmname% --cpus 2

REM Display
"C:\Program Files\Oracle\VirtualBox\Vboxmanage" modifyvm %vmname% --vram 30

REM Storage
"C:\Program Files\Oracle\VirtualBox\Vboxmanage" storagectl %vmname% --name IDE --add IDE --controller PIIX4 --bootable on
"C:\Program Files\Oracle\VirtualBox\Vboxmanage" storageattach %vmname% --storagectl IDE --port 0 --device 0 --type dvddrive --medium "D:\Install\Microsoft\Trial software\Windows 2008R2 x64 EVAL_EN_DVD.iso"

"C:\Program Files\Oracle\VirtualBox\Vboxmanage" storagectl %vmname% --name SATA --add SATA --controller IntelAHCI --portcount 1
"C:\Program Files\Oracle\VirtualBox\Vboxmanage" createhd --filename "D:\VirtualBox VMs\%vmname%\disk1.vdi" --size 125000 --format vdi --variant Standard

"C:\Program Files\Oracle\VirtualBox\Vboxmanage" storageattach %vmname% --storagectl SATA --port 0 --device 0 --type hdd --medium "D:\VirtualBox VMs\%vmname%\disk1.vdi" --nonrotational on


REM network
"C:\Program Files\Oracle\VirtualBox\Vboxmanage" modifyvm %vmname% --nic2 intnet

REM Shared folder
"C:\Program Files\Oracle\VirtualBox\Vboxmanage" sharedfolder add %vmname% --name install --hostpath d:\install --readonly --automount

REM starten maar!
"C:\Program Files\Oracle\VirtualBox\Vboxmanage" startvm %vmname%

REM General

SET "vmname=DC01"

SET "memory=2048"

SET "description=Windows 2008 64 bit domain controller"

"C:\Program Files\Oracle\VirtualBox\Vboxmanage" createvm --name %vmname% --register --ostype Windows2008_64

"C:\Program Files\Oracle\VirtualBox\Vboxmanage" modifyvm %vmname%--clipboard bidirectional

"C:\Program Files\Oracle\VirtualBox\Vboxmanage" modifyvm %vmname%--draganddrop hosttoguest

"C:\Program Files\Oracle\VirtualBox\Vboxmanage" modifyvm %vmname%--description %description%

REM System

"C:\Program Files\Oracle\VirtualBox\Vboxmanage" modifyvm %vmname% --memory %memory%

"C:\Program Files\Oracle\VirtualBox\Vboxmanage" modifyvm %vmname% --cpus 2

REM Display

"C:\Program Files\Oracle\VirtualBox\Vboxmanage" modifyvm %vmname% --vram 30

REM Storage

"C:\Program Files\Oracle\VirtualBox\Vboxmanage" storagectl %vmname% --name IDE --add IDE --controller PIIX4 --bootable on

"C:\Program Files\Oracle\VirtualBox\Vboxmanage" storageattach %vmname% --storagectl IDE --port 0 --device 0 --type dvddrive --medium "D:\Install\Microsoft\Trial software\Windows 2008R2 x64 EVAL_EN_DVD.iso"

"C:\Program Files\Oracle\VirtualBox\Vboxmanage" storagectl %vmname% --name SATA --add SATA --controller IntelAHCI --portcount 1

"C:\Program Files\Oracle\VirtualBox\Vboxmanage" createhd --filename "D:\VirtualBox VMs\%vmname%\disk1.vdi" --size 125000 --format vdi --variant Standard

"C:\Program Files\Oracle\VirtualBox\Vboxmanage" storageattach %vmname% --storagectl SATA --port 0 --device 0 --type hdd --medium "D:\VirtualBox VMs\%vmname%\disk1.vdi" --nonrotational on

REM network

"C:\Program Files\Oracle\VirtualBox\Vboxmanage" modifyvm %vmname% --nic2 intnet

REM Shared folder

"C:\Program Files\Oracle\VirtualBox\Vboxmanage" sharedfolder add %vmname% --name install --hostpath d:\install --readonly --automount

REM starten maar!

"C:\Program Files\Oracle\VirtualBox\Vboxmanage" startvm %vmname%

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

C:\Program Files\Oracle\VirtualBox

to:

C:\Program Files\Oracle VM VirtualBox

Posted in Uncategorized | 1 Comment

Two wrongs make a right: tricking the query optimizer

Posted on May 6, 2014 by Robert

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:

WHERE [tableA].[columnA] LIKE 'R%NL'
AND [tableB].[date] > @date

1 2	WHERE [tableA].[columnA] LIKE 'R%NL' AND [tableB].[date] > @date

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:

WHERE [tableA].[columnA] LIKE 'R%'
AND [tableA].[columnA] LIKE '%NL'
AND [tableB].[date] > @date

WHERE [tableA].[columnA] LIKE 'R%'

AND [tableA].[columnA] LIKE '%NL'

AND [tableB].[date] > @date

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:

WHERE [tableA].[columnA] LIKE 'R%'
AND RIGHT([tableA].[columnA],2) = 'NL'
AND [tableB].[date] > @date

WHERE [tableA].[columnA] LIKE 'R%'

AND RIGHT([tableA].[columnA],2) = 'NL'

AND [tableB].[date] > @date

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)

Posted on February 3, 2014 by Robert

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

Posted on January 27, 2014 by Robert

Chapter 3, Using Multiple Tables.

CREATE TABLE Customers (
CustomerID int IDENTITY
,[First name] varchar(100)
,[Last name] varchar(100)
,[Address] varchar(100)
,PRIMARY KEY (CustomerID))

CREATE TABLE Orders (
OrderID int PRIMARY KEY
,CustomerID int NULL
,OrderDate datetime
,CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID));

CREATE TABLE Orderlines (
OrderID int references orders(orderid)
,[Order line number] int
,Item varchar(100)
,quantity int
,price decimal(18,2)
,primary key (orderid, [Order line number]))

CREATE TABLE Employees (
EmployeeID int IDENTITY
,[First name] varchar(100) 
,[Last name] varchar(100)
,[Address] varchar(100)
,PRIMARY KEY (EmployeeID))

--DROP TABLE orderlines
--DROP TABLE Orders
--DROP TABLE customers
--DROP TABLE Employees

INSERT Customers values ('John','Smith','First Street 1, Washington');
INSERT Customers values ('Peter','Jones','Second Street 2, London');
INSERT Customers values ('Frank','James','Champs-Elysees , Paris');
INSERT Customers values ('Jim','Gray','Main Street 7, New York');

INSERT Customers VALUES 
('Peter', 'Jones', 'Second Street 2, London')
,('Frank', 'James', 'Champs-Elysees , Paris')
,('Jim', 'Gray', 'Main Street 7, New York');

INSERT Orders VALUES ('1','1', '2013-01-01');
INSERT Orders VALUES ('2','2', '2012-07-01');
INSERT Orders VALUES ('3','3', '2012-08-15');
INSERT Orders VALUES ('4','4', '2012-12-31');

INSERT orderlines values (1,1,'A Brief History of Time – S. Hawking',1,9.99);
INSERT orderlines values (2,1,'The Prince – Macchiavelli',10,59.90);
INSERT orderlines values (3,1,'Lord of the Rings – J.R.R. Tolkien',2,17.98);
INSERT orderlines values (4,1,'The Prince – Macchiavelli',1,5.99);

INSERT INTO Employees values ('Barry','St. John','Broadway 5, New York');
INSERT Employees values ('Jim','Gray','Main Street 7, New York');

--outer join
SELECT *
FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN Orderlines ol on ol.OrderID = o.OrderID;

--subqueries 
SELECT *
FROM Customers
WHERE [First name] in ( SELECT [First name]
FROM Employees);


--subquery
SELECT 'Address' = [Street name] + [House number] + ', ' + City
FROM Shipments
--table subquery example
CREATE TABLE Shipments (
 ShipmentID int identity
 ,Shipmentdate datetime
 ,[Street name] varchar(100)
 ,[House number] varchar(10)
 ,[City] varchar(100));
--outer query
SELECT *
FROM Customers c 
 INNER JOIN (...) s on c.Address = s.Address
 
--Total
SELECT *
FROM Customers c 
 INNER JOIN (SELECT 'Address' = [Street name] + [House number] + ', ' + City
 FROM Shipments
 ) s on c.Address = s.Address;

CREATE TABLE Customers (

CustomerID int IDENTITY

,[First name] varchar(100)

,[Last name] varchar(100)

,[Address] varchar(100)

,PRIMARY KEY (CustomerID))

CREATE TABLE Orders (

OrderID int PRIMARY KEY

,CustomerID int NULL

,OrderDate datetime

,CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID));

CREATE TABLE Orderlines (

OrderID int references orders(orderid)

,[Order line number] int

,Item varchar(100)

,quantity int

,price decimal(18,2)

,primary key (orderid, [Order line number]))

CREATE TABLE Employees (

EmployeeID int IDENTITY

,[First name] varchar(100)

,[Last name] varchar(100)

,[Address] varchar(100)

,PRIMARY KEY (EmployeeID))

--DROP TABLE orderlines

--DROP TABLE Orders

--DROP TABLE customers

--DROP TABLE Employees

INSERT Customers values ('John','Smith','First Street 1, Washington');

INSERT Customers values ('Peter','Jones','Second Street 2, London');

INSERT Customers values ('Frank','James','Champs-Elysees , Paris');

INSERT Customers values ('Jim','Gray','Main Street 7, New York');

INSERT Customers VALUES

('Peter', 'Jones', 'Second Street 2, London')

,('Frank', 'James', 'Champs-Elysees , Paris')

,('Jim', 'Gray', 'Main Street 7, New York');

INSERT Orders VALUES ('1','1', '2013-01-01');

INSERT Orders VALUES ('2','2', '2012-07-01');

INSERT Orders VALUES ('3','3', '2012-08-15');

INSERT Orders VALUES ('4','4', '2012-12-31');

INSERT orderlines values (1,1,'A Brief History of Time – S. Hawking',1,9.99);

INSERT orderlines values (2,1,'The Prince – Macchiavelli',10,59.90);

INSERT orderlines values (3,1,'Lord of the Rings – J.R.R. Tolkien',2,17.98);

INSERT orderlines values (4,1,'The Prince – Macchiavelli',1,5.99);

INSERT INTO Employees values ('Barry','St. John','Broadway 5, New York');

INSERT Employees values ('Jim','Gray','Main Street 7, New York');

--outer join

SELECT *

FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID

JOIN Orderlines ol on ol.OrderID = o.OrderID;

--subqueries

SELECT *

FROM Customers

WHERE [First name] in ( SELECT [First name]

FROM Employees);

--subquery

SELECT 'Address' = [Street name] + [House number] + ', ' + City

FROM Shipments

--table subquery example

CREATE TABLE Shipments (

ShipmentID int identity

,Shipmentdate datetime

,[Street name] varchar(100)

,[House number] varchar(10)

,[City] varchar(100));

--outer query

SELECT *

FROM Customers c

INNER JOIN (...) s on c.Address = s.Address

--Total

SELECT *

FROM Customers c

INNER JOIN (SELECT 'Address' = [Street name] + [House number] + ', ' + City

FROM Shipments

) s on c.Address = s.Address;

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

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

Posted on January 27, 2014 by Robert

Code samples for chapter 2, using a Single Table.

-- create table dbo.Persons, scripted by SSMS
USE [testdatabase]
GO

/****** Object: Table [dbo].[Persons] Script Date: 01/02/2013 20:37:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Persons](
[Firstname] [varchar](100) NOT NULL,
[Lastname] [varchar](100) NOT NULL,
[Address] [varchar](100) NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

-- create table dbo.Persons
DROP TABLE Persons
GO
CREATE TABLE [Persons] (
Firstname varchar(100) NOT NULL,
Lastname varchar(100) NOT NULL,
Address varchar(100) NULL DEFAULT '1600 Pennsylvania Avenue, Washington D.C.')

-- insert records into table dbo.Persons
INSERT Persons (Firstname, Lastname)VALUES ('John', 'Smith' );
INSERT Persons VALUES ('Peter', 'Jones', 'Second Street 2, London');
INSERT Persons VALUES ('Frank', 'James', 'Champs-Elysees, Paris');
INSERT Persons VALUES ('Elvis', 'Presley', NULL);

-- select records from table dbo.Persons
SELECT *
FROM Persons

SELECT Firstname
,Lastname
,Address
FROM Persons;

SELECT Firstname,Lastname,Address FROM Persons;

SELECT *
FROM Persons
ORDER BY Lastname;

SELECT *
FROM Persons
ORDER BY Lastname, Firstname;

SELECT *
FROM Persons
ORDER BY Lastname DESC;

SELECT *
FROM Persons
WHERE Lastname = 'Jones';

SELECT *
FROM Persons
WHERE Lastname = 'James'
or Lastname = 'Jones' ;

SELECT *
FROM Persons
WHERE Lastname IN ('James', 'Jones') ;

SELECT *
FROM Persons
WHERE Lastname LIKE 'Jam%';

SELECT *
FROM Persons
WHERE Lastname IS NULL;

SELECT *
FROM Persons
WHERE Lastname = 'James'
and Lastname = 'Jones' ;

SELECT TOP 2 *
FROM Persons;

SELECT DISTINCT [Lastname]
FROM Persons;

SELECT 'Achternaam' = Lastname
FROM Persons

SELECT Lastname AS 'Achternaam'
FROM Persons

SELECT Lastname + ', ' + Firstname AS 'Name'
FROM Persons

SET CONCAT_NULL_YIELDS_NULL OFF

SET CONCAT_NULL_YIELDS_NULL ON

SELECT 'any string'
FROM Persons

-- update records in table dbo.Persons
UPDATE Persons
SET Firstname = 'Frank jr.'
, Address = 'Unter den Linden, Berlin'
WHERE Firstname = 'Frank jr.';

UPDATE Persons
SET Firstname = 'Frank'
, Address = 'Unter den Linden, Berlin'
WHERE Firstname = 'Frank jr.';

-- delete 1 record (in case of duplicates)
DELETE
FROM Persons
WHERE Lastname = 'James';

DELETE * /*comment: this delete statement will produce a syntax error*/
FROM Persons
WHERE Lastname = 'James'

DELETE TOP (1)
FROM Persons
WHERE Firstname = 'John';

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

-- create table dbo.Persons, scripted by SSMS

USE [testdatabase]

/****** Object: Table [dbo].[Persons] Script Date: 01/02/2013 20:37:06 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

CREATE TABLE [dbo].[Persons](

[Firstname] [varchar](100) NOT NULL,

[Lastname] [varchar](100) NOT NULL,

[Address] [varchar](100) NOT NULL

) ON [PRIMARY]

SET ANSI_PADDING OFF

-- create table dbo.Persons

DROP TABLE Persons

CREATE TABLE [Persons] (

Firstname varchar(100) NOT NULL,

Lastname varchar(100) NOT NULL,

Address varchar(100) NULL DEFAULT '1600 Pennsylvania Avenue, Washington D.C.')

-- insert records into table dbo.Persons

INSERT Persons (Firstname, Lastname)VALUES ('John', 'Smith' );

INSERT Persons VALUES ('Peter', 'Jones', 'Second Street 2, London');

INSERT Persons VALUES ('Frank', 'James', 'Champs-Elysees, Paris');

INSERT Persons VALUES ('Elvis', 'Presley', NULL);

-- select records from table dbo.Persons

SELECT *

FROM Persons

SELECT Firstname

,Lastname

,Address

FROM Persons;

SELECT Firstname,Lastname,Address FROM Persons;

SELECT *

FROM Persons

ORDER BY Lastname;

SELECT *

FROM Persons

ORDER BY Lastname, Firstname;

SELECT *

FROM Persons

ORDER BY Lastname DESC;

SELECT *

FROM Persons

WHERE Lastname = 'Jones';

SELECT *

FROM Persons

WHERE Lastname = 'James'

or Lastname = 'Jones' ;

SELECT *

FROM Persons

WHERE Lastname IN ('James', 'Jones') ;

SELECT *

FROM Persons

WHERE Lastname LIKE 'Jam%';

SELECT *

FROM Persons

WHERE Lastname IS NULL;

SELECT *

FROM Persons

WHERE Lastname = 'James'

and Lastname = 'Jones' ;

SELECT TOP 2 *

FROM Persons;

SELECT DISTINCT [Lastname]

FROM Persons;

SELECT 'Achternaam' = Lastname

FROM Persons

SELECT Lastname AS 'Achternaam'

FROM Persons

SELECT Lastname + ', ' + Firstname AS 'Name'

FROM Persons

SET CONCAT_NULL_YIELDS_NULL OFF

SET CONCAT_NULL_YIELDS_NULL ON

SELECT 'any string'

FROM Persons

-- update records in table dbo.Persons

UPDATE Persons

SET Firstname = 'Frank jr.'

, Address = 'Unter den Linden, Berlin'

WHERE Firstname = 'Frank jr.';

UPDATE Persons

SET Firstname = 'Frank'

, Address = 'Unter den Linden, Berlin'

WHERE Firstname = 'Frank jr.';

-- delete 1 record (in case of duplicates)

DELETE

FROM Persons

WHERE Lastname = 'James';

DELETE * /*comment: this delete statement will produce a syntax error*/

FROM Persons

WHERE Lastname = 'James'

DELETE TOP (1)

FROM Persons

WHERE Firstname = 'John';

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

Finding the application timeout

Posted on December 16, 2013 by Robert

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:

After that, I ran the following query:

WAITFOR DELAY '00:00:10'

1	WAITFOR DELAY '00:00:10'

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

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

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

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

Posted on December 4, 2013 by Robert

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

Posted on November 24, 2013 by Robert

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:

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:

CREATE INDEX ncix_test
ON [Person].[Address](ModifiedDate)
WITH (ALLOW_PAGE_LOCKS = OFF);

CREATE INDEX ncix_test

ON [Person].[Address](ModifiedDate)

WITH (ALLOW_PAGE_LOCKS = OFF);

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:

SELECT 'table' = OBJECT_NAME(object_id)
,'index'= name
FROM sys.indexes
WHERE ALLOW_PAGE_LOCKS = 0
ORDER BY name;

SELECT 'table' = OBJECT_NAME(object_id)

,'index'= name

FROM sys.indexes

WHERE ALLOW_PAGE_LOCKS = 0

ORDER BY name;

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!

Posted on October 23, 2013 by Robert

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

Creating a virtual machine with Vboxmanage

Two wrongs make a right: tricking the query optimizer

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

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

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

Finding the application timeout

The curious case of the trailing space

Index reorg problem

Hello world!

Recent Posts

Archives

Categories