Syndication

SQL in the Wild > Syndication

Multiple Execution Paths

Author: 15 September 2009 31 Comments

It’s not uncommon to find stored procedures that have multiple IF statements controlling the flow of execution within the procedure. Now this seems to be a fairly logical thing to do, but there can be a subtle performance problem with this, one that may be hard to identify.

Let’s have a look at a simple example (using AdventureWorks)

CREATE PROCEDURE MultipleExecPaths (
@TransactionType char(1) = NULL
)
AS

IF @TransactionType IS NULL
SELECT max(transactionDate) from Production.TransactionHistory
ELSE
SELECT max(transactionDate) from Production.TransactionHistory
WHERE TransactionType = @TransactionType

GO

Nice and simple. If the parameter is passed, get the latest date for that transaction type, if the parameter is not passed, ie is null, get the latest date over all transaction types. So what’s wrong with this?

The problem goes back to parameter sniffing. When the procedure is first executed the first time all queries in the procedure are parsed, bound and optimised. When the optimiser processes each statement to generate an execution plan it uses the values passed for the various parameters to estimate the number of rows affected. The number of rows that the optimiser thinks the queries will process affects the choice of operators for the plan. Operators that are optimal for small numbers of rows are not always optimal for large numbers of rows, and sometimes the difference can be astounding.

Let’s see how the example above plays out  to understand what’s happening here.

(more…)

Performance, SQL Server, Syndication

Thoughts on the 24 hours of PASS

Author: 4 September 2009 2 Comments

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.

(more…)

Community, SQL Server, Syndication

Backing up to NUL vs Backup with Truncate only

Author: 31 August 2009 19 Comments

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.

Backup Log With Truncate_Only

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.

(more…)

Admin, SQL Server, Syndication

Corruption in sys.dependencies

Author: 26 August 2009 12 Comments

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…

(more…)

Corruption, SQL Server, Syndication

Stats blob corruptions

Author: 24 August 2009 5 Comments

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?

(more…)

Corruption, SQL Server, Syndication

EXISTS vs IN

Author: 17 August 2009 11 Comments

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?

(more…)

SQL Server, Syndication, T-SQL

24 hours of PASS

Author: 14 August 2009 2 Comments

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.

Community, Syndication

Why the DMVs are not a replacement for SQL Trace

Author: 10 July 2009 1 Comment

With the introduction of the DMVs in SQL 2005, there’s now a wealth of information available on every aspect of SQL’s behaviour. It’s possible now to interrogate the SQL procedure cache to find out what queries have been running and how well or badly they are performing. So does all this mean that SQL Trace is now obsolete?

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.

(more…)

Admin, SQL Server, Syndication

Speaking Engagements

Author: 29 June 2009 2 Comments

It’s going to be a busy 6 months in terms of conferences and speaking (well, busy for me, I’m just getting used to the whole speaker thing)

TechEd Africa

TechEd Africa is running from the 2nd to the 5th of August in Durban. I’m presenting two sessions there, one on query hints and plan guides and one on evaluating your indexing strategies. I’ll also very likely be helping out in the community lounge and the Ask the Experts area.

PASS Community Summit

I’m presenting two sessions at the PASS Community Summit this year. A spotlight session on titled ‘Lies, damned lies and Statistics’ and a general session titled ‘Insight into Indexes’

The spotlight session will be covering column statistics, why SQL creates them and how, when they’re updated, the importance of accurate statistics, some of the problems that can result when they’re not accurate and some maintenance strategies.

The general session will be looking at what SQL can tell you about indexes, how they’re been used, what they’re been used for and what indexes SQL thinks it wants, and how reliable all that information is.

SQL Usergroup

I’ll be presenting at the October meeting of the SQL Server usergroup and will be doing a final dry-run of one of the presentations that I’ll be giving at PASS. I haven’t decided which one yet.

Community, Conferences, Syndication

Copyright policy

Author: 27 June 2009 0 Comments

I really hate to have to do this, but there have been a couple cases recently that have indicated the necessity of making things explicit.

All of the entries on this blog are copyright by Gail Shaw. The full content of any post may not be published elsewhere without prior permission from the copyright holder (that would be me). Excerpts from blog posts may be posted elsewhere providing they are short (max 25% of the article) and are attributed to me by name and with a link to this site.

The only site that currently has permission to repost full entries is SQLServerPedia.com

There is now a copyright entry, detailing the full copyright policy, in the sidebar of this blog. I’m also considering adding copyright info to the rss feeds (thought I’ll wait and see if that’s necessary)

If this entry appears in full anywhere other than sqlinthewild.co.za or SQLServerPedia.com, it is republished without permission and should be considered stolen content.

General, Syndication

Copyright 2018 Gail Shaw