Author Archive: Gail

SQL in the Wild > Articles by: Gail

South African SQL Server Usergroup – October meeting

Author: 8 October 2009 18 Comments

It’s our 1st birthday this month! There will be cake and prizes. I have three autographed SQL books and a backpack laptop bag to give away this month.

The meeting will be in the usual location – the Johannesburg Microsoft offices, 3012 William Nicol Drive, Bryanston. 18h30 on Tuesday the 20th October. I’ll be presenting te a session entitled “Lies, damned lies and Statistics”. This session will also be presented in 4 weeks  time at the PASS Summit in Seattle. Consider this a sneak preview.

Please let me know ASAP if you are coming. We need accurate attendance numbers or there won’t be enough cake. 🙂

Community

Review and goals for the rest of the year

Author: 4 October 2009 1 Comment

I should have posted this back in July. I put off writing it then, partially cause I was embarrassed about how little I got done.

Of the goals I set back in January, about the only ones I achieved were getting the articles written (published at Simple Talk), reading the books and listening to podcasts. I’m doing so badly with my university studies that I’m wondering if it’s worth even considering registering next year.

Well, 3 months of the year left, let’s see if I can redeem anything of it. By the end of the year I will

  • Finish the WPF book that I’m reading and finish the WFP app that I’ve been dabbling with for months
  • Read an AI book, cover-to-cover
  • Write 2 articles for SQL Server Central (not hard, one’s half-written, one’s planned out)
  • Do the last section of the proof-of-concept for my Master’s experiments and have at least the outline of the design of the actual experiments documented.
  • Write one certification exam, probably the MCITP SQL Developer

General

TechEd Online Interview

Author: 30 September 2009 0 Comments

Back in August at TechEd Africa I did a TechEd Online interview with Frikkie Bosch. Frikkie’s the marketing manager for the Server products down here in South Africa. We discussed some common mistakes that I’ve seen regarding SQL Server performance. I’m not talking specifics of query/table design here, but rather mistakes at a higher level.

The interview is available on the TechEd Online site. I’m interested in what people think, am I on the mark or completely in the wrong ballpark?

Community, SQL Server, Syndication

More on the MVP SQL Server book

Author: 29 September 2009 1 Comment

If you buy from Manning directly today or tomorrow, there’s a 50% discount . Use the code pop0928 at the checkout. Hurry, hurry, hurry….

For those who want a copy and are going to the PASS Summit in November, why not pick up your copy of the book there? The PASS bookshop will be selling them (though no word yet on how many) and many of the authors will be at the Summit if you want to get the book autographed.

For more details, see http://sqlblog.com/blogs/paul_nielsen/archive/2009/09/29/53-mvps-warchild-org-and-sqlservermvpdeepdives-com.aspx

SQL Server, Syndication

MVP book is available for preorder

Author: 28 September 2009 0 Comments

Last year a large number of MVPs (mostly SQL Server) got together to write a book with all proceeds going to charity. The book consists of over 50 chapters, each on a different SQL-Server related topic, from database architecture through development administration, database development, performance tuning and even Business Intelligence. I contributed a chapter on deadlock graphs, how to dissect the deadlock graph and see what locks were taken and requested and by what statements.

The book’s been delayed a couple times for various reasons, but it is now available for pre-order from a number of places:

SQL Server, Syndication

Why is CheckDB rolling back transactions?

Author: 24 September 2009 2 Comments

I saw this one on a forum recently and it’s a fun question to look into.

When running CheckDB on an in-use database, sometimes messages like the following will appear in the SQL error log

1 transactions rolled back in database ‘Testing’ (8). This is an informational message only. No user action is required.
DBCC CHECKDB (Testing) WITH no_infomsgs executed by theAdmin found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 31 seconds.

What’s going on here? Why is checkDB rolling transactions back?

Well, the truth is, it’s not rolling anything back.

When CheckDB runs it has to be able to get a consistent view of the database, one without half-done transactions, without data modifications in progress. On SQL 2000 (and earlier), CheckDB used the transaction log to get that consistent view. It would read through the transaction log to see what had been done to the various structures in the database. In SQL 2005 this was changed and CheckDB now uses the database snapshot feature to get its consistent, point-in-time view of the database.

Database Snapshots

A database snapshot is a read-only, point-in-time copy of a database. It uses a combination of sparse file and copy-on-write technology to reduce space usage and, as a result, it’s usually much smaller than the database it was based on.

When a snapshot is created, any open transactions are rolled back within the context of the database snapshot to generate the consistent, point-in-time copy. This generates messages in the SQL error log stating how many transactions were rolled back

CheckDB

The first thing that CheckDB has to do is to create an internal, hidden database snapshot to get the consistent view of the DB that it needs. It will do this unless it’s run with the TABLOCK option. When the snapshot is created, any open transactions will be rolled back but only in the context of the snapshot, not in the context of the actual database. CheckDB will then run against the snapshot and will drop it once complete.

So the answer to the question of why checkDB is rolling back transactions is a simple one. It isn’t.

Admin, SQL Server, Syndication

Estimated rows, actual rows and execution count

Author: 22 September 2009 22 Comments

It’s often said that a major discrepancy between estimated and actual row counts in a query’s execution plan is a sign of inaccurate statistics or a poor cardinality estimate and that it’s a sign of a problem. This is generally true, however there are places where the estimates and actual rows will differ, often quite dramatically, without it been a problem. The reason for this is that these two values show slightly different things.

Let’s take a look at an example. (table creation code at the end of the post)

select bt.id, bt.SomeColumn, st.SomeArbDate
from dbo.BigTable bt
inner join dbo.SmallerTable st on bt.SomeColumn = st.LookupColumn
where bt.id between 5000 and 5100

Estimated Actual discrepency

Estimated rows = 1, actual rows = 101. That’s a large discrepancy, but what caused it? It’s not out of date statistics (a usual cause) because the table has only just been created, so why is the estimation so far from the actual.

(more…)

Execution Plans, SQL Server, 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

Copyright 2018 Gail Shaw