Sorry, guys, not that kind of proposition

What could be more fun than getting second-degree burns at the waterpark, you ask? Volunteering on the PASS Performance SIG! That’s right, we’re looking for a few good women and men to join our ranks as content contributors. Specifically, we’re looking for people to write articles and/or host LiveMeeting events on performance-related topics. Not a performance expert? This can be a great way for you to learn more.

In case I scared you off in my opening paragraph, let me assure you that it really does not take that much time to be a volunteer. Just 3-4 hours a month can be a huge help. We’re also looking for contributors of all experience levels, so if you’re only comfortable writing intro-level articles, that’s definitely okay.

Oh, and while I’m begging for volunteers, we’re still looking for speakers for the SQL Saturday in East Iowa.

If you’re interested in either, then please send me an e-mail at michelle at sqlfool dot com for more information.

They say a picture is worth a thousand words so with that in mind I give you this.

Atari 800s have C drives?

Okay, while the instructions do not go on to state that this product needs to be installed on an Atari 800, it does state numerous times that this is to be installed on MSDE.  (Remember that from all those years ago?)  The install scripts, when reviewed also create a login with a matching password (neither strong by any means), creates a database with a data file of 8Mb and corresponding log file of 23Mb (really, 23?  Did someone pull a number out of their <REDACTED>?  Anyone like to guess at what autogrowth is set at for these two files?  Additionally, db_datareader role rights are granted to the SQL user in the database, but yet the script goes on to explicitly grant SELECT rights to about 80% of the tables created.  Is there such a thing as half-<REDACTED> overkill?  Apparently there is.

I do commend this company for doing a few things right.  They do create indexes, constraints, and keys.  I can not tell you how well, but they do create them which is better than some installs I’ve been involved with in the past. 

Being a somewhat-decent DBA what did I do to rectify the shortcomings?  What makes you think I did anything?  I connected as SA and ran the damn script.  Heck, if things aren’t right they’ll take care of themselves right?  DB too small?  It will autogrow 1Mb at a time.  With a login and password that are the same you only need to remember one simple word right?  Right.  No, what I did was manually create the database after contacting the vendor to get a proper estimate of expected growth over the next three years based upon our expected usage.  I also created a role in the database, commented-out the grant of db_datareader rights, and granted explicit SELECT rights on the objects to the new role.  In the meantime I am waiting for the vendor to tell me if they support using Windows-based security or if I need to rely on a SQL Server login/password.  Either way I will assign the proper login role membership to the one I just created in the database.  Oh, and forget MSDE.  We’re running SQL Server in this shop baby!  Don’t try to tell me they’re the same thing.  Most-importantly I did not install the database files or transaction logs on the C: drive.

So, my first Vendor Wall of Shame Inductee is <REDACTED>.  I’ve seen their other products as well.  I sure hope they have a decent amount of space on their bookcase, because more awards are to come I’m sure.

If you are familiar with other programming languages, then you are probably aware of loop optimization techniques. You should try to put all operations outside of the loop if they don’t need to change within the loop. This reduces the amount of unnecessary repetitive work. SQL Server optimizer doesn’t automatically recognize such inefficiencies and clean the code for you (compilers of some other languages do). You have to write efficient loops yourself as in the following example.

These scripts print a table of square roots for all numbers from 1 to 100. (My apologies but Wordpress won’t seem to keep my indents in place in the code.) Notice the boldfaced code below:

-- Loop with code inside = inefficient

SET NOCOUNT ON
DECLARE @message VARCHAR(25),  @counter SMALLINT
SELECT  @counter = 0
WHILE @counter < 100
BEGIN
SET @counter = @counter + 1
SET @message = REPLICATE( '-', 25 )
PRINT  @message
SET @message = str( @counter, 10 ) + str( SQRT( CONVERT( FLOAT, @counter ) ), 10, 4 )
PRINT  @message
END

– Elapsed time: 376 ms

Compare the above Transact-SQL script to the one below, where the boldfaced code is moved outside of the loop:

-- Loop with code outside = efficient

SET NOCOUNT ON
DECLARE @separator VARCHAR(25), @message   VARCHAR(25), @counter   SMALLINT
SET  @counter = 0, @separator = REPLICATE( '-', 25 )
WHILE @counter < 100
BEGIN
SET @counter = @counter + 1
PRINT  @separator
SET @message = Str( @counter, 10 ) + Str( SQRT( CONVERT( FLOAT, @counter ) ), 10, 4 )
PRINT  @message
END

– Elapsed time: 36 ms

The second script executes the REPLICATE( ‘-‘, 25 ) function only once, compared to 100 times in the first script. Results produced by both scripts are identical:

-------------------------
1    1.0000
-------------------------
2    1.4142
-------------------------
3    1.7321
-------------------------
4    2.0000
. . .
. . .
. . .
-------------------------
99    9.9499
-------------------------
100   10.0000

Of course, there are a million and one ways to perform any such algorithm. But I’m still surprised that otherwise experienced and competent database programmers are still embedding very stable elements of their code inside of extensive looping operations rather than outside of them. Thoughts?

Cheers,

-Kev

Due to circumstances outside of anyone’s control our speaker, Andy Leonard, was very late getting signed in to LiveMeeting. What do you do when you have a room full of SQL Server people? You talk shop!

Over the course of the evening we talked about how to find the date when data was last inserted into a table, how to cope with rapidly growing data, future presentations for the user group, future presentations within PASS and the local community, performance on virtual machines, and a few other things that I can’t remember off the top of my head.

Andy’s presentation was phenomenal. I thought I knew at least enough about SSIS to call my self competent. After last night’s presentation on Change Data Capture and Incremental Loading with SSIS, I know that I have a lot left to learn. The best thing about a great presenter and a great presentation is that 1)they make it look effortless and 2) you walk away having learned so much without realizing it. I know that I took away a great deal from the presentation. Andy graciously stuck around on LiveMeeting and answered audience questions. A big thank you to Andy for donating his evening to sharing his knowledge and experience. And an even bigger thank you to the Leonard family for letting us borrow Andy for the evening.

Going along with last night’s presentation topic, here are some SSIS tips.

It does not. The execution stats available through sys.dm_exec_query_stats are only retained while the plan for the query is in cache. As soon as the plan is removed from cache (for whatever reason), the query stats for that query will be discarded. There are also a couple of reasons for a query’s plan not going into the cache at all.

Let’s try a quick example. I’m going to use the AdventureWorks database (SQL 2008), because it’s convenient. I’m going to create 4 procedures, run them several times, along with a couple other commands and then compare what a trace shows and what a query of the plan cache shows.

Create Procedure GetLatestPriorWeeksTransactions (@WeekEnds DATETIME)
AS

SELECT p.Name, TransactionDate, TransactionType, Quantity, ActualCost
FROM Production.TransactionHistory th inner join Production.Product p on th.ProductID = p.ProductID
WHERE TransactionDate BETWEEN @WeekEnds AND DATEADD(ww,-1,@WeekEnds)

GO

CREATE Procedure ArchiveTransactionHistory (@ArchiveEndDate DATETIME)
AS
Insert Into Production.TransactionHistoryArchive (TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate)
SELECT TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, TransactionType, Quantity, ActualCost, ModifiedDate FROM Production.TransactionHistory WHERE TransactionDate < @ArchiveEndDate

DELETE FROM Production.TransactionHistory WHERE TransactionDate < @ArchiveEndDate
GO

CREATE PROCEDURE SalesToday (@ProductID int)
AS
SELECT p.name, sum(sod.lineTotal) AS TotalSales, SUM(OrderQty) AS TotalSold
FROM Sales.SalesOrderHeader soh
INNER JOIN Sales.SalesOrderDetail sod on soh.SalesOrderID = sod.SalesOrderID
INNER JOIN Production.Product p on sod.ProductID = p.ProductID
WHERE DATEADD(dd,dateDiff(dd,0,soh.OrderDate),0) = DATEADD(dd,dateDiff(dd,0,GETDATE()),0)
GO

CREATE PROCEDURE PersonSearch (
@Surname Varchar(50) = NULL,
@FirstName Varchar(50) = NULL
)
WITH RECOMPILE
AS

SELECT Firstname, middlename, LastName from Person.Person
where (LastName like @surname + '%' OR @Surname IS NULL)
AND (FirstName like @FirstName + '%' OR @FirstName IS NULL)
GO

I’m going to use the same trace definition that I detailed in the article that I wrote for Simple-Talk, and use the same technique as is in the article to load the trace up and get aggregated totals.

When I query the plan cache, this is what I get back from it.

And this is what I got from the trace (with the queries relating to the trace removed)

So why the discrepancy?

Let’s start with the entries returned in the query of the trace, not the procedure cache.

The ALTER PROCEDURE wouldn’t be visible in a query of the procedure cache, because DDL statements don’t have execution plans. There’s only one way to run an ALTER PROCEDURE, so there’s no need for a plan, hence there’s nothing to cache and nothing for the query stats to hook off.

According to the procedure cache, the PersonSearch procedure was never run, but the trace records 35 executions. There’s no entry for this procedure in the cache, because it’s marked WITH RECOMPILE. Since a new plan has to be compiled on each execution, the plan is never cached and hence it will never be visible in a query of the plan cache and there will never be saved execution statistics for it.

Now, how about the two with very different execution counts? In each of those cases, something happened to throw the procedure’s existing plan out of cache part way through the test. When the plan gets thrown from cache the query stats get discarded as well.

In the case of the SalesToday procedure, the procedure was altered part way through the test. Altering a procedure or any object that the procedure depends apon will invalidate the cached plan and cause it to be discarded

The other procedure, GetLatestPriorWeeksTransactions, had it’s plan invalidated due to a statistics update. The archiving of TransactionHistory changed enough rows in the table that the next time a query ran it triggered a statistics update. That statistics update would have invalidated any plans that used those statistics. In general, this doesn’t have to be stats update on a permanent table. If a procedure uses a temp table and adds rows to that temp table, there’s a good chance of getting statistics-based recompiles of statements within that procedure, thereby skewing the results of queries against the plan cache.

In conclusion, while the DMVs exposing the plan cache are very useful in seeing how queries are running, they cannot be seen as a complete replacement for SQL Trace because of the possibility of certain types of commands not getting cached at all, and the possibility of plans getting invalidated and dropped from cache for various reasons.

I’ve also received a couple of other requests for feature enhancements. I’m currently swamped at work and outside of work, but as soon as I get time, I will release the latest version. Realistically, it’ll probably be sometime after summer ends.

Thank you to everyone for their comments and suggestions!

For more about Instant File Initialization and other VLDB tips, check out our Very Large Databases wiki article.

Subscribing or Downloading the Podcast

If you have an MP3 player or a portable video player and you want to download our videos automatically, you can subscribe to our podcast feeds here:

• MP4 (Apple) Video Feed
• WMV (Microsoft) Video Feed
• MP3 Audio-Only Feed
• Zune One-Click Subscribe for Video
• Zune One-Click Subscribe for MP3

You can also download this video to watch it later:

• MP4 (Apple) Video Download
• WMV (Microsoft) Video Download
• MP3 Audio-Only Download

“Man, I don’t mean to sound harsh but..you’re an expert DBA? I’m guessing you wouldn’t survive one of my interviews. I only expect the candidates to get 50%. I’ve had them all too. PhDs, Masters in blah-blah-blah, Captain of the Patterns Team at Yale majoring in C#, writing joins since she was in Pampers. Uh huh.”

Answering this requires more than just a comment.

My Dream House Checklist

My Kind of Yard

I use RememberTheMilk to store a lot of stuff, including my list of things that I’m looking for in my dream house. I’m planning long term, baby! It includes things like:

• No ground-level windows – we don’t like people looking in or breaking in.  We like high windows on the ground floor (like 7′ up).  The other floors can have floor to ceiling windows, though.
• On the water, but no nearby vegetation – we hate bugs.  Ideally, there wouldn’t be a plant on the property.  I’m not kidding.
• Within walking distance of at least 2 restaurants – Erika and I really enjoy eating at restaurants.  It’s nice to have other people take care of everything once in a while.  Okay, often.
• Gadget nook in the entryway – I like having my electronic gear near the door so that I can grab it on the way out.  Ideally, I’d have an inset nook in the wall with electric outlets, and the whole thing would be concealed so that wires weren’t dangling all over the place.
• Lots of AC ventilation in the kitchen – we love a really cold house.  I’m talkin’ 65 degrees.  Erika loves to cook, but the kitchen always gets hot, because no house’s AC is ever designed to pump that much cold air into the kitchen while keeping the rest of the house tolerable.  Speaking of which….
• Very powerful but very quiet AC – I don’t want to hear the air conditioning kick on and off, but I want the house at 65, and no, the answer isn’t leaving the air running full blast 24/7.
• Silent garage door openers – I get up early in the morning and I like taking drives.  I hate it when the garage door opener vibrates in a way that you can hear it in the bedroom.  (Yes, there’s a lot of silent items on this list.)

I could go on and on – the checklist has over 20 items on it at the moment.  That doesn’t include my set of tear sheets with all of the interior design features and furnishings we’ve liked over the years, too, or my Delicious bookmarks tagged dreamhouse.

My Current Housing Checklist

My Kind of Price

Back in the real world, since I’m not making seven figures (yet), my housing search checklist is decidedly more utilitarian:

• Washer/dryer connections – I don’t want to go to a community laundry facility.
• Good phone/cable wiring – I need high speed internet access and old building wiring presents problems with that.
• Two parking spots – we’re looking at downtown one-bedroom lofts, and those don’t always come with two spots.
• Low crime rate – I don’t want to have to carry a gun when I walk the dog.

Notice the difference?  Champagne tastes, beer budget.  I can’t go asking about waterfront property with what I’m payin’.  Asking about waterfront property when I can’t afford it doesn’t make me look good – and in fact, my real estate agent is pretty quickly going to start rolling her eyes when I call.  “Here comes this bozo again, asking about waterfront property for fifty large.  What a jerk.”

How This Relates to Database Administrators

If money wasn’t an object, my interview question list would simply be:

• Is your name Paul Randal?

However, money is always an object.  Even when you think money isn’t an object, you have to ask yourself if you would hire one super-expert-senior DBA, or hire two solid but not super-expert DBAs.  My DBA interview questions aren’t designed to separate Paul Randal from the rest – they’re designed for companies who need to find somebody reliable without spending a fortune.

One of my favorite sayings is that there’s two kinds of questions: the ones designed to find out how much the other person knows, and the ones designed to show off how much you know.  Showing off in interviews doesn’t impress the candidate – and in fact, it does the exact opposite.  Humiliating a candidate makes them bitter about your company before you even make them an offer.  If your candidates rarely achieve 50%, then you’ve got a disconnect between your tastes and your budget.

Start asking questions that your candidates might be able to answer.

Otherwise, you’re just showing them you’ve got a lot of DBA-ness.

The reason for this (as it was explained to me) is that on a high-transaction server, there is the possibility that the file I/O associated with the creation and destruction of temporary objects may cause blocking on parallel CPU operations. For example, CPU0 may run a stored procedure that uses temporary tables; CPU1 may also run that same procedure simultaneously.  If there is a single tempdb data file, the I/O associated with the creation of the local temp object with the stored procedure being executed on CPU0 may block the creation of the files associated with CPU1, even though the tables themselves are locally scoped. 

Sizing the files equally also assists the optimizer, as space availability per file is used in considering where to store the local objects.   If you have 1 file that is extremely larger than the second file, most of your objects will be created in the larger file, which could also contribute to blocking concerns.  In short:

• Create the same number of tempdb data files as there are physical processors on the server (up to 8), and
• Make sure that the data files are equivalently sized.

NOTE: Immediately after posting this, I read the following from Kevin Kline.  Please note that I am not talking about multiple log files, but rather multiple data files.  http://sqlblog.com/blogs/kevin_kline/archive/2009/06/23/old-performance-tuning-recommendations-die-hard.aspx

NOTE2: @kbriankelley tweeted me shortly after this post, and recommended the following post: http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-12-Should-you-create-multiple-files-for-a-user-DB-on-a-multi-core-box.aspx

Apparently, there may be some disagreement about the requirement for the 1-to-1 ratio of data files to cores.   Paul Randall indicates that it may be a 1-4 or 1-2 ratio. I don’t know; I had a Microsoftie in the office telling our DBA team that we needed at least 8-10 data files for our 16 core box (and he did specify the 1-1 ratio) .  As a database developer, I don’t think about file I/O enough to argue with him.  I do think that this is a tip worth investigating and testing, however, which is the reason I posted it.