Month: April 2010

My Top Ten Lessons From SQL Saturday #31 Chicago

By on • ( 7 Comments )

I attended SQL Saturday Chicago this weekend. I had an awesome time and attended great sessions. While I got something good out of every one of the sessions, I decided to distill the entire experience into a top ten list with some of the highlights.

SneakersOnWire

10. According to Google Maps, the distance from Grand station on the CTA Blue Line to Navy Pier is 2.1 miles. I learned this after #8 below.

9. According to Google Maps, the distance from Navy Pier to Grand station on the CTA Blue Line is 2.1 miles. I learned this after #8 below.

8. Just like with shrinking databases, the fact that you CAN walk the distance in #10 above and then walk the distance in #9 above (all in under 2 hours, to ensure you will make it back to the airport in time to catch the last shuttle back to the hotel) does not make it a good idea to do so. AND, just like shrinking a database can affect its future performance, the walk mentioned above created soreness that is definitely affecting my current walking performance. But hey, while I was walking I did what anyone else would do: I asked myself, "What would Forrest Gump do?" Keep on goin’ is what. 

7. A good tip from Jeremiah Peschka ( Twitter | Blog ) is to create variables for commonly used strings (like creating new lines, or escaping single quotes) when building dynamic SQL to keep your code as readable as possible.

6. Kevin Kline ( Twitter | Blog ) pointed out that mixing DDL and DML within a stored procedure will cause recompiles, which can add significantly to resource consumption and execution time.

5. If it is a chilly Friday night in April around 8pm and you buy a churro from the little churro hut on Navy Pier, they may just offer you a second one for free as they are closing up anyway. Sweet. A little fuel for #9 above.

4. Before Andrew Karcher’s ( Twitter | Blog ) presentation on MDX, I knew jack about MDX (I could usually spell it properly, but that’s about it). Now, thanks to him, I don’t know jack anymore. Wait. I mean I now understand the basics that were a mystery to me before. Yeah. The second one.

DevilsTower3. Arie Jones ( Twitter | Blog ) pointed out something I had not read concerning SQL Server 2008 R2 Reporting Services. When exporting to multiple worksheets in Excel, R2 allows you to name those worksheets without sacrificing black cats or running off to Devil’s Tower with Richard Dreyfuss. Hazzah to the product team for adding this. Hazzah, I say!

2. When doing Karaoke in a room full of geeks, requesting Barry Manilow’s Copacabana and substituting Weird Al Yankovic’s Star Wars Cantina lyrics can go over well.

1. Wendy Pastrick ( Twitter ), Ted Krueger ( Twitter ), Jes Borland ( Twitter ), and many others worked hard to put on a great SQL Server event. They succeeded quite well, indeed. Thanks so much to all organizers, volunteers, and presenters.

Making Your SSIS Configuration Files More Readable

By on • ( 11 Comments )

When you first create a configuration file in your Integration Services project, the XML contents of the file are just laid out in one long line. This makes reading and/or modifying this file more challenging than it needs to be.

There is an easy way to format your file to alleviate this issue. In BIDS, go to File/Open/File, as in the image below:

image

Browse to your configuration file and select it to open within BIDS.

My brand new configuration file looks like this image below:

image

You see that the contents are all on one line. Bleh.

To fix that, you can go to Edit/Advanced/Format Document, as shown below:

image

I have done that to my config file and now it looks like this:

image

As you can see, this is much easier to deal with. Just save this document and close it. Now, even when you open it in Notepad, that formatting persists.

image

This little trick can make dealing with your configurations files a bit easier.

A Hint on Using Indexed Views For Read Performance

By on • ( 3 Comments )

Some time ago, I was on a project in which we implemented a report model. The data was located in several different databases, none of which were very conducive to reporting. The consequence was that the views upon which we based our entities for the model required lots of joins and performed poorly. We decided to implement indexed views in order to boost the read performance and improve the experience for the users. With indexed views, the data returned by the view is persisted in a clustered index, just like a clustered table, which can have great benefits in read performance.

NOTE: You should research and understand the requirements and cost of using Indexed Views before implementing them in your environment. Indexed views incur a cost in terms of write performance, so you will want to test extensively to make sure the gain on reads is worth that write cost.

You can read details about Creating Indexed Views here.

One thing I noticed is that when I first queried the indexed views, the performance didn’t really change. The reason is that the query optimizer typically defaults to expanding the view definition to access the underlying objects directly. I will demonstrate this in a moment. I did find, however, if my index on the view covered all the columns necessary to satisfy my query, then the optimizer did in fact go ahead and use the index on the view instead of expanding.

You can read details about Resolving Indexed Views here.

Let’s demonstrate this behavior I mentioned above where the indexed view does not cover the query.

CREATE TABLE dbo.Musketeer

(

      MusketeerID int NOT NULL 

    , MusketeerName varchar(20) NOT NULL

)

 

CREATE TABLE dbo.Lackey

(

      LackeyID int NOT NULL

    , MusketeerID int NOT NULL

    , LackeyName varchar(20) NOT NULL

)

 

CREATE TABLE dbo.Sword

(

      SwordID int NOT NULL

    , MusketeerID int NULL

    , SwordName varchar(20) NOT NULL

)

 

INSERT INTO dbo.Musketeer VALUES(1,'Athos')

INSERT INTO dbo.Musketeer VALUES(2,'Porthos')

INSERT INTO dbo.Musketeer VALUES(3,'Aramis')

 

INSERT INTO dbo.Lackey VALUES(1,1,'Grimaud')

INSERT INTO dbo.Lackey VALUES(2,2,'Mousqueton')

INSERT INTO dbo.Lackey VALUES(3,3,'Bazin')

 

INSERT INTO dbo.Sword VALUES(1,1,'Cutlass')

INSERT INTO dbo.Sword VALUES(2,2,'Rapier')

INSERT INTO dbo.Sword VALUES(3,3,'Epee')

 

In the code above, we create our tables and insert some data. I chose The Three Musketeers today. NOTE: I made up the data regarding the Sword preferences.

Let’s create our view.

CREATE VIEW dbo.vMusketeerInfo WITH SCHEMABINDING

AS

SELECT

      m.MusketeerID

    , m.MusketeerName

    , l.LackeyName

    , s.SwordName

FROM dbo.Musketeer m

INNER JOIN dbo.Lackey l

ON l.MusketeerID = m.MusketeerID

INNER JOIN dbo.Sword s

ON s.MusketeerID = m.MusketeerID

 

Let’s query our view, returning the Actual Execution Plan:

SELECT 

      MusketeerID

    , MusketeerName

    , LackeyName

    , SwordName      

 FROM dbo.vMusketeerInfo

 

/* RESULTS

MusketeerID MusketeerName  LackeyName  SwordName

----------- -------------- ----------- --------------------

1           Athos          Grimaud     Cutlass

2           Porthos        Mousqueton  Rapier

3           Aramis         Bazin       Epee

 

(3 row(s) affected)

*/

 

The plan returned is the following:

image

 

Notice that the Optimizer is doing a Table Scan on each table referenced.

Now let’s create an index on our view:

CREATE UNIQUE CLUSTERED INDEX ix_vMusketeerInfo_MusketeerID

ON dbo.vMusketeerInfo (MusketeerID)

GO

 

Great. Now that we have an index on our view, we should be all set, right? Let’s query the view again.

SELECT 

      MusketeerID

    , MusketeerName

    , LackeyName

    , SwordName      

 FROM dbo.vMusketeerInfo

 

/* RESULTS

MusketeerID MusketeerName  LackeyName  SwordName

----------- -------------- ----------- --------------------

1           Athos          Grimaud     Cutlass

2           Porthos        Mousqueton  Rapier

3           Aramis         Bazin       Epee

 

(3 row(s) affected)

*/

 

OK. We get the same results, as one would expect. How about the plan?

image

 

The plan is the same. The Optimizer is still performing Table Scans on each table. SQL Server has expanded the view and accessed the underlying objects directly. If you want to make sure that you are going to use the unexpended view, you can add the WITH (NOEXPAND) hint.

SELECT 

      MusketeerID

    , MusketeerName

    , LackeyName

    , SwordName      

 FROM dbo.vMusketeerInfo WITH (NOEXPAND)

 

/* RESULTS

MusketeerID MusketeerName  LackeyName  SwordName

----------- -------------- ----------- --------------------

1           Athos          Grimaud     Cutlass

2           Porthos        Mousqueton  Rapier

3           Aramis         Bazin       Epee

 

(3 row(s) affected)

*/

Again, the results are the same. However, we have a new plan.

image

 

The WITH (NOEXPAND) hint dictates to SQL Server that the view is not to be expanded to the underlying objects. Thus, in our implementation, we achieved a tremendous gain in READ performance. We did incur a cost on write performance, but in our implementation, the benefits to reads greatly outweighed that write cost.