OK, so yesterday I was talking about how servers can take a long time to finally be brought to their knees with poor processes, and that you want to get to the point where your server is on a strict diet and you control every morsel that goes into it.
Today I want to talk about how that can play into a real performance strategy because unfortunately you have more to consider than just raw performance. I know, I know, it sounded yesterday like I was advocating that performance was the most important thing. And it is… but at the same time it isn’t. So let me explain a little.
Performance is extremely important and some might even say it’s the most important thing on the server, but you also have to consider management et al.
See, quite often the code that leeches out the absolute best performance on the server isn’t as easy to manage as other solutions. Something like this isn’t very concrete without an example so I’ll give you one.
Let’s look at what I call moving schema. The guys in my shop are sick to death of hearing that term but it’s an important one. Moving schema is where you drop and recreate objects all the time that should be permanent. A good example of this is doing ‘select into’ instead of ‘insert into’. Sure, the ‘select into’ performs better than the ‘insert into’, but it’s also harder to manage from a couple different perspectives. For starters, even during your load you can’t specify a filegroup for the object so you’re stuck with the default. Now for performance or growth reasons you may find it better in general if you put it on a different set of disks, but you can’t because ‘select into’ doesn’t allow it. So from a space management aspect ‘select into’ traps you in a corner. Also, if you have specific permissions granted on the table you have to keep track of them somewhere and reapply them every day when you recreate the table. And you also have to remember to update your permission repository every time you change the perms on that table during the course of your other admin functions. As well, most performance stats are gathered by objectID. And if you’re recreating the object every day then your objectIDs are invalid tomorrow. So you either have to keep track of what the objectID is every day so you can track performance and other issues (like fragmentation) from day to day, or you have to store the objectName in your repository as well which leads to an inefficient and larger schema to track production issues. It also makes the timing of other processes more critical because with ‘insert into’ they’ll be blocked until the current load finishes, but with ‘select into’ they’ll fail because the object doesn’t even exist.
So ok, I’ve shown you a few reasons why something as simple as moving schema can be a problem. And like I said, it’s really one of my diatribes because of those issues mainly, and a couple out-lying ones. And the guys at my shop have been hearing this battle cry for about 3yrs now and I’m sure they’re quite tired of it. But when it comes right down to it, it’s good DB practice.
There are some instances where the moving schema rule can be broken though. Let’s look at a really large data load for instance. If for some reason you can’t do any kind of incremental load and you have to do a full snapshot every night (those instances are becoming fewer and fewer with Katmai) on a HUGE table (several hundred million rows), then you may be better off doing a ‘select into’ because you don’t incur the logging and it can it can decrease your load time significantly. It of course can also help control how big your log grows. So this is a decision you have to make at the time, right?
However, I will say that quite often, if you take the size of the log out of the picture, the ‘select into’ doesn’t out-perform the ‘insert into’ by all that much. I recently tested the 2 methods against 30mill rows and the ‘insert into’ only took about 40secs longer. That’s really not enough to worry about in the course of a full ETL load. And for all the extra benefits you get from having a stable object, that 40secs is more than worth it. So this would be a case where choosing the absolutely best performing process wouldn’t be the best way to go. In the case above where I talked about loading several hundred million rows, the ‘insert into’ may increase the load time by as many as 30mins and that’s hard to ignore. So depending on your requirements you may decide that dropping the object every day is worth it. But in the instance where the difference is only a handful of minutes, you’d typically choose to keep your schema in tact and gain the other benefits.
So OK, you’ve got a process and you’ve decided to drop the schema every day to do your load. And your process performs as well as it possibly could because of the lack of logging. The problem is that people tend to think of performance in terms of the single piece they’re writing and nothing else. Performance includes more than just the load. Performance also includes up-time, and number of errors, and manageability, etc. The load is just one part of the process for any given object. Examples of different parts of an object’s process would be the load process, the reporting process, the troubleshooting process, the triage process, security process, the space management process, etc. I’m sure there are others, but you guys get the point. So all of these are all part of the process of managing this object and if they all don’t play well then the process for that object doesn’t work well. So be careful in throwing all the other processes away and making them extremely more complicated just to appease a single piece.
So I’ve been in several shops and it’s always different everywhere. Some shops are really big into management and others are really big into performance. I’ve been in shops where the management of the objects was just a pain because the systems really were so busy that every last CPU cycle was counted and every last I/O had to be justified. So they were willing to put up with the management headache of that kind of performance cost because they needed it to stay alive. And again you have to decide what’s best for your place and your processes. But whatever you decide, don’t rest on that decision. Keep making it again and again. I’ll talk about that tomorrow.
I guess that’s all I’ve got to say for now.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.ITBookworm.com
Read my book reviews at:
www.ITBookworm.com
Blog Author of:
Database Underground – http://www.infoworld.com/blogs/sean-mccown
Follow my Twitter:
http://twitter.com/MidnightDBA