Welcome to the Spotlight Behind SQL Server, a new series from SQLServerCentral.com. As we've grown and spent more time covering SQL Server, we've slowly gained a number of contacts inside Microsoft, including those that develop the product. And we decided to try and interview the SQL Server people inside Microsoft. There are lots of people working on SQL Server 2005 and our goal to is to eventually get to them all.

We know that there are lots of technical things we could ask, and lots of easy marketing questions we could get from them, but you probably read most of those questions elsewhere. So we thought we'd make them think a bit more and get some interviews that showcased the people behind SQL Server. To that end, these interviews will be a little bit different and give you a look at the amazing team that builds SQL Server.

We caught up with Santeri Voutilainen over the last few months as he's gearing up for the next version of SQL Server.

SSC : What's your official title and responsibility at Microsoft?

Santeri : I'm a Software Design Engineer.

SSC : What part of SQL Server 2005 did you really enjoy working on?

Santeri : I really enjoyed working on the lock manager. There are several improvements that we made that were rewarding to work on. Many of these were small but hopefully have made a difference.

I also enjoyed working on the Dynamic Management Views (DMVs) that were introduced in SQL Server 2005. I have personally found them very useful and would encourage every DBA to explore all the information that is available from the various DMVs. Shameless plug: The DMVs are prominently featured in the book "SQL Server 2005 Practical Troubleshooting: The Database Engine", which has chapters written by a group of SQL Server development and support team members, myself included, and edited by Ken Henderson.

SSC : How has the lock manager evolved to make SQL Server applications run better? Is there really row level locking?

Santeri : Over time, the lock manager has evolved to perform better and has also had some new features added. A lot of these improvements have been behind the scenes changes not directly visible outside of the server. In SQL Server 2005 one such change was making the lock manager aware of the Non-Uniform Memory Access architecture, which improves its performance on machines that use this architecture. We've also worked at relaxing previous restrictions with the goal of allowing more scenarios to perform better. One such change in SQL Server 2005 was to relax the conditions under which a lock request must wait behind a previously granted lock. In certain applications this can decrease the occurrence of blocking due to locks.

Yes, there is row level locking, it is on index rows. The key point to note (no pun intended) is that row locks are acquired on the index that is used to retrieve the columns. Since this does not lock all indexes, it may be possible to read certain columns for a row that has been locked in exclusive (X) mode in another index. An example might illustrate this better:

Consider an "employee" table with columns employee_id, name, salary, and department_id. A clustered index exists on the employee_id column. A non-clustered index contains the name and department_id columns. The "update employee set salary = salary * 1.02 where employee_id = 44" only needs to change the contents of the clustered index so an exclusive (X) lock is acquired on the row in the clustered index. However, the query "select department_id from employee where name = John Doe'" can be satisfied using the non-clustered index without touching the clustered index at all, since the non-clustered index is covering it contains both the name and department_id columns. Had the update statement modified the department id (or the employee's name) both indexes would have been locked thus preventing any access.

SSC : How did you enjoy writing a book as opposed to writing code?

Santeri : Frankly, I found it quite hard to write the chapter for the book writing has never really come naturally to me. Had it not been for the repeated requests from Ken Henderson, the chapter would still not be written! The process made me greatly appreciate the efforts of everyone who has written a book, especially the well written ones. Maybe I'll try it again someday; after all, it was a pleasure to see the book finally published, and I would like to share my knowledge if there is a chance it might be useful to others.

SSC : How long have you been working on SQL Server?

Santeri : I first started in SQL Server during a summer internship in 1998. I joined the SQL team full-time the following summer after graduating from college, so a total of over seven years.

SSC : Which area of SQL Server has been the most interesting to work on over your career?

Santeri : That would have to be the allocation subsystem -- it was at times both the most difficult and interesting. The allocation subsystem handles the allotment of 8Kb pages from the data files. In that way it is very similar to memory allocation systems.

SSC : Are there any DMVs that you think DBAs should be paying attention to?

Santeri : There are a bunch of them! Blocking, CPU, IO, and memory are four areas that often come up in questions and discussions so I'll mention several DMVs that can help. Sys.dm_exec_requests and sys.dm_os_waiting_tasks are the best starting points for blocking issues. Sys.dm_exec_query_stats and sys.dm_os_schedulers can provide valuable information on CPU usage by queries and whether SQL is CPU bound, respectively. Sys.dm_exec_query_stats and sys.dm_db_index_operational_stats can be used to pinpoint excessive IO usage. There are several sys.dm_os_memory_* DMVs that provide a lot information about how memory is being used. Many, if not all, of these are discussed in various chapters in the book. In addition, Slava Oks has several posts regarding useful DMVs on his blog (http://blogs.msdn.com/slavao) and a series of very detailed posts on SQL Server memory usage. Definitely worth reading!

SSC : Give us a little background on yourself, how did you get into computers?

Santeri : I was born in Finland but grew up mostly in Nepal where my parents still work. During one of our years back in Finland I recall seeing an ad in some magazine for a computer, probably the Sinclair ZX Spectrum. I tried to convince my mother that if we bought it, I could store all her recipes in it for easy lookup she didn't go for it We did, however, buy a Commodore 64 at the Amsterdam airport duty free on our way back to Nepal. Almost as soon as we arrived I started keying in programs that were printed in a computer magazine I'd brought with me. From that point on there was little doubt I'd end up working with computers.

SSC : Did you see yourself as a programmer/developer when you were growing up?

Santeri : Yes, I quickly progressed from keying-in printed programs to writing my own. At one point I had grand illusions about using my Commodore 64 to control disco lights. I quickly found out that I was much better at the software side than the electronics of hooking up the computer to control actual lights, especially since I wanted to build the connectors myself. The grand vision was downgraded to flashing colored circles on the screen to simulate the lights with the room lights turned down and with appropriate brightness and contrast settings on the TV, the effect was surprisingly decent.

SSC : Was there any program you wrote early on that sticks in your mind?

Santeri : It would have to be the "Disco Lights" program partly due to its absurdity.

SSC : How do you like living in Redmond?

Santeri : I actually live in Seattle on the other side of Lake Washington from Redmond. It makes for a longer commute but we've enjoyed being in the city. During my internship I was awed by the ability to go from seashore to mountains to the plains of eastern Washington in just a couple hours of driving. This variety in climate and landscape has been source of great enjoyment for me.

SSC : Who's the most fun to work with at Microsoft?

Santeri : I have had the pleasure of working with a number of interesting people here, and I wouldn't do justice to all of them by singling out one person.

SSC : We've all heard stories of some characters at Microsoft. Any interesting ones that stunned you or surprised you when you first went to work in Redmond?

Santeri : When I started, I was quite intrigued and surprised by a colleague on my team. For someone who was used to listening to music through computer speakers or the headphones that came with a CD-player, anyone spending hundreds of dollars on just a couple feet of wiring, not to mention separate electricity feeds for his audio equipment, definitely counted as a "character". Craig was as meticulous with his coding and performance measurements as he was with matching vacuum tube characteristics. Over time I grew to admire his attention to details and count him in the list of most fun persons I've work with at Microsoft.

SSC : Ever been to Bill Gates' house?

Santeri : Yes, during my summer internship. He hosted parties for all the interns at this house on Lake Washington. Bill Gates was immediately surrounded by a throng of interns when he came out into the yard. It turned into a question-and-answer session during which he even autographed someone's payslip.

SSC : What's your current favorite tech gadget?

Santeri : My Windows Mobile smartphone. I'm a couple of hardware generations behind, but the combination of email, calendar, contacts, and several other mobile apps has completely sold me. Given the traffic in Seattle, I would now find it hard to manage without the ability to view real time traffic conditions and bus status.

SSC : What does Santeri like to do when he's not working on SQL Server?

Santeri : Right now most of my free time is spent with my 16 month old son. He's quite a playful and energetic boy, and I've greatly enjoyed watching him grow and learn about his surroundings. I also enjoy exploring the Northwest that awed me while an intern.

SSC : You've traveled quite a bit, what was the most beautiful place to see?

Santeri : I really enjoy the combination of cliffs or mountains and the ocean so I'd have to say that northern Norway with its fjords and the Arctic Ocean has been one of the most captivating places I've visited. Closer to home, there are many places along the Oregon and Washington coasts that I find truly beautiful as well.