Next Thursday, May 8, the New England SQL Server Users Group will have a special meeting, featuring Craig Freedman from the SQL Server development team. Craig is The Man when it comes to query optimizer internals, and wrote an incredibly detailed chapter on the topic for “Inside SQL Server 2005: Query Tuning and Optimization”.

At the meeting next week, Craig will discuss some of what he talked about in the chapter, including the basics of how the query processor works and what iterators are. He’ll cover the various operators you’ll commonly see in query plans, and describe how they actually work internally.

This should be a great meeting, and we expect it to be very well attended. In order to help us figure out food and drink, in addition to securing enough chairs for the meeting room, we need you to RSVP if you’re planning to attend. In order to RSVP, sign up for our mailing list. I will send out an e-mail next Tuesday, and you can RSVP by replying to it. Only attendees who RSVP will be eligible for our prize draw at the end of the night, so make sure to sign up for our list by Monday in order to guarantee that you don’t get left out.

We would like to thank Red Gate Software, who made a very generous donation to the group that allowed us to have this special meeting. Red Gate makes some of my favorite SQL Server tools and provides a huge amount of community support in the SQL Server and .NET space, and we hope that you will give their products a try.

The 95^th edition of Log Buffer, the weekly review of database blogs, has been published by Mark Schoonover on his Mark’s IT Blog.

We can look forward to LB#98 Jeff Smith’s Jeff’s SQL Server Blog on May 23^rd. There’s always plenty of room for more editors, so don’t waste another minute — send an email to me, the Log Buffer coordinator, and get started!

Without further ado, here is Mark Schoonover’s Log Buffer #95.

Log Buffer, the weekly review of database blogs, welcomes back for his record-breaking record-tying (Sheeri, are you reading?) third edition Ronald Bradford of Opinions, Expertise, Passion.

Why does Ronald write Log Buffer? Perhaps it’s because he knows that LB is and established and widely read feature, and hence likely to bring his own blog some new readers and improve its ranking. Or maybe he enjoys the fun and challenge of comprehending and presenting the entire DBA blog scene, not just the part that deals with his own favoured technologies. (Or maybe he just likes me? Ronald?)

Since Log Buffer is open to anyone, I encourage you also to join in. If you’d like to edit and publish an edition yourself, take a look at LB’s homepage, read the few guidelines, and then get in touch with me, the Log Buffer coordinator.

You can also contribute by emailing your favourite blog items to the editor.

And now, here’s Ronald Bradford’s Log Buffer #94.

I was just reviewing my calendar for the next several weeks and noticed that the Toronto SQLTeach conference is now only a few weeks away.  This conference includes quite a few interesting SQL Server-related sessions, on topics ranging from best practices, to performance, to some of the new SQL Server 2008 features. I fully expect this to be a great show.

I am doing two breakout sessions during the main conference:

• “SQL Server 2005: Authorization, Privilege, and Access Control”.  In this talk I cover SQL Server 2005’s enhancements around granting permissions via stored modules (i.e., stored procedures, views, functions)
• “Designing Highly Concurrent Database Applications”.  In this talk I get into the business requirements behind supporting concurrent processes, and the areas where SQL Server (and every other database product) falls short. I then go on to show how to solve the problems in the database programmatically.

I am also doing a full-day post-conference session on SQLCLR programming. This will be the first time that I will be presenting all of my SQLCLR material in a single day; should be fun. I will take attendees from the basics all the way through some advanced applications and techniques, so if you’re interested in becoming a SQLCLR expert I highly recommend attending.

The conference starts in just three weeks, but it is not too late to register.

Welcome to the 93^th edition of Log Buffer, the weekly review of database blogs.

Conference season is upon us, so it’s been a busy week. There was the MySQL Conference & Expo, so let’s look at that.

Arjen Lentz posts about Sunday’s community dinner, including the arrival of an unexpected guest. Two photos: one of Pythian’s Paul Vallée getting some Sun; the second from the pre-conference dinner.

Zack Urlocker has a couple pieces with both photos and links to video of the keynote addresses from Marten Mickos, Jonathan Schwartz, and Rich Green. From Wednesday, and from Thursday.

Congratulations are due to Baron Schwartz, Diego Medina, and Sheeri Cabral. Baron reports from the conference that the three of them were awarded the 2008 MySQL Community Awards, and his piece makes for a very apt acceptance speech. Here’s Kaj Arnö’s more official post on the Community Awards.

Baron also has good summaries of the conference course: day one, and day two.

Elsewhere on the MySQL scene, much ado about the immediate roadmap for the DBMS, as introduced at the conference. Jeremy Cole got things going, writing, MySQL to launch new features only in MySQL Enterprise: “MySQL will start offering some features . . . only in MySQL Enterprise. This represents a substantive change to their development model — previously they have been developing features in both MySQL Community and MySQL Enterprise. However, with a shift to offering some features only in MySQL Enterprise, this means a shift to development of those features occurring . . . only in MySQL Enterprise.” This post got a lot of comments, including from MySQL boss Marten Mickos.

(more…)

If you’ve read many of my blog posts, you know that I consider lack of procedure cache control to be a major SQL Server pain point. Badly written apps that use non-parameterized ad hoc queries can quickly flood SQL Server’s memory pools and bring the server to its knees.

SQL Server 2005 brought some relief in the form of the Forced Parameterization database option, and SP2 took things one step further with better throttling of the cache… but it’s still not enough. We want a knob!

The bad news: We’re not getting quite the knob I was hoping for.

The good news: SQL Server 2008 will include an sp_configure option called ”optimize for ad hoc workloads“.  This option will cause the procedure cache to only cache the parameterized stubs for ad hoc queries, rather than the full query with parameters.  This means that applications passing a large number of non-parameterized batches should see much lower procedure cache memory utilization and, therefore, better overall throughput.  I’m really looking forward to seeing this in action; this feature should be added with the next pre-release drop.

Remember, there is simply no substitute for properly designing your application’s data access layer, but hopefully this will help for those applications that simply can’t be changed…

How creative are you with manipulating your queries to produce more efficient plans? Try the following puzzle and e-mail your solution to me at [<my last name> @ pythian.com]. Make sure to include an explanation of why it works, as well as your mailing address. The best two solutions/explanations win a free copy of Expert SQL Server 2005 Development, a wonderful feeling of accomplishment, plus eternal fame and glory when I reveal your solutions here on the blog.

Run the following T-SQL to create two tables in TempDB:

USE TempDB
GO

CREATE TABLE b1 (blat1 CHAR(5) NOT NULL)
CREATE TABLE b2 (blat2 VARCHAR(200) NOT NULL)
GO

INSERT b1
SELECT LEFT(AddressLine1, 5) AS blat1
FROM AdventureWorks.Person.Address

INSERT b2
SELECT AddressLine1 AS blat2
FROM AdventureWorks.Person.Address
GO

Now consider the following query:

SELECT *
FROM b1
JOIN b2 ON
    b2.blat2 LIKE b1.blat1 + '%'

This query takes around three minutes to run on my notebook, and does over 1.8 million logical reads. Can you figure out a way to re-write it so that it performs better? No modification of the base tables or addition of any other objects is allowed (sorry, no indexed views!) — the challenge is to tune this by doing nothing more than re-writing the query.

Good luck! I’ll leave the contest open for submissions until May 1.

Welcome, readers, to the 92^nd Log Buffer, the weekly review of database blogs.

Brian “Krow” Aker started an interesting blog-thread with his post, The Death of Read Replication, the crux of which is that object caches, such as memcached, make the DBMS itself a little less central, particularly in “Web 2.0″ scenarios. “What does this mean? Less database servers. Bringing down your load means you push off the load to another tier. . . . Why do I need to go through MySQL at all… unless I just want it as a backup or for ad-hoc reporting?”

Ronald Bradford responds with an overview of the MySQL-plus-replication scene. Farhan Mashraqi concurred with Brian’s post, while Arjen also agrees, adding, “I’m not sure the new memory based MySQL storage engines coming out are so relevant, they might be fixing the wrong thing in the wrong place.”

Ronald (who, by the way, is on-deck for a his third Log Buffer on the 25^th) also surveys both the storage-engine stuff to be had at the MySQL Conference, and the prevalence lately of talk about Kickfire in MySQL blogs, something also mentioned by Peter Zaitsev on the MySQL Performance Blog.

Peter has another question on his mind: should you have your swap file enabled while running MySQL? He wants to hear your approach to this matter, having himself experienced variable results. Lots of responses already.

Here on the Pythian Group Blog, Paul Moen posted about a situation in which SHOW SLAVE STATUS lies.

Moving into Oracle stuff, our Alex Gorbachev also pointed out something that doesn’t quite work: the ASMCMD cp command in ASM 11g. He sure gives it a try, but finally concludes: “I couldn’t make the cp command work even a single time.” Except maybe on datafiles.

(more…)