All About Database

Bisnis Pulsa Elektrik - Harga Murah Bersahabat

2009-12-27T14:58:00.000-08:00

"Selamat Datang di Website NataTronik | Natatronik Reloads"

Type your summary here

Type rest of the post here

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns By Jeff Moden, 2008/08/19

2008-12-13T01:31:00.000-08:00

Do you get stuck to find the way converting rows to collumns ?? so.. this is the sollutions :

Introduction:

(This article is dedicated to a good friend and fellow T-SQL warrior, Katrina Wright. We've fought and won many battles together.)

I looked for a definition of what a "Cross Tab" actually is and, after a slight modification, couldn't find a better one than what's in SQL Server 2000 Books Online...

"Sometimes it is necessary to rotate results so that [the data in] columns are presented horizontally and [the data in] rows are presented vertically. This is known as creating a PivotTable®, creating a cross-tab report, or rotating data."

In other words, you can use a Cross Tab or Pivot to convert or transpose information from rows to columns either for reporting or to convert some special long skinny tables known as EAV's or NVP's into a more typical form data.

The purpose of this article is to provide an introduction to Cross Tabs and Pivots and how they can be used to "rotate" data...

Before you say anything...

The reason I'm writing a series of articles on the simple concept of Cross Tabs and Pivots is because of the recent number of requests for this type of information on the SQL Server Central forums... there was a while when not a day went by when two or three such requests were posted each day.

Also, yes, I aware that a lot of this type of "formatting" should be done in the GUI, reporting tool, or maybe even a Spreadsheet. I'm also aware that using EAV/NVP tables isn't considered to be a "best practice". But, like I said about the number of recent number of posts, folks get forced into a corner by their bosses and, if they have to do such a thing, I thought they could use a little help.

Notes of Interest:

I wrote all of the example code and data using Temp Tables just to be safe. Sure, I could have used Table Variables, but they don't really allow for people to do partial runs and they don't all people to look and see what's in the Table Variable after each section. Also, some of the data we'll end up using is a wee bit bigger than what I would normally use a table variable for.

Last but not least, I currently only have SQL Server 2000 and 2005 installed. I indicate which rev each section of code will run on in parenthesis. I'm pretty sure that most of this will work on 2008 and that a good portion of the code for Cross Tabs will also work on 7... but I don't have access to either which means I haven't tested it.

Also, for your convenience, all of the code has been attached in the "Resources" section near the end of the article.

Ok... let's get started...

A simple introduction to Cross Tabs:

The Cross Tab Report example from Books Online is very simple and easy to understand. I've shamelessly borrowed from it to explain this first shot at a Cross Tab.

The Test Data

Basically, the table and data looks like this...


--===== Sample data #1 (#SomeTable1)
--===== Create a test table and some data 
 CREATE TABLE #SomeTable1
        (
        Year    SMALLINT,
        Quarter TINYINT, 
        Amount  DECIMAL(2,1)
        )
GO
 INSERT INTO #SomeTable1 
        (Year, Quarter, Amount)
 SELECT 2006, 1, 1.1 UNION ALL
 SELECT 2006, 2, 1.2 UNION ALL
 SELECT 2006, 3, 1.3 UNION ALL
 SELECT 2006, 4, 1.4 UNION ALL
 SELECT 2007, 1, 2.1 UNION ALL
 SELECT 2007, 2, 2.2 UNION ALL
 SELECT 2007, 3, 2.3 UNION ALL
 SELECT 2007, 4, 2.4 UNION ALL
 SELECT 2008, 1, 1.5 UNION ALL
 SELECT 2008, 3, 2.3 UNION ALL
 SELECT 2008, 4, 1.9
GO

Every row in the code above is unique in that each row contains ALL the information for a given quarter of a given year. Unique data is NOT a requirement for doing Cross Tabs... it just happens to be the condition that the data is in. Also, notice that the 2nd quarter for 2008 is missing.

The goal is to make the data look more like what you would find in a spreadsheet... 1 row for each year with the amounts laid out in columns for each quarter with a grand total for the year. Kind of like this...

... and, notice, we've plugged in a "0" for the missing 2nd quarter of 2008.

Year   1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Total 
------ ------- ------- ------- ------- ----- 
2006     1.1     1.2     1.3     1.4     5.0
2007     2.1     2.2     2.3     2.4     9.0
2008     1.5     0.0     2.3     1.9     5.7

The KEY to Cross Tabs!

Let's start out with the most obvious... we want a Total for each year. This isn't required for Cross Tabs, but it will help demonstrate what the key to making a Cross Tab is.

To make the Total, we need to use the SUM aggregate and a GROUP BY... like this...

--===== Simple sum/total for each year
 SELECT Year,
        SUM(Amount) AS Total
   FROM #SomeTable1
  GROUP BY Year
  ORDER BY Year

And, that returns the following...

Year   Total                                    
------ ---------------------------------------- 
2006   5.0
2007   9.0
2008   5.7

Not so difficult and really nothing new there. So, how do we "pivot" the data for the Quarter?

Let's do this by the numbers...

How many quarters are there per year? Correct, 4.

How many columns do we need to show the 4 quarters per year? Correct, 4.

How many times do we need the Quarter column to appear in the SELECT list to make it show up 4 times per year? Correct, 4.

Now, look at the total column... it gives the GRAND total for each year. What would we have to do to get it to give us, say, the total just for the first quarter for each year? Correct... we need a CASE statement inside the SUM.

Number 4 above is the KEY to doing this Cross Tab... It should be a SUM and it MUST have a CASE to identify the quarter even though each quarter only has 1 value. Yes, if each quarter had more than 1 value, this would still work! If any given quarter is missing, a zero will be substituted.

To emphasize, each column for each quarter is just like the Total column, but it has a CASE statement to trap info only for the correct data for each quarter's column. Here's the code...

--===== Each quarter is just like the total except it has a CASE
     -- statement to isolate the amount for each quarter.
 SELECT Year,
        SUM(CASE WHEN Quarter = 1 THEN Amount ELSE 0 END) AS [1st Qtr],
        SUM(CASE WHEN Quarter = 2 THEN Amount ELSE 0 END) AS [2nd Qtr],
        SUM(CASE WHEN Quarter = 3 THEN Amount ELSE 0 END) AS [3rd Qtr],
        SUM(CASE WHEN Quarter = 4 THEN Amount ELSE 0 END) AS [4th Qtr],
        SUM(Amount) AS Total
   FROM #SomeTable1
  GROUP BY Year

... and that gives us the following result in the text mode (modified so it will fit here)...

Year   1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Total
------ ------- ------- ------- ------- -----
2006   1.1     1.2     1.3     1.4     5.0
2007   2.1     2.2     2.3     2.4     9.0
2008   1.5     .0      2.3     1.9     5.7

Also notice... because there is only one value for each quarter, we could have gotten away with using MAX instead of SUM. Go ahead... try it. We'll use a similar method for normalizing an EAV table in the future.

For most applications, that's good enough. If it's supposed to represent the final output, we might want to make it a little prettier. The STR function inherently right justifies, so we can use that to make the output a little prettier. Please, no hate mail here! I'll be one of the first that formatting of this nature is supposed to be done in the GUI!

--===== We can use the STR function to right justify data and make it prettier.
     -- Note that this should really be done by the GUI or Reporting Tool and
     -- not in T-SQL
 SELECT Year,
        STR(SUM(CASE WHEN Quarter = 1 THEN Amount ELSE 0 END),5,1) AS [1st Qtr],
        STR(SUM(CASE WHEN Quarter = 2 THEN Amount ELSE 0 END),5,1) AS [2nd Qtr],
        STR(SUM(CASE WHEN Quarter = 3 THEN Amount ELSE 0 END),5,1) AS [3rd Qtr],
        STR(SUM(CASE WHEN Quarter = 4 THEN Amount ELSE 0 END),5,1) AS [4th Qtr],
        STR(SUM(Amount),5,1) AS Total
   FROM #SomeTable1
  GROUP BY Year

The code above gives us the final result we were looking for...

Year   1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Total 
------ ------- ------- ------- ------- ----- 
2006     1.1     1.2     1.3     1.4     5.0
2007     2.1     2.2     2.3     2.4     9.0
2008     1.5     0.0     2.3     1.9     5.7

Just to emphasize what the very simple KEY to making a Cross Tab is... it's just like making a Total using SUM and Group By, but we've added a CASE statement to isolate the data for each Quarter.

A simple introduction to Pivots:

Microsoft introduced the PIVOT function in SQL Server 2005. It works about the same (has some limitations) as a Cross Tab. Using the same test table we used in the Cross Tab examples above, let's see how to use PIVOT to do the same thing...

--===== Use a Pivot to do the same thing we did with the Cross Tab
 SELECT Year,             --(4)
        [1] AS [1st Qtr], --(3)
        [2] AS [2nd Qtr],
        [3] AS [3rd Qtr],
        [4] AS [4th Qtr],
        [1]+[2]+[3]+[4] AS Total --(5)
   FROM (SELECT Year, Quarter,Amount FROM #SomeTable1)  AS src --(1)
  PIVOT (SUM(Amount) FOR Quarter IN ([1],[2],[3],[4])) AS pvt --(2)
  ORDER BY Year

Ok... let's break that code down and figure out what each part does... the items below have numbers in the code above so you can more easily see what's going on...

The FROM clause is actually a derived table. It very simply contains the columns that we want to use in the cross tab from the source table we want to use the pivot on. It will sometimes work as a normal FROM clause with just the table listed instead of a derived table, but most of the time it will not and is unpredictable when it does work.

This is the "Pivot" line. It identifies the aggregate to be used, the column to pivot in the FOR clause, and the list of values that we want to pivot in the IN clause... in this case, the quarter number. Also notice that you must treat those as if they were column names. They must either be put in brackets or double quotes (if the quoted identifier setting is ON).

This is the pivoted SELECT list. Notice that you have to bring everything in the IN clause from (2) up to the SELECT list. Aliasing the column names is optional but usually a good thing to do just to make the output obvious.

You must also bring Year up as the row identifier in the pivot. Think of this as your "anchor" for the rows.

Last but not least, if you want a total for each row in the pivot, you can no longer use just an aggregate. Instead, you must add all the columns together.

When you run the code, you get this...

Year   1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Total
------ ------- ------- ------- ------- -----
2006   1.1     1.2     1.3     1.4     5.0
2007   2.1     2.2     2.3     2.4     9.0
2008   1.5     NULL    2.3     1.9     NULL

Notice the NULL's where there are no values or where a NULL has been added into a total. Remember that anything plus a NULL is still a NULL. All of this occurs because the Pivot doesn't do any substitutions like the Case statements we used in the Cross Tab. To fix this little problem, we have to use COALESCE (or ISNULL) on the columns... every bloody column! So, you end up with code that looks like this...


--===== Converting NULLs to zero's in the Pivot using COALESCE
 SELECT Year, 
        COALESCE([1],0) AS [1st Qtr],
        COALESCE([2],0) AS [2nd Qtr],
        COALESCE([3],0) AS [3rd Qtr],
        COALESCE([4],0) AS [4th Qtr],
        COALESCE([1],0) + COALESCE([2] ,0) + COALESCE([3],0) + COALESCE([4],0) AS Total
   FROM (SELECT Year, Quarter,Amount FROM #SomeTable1)  AS src 
  PIVOT (SUM(Amount) FOR Quarter IN ([1],[2],[3],[4])) AS pvt 
  ORDER BY Year

That finally gives us the same result as a Cross Tab sans any right hand justification... again, you'd need to add the STR function to the code to do that.

Year   1st Qtr 2nd Qtr 3rd Qtr 4th Qtr Total
------ ------- ------- ------- ------- -----
2006   1.1     1.2     1.3     1.4     5.0
2007   2.1     2.2     2.3     2.4     9.0
2008   1.5     .0      2.3     1.9     5.7

Readability Comparison

Just for grins, here are both the Cross Tab and the Pivot code real close together so that you can do a comparison...

--===== The Cross Tab example 
 SELECT Year,
        SUM(CASE WHEN Quarter = 1 THEN Amount ELSE 0 END) AS [1st Qtr],
        SUM(CASE WHEN Quarter = 2 THEN Amount ELSE 0 END) AS [2nd Qtr],
        SUM(CASE WHEN Quarter = 3 THEN Amount ELSE 0 END) AS [3rd Qtr],
        SUM(CASE WHEN Quarter = 4 THEN Amount ELSE 0 END) AS [4th Qtr],
        SUM(Amount) AS Total
   FROM #SomeTable1
  GROUP BY Year

--===== The Pivot Example
 SELECT Year, 
        COALESCE([1],0) AS [1st Qtr],
        COALESCE([2],0) AS [2nd Qtr],
        COALESCE([3],0) AS [3rd Qtr],
        COALESCE([4],0) AS [4th Qtr],
        COALESCE([1],0) + COALESCE([2] ,0) + COALESCE([3],0) + COALESCE([4],0) AS Total
   FROM (SELECT Year, Quarter,Amount FROM #SomeTable1)  AS src 
  PIVOT (SUM(Amount) FOR Quarter IN ([1],[2],[3],[4])) AS pvt 
  ORDER BY Year

I'm sure that you'll have a preference, but I like the Cross Tab code better for two reasons... the Cross Tab code is simpler, in my eyes... all I have to remember how to do are those very simple Case statements, I only have to list the values of the pivot columns once, and I don't have to use COALESCE anywhere. The second reason is how simple it is to do a row total.

There's actually several other reasons and one of them is performance. We'll get to performance later, but first let's talk about...

Multiple Aggregations In a Cross Tab (or, "The Problem with Pivots")

We're going to do this section backwards from what we've been doing... we're going to cover how to Pivot multiple aggregations before we cover the equivalent Cross Tab.

A "multiple aggregation Pivot" is just that... we want to show two different aggregates in the Pivot something like this (notice both the Qty and Amt columns have been aggregated)...

Company Year   Q1Amt Q1Qty Q2Amt Q2Qty Q3Amt Q3Qty Q4Amt Q4Qty TotalAmt TotalQty
------- ------ ----- ----- ----- ----- ----- ----- ----- ----- -------- --------
ABC     2006   1.1   2.2   1.2   2.4   1.3   1.3   1.4   4.2   5.0      10.1
ABC     2007   2.1   2.3   2.2   3.1   2.3   2.1   2.4   1.5   9.0      9.0
ABC     2008   1.5   5.1   0.0   0.0   2.3   3.3   1.9   4.2   5.7      12.6
XYZ     2006   2.1   3.6   2.2   1.8   3.3   2.6   2.4   3.7   10.0     11.7
XYZ     2007   3.1   1.9   1.2   1.2   3.3   4.2   1.4   4.0   9.0      11.3
XYZ     2008   2.5   3.9   3.5   2.1   1.3   3.9   3.9   3.4   11.2     13.3

This type of Pivot is a common request so that both aggregates can be viewed for the same time period at the same time. Otherwise, you'd have two completely separate Pivots and you'd have to visually scan back and forth to make simple comparisons. As you'll see the "Problem with Pivots" is that each Pivot can only aggregate one column. To do something like this using Pivots, you have two use two Pivots.

The Test Data

Before we begin, we need some data to test with...

--===== Sample data #2 (#SomeTable2)
--===== Create a test table and some data 
 CREATE TABLE #SomeTable2
        (
        Company  VARCHAR(3),
        Year     SMALLINT,
        Quarter  TINYINT, 
        Amount   DECIMAL(2,1),
        Quantity DECIMAL(2,1)
        )
GO
 INSERT INTO #SomeTable2
        (Company,Year, Quarter, Amount, Quantity)
 SELECT 'ABC', 2006, 1, 1.1, 2.2 UNION ALL
 SELECT 'ABC', 2006, 2, 1.2, 2.4 UNION ALL
 SELECT 'ABC', 2006, 3, 1.3, 1.3 UNION ALL
 SELECT 'ABC', 2006, 4, 1.4, 4.2 UNION ALL
 SELECT 'ABC', 2007, 1, 2.1, 2.3 UNION ALL
 SELECT 'ABC', 2007, 2, 2.2, 3.1 UNION ALL
 SELECT 'ABC', 2007, 3, 2.3, 2.1 UNION ALL
 SELECT 'ABC', 2007, 4, 2.4, 1.5 UNION ALL
 SELECT 'ABC', 2008, 1, 1.5, 5.1 UNION ALL
 SELECT 'ABC', 2008, 3, 2.3, 3.3 UNION ALL
 SELECT 'ABC', 2008, 4, 1.9, 4.2 UNION ALL
 SELECT 'XYZ', 2006, 1, 2.1, 3.6 UNION ALL
 SELECT 'XYZ', 2006, 2, 2.2, 1.8 UNION ALL
 SELECT 'XYZ', 2006, 3, 3.3, 2.6 UNION ALL
 SELECT 'XYZ', 2006, 4, 2.4, 3.7 UNION ALL
 SELECT 'XYZ', 2007, 1, 3.1, 1.9 UNION ALL
 SELECT 'XYZ', 2007, 2, 1.2, 1.2 UNION ALL
 SELECT 'XYZ', 2007, 3, 3.3, 4.2 UNION ALL
 SELECT 'XYZ', 2007, 4, 1.4, 4.0 UNION ALL
 SELECT 'XYZ', 2008, 1, 2.5, 3.9 UNION ALL
 SELECT 'XYZ', 2008, 2, 3.5, 2.1 UNION ALL 
 SELECT 'XYZ', 2008, 3, 1.3, 3.9 UNION ALL
 SELECT 'XYZ', 2008, 4, 3.9, 3.4
GO

The Multi-Aggregate Pivot

Like I said... we'll do the Pivot first this time... then we'll show you how easy it is to do using a Cross Tab.

In order to do a single Pivot, you have to have a derived table and a Pivot clause. The "Problem with Pivots" is that you can only Pivot one aggregate per Pivot clause. If you want to Pivot two aggregates as shown at the beginning of this section, you have to make two Pivots and join them as well as adding the necessary columns to the Select list. You already know how to use a single Pivot... Here's how we would do a double Pivot using the data above...

--===== The "Problem with Pivots" is you need to do one Pivot for each aggregate.
     -- This code Pivots the Amt and Qty values by quarter.
 SELECT amt.Company,
        amt.Year,
        COALESCE(amt.[1],0) AS Q1Amt,
        COALESCE(qty.[1],0) AS Q1Qty,
        COALESCE(amt.[2],0) AS Q2Amt,
        COALESCE(qty.[2],0) AS Q2Qty,
        COALESCE(amt.[3],0) AS Q3Amt,
        COALESCE(qty.[3],0) AS Q3Qty,
        COALESCE(amt.[4],0) AS Q4Amt,
        COALESCE(qty.[4],0) AS Q4Qty,
        COALESCE(amt.[1],0)+COALESCE(amt.[2],0)+COALESCE(amt.[3],0)+COALESCE(amt.[4],0) AS TotalAmt,
        COALESCE(qty.[1],0)+COALESCE(qty.[2],0)+COALESCE(qty.[3],0)+COALESCE(qty.[4],0) AS TotalQty
   FROM (SELECT Company, Year, Quarter, Amount FROM #SomeTable2) t1
        PIVOT (SUM(Amount) FOR Quarter IN ([1], [2], [3], [4])) AS amt
  INNER JOIN 
        (SELECT Company, Year, Quarter, Quantity FROM #SomeTable2) t2
        PIVOT (SUM(Quantity) FOR Quarter IN ([1], [2], [3], [4])) AS qty
     ON qty.Company = amt.Company 
    AND qty.Year    = amt.Year         
  ORDER BY amt.Company, amt.Year

I don't know about you, but my personal feeling is that's starting to look a bit complicated and it's starting to be more difficult to read. Certainly, if we tried to convert this to dynamic SQL, you'd have your work cut out for you.

Notice that the FROM clause has two nearly identical derived tables and the only difference in the Pivot clauses are the columns being SUMmed. And, take a look at the row totals in the Select list... thank goodness this example only has 4 columns each for Quantity and Amount.

The Multi-Aggregate Cross Tab

We saw how complicated Multi-Aggregate Pivots can get. And the example above was just for two 4 column aggregates... just image what it might look like for three 12 column aggregates!

Let's see how complicated it might be in a Cross Tab...ready?

--===== Doing multiple aggregations in Cross Tabs is as simple as CPR
     -- (CPR = Cut, Paste, Replace). AND, the table is "dipped" only
     -- once instead of twice so there are NO JOINS to worry about!
 SELECT Company,
        Year,
        SUM(CASE WHEN Quarter = 1 THEN Amount   ELSE 0 END) AS Q1Amt,
        SUM(CASE WHEN Quarter = 1 THEN Quantity ELSE 0 END) AS Q1Qty,
        SUM(CASE WHEN Quarter = 2 THEN Amount   ELSE 0 END) AS Q2Amt,
        SUM(CASE WHEN Quarter = 2 THEN Quantity ELSE 0 END) AS Q2Qty,
        SUM(CASE WHEN Quarter = 3 THEN Amount   ELSE 0 END) AS Q3Amt,
        SUM(CASE WHEN Quarter = 3 THEN Quantity ELSE 0 END) AS Q3Qty,
        SUM(CASE WHEN Quarter = 4 THEN Amount   ELSE 0 END) AS Q4Amt,
        SUM(CASE WHEN Quarter = 4 THEN Quantity ELSE 0 END) AS Q4Qty,
        SUM(Amount)   AS TotalAmt,
        SUM(Quantity) AS TotalQty
   FROM #SomeTable2
  GROUP BY Company, Year
  ORDER BY Company, Year

How easy is that!? There're no derived tables... no fancy Pivot clauses... no huge lines of code to make simple row totals... and no joins!. It's a breeze to make using a little CPR (Copy, Paste, Replace).

Go back and compare the incredible simplicity of this Cross Tab with the relatively complex Pivot code to do the same thing. I don't know about you, but I won't be using Pivot to do such a simple thing.

"Pre-Aggregation"

I found something very handy in the past... I call it "Pre-Aggregation" and it can be used on either a Cross Tab or a Pivot.

The general purpose of pre-aggregation is to make it very easy to summarize the data and then format the data for display. Sometimes you'll have some complex aggregations that are a bit difficult or impossible to do when mixed with the rotation in the Select list, so the best thing to do is to do the aggregations as a derived table and then rotate the results. For example, if you want to aggregate dates by month, you'll find it's much easier to pre-aggregate the data using a formula to convert all dates to the first of the month. We'll cover more on that subject in the next article on Cross Tabs.

Pre-aggregation is nothing more than doing the aggregation as part of a derived table and then doing a Cross Tab or Pivot on that result. That's all it is.

You'll find pre-aggregation code for both Cross Tabs and Pivots in the next section of code where you'll also find another really good reason for doing pre-aggregation even if you don't need it to solve complexity...

Performance

Ah yes... what about performance? Just because the code looks simple or complex doesn't necessarily mean faster or slower nor fewer or more resources. Here's the full test code I used... I intentionally did NOT calculate Quarters from the date in the Cross Tabs or the Pivots because I wanted to show you just how much of a performance difference a simple tweak here and there can make... the biggest tweaks I made was the use of pre-aggregation and the use of CTE's...

--===== Create and populate a 1,000,000 row test table.
     -- Column "RowNum" has a range of 1 to 1,000,000 unique numbers
     -- Column "Company" has a range of "AAA" to "BBB" non-unique 3 character strings
     -- Column "Amount has a range of 0.0000 to 9999.9900 non-unique numbers
     -- Column "Quantity" has a range of 1 to 50,000 non-unique numbers
     -- Column "Date" has a range of  >=01/01/2000 and <01/01/2010 non-unique date/times
     -- Columns Year and Quarter are the similarly named components of Date
     -- Jeff Moden


 SELECT TOP 1000000 --<<Look!  Change this number for testing different size tables
        RowNum       = IDENTITY(INT,1,1),
        Company      = CHAR(ABS(CHECKSUM(NEWID()))%2+65)
                     + CHAR(ABS(CHECKSUM(NEWID()))%2+65)
                     + CHAR(ABS(CHECKSUM(NEWID()))%2+65),
        Amount       = CAST(ABS(CHECKSUM(NEWID()))%1000000/100.0 AS MONEY),
        Quantity     = ABS(CHECKSUM(NEWID()))%50000+1,
        Date         = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
        Year         = CAST(NULL AS SMALLINT),
        Quarter      = CAST(NULL AS TINYINT)
   INTO #SomeTable3
   FROM Master.sys.SysColumns t1
  CROSS JOIN
        Master.sys.SysColumns t2 

--===== Fill in the Year and Quarter columns from the Date column
 UPDATE #SomeTable3
    SET Year    = DATEPART(yy,Date),
        Quarter = DATEPART(qq,Date)

--===== A table is not properly formed unless a Primary Key has been assigned
     -- Takes about 1 second to execute.
  ALTER TABLE #SomeTable3
        ADD PRIMARY KEY CLUSTERED (RowNum)
CREATE NONCLUSTERED INDEX IX_#SomeTable3_Cover1 
    ON dbo.#SomeTable3 (Company, Year)
       INCLUDE (Amount, Quantity, Quarter) 
GO
    SET STATISTICS TIME OFF
    SET STATISTICS IO OFF
GO
---------------------------------------------------------------------------------------------------
--===== "Normal" Cross Tab
  PRINT REPLICATE('=',100)
  PRINT '=============== "Normal" Cross Tab ==============='
    SET STATISTICS IO ON
    SET STATISTICS TIME ON
 SELECT Company,
        Year,
        SUM(CASE WHEN Quarter = 1 THEN Amount   ELSE 0 END) AS Q1Amt,
        SUM(CASE WHEN Quarter = 1 THEN Quantity ELSE 0 END) AS Q1Qty,
        SUM(CASE WHEN Quarter = 2 THEN Amount   ELSE 0 END) AS Q2Amt,
        SUM(CASE WHEN Quarter = 2 THEN Quantity ELSE 0 END) AS Q2Qty,
        SUM(CASE WHEN Quarter = 3 THEN Amount   ELSE 0 END) AS Q3Amt,
        SUM(CASE WHEN Quarter = 3 THEN Quantity ELSE 0 END) AS Q3Qty,
        SUM(CASE WHEN Quarter = 4 THEN Amount   ELSE 0 END) AS Q4Amt,
        SUM(CASE WHEN Quarter = 4 THEN Quantity ELSE 0 END) AS Q4Qty,
        SUM(Amount)   AS TotalAmt,
        SUM(Quantity) AS TotalQty
   FROM #SomeTable3
  GROUP BY Company, Year
  ORDER BY Company, Year
    SET STATISTICS TIME OFF
    SET STATISTICS IO OFF
---------------------------------------------------------------------------------------------------
--===== "Normal" Pivot
  PRINT REPLICATE('=',100)
  PRINT '=============== "Normal" Pivot ==============='
    SET STATISTICS IO ON
    SET STATISTICS TIME ON
 SELECT amt.Company,
        amt.Year,
        COALESCE(amt.[1],0) AS Q1Amt,
        COALESCE(qty.[1],0) AS Q1Qty,
        COALESCE(amt.[2],0) AS Q2Amt,
        COALESCE(qty.[2],0) AS Q2Qty,
        COALESCE(amt.[3],0) AS Q3Amt,
        COALESCE(qty.[3],0) AS Q3Qty,
        COALESCE(amt.[4],0) AS Q4Amt,
        COALESCE(qty.[4],0) AS Q5Qty,
        COALESCE(amt.[1],0)+COALESCE(amt.[2],0)+COALESCE(amt.[3],0)+COALESCE(amt.[4],0) AS TotalAmt,
        COALESCE(qty.[1],0)+COALESCE(qty.[2],0)+COALESCE(qty.[3],0)+COALESCE(qty.[4],0) AS TotalQty
   FROM (SELECT Company, Year, Quarter, Amount FROM #SomeTable3) t1
        PIVOT (SUM(Amount) FOR Quarter IN ([1], [2], [3], [4])) AS amt
  INNER JOIN 
        (SELECT Company, Year, Quarter, Quantity FROM #SomeTable3) t2
        PIVOT (SUM(Quantity) FOR Quarter IN ([1], [2], [3], [4])) AS qty
     ON qty.Company = amt.Company 
    AND qty.Year    = amt.Year         
  ORDER BY amt.Company, amt.Year
    SET STATISTICS TIME OFF
    SET STATISTICS IO OFF
---------------------------------------------------------------------------------------------------
--===== "Pre-aggregated" Cross Tab
  PRINT REPLICATE('=',100)
  PRINT '=============== "Pre-aggregated" Cross Tab ==============='
    SET STATISTICS IO ON
    SET STATISTICS TIME ON
 SELECT Company,
        Year,
        SUM(CASE WHEN Quarter = 1 THEN Amount   ELSE 0 END) AS Q1Amt,
        SUM(CASE WHEN Quarter = 1 THEN Quantity ELSE 0 END) AS Q1Qty,
        SUM(CASE WHEN Quarter = 2 THEN Amount   ELSE 0 END) AS Q2Amt,
        SUM(CASE WHEN Quarter = 2 THEN Quantity ELSE 0 END) AS Q2Qty,
        SUM(CASE WHEN Quarter = 3 THEN Amount   ELSE 0 END) AS Q3Amt,
        SUM(CASE WHEN Quarter = 3 THEN Quantity ELSE 0 END) AS Q3Qty,
        SUM(CASE WHEN Quarter = 4 THEN Amount   ELSE 0 END) AS Q4Amt,
        SUM(CASE WHEN Quarter = 4 THEN Quantity ELSE 0 END) AS Q4Qty,
        SUM(Amount)   AS TotalAmt,
        SUM(Quantity) AS TotalQty
   FROM (SELECT Company,Year,Quarter,SUM(Amount) AS Amount,SUM(Quantity) AS Quantity
           FROM #SomeTable3 GROUP BY Company,Year,Quarter) d
  GROUP BY Company, Year
  ORDER BY Company, Year
    SET STATISTICS TIME OFF
    SET STATISTICS IO OFF
---------------------------------------------------------------------------------------------------
--===== "Pre-aggregated" Pivot
  PRINT REPLICATE('=',100)
  PRINT '=============== "Pre-aggregated" Pivot ==============='
    SET STATISTICS IO ON
    SET STATISTICS TIME ON
 SELECT amt.Company,
        amt.Year,
        COALESCE(amt.[1],0) AS Q1Amt,
        COALESCE(qty.[1],0) AS Q1Qty,
        COALESCE(amt.[2],0) AS Q2Amt,
        COALESCE(qty.[2],0) AS Q2Qty,
        COALESCE(amt.[3],0) AS Q3Amt,
        COALESCE(qty.[3],0) AS Q3Qty,
        COALESCE(amt.[4],0) AS Q4Amt,
        COALESCE(qty.[4],0) AS Q5Qty,
        COALESCE(amt.[1],0)+COALESCE(amt.[2],0)+COALESCE(amt.[3],0)+COALESCE(amt.[4],0) AS TotalAmt,
        COALESCE(qty.[1],0)+COALESCE(qty.[2],0)+COALESCE(qty.[3],0)+COALESCE(qty.[4],0) AS TotalQty
   FROM (SELECT Company, Year, Quarter, SUM(Amount) AS Amount FROM #SomeTable3 GROUP BY Company, Year, Quarter) t1
        PIVOT (SUM(Amount) FOR Quarter IN ([1], [2], [3], [4])) AS amt
  INNER JOIN 
        (SELECT Company, Year, Quarter, SUM(Quantity) AS Quantity FROM #SomeTable3 GROUP BY Company, Year, Quarter) t2
        PIVOT (SUM(Quantity) FOR Quarter IN ([1], [2], [3], [4])) AS qty
     ON qty.Company = amt.Company 
    AND qty.Year    = amt.Year         
  ORDER BY amt.Company, amt.Year
    SET STATISTICS TIME OFF
    SET STATISTICS IO OFF
---------------------------------------------------------------------------------------------------
--===== "Pre-aggregated" Cross Tab with CTE
  PRINT REPLICATE('=',100)
  PRINT '=============== "Pre-aggregated" Cross Tab with CTE ==============='
    SET STATISTICS IO ON
    SET STATISTICS TIME ON
;WITH
ctePreAgg AS
(SELECT Company,Year,Quarter,SUM(Amount) AS Amount,SUM(Quantity) AS Quantity
   FROM #SomeTable3 
  GROUP BY Company,Year,Quarter
)
 SELECT Company,
        Year,
        SUM(CASE WHEN Quarter = 1 THEN Amount   ELSE 0 END) AS Q1Amt,
        SUM(CASE WHEN Quarter = 1 THEN Quantity ELSE 0 END) AS Q1Qty,
        SUM(CASE WHEN Quarter = 2 THEN Amount   ELSE 0 END) AS Q2Amt,
        SUM(CASE WHEN Quarter = 2 THEN Quantity ELSE 0 END) AS Q2Qty,
        SUM(CASE WHEN Quarter = 3 THEN Amount   ELSE 0 END) AS Q3Amt,
        SUM(CASE WHEN Quarter = 3 THEN Quantity ELSE 0 END) AS Q3Qty,
        SUM(CASE WHEN Quarter = 4 THEN Amount   ELSE 0 END) AS Q4Amt,
        SUM(CASE WHEN Quarter = 4 THEN Quantity ELSE 0 END) AS Q4Qty,
        SUM(Amount)   AS TotalAmt,
        SUM(Quantity) AS TotalQty
   FROM ctePreAgg
  GROUP BY Company, Year
  ORDER BY Company, Year
    SET STATISTICS TIME OFF
    SET STATISTICS IO OFF
---------------------------------------------------------------------------------------------------
--===== "Pre-aggregated" Pivot with CTE
  PRINT REPLICATE('=',100)
  PRINT '=============== "Pre-aggregated" Pivot with CTE ==============='
    SET STATISTICS IO ON
    SET STATISTICS TIME ON
;WITH
ctePreAgg AS
(SELECT Company,Year,Quarter,SUM(Amount) AS Amount,SUM(Quantity) AS Quantity
   FROM #SomeTable3 
  GROUP BY Company,Year,Quarter
)
 SELECT amt.Company,
        amt.Year,
        COALESCE(amt.[1],0) AS Q1Amt,
        COALESCE(qty.[1],0) AS Q1Qty,
        COALESCE(amt.[2],0) AS Q2Amt,
        COALESCE(qty.[2],0) AS Q2Qty,
        COALESCE(amt.[3],0) AS Q3Amt,
        COALESCE(qty.[3],0) AS Q3Qty,
        COALESCE(amt.[4],0) AS Q4Amt,
        COALESCE(qty.[4],0) AS Q5Qty,
        COALESCE(amt.[1],0)+COALESCE(amt.[2],0)+COALESCE(amt.[3],0)+COALESCE(amt.[4],0) AS TotalAmt,
        COALESCE(qty.[1],0)+COALESCE(qty.[2],0)+COALESCE(qty.[3],0)+COALESCE(qty.[4],0) AS TotalQty
   FROM (SELECT Company, Year, Quarter, Amount FROM ctePreAgg) AS t1
        PIVOT (SUM(Amount) FOR Quarter IN ([1], [2], [3], [4])) AS amt
  INNER JOIN 
        (SELECT Company, Year, Quarter, Quantity FROM ctePreAgg) AS t2
        PIVOT (SUM(Quantity) FOR Quarter IN ([1], [2], [3], [4])) AS qty
     ON qty.Company = amt.Company 
    AND qty.Year    = amt.Year         
  ORDER BY amt.Company, amt.Year
    SET STATISTICS TIME OFF
    SET STATISTICS IO OFF

The test code was executed 10 times each for 10k, 100k, and 1 million rows both with and without the index created at the beginning of the code. The averaged results, calculated from a profiler table (not included in the code), are fascinating. The light green cells indicate the fastest run times or the least number of reads. The light blue indicate the second place for the same thing...

Zoom in | Open in new window

Notice that even for "normal" Cross Tabs and Pivots that the only place a Pivot wins in any category is in the paltry 10k row test. The Cross Tab wins everywhere else. That's good news for SQL Server 2000 users because you won't want to change your code if and when you upgrade to SQL Server 2005. Using CTE's helps a bit but not as much as pre-aggregation on the larger row counts does. Again, that's good news for SQL Server 2000 users.

Review

In this article, we learned the basis of how to change rows to columns using both Cross Tabs and Pivot. We've discovered that Cross Tabs are nothing more than simple aggregations that have a built in selection condition in the form of a simple Case statement. We've seen how to use a Pivot to do the same thing as a Cross Tab and, in the process, discovered that they're a bit more complicated to create, read, and understand especially when compared to the simplicity of the Cross Tab code. We've been introduced to the concept of "pre-aggregation and the fact that pre-aggregation can make more complex aggregations both easier to read and to contrive. Through testing, we've found that the Cross Tab beats Pivot code in all but the smallest of tables. Through that same testing, we've also found that pre-aggregation adds a substantial performance gain in all but the smallest of tables.

Last but certainly not least, we've discovered that there's no reason to rewrite properly written Cross Tabs to become Pivots when shifting from SQL Server 2000 to SQL Server 2005. To do so would actually cause a negative impact to performance most of the time.

In the Works...

Coming soon to a forum near you... Dynamic Cross Tabs, EAV/NVP conversions, and more on pre-aggregation.

Thanks for listening folks.

--Jeff Moden

Tuning SQL Server performance via disk arrays and disk partitioning

2008-12-13T01:22:00.000-08:00

One of my collections, about MSSQL Server Tunning. Enjoy it...

Author : Denny Cherry, 08.19.2008

As a DBA, much of your focus is on performance tuning SQL Server. But have you spent time to tune the hardware supporting your SQL Server system? Are you using the optimal disk array configuration? Are the disk partitions aligned? This tip discusses how to get your SQL Server hardware performance in top shape – whether the system is already in operation or it's a new setup.

With the massive amount of raw horse power available in today's server class hardware, it's easy to skip over the hardware when it comes to performance tuning the SQL Server database. After all, with so much power available, who cares if something takes a few extra milliseconds to complete? Is anyone really going to notice that extra millisecond?

But what happens when you perform an operation that takes 10 extra milliseconds to complete; and it needs to perform 100 times an hour, for a year? All of a sudden, that 10 milliseconds turns into 2.4 hours. If you perform that operation 1,000 times an hour -- which isn't all that unheard of in a smaller OLTP database -- you are now looking at more than 24 hours of wasted time.

In my particular environment, we run the same stored procedure at least 2,000 times per minute. If that stored procedure takes an extra 10 milliseconds to complete, we are looking at eight hours of lost time daily, or 121 days of lost time per year.

Tune SQL Server by tuning disk arrays

There are a few places to check hardware components when tuning your SQL Server system. The easiest components to check are disk arrays. Typically, disk arrays are where the most time is expended

More on tuning SQL Server performance and hardware:

Determining SQL Server database storage requirements

Optimize disk configuration in SQL Server

Storage area network (SAN) basics every DBA must know

waiting for something to happen. There are a couple of ways to tune the disks to improve SQL Server performance. The first is to make sure your disk array has enough spindles to handle the workload that will be placed on it. Second, make sure the disk arrays are in the correct RAID level to offer the best support level for the database.

While it is true that RAID 10 offers better write performance, in most cases, RAID 10 isn't required for the data files. That said, you should use RAID 10 for your transaction logs and tempdb database, as they are mostly write files. The reason I say not to use RAID 10 for all database files is that RAID 10 is very costly to implement in large disk sizes. This is because for each spindle used for data, a second spindle is used for redundancy.

Finding out if you need more spindles on an existing system is easy. Open Performance Monitor on the server and add the "Physical Disk" object and the "Current Disk Queue Length" counter. Some queuing is OK; however, there is a tipping point. To find out where the tipping point of "OK queuing" and "too much queuing" is, take the number of disks in the array and multiply it by two. If the result is greater than the maximum value in Performance Monitor, then you have too much queuing. When we talk about the number of disks, we're referring to the number of disks that are actively working with data. If you have a RAID 10 array, this is half the number of disks in the array.

"Number of Disks" x 2 = Maximum Allowable Queuing

How to configure the disk array on your new SQL Server system

When working on a new system without any load on it, making sure you configure your disk array correctly is a little more challenging. If you have another system with the same amount of load on it, you can use that system as a guide. However, if this is the first large system in your environment, then getting it correct can be a bit harder.

You'll need to look at your database system and the expected transactions per second, and make an educated guess on how many spindles you'll need. When dealing with high-end drives, expect each drive to give you about 100 IOPs to 120 IOPs per second per disk in an OLTP environment. When dealing with SATA drives, expect each drive to give you about 60 IOPs to 80 IOPs per second per disk in an OLTP environment. Those numbers will go up when working in an OLAP environment because OLAP databases put a different kind of load on the disk system. It's a more sequential load, whereas OLTP databases put a random load on the disks.

Disk partition alignment improves SQL Server performance

Once you set up your disk array, you'll want to make sure the partition you create is correctly aligned. By default, when Windows (or any other operating system for that matter) creates the partition on a disk or array, the partition is not correctly aligned for peak performance. Disk drives are made up of 1K blocks. The physical disks like to do all their operations in 64-block chunks called clusters. Conveniently, SQL Server likes to do all its operations in 64 K operations -- there are eight 8K blocks in each extent, and SQL does its reads one extent at a time. When the partition is created, the boot sector is created at the beginning of the partition. This boot sector is 32 K in size, causing the 64K operations to be spread between two 64K clusters. This then causes each logical operation to take twice as many physical operations as needed.

You can see your current alignment offset by using the diskpart application. Open a command prompt and run diskpart.exe. When you are prompted with a DISKPART> prompt, type SELECT DISK n where n is the disk number you want to look at -- the command LIST DISK will give you a list of disks in the machine. After selecting the disk, type in "LIST PARTITION" to get the partition information, including the offset.

Figure 1: Run the DISKPART application to view disk alignment.

In order to create the partition, you'll need to use the CREATE PARTITION command. The full command is CREATE PARTITION PRIMARY ALIGN=64. This creates the new partition with the 64K offset, aligning the partition into the optimum position for maximum performance.

Check out part two in this tip series, configuring memory and CPU processing for improved SQL Server performance.

Tuning SQL Server performance via memory and CPU processing

2008-12-13T01:11:00.000-08:00

After a long journey browsing on internet, finally I found this interesting and usefull article for tunning our MSSQL Database Server. So in this place i re posting to share with you guys.... enjoy it ;)

Author : Denny Cherry, 09.09.2008
In my previous tip, Tuning SQL Server performance via disk arrays and disk partitioning, we talked about how important it is to ensure that your storage was set up correctly to optimize SQL Server performance. However, storage isn't the only part of SQL Server hardware that needs special consideration when designing your infrastructure.

SQL Server memory can also impact performance. While having too much memory in a SQL Server system is a waste of money, having too little memory is extremely detrimental to performance. Unfortunately, determining when you need more memory in the system can be a bit tricky. When memory problems begin, you'll start to see an increase in disk I/O, as well as an increase in disk queuing. You'll also see a decrease in the buffer cache hit ratio and page life expectancy. As memory requirements increase, you may begin to see these error messages in the log file:

A significant part of SQL Server process memory has been paged out. This may result in a performance degradation. Duration: %n seconds. Working set (KB): %w, committed (KB): %c, memory utilization: %u.

SQL Server has encountered %o occurrence(s) of IO requests taking longer than 15 seconds to complete on file [%f] in database [%d] (%i). The OS file handle is %h. The offset of the latest long IO is: %l.

Unfortunately, this is not the only time these errors are reported, so you have to use them along with the performance monitor metrics to determine that memory is actually low.

When dealing with SQL Server memory issues, there are a few options to resolve the problems. The easiest solution is to increase server memory, which increases the amount of buffer

More on improving SQL Server performance:

Memory configurations for procedure cache and buffer cache

Configuring SQL Server memory settings

Clustered and non-clustered indexes in SQL Server

cache available. This adds to the amount of data in memory and reduces your disk I/O. Other potential solutions include removing clustered indexes for extremely large tables and using only nonclustered indexes for the table, including the Primary Key.

This will only make a difference when the clustered index is being used for lookups, and clustered index seeks are used. If another index is in use, it will not relieve any memory pressure, as the clustered index won't be in memory. If you're using clustered index scans, then this turns into table scans that load the table into memory instead of the index. If clustered index scans are being performed, then a new nonclustered index may help the situation without removing the index.

How to monitor CPU queuing

The CPU is another piece of hardware that can cause potential performance problems. Most people only look at the speed of or number of CPUs. However, just like disks, CPUs can become bottlenecked. If there is a CPU bottleneck, you may not even see the CPU performance at 100%. CPUs have command queues in much the same way that disks have I/O queues. Commands are loaded into a CPU queue and the operation waits for the CPU to become available before performing the operation. As CPUs became faster, we could do things much faster within the CPU, but we could still only do the same number of things at one time. Now, as dual-core, tri-core and quad-core CPUs become available, we can process more commands at one time.

You can monitor your CPU queue using SQL Server Performance Monitor. You'll find PerfMon under the System object, with the counter name "Processor Queue Length." Pretty much any queue length other than zero indicates a need to increase the number of operations that SQL Server can perform at any one time. It doesn't indicate a need for faster CPUs, but a need for more CPU cores. Today's newest servers support 32 cores per server, and some of the most advanced servers support up to 64 cores -- when chases are scaled together support for 64 cores can be built (available only from certain vendors).

In parts one and two, I've pointed out a variety of places within the hardware that impact whether your SQL Server system will run at peak performance. These tips are not the be-all, end-all solutions to performance problems. Table design and index tuning always have been and will continue to be extremely important. Today's SQL Server is expected to do more work for more hours of the day, which makes hardware tuning more important to the success of the database platform. With these tools in your arsenal to combat performance problems, you'll be able to get every ounce of performance from the existing hardware with no or minimal hardware upgrades to the platform. But when you do need to make those purchasing decisions, use these tips to make the correct purchasing decisions to get the most upgrade for your dollars spent.

Export data from SQL Server to Excel

2008-09-23T21:57:00.000-07:00

Problem
Exporting data from SQL Server to Excel seems like a reasonably simple request. I just need to write out a few reports for users on a regular basis, nothing too fancy, the same basic report with a few different parameters. What native SQL Server options are available to do so? Do I need to learn another tool or can I use some T-SQL commands? Does SQL Server 2005 offer any new options to enhance this process?

..Read More

Solution
Exporting data from SQL Server to Excel can be achieved in a variety of ways. Some of these options include Data Transformation Services (DTS), SQL Server Integration Services (SSIS) and Bulk Copy (BCP). Data Transformation Services (SQL Server 2000) and SQL Server Integration Services (SQL Server 2005) offers a GUI where widgets can be dragged and dropped Each option has advantages and disadvantages, but all can do the job. It is just a matter of your comfort level with the tools and the best solution to meet the need.

Another option that is available directly via the T-SQL language is the OPENROWSET command (SQL Server 2000 and SQL Server 2005). This command can be called directly in any stored procedure, script or SQL Server Job from T-SQL. Below outlines the full syntax available:

Source - SQL Server 2005 Books Online

Below is a simple example of writing out the Job name and date to Sheet1 of an Excel spreadsheet in either SQL Server 2005 or 2000:

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\testing.xls;',
'SELECT Name, Date FROM [Sheet1$]')
SELECT [Name], GETDATE() FROM msdb.dbo.sysjobs
GO

Using the OPENROWSET command creates two caveats. The first caveat is the need to have an Excel spreadsheet serve as a template in the needed directory with the correct worksheets and columns. Without this the you would receive an error message. The second caveat is that it is necessary to enable the OPENROWSET command with the SQL Server 2005 Surface Area Configuration utility. Note - This step is not needed for SQL Server 2000. With the loop example you could copy and paste the Excel spreadsheet and load the data as needed.

Although the example above is very simple, you could integrate this simple example into your code. For example, if you had the need to write out a number of reports in the same format, you could loop (WHILE loop or Cursor) over the records and write them out to different Excel spreadsheets based on the name or report type. In addition, you could integrate this code with either SQL Server 2000 mail or Database mail (SQL Server 2005) and mail the results to the users with very little effort and build the process entirely with T-SQL.

Next Steps

Depending on how your users analyze data and the software infrastructure you have in place, Excel may be a simple and easy solution for your users to receive their data in a familiar interface for them to conduct the needed analysis. I am surprised by the sophisticated Excel infrastructure at some organizations, but these sorts of applications seem to grow organically.
The next time you are faced with exporting SQL Server data to Excel take a step back and understand the requirements then determine which of the following options best meets the need:
- Data Transformation Services
- SQL Server Integration Services
- Bulk Copy (BCP)
- OPENROWSET
Check out the following MSSQLTips.com tips related to data import\export:

Getting IO and time statistics for SQL Server queries

2008-09-23T21:44:00.000-07:00

Problem
If you're like any other DBA or developer in the world, writing sound T-SQL code and performance tuning is one of your primary duties. There are many tools, both native to SQL Server and third-party, that assist you in this endeavor. But in thinking about writing and tuning queries, what should the focus be? For many, the length of time taken to execute the query is "good enough". One focus should be on the resources used by the server, since the length of time taken to execute the query can vary based on other server activity. In addition to using Profiler and Execution Plans, consider using SET STATISTICS IO and SET STATISTICS TIME.

Solution
SET STATISTICS IO and SET STATISTICS TIME are two settings that can help you measure the absolute resources needed by a server during query execution. SET STATISTICS IO displays statistics on the amount of disk activity generated by the query. SET STATISTICS TIME displays the amount of time needed to parse, compile, and execute each statement in the query. By writing a query a few different ways you can compare the statistics and determine the best statement to deploy to production.

Turning these settings on can be done one of two ways. First this can be done by using the SET commands to turn on and off these options or you can turn these options on and off by using the option settings in Query Analyzer.

Before you run the commands you need to turn on these settings and then issue your SQL statements. In addition, once these are turned on you will get these statistics for all queries for the current session you are working in until you have turned these settings off.

TRANSACT-SQL STATEMENT (added at the beginning of the statement).

Here is an example of turning the STATISTICS IO on and off.

-- turn on statistics IO
SET STATISTICS IO ON
GO

-- your query goes here
SELECT * FROM Employee
GO

-- turn off statistics IO
SET STATISTICS IO OFF
GO

Here is an example of the output with SET STATISTICS IO turned on (the query statement evaluated is a cursor). From this we can see that there are several iterations for each time the cursor loops through the data. Each of these shows how many pages are accessed for each process that SQL Server executes.

Here is an example of the output with SET STATISTICS TIME turned on. This output shows the time in milliseconds for each operation to complete. These times could vary depending on when this is run, the load on the system as well as the size of the data set.

WITHIN QUERY ANALYZER
To set the STATISTICS IO and STATISTICS TIME options inside Query Analyzer, click Tools -> Options -> Connection Properties and then check either or both "Set statistics time" or "Set statistics IO".

There you have it. Two more additional pieces of information that can be used to determine how your queries are executing. The data you get from the execution plan is helpful, but these additional pieces of information provide additional insight as to how your queries are performing.

Next Steps

When testing or tuning queries, use the SET STATISTICS IO and TIME options to shed more light on what server resources are being used

Read more information about setting STATISTICS IO options in SQL Server 2000 and STATISTICS IO options in SQL Server 2005

Read more information about setting STATISTICS TIME options in SQL Server 2000 and STATISTICS TIME options in SQL Server 2005

Read information on Analyzing a Query in SQL Server 2005

Find more great tips on Query Optimization and Performance Tuning on MSSQLTIPS.com!

Thanks go out to Tim Cullen for providing this tip

Gathering I/O statistics down to the SQL Server database file level

2008-09-23T21:41:00.000-07:00

Problem
When managing your SQL Server environment there are many aspects that need to be reviewed to determine where the bottlenecks are occurring to ensure you are getting the best performance possible. SQL Server offers many great tools and functions to determine issues with locking, blocking, fragmentation, missing indexes, deadlocks, etc... In addition, to looking at all of these areas another area of concern is I/O. Disk I/O can be tracked at the OS level by using counters in Performance Monitor, but these counters give you an overall picture of what is occurring on the server. What other options are available to look at I/O related information down to the file level for each database?

Solution
As mentioned above, SQL Server offers many great little functions and utility programs to help you gain an insight as to what is occurring on the server. One of these tools is fn_virtualfilestats.

This function, fn_virtualfilestats allows you to get information for each physical file that is being used to hold your data including both the data and log files. The function returns read and write information as well as stall information, which is the time users had to wait for an I/O operation to complete. Each time this function is called it returns the overall numbers that SQL Server has collected since the last time the database engine was started, so to use this effectively you need to gather data from two different points of time and then do a comparison.

To run this function to get data for all databases and all files this can be done as easily as this:

SQL 2005

SELECT * FROM fn_virtualfilestats(NULL,NULL);

SQL 2000

SELECT * FROM :: fn_virtualfilestats(-1, -1)

The output for SQL 2000 and 2005 is pretty much the same, but some additional columns have been added for SQL Server 2005.

Column Name	Notes	Description
DbId		Database ID.
FileId		File ID.
TimeStamp		Database timestamp at which the data was taken.
NumberReads		Number of reads issued on the file.
BytesRead		Number of bytes read issued on the file.
IoStallReadMS	SQL2005 only	Total amount of time, in milliseconds, that users waited for the read I/Os to complete on the file.
NumberWrites		Number of writes made on the file.
BytesWritten		Number of bytes written made on the file.
IoStallWriteMS	SQL2005 only	Total amount of time, in milliseconds, that users waited for the write I/Os to complete on the file.
IoStallMS		Sum of IoStallReadMS and IoStallWriteMS.
FileHandle		Value of the file handle.
BytesOnDisk	SQL2005 only	Physical file size (count of bytes) on disk. For database files, this is the same value as size in sys.database_files, but is expressed in bytes rather than pages. For database snapshot spare files, this is the space the operating system is using for the file.

(Source SQL Server 2005 Books Online)

Sample Output

As you can see from the sample output, the Dbid and FileId columns are pretty cryptic. The Dbid can be be translated to the database name pretty easily by using the DB_NAME() function, but the fileId needs to be looked up from one of the system tables.

To lookup the filename from the system tables you can use these queries.

SQL 2005

SELECT dbid, fileid, filename
FROM sys.sysaltfiles
WHERE dbid = 5 and fileid in (1,2)

SQL 2000

SELECT dbid, fileid, filename
FROM dbo.sysaltfiles
WHERE dbid = 5 and fileid in (1,2)

Here is sample output.

From just using this function directly you can gather data from two different points in time and then do a comparison to determine the change that has occurred between these two periods of time. Here is a sample query that gathers data, waits for a period of time and then gathers data again to show you a comparison.

This example is written for SQL Server 2005, but can easily be changed for SQL 2000.

USE master
GO

-- create table
IF NOT EXISTS (SELECT * 
       FROM sys.objects 
       WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[filestats]') 
       AND type IN (N'U'))
BEGIN
   CREATE TABLE filestats
    (dbname  VARCHAR(128),
    fName  VARCHAR(2048), 
    timeStart  datetime,
    timeEnd datetime,
    timeDiff bigint,
    readsNum1 bigint,
    readsNum2 bigint,
    readsBytes1 bigint,
    readsBytes2 bigint,
    readsIoStall1 bigint,
    readsIoStall2 bigint,
    writesNum1 bigint,
    writesNum2 bigint,
    writesBytes1 bigint,
    writesBytes2 bigint,
    writesIoStall1 bigint,
    writesIoStall2 bigint,
    ioStall1 bigint,
    ioStall2 bigint
    )
END

-- clear data
TRUNCATE TABLE dbo.filestats

-- insert first segment counters
INSERT INTO dbo.filestats
   (dbname,
   fName, 
   TimeStart,
   readsNum1,
   readsBytes1,
   readsIoStall1, 
   writesNum1,
   writesBytes1,
   writesIoStall1, 
   IoStall1
   )
SELECT 
   DB_NAME(a.dbid) AS Database_name,
   b.filename,
   GETDATE(),
   numberReads,
   BytesRead,
   IoStallReadMS,
   NumberWrites,
   BytesWritten,
   IoStallWriteMS,
   IoStallMS
FROM 
   fn_virtualfilestats(NULL,NULL) a INNER JOIN
   sysaltfiles b ON a.dbid = b.dbid AND a.fileid = b.fileid
ORDER BY 
   Database_Name

/*Delay second read */
WAITFOR DELAY '000:01:00'

-- add second segment counters
UPDATE dbo.filestats 
SET 
   timeEnd = GETDATE(),
   readsNum2 = a.numberReads,
   readsBytes2 = a.BytesRead,
   readsIoStall2 = a.IoStallReadMS ,
   writesNum2 = a.NumberWrites,
   writesBytes2 = a.BytesWritten,
   writesIoStall2 = a.IoStallWriteMS,
   IoStall2 = a.IoStallMS,
   timeDiff = DATEDIFF(s,timeStart,GETDATE())
FROM 
   fn_virtualfilestats(NULL,NULL) a INNER JOIN
   sysaltfiles b ON a.dbid = b.dbid AND a.fileid = b.fileid
WHERE   
   fName= b.filename AND dbname=DB_NAME(a.dbid)

-- select data
SELECT 
   dbname,
   fName,
   timeDiff,
   readsNum2 - readsNum1 AS readsNumDiff,
   readsBytes2 - readsBytes2 AS readsBytesDiff,
   readsIoStall2 - readsIOStall1 AS readsIOStallDiff,
   writesNum2 - writesNum1 AS writesNumDiff,
   writesBytes2 - writesBytes2 AS writesBytesDiff,
   writesIoStall2 - writesIOStall1 AS writesIOStallDiff,   
   ioStall2 - ioStall1 AS ioStallDiff
FROM dbo.filestats

Summary
One problem that you may be faced with though is that not all files are stored on their own physical disks, so you may have a case where you want to look at things from a drive perspective vs. at an individual file level. Here is a previous article written by Andy Novick that has the entire process broken down into functions, so you can aggregate things to a drive perspective. The article can be found here, Examining SQL Server's I/O Statistics

Next Steps

When researching performance problems, don't forget to look at I/O stats as well. This handy little function could give you big insight into some of your performance issues.
Stay tuned for more performance related tips, but for now check out these other tips.

How To Collect Performance Data With TYPEPERF.EXE

2008-09-23T18:20:00.000-07:00

Problem
As a DBA I like to take advantage of command line tools when I'm working on performance tuning and optimizing my SQL Server databases. One of the things I typically need to do is to collect performance data on the server which includes CPU, memory and disk utilization as well as SQL Server-specific data. What command line tools are available to do this?

Solution

TYPEPERF.EXE is a command line tool included with the Windows operating system that writes performance data to the command window or to a file. It is necessary to capture performance data whenever you are trying to diagnose performance issues on a server. Performance data provides information on the server's utilization of the processor, memory, and disk, as well as SQL Server-specific performance data.

The Windows operating system supplies a plethora of performance data in the form of objects which have associated counters. As an example SQL Server provides the SQLServer:General Statistics object which reports the details on logins, logouts, database connections, etc. Some objects break out the counters into specific instances. As an example the SQLServer:Databases object provides details on data file and transaction log file sizes, the percentage of the transaction log in use, active transactions, etc. for each database. You can specify a single database or all databases combined together as the instance. Unfortunately the term "instance" has a different connotation in SQL Server; i.e. a named instance.

As is typical with command line tools, there are many options available which allow you to fine-tune how you would like to use the tool. Open a command prompt and enter TYPEPERF -? and you will see the following output:

Usage:
typeperf { <counter [counter ...]>
                            | -cf <filename>
                            | -q [object]
                            | -qx [object]
                            } [options]
Parameters:
<counter [counter ...]>       Performance counters to monitor.
Options:
-?                            Displays context sensitive help.
-f <CSV|TSV|BIN|SQL>          Output file format. Default is CSV.
-cf <filename>                File containing performance counters to
                            monitor, one per line.
-si <[[hh:]mm:]ss>            Time between samples. Default is 1 second.
-o <filename>                 Path of output file or SQL database. Default
                            is STDOUT.
-q [object]                   List installed counters (no instances). To
                            list counters for one object, include the
                            object name, such as Processor.
-qx [object]                  List installed counters with instances. To
                            list counters for one object, include the
                            object name, such as Processor.
-sc <samples>                 Number of samples to collect. Default is to
                            sample until CTRL+C.
-config <filename>            Settings file containing command options.
-s <computer_name>            Server to monitor if no server is specified
                            in the counter path.
-y                            Answer yes to all questions without prompting.

The ultimate goal of using TYPEPERF is to capture performance data in a repeatable way; e.g. specify your options in a batch file that you can execute as required. The default is to display the performance data in the command window; alternatively you can use the -f option to specify a CSV file (comma separated values), TSV file (tab separated values), etc.

To get started let's figure out what performance objects are available then setup TYPEPERF to capture some performance data. There are two options that you can use to get the list of performance objects on a particular machine:

-q [object] lists the installed counters without the instances
-qx [object] list the counters including the instances

In both cases [object] is an optional parameter which filters the list to just that object. The default is to query the performance objects on your current machine; you can include -s <computer name> to specify another machine. To get the list of counters for the SQL Server Buffer Manager object enter the following command:

TYPEPERF -q "SQLServer:Buffer Manager"

You will see output similar to the following:

\SQLServer:Buffer Manager\Buffer cache hit ratio
\SQLServer:Buffer Manager\Page lookups/sec
\SQLServer:Buffer Manager\Free list stalls/sec
\SQLServer:Buffer Manager\Free pages
\SQLServer:Buffer Manager\Total pages
\SQLServer:Buffer Manager\Target pages
\SQLServer:Buffer Manager\Database pages
\SQLServer:Buffer Manager\Reserved pages
\SQLServer:Buffer Manager\Stolen pages
\SQLServer:Buffer Manager\Lazy writes/sec
\SQLServer:Buffer Manager\Readahead pages/sec
\SQLServer:Buffer Manager\Page reads/sec
\SQLServer:Buffer Manager\Page writes/sec
\SQLServer:Buffer Manager\Checkpoint pages/sec
\SQLServer:Buffer Manager\AWE lookup maps/sec
\SQLServer:Buffer Manager\AWE stolen maps/sec
\SQLServer:Buffer Manager\AWE write maps/sec
\SQLServer:Buffer Manager\AWE unmap calls/sec
\SQLServer:Buffer Manager\AWE unmap pages/sec
\SQLServer:Buffer Manager\Page life expectancy

To get a list of counters with instances enter the following command:

TYPEPERF -qx "SQLServer:Databases" | FIND "tempdb"

You will see output similar to the following:

\SQLServer:Databases(tempdb)\Data File(s) Size (KB)
\SQLServer:Databases(tempdb)\Log File(s) Size (KB)
\SQLServer:Databases(tempdb)\Log File(s) Used Size (KB)
\SQLServer:Databases(tempdb)\Percent Log Used
\SQLServer:Databases(tempdb)\Active Transactions
\SQLServer:Databases(tempdb)\Transactions/sec
\SQLServer:Databases(tempdb)\Repl. Pending Xacts
\SQLServer:Databases(tempdb)\Repl. Trans. Rate
\SQLServer:Databases(tempdb)\Log Cache Reads/sec
\SQLServer:Databases(tempdb)\Log Cache Hit Ratio
\SQLServer:Databases(tempdb)\Bulk Copy Rows/sec
\SQLServer:Databases(tempdb)\Bulk Copy Throughput/sec
\SQLServer:Databases(tempdb)\Backup/Restore Throughput/sec
\SQLServer:Databases(tempdb)\DBCC Logical Scan Bytes/sec
\SQLServer:Databases(tempdb)\Shrink Data Movement Bytes/sec
\SQLServer:Databases(tempdb)\Log Flushes/sec
\SQLServer:Databases(tempdb)\Log Bytes Flushed/sec
\SQLServer:Databases(tempdb)\Log Flush Waits/sec
\SQLServer:Databases(tempdb)\Log Flush Wait Time
\SQLServer:Databases(tempdb)\Log Truncations
\SQLServer:Databases(tempdb)\Log Growths
\SQLServer:Databases(tempdb)\Log Shrinks

Instances in this case (-x option) report the performance counters for the SQLServer:Databases object for each SQL Server database (there is also a _Total instance which combines all databases). The above output was filtered to include just the tempdb database by piping to the FIND command. When you are working with a named instance of SQL Server, the performance objects will reflect the SQL Server instance name. For example I am running an instance of SQL Server 2000 Enterprise Edition which is named SQL2000EE; the performance objects are named MSSQL$SQL2000EE instead of SQLServer as shown above.

Use the -q or -qx options to get the list of performance counters, redirect the list to a text file, then edit the file as necessary to get just the performance counters that you want to capture. Include the -cf <filename> option on your TYPEPERF command line to get the list of counters to report on from a text file.

Now we are ready to use TYPEPERF to report some performance data. Here is a sample command:

TYPEPERF -cf MyCounters.txt

The above command will display the counters in the text file MyCounters.txt in the command window every second. Hit Ctrl-C to cancel.

Here is another example:

TYPEPERF -f CSV -o MyCounters.csv -si 15 -cf MyCounters.txt -sc 60

The above example writes the counter values to MyCounters.csv every 15 seconds. It stops after writing out the counters 60 times (i.e. 15 minutes).

An example of the output is shown below in Excel 2007:

The first row has the counter names; the columns do not show the full counter names just to conserve space. The list of counters in MyCounters.txt is:

\SQLServer:Databases(_Total)\DBCC Logical Scan Bytes/sec
\SQLServer:Databases(tempdb)\Percent Log Used
\SQLServer:Buffer Manager\Buffer cache hit ratio
\SQLServer:General Statistics\User Connections
\SQLServer:Locks(_Total)\Lock Requests/sec
\SQLServer:SQL Statistics\Batch Requests/sec

In the above screen shot the custom format used for the Time column is m/d/yyyy h:mm:ss.

Next Steps

Take a look at our earlier tip Creating SQL Server performance based reports using Excel for some helpful hints on formatting the performance data in Excel. I used these to format the data in Excel shown above.
Add TYPEPERF.EXE to your tool box. It provides a simple, repeatable way to quickly start capturing performance data.

Top Tips for Effective Database Maintenance

2008-09-16T08:21:00.000-07:00

Paul S. Randal

At a Glance:

Managing data and transaction log files
Eliminating index fragmentation
Ensuring accurate, up-to-date statistics
Detecting corrupted database pages
Establishing an effective backup strategy

Contents

Several times a week I'm asked for advice on how to effectively maintain a production database. Sometimes the questions come from DBAs who are implementing new solutions and want help

fine-tuning maintenance practices to fit their new databases' characteristics. More frequently, however, the questions come from people who are not professional DBAs but for one reason or another have been given ownership of and responsibility for a database. I like to call this role the "involuntary DBA." The focus of this article is to provide a primer of database maintenance best-practices for all the involuntary DBAs out there.

As with the majority of tasks and procedures in the IT world, there isn't an easy one-size-fits-all solution for effective database maintenance, but there are some key areas that nearly always need to be addressed. My top five areas of concern are (in no particular order of importance):

Data and log file management
Index fragmentation
Statistics
Corruption detection
Backups

An unmaintained (or poorly maintained) database can develop problems in one or more of these areas, which can eventually lead to poor application performance or even downtime and data loss.

In this article, I'll explain why these issues matter and show you some simple ways to mitigate the problems. I will base my explanations on SQL Server® 2005, but I'll also highlight the major differences that you'll find in SQL Server 2000 and the upcoming SQL Server 2008.

Data and Log File Management

The first area I always recommend checking when taking over a database concerns the settings related to data and (transaction) log file management. Specifically, you should make sure that:

The data and log files are separated from each other and isolated from everything else as well
Auto-growth is configured correctly
Instant file initialization is configured
Auto-shrink is not enabled and shrink is not part of any maintenance plan

When data and log files (which ideally should be on separate volumes altogether) share a volume with any other application that creates or expands files, there is the potential for file fragmentation. In data files, excessive file fragmentation can be a small contributing factor in poorly performing queries (specifically those that scan very large amounts of data). In log files, it can have a much more significant impact on performance, especially if auto-growth is set to increase each file size only by a very small amount each time it is needed.

Log files are internally divided into sections called Virtual Log Files (VLFs) and the more fragmentation there is in the log file (I use the singular here because there is no gain from having multiple log files—there should only be one per database), the more VLFs there are. Once a log file has more than, say, 200 VLFs, performance can be negatively impacted for log-related operations such as log reads (for transactional replication/rollback, for example), log backups, and even triggers in SQL Server 2000 (the implementation of triggers changed in SQL Server 2005 to the row versioning framework instead of the transaction log).

The best practice regarding the sizing of data and log files is to create them with an appropriate initial size. For data files, the initial size should take into account the potential for additional data being added to the database in the short-term. For instance, if the initial size of the data is 50GB, but you know that over the next six months an additional 50GB of data will be added, it makes sense to create the data file to be 100GB right away, rather than having to grow it several times to reach that size.

It's a little more complicated for log files, unfortunately, and you need to consider factors like transaction size (long-running transactions cannot be cleared from the log until they complete) and log backup frequency (since this is what removes the inactive portion of the log). For more information, see "8 Steps to Better Transaction Log Throughput", a popular blog post on SQLskills.com written by my wife, Kimberly Tripp.

Once set up, the file sizes should be monitored at various times and proactively grown manually at an appropriate time of day. Auto-grow should be left on as a just-in-case protection so the files can still grow if they need to if some abnormal event occurs. The logic against leaving file management entirely to auto-grow is that auto-grow of small amounts leads to file fragmentation, and that auto-grow can be a time-consuming process that stalls the application workload at unpredictable times.

The auto-grow size should be set to a specific value, rather than a percentage, to bound the time and space needed to perform the auto-grow, if it occurs. For instance, you may want to set a 100GB data file to have a fixed 5GB auto-grow size, rather than, say 10 percent. This means it will always grow by 5GB, no matter how large the file ends up being, rather than an ever-increasing amount (10GB, 11GB, 12GB, and so on) each time the file gets bigger.

When a transaction log is grown (either manually or through auto-grow), it is always zero-initialized. Data files have the same default behavior in SQL Server 2000, but starting with SQL Server 2005, you can enable instant file initialization, which skips zero-initializing the files and hence makes growth and auto-growth virtually instantaneous. Contrary to popular belief, this feature is available in all editions of SQL Server. For more information, enter "instant file initialization" in the index of Books Online for SQL Server 2005 or SQL Server 2008.

Finally, care should be taken that shrink is not enabled in any way. Shrink can be used to reduce the size of a data or log file, but it is a very intrusive, resource-heavy process that causes massive amounts of logical scan fragmentation in data files (see below for details) and leads to poor performance. I changed the SQL Server 2005 Books Online entry for shrink to include a warning to this effect. Manual shrinking of individual data and log files, however, can be acceptable under special circumstances.

Auto-shrink is the worst offender as it starts every 30 minutes in the background and tries to shrink databases where the auto-shrink database option is set to true. It is a somewhat unpredictable process in that it only shrinks databases with more than 25 percent free space. Auto-shrink uses lots of resources and causes performance-dropping fragmentation and so is not a good plan under any circumstances. You should always switch off auto-shrink with:

Copy Code

ALTER DATABASE MyDatabase SET AUTO_SHRINK OFF;

A regular maintenance plan that includes a manual database shrink command is almost as bad. If you find that your database continually grows after the maintenance plan shrinks it, that's because the database needs that space in which to run.

The best thing to do is allow the database to grow to a steady-state size and avoid running shrink altogether. You can find more information on the downsides of using shrink, plus some commentary on the new algorithms in SQL Server 2005 on my old MSDN® blog at blogs.msdn.com/sqlserverstorageengine/archive/tags/Shrink/default.aspx.

Index Fragmentation

Apart from fragmentation at the file-system level and within the log file, it's also possible to have fragmentation within the data files, in the structures that store the table and index data. There are two basic types of fragmentation that can occur within a data file:

Fragmentation within individual data and index pages (sometimes called internal fragmentation)
Fragmentation within index or table structures consisting of pages (called logical scan fragmentation and extent scan fragmentation)

Internal fragmentation is where there is a lot of empty space in a page. As Figure 1 shows, each page in a database is 8KB in size and has a 96-byte page header; as a result, a page can store roughly 8096 bytes of table or index data (specific table and index internals for data and row structures can be found on my blog at sqlskills.com/blogs/paul in the Inside The Storage Engine category). Empty space can occur if each table or index record is more than half the size of a page, as then only a single record can be stored per-page. This can be very hard or impossible to correct, as it would require a table or index schema change, for instance by changing an index key to be something that doesn't cause random insertion points like a GUID does.

Figure 1 The structure of a database page (Click the image for a larger view)

More commonly, internal fragmentation results from data modifications, such as inserts, updates, and deletes, which can leave empty space on a page. Mismanaged fill-factor can also contribute to fragmentation; see Books Online for more details. Depending on the table/index schema and the application's characteristics, this empty space may never be reused once it is created and can lead to ever-increasing amounts of unusable space in the database.

Consider, for instance, a 100-million-row table with an average record size of 400 bytes. Over time, the application's data modification pattern leaves each page with an average of 2800 bytes of free space. The total space required by the table is about 59GB, calculated as 8096-2800 / 400 = 13 records per 8KB page, then dividing 100 million by 13 to get the number of pages. If the space wasn't being wasted, then 20 records would fit per page, bringing the total space required down to 38GB. That's a huge savings!

Wasted space on data/index pages can therefore lead to needing more pages to hold the same amount of data. Not only does this take up more disk space, it also means that a query needs to issue more I/Os to read the same amount of data. And all these extra pages occupy more space in the data cache, thus taking up more server memory.

Logical scan fragmentation is caused by an operation called a page split. This occurs when a record has to be inserted on a specific index page (according to the index key definition) but there is not enough space on the page to fit the data being inserted. The page is split in half and roughly 50 percent of the records moved to a newly allocated page. This new page is usually not physically contiguous with the old page and therefore is called fragmented. Extent scan fragmentation is similar in concept. Fragmentation within the table/index structures affects the ability of SQL Server to do efficient scans, whether over an entire table/index or bounded by a query WHERE clause (such as SELECT * FROM MyTable WHERE Column1 > 100 AND Column1 < 4000).

Figure 2 shows newly created index pages with 100 percent fill-factor and no fragmentation—the pages are full and the physical order of the pages matches the logical order. Figure 3 shows the fragmentation that can occur after random inserts/updates/deletes.

Figure 2 Newly created index pages with no fragmentation; pages 100% full (Click the image for a larger view)

Figure 3 Index pages showing internal and logical scan fragmentation after random inserts, updates, and deletes (Click the image for a larger view)

Fragmentation can sometimes be prevented by changing the table/index schema, but as I mentioned above, this may be very difficult or impossible. If prevention is not an option, there are ways to remove fragmentation once it has occurred—in particular, by rebuilding or reorganizing an index.

Rebuilding an index involves creating a new copy of the index—nicely compacted and as contiguous as possible—and then dropping the old, fragmented one. As SQL Server creates a new copy of the index before removing the old one, it requires free space in the data files approximately equivalent to the size of the index. In SQL Server 2000, rebuilding an index was always an offline operation. In SQL Server 2005 Enterprise Edition, however, index rebuilding can take place online, with a few restrictions. Reorganizing, on the other hand, uses an in-place algorithm to compact and defragment the index; it requires only 8KB of additional space to run—and it always runs online. In fact, in SQL Server 2000, I specifically wrote the index reorganize code as an online, space-efficient alternative to rebuilding an index.

In SQL Server 2005, the commands to investigate are ALTER INDEX … REBUILD to rebuild indexes, and ALTER INDEX … REORGANIZE to reorganize them. This syntax replaces the SQL Server 2000 commands DBCC DBREINDEX and DBCC INDEXDEFRAG, respectively.

There are many trade-offs between these methods, such as the amount of transaction logging generated, the amount of free space in the database required, and whether the process is interruptible without loss of work. You'll find a white paper that discusses these trade-offs, and more, at microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx. The paper is based on SQL Server 2000 but the concepts translate well to later versions.

Some people simply choose to rebuild or reorganize all indexes every night or every week (using a maintenance plan option, for instance) rather than figuring out which indexes are fragmented and whether any benefit will come from removing the fragmentation. While that can be a good solution for an involuntary DBA who just wishes to put something in place with minimal effort, note that it can be a very poor choice for larger databases or systems where resources are at a premium.

A more sophisticated approach involves using the DMV sys.dm_db_index_physical_stats (or DBCC SHOWCONTIG in SQL Server 2000) to periodically determine which indexes are fragmented, and then choosing whether and how to operate on those. The whitepaper also discusses using these more targeted choices. Additionally, you can see some example code to do this filtering in Example D of the Books Online entry for the DMV sys.dm_db_index_physical_stats in SQL Server 2005 (msdn.microsoft.com/library/ms188917) or Example E in the Books Online entry for DBCC SHOWCONTIG in SQL Server 2000 and later (at msdn.microsoft.com/library/aa258803).

Whichever method you use, it is highly advisable to investigate and fix fragmentation on a regular basis.

Statistics

The Query Processor is the part of SQL Server that decides how a query should be executed—specifically, which tables and indexes to use, and which operations to perform on them to obtain the results; this is called a query plan. Some of the most important inputs into this decision-making process are statistics that describe the distribution of data values for columns within a table or index. Obviously, statistics need to be accurate and up-to-date to be useful to the Query Processor, otherwise poorly performing query plans may be chosen.

Statistics are generated by reading the table/index data and determining the data distribution for the relevant columns. Statistics can be built by scanning all the data values for a particular column (a full scan) but they can also be based on a user-specified percentage of the data (a sampled scan). If the distribution of values in a column is pretty even, then a sampled scan should be good enough, and this makes creating and updating the statistics faster than with a full scan.

Note that statistics can be automatically created and maintained by turning on the AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS database options, as shown in Figure 4. These are on by default but if you've just inherited a database, you may want to check to make sure. Sometimes statistics can become out-of-date, in which case manually updating them is possible using the UPDATE STATISTICS operation on specific sets of statistics. Alternatively, the sp_updatestats stored procedure can be used, which updates all statistics that are out of date (in SQL Server 2000, sp_updatestats updates all statistics, regardless of age).

Figure 4 Changing database settings through SQL Server Management Studio (Click the image for a larger view)

If you want to update statistics as part of your regular maintenance plan, there is a catch you should be aware of. Both UPDATE STATISTICS and sp_updatestats default to using the previously specified level of sampling (if any)—and this may be less than a full scan. Index rebuilds automatically update statistics with a full scan. If you manually update statistics after an index rebuild, it's possible to end up with less accurate statistics! This can happen if a sampled scan from the manual update overwrites the full scan generated by the index rebuild. On the other hand, reorganizing an index does not update statistics at all.

Again, many people have a maintenance plan that updates all statistics at some point before or after rebuilding all indexes—and so unknowingly they end up with potentially less accurate statistics. If you do choose to simply rebuild all indexes every so often, that will take care of statistics too. If you choose to go a more complex route with fragmentation removal, you should also do that for statistics maintenance. Here's what I suggest:

Analyze indexes and determine which indexes to operate on and how to do the fragmentation removal.
For all indexes that were not rebuilt, update the statistics.
Update statistics for all of the non-indexed columns.

For more information on statistics, see the white paper "Statistics Used by the Query Optimizer in Microsoft® SQL Server 2005" (microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx).

Corruption Detection

I have discussed performance-related maintenance. Now I want to switch gears and discuss corruption detection and mitigation.

It's very unlikely that the database you're managing contains totally useless information that no one cares about—so how do you go about making sure that the data remains uncorrupted and recoverable in the event of a disaster? The ins-and-outs of putting together a full disaster-recovery and high-availability strategy are beyond the scope of this article, but there are a few simple things you can do to get started.

The overwhelming majority of corruptions are caused by "hardware." Why do I put it in quotes? Well, hardware here is really shorthand for "something in the I/O subsystem underneath SQL Server." The I/O subsystem consists of elements such as the operating system, file-system drivers, device-drivers, RAID controllers, cables, networks, and the actual disk drives themselves. That's a lot of places where problems can (and do) occur.

One of the most common problems is when a power failure occurs and a disk drive is in the middle of writing out a database page. If the drive cannot complete the write before it runs out of power (or write operations are cached and there isn't enough battery backup to flush the drive's cache) the result could be an incomplete page image on the disk. This can happen because an 8KB database page is actually comprised of 16 contiguous 512-byte disk sectors. An incomplete write could have written some of the sectors from the new page but leave some of the sectors from the previous page image. This situation is called a torn page. How can you detect when this happens?

SQL Server has a mechanism to detect this situation. It involves storing a couple of bits from every sector of the page and writing a specific pattern in their place (this happens just before the page is written to disk). If the pattern is not the same when the page is read back in, SQL Server knows the page was "torn" and raises an error.

In SQL Server 2005 and later, a more comprehensive mechanism called page checksums is available that can detect any corruption on a page. This involves writing a whole-page checksum on the page just before it is written out and then testing it when the page is read back in, just as for torn-page detection. After enabling page checksums, a page needs to be read into the buffer pool, changed in some way, and then written out to disk again before it is protected by a page checksum.

So, it is a best practice to have page checksums enabled for SQL Server 2005 onwards, with torn-page detection enabled for SQL Server 2000. To enable page checksums, use:

Copy Code

ALTER DATABASE MyDatabase SET PAGE_VERIFY CHECKSUM;

To enable torn-page detection for SQL Server 2000, use this:

Copy Code

ALTER DATABASE MyDatabase SET TORN_PAGE_DETECTION ON;

These mechanisms allow you to detect when a page has a corruption on it, but only when the page is read. How can you easily force all the allocated pages to be read? The best method for doing this (and finding any other kinds of corruption) is to use the DBCC CHECKDB command. Regardless of the options specified, this command will always read all pages in the database, thus causing any page checksums or torn-page detection to be verified. You should also set up alerts so you know when users encounter corruption problems when running queries. You can be notified of all the problems described above using an alert for Severity 24 errors (Figure 5).

Figure 5 Setting up an alert for all Severity 24 errors (Click the image for a larger view)

So another best practice is to regularly run DBCC CHECKDB on databases to verify their integrity. There are many variations on this command and questions around how often to run it. Unfortunately, there is no white paper available that discusses this. However, as DBCC CHECKDB was the main piece of code I wrote for SQL Server 2005, I have blogged extensively about it. See the "CHECKDB From Every Angle" category of my blog (sqlskills.com/blogs/paul) for many in-depth articles on consistency checking, best practices, and how-to advice. For involuntary DBAs, the rule of thumb is to run a DBCC CHECKDB as often as you take a full database backup (more on this below). I recommend running the following command:

Copy Code

DBCC CHECKDB ('MyDatabase') WITH NO_INFOMSGS,
ALL_ERRORMSGS;

If there is any output from this command, DBCC has found some corruptions in the database. The question then becomes what to do if DBCC CHECKDB finds any corruptions. That's where backups come in.

Backups

When a corruption or other disaster occurs, the most effective way to recover is to restore the database from backups. Now, this assumes that you have backups in the first place, and that they're not corrupt themselves. All too often, people want to know how to get a badly corrupted database running again when they don't have a backup. The simple answer is that you can't, not without experiencing some form of data loss that could play havoc with your business logic and relational data integrity.

So there is a very strong case for taking regular backups. The intricacies of using backup and restore are well beyond the scope of this article, but let me give you a quick primer on how to establish a backup strategy.

First, you should take regular full-database backups. This gives you a single point-in-time to which you can later restore. You can take a full-database backup using the BACKUP DATABASE command. Look in Books Online for examples. For added protection, you can use the WITH CHECKSUM option, which verifies the page checksums (if present) of pages being read and calculates a checksum over the entire backup. You should choose a frequency that reflects how much data or work your business is comfortable losing. For example, taking a full database backup once per day means you may lose up to a day's worth of data in the event of a disaster. If you are only using full database backups, you should be in the SIMPLE recovery model (commonly called recovery mode) to avoid complexities relating to transaction log growth management.

Second, always keep the backups around for a few days in case one becomes corrupt—a backup from a few days ago is better than no backup at all. You should also verify the integrity of your backups using the RESTORE WITH VERIFYONLY command (again, see Books Online). If you used the WITH CHECKSUM option when the backup was created, running the verification command will check that the backup checksum is still valid, as well as re-check all the page checksums of pages within the backup.

Third, if a daily full database backup does not allow you to meet the maximum data/work loss your business can sustain, you may want to investigate differential database backups. A differential database backup is based on a full database backup and contains a record of all the changes since the last full database backup (a common misconception is that differential backups are incremental—they are not). A sample strategy might be to take a daily full database backup, with a differential database backup every four hours. A differential backup provides a single extra point-in-time recovery option. If you are only using full database and differential database backups, you should still be using the SIMPLE recovery model.

Finally, the ultimate in recoverability comes with using log backups. These are only available in the FULL (or BULK_LOGGED) recovery models and provide a backup of all the log records generated since the previous log backup. Maintaining a set of log backups with periodic full database (and maybe differential database) backups gives an unlimited number of points-in-time to recover to—including up-to-the-minute recovery. The trade-off is that the transaction log will continue to grow unless it is "freed" by taking a log backup. A sample strategy here would be a full database backup every day, a differential database backup every four hours, and a log backup every half hour.

Deciding on a backup strategy and setting it up can be complicated. At the very least, you should have a regular full-database backup to ensure you have at least one point-in-time to recover from.

Wrap-Up

As you can see, to ensure your database stays healthy and available there are a few "'must do" tasks. Here's my final checklist for an involuntary DBA taking over a database:

Remove excessive transaction log file fragmentation.
Set auto-growth correctly.
Turn off any scheduled shrink operations.
Turn on instant file initialization.
Put a regular process in place to detect and remove index fragmentation.
Turn on AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS, plus have a regular process in place to update statistics.
Turn on page checksums (or least torn-page detection on SQL Server 2000).
Have a regular process to run DBCC CHECKDB.
Have a regular process in place to take full database backups, plus differential and log backups for point-in-time recovery.

I've given T-SQL commands within the article, but you can do a lot from Management Studio too. Hopefully, I have given you some useful pointers for effective database maintenance.

Using Performance Monitor

2008-09-16T08:14:00.000-07:00

As you probably already know, SQL Server is very good at tuning itself. It has the ability to monitor itself, and through a feedback loop, it knows how to internally adjust and tune itself so that it keeps running efficiently, even when external events, such as the number of user connections or the amount of available RAM, change over time.

But as we all know, SQL Server's ability to self-tune is not perfect and does not take into consideration every possible aspect that affects its performance. As a DBA, we need to help SQL Server along, providing it the resources it needs for it to do a good job serving up data.

As a good DBA, we don't want to find out from our users that SQL Server is having a performance problem. Instead, we want to be proactive and catch performance problems before they arise. That is what Window's Performance Monitor can help us do. It is a tool that allows us to monitor what is going on with our SQL Server, and to provide us the information we need to make decisions on how to best tune our SQL Servers.

Performance Monitor is an important tool, because it not only provides us with information on how SQL Server is performing, but it also lets us know how Windows Server is doing, which of course directly affects SQL Server's performance. [6.5, 7.0, 2000] Updated 7-10-2006

The "Performance Monitor" under the "Microsoft SQL Server" entry under your Start Menu is the same "Performance Monitor" under the "Administrative Tools" entry under your Start Menu. They are the same programs. What is different is that when you bring up Performance Monitor from under the "Microsoft SQL Server" entry, is that it comes up already running several pre-configured SQL Server performance counters.

The Performance Monitor under the "Administrative Tools" entry does not come with any pre-configured counters loaded. Personally, I dislike the "Microsoft SQL Server" option and always choose the Performance Monitor option under "Administrative Tools." This way, I always get to choose the SQL Server Performance Monitor counters I prefer to use. [6.5, 7.0] Updated 7-10-2006

*****

If you are like me, you have one or two SQL Server production servers that are very important to monitor. To help me keep tabs on these "high-visibility" SQL Servers, I always run an instance of Performance Monitor in the background on my Windows NT 4.0 or Windows 2000 Workstation desktop pointing to these servers. I don't log this data, but I like the ability to very quickly take a look at key performance counters (in chart mode) throughout the day.

Since Performance Monitor is always running, I don't have any excuse not to take a peek at my SQL Server's performance at various times throughout the day. You would be surprised at the things you find, including bottlenecks you may not know you had. In addition, after some time, you begin to better learn how your server's perform, which makes it easier to diagnose potential problems as they arise.

In order to minimize the affect of this constant monitoring on your SQL Servers, you will not want to monitor too many counters. Here are the key counters I like to watch on a regular basis:

Memory — Pages/Sec: To see how much paging my server is doing. This should be close to zero on a dedicated SQL Server. You will see spikes during backups and restores, but this is normal.
Network Interface — Bytes Total/sec: To see how much network activity is going on.
PhysicalDisk — % Disk Time — _Total: To see how busy all the disk drives are.
PhysicalDisk — Current Disk Queue Length — _Total: Also to see how busy the drives are.
System — % Total Processor Time: To see how busy all the CPUs are as a whole.
System — Processor Queue Length: Also see how busy the CPUs are.
SQLServer: General Statistics — User Connections: To see how many connections (and users) are using the server. Keep in mind that one connection does not equal one user. A single user can have more than one connection, and a single connection can have more than one user.
SQLServer: Access Methods — Page Splits/sec: Lets me know if page splits are an issue or not. If so, then that means I need either to increase the fill factor of my indexes, or to rebuild the indexes more often.
SQLServer: Buffer Manager — Buffer Cache Hit Ratio: To find out if I have enough memory in the server. Keep in mind that this ratio is based on the average of the buffer hit cache ratio since the SQL Server service was last restarted, and is not a reflection of the current buffer cache hit ratio.
SQLServer: Memory Manager — Target Server Memory (KB): To see how much memory SQL Server wants. If this is the same as the SQLServer: Memory Manager — Total Server Memory (KB) counter, then I know that SQL Server has all the memory that it wants.
SQLServer: Memory Manager — Total Server Memory (KB): To see how much memory SQL Server actual is using. If this is the same as SQLServer: Memory Manager — Target Server Memory (KB), then I know that SQL Server has all the memory that it wants. But if this is smaller, then SQL Server needs more available memory in order to run at its optimum performance.

Based on my experiences and preferences, these are the counters I like to watch regularly. If I see something interesting in these counters, I often add additional counters as necessary to get a more detailed look at what is going on. I run Performance Monitor from my desktop, not the server I am monitoring in order to minimize overhead on the SQL Server.

By default, readings will appear every second and less than two minutes at a time will appear on the graph. I don't find this time frame all that useful, so I change it to 36 seconds, which displays an hour on the screen of activity. This gives me a good feel of the health of my critical SQL Servers without putting any undue overhead on the server.

If you don't already check your SQL Server's key performance counters throughout the day, you need to start this important habit. The more you learn about how your servers run, the better DBA you will be. [6.5, 7.0, 2000] Updated 7-10-2006

*****

Once you have identified the Performance Monitor counters you like to use, you can save them in a file and then later reload them when you want to see them again. This way, you won't have to re-add the counters to Performance Monitor each time you use it. In fact, you can create different sets of counters, with different names, so you can track different types of counters at a time. In addition, each different type of Performance Monitor's modes, such as "Chart" and "Log," allows you to store its own set of counters.

How you use Performance Monitor to do this depends on if you are using Windows NT 4.0 or Windows 2000.

If you are using Windows NT 4.0, then use Performance Monitor's "File" menu option to save and load your counter files.

If you are using SQL Server 2000, then you will use the "Console" menu option save and load counter files. [6.5, 7.0, 2000] Updated 7-10-2006

*****

When monitoring your server using NT Server 4.0's "Performance Monitor," or Windows 2000's "System Monitor" tool, keep in mind that the more counters you monitor the more overhead that is required to perform the monitoring. This is true whether you are viewing a performance chart, logging counters, or creating alerts based on counters. Because of this, don't monitor counters you don't need to monitor. If you are using multiple counters for your monitoring, but soon realize that one or more of them are of little value to the task at hand, then remove these counters from your monitoring.

One difference between NT Server 4.0's Performance Monitor and Windows 2000's System Monitor is how logging is done. In Performance Monitor, you must log entire performance objects, you can't just log individual counters. This can lead to very large log files with a lot of data you don't need. In System Monitor, you are now able to log individual counters, not just entire objects. This makes these logs much smaller in size, making it more practical to log over long periods of time. [6.5, 7.0, 2000] Updated 7-10-2006

*****

When monitoring your server using NT Server 4.0's "Performance Monitor," or Windows 2000's "Performance" tool, keep in mind that how often you set these tools to collect counter data affects the amount of overhead experienced by your server. For example, the default counter collection interval for displaying near real-time charts of performance counters for these two tools is 1 second. If you increase this to .5 seconds, then overhead is essentially doubled. But if you decrease it to 5 seconds, then overhead is substantially reduced).

So how often should you collect performance counter data? It depends on what your goals are. In some cases, you need to collect data in near real-time (every second), and it other cases, collecting it every 5, 15, 30 seconds is adequate. The key is to not collect data more often than you really need to. Personally, when I collect performance counter data to display as a chart, I use 36-second intervals. Experience has proven to me that this time interval best meets my needs for monitor SQL Server's performance, and it also allows exactly one hour of chart data to be on the screen at one time. Of course, your mileage may vary. [6.5, 7.0, 2000] Updated 7-10-2006

*****

When monitoring a SQL Server using Performance Monitor, don't run Performance Monitor on the same server you are monitoring. Instead, run it on a different server or workstation and remotely monitor the SQL Server. Running Performance Monitor on the same server you are monitoring will skew the results.

Along the same train of thought, don't run both Performance Monitor and Profiler at the same time, even if you are running them remotely. This is too much overhead and will cause your SQL Server to suffer some performance degradation. In addition, running both together can cause Performance Monitor to produce less than accurate data because of the overhead of Profiler running at the same time. [6.5, 7.0, 2000]

SQL Server Tuning

2008-09-16T08:06:00.001-07:00

Q : I have SQL Server 2000 with approximately 45 databases on a single instance. These databases are used by multiple ASP applications running on my public Web site. SQL Server regularly reaches 100 percent CPU utilization and requires reboots. How should I start troubleshooting and repairing this problem?

A : Start with rebuilding all of your indexes. It’s easy, assuming you have memory configured correctly. Then run SQL Profiler for a few hours and capture a snapshot of your standard business T-SQL traffic. Run this trace through the Index Tuning Wizard (ITW) that comes built into SQL Server 2000. It will take a look at the queries and make recommendations for indexes to improve performance.

If you have SQL Server 2005 installed, you can use the Database Tuning Advisor (DTA) which is the successor to ITW, to tune your workload. DTA can tune SQL Server 2000 as well. It tunes a database on a production server by offloading most of the tuning load onto a test server. DTA uses the production server hardware configuration information, without actually copying the data from the production server to the test server. It only copies the metadata and necessary statistics.

Finding the Bottleneck

2008-09-16T07:59:00.000-07:00

Q : When I run one particular query, CPU usage will reach 100 percent after a few seconds, and the server will be shut down in a few minutes. I can reproduce it every time. We have hundreds of millions of unique values in the table in question.

Is this a hardware issue or a software issue? I can’t get any information from Event Log. The server is an HP AMD 64-bit machine. I am using SQL Server 2005 (64-bit).

A : If the system really is crashing (bugchecking) then you will need to take a look in the memory.dmp file. First check the Startup and Recovery settings (in the Control Panel) and make sure it’s set to create at least a kernel memory dump. If that is already set, then find the memory.dmp file and save it to another machine. The next step is to download the Debugging Tools for Windows (windbg) from microsoft.com/whdc/devtools/debugging. Make sure to select the right package for your system (in this case you want the 64-bit versions), and then choose the x64 package.

When the debugger is installed, run windbg and set the symbol path to the Microsoft Symbol Server. To do this, type Ctrl+S and enter a valid symbol path. I usually use something like srv*c:\symcache*http://msdl.microsoft.com/download/symbols. For more information on this, see Debugging Tools and Symbols: Getting Started.

Next, open the memory.dmp file in your favorite Windows debugger and run !analyze -v. This will tell you what the bugcheck was and give more details on why the server crashed.

Log Shipping or Mirroring?

2008-09-16T07:52:00.000-07:00

Q : Is it true that once mirroring becomes available, there is no reason to use log shipping anymore? It seems that mirroring is less functional than log shipping if you want to use the mirrored database for reporting. It also seems that mirroring only allows you to query against a database snapshot, which represents a point in time. Log shipping, on the other hand, appears to allow you to query against current data. I know the data can be out of date depending on how often you ship the logs, but this seems better than having to constantly create new snapshots. In other words, shipping the logs every hour seems a lot better than using mirroring and creating a new snapshot each hour.

A : If reporting is needed, log shipping is not necessarily better in this scenario than mirroring and using snapshots. The log shipping secondary must be restored with the WITH STANDBY option in order to allow read-only access. The data is static at that point in time up to the last transaction log loaded, just like a database snapshot represents a point in time. If you need more up-to-date data to query against with log shipping, the secondary database must have transaction logs applied, which means users cannot access the database while that is occurring. Log shipping is not really a reporting solution.

Database mirroring and using a snapshot on the mirror may be easier to administer since you are not affecting the process like you are with log shipping. You are just creating a new snapshot on a periodic basis. The problem, though, with mirroring and snapshots is that since each snapshot is named, either each new snapshot created would have a different name (which may impact the reporting application) or the old snapshot would need to be deleted and then created again (which would impact the availability of the reporting solution). So to some degree, both log shipping and mirroring have different advantages as reporting solutions in addition to their intended use for availability and disaster recovery. But with log shipping, reporting is unavailable when you apply a new transaction log to refresh data, and with database mirroring, reporting is unavailable when you drop and recreate the existing snapshot, or create an additional snapshot to make it more current.

Log shipping is based on backup and restore, and supports multiple secondaries, each of which can have its own schedule or delay, which can also account for other problems, such as user error. Database mirroring can be simpler to implement. When using its synchronous mode, the mirror database is more up-to-date (with no data loss) than its log shipping counterpart, which is only as good as the last log that has been applied to the secondary. With the right configuration, mirroring also supports an automatic failover scenario. Database mirroring, unlike log shipping, can only have one mirror database. Both database mirroring and log shipping provide protection at the database level, while failover clustering provides protection at the instance level. A geographically dispersed cluster could work over distances for availability purposes, but this can be an expensive solution. Database mirroring and log shipping can work with clustering to provide extremely high availability for your SQL Server™ instances.

Install Firebird 2.0.3 Database Server On Ubuntu 7.10

2008-09-13T08:33:00.000-07:00

This tutorial explains how you can install the Firebird database server (version 2.0.3) on an Ubuntu 7.10 server.

Step 1. Prepare an nice Ubuntu server : fire a "The Perfect Setup - Ubuntu"
search on HowtoForge and follow the steps according to the OS version
you are decided to go on.

Step 2. Do the libstdc++5 installation (before installing Firebird)

user@machine:~# sudo apt-get install libstdc++5

Step 3. Download and expand Firebird installation kit from
http://firebirdsql.org (downloads) or source force: the 2.0.3 NTPL
superserver.

- for 32 bit OS:

user@machine:~# sudo wget http://downloads.sourceforge.net/firebird/FirebirdSS-2.0.3.12981-1.nptl.i686.tar.gz

user@machine:~# sudo tar -xvf FirebirdSS-2.0.3.12981-1.nptl.i686.tar.gz

user@machine:~# cd cd FirebirdSS-2.0.3.12981-1.i686

- for 64 bit OS:

user@machine:~# sudo wget http://downloads.sourceforge.net/firebird/FirebirdSS-2.0.3.12981-1.amd64.tar.gz

user@machine:~# sudo tar -xvf FirebirdSS-2.0.3.12981-1.amd64.tar.gz

user@machine:~# cd FirebirdSS-2.0.3.12981-1.amd64

(remark: is NTPL by default for 64 bit OS)

Step 4. Install Firebird.

- user@machine:~# sudo ./install.sh

(remark : install script will prompt you twice for the SYSDBA password

Step 5. Do some UDF's housekeeping.

- if you have UDF's please (sudo) put them in /opt/firebird/UDF/
folder. Of course the UDF's must be compiled according to OS version
....

- change rights on UDF's folder:

user@machine:~# sudo chown root:root /opt/firebird/UDF/*

user@machine:~# sudo chmod 444 /opt/firebird/UDF/*

- and restart firebird :

user@machine:~# sudo /etc/init.d/firebird reload

Optional step 6. Move your data:

- do a backup on the old machine:

<put path (include ending slash at the end) to gbak here>gbak -b
-t -v -USER SYSDBA -PASS <put SYSDBA password here> <put path
and name of the database here> <put path and name of the backup
file here>

- make home of the new database on the new machine:

user@machine:~# sudo mkdir <put path to the database location here>

- and give Firebird the needed rights there:

user@machine:~# sudo chown firebird:firebird <put path to the database (include ending slash at the end) location here>

- move the backup file from old to the new machine in the <put path
to the database (include ending slash at the end) location here>

folder and restore data:

<put path (include ending slash at the end) to gbak here>gbak -c
-v -t -USER

SYSDBA -PASS <put SYSDBA password here> <put path and name of the backup
file here> <put path
and name of the database here>

Enjoy.

SQL Server 2000 - Merge Replication

2008-09-13T08:30:00.000-07:00

By : D J Nagendra

Introduction

Replication is the process of sharing data between databases in different locations. Using replication, we can create copies of the database and share the copy with different users so that they can make changes to their local copy of database and later synchronize the changes to the source database.

Microsoft SQL Server replication uses publisher, distributor and subscriber entities.

Publisher is a server that makes the data available for subscription to other servers. In addition to that, publisher also identifies what data has changed at the subscriber during the synchronizing process. Publisher contains publication(s).

Subscriber is a server that receives and maintains the published data. Modifications to the data at subscriber can be propagated back to the publisher.

Distributor is the server that manages the flow of data through the replication system. Two types of distributors are present, one is remote distributor and the other one local distributor. Remote distributor is separate from publisher and is configured as distributor for replication. Local distributor is a server that is configured as publisher and distributor.

Agents are the processes that are responsible for copying and distributing data between publisher and subscriber. There are different types of agents supporting different types of replication.

An article can be any database object, like Tables (Column filtered or Row filtered), Views, Indexed views, Stored Procedures, and User defined functions.

Publication is a collection of articles.

Subscription is a request for copy of data or database objects to be replicated.

Types of Subscription:

Changes to the subscriptions at the publisher can be replicated to subscribers via PUSH subscription or PULL subscription. With Push subscription, the publisher is responsible for synchronizing all the changes to the subscriber without subscriber asking for those changes. With Pull subscription, the subscriber initiates the replication instead of the publisher.

Replication Types

Microsoft SQL Server 2000 supports the following types of replication:

Snapshot Replication
Transactional Replication
Merge Replication

Snapshot Replication

Snapshot replication is also known as static replication. Snapshot replication copies and distributes data and database objects exactly as they appear at the current moment in time.
Subscribers are updated with complete modified data and not by individual transactions, and are not continuous in nature.
This type is mostly used when the amount of data to be replicated is small and data/DB objects are static or does not change frequently.

Transactional Replication

Transactional replication is also known as dynamic replication. In transactional replication, modifications to the publication at the publisher are propagated to the subscriber incrementally.
Publisher and the subscriber are always in synchronization and should always be connected.
This type is mostly used when subscribers always need the latest data for processing.

Merge replication

It allows making autonomous changes to replicated data on the Publisher and on the Subscriber. With merge replication, SQL Server captures all incremental data changes in the source and in the target databases, and reconciles conflicts according to rules you configure or using a custom resolver you create. Merge replication is best used when you want to support autonomous changes on the replicated data on the Publisher and on the Subscriber.

Replication agents involved in merge replication are snapshot agent and merge agent.

Implement merge replication if, changes are made constantly at the publisher and subscribing servers, and must be merged in the end.

By default, the publisher wins all conflicts that it has with subscribers because it has the highest priority. Conflict resolver can be customized.

Necessary steps to be taken before doing replication process:

Before starting the replication process, change the log on account for the MSSQLSERVER service as “This account”. Use any SQL login account which is a member of sysadmin server role. Please see the screenshot for more information. Don’t forget to restart the MSSQLSERVER service.
Adequate disk space should be allocated for publisher, distribution and subscriber’s databases.
Use NOT FOR REPLICATION option when defining Identity columns.

Step by Step Procedure for Merge Replication setup

Open SQL Server Enterprise Manager and select Tools menu -> Replication -> Configure Publishing, Subscribers, and Distribution…
1. Configure the appropriate server as publisher or distributor.
2. Enable the appropriate database for merge replication.
3. Enable the appropriate server as subscriber.
Open SQL Server Enterprise Manager and select the appropriate SQL Server Group for which replication needs to be done, then select Tools menu -> Replication -> Create and Manage Publications.
This will open a dialog box for “Create and Manage Publications on respective server”. Select the appropriate database and then click “Create Publication”. This will open “Create Publication Wizard”. Just click Next.
It will ask to choose a Distributor for the selected server. Select “Make Server its own Distributor; SQL Server will create a distribution database and a log”. Then click Next.
It will ask for the Snapshot folder path. Browse and select the appropriate path for Snapshot folder and then click Next.
Note: Create one folder in the Publisher machine and share the folder, then give full permissions for the user through which you logged in. Make sure that you are able to access this folder from the Subscriber machine also. If you are not able to access, give full permissions to that shared folder for the appropriate user in the Publisher machine. The Snapshot folder should be in the Publisher machine.
Choose the database which you want to publish and Click Next.
Select the Publication Type as “Merge Publication”.
Specify the Subscriber Types. Select “Servers running SQL Server 2000”. Then click Next.
Select the Object Types (like Tables, Stored Procedures and Views) which you want to publish, and click Next.
It will show some issues which may require some changes at later stages in order to work as expected. Just click Next.
Give Publication Name and click Next.
It will ask to customize the properties of the Publication. Select “Yes, I will define data filters, enable anonymous subscriptions, or customize other properties”. Then click Next.
Then, it will ask “How do you want to filter this publication?” Don’t select any thing here. Just click Next.
Then, it will ask “Whether you want to allow anonymous subscription to this publication?”. Select “No, allow only named subscriptions”, and click Next.
It will show “Set Snapshot Agent Schedule” dialog box. Change the Snapshot Agent Schedule as per your requirement, then select “Create the first snapshot immediately”. And click Next.
Click Finish to create a Publication.
Finally, it will show “SQL Server Enterprise Manager successfully created publication ‘pub1’ from database ‘db1’. Just click Close.
It will show the dialog box “Create and Manage Publications on respective Server”. Now go to the respective created Publication and click “Push New Subscription”.
Before doing “Push New Subscription”, create new SQL Server Registration for Subscriber machine in the Publisher machine’s SQL Server Enterprise manager with SQL Authentication mode. For this, there should be one common SQL login name in both Publisher and Subscriber machines. Set server roles for this user as System Administrator, Process Administrator and Bulk Insert Administrators, and give database access to the respective database for which you want to perform replication.
Go to “Push New Subscription” wizard. This will open “Push Subscription Wizard”. Just Click Next.
Choose one or more subscribers from Enabled Subscribers and click Next. (Note: It will show the Subscriber’s SQL Server name under Enable Subscribers only if you do step 19.)
Choose Subscription (destination) database name by browsing and clicking Next. (Note: You can create new database if you want by clicking Create New).
“Set Merge Agent Schedule”. Change the Schedule as per your requirement and click Next.
Specify whether the Subscription(s) needs to be initialized or not. Select “Yes, initialize the schema and data” as well as select “Start the Snapshot Agent to begin the initialization process immediately”, and click Next.
“Set Subscription Priority” as “Use the Publisher as a proxy for the Subscriber when resolving conflicts”, and click Next.
It will show the status of the SQLSERVERAGENT service as running. Just click Next.
Click Finish to complete the Push Subscription.
Finally, it will show “Subscriptions were created successfully at the following Subscribers:”. Just click Close.
Now, in the SQL Server Enterprise Manager, go to the appropriate SQL Server Group and go to “Replication Monitor -> Publishers -> Respective Server -> Publication Name”. In the right pane, you will see the snapshot agent. Just right click and select “Start Agent”. Refresh it once. Then right click on the respective publication name and select “Start Synchronizing”. It will merge the necessary data. Refresh it once.

Important Note:

SQL Server 2000 replication will not support full-text indexing. But, enable full-text indexing at the subscriber machine manually. This can be done by Full-text indexing wizard. Select the appropriate table and enable the required fields in that table as full-text indexed. Then, create a new catalog or else use the existing catalog and schedule it, if needed. Once this is done, go to that particular catalog and right click and select “Start full population”. The status will be displayed as “population in progress”.

Advantages in Replication:

Users can avail the following advantages by using replication process:

Users working in different geographic locations can work with their local copy of data thus allowing greater autonomy.
Database replication can also supplement your disaster-recovery plans by duplicating the data from a local database server to a remote database server. If the primary server fails, your applications can switch to the replicated copy of the data and continue operations.
You can automatically back up a database by keeping a replica on a different computer. Unlike traditional backup methods that prevent users from getting access to a database during backup, replication allows you to continue making changes online.
You can replicate a database on additional network servers and reassign users to balance the loads across those servers. You can also give users who need constant access to a database their own replica, thereby reducing the total network traffic.
Database-replication logs the selected database transactions to a set of internal replication-management tables, which can then be synchronized to the source database. Database replication is different from file replication, which essentially copies files.

Replication Performance Tuning Tips:

By distributing partitions of data to different Subscribers.
When running SQL Server replication on a dedicated server, consider setting the minimum memory amount for SQL Server to use from the default value of 0 to a value closer to what SQL Server normally uses.
Don’t publish more data than you need. Try to use Row filter and Column filter options wherever possible as explained above.
Avoid creating triggers on tables that contain subscribed data.
Applications that are updated frequently are not good candidates for database replication.
For best performance, avoid replicating columns in your publications that include TEXT, NTEXT or IMAGE data types.

Conclusion

In a nutshell, replication is the capability to reliably duplicate data from a source database to one or more destination databases. SQL Server 2000 gives you the power for replication design, implementation, monitoring, and administration. This gives you the functionality and flexibility needed for distributing copy of data and maintaining data consistency among the distributed. You can automatically distribute data from one SQL Server to many different SQL Servers through ODBC (Open Database Connectivity) or OLE DB. SQL Server replication provides update replication capabilities such as Immediate Updating Subscribers and merges replication. With all the new enhancements to SQL Server replication, the number of possible applications and business scenarios is mind-boggling.

Accessing SQL Server Databases with PHP

2008-09-13T01:46:00.000-07:00

SQL Server Technical Article

Writer: Brian Swan
Published: August 2008
Applies to: SQL Server 2005 and SQL Server 2008 – all editions

Summary:

The SQL Server 2005 Driver for PHP is a Microsoft-supported extension of PHP 5 that provides data access to SQL Server 2005 and SQL Server 2008. The extension provides a procedural interface for accessing data in all editions of SQL Server 2005 and SQL Server 2008. The SQL Server 2005 Driver for PHP API provides a comprehensive data access solution from PHP, and includes support for many features including Windows Authentication, transactions, parameter binding, streaming, metadata access, connection pooling, and error handling. This paper discusses how to use several of these features by closely examining parts of the Example Application in the SQL Server 2005 Driver for PHP product documentation in MSDN. This paper assumes that the reader is familiar with programming in PHP, that the reader has a computer that meets the System Requirements listed for using the driver, and that the AdventureWorks example database is installed from CodePlex.

Introduction

The Microsoft SQL Server 2005 Driver for PHP allows PHP developers to access data in SQL Server 2005 and SQL Server 2008 databases. The driver includes support for Windows and SQL Server Authentication methods, transactions, parameter binding, streaming, metadata access, connection pooling, and error handling.

This paper explains how to load and configure the driver, and discusses how to leverage several of the features mentioned above. Parts of the Example Application in the product documentation will be used to demonstrate these programming scenarios.

For more information about a complete list of driver features and functions, see the API Reference in the SQL Server 2005 Driver for PHP product documentation in MSDN. The prefix for all driver functions is sqlsrv.

The SQL Server Driver for PHP relies on the Microsoft SQL Server 2005 ODBC Driver to handle the low-level communication with SQL Server. As a result, the SQL Server Driver for PHP is only supported on Windows. Microsoft provides support for this driver under its normal support methods. While the source code for this driver has been made available on the codeplex.com website, Microsoft supports only the signed version of the driver from the MSDN download site.

This paper assumes that the reader is familiar with programming in PHP, that the reader has a computer that meets the System Requirements listed for using the driver, and that the AdventureWorks example database is installed.

Loading the Driver

You can download the SQL Server 2005 Driver for PHP at the Microsoft Download Center. Included in the download are two .dll files: php_sqlsrv.dll and php_sqlsrv_ts.dll.

These files correspond to the non-thread-safe and thread-safe versions of the driver respectively.

Loading the SQL Server 2005 Driver for PHP is similar to loading any PHP extension:

1. Put the extension file (php_sqlsrv.dll or php_sqlsrv_ts.dll) in the PHP extension
directory. If you are running the non-thread-safe version of PHP (php5.dll),
you should use the non-thread-safe version of the driver (php_sqlsrv.dll).
Similarly, if you are running the thread-safe version of PHP (php5ts.dll), you
should use the thread-safe version of the driver (php_sqlsrv_ts.dll).

2. Modify the php.ini file to include the extension.

Depending on which version of the driver you want to load (non-thread-safe or thread-safe), you will need to add one of the following lines to the Dynamic Extensions section
of your php.ini file:

extension=php_sqlsrv.dll

-or-

extension=php_sqlsrv_ts.dll

See Figure 1 below for more detail.

3. Restart the Web server.

For more information, see Loading the Driver in the product documentation.

Configuring the Driver

The SQL Server 2005 Driver for PHP has three configuration options:

· LogSubsystems

Use this option to turn the logging of subsystems on or off. The default setting is SQLSRV_LOG_SYSTEM_OFF (logging is turned off by default).

· LogSeverity

Use this option to specify what to log after logging has been turned on. The default setting is SQLSRV_LOG_SEVERITY_ERROR (only errors are logged by default after logging has been turned on).

· WarningsReturnAsErrors

By default, the SQL Server 2005 Driver for PHP treats warnings generated by sqlsrv functions as errors. Use the WarningsReturnAsErrors option to change this
behavior. The default setting for this option is true (1).

Note There
are exceptions to this rule. For example, the warning generated by changing the
database context is never treated as an error.

For more information about these options and settings, see Configuring the Driver in the product documentation.

Settings for the configuration options can be set in the php.ini file, or they can be set in a PHP script with the sqlsrv_configure function. The figure below shows the Dynamic Extensions section of the php.ini file modified to load the thread-safe version of the driver, log activity on
all subsystems, log all activity (errors, warnings, and notices), and turn off
the WarningsReturnAsErrors behavior.

Figure
1: The Dynamic Extensions section of the php.ini.

For more information about how to change the default settings, see Logging Activity and How to: Configure Error and Warning Handling in the product documentation.

One way to be sure that the driver is loaded and to see the configuration settings is to run a script that calls the phpinfo() function. To do this, follow these steps:

1. Open a text file
and copy the following code into it:

<?php phpinfo(); ?>

2. Save the file as
info.php in your Web server’s root directory.

3. Open a browser
and go to http://localhost/info.php.

4. Scroll down the
resulting page to find the sqlsrv section.

The following figure shows the sqlsrv section of the phpinfo() page. This output confirms the driver is loaded and the configuration settings are set to default values.

Figure 2: The sqlsrv section of the phpinfo() page.

Creating a Connection

The sqlsrv_connect function is used to establish a connection to the server. The code shown here (from the Example Application in the product documentation) establishes a connection to the local server and specifies the AdventureWorks database as the database
in use:

$serverName = "(local)";

$connectionOptions =
array("Database"=>"AdventureWorks");

/* Connect using Windows
Authentication. */

$conn = sqlsrv_connect($serverName, $connectionOptions);
if( $conn === false )
{ die( FormatErrors( sqlsrv_errors() ) ); }

By default, the sqlsrv_connect

function uses Windows Authentication to establish a connection. In most
scenarios, this means that the Web server's process identity or thread identity
(if the Web server is using impersonation) is used to connect to the server,
not an end-user's identity.

The sqlsrv_connect function
accepts two parameters: $serverName and $connectionOptions
(optional).

· $serverName – This required parameter is used to
specify the name of the server to which you want to connect. In the code above,
a connection is established to the local server. This parameter can also be use
to specify a SQL Server instance or a port number. For example:

$serverName = "myServer\instanceName";

-or-

$serverName = "myServer,
1521";

· $connectionOptions - This optional parameter is an
array of key-value pairs that set options on the connection. In the code above,
the database is set to AdventureWorks for the connection. Other options
include ConnectionPooling, Encrypt, UID, and PWD.
For more information, see sqlsrv_connect in the product documentation.

Note The UID and PWD options must be set in the $connectionOptions
parameter to log into the server with SQL Server Authentication.

For more information about creating a connection, see Connecting to the Server
in the product documentation.

Note The FormatErrors function that is shown in the example is a custom
function for formatting error output. It is described in the Handling Errors
and Warnings section later in this paper.

Connection Pooling

The SQL Server 2005 Driver for PHP is designed to use ODBC
connection pooling. By default, connection pooling is enabled. When you connect
to a server, the driver attempts to use a pooled connection before it creates a
new one. If an equivalent connection is not found in the pool, a new connection
is created and added to the pool. The driver determines whether connections are
equivalent based on a comparison of connection strings. Calling sqlsrv_close on a
connection returns the connection to the pool. However, if the connection was
created with the ConnectionPooling attribute set to false (see the Creating
a Connection section), calling sqlsrv_close closes the connection.

Note The
first time you execute a query on a connection that was retrieved from a pool,
the driver tells the server to reset the connection prior to executing the
query. Resetting the connection returns the connection to its original state.
For example, resetting the connection deletes any temporary objects and rolls
back any pending transactions.

For more information, see Connection
Pooling in the product documentation.

Executing a Query

The SQL Server 2005 Driver for PHP
provides two options for executing queries: the sqlsrv_query

function, or the combination of the sqlsrv_prepare
and sqlsrv_execute
functions.

The sqlsrv_query function does
both statement preparation and execution with one function call and is best
suited for executing one-time queries. An alternate method for executing
queries (a method well-suited for executing a query multiple times with
different parameter values) is the combination sqlsrv_prepare and sqlsrv_execute.
This option breaks statement preparation and execution into two function calls.
For more information, see Comparing
Execution Functions in the product documentation.

The general programming pattern for
either option requires doing the following before calling sqlsrv_query

or sqlsrv_prepare/sqlsrv_execute:

·
Establishing a connection to the server (see the Creating a
Connection section)

·
Defining a Transact-SQL statement

·
Providing an array of parameter values (only required for
parameterized queries)

· Setting options on the connection (optional)

The following code (from the Example Application in the product documentation) demonstrates the use of the sqlsrv_query function:

$tsql = "SELECT ProductID, Name, Color, Size, ListPrice FROM Production.Product WHERE
Name LIKE '%' + ? + '%' AND ListPrice > 0.0";
$params = array( $_REQUEST['query'] );
$getProducts = sqlsrv_query(
$conn, $tsql, $params);

if ( $getProducts === false){ die( FormatErrors( sqlsrv_errors() ) ); }

The sqlsrv_query and sqlsrv_prepare functions each accept four parameters: $conn, $tsql, $params (optional), and $options (optional, not shown).

· $conn – This required parameter is a PHP connection
resource created with the sqlsrv_connect function (see the Creating a
Connection section).

· $tsql – This required parameter is a string that
defines a Transact-SQL query. Question marks (?) are used as placeholders for
parameters.

· $params – This optional parameter is an array of values that correspond (in order) to the parameter placeholders (question marks) in the query defined by the $tsql parameter. Each value in the $params array can be a literal value (such as 5), a PHP variable (such as $myVar), or an array with the following structure:

array($value [, $direction [, $phpType [, $sqlType]]])

This array is used to specify the parameter value, the
parameter direction (in the case where the parameter is being passed to a
stored procedure), the PHP type of the parameter, and the SQL Server type of a
value sent to the server. For more information about this array, see the Sending Images to the Server section. For more
information, see Using
Directional Parameters, How to: Send Data as a
Stream, and How to: Specify SQL Server
Data Types in the product documentation.

· $options – This optional parameter (not shown in
the example) is an associative array that sets properties on the query. Two
keys are supported: QueryTimeout and SendStreamParamsAtExec. The QueryTimeout
key sets the maximum time in seconds that a query is allowed to run. The SendStreamParamsAtExec
key determines if all stream data is sent at the time of query execution or if
subsequent calls to sqlsrv_send_stream_data
are necessary to send all stream data. For more information, see How to: Send Data as a
Stream.

Retrieving Data from the
Server

The general pattern for retrieving data with the SQL Server 2005 Driver for PHP involves defining and executing a query (see the Executing a Query section) and then using one of the following three options to retrieve data from the result set.

· The sqlsrv_fetch_array
function (retrieves a row of data as an array).

· The sqlsrv_fetch_object
function (retrieves a row of data as a PHP object).

· The combination of the sqlsrv_fetch and sqlsrv_get_field functions (retrieves a single field from a row of data).

Note These functions provide forward-only
access to the rows of a result set.

When choosing which option to use, consider the following:

· The sqlsrv_fetch_array and sqlsrv_fetch_object
functions pull an entire row of data into script memory. This may not be desirable
for rows that contain large amounts of data.

· Data returned by the sqlsrv_fetch_array and sqlsrv_fetch_object functions will be typed according to the defaults PHP data types
assigned by the driver. For more information, see Default PHP Data
Types in the product documentation.

· Using the combination of sqlsrv_fetch and sqlsrv_get_field
allows you to specify the PHP data type of the returned data, including
specification of the data as a stream.

For more information about data retrieval functionality, see Comparing Data Retrieval Functions in the product documentation.

For more information about retrieving data with the SQL Server 2005 Driver for PHP see Retrieving Data in the product documentation.

Retrieving Data as an Array

In this section, we examine code in the Example Application that retrieves data as an array. The following code uses the sqlsrv_fetch_array function to retrieve one row at a time from a result set. Each row is retrieved as an associative array and is passed to the custom function PopulateProductsTable for processing:

$productCount = 0;
while( $row = sqlsrv_fetch_array( $getProducts, SQLSRV_FETCH_ASSOC))
{
PopulateProductsTable(
$row );
$productCount++;
}

The sqlsrv_fetch_array function
accepts two parameters, $stmt and $fetchType (optional):

· The $stmt parameter is a PHP statement resource created
with sqlsrv_query or sqlsrv_execute (see the Executing a Query
section).

· The $fetchType parameter (optional) is a driver-defined
constant that specifies what type of array will be returned: associative,
numeric, or both. The corresponding constants are SQLSRV_FETCH_ASSOC,
SQLSRV_FETCH_NUMERIC, and SQLSRV_FETCH_BOTH. By default, an array with both
types of indices is returned.

Retrieving Images

In this section, we examine code in the Example Application that retrieves an image from the server. The code below executes a query that retrieves an image from the server, specifies that the returned data be retrieved as a binary stream, and dumps the data to the Web page using the PHP fpassthru
function:

/* Get the product picture for
a given product ID. */

$tsql = "SELECT LargePhoto
FROM Production.ProductPhoto AS p
JOIN Production.ProductProductPhoto AS q
ON p.ProductPhotoID = q.ProductPhotoID
WHERE ProductID = ?";

$params = array($_REQUEST['productId']);

/* Execute the query. */

$stmt = sqlsrv_query($conn, $tsql, $params);

if( $stmt === false )
{
echo "Error in statement execution.";
die(
print_r( sqlsrv_errors(), true));
}

/* Retrieve the image as a binary stream. */

$fieldIndex = 0;
$getAsType = SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY);

if ( sqlsrv_fetch( $stmt ) )
{
$image = sqlsrv_get_field( $stmt, $fieldIndex, $getAsType);
fpassthru($image);
}
else
{
echo
"Error in retrieving data.";

die(print_r( sqlsrv_errors(), true));}

The code above defines a parameterized Transact-SQL query ($tsql), specifies the parameter value ($params), and executes the query with the sqlsrv_query function (see the Executing
a Query section). The result set is consumed by calling sqlsrv_fetch (to make the next row of the result set available for reading) followed by calling sqlsrv_get_field (to read one field in the active row). The sqlsrv_fetch function takes a single parameter ($stmt in the example) that is a PHP resource created by sqlsrv_query or sqlsrv_execute. The sqlsrv_get_field takes three parameters, $stmt, $fieldIndex, and $getAsType optional):

· The $stmt parameter is a PHP resource corresponding to an
executed statement. The next (or first) row of data is made available to the sqlsrv_get_field function by first passing the $stmt parameter to the sqlsrv_fetch function.

· The $fieldIndex specifies the index of the field to be retrieved. Indices start at zero.

· The $getAsType parameter (optional) is used to specify the PHP type (and encoding, in this case) of the returned data. If this parameter is not provided, data will be returned according to its default PHP type. For more information, see Default PHP Data Types in the product documentation.

For more information about retrieving images and binary/large data, see Retrieving Data as a Stream in the product documentation.

Retrieving data with the combination if the sqlsrv_fetch and sqlsrv_get_field functions can be used to specify the PHP type of returned data, not only for retrieving data as a stream. For more information, see How to: Specify PHP Data Types in the product documentation.

Sending Data to the Server

The general pattern for sending data to the server involves executing an appropriate Transact-SQL query (such as an UPDATE or INSERT query) with the sqlsrv_query function or the combination of the sqlsrv_prepare and sqlsrv_execute functions (see the Executing a Query section). For example, the code below (from the Example Application in the product documentation) sends a product review to the server using the combination of the sqlsrv_prepare and sqlsrv_execute functions:

/*Prepend the review so it can be opened as a stream.*/

$comments = "data://text/plain,".$_REQUEST['comments'];
$stream = fopen( $comments, "r" );

$tsql = "INSERT INTO
Production.ProductReview (ProductID,ReviewerName,ReviewDate,EmailAddress,
Rating,Comments)
VALUES (?,?,?,?,?,?)";

$params = array($_REQUEST['productid'],
$_REQUEST['name'],
date("Y-m-d"),
$_REQUEST['email'],
$_REQUEST['rating'],
$stream);

/* Prepare and execute the statement. */

$insertReview =
sqlsrv_prepare($conn, $tsql, $params);

if( $insertReview === false )
{ die( FormatErrors( sqlsrv_errors() ) ); }

/* By default, all stream data is sent at the time of query execution. */

if( sqlsrv_execute($insertReview) === false )
{ die( FormatErrors( sqlsrv_errors() ) ); }

The parameters for sqlsrv_prepare

are the same as those for sqlsrv_query (see the Executing a Query
section). The sqlsrv_execute function takes one parameter ($insertReview
in the example) that is a PHP resource specifying the prepared statement to be
executed.

Note
The query in the example could have been executed with the sqlsrv_query
function. The recommended practice for executing a single query is to use the sqlsrv_query

function. The use of sqlsrv_prepare and sqlsrv_execute in the
example is done to demonstrate how these functions are used together.)

This example highlights the driver’s
streaming capabilities. The customer’s comments ($comments) are opened
as a text stream ($stream), which is a parameter in the query. By
default, all stream data is sent to the server at the time of query execution.
However, the driver also provides functionality that allows up to 8KB of stream
data to be sent to the server at a time. For
more information, see the Sending Images to the Server section below or How to: Send Data as a
Stream in the product documentation.

For more information about sending
data to the server with the SQL Server 2005 Driver for PHP, see Updating Data in the
product documentation.

Sending
Images to the Server

In this section, we examine code in
the Example Application

that sends an image to the server as a binary stream. The following code opens
an image as a stream and then sends the file up to the server in parts up to 8KB
at a time:

$tsql = "INSERT INTO Production.ProductPhoto
(LargePhoto)VALUES (?);
SELECT SCOPE_IDENTITY() AS PhotoID";
$fileStream =
fopen($_FILES['file']['tmp_name'], "r");
$params = array(array($fileStream, SQLSRV_PARAM_IN,SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_BINARY),SQLSRV_SQLTYPE_VARBINARY('max')));

/* Turn off the default behavior of sending all stream data to the server at the time of query execution. */

$options = array("SendStreamParamsAtExec"=>0);
$uploadPic = sqlsrv_prepare($conn, $tsql, $params, $options);

if( $uploadPic === false )
{ die( FormatErrors( sqlsrv_errors() ) );}

if( sqlsrv_execute($uploadPic) === false )
{ die( FormatErrors( sqlsrv_errors() ) ); }

/* Stream data to the database
in chunks. */

while( $success = sqlsrv_send_stream_data( $uploadPic))

{

}

As noted in the previous example, the
general pattern for sending data to the server involves executing an
appropriate Transact-SQL query (such as an UPDATE or INSERT statement). The
notable differences in this example are described here:

· Types are specified in the in the $params array. The PHP
type must be specified here so that the driver will interpret the data as a
binary stream. The SQL Server type must be specified so that the server will
interpret the incoming data correctly.

For more information,
see How to: Specify PHP
Data Types and How
to: Specify SQL Server Data Types in the product documentation.

· The $options parameter is used when calling sqlsrv_prepare.
The default behavior of sending all stream data to the server at the time of
query execution is turned off by setting "SendStreamParamsAtExec" to
0 in the $options parameter. When this behavior is turned off, calls to sqlsrv_send_stream data

(after query execution) are required to send stream data to the server (see
next bullet).

· The sqlsrv_send_stream
data function is used to send data up to 8KB of stream data to the
server at a time. Turning off the default behavior of sending all stream data
to the server at once and using sqlsrv_send_stream_data to send stream
data allows for flexibility in application design. For example, it allows an
application to present users with a progress bar when uploading a large image.

Moving
Through Groups of Result Sets

The SQL Server 2005 Driver for PHP
provides the sqlsrv_next_result

function for moving forward through multiple results returned by batch queries
or stored procedures. This function makes the next result set, row count, or
output parameter of an active statement available for reading.

The code below demonstrates how to use
sqlsrv_next_result to move through results. The code here is taken from
the Example Application
in the product documentation and immediately follows the code in the previous
example. The $uploadPic statement corresponds to a batch query in the
previous example. The code here moves to the second result in that statement
and uses the retrieved value to execute a query that associates the new
ProductPhotoID with a ProductID:

/*Skip the open (first) result
set (rows affected). */

$next_result =
sqlsrv_next_result($uploadPic);

if( $next_result === false )
{ die( FormatErrors( sqlsrv_errors() ) ); }

/* Fetch the next result
set. */

if( sqlsrv_fetch($uploadPic)=== false)
{ die( FormatErrors( sqlsrv_errors() ) ); }

/* Get the first field - the
identity from INSERT. */

$photoID = sqlsrv_get_field($uploadPic, 0);

/* Associate the new photoID
with the productID. */

$tsql = "UPDATE Production.ProductProductPhoto
SET ProductPhotoID = ?
WHERE ProductID = ?";

$params = array($photoID, $_REQUEST['productid']);

if( sqlsrv_query($conn, $tsql, $params) === false )
{ die( FormatErrors( sqlsrv_errors() ) ); }

The required parameter for the sqlsrv_next_result
function is a PHP resource corresponding to an active statement. It is not
necessary to call sqlsrv_next_result to access the first result of a
statement. This function will return null if there are no more results
on the statement.

For more information about moving
through results, see How to: Work with
Multiple Result Sets and How to: Detect Empty
Result Sets in the product documentation.

Handling
Errors and Warnings

The SQL Server 2005 Driver for PHP
provides the sqlsrv_errors

function for retrieving details about errors and warnings. If an error occurs
in any sqlsrv function, the function returns false and the error
details are added to the error collection. The sqlsrv_errors function
provides access to this error collection.

Note
By default, warnings are treated as errors with a few exceptions: warnings that
correspond to the SQLSTATE values 01000, 01001, 01003, and 01S02 are never
treated as errors. This default behavior can be changed so that warnings are
not treated as errors. For more information, see How to: Configure Error
and Warning Handling in the product documentation and the Configuring the
Driver section.

The general pattern for using sqlsrv_errors

is to check the return value of a sqlsrv function and then handle errors
accordingly. This code from the Example Application in
the product documentation demonstrates the pattern:

if( sqlsrv_execute($insertReview) === false )

{ die( FormatErrors( sqlsrv_errors() ) ); }

The sqlsrv_errors function
returns a collection of arrays, one array for each error that occurred. Each
array contains detailed error information. The custom function FormatErrors

in the Example
Application simply iterates through the collection of arrays and displays
error information:

function FormatErrors( $errors )

{

/* Display errors. */

echo "Error information: ";

foreach ( $errors as $error
)

{

echo "SQLSTATE: ".$error['SQLSTATE']." ";

echo "Code:
".$error['code']." ";

echo "Message: ".$error['message']." ";

}

When evaluating the return value of a sqlsrv

function, it is best to use the PHP triple equals operator (===). This is
because all sqlsrv functions return false if an error occurs. For
sqlsrv functions that could return some value that PHP evaluates to false
it is important to use the triple equals operator to force a literal
comparison. For example, sqlsrv_fetch could return null if there
are no more rows in a result set. In this case, using a double equals operator
(==) to check for an error ($result == false)
would evaluate to true, resulting in unexpected program flow.

For more information, see Handling Errors and
Warnings in the product documentation.

Resources

The following resources are available for
developing applications with the SQL Server 2005 Driver for PHP:

· Download site: SQL Server 2005 Driver for PHP in the Microsoft Download Center

· Peer-to-peer support: SQL Server Driver for PHP in the MSDN Forums

· Online documentation: SQL Server 2005 Driver for PHP Documentation in MSDN Library

· Source code: Microsoft SQL Server 2005 Driver for PHP in CodePlex

Conclusion

The SQL Server 2005 Driver for PHP provides fast and reliable
access to SQL Server data using PHP. The driver leverages both Microsoft and
PHP technologies (such as Windows Authentication, ODBC connection pooling, and PHP
streams) to enable the development of rich PHP Web applications.

For more information:

SQL Server Web site: http://www.microsoft.com/sqlserver/
SQL Server TechCenter: http://technet.microsoft.com/en-us/sqlserver/
SQL Server DevCenter: http://msdn.microsoft.com/en-us/sqlserver/
Data Platform DevCenter: http://msdn.microsoft.com/en-us/data/

Best Practices for Data Warehousing with SQL Server 2008

2008-09-13T01:35:00.000-07:00

Writers: Mark Whitehorn, Solid Quality Mentors; Keith Burns, Microsoft
Technical Reviewer: Eric N. Hanson, Microsoft
Published: July 2008
Applies to: SQL Server 2008

Summary: There is considerable evidence
that successful data warehousing projects often produce a very high return on
investment. Over the years a great deal of information has been collected about
the factors that lead to a successful implementation versus an unsuccessful one.
These are encapsulated here into a set of best practices, which are presented
with particular reference to the features in SQL Server 2008. The
application of best practices to a data warehouse project is one of the best
investments you can make toward the establishment of a successful Business
Intelligence infrastructure.

Introduction

Microsoft SQL Server 2008 represents an excellent choice for the
construction and maintenance of data warehouses in enterprises of all sizes.

The term Business Intelligence (BI) describes the process of
extracting information from data. The operational data in most enterprises is
held in transaction-based systems with specific functions (HR, Sales, Finance,
and so on). Frequently the information requested by decision makers within the
enterprise requires data from several of the operational systems. Indeed, the
more general the question, such as “What is our current profit?” the more
operational systems are likely to be involved in providing data.

An integral part of any BI system is the data warehouse—a central
repository of data that is regularly refreshed from the source systems. The new
data is transferred at regular intervals (often nightly) by extract, transform,
and load (ETL) processes.

Typically the data in the data warehouse is structured as a star
schema [Kim08] although it may also be structured as normalized relational data
[Inmon05] or as a hybrid between the two. No matter which structure is chosen,
after the new data has been loaded into the data warehouse, many BI systems copy
subsets of the data to function-specific data marts where the data is typically
structured as a multi-dimensional OLAP cube as shown in Figure 1.

Figure 1: Overall plan of a data warehouse

Data warehouses have been built in one form or another for over 20 years.
Early in their history it became apparent that building a successful data
warehouse is not a trivial undertaking. The IDC report from 1996 [IDC96] is a
classic study of the state of data warehousing at the time. Paradoxically, it
was used by both supporters and detractors of data warehousing.

The supporters claimed that it proved how effective data warehousing
is, citing that for the 62 projects studied, the mean return on investment (ROI)
over three years was just over 400 percent. Fifteen of those projects (25
percent) showed a ROI of more than 600 percent.

The detractors maintained that the report was a searing
indictment of current data warehouse practices because of 45 projects
(with outliers discounted) 34 percent failed to return even the cost of
investment after five years. A warehouse that has shown no return in that
length of time is not a good investment.

Both sets of figures are accurate and, taken together, reflect the
overall findings of the paper itself which says “One of the more interesting
stories is found in the range of results. While the 45 organizations
included in the summary analysis reported ROI results between 3% and 1,838%,
the total range varied from as low as – 1,857% to as high as 16,000%!”

Worryingly, the trend towards failure for data warehouse projects
continues today: some data warehouses show a huge ROI, others clearly fail. In
a report some nine years later (2005), Gartner predicted that “More than 50 percent
of data warehouse projects will have limited acceptance or will be failures through
2007” (Gartner press
release [Gart07]).

Does this mean that we have learned nothing in the intervening
time? No, we have learned a great deal about how to create successful data
warehouses and a set of best practices has evolved. The problem seems to be
that not everyone in the field is aware of those practices.

In this paper we cover some of the most important data
warehousing features in SQL Server 2008 and outline best practices for
using them effectively. In addition, we cover some of the more general best
practices for creating a successful data warehouse project. Following best
practices alone cannot, of course, guarantee that your data warehouse project
will succeed; but it will improve its chances immeasurably. And it is
undeniably true that applying best practices is the most cost-effective
investment you can make in a data warehouse.

A companion paper [Han08] discusses how to scale up your data
warehouse with SQL Server 2008. This paper focuses on planning, designing,
modeling, and functional development of your data warehouse infrastructure. See
the companion paper for more detail on performance and scale issues associated
with data warehouse configuration, querying, and management.

Benefits of Using Microsoft
Products for

Data Warehousing and Business Intelligence

BI systems are, of necessity, complex. The source data is held in
an array of disparate operational applications and databases. A BI system must turn
these nonhomogeneous sets into a cohesive, accurate, and timely set of useful
information.

Several different architectures can be successfully employed for
data warehouses; however, most involve:

·
Extracting data from source systems, transforming it, and then loading
it into a data warehouse

·
Structuring the data in the warehouse as either third normal form
tables or in a star/snowflake schema that is not normalized

·
Moving the data into data marts, where it is often managed by a
multidimensional engine

·

Reporting in its broadest sense, which takes place from data in
the warehouse and/or the data marts: reporting can take the form of everything
from printed output and Microsoft Office Excel® spreadsheets through rapid
multidimensional analysis to data mining.

SQL Server 2008 provides all the tools necessary to perform these
tasks [MMD07].

·
SQL Server Integration Services (SSIS) allows the creation and
maintenance of ETL routines.

·
If you use SQL Server as a data source, the Change Data
Capture feature simplifies the extraction process enormously.

·

The SQL Server database engine holds and manages the tables that
make up your data warehouse.

·
SQL Server Analysis Services (SSAS) manages an enhanced
multidimensional form of the data, optimized for fast reporting and ease of
understanding.

·
SQL Server Reporting Services (SSRS) has a wide range of
reporting abilities, including excellent integration with Excel and Microsoft
Office Word. PerformancePoint Server™ makes it easy to visualize
multidimensional data.

Moreover, Microsoft Office SharePoint Server® (MOSS) 2007
and Microsoft Office 2007 provide an integrated, easy-to-use, end-user
environment, enabling you to distribute analysis and reports derived from your
data warehouse data throughout your organization. SharePoint can be used to
build BI portal and dashboard solutions. For example, you can build a score
card application on top of SharePoint that enables employees to get a custom
display of the metrics and Key Performance Indicators (KPIs) depending on their
job role.

As you would expect from a complete end-to-end solution, the
level of integration between the components is extremely high. Microsoft Visual
Studio®

provides a consistent UI from which to drive both SQL Server and Analysis
Services and, of course, it can be used to develop BI applications in a wide
range of languages (Visual C#®, Visual C++®, Visual Basic.Net®, and so on).

SQL Server is legendary for its low total cost of ownership (TCO)
in BI solutions. One reason is that the tools you need come in the box at no
extra cost—other vendors charge (and significantly) for ETL tools,
multidimensional engines, and so on. Another factor is the Microsoft renowned
focus on ease of use which, when combined with the integrated development
environment that Visual Studio brings, significantly reduces training times and
development costs.

Best Practices: Creating Value
for Your Business

The main focus of this paper is on technical best practices. However
experience has shown that many data warehouse and BI projects fail not for
technical reasons, but because of the three Ps—personalities, power struggles,
and politics.

Find a sponsor

Your sponsor should be someone at the highest level within the
organization, someone with the will and the authority to give the project the
strong political backing it will need. Why?

Some business people have learned that the control of information
means power. They may see the warehouse as an active threat to their power
because it makes information freely available. For political reasons those
people may profess full support for the BI system, but in practice be
effectively obstructive.

The BI team may not have the necessary influence to overcome such
obstacles or the inertia that may come from the higher management echelons:
that is the job of the sponsor.

Get the architecture right
at the start

The overall architecture of the entire BI system must be
carefully planned in the early stages. An example of part of this process is
deciding whether to align the structure with the design principles of Ralph Kimball
or Bill Inmon (see References).

Develop a Proof of Concept

Deciding upon a Proof of Concept project is an excellent way to
gain support and influence people. This might involve the creation of an OLAP
cube and the use of visualization software for one (or several) business units.
The project can be used to show business people what they can expect from the
new system and also to train the BI team. Choose a project with a small and
well-defined scope that is relatively easy to achieve. Performing a Proof of
Concept requires an investment of time, effort, and money but, by limiting the
scope, you limit the expenditure and ensure a rapid return on the investment.

Select Proof of Concept
projects on the basis of rapid ROI

When choosing a Proof of Concept project, it can be a useful
exercise to talk to ten or so business units about their needs. Score their
requirements for difficulty and for ROI. Experience suggests that there is
often little correlation between the cost of developing a cube and the ROI it
can generate, so this exercise should identify several low-effort, high-return
projects, each of which should be excellent candidates for Proof of Concept projects.

Incrementally deliver high
value projects

By building the most profitable solutions first, a good ROI can
be ensured for the entire data warehouse project after the initial stages. If
the first increment costs, say $250,000 and the ROI is $1 million per
annum, after the first three months of operation the initial outlay will have
been recouped.

The ease of use and high level of integration of between
Microsoft BI components are valuable assets that help you deliver results in a
timely fashion; this helps you to rapidly build support from your business
users and sponsors. The value remains apparent as you build on the Proof of
Concept project and start to deliver benefits across the organization.

Designing Your Data Warehouse/BI solution

In this section two areas of best practices are discussed. The first
are general design guidelines that should be considered at the start of the
project. These are followed by guidance on specifying hardware.

Best Practices: Initial
Design

Keep the design in line
with the analytical requirements of the users

As soon as we start to discuss good data warehouse design we must
introduce both Bill Inmon [Inmon05] and Ralph Kimball [Kim08]. Both contributed
hugely to our understanding of data warehousing and both write about many
aspects of warehouse design. Their views are often characterized in the following
ways:

·

Inmon favors a normalized data warehouse.

·
Kimball favors a dimensional data warehouse.

Warehouses with these structures are often referred to as Inmon
warehouses or Kimball warehouses. Microsoft has no bias either way
and SQL Server is perfectly capable of supporting either design. We
observe that the majority of our customers favor a dimensional warehouse. In
response, we introduced star schema optimization to speed up queries against
fact and dimension tables.

The computer industry has understood how to design transactional
systems since the 1970s. Start with the user’s requirements, which we can
describe as the User model. Formalize those into a Logical model that
users can agree to and approve. Add the technical detail and transform that
into a Physical model. After that, it is just a matter of implementation…

Designing a data warehouse should follow the same principles and
focus the design process on the requirements of the users. The only difference
is that, for a data warehouse, those requirements are not operational but analytical.

In a large enterprise, users tend to split into many groups, each
with different analytical requirements. Users in each group often articulate
the analysis they need (the User model of analysis) in terms of graphs, grids
of data (worksheets), and printed reports. These are visually very different
but all essentially present numerical measures filtered and grouped by the
members of one or more dimensions.

So we can capture the analytical requirements of a group simply by
formalizing the measures and dimensions that they use. As shown in Figure 2
these can be captured together with the relevant hierarchical information in a sun
model, which is the Logical model of the analytical requirements.

Figure 2: A sun model used to capture the analytical
requirements of users in terms of measures, dimensions, and hierarchical
structure. This is the Logical model derived from the User model.

Once these analytical requirements have been successfully
captured, we can add the technical detail. This transforms the Logical model
into a Physical one, the star schema, illustrated in Figure 3.

Figure 3: A representation of a star schema, which is an example
of a Physical model of analytical requirements

The star schemas may ultimately be implemented as a set of
dimensional and fact tables in the data warehouse and/or as cubes (ROLAP, HOLAP,
or MOLAP), which may be housed in multiple data marts.

To design the ETL system to deliver the data for this structure,
we must understand the nature of the data in the source systems.

To deliver the analytics that users outlined in their
requirements, we must find the appropriate data in the source systems and
transform it appropriately. In a perfect world, the source systems come with excellent
documentation, which includes meaningful table and column names. In addition,
the source applications are perfectly designed and only allow data that falls
within the appropriate domain to be entered. Back on planet Earth, source
systems rarely meet these exacting standards.

You may know that users need to analyze sales by the gender of
the customer. Unfortunately, the source system is completely undocumented but
you find a column named Gen in a table called CUS_WeX4. Further investigation
shows that the column may store data relating to customer gender. Suppose that
you can then determine that it contains 682370

rows and that the distribution of the data is:

M	234543
F	342322
Mail	5
Femal	9
Female	4345
Yes	43456
No	54232
Male	3454
Girl	4

You now have more evidence that this is the correct column and
you also have an idea of the challenges that lie ahead in designing the ETL
process.

In other words, knowledge of the distribution of the data is
essential in many cases, not only to identify the appropriate columns but also
to begin to understand (and ultimately design) the transformation process.

Use data profiling to
examine the distribution of the data in the source systems

In the past, gaining an understanding of the distribution of data
in the source systems meant running multiple queries (often GROUP BYs) against the
source system tables to determine the domain of values in each column. That
information could be compared to the information supplied by the domain experts
and the defined transformations.

Integration Services has a new Data Profiling task that makes the
first part of this job much easier. You can use the information you gather by using
the Data Profiler to define appropriate data transformation rules to ensure that
your data warehouse contains “clean” data after ETL, which leads to more
accurate and trusted analytical results. The Data Profiler is a data flow task
in which you can define the profile information you need.

Eight data profiles are available; five of these analyze
individual columns:

·

Column Null Ratio

·
Column Value Distribution

·
Column Length Distribution

·
Column Statistics

·
Column Pattern

Three analyze either multiple columns or relationships between
tables and columns:

·

Candidate Key

·
Functional Dependency

·
Value Inclusion

Multiple data profiles for several columns or column combinations
can be computed with one Data Profiling task and the output can be directed to
an XML file or package variable. The former is the best option for ETL design
work. A Data Profile Viewer, shown in Figure 4, is provided as a
standalone executable that can be used to examine the XML file and hence the
profile of the data.

Figure 4: The Data Profile Viewer

Design from the start to
partition large tables, particularly large fact tables

No matter how good the indexing and how fast the hardware, large
tables can lead to time-consuming system management operations, such as index
creation and removal of old data. This can be particularly apparent for large
fact tables. So, partition the tables if they are large. In general, if a table
is larger than 50 GB, you should partition it (see Relational Data Warehouse Setup, Query, and
Management later in this white paper).

Plan to age out old data
right from the start

One of the main functions of a data warehouse is to track the
business history of the enterprise—something that the source systems generally
do particularly badly. Tracking history means that the data warehouse will acquire
vast quantities of data over time. As data ages, it is accessed less frequently
and also differently (typically as aggregations rather than the detail). It eventually
becomes necessary to treat older data differently, perhaps by using slower,
cheaper storage, perhaps storing only the aggregations, removing it altogether,
or another plan. It is vital to plan for this right from the start. Partitioning
makes this much easier (see Relational Data
Warehouse Setup, Query, and Management later in this white paper).

Best Practices: Specifying Hardware

Design for maintenance
operation performance, not just query performance

Specifying hardware is never easy but it is common (and good)
practice when making hardware decisions to focus most of the attention on query
performance for the simple reason that query performance is vital. However, it
is also important not to overlook other considerations.

Think about query performance. A very simplistic view is that it
scales in a linear fashion with data size. Generations of DBAs have learned the
hard way that it often does not scale in this way—double the size of the data
and the query may take ten times as long to run—so they may plan hardware
requirements accordingly. But this is a somewhat simplistic approach.

For example, consider a query that is run against a 1‑terabyte
table. Its performance is limited by many factors—indexing, memory, number of
rows scanned, number of rows returned and so on. Imagine that the query uses
indexes efficiently, scans some fixed number of rows, and returns only a few
rows. If we run that same query against 2 terabytes of data in the same
table and assume the indexing has been applied efficiently and that the number
of scanned and returned rows is not significantly different, the response time is
about the same. Certainly it will be nothing like double the time. In other
words, query performance can sometimes scale in a nonlinear way to our
advantage. However other factors, such as backup time, do scale linearly in the
expected way. Backing up twice the data takes twice the resources (such as CPU
time and I/O bandwidth), which may well affect the hardware specifications
needed to meet your requirements.

In other words, when we design large BI systems we must be
careful to consider all the relevant factors. This does not mean that query
performance is unimportant; it is still the most important consideration. But
it is also a mistake to focus solely on that issue. Other factors, such as
maintenance of the BI system (both the relational and multidimensional
components) must be considered carefully as well.

Specify enough main memory
so most queries never do I/O

According to Microsoft SQL Server Customer Advisory Team
engineers, in most data warehouses the data is very unevenly accessed. By far
the majority of queries access some proportion of the same 20% of the total; in
other words, about 80% of the data is rarely accessed. This means that disk I/O
for the warehouse overall reduces dramatically if there is enough main memory
to hold about 20% of the total data.

ETL

The field of ETL (extract, transform, and load) is the most
complex area of data warehousing [Hath07]. The data itself is often complex and
requires a whole range of techniques and processes to be applied before it is
loaded into the warehouse. These include merging, time/date stamping,
de-duplication and survivorship, data type conversion, normalization and/or
denormalization, surrogate key insertion, and general cleansing.

Best Practices: Simplify
the ETL Process and Improve Performance

Use
SSIS to simplify ETL programming

SSIS was designed from the ground up to simplify the process of developing
your

ETL code. Its automated tools and predefined transformations and connectors
vastly reduce the number of lines of ETL code you have to write compared to
programming in a traditional high-level language or scripting language. It is a
best practice to use SSIS rather than these other methods if reducing the
amount of code for ETL and simplifying ETL job maintenance is important to you.

SSIS provides a comprehensive set of features for building data
warehouses, including:

·
A scalable pipeline architecture that provides a multithreaded 64‑bit
platform to transform growing data volumes in narrower batch windows,

·
Connectivity to non-SQL Server RDBMs, mainframes, ERP systems and
other heterogeneous data sources.

·
A large number of complex transformations to consolidate data
from numerous systems.

·

Advanced data cleansing transformations to reduce data
duplication and dirty data.

·
Data warehouse awareness by providing out-of-the-box capability
to manage slowly changing dimensions.

·
Seamless integration with the SQL Server BI platform to directly
build Analysis Services cubes and load into Analysis Services partitions.

·
Extensibility with the power of .NET by incorporating custom
scripts and pluggable components directly into the data integration flow.

Simplify the transformation
process by using Data Profiling tasks

The new Data Profiling task in Integration Services can be used
to initially understand the nature of the source data for design purposes (see Designing Your Data Warehouse/BI Solution). However,
the profiles it produces can also be used to apply business rules to data as
part of the transformation process. Suppose, for example, the business rule
says that the data from a particular source is acceptable only if the number of
nulls does not exceed 1%. The profiles produced by a Data Profiling task
can be used to apply this rule.

Note that Data Profiling tasks profile SQL Server tables;
data in other locations must be loaded into staging tables before it can be
profiled.

Simplify using MERGE and
INSERT INTO

Whether you are extracting from the source systems into an
Operational Data Store (ODS) or from the ODS into the fact or dimension tables,
you must manage the movement of the changes (the deltas) that have occurred. During
this phase you often need multiple Data Manipulation Language (DML) queries in
order to perform one logical movement of the deltas into the relevant table.
This is particularly true when you have to deal with slowly changing dimensions
(and who doesn’t?).

SQL Server 2008 allows you to combine these multiple queries into
one MERGE statement.

MERGE

The MERGE statement performs insert, update, or delete operations
on a target table based on the results of a join with a source table.

The MERGE statement provides three types of WHEN clauses:

·

WHEN MATCHED enables you to UPDATE or DELETE the given row in the
target table when the source and target rows match some criteria or criterion.

·
WHEN NOT MATCHED [BY TARGET] enables you to INSERT a row into the
target when it exists in the source but not in the target.

·
WHEN NOT MATCHED BY SOURCE enables you to UPDATE or DELETE the
given row in the target table when it exists in the target but not in the
source.

You can specify a search condition with each of the WHEN clauses
to choose which type of DML operation should be performed on the row.

The OUTPUT clause for the MERGE statement includes a new virtual
column called $action that you can use to identify the DML action that
was performed on each row.

To illustrate the use of the MERGE statement, imagine that you
have a table of Employees:

EmpID	Name	Title	IsCurrent
1	Jones	Ms	Yes
2	Smith	Prof	Yes
3	Brown	Mr	Yes
4	Wilson	Dr	Yes
5	Carlton	Dr	Yes

and a table of changes:

EmpID	Name	Title	IsCurrent
1	Jones	Prof	Yes
6	Heron	Mr	Yes

Type
1 slowly changing dimension

Suppose first that the Employee table is a Type 1 slowly
changing dimension, meaning that changes to the Title field are simply allowed
to overwrite the existing value and no history is kept of the change nor of the
previous value. Further assume that new rows in the source are to be inserted
into the Employees table as well. You can manage this simple case with a MERGE
statement:

MERGE Employee as TRG

USING EmployeeDelta AS SRC

ON (SRC.EmpID = TRG.EmpID)

WHEN NOT MATCHED THEN

INSERT VALUES (SRC.EmpID, SRC.Name, SRC.Title, 'Yes')

WHEN MATCHED THEN

UPDATE SET TRG.Title = SRC.Title

If we add an OUTPUT clause like this:

OUTPUT $action, SRC.EmpID, SRC.Name, SRC.Title;

we get the following result rowset in addition to the effect on
the Employee table:

$action	EmpID	Name	Title
INSERT	6	Heron	Mr
UPDATE	1	Jones	Prof

This can be very helpful with debugging but it also turns out to
be very useful in dealing with Type 2 slowly changing dimensions.

Type
2 slowly changing dimensions

Recall that in a Type 2 slowly changing dimension a new
record is added into the Employee dimension table irrespective of whether an
employee record already exists. For example, we want to preserve the existing
row for Jones but set the value of IsCurrent in that row to ‘No’. Then we want
to insert both of the rows from the delta table (the source) into the target.

MERGE Employee as TRG

USING EmployeeDelta AS SRC

ON (SRC.EmpID = TRG.EmpID AND TRG.IsCurrent = 'Yes')

WHEN NOT MATCHED THEN

INSERT VALUES (SRC.EmpID, SRC.Name, SRC.Title, 'Yes')

WHEN MATCHED THEN

UPDATE SET TRG.IsCurrent = 'No'

OUTPUT $action, SRC.EmpID, SRC.Name, SRC.Title;

This statement sets the value of IsCurrent to ‘No’ in the
existing row for Jones and inserts the row for Heron from the delta table into the
Employee table. However, it does not insert the new row for Jones. This does
not present a problem because we can address that with the new INSERT
functionality, described next. In addition, we have the output, which in this
case is:

$action	EmpID	Name	Title
INSERT	6	Heron	Mr
UPDATE	1	Jones	Prof

New
functionality for INSERT

We can combine the capacity to output the data with a new ability
in SQL Server 2008 to have INSERT statements consume the results of DML
statements. This ability to consume output can, of course, be used very effectively
(and simply) as follows:

INSERT INTO Employee (EmpID, name, Title)

SELECT EmpID, name, Title from EmployeeDelta

If we combine this new capability with the output above, we have the
synergistic ability to extract the row that was updated (Jones) and

insert it into the Employees table to achieve the desired effect in the context
of Type 2 slowly changing dimensions:

INSERT INTO Employee( EMPID, Name, Title, IsCurrent)

SELECT EMPID, Name, Title, 'Yes'

FROM

(

MERGE Employee as TRG

USING EmployeeDelta AS SRC

ON (SRC.EmpID = TRG.EmpID AND TRG.IsCurrent = 'Yes')

WHEN TARGET NOT MATCHED THEN

INSERT VALUES (SRC.EmpID, SRC.Name, SRC.Title, 'Yes')

WHEN MATCHED THEN

UPDATE SET TRG.IsCurrent = 'No'

OUTPUT $action, SRC.EmpID, SRC.Name, SRC.Title

)

As Changes (action, EmpID, Name, Title)

WHERE action ='UPDATE';

MERGE in SQL Server 2008 was implemented to comply with the SQL-2006
standard. The main reason for the introduction of MERGE into the SQL standard
and into SQL Server 2008 is its usefulness in managing slowly changing
dimensions but it is worth remembering that both MERGE and INSERT with output
have many other applications both within data warehousing specifically and in databases
in general.

Terminate all SQL
statements with a semi-colon in SQL Server 2008

Prior to SQL Server 2005, Transact-SQL was relatively
relaxed about semi-colons; now some statements (including MERGE) require this
terminator. If you terminate all SQL statements with a semi-colon, you avoid
problems when the use of a semi-colon is obligatory.

If you cannot tolerate
downtime, consider using “ping pong” partitions

Imagine that you have a fact table that is partitioned by month. It
is currently August. You need to load today’s data into the August partition,
but your users cannot tolerate the performance hit and potential locking
conflicts that will be incurred as you load it. Copy the August partition to
another table (a working table), load the data into that table, index it as
needed, and then simply switch the partitions between the two tables.

Use minimal logging to load
data precisely where you want it as fast as possible

Writing data to a database typically involves two separate write-to-disk
processes, once writing to the database and once to the log (so transactions
can be rolled back or re-done). When inserting data into an existing table it
is, in fact, possible to write only once in some cases by using the minimally
logged INSERT feature.

Minimal logging enables transactions to be rolled back but does
not support point-in-time recovery. It is also only available with the bulk
logged and simple recovery models. In SQL Server 2008, minimal logging can
be used with INSERT INTO…SELECT FROM Transact-SQL statements when inserting a
large number of rows into an existing table if they are inserted into an empty
table with a clustered index and no nonclustered indexes, or a heap, empty or
not, with no indexes. (For full details and any late-breaking changes, see SQL
Server 2008 Books Online.)

This extends the support for minimal logging, which in SQL Server 2005
included bulk import, SELECT INTO, index creation, and rebuild operations.

One huge benefit of minimal logging is that it speeds up the
loading of empty partitions or tables that are on specific filegroups. In SQL
Server 2005, you could achieve effectively the same effect by using a
work-around that involved changing the default filegroup and performing a
SELECT INTO to get minimal logging. Then the default filegroup would be returned
to its initial state. Now you can just create a table on the filegroup(s) you
want it to be in, define its partitioning scheme and then load it with INSERT
INTO tbl WITH(NOLOCK) SELECT FROM and you acheive minimal logging.

Minimal logging makes it much easier to put the data just where
you want it and write it to disk only once. As an added bonus, load performance
is increased and the amount of log space required is reduced.

Simplify data extraction by
using Change Data Capture in the SQL Server source systems

SQL Server 2008 has a new data tracking feature that is of particular
benefit in data warehousing. The Change Data Capture process tracks changes to
user tables and collects them into a relational format. A typical use would be
to track changes in an operational database for later inclusion in the
warehouse.

The capture process collects change data from the database’s
transaction log and inserts it into a change table. Metadata about each
transaction is also inserted into a metadata table so that changes can be
ordered with regard to time. This enables the identification of, for instance,
the type of change made to each row, and which column or columns changed in an
updated row. It is also possible to request all rows that changed between two
time/dates. Change Data Capture is a big step towards improved extraction performance,
and makes programming the change capture portion of your ETL jobs much easier.

Simplify and speed up ETL
with improved Lookup

The performance of the Lookup component has greatly improved
performance in SQL Server 2008 Integration Services and is much easier to
program.

A Lookup verifies whether each row in a stream of rows has a
matching row in a set of reference data. This is often used within the ETL
process to check, for example, the ProductID column in a fact table (acting as
the data source) against a dimension table holding a complete set of products
(the reference set).

In SQL Server 2008, the Lookup
transformation supports two connection types when connecting to the reference
dataset: the Cache connection manager and the OLE DB connection manager. The
reference dataset can be a cache file, an existing table or view, a new table, or
the result of an SQL query.

Reference data is usually cached for efficiency and now a
dataflow can be used to populate the cache. Many potential sources can be used
as reference data: Excel, XML, text, Web services—anything within reach of an
ADO.Net provider. In SQL Server 2005, the cache could only be populated by
an SQL query and a Lookup could only take data from specific OLE /DB
connections. The new Cache Transform component populates a cache defined by the
Cache connection manager.

The cache no longer needs to be reloaded each time it is used:
this removes the speed penalty incurred by reloading from a relational source. If
a reference dataset is used by two pipelines in a single package, the cache can
be saved to permanent file storage as well as to virtual memory so it is
available to multiple Lookups within one package. Furthermore the cache file
format is optimized for speed and its size is unrestricted.

The miss-cache feature is also new. When running directly against
the dataset, a Lookup component can add to the cache any key values from the
source where there is no matching value in the reference dataset. So if Lookup
has once determined that the reference set does not contain, for example, the
value 885, it does not waste time inspecting the reference set for that value
if it appears again in the source data. Under certain conditions this feature
can produce a performance improvement of 40%.

Finally there is now a ‘Lookup no match output’ to which ‘miss-cache’
rows can be directed instead of going to the error output.

Relational Data Warehouse Setup, Query,
and Management

The relational database is the heart of any BI system. Best
practices here affect not only the performance of the entire system, but also
its flexibility and value to the enterprise. For a more in-depth discussion of
how to get the best performance from your SQL Server 2008 data warehouse
for large-scale data warehouses, see the companion paper [Han08].

Best Practices: General

Use the resource governor
to reserve resources for important work such as data loading, and to prevent
runaway queries

The workload on a data warehouse can be thought of as a series of
requests that compete for the available resources. In SQL Server 2005,
there was a single pool of resources and requests competed equally for those. The
resource governor in SQL Server 2008 allows the available resources to be
allocated into multiple (up to 20) pools. Requests are classified so that
they fall into specific groups and those groups are allocated to the resource pools—many
requests to each resource pool. Processes that must complete rapidly (such as
the data load) can be allocated high resources when they run. In addition,
important reports can be allocated enough resources to ensure that they
complete rapidly [Bar08].

Many users find unpredictability of performance highly
frustrating. If this occurs, it is beneficial to use the resource governor to
allocate resources in a way that ensures more predictable performance. Over
time, as experience with the resource governor grows, we expect this to evolve
into a best practice.

Suppose, as is often the case, that the data warehouse is used
for both reporting and ETL processes and is configured for zero or minimal
downtime during loads. In some enterprises (despite what the BI team might
prefer), the generation of reports on time is seen as more important than the
completion of the daily load. In this case the reporting workload group would
be allocated a high priority.

Or, by contrast, the load processes may be given high priority in
order to guarantee the minimum downtime that the business requires.

Finally, it is worth noting that the resource governor also enables
you to monitor the resource consumption of each group, which means that resource
usage can be better understood, which in turn allows better management.

Carefully plan when to
rebuild statistics and indexes

The query optimizer uses information from the database statistics
(number of rows, data distribution, and so on) in order to help determine the
optimal query plan. If the statistics are inaccurate, a less optimal plan may
be chosen, which degrades query performance.

If you can afford the time during your ETL process, rebuild
statistics after every load of the data warehouse. This ensures that the statistics
are always accurate. However, rebuilding statistics takes time and resources. In
addition, the time between rebuilds is less important than the change to the
distribution of the data that has occurred.

When the data warehouse is new and evolving, and also relatively
small, it makes sense to update statistics frequently, possibly after every
load. As the warehouse matures, you can sometimes reduce the frequency of
rebuilding without degrading query performance significantly. If it is
important to reduce the cost of updating statistics, you can determine the
appropriate statistic refresh frequency by monitoring query performance as you
reduce the frequency of refreshes. Be aware that if the bulk of your queries
target only the most recently loaded data, you will not have statistics for
that data unless you update statistics after each load. This is a fairly common
situation, which is why we recommend updating statistics after each load of the
data warehouse by default.

Best Practices: Date/time

Use the correct time/date
data type

SQL Server 2008 has six date and time data types:

·

date

·
datetime2

·
datetime

·
datetimeoffset

·
smalldatetime

·

time

These store temporal information with varying degrees of
precision. Choosing the right one enables you to maintain accurate time and date
information in a way that suits your application best, saves storage space, and
improves query performance. For example, many older SQL Server
applications use datetime for dates, but leave the time portion blank.
This takes more space than necessary. Now, you can use the new date type
for these columns, which takes only three bytes compared to eight bytes
for datetime.

Consider using datetime2

in some database ports

datetime2 can be considered to be an extension of the
existing datetime type—it combines a date with a time based on the
24-hour clock. However, datetime2 has a larger date range, a larger
default fractional precision, and an optional user-specified precision. The
precision is such that it can store fractions of a second to seven digits—in other
words to within one ten millionth of a second. This new feature can influence
the porting of some data warehouse applications.

For example, the DB2 TimeStamp data type has an accuracy of one
millionth of a second. In a data warehouse application written on DB2, if the
application logic is built to ensure that new records are created at least one
microsecond apart (which is not a particularly onerous limitation), time/date can
be used as a unique ID. We can debate whether it is a best practice to design a
database application in this way but the fact is that it is sometimes done with
DB2.

Before the advent of datatime2, if such an application were
ported to SQL Server, the application logic would have to be rewritten
because datetime provides an accuracy of only a thousandth of a second. Because

datetime2 is ten times more precise than DB2’s TimeStamp, the
application can now be ported with no change to the logic.

Best Practices: Compression
and Encryption

Use PAGE compression to reduce data volume and
speed up queries

Full-blown data compression capability has been added to SQL
Server 2008; the improvements come in two types—row and page.

Row compression stores all fields in variable width format. If
the data is compressible, row compression reduces the number of bytes required
to store it.

Page compression does the same but the compression takes place
between the rows within each page. A page-level dictionary is used to store
common values, which are then referenced from the rows themselves rather than
stored redundantly. In addition, common prefixes of column values are stored
only once on the page. As an illustration of how prefix compression can help, consider
product codes where the prefixes are often similar.

Code	Quantity
A-F234-10-1234	1834
A-F234-10-1235	1435
A-F234-10-1236	1237
A-F234-10-1237	1546
A-F234-10-1238	1545
A-F234-10-1239	1543
A-F234-10-1240	1756
A-F234-10-1241	1435
A-F234-10-1242	1544

This can be compressed to:

A-F234-10-12

1000

Code	Quantity
34	834
35	435
36	237
37	546
38	545
39	543
40	756
41	435
42	544

Even a column like Quantity can benefit from compression. For
more details on how row and page compression work in SQL Server 2008, see SQL
Server 2008 Books Online.

Both page and row compression can be applied to tables and
indexes.

The obvious benefit is, of course, that you need less disk space.
In tests, we have seen compression from two- to seven-fold, with three-fold being
typical. This reduces your disk requirements by about two thirds.

A less obvious but potentially more valuable benefit is found in
query speed. The gain here comes from two factors. Disk I/O is substantially
reduced because fewer reads are required to acquire the same amount of data. Secondly
the percentage of the data that can be held in main memory is increased as a
function of the compression factor.

The main memory advantage is the performance gains enabled by
compression and surging main memory sizes.

Query performance can improve ten times or more if you can get
all the data that you ever query into main memory and keep it there. Your
results depend on the relative speed of your I/O system, memory, and CPUs. A
moderately large data warehouse can fit entirely in main memory on a commodity four-processor
server that can accommodate up to 128 GB of RAM—RAM that is increasingly
affordable. This much RAM can hold all of a typical 400‑GB data
warehouse, compressed. Larger servers with up to 2 terabytes of memory are
available that can fit an entire 6‑terabyte data warehouse in RAM.

There is, of course, CPU cost associated with compression. This
is seen mainly during the load process. When page compression is employed we have
seen CPU utilization increase by a factor of about 2.5. Some specific
figures for page compression, recorded during tests on both a 600‑GB and a
6‑terabyte data warehouse with a workload of over a hundred different
queries, are a 30-40% improvement in query response time with a 10-15% CPU
time penalty.

So, in data warehouses that are not currently CPU-bound, you
should see significant improvements in query performance at a small CPU cost. Writes
have more CPU overhead than reads.

This description of the characteristics of compression should enable
you to determine your optimal compression strategy for the tables and indexes
in the warehouse. It may not be as simple as applying compression to every
table and index.

For example, suppose that you partition your fact table over time,
such as by Quarter 1, Quarter 2, and so on. The current partition is
Quarter 4. Quarter 4 is updated nightly, Quarter 3 far less
frequently, and Quarters 1 and 2 are never updated. However, all are
queried extensively.

After testing you might find that the best practice is to apply
both row and page compression to Quarters 1 and 2, row compression Quarter 3,
and neither to Quarter 4.

Your mileage will, of course, vary and testing is vital to
establish best practices for your specific requirements. However, most data
warehouses should gain significant benefit from implementing a compression strategy.
Start by using page compression on all fact tables and fact table indexes. If
this causes performance problems for loading or querying, consider falling back
to row compression or no compression on some or all partitions.

If you use both compression
and encryption, do so in that order

SQL Server 2008 allows table data to be encrypted. Best practice
for using this depends on circumstances (see above)
but be aware that much of the compression described in the previous best
practice depends on finding repeated patterns in the data. Encryption actively
and significantly reduces the patterning in the data. So, if you intend to use
both, it is unwise to first encrypt and then compress.

Use backup compression to
reduce storage footprint

Backup compression is now available and should be used unless you
find good reason not to do so. The benefits are the same as other compression
techniques—there are both speed and volume gains. We anticipate that for most
data warehouses the primary gain of backup compression is in the reduced
storage footprint, and the secondary gain is that backup completes more
rapidly. Moreover, a restore runs faster because the backup is smaller.

Best Practices: Partitioning

Partition large fact tables

Partitioning a table means splitting it horizontally into smaller
components (called partitions). Partitioning brings several benefits. Essentially,
partitioning enables the data to be segregated into sets. This alone has huge
advantages in terms of manageability. Partitions can be placed in different
filegroups so that they can be backed up independently. This means that we can
position the data on different spindles for performance reasons. In data
warehouses it also means that we can isolate the rows that are likely to change
and perform updates, deletes, and inserts on those rows alone.

Query processing can be improved by partitioning because it
sometimes enables query plans to eliminate entire partitions from
consideration. For example, fact tables are frequently partitioned by date,
such as by month. So when a report is run against the July figures, instead of
accessing 1 billion rows, it may only have to access 20 million.

Indexes as well as tables can be (and frequently are)
partitioned, which increases the benefits.

Imagine a fact table of 1 billion rows that is not
partitioned. Every load (typically nightly) means insertion, deletion, and
updating across that huge table. This can incur huge index maintenance costs,
to the point where it may not be feasible to do the updates during your ETL
window.

If the same table is partitioned by time, generally only the most
recent partition must be touched, which means that the majority of the table
(and indexes) remain untouched. You can drop all the indexes prior to the load
and rebuild them afterwards to avoid index maintenance overhead. This can
greatly improve your load time.

Partition-align your
indexed views

SQL Server 2008 enables you to create indexed views that are
aligned with your partitioned fact tables, and switch partitions of the fact
tables in and out. This works if the indexed views and fact table are
partitioned using the same partition function. Typically, both the fact tables
and the indexed views are partitioned by the surrogate key referencing the Date
dimension table. When you switch in a new partition, or switch out an old one,
you do not have to drop the indexed views first and then re‑create them
afterwards. This can save a huge amount of time during your ETL process. In
fact, it can make it feasible to use indexed views to accelerate your queries
when it was not feasible before because of the impact on your daily load cycle.

Design your partitioning
scheme for ease of management first and foremost

In SQL Server 2008 it is not necessary to create partitions to
get parallelism, as it is in some competing products. SQL Server 2008
supports multiple threads per partition for query processing, which
significantly simplifies development and management. When you design your
partitioning strategy, choose your partition width for the convenience of your
ETL and data life cycle management. For example, it is not necessary to
partition by day to get more partitions (which might be necessary in SQL Server 2005
or other DBMS products) if partitioning by week is more convenient for system
management.

For best parallel
performance, include an explicit date range predicate on the fact table in
queries, rather than a join with the Date dimension

SQL Server generally does a good job of processing queries
like the following one, where a date range predicate is specified by using a
join between the fact table and the date dimension:

select top 10
p.ProductKey, sum(f.SalesAmount)

from
FactInternetSales f, DimProduct p, DimDate d

where
f.ProductKey=p.ProductKey and p.ProductAlternateKey like N'BK-%'

and f.OrderDateKey =
d.DateKey

and
d.MonthNumberOfYear = 1

and d.CalendarYear =
2008

and
d.DayNumberOfMonth between 1 and 7

group by
p.ProductKey

order by
sum(f.SalesAmount) desc

However, a query like this one normally uses a nested loop join
between the date dimension and the fact table, which can limit parallelism and
overall query performance because, at most, one thread is used for each
qualifying date dimension row. Instead, for the best possible performance, put an
explicit date range predicate on the date dimension key column of the fact
table, and make sure the date dimension keys are in ascending order of date.
The following is an example of a query with an explicit date range predicate:

select top 10
p.ProductKey, d.CalendarYear, d.EnglishMonthName,

sum(f.SalesAmount)

from
FactInternetSales f, DimProduct p, DimDate d

where
f.ProductKey=p.ProductKey and p.ProductAlternateKey like N'BK-%'

and OrderDateKey
between 20030101 and 20030107

and
f.OrderDateKey=d.DateKey

group by
p.ProductKey, d.CalendarYear, d.EnglishMonthName

order by sum(f.SalesAmount) desc

This type of query typically gets a hash join query plan and will
fully parallelize.

Best Practice: Manage
Multiple Servers Uniformly

Use Policy-Based Management
to enforce good practice across multiple servers

SQL Server 2008 introduces Policy-Based Management, which
makes it possible to declare policies (such as "all log files must be
stored on a disk other than the data disk") in one location and then apply
them to multiple servers. So a (somewhat recursive) best practice is to set up best
practices on one server and apply them to all servers. For example, you might build
three data marts that draw data from a main data warehouse and use Policy-Based
Management to apply the same rules to all the data marts.

Additional Resources

You can find additional tips mostly related to getting the best
scalability and performance from SQL Server 2008 in the companion white
paper [Han08]. These tips cover a range of topics including storage
configuration, query formulation, indexing, aggregates, and more.

Analysis

There are several excellent white papers on Best Practices for
analysis such as OLAP
Design Best Practices for Analysis Services 2005, Analysis
Services Processing Best Practices , and Analysis
Services Query Performance Top 10 Best Practices. Rather than repeat their
content, in this paper we focus more specifically on best practices for
analysis in SQL Server 2008.

Best Practices: Analysis

Seriously consider the best
practice advice offered by AMO warnings

Good design is fundamental to robust, high-performance systems,
and the dissemination of best practice guidelines encourages good design. A
whole new way of indicating where following best practice could help is
incorporated into SQL Server 2008 Analysis Services.

SQL Server 2008 Analysis Services shows real-time suggestions
and warnings about design and best practices as you work. These are implemented
in Analysis Management Objects (AMO) and displayed in the UI as blue wiggly underlines:
hovering over an underlined object displays the warning. For instance, a cube
name might be underlined and the warning might say:

The ‘SalesProfit’ and ‘Profit’ measure groups have the
same dimensionality and granularity. Consider unifying them to improve
performance. Avoid cubes with a single dimension.

Over 40 different warnings indicate where best practice is
not being followed. Warnings can be dismissed individually or turned off
globally, but our recommendation is to follow them unless you have an active
reason not to do so.

Use MOLAP writeback instead
of ROLAP writeback

For certain classes of business applications (forecasting,
budgeting, what if, and so on) the ability to write data back to the cube can be
highly advantageous.

It has for some time been possible to write back cell values to
the cube, both at the leaf and aggregation levels. A special writeback
partition is used to store the difference (the deltas) between the original and
the new value. This mechanism means that the original value is still present in
the cube; if an MDX query requests the new value, it hits both partitions and
returns the aggregated value of the original and the delta.

However, in many cases, despite the business need, performance
considerations have limited the use of writeback.

In the previous implementation, the writeback partition had to
use ROLAP storage and this was frequently a cause of poor performance. To
retrieve data it was necessary to query the relational data source and that can
be slow. In SQL Server 2008 Analysis Services, writeback partitions can be
stored as MOLAP, which removes this bottleneck.

While it is true that this configuration can slow the writeback
commit operation fractionally (both the writeback table and the MOLAP partition
must be updated), query performance dramatically improves in the majority of
cases. One in-house test of a 2 million cell update showed a five-fold
improvement in performance.

Use SQL Server 2008 Analysis
Services backup rather than file copy

In SQL Server 2008 Analysis Services, the backup storage
subsystem has been rewritten and its performance has improved dramatically as Figure 5
shows.

Figure 5: Backup performance in SQL Server 2005 Analysis
Services versus SQL Server 2008 Analysis Services. X = Time Y=Data volume

Backup now scales linearly and can handle an Analysis Services
database of more than a terabyte. In addition, the limitations on backup size
and metadata files have been removed. Systems handling large data volumes can
now adopt the backup system and abandon raw file system copying, and enjoy the
benefits of integration with the transactional system and of running backup in
parallel with other operations.

Although the file extension is unaltered, the format of the
backup file has changed. It is fully backwardly compatible with the previous
format so it is possible to restore a database backed up in SQL
Server 2005 Analysis Services to SQL Server 2005 Analysis Services. It
is not, however, possible to save files in the old format.

Write simpler MDX without
worrying about performance

A new feature, block computation (also known as subspace
computation), has been implemented in SQL Server 2008 Analysis
Services and one of the main benefits it brings is improved MDX query
performance. In SQL Server 2005 complex workarounds were possible in some
cases; now the MDX can be written much more simply.

Cubes commonly contain sparse data: there are often relatively
few values in a vast sea of nulls. In SQL Server 2005 Analysis Services,
queries that touched a large number of nulls could perform poorly for the
simple reason that even if it was logically pointless to perform a calculation
on a null value, the query would nevertheless process all the null values in
the same way as non-nulls.

Block computation addresses this issue and greatly enhances the
performance of these queries. The internal structure of a cube is highly
complex and the description that follows of how block computation works is a
simplified version of the full story. It does, however, give an idea of how the
speed gain is achieved.

This MDX calculation calculates a running total for two
consecutive years:

CREATE MEMBER CURRENTCUBE.[Measures].RM

AS ([Order Date].[Hierarchy].PrevMember,[Measures].[Order Quantity])+

Measures.[Order Quantity],

FORMAT_STRING = "Standard",

VISIBLE = 2 ;

These tables show orders of a small subset of products and
illustrate that there are very few values for most products in 2003 and 2004:

	2003	2004
Product 1
Product 2	2
Product 3
Product 4		5
Product 5
Product 6
Product 7	3	1
Product 8
Product 9

This query:

SELECT [Order Date].[Hierarchy].[all].[2004] on columns,

[Dim Product].[Product Key].[All].children on rows

From Foo

Where Measures.RM

returns the calculated measure RM for all products ordered in
2004.

In SQL Server 2005 Analysis Services, the result is
generated by taking the value from a cell for 2004 (the current year), finding
the value in the corresponding cell for the previous year and summing the
values. Each cell is handled in this way.

This approach contains two processes that are slow to perform. Firstly,
for every cell, the query processor navigates to the previous period to see if
a value is present. In most hierarchical structures we know that if data is
present for one cell in 2003, it will be there for all cells in 2003. The trip
to see if there is data for the previous period only needs to be carried out
once, which is what happens in SQL Server 2008 Analysis Services. This
facet of block computation speeds up queries regardless of the proportion of
null values in the cube.

Secondly, the sum of each pair of values is calculated. With a
sparse cube, a high proportion of the calculations result in a null, and time
is wasted making these calculations. If we look at the previous tables, it is
easy to see that only the calculations for products 2, 4, and 7 will
deliver anything that is not null. So in SQL Server 2008 Analysis Services,
before any calculations are performed, null rows are removed from the data for
both years:

	2003
Product 2	2
Product 7	3

	2004
Product 4	5
Product 7	1

The results are compared:

	2003	2004
Product 2	2
Product 4		5
Product 7	3	1

and the calculations performed only for the rows that will
generate a meaningful result.

The speed gain can be impressive: under testing a query that took
two minutes and 16 seconds in SQL Server 2005 Analysis Services took
a mere eight seconds in SQL Server 2008 Analysis Services.

Scale out if you need more
hardware capacity and hardware price is important

To improve performance under load, you can either scale up or
scale out. Scale up is undeniably simple: put the cube on an extra large high-performance
server. This is an excellent solution—it is quick and easy and is the correct
decision in many cases. However, it is also expensive because these servers
cost more per CPU than multiple smaller servers.

Previous version of Analysis Services offered a scale-out
solution that used multiple cost-effective servers. The data was replicated
across the servers and a load balancing solution such as Microsoft Network Load
Balancing (NLB) was installed between the clients and the servers. This worked
but incurred the additional costs of set up and ongoing maintenance.

SQL Server 2008 Analysis Services has a new scale-out
solution called Scalable Shared Database (SSD). Its workings are very similar
to the SSD feature in the SQL Server 2005 relational database engine. It
comprises three components:

·

Read-only database – enables a database to be designated
‘read-only’

·
Database storage location – enables a database to reside outside
the server Data folder

·
Attach/detach database - a database can be attached or detached
from any UNC path

Used together, these components make it easier to build a
scale-out solution for read-only Analysis Services cubes. For example, you can
connect four blade servers to a shared, read-only database on a SAN, and direct
SSAS queries to any of the four, thereby improving your total throughput by a
factor of four with inexpensive hardware. The best possible query response time
remains constrained by the capabilities of an individual server, since only one
server runs each query.

Reporting

This section offers best practices for different aspects of
reporting such as improving performance.

Best Practices: Data Presentation

Allow IT and business users
to create both simple and complex reports

Reports have always fallen between two opinions—should IT people
or business people design them? The problem is that business users understand
the business context in which the data is used (the meaning of the data) while
IT people understand the underlying data structure.

To help business users, in SQL Server 2005, Microsoft
introduced the concept of a report model. This was built by IT people for the
business users who then wrote reports against it by using a tool called Report
Builder. Report Builder will ship in the SQL Server 2008 box and will work
as before.

An additional tool, Report Builder, aimed squarely at the power
user, has been enhanced in SQL Server 2008. This stand-alone product,
complete with an Office System 12 interface, boasts the full layout
capabilities of Report Designer and is available as a Web download.

IT professionals were well served in SQL Server 2005 by a
tool called Report Designer in Visual Studio, which allowed them to create very
detailed reports. An enhanced version of Report Designer ships with SQL Server 2008
and is shown in Figure 6.

Figure 6: Report Designer in Visual Studio

Present data in the most
accessible way possible

Traditionally, grid data has been presented to users as tables, matrices,
and lists. Each has its strengths, which is another way of saying that each has
its weaknesses. SQL Server 2008 Reporting Services combines all three into
a new data region called a Tablix.

In Figure 7, the table on the left shows percentage growth
and the matrix on the right shows the actual figures.

Figure 7: A table and a matrix

The Tablix shown in Figure 8 combines these and adds some totals.

Figure 8: A Tablix data region combining table and matrix
features

The Tablix data region gives users far more control over layout
than tables, lists, and matrices. It also enables them to add column groups,
specify arbitrary nesting on each axis, optionally omit rows and column headers,
and have multiple parallel row/column members at each level.

Present data in reports that
can be understood easily

The reason for generating reports is to transform data into
information for the business user. Many people find graphics easier to
understand than numbers. SQL Server 2008 Reporting Services introduces
the gauge, a new form of visual output. A gauge displays a single value
from the data. As Figure 9 shows, gauges are particularly useful for
creating easy-to-read displays that compare several values.

Figure 9: Examples of the gauges that can be created in SSRS

Charts have also been extended to include polar, range, and
shape. Figure 10 shows only a subset of those available.

Figure 10: A large number of charts are available in SQL
Server 2008 Reporting Services

Multiple data series can now be displayed on more than one axis,
the user has control over scale breaks on the axis, and there is support for
run-time formulae.

Present data to users in
familiar environments

Reports can now be rendered as Word documents compatible with
Word versions back to and including Word 2000. In addition, the existing
Excel renderer has been enhanced and now supports features such as nested data
regions, merged cells and subreports.

Best Practices: Performance

Structure your query to
return only the level of detail displayed in the report

Use report-level aggregation only for subtotals in the report,
not for detail rows. Remember that the most commonly used aggregate, Sum, can
be summed in any order and still yield the same result. While other commonly
used aggregates cannot, they can often be decomposed into simpler, reusable
components.

For example, if you are trying to show an average at several
grouping levels and also want subtotals, rather than returning detail rows and
aggregating everything in the report, you can decompose the calculation into
sums and counts. Then you can reconstitute the average in the report by using this
kind of division:

Sum(Fields!Sum.Value)/Sum(Fields!Count.Value)

thereby avoiding the need to pass detail rows to the report. Let
the database do the bulk summarization and aggregation, and use SSRS to
assemble and format results for display.

Filter by using parameters that
are passed to the query

As a general rule it is better to filter in the query rather than
in the report (SELECT * FROM xyz WHERE field = @param). However, if you do this
you cannot create a historical snapshot of the report without locking down the
parameter value. If users need to change the parameter value in the snapshot,
the best practice is to return all of the data to the report and filter inside
the report instead.

Sort within the query

SSRS utilizes sort-stable operations internally, so the order of
data returned from the query is not altered within each group instance, thereby
allowing sorting to be performed in the query.

However, sorting that is based on aggregate values is far more
convenient (and usually more efficient) to perform in the report itself.

Avoid using subreports
inside a grouping

Each subreport instance is a separate query execution and a
separate processing step for SSRS. For master-detail reports, it is far more
efficient to simply join the master and detail data in your query and then
group by the master key in the report, except in cases where the number of
master records is small (in which case subreport usage is not a performance
issue).

There are two cases where subreports may be required:

·

When the master and detail data are in different data sources.
Pulling the data into a single data warehouse is recommended in this case. If
that is not possible, SQL Server linked server or open rowset capabilities
should be considered.

·
When there are multiple independent sets of detail records for
each master record. An example might be displaying a detailed list of both
sales and returns for each customer. In this case, drillthrough reports are
recommended instead of inline subreports unless the number of master records is
small.

Limit the data in charts to
what a user can see

While it may be tempting to include a large amount of detail data
in charts to improve accuracy, it is better to pre-group the data in either the
query or in the report, limiting the number of data points. Drawing hundreds of
points in a space that only occupies a few pixels degrades performance and does
nothing to enhance the visual appeal of the chart.

Pre-sort and pre-group the
data in your query

You can normally improve performance by grouping and sorting the
data in the query to match the sort order required by the report.

Use drillthrough rather
than drilldown when detail data volumes are large

While the SQL Server 2008 on-demand processing engine
optimizes away most calculations for items that are not displayed, keep in mind
that all of the data is retrieved for every detail row, even if your initial
drilldown state has everything collapsed up to the highest level of
aggregation. In addition, all grouping, sorting, and filtering must be
performed regardless of visibility or drilldown state. So if the user typically
is only interested in seeing a small percentage of the detail data, an
associated drillthrough report is a better choice.

Avoid complex expressions
in the page header and footer

If the page header or footer contains complex expressions
(anything other than a simple field or parameter reference), SSRS must assume
it may include a reference to the total number of pages. As a result, the
entire report must be paginated before the first page can be rendered. Otherwise,
the first page can be rendered and returned to the user immediately who will
not have to wait for the entire report to be paginated first.

Turn off CanGrow on
textboxes and AutoSize on images if possible

Some renderers are more efficient if the sizes of all objects in
the report are known to be fixed.

Do not return columns that
you are not going to use from your query

Since all of the data in the query must be retrieved (and stored)
by the report server, it is more efficient to return only the columns that will
be used in the report. If the result set cannot be controlled (such as when the
results are returned from a stored procedure), it is sufficient to remove the
field definitions from the dataset. While the extra columns will be retrieved,
this will at least prevent them from being stored.

Best Practices: System
Architecture and Performance

Keep the report server
catalog on the same computer as the report server

Reporting Services generates and uses a database that is
essentially a catalog. This is used during report processing for a number of
tasks, including managing the data returned from queries. While it is possible
to store this database on a server other than the reporting server, doing so
requires that the report data be pushed across the network unnecessarily, which
slows down report execution. It is a much better practice to hold the catalog
database on the reporting server to avoid this network traffic and the
associated delays.

Consider placing Reporting Services
on a different server from your data warehouse

While pulling the data from the queries across the network does
slow things down, it is beneficial to not have your data warehouse and SSRS
competing for memory and processing time.

Conclusion

Designing and building an enterprise data
warehouse can be a major effort with a significant cost. Data warehouses are
not built as displays of technical excellence; they are investments made by the
enterprise and intended to yield a high return. As both the IDC and Gartner
papers show, all too often these projects have more of the characteristics of a
gamble than a shrewd investment—a potentially high return but a concomitantly
high risk of failure and loss.

A great deal has been learned about factors that
are associated with successful projects. These have been distilled into a
number of best practices, many of which are described here, encompassing
business processes, design, and technical implementation using SQL Server 2008.
Indeed, many of the new features in SQL Server 2008 were specifically
designed to allow these best practices to be implemented easily.

As a proportion of the total effort required to
create a data warehouse, applying these best practices is inexpensive, and yet
doing so can have a major impact on the success (and therefore the ROI) of the
project as a whole.

For more information:

·

SQL Server
Web site

·
SQL Server
TechCenter

·
SQL
Server Developer Center

All About Database

Bisnis Pulsa Elektrik - Harga Murah Bersahabat

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns By Jeff Moden, 2008/08/19

Introduction:

Before you say anything...

Notes of Interest:

A simple introduction to Cross Tabs:

The Test Data

The KEY to Cross Tabs!

A simple introduction to Pivots:

Readability Comparison

Multiple Aggregations In a Cross Tab (or, "The Problem with Pivots")

The Test Data

The Multi-Aggregate Pivot

The Multi-Aggregate Cross Tab

"Pre-Aggregation"

Performance

Review

In the Works...

Tuning SQL Server performance via disk arrays and disk partitioning

Tuning SQL Server performance via memory and CPU processing

Export data from SQL Server to Excel

Getting IO and time statistics for SQL Server queries

Gathering I/O statistics down to the SQL Server database file level

How To Collect Performance Data With TYPEPERF.EXE

Top Tips for Effective Database Maintenance

Using Performance Monitor

SQL Server Tuning

Finding the Bottleneck

Log Shipping or Mirroring?

Install Firebird 2.0.3 Database Server On Ubuntu 7.10

SQL Server 2000 - Merge Replication

Introduction

Types of Subscription:

Replication Types

Snapshot Replication

Transactional Replication

Merge replication

Necessary steps to be taken before doing replication process:

Step by Step Procedure for Merge Replication setup

Important Note:

Advantages in Replication:

Replication Performance Tuning Tips:

Conclusion

Accessing SQL Server Databases with PHP

Best Practices for Data Warehousing with SQL Server 2008

Relational Data Warehouse Setup, Query,and Management

Use PAGE compression to reduce data volume andspeed up queries

Relational Data Warehouse Setup, Query,
and Management

Use PAGE compression to reduce data volume and
speed up queries