This is the 170th edition of Log Buffer, the weekly review of database blogs. Welcome. Let’s kick off this week with a double-helping of . . .
SQL Server
There are lots of good technical posts this week. The SSIS Junkie has some observations and a straw poll on sort transform arbitration. He writes, “This post was prompted by a thread on the MSDN SSIS forum today where the poster was asking how he could replicate the behaviour of SSIS’s Sort transform using T-SQL, specifically he wanted to know how the Sort transform chooses what data to pass through when the ‘Remove Duplicates’ option is checked.”
Another poll, courtesy of Tibor Karaszi: do you perform log backup for the model database?
Eric Johnson has a lesson in looping through rows in a table in SSIS 2008, which begins, “When writing code against a SQL Server, as we usually are doing in SSIS Packages, you often need to iterate over all the rows in a table. This can be done using an SSIS Foreach Loop Container, but the how is not obvious.”
Simple-Talk’s Tony Davis wonders, Do Scalar UDFs give SQL Server a Bad Name? “Many developers seem to regard SQL Server as if it were a science-fiction alien planet where unsuspecting crew-members in blue jumpers occasionally die horribly; everything is suddenly unsafe, and potentially malicious: nothing really works properly and so any serious code should be kept well away from it. Is this developer ignorance, or is their fear justified?”
From Merrill Aldrich comes a trick question — part quattro. Spoiler: TPH is an evil trap. As a commenter says: “Very interesting, I’ve never heard this vehement an argument against TPH before.”
Thinking outside the box lets us in on how to tell if you are running on a virtual environment, with a handy little bit of code.
Kalen Delaney elucidates UPDATE Locks, ” . . . a hybrid of SHARED and EXCLUSIVE locks. [Contrary] to what you might think, UPDATE locks are not just acquired for UPDATE operations.”
Stephen Forte shows how SQL Server R2 Does SQL Azure.
Now that PASS 2009 in Seattle, Washington has passed, it’s time to fondly look back on it. Kendal Van Dyke shares his experiences and some photos in Looking Back – PASS Summit 2009 Day 4.
The Rambling DBA, Jonathan Kehayias does so with the benefits of attending PASS realized: ” . . . as a testament to the value of attending this conference the very first session I attended, diagnosed and provided information for a problem that has existed in one of my servers for many weeks but was impossible to diagnose unless you knew what you were looking at.”
Thomas LaRock, SQL Rockstar concurs: “If you want to grow your skills, then you need to connect, learn, and share with others. And there is no better place to do that than at PASS.”
Not that PASS was the only game in town. Just down the coast a bit, Baron Schwartz gives his recap of Portland OpenSQL Camp 2009.
Selena Marie Deckelmann reports that OpenSQLCamp was awesome!
PostgreSQL
Pavel Stehule has some news of a longtime plpgsql misfeature removed. He writes, “plpgsql is good language – simple, robust with good error diagnostic. But it had one bizarre behave. plpgsql connects two worlds – procedural ADA like code and SQL statements. Usually there are not problems. But there are one exception – collision of identifiers.”
In the latest in his Waiting for 8.5 series, Hubert Lubacziewski introduces and tests something new: TRIGGERS on columns.
MySQL
On code.openark.org, Shlomi Noach is surprised by questions or queries. He writes, “I was used to checking for the ‘questions’ global status variables . . . So, for example, I could run com_select/questions to learn the SELECT ratio out of all queries. . . . Apparently, as of 5.0.72-5.0.76 & 5.1.31 this has changed. A new status variable was introduced, called ‘queries’.” What’s the difference? Is this good or bad? Shlomi and his readers kick it around.
Venu Anuganti also was surprised, in his case by InnoDB Tablespace Corruption: “When . . . InnoDB crashes, it automatically recovers during the next start by rolling back/forward based on what was pending and un-flushed/un-committed changes at the time of crash. . . . [On] one of the servers; we ran out of disk space . . . on data directory . . . and server was running for few hours in this mode . . . [It] became un-available and not responding after a while. Only option left was to kill the server process and its PID along with cleaning the stuff to get the space back. After I (re)started the server . . . the tablespace is corrupted.”
From Arnold Daniels comes a version of versioning MySQL data, which Arnold introduces thus: ” . . . You’re probably using a versioning control system . . . to safeguard your data. Advantages of using a VCS are that you can walk to the individual changes for a document, see who made each change and revert back to specific revision if needed. These are features which would also be nice for data stored in a database. With the use of triggers we can implement versioning for data stored in a MySQL db.” Example code follows.
On the MySQL Perfomance Blog, Morgan Tocker qualifies his earlier piece on why you don’t want to shard: “What I didn’t mention was that if you’ve established that you will need to eventually shard, is it better to just get it out of the way early? My answer is almost always no. That is to say I disagree with a statement I’ve been hearing recently; ‘shard early, shard often’”
Morgan also mentions that interviews for InfiniDB and TokuDB are next: “I’d like to announce that Robert Dempsey (InfiniDB storage engine) and Bradley C. Kuszmaul (TokuDB storage engine) have also accepted an interview. If you have any questions about either storage engine, please post them here by Friday 20th November.”
Here’s a new blog to watch, particularly if you’re new to MySQL or Drizzle: Kent Bozlinski’s Learning Drizzle. Kent says, “I’m not really scared of rain after living in Seattle for seven years. I am a little scared of sticking my neck out and writing about something which (for the moment) I know almost nothing about.” The post is Drizzle is Scary (A Little).
Oracle
Maybe you’ve already heard about the fabulous unpopularity of the new My Oracle Support. Daniel Fink comes back with some data and commentary on just that, with his My Oracle Support Survey Results.
Richard Foote asks, An index only performs how much work???, the result of looking into exactly why index rebuilds can improve performance so significantly.
Kerry Osborne gives a lesson on fixing bad index hints in SQL Profiles (automatically). He says, “With 10g and 11g, it appears the goal [or Outlines] has swung away from the “locking” concept and towards allowing the optimizer more flexibility. . . . I must say that I find this decision to be irritating at best. . . . One of the main offenders in this regard is the use of a new format available for index hints as of 10g.”
Inside the Oracle Optimizer covers similar turf in answering to the question, What should I do with old hints in my workload?, or more specifically, “When moving from 10g to 11g, should hints in existing SQL be removed?”
Tom Kyte wants your opinions on comparative window functions: ” . . . they could be getting better in the near future. . . . analytics [could be allowed] to access the current row value to be compared against any other row value in a defined window. . . . I’ve already supplied them with my feedback (which started with “this is an awesome idea”) – and you can too – by posting it here. They’ll be checking back to see what you say.”
If you like staying on top of fresh things, perhaps John Piwowar’s method of retrieving Oracle patches with wget would also appeal to you.
And that is all for now. If you think I’ve missed a worthwhile DB blog from this week, please mention it in a comment.
Log Buffer will be back in a week’s time. See you then!
No comments