Log Buffer #105, A Carnival of the Vanities for DBAs
Jul 11, 2008 / By Sheeri Cabral
This week, database blogs seemed to talk about conforming to stereotypes as well as breaking them.
Fulfilling the stereotype of Microsoft software being unsecure, Microsoft released a very important SQL Server update that Aaron Bertrand notes patches “four elevation of privilege vulnerabilities.” That’s right, not one, not two, but four!!! At least there is a patch now……go forth and patch! Usually it is MySQL that throws ambiguous warnings or errors which are not an accurate representation of the actual error, but Bertrand also notes that there is A Little Management Studio Oops.
Contrary to stereotype, Microsoft is giving away content with NO DRM! Kalen Delaney asks, Did You Know the History of SQL Server? and shares a link to a chapter from a book on SQL Server 2000 in the Inside SQL Server series.
The PSS SQL Escalation Services team has fought many times about SQL Server I/O Bottleneck, I don’t have one, YES YOU DO! The team gives some reminders about how to interpet averages properly.
Allen White asks about Tools for the Reluctant DBA — that is, a programmer or administrator so good at databases that they were promoted to DBA, but may not actually want the job. Check out the comments and add your own.
Tibor Karaszi shares his stored procedure to find physical index details. Now you can use one stored procedure does what a stored procedure plus 3-4 tables ordinarily does.
But wait! There are some more updates!
Another tool is Steve Fibich’s new stored procedure, which he shows us in space_used_sp vs. sp_spaceUsed. In his own words, “Sp_spaceUsed is great procedure for giving you overall storage utilization information for a database or database objects (table, index view, or queue)….I wanted to see how this was splitting my data as far as size and row counts was going and how it was being disturbed across the file system.”
Bob Beauchemin recommends Using Power Shell To Program SQL Server’s Policies And Data Collection. Well, it was not in direct response to Allen White’s post, but it is a good tool. He also recommends Programming SQL Registration In The SQL Server Power Shell Provider.
If you are a reluctant Oracle DBA, there are also plenty of tools for you. Tanel Poder continues his Advanced Oracle Troubleshooting Guide series with Sampling latch holder statistics using LatchProf. The Oracle Magician reviews a new feature of Oracle 11g, the Oracle 11g Monitoring and Goodbye Alert Log.
Roland Bouman has a wonderful tool for MySQL, a UDF that helps with custom Writing to MySQL Error Log. He also overcomes the lack of functionality stereotype in MySQL by going in depth on a series of Calculating Percentiles and Quantiles (this is a page with a few blog posts on it, so read them all!).
David Aldridge suggests using simplification instead of complicated tools in Has Everyone Forgotten ‘Keep It Simple, Stupid’? Along those same lines, Mark Atwood reminds us that often a query or schema optimization is all that is needed in This is the kind of stuff that MySQL PS Performance Optimization gigs do All The Time.
Neelakanth Nadgir receives suggestions for tools to use to find out how much time queries are using, with respect to both the server and the storage engine. These suggestions are in comments to his question, “Storage Engine or MySQL Server? Where has the time gone?”
Robert Treat treats us to Index pruning techniques.
Parvesh Garg released a MySQL command line pager & mysmartpager tool, to filter MySQL’s command line output with regular expressions. Ronald Bradford shows how to make Deleting From Archive Tables happen.
Baron Schwartz shows how to properly use a MySQL tool in How to Load Large Files Safely Into InnoDB with LOAD DATA INFILE.
In Justifying SQL Cop-out, Steve Dassin calls Joe Celko on Celko’s “cop-out” that “a schema where there are multiple paths between tables due to cascading updates” cannot be done in SQL. Dassin makes the point that the problem is, in fact, NP complete as Celko points out, but that does not mean SQL should just forget about the issue and disallow it. Dassin then makes an analogy using some rather offensive stereotypes, notes that Dataphor makes attempts to handle this issue instead of giving errors, and ends with the good point that “Users should understand that because sql doesn’t try to solve complex modeling issues and doesn’t attempt to resolve views/expressions with multiple tables that this doesn’t mean these things can’t be solved.”
It poses an interesting conundrum though: Given a problem, would you rather be disallowed from doing it a certain way, or have it be done in such a way that many, but not all, cases are handled? I have found that in the latter scenario, DBAs end up frustrated and upset because they assume that all cases will be handled and submit bug reports because they’re in the minority for which the case is not allowed, or worse, works in an unexpected manner. I have also found that in the former scenario, the resolution is such that each case is solved clearly and properly — it is just annoying for all cases, instead of broken or impossible for a few. What do you think?
(In other “blog posts that make you think”, Peter Robson guest writes a column on Doug’s Oracle Blog about Server Technology Conundrums. Take some time reading and thinking about this article; there are some very interesting conundrums posed.)
Back at Dataphor, Steve Dassin points out another feature — the opposite constraint. This is a check constraint type (not available in SQL Server) that disallows entries across multiple columns where the combination of entries is already present. SQL Server check constraints require that the permutation (order) of the entries is the same. This means that given columns (a,b) and values (1,2), an insert of (2,1) can be disallowed in Dataphor. Very nice!
Ken Downs points out a usual stereotypes of databases — there is no way to get around performance issues in Database Performance: Pay Me Now or Pay Me Later. It is as if database systems have to follow the first law of thermodynamics as applied to performance instead of heat or energy.
Aaryadba runs into a strange Effect of OS Terminal Setting ‘STTY’ on Oracle Database
Staying with their reputation of asking the community for input, Dave Stokes of the MySQL Certification Team asks folks to Volunteer to develop with the next generation of DBA exams. Kim, a Google Summer of Code student assigned to MySQL, asks MySQL Obfuscator, what features do you want to see? The MySQL Obfuscator is his summer project, a tool for folks to submit bug reports easily, using their own schema. The Obfuscator will, well, obfuscate sensitive details, so that bug reporters do not need to spend a lot of time editing their queries and schema.
MySQL broke the stereotype of open source companies not being viable because there are no trade secrets, which Matt Asay discusses in MySQL’s Marten Mickos: No one can imitate our culture.
I hope that memcached will become the default (“stereotypical”) caching application, so I am excited about Frank Mash‘s Memcached for MySQL Advanced Use Cases webinar — he links to both the recording and the slides, so anyone can learn about using it!