I am at the PASS conference in Barcelona, Spain this week. It is not as big an event as the conference held in the USA each fall, but there is some very good content here, as well as some top-notch resources. Today I learned that if you rebuild your master database in SS2K5 you also have to reapply all patches. That nugget alone could be worth the price of admission.
Today I attended the pre-conference seminar with the PSS team, which includes Ken Henderson, Bob Ward, and Bart Duncan. Ken dived right into the new I/O features of SS2K5, before my cafe con leche had a chance to kick in. Bob sandwiched his talks around lunchtime, and Bart brought us home with a discussion on query plans and optimization.
There were so many jewels tossed around the room today it is hard to keep track of them all. Luckily, I took some notes, and this blog will help me to point out some of the ones that made it to paper. I will try to be brief, but there is so much to talk about.
Ken pointed out some of the new I/O enhancements with SS2K5, as well as reviewing some definitions (latch versus lock). Some of the new enhancements that I think are a great benefit are:
- Time of Last Access (lazywriter will return data pages to the free list based upon the time they were last accessed)
- Copy-on-Write (SS2K5 will wait to make a copy of a data page until immediately before it is modified, useful for snapshots)
- Stalled I/O Detection (detection of I/O’s that are running for 15 seconds, and will report on ones that have been running for five minutes)
- Read Retry (SS2K5 will retry a failed read, up to four times)
- Stale Read Detection (old versions of data pages are detected when read)
There is plenty more to be excited about, if you like this kind of stuff. Mostly this is the “free” stuff that we will get when we upgrade to the new platform, and it will be invisible to mostly everyone. But for us administrators, it is useful to be aware of how the engine is working, should there ever come a time we need to figure out what is happening under the hood.
Bob had two talks, and I already mentioned one point he made to us regarding the rebuilding of the master database, should that ever be necessary. Essentially Bob’s talks centered around things we can do to help ourselves before placing the call to PSS. There was a lot of meat in his talks, and a few of the items I took from them are as follows:
- There are some system database files (mssqlsystemresource.mdf) that must be located in the same directory as the master database file. These files cannot be accessed like a typical database, and cannot be backed up by command. In order to back them up, they must be copied when they are not open, which means shutting down the sql service first. Fortunately, this only has to be done after any installation or patch, and they are avilable on the install media, just in case.
- There is a database mode called ‘Emergency’ (also true in SS2K, who knew?) This mode will allow a DBA to repair a database, but there will be some data loss as the log must be rebuilt, thus there could be some logical inconsistencies.
- New commands available for BACKUP and RESTORE, as well as some additional (and informative) error log messages.
- If we are not running Win2k3 SP1, and we are using AWE, we need to apply the PAE fix (KB 838765). Someone should make a note of this.
- The dedicated admin connection for SS2K5 is really cool.
- I can now read a stack dump. OK, I made that up. But I can decipher certain parts of a stack dump in order to help determine what might have been the root cause. I can also generate a stack dump for a specific error code (say, 208, which is an object not found error).
Lastly, Bart took the mic and started in on the internals of the query optimizer. Quite simply, I learned that sql server can be quite advanced in some areas, and not so advanced in others. For example, I learned that the optimizer does not like to play with the following in a where clause:
WHERE col / 1000 = 50
For whatever reason, this is what is called non-SARG-able. I believe that means non-searchable, as SARG is short for a “search argument”. But, the following statement is SARG-able:
WHERE col = 50000
Go figure. MS SQL Server can do lots of stuff, but not algebra. But the most fascinating non-SARG-able thing presented to me was the use of columns in functions. So, this is non-SARG-able:
DATEDIFF(m, col, getdate())
But this is:
DATEDIFF(m, -12, getdate())
What??? I use this stuff all the time! And just now I am finding out that this would force an index scan versus an index seek? Ugh. Of course, there are also some exceptions. ISNULL is a good one to look at. If a column is non-NULLable, then the optimizer takes the following:
ISNULL(col,0)
and throws away the ISNULL function, leaving just the column. But if the column is NULLable, then the statement is non-SARG-able. Who designs such things? How can you be smart enough to toss out an ISNULL, but not smart enough to multiply 1000*50?
And don’t get me started on user-defined functions. These things are a nightmare. Of course, the only exception is a function that returns a single select statement, which is essentially a parameterized view. Those will get look at by the optimizer, but any other UDF and the optimizer has no chance at getting stats and takes a best guess.
Now, do you want a system that cannot do algebra to take a best guess with that UDF you spent a week putting together?
All in all, today was a great day. Good topics, good presentation, and I am in Spain.
Adios amigos.
