Don Vilen is a program manager in SQL Server engine group
Greg Low: Introducing show number 18 with guest Don Vilen.
Our guest today is Don Vilen. Don’s a program manager in SQL Server engine group, welcome Don.
Don Vilen: Thanks Greg.
Greg Low: As usual, describe how did you come to be involved with SQL Server in the first place?
Don Vilen: About 11-1/2 or 12 years ago, I was too comfortable in my old job as a professor at a university in northern California. Thought it would be cool to work at Microsoft. I had taught computer science courses for 15 years and taught quite a few different things. Job seemed interesting at Microsoft, working with SQL Server, the database system. Needed someone to do training for them. Called worldwide training at time. Group of three or four people, trained support engineers, that was the position I accepted. I trained support engineers, escalation engineers around the world for a couple of years. Couple of guys from SQL Server development team asked me to join. I was actually the program manager, tester, and developer for SQL Server setup for one point in early 7.0. After I did that for a year-and one-half I moved to the UK, in Reading outside London, trained support engineers again, across EMEA around the world. Five years ago after being in England for four years, moved back to development team in Redmond. Been working on development of SQL Server 2005 since early 2000.
Greg Low: Many years, that’s great. We’ve both been at TechEd in Australia this week, kept us busy. I’ve had a lot of sessions myself, and Don’s had similar number of sessions. Must be fairly tired at the end of the week. What I trouble you to do, looking to do in this show is go through material I saw in one of your other session in Dallas or Munich or somewhere. Looking at transparent upgrade benefits. Number of things in SQL Server 2005, if you upgrade without doing anything else, there are number of distinct advantages to upgrading the product. Start to talk through those. First on the list was for Techsearch.
Don Vilen: Many different things with various features within SQL Server you could take advantage of without changing application. Called these transparent because they required little server change, if any. Refer to them as immediate benefits, the ones you get just by upgrading. Key thing about full text is its multi-instanced, separate servers for each SQL Server instance you install. Isolate different instances; take advantage of everything you get with multiple instances of database engine as well.
Greg Low: Things like shared I-filters and so on, those were an issue before?
Don Vilen: Different things. Comes down to installation of service pack, you might be thinking you’re still working on SQL 7.0 full-text index, but someone’s installed SQL Server 2000. There’s only a single instance of it, you were brought forward in time as well. Different things you might have within a full text index, but all those different things helped you by separating things.
Greg Low: The I-folders before, were they shared with index server before or were they separate filters?
Don Vilen: I don't know.
Greg Low: I had seen in some material that you could have an issue where you’re dependent on one of the filters. I-filters are things that take the content and send a stream of characters. Noticed the general approach with index servers, they take different filters for different types of documents, generate a stream of characters, language-specific word breakers that broke them into words. After that, noise word removal, words like “and” and “the” that you don’t want to index. Index server did things like add to noise words yourself. You might have company name would appear in every document, don’t want to index that as well. Things left over ended up in index. One thing I’d heard was a shared I-filter thing with Index Server. Someone could make changes to that, it would affect SQL Server instance. All those things are now instanced.
Don Vilen: Just because you needed it for one of your instances of SQL Server doesn’t mean it has to apply to every full-text index you have across all the instances.
Greg Low: Performance is the main thing claiming big benefits.
Don Vilen: Confirmation at TechEd this week on that. Someone had full-text catalogues, when they populated them it would take four or five days, they upgraded to SQL Server 2000, half hour to an hour to do. The number I quote is 100 times faster, we have found a lot of the population to be that.
Greg Low: Love the database optimization, see it in Dot Net world, see people trying to do optimization. See numbers like five and six percent faster. Love it in database things. In performance tuning, from five minutes to two seconds. Nothing anyone’s going to do in other parts of optimization to change anything that looks like that.
Don Vilen: People will start submitting bugs because it’s too fast. “We can slow it down for you!” It’s doing its job, it’s a lot faster.
Greg Low: Liked the idea that full-text catalogues are now in the database, part of backup and restore strategy as well.
Don Vilen: Always one of the drawbacks, so although you can build indexes a lot faster maybe you don’t need to rebuild them. When you backup database and restore it, full-text indexes go with. No need for repopulation like in the past.
Greg Low: How does that work with attach and detach?
Don Vilen: Same for attach and detach. It understands full-text catalogues there, when you detach and attach, it understands…
Greg Low: Do they have to be in sync with the database? Had someone ask me at show, thought it was interesting, they didn’t have enough performance available to build full-text catalogues in first place, wondering if there’s any way to build them on a replicated system or on some mirrored system or something else, attach to database. Didn’t know if there was a way to do that.
Don Vilen: Don't know if there’s a supported way to do that. Key thing. After playing with insides of SQL Server, I know a lot of ways to get some things done. I wouldn’t publish them because people would do it and expect it to work other ways. This is one of those. You would think that it wouldn’t have to be completely in sync, would have to be in sync with database itself. Might not be fully populated, something they could pick up and carry on from where it had been before. Does get moved as a unit.
Greg Low: Got to do a Michael Reese session this week, full-text search covered in that. Found interesting that they now have I-filters for XML data type. Been added to product. What he had were nice examples of using promotable properties effectively. Had sort of user-defined functions that did ex-query to extract data out of XML columns. Promoted them as computed columns. Index those using full-text indexing, completely magic stuff. If people have a chance to look at that on TechEd US DVD, Michael Reese’s session would be worth the look. Another is query optimization. Queries that run quicker.
Don Vilen: Lots of things to do from a logical point of view, queries to do to make things faster, other things too. First thing is non-updating updates of indexes. Simple thing to do, if you tell me to update a million rows, setting a certain column to a value, if half of those columns are already those values, why should I go to all those rows and update the index if it’s already correct? I do have to touch the rows to cause triggers to fire. That’s what behavior you’d expect. If you set a column that’s already one equal to one, I shouldn’t update the index. Skip that step, which means our non-updating updates really can avoid that.
Greg Low: That could be quite key, too. See a lot of object-relational matters. Tend to read in the state of an object and modify some property of the object, persist the entire object back, update all the columns even though they’re not being updated. If there were indexes tied off those, could have substantial impact.
Don Vilen: Another one is that we will match indexes and reviews in more cases than we would before. Gotten smarter with that. Several cases where you have a query that doesn’t mention index view at all. Determine that answer could come from a view that’s pre-computed and sorted index view. Do that in more cases than before.
Greg Low: With index views, my recollection was it was only the enterprise edition that chose plans based on index views, is that still the case?
Don Vilen: Yeah, still the case. Some ways to get standard edition to use Index View, had to mention that.
Greg Low: Like a hint or something.
Don Vilen: Yeah. Other thing with query optimization, we call it predicate implication across equi-joins. If we know that you’re doing a join on equality, for example, you’ve got column X and joining on two tables R and S, also saying that’s column X in the table S is greater than some value. We know it has to be greater than that value in the other table. Make use of index in other table even though you’ve never said it’s greater than 10 or something like that.
Greg Low: Presume optimizer before would’ve dealt with that if it was in equality. Now just a range or being able to imply something?
Don Vilen: Equality across a join now. If you had said a certain column was equal to another column, we could’ve taken advantage of that. Now it’s a couple of columns in a couple different tables. Another area is optimizations we can do by looking at query itself; call them symbolic query optimizations or simplifications. Perhaps because I’m doing select and joining to a view or something I may get different predicates, that either seem to conflict or further reduce what one predicate might have done. Might end up having ware-clauses for different places that in a single query come down to saying that column A is greater than three and column A is greater than five. In past keep those predicates. We know that if something is greater than three and five, it has to be greater than five, throw away the three parts. Simplifies things, Start at five onward when looking at index rather than looking at three. Throw away everything from three, four, and up to five.
Greg Low: People look at that and go “why would anyone write a query where A is greater than three and A is greater than five?” I see those things are generated by code or generated by query tool, end up putting in overlapping predicates, you would’ve processed them before.
Don Vilen: You might have two or three views selecting from, you don't know what the ware-clause is in the view themselves are, we bring them together and get rid of the extraneous stuff. Another case where we do similar thing is for betweens and other comparisons. If we have a ware-clause that says A is between one and 100, and another that says A is between one and 200, we can reduce that to A equals 100, we know that’s the only part that would be able to solve that pair of equations or expressions.
Greg Low: Interesting. Then statement level recompile is another one in terms of performance.
Don Vilen: These are all things you get for free; don’t need to change your application. Statement level recompile should help with many different queries. You might have a stored procedure, 30,000 lines long, somewhere near the top refer to temporary table you’re creating. Later you might refer to it. Every time you refer to it, we have to recompile in 2000 from that point onward. If it was 30,000 lines long that could take awhile. If you hit it again we recompile from that point onward. You had something that forced a recompile. It would be nice if it only recompiled the parts that needed it, so now in SQL Server 2005, we’re much more sophisticated, we’ll only recompile that statement that needs to be changed, not all the stuff from that point on. Cool thing, people will be less effected by recompiles in the future, only doing it at the statement rather than re-recompiling over and over.
Greg Low: Found previously people would take larger stored procs and break them into pieces to avoid the recompile overhead where that would need to occur. This seems to get around there nicely. When I Look in pro-filer and you look at re-compile event. Seems to be more re-compiles occurring, but they’re shorter duration.
Don Vilen: Another solution to the recompile problem was the use of table variables. Logic that underlies that didn’t have to recompile when using table variables, but temporary tables would require recompile.
Greg Low: Noticed that there’s a good white-paper by Aaron Marath had originally. Talked about recompilation strategy. Recall that it’s a white-paper on the MSDN site. Temp tables, it had a row counter for modifications. Quite tidy, threshold was six rows or something. At that point it would cause recompiles with table value variables and said yeah there wasn’t a recompilation threshold for those. Kind of interesting.
Don Vilen: I believe that when you’re creating a temp table, it can’t be the same temp table that someone else is using. Can’t use the plan we’ve created for other people. Object ID has changed, we have to change it from here on down throughout the program. Key reason for temp table recompile.
Greg Low: Next one on the list was forced parameterization.
Don Vilen: Something called auto-parameterization, big mouthful to say over and over. SQL Server has been using auto-parameterization in order to obtain reuse of plans for quite a while. If you have two identical queries, a select or an update or something, except for the constant, so somewhere you’re setting a column to a three, or equal to three, issue exactly the same statement. 17 in it instead of three now. Can’t reuse plan because plans aren’t character-for-character identical.
Greg Low: Seems to not be clearly understood either, even though the server is set up casing-sensitive and everything, there seems to be a strict text-comparison it’s doing of the plans. White space and different characters, casing and so on. Make it end up being a different plan. Found useful is the new Showplan XML. Found you can easily see the auto-parameterization occurring. When I looked at Showplan XML and a statement that has been parameterized, immediately at bottom of XML plan, it breaks down details of parameters it’s found in there. Seems efficient in the way it does it.
Don Vilen: Go through and replace constants with variables, create variables for you, put it in there so next time we encounter a query like this, if we auto-parameterize it, it will have the same characters and statement, we can reuse the plan. What we’re doing in SQL Server 2005, you can force it to do this in cases it wouldn’t have done so before. Try to walk fine line between spending time auto-parameterizing to reuse a plan or let’s do it lighter and maybe miss cases where we could’ve reused a plan, but save time on compilation. We might go thru and do this auto-parameterization only to find there wasn’t a plan for it anyway. Now, if you turn forced auto-parameterization on at database level, we’ll do what we can to parameterize things. This had to be a database option rather than go on by default, might change behavior of system. Might spend more time compiling, but if you want us to, we’ll do it.
Greg Low: Changes to how statistics are handled?
Don Vilen: Several different changes with statistics. First, statistics can be updated asynchronously. May be aware that you’re running a query that has a plan out in cache, updated rows within tables, might have hit trigger that I shouldn’t use these statistics because I’ve made so many updates they might not be correct anymore. Maybe my plan is incorrect. In this one update you’re doing, now you have to wait until statistics get recalculated. May be better to have statistics that were there continue to be used, and have flags that say update these but don’t make me wait for them .That’s what we do. Use existing plan and statistics, while we’re doing operations, perhaps statistics will get updated so next time that comes along we have updated statistics. You can improve predictability. Query that was running pretty fast now doesn’t have to be changed while you gather statistics. Our statistics are more accurate, too. Changed underlying structures of some statistics, keep statistics on strings now. If you’re doing something with a wild card at the beginning of a string (used statistics for wild cards on end of string), but now have statistics that help us understand distribution of strings. We can also create statistics on computed columns, so if you have a query that uses computed columns and a where clause, might be nice to know distribution of values on computed column even though the computed column values don’t get stored.
Greg Low: Not even that we have persisted computed columns. This is on non-persisted computed columns.
Don Vilen: Column can have statistics, just happens to be computed column that doesn’t have any real data stored underneath it. Could be with persisted we could store those values if calculating them takes a while. Now we’ve got these statistics that give us better ideas as to what indexes we should use for computed columns, come up with better plans, that’s what statistics are for. Parallel statistics gathering with using full-scan. If you tell us to gather statistics and do full-scan while we’re doing that, we can now use multiple threads and scan different parts of the table using different threads, calculate your statistics faster. Only on full-scan when recalculating statistics.
Greg Low: Lastly, changes to SharePoint update statistics.
Don Vilen: SharePoint update statistics was another “why didn’t you do this before?” type of thing. SharePoint update statistics goes out and does an update statistics statement for every table in database. Some of your tables maybe haven’t been updated. If we go out and update statistics on those tables it would be a waste of time. Now with update statistics, what we’ll do is check to see if there have been changes to the table. If there have been changes to the table we’ll update your statistics, if there haven’t been changes to your table your statistics are good and the same as they were before. Some people have found this is faster. If you have read-only tables that haven’t changed since you last did it, we’ll skip those and it’ll be faster. It’s smarter since it avoids work that it didn’t have to do to start with.
Greg Low: Know especially with indexes, something that seems useful is the new distributive management views with index usage stats. Wondering do any of the things that make these decisions use that same data, or some other data?
Don Vilen: Few categories of index DMVs. One is index usage and other is missing indexes. In some cases we’re gathering that information to be able to report it to you; you can make appropriate decisions based on that. Execute a query but the optimizer couldn’t find the perfect index it would’ve used, records that in missing indexes. Perhaps you go back and decide you need that index. Doing that completely in isolation and without regard for the amount of updating you might have to do in order to maintain those indexes. With usage statistics you may be wondering if there are certain indexes that aren’t being used. Look at index usage to see what’s not being used. Maybe it’s time to get rid of them. Could put that together with database tuning advisor, if you tell it it’s okay for it to recommend the removal of certain indexes. It could look at index usage statistics to determine they hadn’t been used anyway. Given the fact that most of the DMV values get reset each time you restart server, they could’ve been used, but used previously. Maybe those will evolve to being recorded and you can select everything out of index user…
Greg Low: And periodically put it into a table.
Don Vilen: And make decisions yourself. Some is framework for future work, anyone who looks at it or understands it can figure out a direction it could go, whether it’s the direction I don't know.
Greg Low: Would you share about yourself, where you live, families, sports, hobbies or anything you’re willing to share.
Don Vilen: I live in Seattle area, Kirkland, along Lake Washington. Lucky to get spot over water, view of Seattle skyline.
Greg Low: Lot of water around Seattle.
Don Vilen: Yeah, it falls out of sky all year long. Enjoy going for a run, went for 10k run in Sydney this morning, around Harbor Bridge and around. Did come back and did TechEds and other things. I enjoy the traveling I get to do, mostly because of these opportunities to speak at different locations. 50 countries worldwide the past few years. Enjoy doing that.
Greg Low: Earlier in week, you mentioned busiest week you had was nine sessions in TechEd Australia and the same in New Zealand in a week?
Don Vilen: Yeah, for TechEd 2000 did nine talks in Australia, had a day to travel to Auckland and did nine again. One of our architects said we had a guy who did 10 percent of a TechEd. I didn’t know. Calculated and I did 10.5 percent of two TechEds in one week.
Greg Low: Had 12 sessions this week, they were telling me I had my own track. In addition with other things, query execution changes with dynamic objects?
Don Vilen: In the past when we were taking a look and calculating a plan and putting it into cache, some were marked for immediate recompile, they wouldn’t be held. That applied to dynamic objects like triggers. Trigger needs to be compiled and executed, we’d always mark it for immediate removal, or not re-use. Now, for dynamic objects and triggers, we’ll retain that so we can use it again the next time.
Greg Low: Distributed queries now executed asynchronously?
Don Vilen: When you’re working with a distributed query, in link servers, used to be that if you mentioned two link servers in same query, it would go after one, pull back data, and then go to second one. Now it will start up on both sides and pull back data asynchronously rather than waiting for it.
Greg Low: Also noting additional exposed through SQL Server profiler that weren’t there before.
Don Vilen: SQL Server profiler has new capabilities. One is the ability to profile events out of Analysis Services, works with distributed query, tied together with what we were just talking about. Deadlock notification and graphs it can also do, just showed one in the session I finished.
Greg Low: The session I did this morning was Bob Beauchemin’s session on error-handling. I showed a deadlock graph in there as well. Look at lots, didn’t hover over objects in the graphs before, didn’t realize it showed batch of statements in each of the things involved. Very powerful.
Don Vilen: Interesting that you can save these graphs as XDL files, so it’s a new vent out there in profiler. Click Deadlock group as event to capture, another tab that shows up for XML Showplans and representations of your Deadlock, have each one sent off to a file, either separate or one that combines them. When you open those files it shows the graph again. Open in Management Studio, still hover over it. In the past with Showplan, you’d have to screen and capture or see in text, but now you can capture in XML, XML can be rendered as graph for Deadlock or Showplan, you can still hover over different aspects and see what’s there, it’s really cool.
Greg Low: Off-line rebuilds of indexes have improved.
Don Vilen: This talk that we’re going through was transparent benefits. Things that meant that you didn’t have to make changes whatsoever. Always an intro to “well we have this new online index rebuild.”
Greg Low: Customer one-on-ones this week. For very last database guys, online index rebuild is a godsend. Number one thing they all keep talking about.
Don Vilen: Having it online means you can get to the data even when you’re doing rebuild. Table and index partitioning means that you only have to rebuild the part of the index that’s been updated recently, so you have less of an impact even there. Then, even if you don’t do the online or partitioning, you’ll find your index rebuilds are a bit faster.
Greg Low: Locking changes?
Don Vilen: Within locking we’ve got the fact that lock memory is more NUMA-aware. With NUMA processor you’ve got local memory.
Greg Low: In general, NUMA, for anybody listening, going “what’s NUMA aware?”
Don Vilen: Means Non-Uniform Memory Access. Processor has memory tied closely to it; if you want to go to it it’s quick. If you want to go to memory that’s on another processor or elsewhere it’s going to take longer, maybe ten times longer or more. If possible, like to get to local memory rather than somewhere else. With locks we’ll create locks close to processor that’s referring to it, rather than storing it in some memory that’s further away. More aware of NUMA architecture, works with its lock memory in that way. Done lock partitioning, allows us to take a lock and separate it, create multiple locks, but gain from that same NUMA type processing. If you’ve got 16 or more CPUs it’s on by default and we partition the lock. Looks like we’re taking more locks, but you gain because you’ve got those locks locally rather than having to reach out to some remote memory. One of those things that rather esoteric, only if you have 16 processors or more, not everybody. About the detail I’d go into at this point, a lot in books online that’s just on lock partitioning section.
Greg Low: DBCC, bit of overhaul, several changes?
Don Vilen: Key ones, use database snapshots, be able to create a copy of a database at certain point in time. DBCC CheckStar the different check operations, will use a database snapshot to get a consistent point in time version of a database. In past when you did a DBCC it would scan database and then look at log to see what changes happened while it was scanning your database and figure out if any facts it had gathered and errors it thought it had were really not errors because you’d done some operation as it was scanning the database. Now it sees the database as of one time with database snapshots, builds underneath the covers, does it on alternate stream of each of the files within database. One thing that magically works and avoids problems.
Greg Low: Do you see the snapshot file appear in the file system, is it like a normal snapshot?
Don Vilen: It’s an alternate stream…
Greg Low: Like an NTFS alternate stream?
Don Vilen: On a regular database snapshot you specify where those files should be and you see them. They’re an alternate stream. It looks like a dollar file if you know what you’re doing, I don't know if you can find them. Under DBCC, if you’re doing a shrinker or a defrag, your large objects get shrunk and defragged, and moved around now. In SQL 2000 you might have noticed that you thought you were shrinking, but some of those pages didn’t move since they were large objects. The large objects have tree-like structures that are pointers to other aspects of them, and if you ever moved a part of it you’d have to move al the pointers that point to it. Convoluted, complex process. We’ve made sure we’ve done and tested it, it works, that’s fine, so now we work where we have that capability in SQL Server 2005 where we didn’t have it before. Less reserved space for large objects than before since we know how to compress now.
Greg Low: Number of operations in DBCC now have progress reporting. Check operations and shrink operations.
Don Vilen: You can go on to the DMV on ExecRequest and it’ll tell you how far along it is and how much further it has to go.
Greg Low: Some of those processes could take a while.
Don Vilen: If you have a huge database. You don’t have to check the entire database, you can check file groups. Some people get a database check going and they’re wondering how long that’s going to take. You always kill it just before it finishes. Now you know.
Greg Low: Storage engine changes, close to home.
Don Vilen: My talk I always give detail. These are ones we have good detail. Rows can be larger than an 8,000 page. In past the largest was 8,060 bytes since it had to fit on a single page, and that’s not counting any large objects or text image in-text. Now, what we will do is we’ll take part of variable columns and spill them over into another page. You can create a table that has 10 columns, each could be a varchar 8,000, you could fill them all up. In the past you could create them, but not fill them up.
Greg Low: And got warning that you were creating a table you might not be able to populate.
Don Vilen: Once you did an insert or an update you got an error rather than, now you don’t get those. Improved temp database scalability. One thing we have is a new snapshot isolation, makes use of temp database. Other aspects of SQL Server that make use of temp database as well. More scalable, that it could handle this heavier load. Aware that temp database attributes make it where we can treat it special. One thing is when you start a SQL Server instance, temp database gets re-created from model database, whatever was in it gets thrown away which means you don’t have to restore the temp databases, don’t need stuff the temp database had for restoring. In the past (I don't know if you go back), but prior to SQL Server 7.0, when we had syslogs, and it was a table and had to look like every other just to do it. I only changed it… That was syslogs; I’m talking about temp database. There are things we don’t have to do with temp database, we’ve optimized it.
Greg Low: Do you think in terms of putting things into production people need to be more aware of temp database with this version? Thinking in terms of raw version stores and these things. When we went to past conference in Dallas and had MVP conference, spent a day with engine team guys. Did a session with us for an hour on changes surrounding temp database. I was impressed with the amount of work that had gone into that area. Left feeling that there’s more things dependent on it. In terms of performance or things, it may be more critical than it was before.
Don Vilen: More things that work with temp database. If you do some of the things like row versions with snapshot isolation.
Greg Low: Triggers, another.
Don Vilen: Triggers. Every time you work with a trigger, a trigger is using a raw version as well, even without snapshot isolation turned on. The nice thing with triggers doing that is does use temp database, but some of the info doesn’t make it to temp database disc, gets used up in cache, removes concern.
Greg Low: Lots of memory.
Don Vilen: Also saves us from having to read from log, that’s where inserted and deleted tables used to be. Maybe accepting smaller charge than bigger charge we used to have before. Increased scalability. Should be faster, using a different methodology. With temp database, we recommend as many files as you have CPUs, just to help avoid some of the allocation conflicts you might have. We do a lot better when it comes to allocations and avoiding the locks and conflicts there would be. If we could allocate space on files at same time, we’d avoid even more conflicts.
Greg Low: Rather than doing it via autogrow.
Don Vilen: Autogrow is always a good thing to have turned on, just in case, if your database needs auto grow it can take care of itself. Autogrow on any database as well as temp database is better managed than ad hoc let it happen when autogrow feels like doing it. If you grow all your files at the same time, we can do our proportional fill like we’ve always done. If you rely on autogrow, one of the files out of that group is going to grow and everything’s going to go into that one rather than getting spread across different files, so it’s always good to have multiple files for a database, especially for sufficient loads.
Greg Low: Number changes, backup and restore area, the high end with log-shipping.
Don Vilen: First off, mention that log-shipping is also in the standard edition, interesting aspect to it.
Greg Low: Number of things in standard edition that required enterprise edition previously. Pleased to see them.
Don Vilen: Log shipping, database mirroring, new of course, available in both. Special capabilities in enterprise edition. Needs operating system underlying it. Under backups, you can backup data and log at same time. Great for log-shipping, you would perhaps log ship every two to five minutes. If someone was doing data backup at the same time, your attempt to backup the log would fail. If you’re doing it for disaster recovery, and you’re backing up every two minutes, you’ve lost one of your two-minute cycles. Come back later and try again. Which, as long as your system was available two minutes later, now you’re backing up four minutes. Now you can back up the log and the data at the same time. That’s great. If you have log shipping happening and you’re backing up the log, you can still back up the data. If you’re backing up data and log backup comes along you can do that.
Greg Low: Restorers, more powerful?
Don Vilen: We can restore single pages now, so that’s a great thing. Verify check sums that you took during backups, things like that. Require change on your part, in this talk I give it was transparent benefits, one was restore verify only. It’s sort of those things we improve every release, like our Deadlock detection. It either changes or we improve it. Restore verify only does virtually everything except writing those pages onto the disk and over the top of your database. Go through and read every page, do check sums if you have that turned on. Check the formats of the pages and things like that. Does virtually everything except final steps.
Greg Low: Have a colleague who used to describe (an area you had to get right), CLM. Career limiting move. The users of HP saw so many hardware issues that could also cause pain, grief and agony, been wary over the years. Remember one type drive, for example. Saw one that had the erase head stuck on, it would erase the data, write the new data, read the data, check it was fine, and erase the tape as it rewound. Nothing short of rebuilding systems occasionally from back up to have any concept that you have what you’re meant to have on the tape.
Don Vilen: Times when it’s not important. Not when you have your CIO and VP over your shoulder and asking how much it costs.
Greg Low: Even with new options (verifier), at least this drive can restore it and almost get to the same thing. Seen situations where the only tape drive that will read the tape is the one that just got burnt or stolen. With backups and restores, I like to periodically see systems rebuilt on others to see you have a chance if things go wrong.
Don Vilen: Verify only is something that changes with each release. Back in 4.2 and 6.0, I can see the file it must be good, didn’t bother to read it, changed it as we’ve gone on through.
Greg Low: Used to see ones that were loaded tape, if it had the right number of tape markers it could read the tape. The tape’s fine.
Don Vilen: Bits aren’t important, just tape marks.
Greg Low: So the file IO, changes in that area?
Don Vilen: Realized that there are disc systems that can lose a bit every now and then. As they get larger the possibility gets higher. People will sometimes blame SQL Server or point the finger our way. We haven’t had any corruptions or problems within SQL Server to be the ones to take the blame. We put check sums on each page. If you turn this on as a page verify option, every time we write a data page out to the disc, we’re going to put a check sum on it. Go through and add the bytes together, store that value to the page and write it out.
Greg Low: Much of an overhead with that?
Don Vilen: Shouldn’t be, only when a data page gets written. If we have something in cache and it gets used over and over, make maybe…. Every time we read it we have to make sure it’s correct. If there’s a problem the operating system or the disc system didn’t report to us. Different tests. Some are two, three, or four percent of impact, depends on behavior of system, how often page gets updated and written. If you touch every page and there’s not enough room in cache for it to retain, you might see something there. Gives you an idea that you’re not losing information.
Greg Low: Files get initialized instantly now, too as an option?
Don Vilen: People are going to tell us it’s too fast, there must be a bug. With Windows 2003, and right permission on service account, when we ask Windows for space, if it’s got SE managed volume name permission it can give us this space and we can use it without having to write a bunch of zeroes before starting work with it. One thing that’s happened with autogrow, create a database, or restore a database, which causes same storage allocation. We’d have to write it out, write zeroes all out before putting real data on top of it.
Greg Low: Quite scary in an autogrow operation.
Don Vilen: Don’t want to spend the time to write 10 gigabytes since you’ve got some file…
Greg Low: And it said add 10 percent to the size of the 100-gigabyte file with autogrow on 10 percent.
Don Vilen: Now we can get that data space and start using it right away. Zero it out as we start allocating those pages. Don’t have to write to all of it before we start working.
Greg Low: Question I have. Wondered why default value is 10 percent for autogrow, rather than certain size that wouldn’t cause a problem as it got larger.
Don Vilen: Right decision at the time. People didn’t realize I’m going to have this 100-gigabyte file, and 10 percent is a large amount. Okay for default, but maybe people select default when they should’ve thought about it. Start with small databases, your company grows, you’re saving everything in the world inside your entire database.
Greg Low: Optimizations on inserts.
Don Vilen: Some right operations that work with range of rows. Don’t think of insert having more than one row that gets inserted at a time, but if you do…
Greg Low: Insert select or something like that…
Don Vilen: Now you can have a batch of multiple rows that get inserted at a single time. Certain optimizations for bulk inserts to do this thing, BCP, and we can use the same optimizations for these multi-insert statements.
Greg Low: One of the last ones is database recovery changes.
Don Vilen: Have something called past recovery. In enterprise edition, go through and allow the user to be in the database while we do the undo phase of recovery. Every time we start up database, we go through and redo and undo phases to make sure everything in the log is also on the data pages. SQL Server 2000 and in standard edition, we have to wait until both the redo and undo phases are complete. Enterprise edition, let you in the database before that.
Greg Low: Is it limited to a table though? Does it know which are affected by the undo?
Don Vilen: During the redo we take locks on different rows that we’re redoing. When a commit comes along those get committed and those locks get released. Anything that doesn’t get committed or rolled back from the log is still there, must be stuff we need to roll back. We still have locks on those...
Greg Low: Changes to Checkpoint process itself?
Don Vilen: Checkpoint has been around for a long time. What it does is periodically takes dirty pages and data cache and writes it out to discs so we don’t have to spend too much time recovering. Good baseline, when we start a database, we know where everything was in sync before, that’s the last Checkpoint record. Checkpoint process comes periodically after a certain amount of log records have been written. And then it says oh I need to write these data pages out.
Greg Low: Is it time-based currently? Or is it based on number…
Don Vilen: What you do is you say you want the recovery not to take more than this amount of time. Tied that to a certain amount of log records. This many log records is probably going to take this amount of time to recovery. How much is in the log since we last did a Checkpoint, time to do a Checkpoint, it should only take this much time. Not a perfect measure, but a good ballpark, measured on a certain machine at certain time. Checkpoint used to not have to worry about overloading the IO system since we only had 32-bit addresses. Limit to amount of data cache that you had. Even if caused queries to pause it wouldn’t take too long. Now with 64-bit, you can have 64 gigabytes, and if I’m trying to write 64 gigabytes to the disc, you might notice that, and it might have a real impact on what you’re processing. Checkpoint’s a lot smarter now, it realizes that it can’t just slam all the data out to disc; it has to be a better citizen within SQL Server. Monitors the IO load that it’s placing on the system, backs itself off and tries not to have as much impact as it could. Checkpoint could write a subset of pages out to disc, once it writes them they’re not dirty, they’re clean. What’s on disc is the same as is in the cache. If you needed to stop Checkpoint and start it back up again you could do that, it would just continue with the dirty pages. The ones wrote before don’t have to be written. Checkpoint also is a statement, execute the Checkpoint statement, specify duration. I want this to be done quickly or not so fast, maybe not as important as something else.
Greg Low: Do you know if things like detach force a Checkpoint before they detach?
Greg Low: See people in scripts who do a Checkpoint before they run a SharePoint detached database.
Don Vilen: I believe a detach will quiesce the database. If you were to look at the status, you could see the database was cleanly shut down. There weren’t any dirty pages. The stuff in the log we could almost get along without, made sure the dirty pages were written to the data, nothing that needs to be recovered. Attach single file database only works with databases that were cleanly recovered. Doesn’t have to look for a log because it has what it needs.
Greg Low: Heard people say that the easiest way to shrink a log file on databases like that is to detach it cleanly and reattach the single file, quickly removes log file.
Don Vilen: Created a database for attach rebuild log as a new DDL, if you cleanly shut something down, then it can be an option, but a problem is that people delete the log and then do this….
Greg Low: I see that all the time. People just copy MDF file and ask how they attach single file database. Sorry.
Don Vilen: It comes out as a sorry, there’s nothing we can do. You had stuff in your log that you needed, maybe you should’ve renamed the old log file and tried it. Make sure you’re covered, don’t delete that old log without coming forward. Otherwise you might get a message saying sorry, I can’t recover, I need that log.
Greg Low: Often see just with operating system guys that have backed up what they think is the database file, and the log hasn’t come.
Don Vilen: Education is one of the key points for high availability. If you want your data around you should understand how it works. If you get the system guys who don’t have the respect for our data we wish they did.
Greg Low: In your talk you also had raw-level versioning, sounds like one that’s not transparent.
Don Vilen: Raw-level versioning comes in two flavors. Snapshot isolation, have to change application to use it; the other is re-committed snapshot isolation. Don’t have to change application for that, stays at default of re-committed at isolation level. Under covers within database we’ll use raw-level versioning. You’ll see the values as they were as of the time you began your statement, quite a difference from snapshot isolation. In snapshot isolation you’ll see the values that were committed as of the time you began the transactions. If inside a transaction you read the same data over and over you’re going to see the same stuff, that’s what was committed when you began your transaction. Re-committed snapshot as isolation, if you read the same thing a couple times you might see things a little different because you’ll see the time you began your statement. One of the nice things about snapshot isolation raw-level versioning, snapshot isolation, re-committed snapshot isolation, see the value that was committed. Maybe things have changed since you began your statement, but rather than pausing or waiting and being blocked by someone who’s updating things, you’ll get to see the value that was committed. Gives you a time-consistent view of all the data, as it was at a specific time, rather than seeing values that have changed subsequently. Reading old values at the beginning of a table scan and new values at the end.
Greg Low: Already mentioned triggers and using raw-versioning internally. Described to me that inserted and deleted tables were like a view of the transaction log itself, used to have to recover the values from there. Now in the raw version it seems quicker.
Don Vilen: It’s always interesting, if you ask people where the inserted and deleted tables are, most say they’re in temp database, that’s wrong, they’ve always been in the log, it’s a logical interpretation of what’s in the log. And now it’s the right answer, using raw-level versioning. Those versions are out there in the logs.
Greg Low: Getting toward the end of this, but clustered index rebuilds?
Don Vilen: As you probably know, if I have a clustered index, the clustered index key is part of my non-clustered index rows, as are row locators. That’s how I find a row. I might look something up and find it in the non-clustered index, but in order to find the column I need, I need to go to the table. I use that clustered index key to get to the row in the table. I rebuild my clustered index that might change some of my row locator, especially if I had a non-unique clustered index, since I have a uniquifier as part of my added row locator. If I do a rebuild of my clustered indexes it might move some rows around, change my uniquifier. Means that my non-clustered indexes aren’t any good anymore. What we’ve done is tried to preserve those uniquifiers, and in more cases your non-clustered index will be good, don’t have to rebuild it. Don’t go out and drop your non-clustered indexes before you rebuild your clustered index only to rebuild your non-clustered index. If it needs to be done we’ll do it ourselves. In the past if you rebuilt your clustered index and your non-clustered indexes weren’t good anymore we’d rebuild them for you. Seen some people who’ve dropped them first or go through and rebuild them after, not realizing they got rebuilt automatically.
Greg Low: General things, interesting changes to server and SQL operating system.
Don Vilen: SQL operating system some people think of it as separate entity, but a bunch of functions that the rest of the SQL Server calls, just consolidation, centralization of some of the mechanisms that allocate memory or starting threads.
Greg Low: Tend to abstract away the underlying operating system?
Don Vilen: To some degree. Rather than having some different parts of storage engine or relational engine call the operating system, it just uses this central part called the SQL operating system. Not a separate entity, just an abstraction.
Greg Low: Often when you add an abstraction layer it’s to replace the underlying, any intention to move to a different underlying operating system, or support the product on other underlying operating systems?
Don Vilen: No, we’re still Windows only, the big benefit is rather than having everyone vie for memory themselves, if we put this through a central place it can be managed, we can work with being a good citizen regarding memory usage, allocating and releasing memory. More of a management thing than portability layer or anything.
Greg Low: In terms of replication, seems better supported in this version of the product.
Don Vilen: Changes in replication. Two cool ones. Peer-to-peer replication, capability of improved availability, good scale out. Another is if you alter a table at a column, rather than using replication-stored procedures, back door, or some replication stored procedure that will do alter for you, now you do alter and DDL triggers underneath, captures alter and send it off to subscribers. Hate it when we add features and we have to do the common maintenance in a different way.
Greg Low: One of the things I love about mirroring, transparent to the developers using the system that this is happening. Replication has been far from transparent to the developers.
Don Vilen: We have them with database mirroring. If you’re using three-part names within a database, that database might fail over to the mirror when the other database that was co-located isn’t there anymore. There are some things to be aware of there.
Greg Low: Few transparent things with con-activity.
Don Vilen: With con-activity now there’s some stuff with security. In the past if you didn’t have a certificate on a machine then we wouldn’t do encryption, but now we’ll do self-signed SSL certificate in order to do an encryption. Always encrypt the login packet now, so even with SQL Server authentication where you supply password when you connect, you don’t have to worry about that being intercepted and being readable. Have endpoints within SQL Server. SQL Server has listened on things, no formal way to say I want it to, I don’t want it to, that kind of thing. Given it a name, you can create these endpoints, say only certain people can use it, only used for database mirroring, certain protocol.
Greg Low: Even for TCP endpoints seem to be flexibility on how they can be configured. Making connection, force a specific protocol by putting that in the connection string. Kind of nice.
Don Vilen: In the past it would find a protocol you had set up from that client. Now you can say I want to connect over TCP.
Greg Low: Rather than just a preference. Last is dedicated administrative connection.
Don Vilen: Off by default like new features, but you can use the surface area communication manager to turn on the dedicated admin. Allows you to take in the server even when the server appears to be pegged and other connections can’t get in. Allocate space and time so you can connect. Dash-A or SQL command. If you’re admin you come through here, subset of operations an admin can do, looking at DMVs, find out what’s going on, kill run-away process. Don’t have to restart server and kill everyone to kill runaway process.
Greg Low: Last one, general network error, mentioned it disappearing.
Don Vilen: Don’t know with general network, useful information.
Greg Low: Mentioned in a session this morning that the one that used to throw me (starting to work for ADO.net), couldn’t get to the bottom of general network era. The one that had me thrown in ADO.net version one, if you set command timeout to zero, meant to mean unlimited, actually meant zero. Took me ages to realize sometimes command would get executed, sometimes it would not. What would be thrown would be general network error, took me so long to realize that was what was causing it. Won’t be sad to see general network error disappearing.
Don Vilen: We have errors we try to improve. Fine line when you’re concerned about security. Sometimes you’re giving the wrong person the…. It’s an information disclosure of a sort… You tell them you can connect to this database using this instead. They weren’t even supposed to know a certain database existed. Goes back to some of the stuff with sys-objects table in SQL Server 2005, you can only see the objects you have some permission to. Let you know there’s this other thing going on.
Greg Low: Even if you can’t access it, the knowledge it exists is more knowledge than you should have.
Don Vilen: Like we have a table called merger with XYZ company.
Greg Low: Thanks very much Don. What else is coming up, where can people see you?
Don Vilen: Be in Melbourne the next few days, then off to Kuala Lumpur for TechEd there, then TechEd SEA (Southeast Asia), then back in the states the ninth of September, something…
Greg Low: So people in Kuala Lumpur should come and see your sessions. Noticed in reviews today you had some of the highest-rated sessions in TechEd. Wonderful to see. People who get along to Kuala Lumpur should see you.
Don Vilen: If you’re coming to Barcelona for TechEd Europe I’ll be there as well as well as for IT forum, second week of TechEd there.
Greg Low: Thank you again, great to have had you on the show.
Don Vilen: Thanks, Greg.