A discussion on SQL Server
The September meeting of the SA SQL Server usergroup will be on the 15th of September. Venue and time are the same as always, 18h30 at the Microsoft offices
This month, Theo Potgieter will be presenting a session on lessons learn in deploying, maintaining, optimising and scaling a merge replication architecture
Hope to see you all there. Please let me know if you’re coming, I need numbers for catering purposes
All of the presentations from TechEd Africa are now available for download on the TechEd site. Note that these are just the slide decks, not recorded sessions. Any demos that the presenter did are not available.
I’ve uploaded the demos for my two sessions and they’re available here: TechEd 2009 demos
From all indications, the first 24 hours of PASS was a resounding success. Hundreds of attendees for each session, minimal technical glitches. All in all, it went very well.
The first session I attended (well, part thereof) was Greg’s discussion on Spatial data. I would really have loved to see the entire thing, what I did see of it was exceptional, but I had to set up for my own session, and it was very difficult to listen to both Rick and Jacob going over last minute details and listen to Greg discussing geometry and geography. I’ll be sure to catch the entire thing once the sessions are available.
My session on indexes went quite well, with a good number of people attending. However it appears from some of the things I’ve read today that I totally messed up the explanation of order of columns in an index and confused the issue more than clarifying it. I think this presentation needs to go back to the drawing board. It works well enough with a small group of people in an interactive format, but not as a straight-up presentation with larger groups. Maybe some more diagrams would help, or more examples. I need to give some thought into what would work best.
Grant’s session on Performance Tuning was excellent, had me laughing at regular intervals, which was bad, I was watching it on my laptop at a local coffee shop. Other patrons must have thought I was crazy (and maybe they’re right). Grant got an infection of the demo-gremlins, but someone had to so that’s hardly a big problem.
Or “It’s 10pm, do you know where your log records are?”
Something that I’ve started to see recently is the idea that Backup Log … With Truncate Only, which was deprecated in SQL 2005 and gone in SQL 2008 can be simulated by backing the log up to the NUL device (backup log … to disk = ‘Nul’). Is that true? If so, is that a good idea?
To answer those questions, first we need to explore what the two statements do.
When this statement is run against a database in full or bulk-logged recovery, SQL discards the inactive log records for that database. From that point until a full database backup is done, the database is in pseudo-simple recovery, the inactive log records get discarded when a checkpoint runs and any attempt to back the log up results in an error message.
Msg 4214, Level 16, State 1, Line 1
BACKUP LOG cannot be performed because there is no current database backup.
So once the log has been truncated it’s exceedingly clear that the log chain is broken.
The second type of corruption I want to look at is that of missing references in sys.sql_dependencies. This typically appears on SQL 2005 in a database upgraded from SQL 2000.
On SQL 2000 it was possible to make direct updates to the system tables, and, in my opinion, if was done too often without the DBAs realising the long-term consequences.
As an interesting aside, early in 2005 I attended a series of pre-launch courses on SQL 2005. Among the other attendees was a senior DBA from one of our large banks. When he heard that 2005 hid the system tables and that it was no longer possible to update them, he went on a 10 minute rant about how MS was making his life impossible and removing essential features, etc, etc. It turned out that he did direct updates to drop users, drop logins, link logins to users, drop columns from tables and several other things. He absolutely would not accept that there were other ways to do those tasks and that modifying the system tables was risky. I’d hate to see the state of that database…
Back to the point…
There have been a couple odd types of corruption that I’ve been seeing on the forums. I want to take the time to go into some detail, because the fix is possibly not obvious. The first one that I want to look at is corruption of a stat blob.
These are specifically SQL 2000 errors, I don’t know what the equivalent errors in SQL 2005 look like, if there are equivalent errors.
The errors that indicate corruption in the stats blob look like this.
Server: Msg 8929, Level 16, State 1, Line 4 Object ID 2:
Errors found in text ID 153175064576 owned by data record identified by RID = (1:526:13) id = 238623893 and indid = 1.
Server: Msg 8929, Level 16, State 1, Line 4 Object ID 2:
Errors found in text ID 153175195648 owned by data record identified by RID = (1:526:15) id = 50815243 and indid = 3.
The things to note are the Object ID, 2 being sysindexes in SQL 2000 and the reference to an indid in the section that identifies a record.
In SQL 2000, the statistics for an index (or indeed for column statistics) was stored in an image column in sysindexes called statblob (ref). Each index (and statistic) has a row in sysindexes and keeps the statisics data in that image column. This means that the statblob is just as susceptible to corruption as any other LOB column. Unfortunately it’s not as easily fixed. In fact, since it’s a corruption in the system tables checkDB will not repair it
All well and good, so how do we fix these?
This one’s come up a few times recently, so I’ll take a look at it.
The EXISTS and IN clauses at first glance look fairly similar. They both use a subquery to evaluate rows, but they do it in a slightly different way
IN does a direct match between the column specified before the IN keyword and the values returned by the subquery. When using IN there can only be a single column specified in the select clause of the subquery
Let’s have a look at a quick example
DECLARE @SomeTable (IntCol int) Insert into @SomeTable (IntCol) Values (1) Insert into @SomeTable (IntCol) Values (2) Insert into @SomeTable (IntCol) Values (3) Insert into @SomeTable (IntCol) Values (4) Insert into @SomeTable (IntCol) Values (5) SELECT * FROM BigTable WHERE SomeColumn IN (Select IntCol FROM @SomeTable)
So this query returns all the rows from BigTable where SomeColumn has any of the values returned by the subquery, that is 1,2,3,4 or 5
But what if there were duplicate values returned by the subquery?
I’m going to be speaking at the PASS ’24 hours of PASS’ event. My slot’s at midday local time (10 AM GMT) and I’ll be talking about indexing – what makes indexes useful for SQL Server
You can see all the sessions and speakers, and register for the event at the official site.
If anyone’s wondering why I’m announcing this so late, I wanted to do some tests of bandwidth to make sure that the local connections could handle Live Meeting before publically committing to this.
The August meeting of the SA SQL Server usergroup will be on the 25th of August. Please note this is a week later than we would normally meet. The change in scheduling is due to some logistical problems (the conference rooms were booked). Venue and time are the same as always, 18h30 at the Microsoft offices
This month, James Pheiffer, who recently joined the Premier Field Engineers at Microsoft, will be presenting a session on Performance Monitoring using Perfmon and PSSDiag
Hope to see you all there. Please let me know if you’re coming, I need numbers for catering purposes
Chris Shaw‘s come up with yet another of his SQL quizes, this one’s a little more technical than the previous few have been. Tim Ford tagged me on this one.
1) Do you feel that you have a reliable SAN Solution? If so what is the secret?
Well, this one’s easy for me. What SAN?
I work from home and don’t directly admin databases any longer. Unlike Paul and Kimberly, I can’t afford a multi TB SAN in the basement to play with (not that I have a basement)
The most advanced storage I have here is a RAID 5 array on my fileserver/domain controller, and that came in very handy a couple months ago when one of the drives in there failed without warning.
2) Describe in laymen’s term what database mirroring is
Ok, do you know the kind of sleight-of-hand tricks that performing magicians do? Show a coin in one hand, make it disappear and then have it appear in the other hand? The trick is that there are two identical coins, but one one is ever visible to the audience.
Database mirroring’s like that. There are two identical databases (kept identical by technological wizadry), the users can only ever see one of them and when one disappears the other one appears, hopefully fast enough that the users don’t get frustrated waiting.
Let’s see… going to pass this one on to Paul Randal and Christopher Stobbs