Michael J. Swart

December 12, 2014

Obvious and Not-So-Obvious Writing Tips

Filed under: SQLServerPedia Syndication — Michael J. Swart @ 10:54 am

Takeaway: I leave SQL Server behind this week and I give two tips for technical bloggers,

  1. An obvious tip: Practice a lot
  2. A not-so-obvious tip: Help your readers skip reading your article

First the obvious tip.

Practice in Volume

As far as tips go, practice makes perfect is kind of obvious, and ultimately a little disappointing. Just like “Eat right and exercise”, the phrase “Go practice more” is one of those things that is easier said than done.

I first heard about a Composition Derby when I read The Underachieving School by John Holt. John Holt was an English teacher and author and he describes the Composition Derby as a device he used to help kids practice writing. The kids in his English class get divided into teams and they are asked to write about anything they want (spelling and grammar doesn’t count). At the end of the competition, the team who has written the most words wins. That’s the only criteria, number of words. When kids don’t worry about making mistakes they feel free to practice more. And that frees them to improve faster.

But I think the volume of practice is the key here. I believe in Malcolm Gladwell’s 10,000 hours rule. The rule claims that it takes 10,000 hours to become an expert at something. I like the idea of the 10,000 hour rule, but the one thing I don’t like is that it gives a definite number. Eight hours of writing practice can yield results and 10,000 hours implies a finish line. For example, compare these two illustrations I drew. They both use the same reference photo but they’re spaced apart by about 1,000 hours of practice.
An upset looking E. F. CoddTed Codd

It’s easy to compare illustrations when presented side by side. It’s not as easy to compare writing but feel confident that with practice, you’ll improve and your readers will notice.

Make Your Article Skippable

The second tip is a little counter-intuitive. Make it easy for your readers to skim your article or even skip reading your article all together.

You have something important to write, and I get that. But when thinking about the reader-writer relationship, your article is all about your readers. Their need to read actually outweighs your need to write and ultimately your readers will decide what’s important. I’m notoriously bad at predicting whether a post of mine will be well received or not. And so I make my blog posts skippable. The readers who find what I write important will stick around.

Here are some methods I use that help readers stop reading. Consider using these methods in your own writing

  • Topic sentence (which I frame as a takeaway). Condense your whole blog into a tweet-sized sentence. Give everything away as quickly and clearly as you can. Leave suspense-building for mystery writers. For example, if you only read SQL Server articles, you probably haven’t made it this far. You probably didn’t make it past the first sentence.
  • Organize your article into sections with headings that can stand alone as an outline. It improves skimmability.
  • In general, put a high value on your reader’s time. Make every word count in helping you say the one thing you want to say and don’t say anything else.

Now here’s the crazy part, when you make your article skippable it actually has the opposite effect. These methods I use actually help readers stick around. Readers have a better mental roadmap of the content and they stay (see, you’ve stuck around this far!).

December 3, 2014

Materialized Views in SQL Server

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 9:28 am

What’s the difference between Oracle’s “materialized views” and SQL Server’s “indexed views”? They both persist the results of a query, but how are they different? Sometimes it’s difficult to tell.

I'm on the left (or am I?)

I’m on the left (or am I?)

One difference is that SQL Server’s indexed views are always kept up to date. In SQL Server, if a view’s base tables are modified, then the view’s indexes are also kept up to date in the same atomic transaction.

Let’s take a look at Oracle now. Oracle provides something similar called a materialized view. If Oracle’s materialized views are created without the REFRESH FAST ON COMMIT option, then the materialized view is not modified when its base tables are. So that’s one major difference. While SQL Server’s indexed views are always kept current, Oracle’s materialized views can be static.

Static Materialized Views In SQL Server?

Yeah, we just call that a table. You can use a SELECT INTO statement and it’s pretty easy. In fact, for fun I wrote a procedure that does the work for you. Given the name of a view it can create or refresh a table:

/* This is a proof-of-concept and is written for illustration purposes, don't use this in production */
create procedure dbo.s_MaterializeView
  @viewName nvarchar(300),
  @yolo bit = 0 -- use @yolo = 1 to execute the SQL immediately
as
 
declare @persistedViewName nvarchar(300);
 
if not exists (select 1 from sys.views where object_id = object_id(@viewName))
  THROW 50000, N'That @viewName does not exist', 1;
 
select 
  @viewName = QUOTENAME(object_schema_name(object_id)) 
  + N'.'
  + QUOTENAME(object_name(object_id)),
  @persistedViewName = QUOTENAME(object_schema_name(object_id)) 
  + N'.'
  + QUOTENAME(N'persisted_' + object_name(object_id))
from sys.views
where object_id = object_id(@viewName);
 
set xact_abort on;
begin tran
  declare @sql nvarchar(2000);
  set @sql = N'
    IF OBJECT_ID(''' + @persistedViewName + N''') IS NOT NULL
      DROP TABLE ' + @persistedViewName + N';
 
    SELECT *
	INTO ' + @persistedViewName + N'
    FROM ' + @viewName + N';'
 
  if (@yolo = 1)
    exec sp_executesql @sql;  
  else 	
    print @sql;
commit

Which can be used to generate sql something like this:

    IF OBJECT_ID('[dbo].[persisted_vSomeView]') IS NOT NULL
      DROP TABLE [dbo].[persisted_vSomeView];
 
    SELECT *
	INTO [dbo].[persisted_vSomeView]
    FROM [dbo].[vSomeView];

Are Such Static Materialized Views Useful?

Yes:

  • They can be used to get around all the constraints placed on regular indexed views. And if you’ve ever implemented indexed views, you understand that that’s a lot of constraints. I think this benefit is what makes this whole blog post worth consideration.
  • Because it’s static, you can avoid all the potential performance pitfalls that accompany the maintenance of an indexed view (more on this next week).
  • Good or bad, the view doesn’t have to be created with SCHEMABINDING.
  • Indexing is strictly do-it-yourself. Chances are you want more than a single heap of data for your materialized view.

… and no:

  • Most obviously, the data is static, which is another way of saying stale. But notice how Microsoft promotes indexed views. They say that indexed views are best suited for improving OLAP, data mining and other warehousing workloads. Such workloads can typically tolerate staleness better than OLTP workloads. And so maybe materialized views are a feasible alternative to indexed views.
  • You have to manage when these views get refreshed. This means scheduling jobs to do extra maintenance work (yuck). For me that’s a really high cost but it’s less costly if I can incorporate it as part of an ETL process.
  • Using Enterprise Edition, SQL Server’s query optimizer can choose to expand indexed views or not. It can’t do that with these materialized views.

I didn’t write the procedure for any important reason, I just wrote it because it was fun. But I have used this materialized view technique in SQL Server at work and I’ve been quite successful with it. It’s not something that should be used often, but it’s always worth considering if you can understand the trade-offs.

November 20, 2014

Developers, Ready for a New SQL Server Version?

Filed under: Miscelleaneous SQL,Technical Articles — Michael J. Swart @ 2:04 pm

Is your application ready to have its SQL Server upgraded to the next version? It’s important to find out because if you’re ready (and you know it) you can proceed with confidence. But if you’re not ready, you can choose to adjust your application, postpone the upgrade or do both.

You Only Live Once

But there’s some good news, chances are really good that your application is in fact ready for the next version of SQL Server. Focusing on the database engine I want to explore ways to find out for sure.

First, Inspect Your App for Discontinued Features and Other Changes

Bookmark Microsoft’s docs on the subject: SQL Server Database Engine Backward Compatibility. This is your main resource for your upgrade “certification” effort. Microsoft describes Deprecated and Discontinued features and it also describes breaking and behavior changes. The list is well-written and well-organized. Microsoft has really done well with these.

Look through your own application’s code using a code search or other means and check to see whether your application is affected by these changes. Luckily these lists have always been manageable. Microsoft introduces a lot of features, but they don’t deprecate a lot. It’s in their best interest to maintain backwards compatibility.

Just like breakfast, it’s important not to skip this step, it’s the most important one. I’ve seen this inspection step uncover more potential upgrade issues than any of the following steps.

Test Your App on a Production-like Instance

Now it’s time to test your application with the new version. But that’s easier said than done. You’ll need to:

  • Get a database server (easy) whose specs are close to what’s running in prod (less easy).
  • Find a database with the same size data as prod (a restored backup of prod if you can swing it).
  • The ability to generate a workload with the same volume and variety as seen in prod (really tricky).
  • Now upgrade to the next version of SQL Server and test.

Even if you can’t perfectly reproduce production hardware, production data or production workloads, most people expect this kind of testing. For that reason alone, this step shouldn’t be skipped. It gives people confidence.

Use the Deprecated Features Performance Counter

SQL Server provides performance counters that track when it uses a deprecated feature. And because they’re SQL Server performance counters, you can inspect the values from a query window like this:

select * 
from sys.dm_os_performance_counters
where object_name like '%Deprecated Features%'
and cntr_value > 0

These counters get reset when SQL Server is restarted. So look at a production server that has been running for a while.

But this list can be tricky. Even against the cleanest, quietest database server, you’re never going to see an empty result set. There are always a number of “problems” that get reported. And it’s not easy to sort out which ones matter. The list indicates that someone used a deprecated feature of SQL Server and that someone could be:

  1. Your application
  2. SQL Server system processes
  3. Some admin or other user writing ad hoc SQL
  4. SQL Server Management Studio

Try to determine which problems come from your application and whether you want to do anything about it.

Upgrade Advisor

The SQL Server Upgrade Advisor is a tool that Microsoft provides to help people when upgrading to a new version. It’s in Microsoft’s interest to make the whole process as painless as possible and they do a pretty good job.

The Upgrade Advisor will also look at Analysis Services, Reporting Services and Integration Services, but you’ll be focusing on the Database Engine. Optionally you can provide a workload in the form of a system trace. It’s a nice tool, but it’s rare that it will catch anything in your app that you haven’t caught already with the previous steps.

Other Considerations

Undocumented Features
Are there any undocumented trace flags that you’re using? If you’re depending on behavior of such trace flags, now’s a time to take a fresh look at what you’re trying to accomplish. The behavior is, by definition, undocumented and can change any time.

Database Compatibility Levels Other Than Latest
I like to avoid any compatibility level that’s not the most current. Mostly this is a supportability thing.

As a vendor, I want to support the fewest number of versions possible. If I begin to entertain different compatibility levels, this multiplies the number of environments I have to support.

But maybe you’re not a vendor. If you have just one single database to support. If you choose to take small steps through compatibility levels and versions, you’re probably doubling the amount of testing required.

Known Issues
If you’re not going to the latest Service Pack and/or Cumulative Update, then you should be aware of the fixes you’re choosing not to take.
It’s actually very difficult to keep up to date on what known issues are out there and which ones are severe enough to worry about. For example, if I only use the SQL Server database engine, I’m not going to be impacted by a DAX performance bug in SSAS, but I do care an awful lot about Data Corruption Bugs.
I like to read what Aaron Bertrand has to say on SQLBlog.com or SQLPerformance.com. He’s a good resource when trying to keep up with known issues in SQL Server.

Unknown Issues
Otherwise known as Avoid-RTM. This advice is less relevant lately and I think it might be for a couple reasons. Just speculating here:

  • The cynical part of me says that Microsoft will just wait a little bit and call the CU that month SP1 just to appease the people who are waiting.
  • The optimist in me sees that Microsoft lately has a really nice cloud-first deployment model where Azure customers are kicking the tires behind the scenes so that when RTM comes around, parts of the database have seen a lot of production hours they wouldn’t have otherwise. In the past I have been burned by database engine bugs in 2005 and 2008. I haven’t been burned by 2008 R2 or 2012. It’s anecdotal, but it makes me hopeful.

Does Version Matter

Not really, I wrote this post with no particular version in mind.

But I do want to mention one particular point that has to do with SQL Server 2014. The new cardinality estimator really affects the behavior of the database engine. It affects plans chosen by the query optimizer and hence it affects the performance of applications that use it.
This is why I advise everyone to give the upgrade to 2014 a little more scrutiny than an upgrade to previous versions. Look for performance regressions. This is especially true if you or other developers are fond of writing queries with a lot of query hints. I understand that regressions are more common in queries that make heavy use of hints.

October 3, 2014

Watch Out for Misleading Behaviour From SQL Server

Takeaway: To get consistent behaviour from SQL Server, I share a set of statements I like to run when performing tuning experiments.

Inconsistent Behaviour From SQL Server?

I often have conversations where a colleague wants to understand why SQL Server performs faster in some cases and slower in other cases.

The conversation usually starts “Why does SQL Server perform faster when I…” (fill in the blank):

  1. … changed the join order of the query
  2. … added a transaction
  3. … updated statistics
  4. … added a comment
  5. … crossed my fingers
  6. … simply ran it again

What’s Going On?

It can actually seem like SQL Server performs differently based on its mood. Here are some reasons that can affect the duration of queries like the ones above

  • You changed something insignificant in the query. What you may be doing is comparing the performance of a cached plan with a newly compiled plan. Examples 1 – 4 might fall under this scenario. If that’s the case, then you took a broken thing and gave it a good thump. This percussive maintenance may be good for broken jukeboxes, but maybe not for SQL Server.
  • What about those last two? Say you hit F5 to execute a query in Management Studio, and wait a minute for your results. You immediately hit F5 again and watched the same query take fifteen seconds. Then I like to point out that maybe all that data is cached in memory.

In order to do tune queries effectively, we need consistent behaviour from SQL Server, if only to test theories and be able to rely on the results. SQL Server doesn’t seem to want to give us consistent behaviour…

So Is It Possible To Get Straight Answers?

Best line from all Star Wars

But maybe we can get straight answers from SQL Server. Here’s a test framework that I like to use before all experiments when I want consistent behaviour:

/* Only do this on dev sql servers! */
CHECKPOINT 
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
SET STATISTICS IO, TIME ON
-- Ctrl+M in Management Studio to include actual query plan

The first two statements are meant to clear SQL Server’s cache of data. Because of write ahead logging, SQL Server will write data changes to disk immediately, but may take its time writing data changes to disk. Executing CHECKPOINT makes SQL Server do that immediately. After the checkpoint there should be no dirty buffers. That’s why DBCC DROPCLEANBUFFERS will succeed in dropping all data from memory.

The DBCC FREEPROCCACHE command will remove all cached query plans.

These commands give SQL Server a fresh starting point. It makes it easier to compare behaviour of one query with the behaviour of another.

The SET STATISTICS IO, TIME ON and the Ctrl+M are there in order to retrieve better information about the performance of the query. Often CPU time, Logical IO, and the actual query plan are more useful when tuning queries than elapsed time.

September 18, 2014

SQL Server Ignores Trailing Spaces In Identifiers

Filed under: Miscelleaneous SQL,SQLServerPedia Syndication,Technical Articles — Michael J. Swart @ 10:27 am

Takeaway: According to SQL Server, an identifier with trailing spaces is considered equivalent to the same identifier with those spaces removed. That was unexpected to me because that’s not how other programming languages work. My investigation was interesting and I describe that here.

The First Symptom

Here’s the setting, I work with a tool developed internally that reads metadata from a database (table names, column names, column types and that sort of thing). Recently the tool told me that a table had an unexpected definition. In this case, a column name had an extra trailing space. I expected the column name "Id" (2 characters), but my tool was reporting an actual value of  "Id " (notice the blank at the end, 3 characters). That’s what started my investigation.

But that’s really weird. What would lead to a space accidentally getting tacked on to a column name? I couldn’t think of any reason. I also noticed a couple other things. Redgate SQL Compare was reporting no discrepancies and the database users weren’t complaining at all, they seemed just fine. A bug in the in-house tool seemed most likely. My hunch was that there was a problem with the way we collecting or storing these column names (how did a space sneak in there?).

Where Are Column Names Stored?

I wanted to look at the real name of the column – straight from the source – so I ran:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'Id%'

It told me that my tool wasn’t wrong. That the column was actually named "Id " with the space. So maybe Red Gate is getting its metadata from somewhere else? I know of a few places to get column information. Maybe Red Gate is getting it from one of those? Specifically I wanted to look closer at these views:

  • sys.columns
  • sys.syscolumns
  • INFORMATION_SCHEMA.COLUMNS

Because these objects are views, I used sp_helptext to learn that all the column names ultimately come from a system table called sys.syscolpars. But sys.syscolpars is a system table and you can’t look at its contents unless you connect to the database using the dedicated administrator connection. And that’s exactly what I did.

I learned that there is only one version of column names, only one place on disk that sql server persists the name of the column. It’s interesting because this implies that Red Gate’s SQL Compare trims trailing spaces from identifier names.

But Doesn’t SQL Server Care?

Well, there’s one way to check:

CREATE TABLE [MyTest] ( [id ] INT );
INSERT INTO [MyTest] VALUES (1);
 
SELECT [id ], [id] -- one column name with a space, one column name without
FROM [MyTest]; 
-- returns a dataset with column names as specified in the query.
go

Just like Red Gate’s SQL Compare, it seems like SQL Server doesn’t care about trailing spaces in identifiers either.

Google? Stackoverflow? Want to Weigh In?

A quick search led me to the extremely relevant Is SQL Server Naming trailing space insensitive?.

And that question has answers which link to the Books Online page documenting Delimited Identifiers. That page claims that “SQL Server stores the name without the trailing spaces.” Hmmm, they either mean in memory, or the page is inaccurate. I just looked at the system tables a moment ago and the trailing spaces are definitely retained.

The stackoverflow question also led me to a reported defect, the Connect item Trailing space in column names. This item was closed as “by design”. So this behavior is deliberate.

What do other SQL Vendors do?

I want to do experiments on SQL databases from other vendors but my computer doesn’t have a large number of virtual machines or playground environments. But do you know who does? SQL Fiddle
It’s very easy to use this site to see what different database vendors do. I just pick a vendor and I can try out any SQL I want. It took very little effort to be able to compile this table:

RDBMS CREATE TABLE... SELECT "id ", "id"...
MySQL Incorrect column name 'id '
Oracle Success "id": invalid identifier
PostgreSQL Success Column "id" does not exist
SQLite Success could not prepare statement (1 no such column: id)
SQL Server Success
ID ID
1 1

And What Does the ANSI standard say?

Look at the variety of behaviors from each vendor. I wonder what the “standard” implementation should be.

Mmmm... SQL Syntax rules.

I googled “ANSI SQL 92″ and found its wikipedia page and that led me to the SQL-92 Standard itself.

ANSI (paraphrased) says that

<delimited identifier> ::= <double quote><one or more characters><double quote>

And it also says explicitly that delimited identifiers can include spaces.

What About String Comparisons In General?

During my experiments on SQL Server I found myself executing this query:

SELECT *
FROM sys.columns
WHERE name = 'Id'

I was surprised to find out that my three-character "Id " column came back in the results. This means that SQL Server ignores trailing spaces for all string comparisons, not just for identifiers.

I changed my google search and looked for “sql server string comparison trailing space”. This is where I found another super-relevant document from Microsoft: INF: How SQL Server Compares Strings with Trailing Spaces.

Microsoft pointed to the ANSI standard again. I mean they explained exactly where to look, they pointed straight to (Section 8.2, , General rules #3) which is the section where ANSI explains how the comparison of two character strings is determined. The ANSI standard says that for string comparisons, the shorter string is effectively padded with trailing spaces so that comparisons can always performed on strings with an equal number of characters. Why? I don’t know.

And that’s where identifier comparisons come in. I found another part of the standard (Syntax rule #11) which tells me that Identifiers are equivalent if they compare as equivalent according to regular string comparison rules. So that’s the link between string comparisons and identifier comparisons.

Summary

There’s a number of things I learned about string comparisons. But does any of this matter? Hardly. No one deliberately chooses to name identifiers using trailing spaces. And I could have decided to sum this whole article up in a single tweet (see the title).

But did you figure out the head fake? This blog post is actually about investigation. The investigation is the interesting thing. This post describes the tools I like to use and how I use them to find things out for myself including:

  • Queries against SQL Server itself, the obvious authority on SQL Server behavior.
    • Made use of sp_helptext
    • Made use of the Dedicated Adminstrator Connection to look at system tables
  • Microsoft’s Books Online (used this twice!)
  • Microsoft Connect
  • Google
  • Stackoverflow
  • SQLFiddle
  • Wikipedia
  • the ANSI Standard

Maybe none of these resources are new or exciting. You’ve likely used many of these in the past. But that’s the point, you can find out about any topic in-depth by being a little curious and a little resourceful. I love to hear about investigation stories. Often how people find things can be at least as interesting as the actual lesson.

September 9, 2014

Take Care When Scripting Batches

Filed under: Miscelleaneous SQL,SQL Scripts,Technical Articles — Michael J. Swart @ 1:22 pm

Takeaway: When performing long-running modifications, I’m sure many of you enjoy using batches to increase concurrency. But I want to talk about a pitfall to be aware of. If you’re not careful, the method you use to implement batching can actually worsen concurrency.

... we don't need no stinkin' batches either

Why Use Batches?

Even without an explicit transaction, all SQL statements are atomic – changes are all or nothing. So when you have long-running modifications to make, locks on data can be held for the duration of your query and that can be too long. Especially if your changes are intended for live databases.

But you can make your modifications in several smaller chunks or batches. The hope is that each individual batch executes quickly and holds locks on resources for a short period of time.

But care is needed. I’m going to give an example to show what I mean. The example uses the FactOnlineSales table in the ContosoRetailDW database (available as a download here). The FactOnlineSales table has

  • one clustered index on OnlineSalesKey and no other indexes,
  • 12 million rows,
  • and 46 thousand database pages

Metrics to Use
In this example, I want to know how long each query takes because this should let me know roughly how long locks are held.
But instead of duration, I’m going to measure logical reads. It’s a little more consistent and in the examples below it’s nicely correlated with duration.

The Straight Query

Suppose we want to remove sales data from FactOnlineSales for the “Worcester Company” whose CustomerKey = 19036. That’s a simple delete statement:

DELETE FactOnlineSales WHERE CustomerKey = 19036;

This delete statement runs an unacceptably long time. It scans the clustered index and performs 46,650 logical reads and I’m worried about concurrency issues.

Naive Batching

So I try to delete 1,000 rows at a time. This implementation seems reasonable on the surface:

DECLARE	
	@RC INT = 1;
 
WHILE (@RC > 0)
BEGIN
 
  DELETE TOP (1000) FactOnlineSales
  WHERE CustomerKey = 19036;
 
  SET @RC = @@ROWCOUNT
 
END

Unfortunately, this method does poorly. It scans the clustered index in order to find 1,000 rows to delete. The first few batches complete quickly, but later batches gradually get slower as it takes longer and longer to scan the index to find rows to delete. By the time the script gets to the last batch, SQL Server has to delete rows near the very end of the clustered index and to find them, SQL Server has to scan the entire table.

In fact, this last batch performs 46,521 logical reads (just 100 fewer reads than the straight delete). And the entire script performed 1,486,285 logical reads in total. If concurrency is what I’m after, this script is actually worse than the simple DELETE statement.

Careful Batching

But I know something about the indexes on this table. I can make use of this knowledge by keeping track of my progress through the clustered index so that I can continue where I left off:

DECLARE
	@LargestKeyProcessed INT = -1,
	@NextBatchMax INT,
	@RC INT = 1;
 
WHILE (@RC > 0)
BEGIN
 
  SELECT TOP (1000) @NextBatchMax = OnlineSalesKey
  FROM FactOnlineSales
  WHERE OnlineSalesKey > @LargestKeyProcessed
    AND CustomerKey = 19036
  ORDER BY OnlineSalesKey ASC;
 
  DELETE FactOnlineSales
  WHERE CustomerKey = 19036
    AND OnlineSalesKey > @LargestKeyProcessed
    AND OnlineSalesKey <= @NextBatchMax;
 
  SET @RC = @@ROWCOUNT;
  SET @LargestKeyProcessed = @NextBatchMax;
 
END

The delete statements in this script performed 46,796 logical reads in total but no individual delete statement performed more than 6,363.

Graphically that looks like:

Logical Reads Per Delete Statement

Logical Reads Per Delete

The careful batching method runs in roughly the same time as the straight delete statement but ensures that locks are not held for long.
The naive batching method runs with an order or complexity (compared to the expected complexity of n) and can hold locks just as long as the straight delete statement.
This underlines the importance of testing for performance.

June 27, 2014

Trivia about Trivial Plans

Filed under: SQLServerPedia Syndication — Michael J. Swart @ 12:43 pm

Takeaway: I found an example of a query plan which performs better than the “trivial” query plan.

This post is trivia in that it won’t help you do your job as a developer or DBA. But it’s interesting anyway. It offers a look into an interesting part of SQL Server’s query optimizer.

The Setup

I use the 2012 AdventureWorks database and I mess around with the indexes. It’s a setup that Kendra Little developed in order to demonstrate index intersection.

use AdventureWorks2012
GO
 
DROP INDEX Person.Person.IX_Person_LastName_FirstName_MiddleName;
GO
 
CREATE INDEX [IX_Person_FirstName_LastName] ON [Person].[Person] 
( FirstName, LastName ) WITH (ONLINE=ON);
GO
 
CREATE INDEX [IX_Person_MiddleName] ON [Person].[Person] 
( MiddleName ) WITH (ONLINE=ON);
GO

The Trivial Plan

In management studio, include the actual query plan and run this query:

SET STATISTICS IO ON
 
SELECT FirstName, MiddleName, LastName
FROM Person.Person
 
-- 19972 rows returned
-- 1 scan, 3820 logical reads
-- optimization level: TRIVIAL
-- estimated cost: 2.84673

With such a simple query – one against a single table with no filters – SQL Server will choose to scan the narrowest covering index and it won’t bother optimizing the plan any further. That’s what it means to say the optimization level is TRIVIAL.

For this query, the only index that contains all three columns is the clustered one. So it seems there’s no alternative but to scan it. That sounds reasonable right? That’s what we see in the query plan, it looks looks like this:

ClusteredScan

But notice that SQL Server is doing a lot of reading with this plan choice. The table Person.Person has a column called Demographics. This xml field makes the table very wide, so wide that a typical page in Person.Person can only fit about 5 or 6 rows on average.

The Better-Than-Trivial Plan

Now look at this query:

SELECT FirstName, MiddleName, LastName
FROM Person.Person
WHERE FirstName LIKE '%'
 
-- 19972 rows returned
-- 2 scans, 139 logical reads
-- optimization level: FULL
-- estimated cost: 1.46198

The filter is put in place to have no logical effect.  It complicates things just enough so that SQL Server won’t use a trivial plan. SQL Server fully optimizes the query and the query plan now looks like this:

IndexIntersect

Notice that the plan has scans on two indexes nonclustered indexes and a hash join. SQL Server figures (correctly) that scans of two narrow indexes plus a hash join are still cheaper than the single scan of the fat clustered index.

Careful

I don’t think I need to say this, but I do not recommend adding WHERE column like '%' anywhere except maybe in contrived examples for demo purposes.

(MJS — Enjoy the summer, See you in September!)

June 25, 2014

Looking Back at 100 Illustrations

Filed under: Data Cartoons — Michael J. Swart @ 1:05 pm

So I recently took a look at the illustrations I have on this blog and I realize that I’ve got 100 illustrations. I’ve even built a page to show them off. Since 100 is a nice round number, I’m going to take this opportunity to look at some trends. I’ve grouped some of my illustrations into categories:

Movie Franchises

the Princess Bride:

Star Wars:

Star Trek:

Lord of the Rings:

Myself

Turns out I’m also a bit of a narcissist:

Women

A little over half of my illustrations are of people, and a smaller fraction are of fictional characters. But only a tiny fraction of those are women. I’m a bit worried about that. Maybe it says something about pop culture. Maybe it says something about me. I’ll have to give that some extra thought.

My Favorites:

This illustration has the right level of snark and it just makes me laugh. In my head I’ve titled this one “Grumpy Ted Codd” and I have this on a mug. It was one of my first illustrations I ever published and I’ve never been able to capture the same feeling of humour and relevance.

I was proud of this one because the likeness turned out. It was one of the first feelings I had that I was getting the hang of this.

Looking back on this now, their heads are too shiny. But this illustration was used with one of my most popular articles and I find myself looking for this article at least once a month. I hit the Browse By Illustration page and start looking for the mythbusters.

I did this one for a guest post on SQL Brit’s site. There were a lot of details in the spaceship and it’s a different kind of drawing than drawing faces. The pun works and I got to reference cheesy 80’s sci-fi… classic.

In General

It’s been a ton of fun. I don’t know what I have in store for the future but I do feel like a change of format is due.

As always, the comment form below is open. I’d like to hear what you think.

May 22, 2014

Enabling the New Cardinality Estimator in SQL Server 2014

Filed under: SQLServerPedia Syndication — Michael J. Swart @ 8:33 am

Takeaway: SQL Server 2014 will make use of its newly re-written Cardinality Estimator when the database’s compatibility mode is at least 120. But there’s more to the story.

What’s a Cardinality Estimator (CE)?

Say you’ve been hired to phone everyone on a particular list. If it’s a list of all Americans taller than seven feet, you might manage quite well on your own. But if it’s a list of all Americans shorter than seven feet, you’ll probably need help from others. That’s not surprising because the sizes of the lists are wildly different. One list could have 300 people on it and the other could have 300 million. The expected sizes of the lists influence how you tackle this problem.

This was after phones, but before the do not call list.

SQL Server does the same thing. It uses statistics to find the best ways to execute queries. To find a good query plan, SQL Server often needs to make many choices (which join type, join order, parallelism etc…) It needs to estimate the cost of each choice and it uses educated guesses to evaluate these costs. That’s what the CE was built to do. It provides educated guesses about the number of rows a query plan has to process. That’s why it’s called the cardinality estimator. The accuracy of these estimates will influence the quality of query plans, and consequently, the performance of queries.

With SQL Server 2014, Microsoft released a rewritten version of SQL Server’s CE. I can’t wait to take advantage of it. I’m looking forward to tuning fewer poorly performing queries. Queries that seem to be written well, but are vulnerable to bad query plans.

Risk of Regressions

The CE is part of the query optimizer, so the rewrite represents a significant change to the database engine. And with any pervasive change, there’s always a risk of regressions. While rare, some workloads are expected to perform worse with the new CE. Joe Sack’s excellent white paper Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator has some essential tips and suggestions on how to assess and deal with these potential regressions.

Some users may want to continue using the legacy CE. And some users may want to decouple the adoption of the new CE with the adoption of SQL Server 2014. Microsoft anticipated this and so they give DBAs a choice. DBAs have the option to either use the new CE or to stick with the legacy CE.

Enabling the New CE – the Official Details

Simply put, CE behavior can be controlled using the compatibility mode and/or trace flags:

  • The new CE is enabled when compatibility mode is 120 and disabled when it is less than that. The compatibility mode of a database is not modified automatically during an upgrade to 2014, so remember to adjust it accordingly.
  • New trace flags are introduced. Trace flag 9481 can force SQL Server to use the legacy CE when it would otherwise use the new one. Conversely, trace flag 2312 can force SQL Server to use the new CE. And if flags 9481 and 2312 are ever both enabled (in any context), then neither flag takes effect. They cancel each other out and the CE behavior is determined only by the compatibility mode.

Just those two things allow you to influence the CE behavior depending on the granularity you require:

  • For a single query – You could use the QUERYTRACEON hint but it’s not a tempting option. Sysadmin privileges or a forced plan are required.
  • Based on your session – Use session trace flags (again, sysadmin privileges are required).
  • Based on the database you’re connected to – Use compatibility mode.
  • For the whole server – Use global trace flags.

Again, Joe Sack’s white paper explains this in more detail. He provides syntax examples and methods to determine which CE was used based on a query plan.

Corner Use Cases

This leads to some surprising behaviors:

Connect to a System Database to Avoid Compatibility Mode issues

For example, this works:

use master -- in SQL Server 2014, master will always be at compatibility mode 120
GO
 
-- any query (regardless of participating tables) will now use the new CE. e.g.:
SELECT COUNT(*) 
FROM Adventureworks2012.Sales.SalesOrderHeader;

But it’s just a trick and not a technique I would recommend. Besides, this trick doesn’t work when calling stored procedures from other databases.

Using a Trace Flag to Cancel Another One

Trace flags 2312 and 9481 don’t play together well. There is no scenario where one takes precedence over the other. If they’re both enabled, then they cancel each other out:

use Adventureworks2012 -- at compatibility mode 110
GO
DBCC TRACEON( 9481 );
 
SELECT COUNT(*) 
FROM Sales.SalesOrderHeader
OPTION( QUERYTRACEON 2312 ); -- 2312 normally enables the new CE 
-- the 2312 hint is canceled by the 9481 trace flag, the legacy CE is still used.

Again, I avoid this scenario so that I don’t need to worry.

How I Plan To Adopt the New CE

I’d like to begin using the new CE as soon as I upgrade to 2014.

But if I wanted to, I would feel comfortable using compatibility mode as a feature toggle for the new CE. There are other behavior differences between compatibility modes 110 and 120. But I don’t use them and won’t encounter them. They’re obscure and easy to review. So for me, I can ignore those other features and use compatibility mode 120 as the CE feature toggle.

The trace flags 2312 and 9481 are new in SQL Server 2014. So if SQL Server is not at version 2014, it will ignore those trace flags. I intend to do the same no matter what version I’m using. I don’t expect to see many queries showing serious regressions with the new CE, but if I encounter any I’m not going to manage them with these trace flags. Instead, I plan to:

  1. Use hints (whether that means index hints, join hints or query hints) to stabilize the plan temporarily.
  2. Spend time tuning or rewriting the query so that it performs well without these hints.

Further Reading

May 8, 2014

I’m Going To Help You Become A Better Writer

Filed under: Technical Articles — Michael J. Swart @ 11:06 am

I’m offering free copy editing for your technical articles.

Anyone can become a better writer through practice, but you can speed up that process with a mentor.

Grammar, the one thing that Obi Wan didn't learn from Yoda

Copy Editing?

That’s right. I want to help you with your writing. If you submit your writing to me, I’ll go through it and offer suggestions. I won’t just point out spelling and grammar mistakes. I’ll point out sentences that can be reworded or cut. And I’ll point out paragraphs that can be rearranged. Basically I’ll point out any improvement I see given your intended audience and your style. So for example, if you’re writing a rap song about SQL Server’s Cardinality Estimator, I may let some grammar mistakes (and your backbone) slide.

I don’t like to offer unsolicited advice, so consider this an invitation to solicit my advice. I welcome everyone’s writing, I likely have some advice to offer anyone brave enough to submit something for me to look at. (Unless your name is Kevin Kline. Kevin, you’re doing fine.)

How?

Easy! Fill out a form. Request Copy Editing

Why Are You Doing This Michael?

Sentences which are awkward hurts my eyes and is making me feeling some uncomfortableness and I am wanting to stop it any way.

Actually, I remember proofreading some articles for John Sansom’s DBA Jumpstart project. There was some wincing, but I found the work very rewarding. I’ve continued to do this for others in an ad hoc manner. I’ve been helping a user group friend with proofreading and I find it easy and fun.

I’ve gotten a lot of help from others in the past few years. This is me paying it forward using my talents.

Older Posts »

Powered by WordPress