SQLblog.com - The SQL Server blog spot on the web

Check out another Erland Sommarskog's utility: ABAPERLS

Alexander Kuznetsov — Mon, 05 Jan 2009 19:06:00 GMT

2009 SQL Resolutions

drsql — Mon, 05 Jan 2009 02:55:02 GMT

Every year I put out resolutions, but this year is a little different. I will post my SQL only blog resolutions here, and my personal ones here. Every year I find my ability to put out stuff is limited by my desire for sleep and fun, and this year is no different. So I am going to be realistic this time…I think.

1. Only collaborative works this year. I am collaborating with Tim Ford (http://ford-it.com/) on the oft mentioned DMV book for red-gate. I feel it will get done with or without me this time, but I am committed to making it happen. The MVP book work is mostly done, but there is a good bit more work to do before it goes to print for sure.

2. At least one SQL blog on this site every two weeks or more often. I have my pillar posts that are in progress, (one is ready for posting in a day or two), and I am going to post some of the DMV stuff along too, most likely.

3. Back on the forums more steady now that the holidays have passed. I try to stick to 3-5 posts a day, even when I am writing, but it can be laborious. Luckily the talent that is working the forums has greatly improved over the past three years.

4. Bring my back catalog of posts from drsql.spaces.live.com over to sqlblog in cleaned up/updated form, as well as posting them to my website drsql.org for future reference as well.

5. Keeping drsql.org more up to date with stuff I do.

6. Speak at least 3 times this year (user group (nashville.sqlpass.org), Devlink (devlink.net), and PASS, hopefully).

7. Work with PASS on social media to help make them a solid place to go to meet other SQL nerds, even those on other communities.

8. Work on my responsibilities with the Nashville SQL User’s group to help us turn a corner to a thriving group. Right now we have a lot of potential, but need to do a bit more.

9. Read more. One thing about producing content is that it prohibits you from ingesting content from others.

See, realistic.

5 Reasons why I think MDX is the Devil

James Luetkehoelter — Sun, 04 Jan 2009 17:24:00 GMT

First, an up front apology to Mosha - this is half in jest, half real. MDX is a powerful and useful language, but I find that its very existence has made my life very difficult when it comes to developing a solid Analysis Services solution. That's not to say I don't use it, or endorse it, but I worry greatly about its overall usage.

1) It tempts you by looking like SQL, but once your brought into it you realize how complex it is (if you don't have Mosha's MDX Studio, get it).

2) MDX allows you to do TOO much when it comes to a multi-dimensional structure, without first understanding multi-dimensional concepts. I've seen code that creates filters or subset cubes that overlap or end up cutting out measures implicitly. You have no warning about possibly undermining previous dimensional modeling - that would be a nice addition to see.

3) Along those lines, it enables sloppy dimensional modeling in the first place. "Throw it together, will add in things later". With a multi-dimensional database, it is very important to model it correctly, just like you would a relational structure. Unfortunately there are no Codd/Date rules of normalization, and often very little work done up front understanding the actually relationships and what the data is meant to represent. For example, instead of using a many-to-many cube dimension (what I call the dimesion to measure tie) to represent something like a bill of materials, I often see this done afterwards somehow via MDX calculations and such. Instead of going back to the dimensional modeling, the answer is usually "we can do this in MDX later".

4) Yet another language with its own syntactical oddities - damn you syntax, you will be the death of me!

5) I can't count the number of calculations I've seen in a cube that could have been done before the cube is loaded in the first place. An MDX calculation that does something like PRICE * QUANTITY = TOTAL SALES is just a waste of processing on the cube side. Remember, MDX is post aggregation - so the large the cube, the more dimensions it has and the level of drill down it allows all play a roll in the speed of things.

Those are 5 to start - anyone have anymore?

Reproducing deadlocks involving only one table

Alexander Kuznetsov — Thu, 01 Jan 2009 23:12:00 GMT

Although the best known deadlock scenario involves two connections modifying two tables in different order, there are also other deadlock scenarios involving only one table. Besides, in some scenarios each connection needs to issue only one statement, and it is enough to get a deadlock. Also in some scenarios only one connection needs to modify or acquire exclusive locks – the other one may only read data and only acquire shared locks and still embrace in a deadlock. I will provide repro scripts...(read more)

Deep dive to MDX presentation - slides

mosha — Wed, 31 Dec 2008 19:54:45 GMT

During SQL PASS 2008 summit, I gave full day preconference seminar “Deep Dive to MDX”. The evaluation forms now have arrived, and the scores for the content, relevance and depth portion of the seminar are very good. The scores are not as high, however, for the time allocated (people argued that it should’ve been 2 days instead of one), and for the materials (since nobody received any materials). Both points are valid, one day turned out to be not enough, and perhaps two days wouldn’t have been enough either, since I got into less than a half of material that I had prepared. Also, due to some issues, I couldn’t hand down the presentation material – slides and MDX queries/calculations. These issues are cleared now, and I will start uploading the slides from the presentation. I don’t suppose they will be very useful for people who didn’t attend the session, since slides really only contain major talking points, and the rest was delivered verbally, but they probably will be very useful for people who did attend.

The time allowed to cover the following subjects:

Overall Architecture
Subcubes
Sonar
Storage Engine query plan
Storage Engine cache
Formula Engine query plan

Block mode

Formula Engine cache
Sets

More detailed Table of Content can be found here.

I will do series of posts uploading one subject at a time. The first subject is “Overall Architecture”, and it gives high-level picture of the components discussed further in the presentation. The slides are here.

Slightly unrelated announcement: MDX Studio 0.4.11 was also released today, with multiple UI enhancements, docked windows (Visual Studio style) etc. Versions for SSAS 2005 and SSAS 2008 are available, and can be downloaded from here.

A year in review, The 31 best blog posts on SQLBlog for 2008

Denis Gobo — Wed, 31 Dec 2008 15:36:00 GMT

Wow, it has been already a year since I wrote A year in review, The 21 + 1 best blog posts on SQLBlog last year

Here we are with another fascinating collection of links, some of them are chosen for their content and some of them for the great comments.

Which was your favorite post of the past year?

A Gift of Script for 2009: Who is Active, Redux

Adam Machanic — Wed, 31 Dec 2008 14:49:00 GMT

Last year on December 31 I posted part of a larger monitoring script that I had been working on for a few months. I received lots of great feedback on the little script in both the comments and from people I was working with, and over the course of the last year I estimate that I have invested at least a couple of hundred hours in the script. The result of all of this time is attached, and I hope that this script is as useful for others as it has been for me; I've been using it pretty much nonstop while modifying it and it has become an indispensable part of my toolkit.

Alas, while I had planned to write a comprehensive blog post on the script for publication today, I simply don't have time to do it correctly at the moment--but I wanted to get the script out on the 31st in order to create a kind of tradition. So instead of telling you everything it can do, I will ask that you instead take a look inside the script, where I've done a fairly good job documenting both the parameters and the outputs. I'll do a followup post soon (I hope) and do a better job of telling you about the various options. In the meantime, I'll leave you with a list of the script's major features:

Wait stats collection, if you enable the GET_WAITS switch. This mode aggregates the various resources that the tasks associated with the session are waiting on, and lets you know how long the tasks have been waiting.
Collection of lock information, if you enable the GET_LOCKS switch. A lot of effort went into this particular feature--all of the locks for each session are aggregated in an XML format, and work is done to map the various identifiers associated with each lock to actual object names so that you don't have to do the footwork yourself.
Information about sleeping SPIDs holding open transactions, if you enable the GET_TRANSACTION_INFO switch. If you're being blocked by a SPID that is not active, you should be able to find out about it. This option lets you do it. As a bonus, it also pulls information about transaction log writes in any database in which the transaction has done a write.
Collection of the "outer" command/batch, if you enable the GET_OUTER_COMMAND switch. One of the comments on the script I posted last year asked why, if you issue SQL like "ALTER TABLE dbo.xx ALTER COLUMN yyy FLOAT NULL", you'll end up with statement text that looks like "UPDATE [zzz].[dbo].[xxx] SET [yyy] = [yyy]". This is an internal representation of what's actually happening, but in many cases is not esepecially useful for end-users. So if you want to see what was actually run, now you can.
Every expensive or potentially expensive option is now parameterized. I spent a lot of time tweaking performance and with no options running the thing is pretty fast. Enable some options--especially lock collection--and it will slow down considerably, so use these advanced features with care.

Enjoy, and please keep sending feedback! Let me know what else you would like to see, if you find bugs, etc. I'll post updated versions as major changes are made.

To finish I would like to thank a few of the people who had an impact on the evolution of this script over the past 12 months: Michael Condon, Erland Sommarskog, Aaron Bertrand, Louis Davidson, and Jonathan Kehayias.

Have a great 2009, everyone!

Did You Know? You, too, can be a Geek!

Kalen Delaney — Wed, 31 Dec 2008 03:33:00 GMT

Danny Gould has a new (beta) version of his Internals Viewer, that you can download from Codeplex at http://www.codeplex.com/InternalsViewer

I've just started playing around with it, and the coolest thing is that it integrates right with your Management Studio!

The codeplex site include a Troubleshooting Guide and a User Guide.

It does look like it can display SQL Server 2008 data pages that have been compressed. For pages with ROW compression you'll be able to see the data, but for PAGE compression, no translation is done.

You can also request to have information from the transaction log displayed, but it's not the same as the 3rd party log reader tools, in that it doesn't give you the complete set of values that were inserted or deleted.

I'm still exploring its nuances, and now you can too!

Have fun!

~Kalen

Performance impact: file fragmentation and SAN - Part VI

Linchi Shea — Tue, 30 Dec 2008 21:19:00 GMT

A script to fragment a test file

Well, this post won't cover any actual test results and is not specific to SAN. But in the spirit of full disclosure, I've attached the script I used to control the fragmentation level of the test files mentioned in this series of posts. The script is written in Perl, but can be easily done in any language.

The script does not itself fragment any file. Rather, it fragments the entire disk drive into non-contiguous blocks, or fragments, of a given size. A file (such as a database file) created afterwards is then necessarily fragmented into these fragments.

It takes three parameters at the beginning of the script: (1) $DRIVE to specify which drive the test file will be placed, (2) $FRAGMENT_SIZE to specify the size of the fragments in bytes, and (3) $FREE_SIZE to specify how much free space you want to have after the script completes. So if you want to examine the behavior of a 10GB test file that is fragmented into 128KB fragments, you may want to specify $FREE_SIZE to be slightly bigger than 10GB.

The script begins by creating an empty directory tree with enough directories so that each directory will hold 200 files of the specified size. It then proceeds to create 200 files in each of these directories until the drive is completely filled up. Finally, it randomly deletes some of these files until the free space on the drive reaches the value specified by $FREE_SIZE.

Hope you find this script useful in case you'd like to check out the impact of file fragmentation yourself.

Restoring master database

Michael Zilberstein — Tue, 30 Dec 2008 17:03:44 GMT

Disclaimer: I'm not an infrastructure DBA.Probably everything below is just bullshit. Recently our talented IT guys destroyed disk on a virtual machine. They chose the disk carefully - it was the one with master database. How do you restore master db from backup? Start SQL Server service from command line in single user mode and execute RESTORE DATABASE command. OK, start in single user mode fails because it doesn't find master database files. At this point I would like to take backup file and instead...(read more)

A response to Linchi Shea's excellent posts on fragmentation

James Luetkehoelter — Mon, 29 Dec 2008 23:08:00 GMT

In case this is your first visit to SQLBlog.com, Linchi Shea does very methodical analysis of the performance and behaviour of disk arrays. His most recent listings specifically explore file fragmentation and its affects on performance on storage. The posts sort of give one the impression that there is no significant impact on performance when the disk storage is a SAN. I'd be inclined to agree, and his data certainly backs that up. But in many in the posts he does compare direct attached storage, where we do see degradation.

I'm writing this little blurb because my hunch is, at least from what I've encountered, the majority of SQL installations use DAS or "quasi-SANs" (Linchi, I'm sure you've run into some of these devices that claim to be SANs but are sorely lacking). In those cases, especially ones where databases are unmonitored and auto-grow occurs on a regular basis (or transaction log auto-grow/manual shrink accordianism), Linchi actually demonstrates that there will be performance degradation. This is one of the most common performance issues I run into out in the "jungle". Fragmentation.

My message - those of you on direct storage or if you aren't certain of fragmentation affects, error on the safe side and defragment. Or at the very least investigate how physically fragmented your arrays are. Please.

Performance impact: file fragmentation and SAN – Part V

Linchi Shea — Mon, 29 Dec 2008 21:36:00 GMT

SQL Server workloads

So far, the discussions in all the previous posts (1, 2, 3, and 4) on the performance impact of file fragmentation on a drive presented from a high-end enterprise-class disk array are related to disk I/O workloads. Ultimately, you want to know how file fragmentation may impact your SQL Server workloads.

In this post, I share with you some results from running three SQL Server workloads: (1) database backups, (2) checkpoints, and (3) table scans. These SQL Server workloads were run in the same three test scenarios as used in all the previous four posts:

· Non-fragmented. The database files were created on an empty drive,

· 2MB fragments. The database files were created on a drive whose free space had been fragmented into non-contiguous chunks with each chunk being contiguous and 2MB in size,

· 128KB fragments. The database files were created on a drive whose free space had been fragmented into non-contiguous chunks with each chunk being contiguous and 128KB in size,

The same database was used in all the tests. The database was about 9.5GB in size, and the table on which table scan was performed had 10 million rows and was about 4GB in size. DBCC DROPCLEANBUFFERS was run prior to each table scan.

The following chart is a summary of the SQL Server workload test results:

Clearly, file fragmentation did not have any significant performance impact on these SQL Server workloads. Because the performance of these workloads is dependent on the disk I/O throughput, we could have predicted this from the results of our disk I/O tests as reported in the previous four posts. However, it’s still comforting to see the prediction validated with real data points.

This concludes this series of posts on the performance impact of file fragmentation on a drive that is presented from a high-end enterprise class disk array. Overall, the impact was significantly less than what we have seen on a traditional directly attached disk drive. For I/O throughput, there was little to no impact. For I/O latency (or response time), file fragmentation can cause some I/O request to take much longer to complete.

In a future post(s), I’ll explore whether the same holds true with a drive presented from a lower end disk array.

Season's Greetings!

Kalen Delaney — Mon, 29 Dec 2008 18:50:00 GMT

I was catching up on my blog reading and came across a very nice post on Ward Pond's blog; he gives us some TSQL code that produces a holiday message. It's a few days late, but it's the thought that counts, right?

I did have a few problems with the code on Ward's site... when I copied and pasted to SSMS, there were no line breaks at all, so everything appeared as one huge long line. That made it hard to interpret, and hard to troubleshoot when a syntax error was reported. It turned out that not only did no line breaks appear, but whatever was in the code to break the lines before the word 'cross' got swallowed up, so the last part of the code was:

from firstTablecross join SecondTablecross join ThirdTablecross join ForthTablewhere therow%2!=0

So I fixed up the code to add line breaks, to separate the 'cross' from the preceding table names, and to fix a spelling error.

Enjoy... and thanks, Ward!

with FirstTable as (select top 14 row_number()
          over (order by name) therow
     from master.sys.objects)
   , SecondTable as (select replicate(char(32),15) theLine)
   , ThirdTable as (Select replicate(char(124),3) theOtherLine)
   , FourthTable as (
      select (select left(db_name(4),1) ) + (
      select substring(db_name(2),2,1) ) + (
      select replicate(substring(db_name(1),6,1),2) ) + (
      select replace(schema_name(4),'s','') ) + (
      select char(max_length * 2)
         from master.sys.types
         where system_type_id = 36) + (
      select top 1 substring(wait_type,10,2)
         from master.sys.dm_os_wait_stats
         where wait_type like 'PageIO%' ) + (
      select substring(@@version,4,1) ) + (
      select substring(object_name(55),4,2) ) + (
      select convert(char(1),(reverse(convert(char(7),name))))
         from sys.configurations
         where configuration_id = 124 ) + (
      select left(db_name(1),3) ) theEnd
)
select case therow
      when 11 then
           stuff( theLine,(datalength(theLine)/2)
              - 1,3,TheOtherLine)
      when 13 then upper(theEnd )
      else stuff( theLine,(datalength(theLine)/2) -
              (theRow/2),therow,
                  replicate(char(42),therow)) end ' '
from firstTable
      cross join SecondTable
      cross join ThirdTable
      cross join FourthTable
where therow%2!=0;

Happy New Year!

~Kalen

LINQ to SQL: Does it have much of a future?

Greg Low — Tue, 23 Dec 2008 22:10:00 GMT

Kevin Kline recently posted, wondering about the directions for LINQ.

When people refer to LINQ, they're often referring to "LINQ to SQL" instead.

LINQ as a language enhancement is pretty cool. It provides a way to easy navigate enumerable objects. The only thing that puzzles me is why they picked SQL-like syntax for this instead of something more appropriate for objects.

On the other hand, LINQ to SQL I can't get excited about. The main issues are that it's tied to SQL Server and only gives you a one-to-one mapping of tables to objects. That leaves you with either a lousy database design or a lousy object model.

The easiest example is anything that involves many to many relationships. If I have a flights table and a passengers table, I'd typically have a linking table (like flightmanifests) that records which passengers are on which flights. That's good database design but I wouldn't want an object model based directly on those three tables. What I'd want at the object level is a passenger object with a flights collection and a flight object with a passengers collection.

The Entity Framework lets you cope with both the issues mentioned and seems to be more likely to be the direction that Microsoft will keep heading. It also adds some interesting constructs in the ESQL language. However, its generic nature means you need to work with a very constrained set of data types. You lose the rich data types available with SQL Server in the trade off with the ability to write more generic code.

From the database end, LINQ to SQL can generate quite poor TSQL and the way people often use it ends up causing plan cache pollution issues, much the same as anyone using AddWithValue() to add parameters to a SqlParameters collection in ADO.NET would.

Every time I show people the TSQL code generated from some simple LINQ to SQL queries, I see two reactions. People with a developer background usually say "I'm glad I didn't have to write that". People with a database background say "No-one should write that - it's horrible code". And LINQ to Entities generates even more generic code (as you would imagine it needs to when it doesn't even know what database engine it's targeting).

This is usually all justified by increased developer productivity. "As long as it works well enough and it's done quickly, who cares if what's going on under the covers isn't great?" And that's 100% true for small or simple applications. However, the places I see Microsoft pushing this technology is to ISVs and large enterprise clients. These are likely to be the people it's least appropriate for.

The ADO.NET group seems to have adopted LINQ to SQL now but I know they really don't love it and the Entity Framework is what they're interested in. So I can't see LINQ to SQL having much of a future at all. LINQ to Entities is much more likely to stay around.

The other big issue I see in this area is maintenance. Microsoft have made big strides with the Database Edition of Visual Studio Team System but every time I ask questions about what the plans are for allowing it to "see" all the mapping files from these coding technologies, I get very blank stares back. Many DBAs can't make any changes to their databases today because they have a sea of Access applications all over the organisation and they don't know what will break when they change something. Are LINQ to SQL and Entity Framework mapping files going to be the next generation of Access-database-style management issues?

More updates for SQL Server 2005 Service Pack 3

AaronBertrand — Tue, 23 Dec 2008 20:13:00 GMT

As promised, upon return from a trip to the frozen tundra of Northern Ontario, I am posting the latest updates from last week's release of SQL Server 2005 Service Pack 3. For those who have been waiting to test the service pack until the post-SP3 cumulative update is available, you can get it in KB #959195 and read more about it over at the SQL Server Release Services blog. I apologize for being a few days behind, as the release was pushed out on Saturday, but I had very little access to anything during my trip.

For those of you keeping track, SP3 would bump your SQL Server instance to build # 9.00.4035, while the CU#1 update will bump it all the way up to 9.00.4207.

Also, if you are not ready to migrate to Service Pack 3, there is a security bulletin you should be aware of, surrounding an exploit using the extended stored procedure sp_replwritetovarbin. You can read about the issue in Microsoft Security Advisory (961040) and get some more background information at the MSRC and SVRD blogs. Note that SQL Server 2005 instances patched with SP3 or later are not affected by this vulnerability, nor is any RTM+ build of SQL Server 2008.

LINQ: Enabling or Entangling?

KKline — Tue, 23 Dec 2008 19:49:00 GMT

There’s been a lot of positive press for LINQ, such as the article about LINQ by Mike Otey at http://www.sqlmag.com/Article/ArticleID/48759/sql_server_48759.html. You can also find lots of glowing reviews and info about LINQ by Troy Magennis at http://blog.aspiring-technology.com/.

I've been trying to figure out exactly how I feel about LINQ, along with several other developer-oriented technologies that Microsoft has launched over the years, such as CLR. Ambivalence is the emotion that bubbles to the top most frequently. It's pretty obvious to me that Transact-SQL is the red-headed step child within Microsoft's overall ranking of languages.

One of the big problems I have with Microsoft’s approach is that it’s too tactical. Every 2-3 years, Microsoft launches TNBT (“the next big thing”). TNBT will make our code better, our developers faster, our applications more efficient, walk your dog, wash your cat, tie your shoelaces, end world hunger, and otherwise make everything better. The only problem is that TNBT is usually put together by one subteam of a major group, such as the languages group inside of the SQL Server development team. These small teams, while brilliant, often don’t get the top-down support to institute a major sweeping change to how things work. Consequently, we get a feature set that, while useful, doesn’t give us everything we need to sweep out the old and introduce the new.

This sort of 50% solution manifests itself in a lot of different ways, usually by making some aspects of the development process better and other aspects worse. My friend and fellow SQL Server MVP Andrew Kelly has an interesting blog post and subsequent comments at http://sqlblog.com/blogs/andrew_kelly/archive/2007/09/06/linq-to-the-rescue.aspx which strongly illustrates this idea. Basically, (most) developers still don’t understand the basics of building an efficient database solution. The thing that most improves database applications is proper planning and design. But tools like LINQ and the entity framework most obviously help developers speed up development process, in effect encouraging even less planning and design than ever before. A recipe for disaster? Almost certainly.

I hate to think that the language that I’m best at is the most likely to lose overall support. On the other hand, I’d love to get in on TNBT (“the next big thing”) while it’s still in its genesis. However, figuring out what, exactly, they are planning to replace Transact-SQL with is yet to be seen since each new offering is, IMO, sadly lacking in value.

As an administrator, I’m putting a lot of eggs into the PowerShell basket – the first major scripting/programming language in many years that I’m taking the time to get really good at. However, I still don’t think we’ve hit TNBT in development languages and we probably won’t until Microsoft takes the time to convene a high-powered team composed of members from both the SQL Server and Visual Studio organizations.

Did You Know? My Book Had Too Many Pages!

Kalen Delaney — Mon, 22 Dec 2008 21:58:00 GMT

Some of you are aware that I am writing an (almost) weekly commentary for a SQL Server Magazine e-newsletter called SQL Server Magazine UPDATE. I was told that I could write about anything related to SQL Server, but that turned out not to be true. There has to be at least some technical or business value to my commentary. A few weeks ago, I wrote a commentary about my publishers' page count limitations on my books, and it was rejected. :-(

So I guess I I'll just have to post it here, since so far Adam and Peter haven't rejected anything I've written. :-)

In my commentary of August 14, I talked about “Too much information”, and listed many different sources where you could get details about the features and behavior of SQL Server 2008. When I referred to “too much”, I was talking about too many different places to have to look. I am currently faced with a problem of having too much information in one book: my upcoming SQL Server 2008 Internals book. However, it’s not readers or information seekers saying that it is too much, it is the publisher.
When I sign a contract to write a book, the publisher always asks for a page estimate. However, before I start researching a new topic, I have no idea how many pages it will take to explain the topic well. For example, before I knew anything at all about SQL Server 2008 compression, I had to figure out how many pages I was going to write about it. I always tell my editors that I just can’t say, and they reply that they just need an estimate, and not to worry. So I have always given a lower limit, trying to figure out the fewest number of pages I might need. I always assumed that the publisher wanted something in the contract that would assure that the book would have some substantial content, and not just be something fluffy written in a hurry to meet a deadline. So I always thought the estimate was a minimum. But I was wrong.
Suppose you have a contract to work for someone for 40 hours a week. Is that ‘at most’ 40 hours, or ‘at least’? Is your boss going to tell you to go home once you’ve been there for 40 hours? If I have a contract to work 20 hours on a project for a fixed rate and end up working 25, is my client going to complain?
I was quite surprised when during my last month of writing my SQL Server 2008 Internals book, my project editor wrote to me and said we already had 70% of the page count for only about 50% of the chapters, and she hoped the rest of the chapters would be very short. In fact, the longest chapter had not been submitted yet. So I had a week of not writing while we tried to figure out what we could cut, and we tried to get the publisher to agree to increase the limit. It was very hard to get an increase, because the lower number was in my contract.
This got me thinking of a line from the 1984 movie Amadeus, when Emperor Joseph tells Mozart: “Your work is ingenious. It's quality work. And there are simply too many notes, that's all. Just cut a few and it will be perfect.” And Mozart replies: “Which few did you have in mind?” http://www.imdb.com/title/tt0086879/quotes
The outline of the book had been carefully planned and all the co-authors and I had either finished writing or were almost finished. What researched, tested and polished information about SQL Server internals should we remove?
I finally came to a somewhat satisfactory arrangement with the editors for my Internals book (although it’s still not clear if the introduction, foreword and index will count against the total or not), and I thought that I wouldn’t have to worry about anything like that for a while, because not every publisher could be so short-sighted as to sacrifice quality by trying to reduce immediate costs. But again I was wrong. I am also working as a co-editor on a book of SQL Server tips and best practices, written by a group of over 40 SQL Server MVPs. We are not getting any payment of any kind, but will donate all our royalties to a children’s charity. We’re also doing all the technical editing among ourselves, so the publisher has very little work to do. But I just found out that we also will have a severe page limit on this book. So again we have to decide what to cut and what to keep.
I’m sure the publishers have their reasons for this limit (although it never came up on any of my earlier books), but it seems like reasons can always be re-evaluated. I’m just glad there are more places that my readers can look for information, so anything that doesn’t fit in my book, my readers can find out more about in my blog, in a class, in a conference session, or in one of the “too many different places” that I have already told you about.

Happy Reading!

~Kalen

Performance impact: file fragmentation and SAN – Part IV

Linchi Shea — Mon, 22 Dec 2008 16:34:00 GMT

Lies, damned lies, and statistics!

If you have read my three previous posts (1, 2, 3), you may walk away with an impression that on a drive presented from a high-end enterprise class disk array, Windows file fragmentation does not have a significant performance impact. And I’ve given you empirical data—oh yeah, statistics—to support that impression.

But that is not the whole story! No, I didn’t lie to you. The numbers I presented were solid. It’s just that the story is not yet finished.

In these previous posts on the performance impact of file fragmentation, I presented the I/O throughput data as the evidence. The arguments were valid, especially we did see file fragmentation causing the I/O throughput to degrade in a directly attached storage. But I/O throughput is but one I/O performance metric, and it is not enough to look at the I/O throughput alone.

Let me start with an analogy. So suppose we have an eight-lane super highway going from New York City to Los Angles. As we pumping (okay, driving) more cars from NYC to LA, we take measure at a checkpoint in LA to find out how many cars are crossing that checkpoint every hour, i.e. we are measuring the throughput of the super highway. Now, instead of building the eight-lane super highway straight from NYC to LA, we have it take a detour via Boston. At that same checkpoint in LA, we again measure the throughput. Everything else being equal, we should get the same throughput.

However, for a given car, the trip from NYC to LA would take a lot longer on this detoured highway.

An I/O path is similar to a super highway. While its throughput is an important measure, how long it takes for an I/O request to complete—I/O latency or response time—is also an important measure. The question is, will file fragmentation take your I/O traffic for a detour?

Indeed, empirical test data show that when a file is severely fragmented, the maximum I/O latency of large sequential reads and writes (e.g. 256KB reads and writes) can suffer significantly. The following chart shows the impact of file fragmentation on the maximum I/O latency. The data were obtained from the same tests whose throughputs were reported in Part III of this series of posts.

Clearly, when the test file was fragmented into numerous 128KB disconnected pieces, some of the 256KB reads suffered terribly latency degradation. And if your applications happen to be issuing these I/Os, you would most likely experience a performance degradation regardless whether the I/O path can maintain the same I/O throughput.

Having some valid statistics may seem to add force to an argument, which makes it so much easier to be misleading if the whole story is not told, and technically everything is valid, and nobody is lying. By the way, this is a trick often employed by the vendors, who tend to conveniently ignore the bad news, or intentionally bury it with statistics on the good news. In my book, that would be lies, damned lies, and statistics.

For better precision cast decimals before calculations

Alexander Kuznetsov — Sun, 21 Dec 2008 03:38:00 GMT

I frequently do calculations against decimal values. In some cases casting decimal values to float ASAP, prior to any calculations, yields better accuracy. For example, in the following script both expressions should return 12100.0: SELECT POWER ( POWER ( 12100.0 , 0.01 ), 100 ), POWER ( POWER ( CAST ( 12100.0 AS FLOAT ), 0.01 ), 100 ) --------------------------------------- ---------------------- 13780.6 12100 Another example demonstrates poor accuracy of averaging decimals as compared to averaging...(read more)

Geek City: Splitting a page into multiple pages

Kalen Delaney — Sat, 20 Dec 2008 23:45:00 GMT

You know about page splits... if a table has a clustered index, any new row has to be inserted into the appropriate page, based on the clustered index key order. However, if the page where the new row belongs is full, SQL Server will split the page into two pages, and then put the new row on one of the now half empty (I know, I know, I should say "half full") pages.

But what if the new row is bigger than 4000 bytes (half a page) in size? I always knew that sometimes a page might have to split into three to allow big rows to be inserted, but I just found out it could be worse than that. If a page is split into two, and the new row still won't fit, one of the half full pages is split again. But if the row is so big, it still won't fit, another split could occur. And if the row is still too big.... well, you get the idea. One of the SQL Server engineers at Microsoft made note of what he called a 'corner case' where one page split into 17!

I read about that right before I shut down my machine one night, but I couldn't stop thinking about it. I woke up early the next morning, and my first thought was, "Why am I such a geek?" But I am what I am, and I was still thinking about a 17 way split. I wondered if I could duplicate that...So I got up and started trying. I spent several hours on it, but the most I could get was one page splitting into 10. It's always good to have something to work towards. Maybe next time I'm on a long plane flight (see my next year's schedule for possibilities), I'll try some more.

So here's how I did it. I probably should have called the table Sybil, but I called it split_page instead.

You can use any database; I used tempdb.

-- Create the table
USE tempdb;
GO
SET NOCOUNT ON
GO
IF EXISTS (SELECT * FROM sys.tables
WHERE name = 'split_page')
DROP TABLE split_page;
GO
CREATE TABLE split_page
(id int identity(0,2) PRIMARY KEY,
id2 bigint default 0,
data1 varchar(33) null,
data2 varchar(8000) null);
GO

-- fill page until no more rows fit
INSERT INTO split_page DEFAULT VALUES;
GO 385

-- verify that there is only one data page
DBCC IND(tempdb, split_page, -1);
GO

DBCC IND should return one IAM page (PageType = 10) and one data page (PageType = 1). If you want to see how full the page is, you can run DBCC PAGE and look at just the header. For your third parameter to DBCC PAGE, you should use the number for the data page that DBCC IND returned.

DBCC TRACEON(3604);
DBCC PAGE(tempdb, 1, 177, 0);
GO

The header has a value it it called m_freeCnt, and it told me there were only 11 free bytes left on the page, which is not enough for another one of these rows. (The INT primary key is always 4 bytes, and the BIGINT is always 8, so even without any overhead bytes, we need more than 11.)

-- Now insert one more row, this time filling the VARCHARs to the maximum length.
SET IDENTITY_INSERT split_page ON;
GO
INSERT INTO split_page (id, id2, data1, data2)
SELECT 111, 0, replicate('a', 33), replicate('b', 8000);
GO
SET IDENTITY_INSERT split_page OFF;
GO

When you look at DBCC IND again, you should see that the table now has 10 data pages! (Plus one IAM page, and one index page for the clustered index root, which has PageType = 2)

DBCC IND(tempdb, split_page, -1);
GO

Here's my output:

(I really don't know how to make the screen shot any clearer. If you click on it, it should open in a browser window by itself and be a little easier to read. If I try to enlarge it when pasting it here, it becomes very blurred.)

Have fun!

~Kalen

Did You Know? My 2009 Schedule

Kalen Delaney — Sat, 20 Dec 2008 22:00:00 GMT

I am already pretty booked for the entire first half of the new year. March and April include three separate trips to Scandinavia and in June I head down under for a four-city tour in Australia. I've also got several public training classes scheduled.

Check out my schedule here.

There are quite a few open weeks in the first couple of months of the year, but during that time I will be writing a couple of whitepapers for Microsoft, managing several others, finishing the upgrade of my course to SQL Server 2008, planning for a preconference seminar at TechEd in Los Angeles and updating my web site. I think that should keep me busy.

I frequently get asked about my travel plans, and many people say something like: "Do you ever come to <insert a city name here>?" The answer is, there are only a couple of places I go to regularly, where I have public training partners, and those cities are Bellevue (Seattle area), Beaverton (Portland area) and Minneapolis. Other than that, I don't know where I will be going before I get scheduled to go there. And I get scheduled by someone asking me to come to their city or their company. Last year I taught private courses in Portland, Dallas, Austin, and Danbury, CT. In addition to my regular training partners, I taught a public class in Brisbane, Australia. I gave a seminar in Kansas City, and a four-city roadshow in Sweden. I didn't plan any of those; I was invited to come.

If I know I'm going to be visiting a city, it's on my schedule. If it's not on my schedule, I have no plans yet to make a trip there.

So if you would like my 5-day SQL Server Internals class to be offered in your city, talk to your boss. Several companies could get together to bring me in and share the cost. Or you could get your local user group to arrange a one day seminar, which is what happened in Kansas City last year. (Unless there are several one day seminars, as I will be doing in Australia, a one day seminar is only possible in cities that are relatively easy to get to from Seattle.)

Although there are costs associated with the classes and seminars, I do frequently speak at local user group meetings for no charge. If you see that I will be in your city, and you'd like me to speak at your user group, just let me know!

Have a great new year!

~Kalen