Archive for September, 2012

SQL Server Database Performance Tuning: Why is my DW Query so SLLLOW?!

September 23, 2012

I get asked this question a lot and in most instances the answer is… the query is returning a lot of data.

Most SQL Server Data Warehouse and/or Analytical queries return a lot of data because they aggregate transactions (facts) from a low level of granularity (day) to a higher level of granularity (month). The higher target level of granularity, the greater the number of  transactions that need be aggregated.

On other words, in order to find out what my total sales for this year is, I have to aggregate all of the sales transactions for this year. This could be mean that millions, billions, or even TRILLIONS of  sales transactions need to be aggregated to produce the Sales Grand Total for the year.

From a hardware perspective all of your transactions are sitting in your storage/disks (DAS/NAS). At SQL query execution time all of that data needs to be moved from storage to the processors to be aggregated.

The “pipe” that connects storage to the CPUs needs to be wide enough to flood the processors with data so that CPUs/users are not sitting idle waiting for data! This “pipe” width or throughput  is measured in MB/sec.

There are standard to tools measure your SQL Server Data Warehouse Server’s throughput in MB/sec. As explained in a previous post.

Once you know the SQL Server Data Warehouse server throughput in MB/sec you then need to measure the amount of data returned by your SQL query. By comparing the throughput (MB/sec) to the amount of data returned, it should then not be so surprising why your SQL queries are taking so long!

Example:

  • DW MB/sec = 1 MB/sec
  • SQL Query data = 100 MB

Minimum SQL query response time = 100 seconds

If you need reduce the SQL query response by 1/2 to 50 seconds then you need double the SQL Server Data Warehouse’s throughput to 2 MB/sec (or reduce the amount of data returned think: SQL Server 2012  ColumnStore Indexes/Page Compression Smile )

That is great, but how do I measure the amount of data that is returned by a SQL query? Glad you asked !

Create/Populate test table: – (use AdventureWorks)

select top 10000000 a.*

into test10m

from [dbo].[FactInternetSales] a
,[dbo].[FactInternetSales] b

Very Simple Query Example:

DBCC dropcleanbuffers;
GO
SET STATISTICS IO ON;
GO
SET STATISTICS TIME ON;
GO
SELECT [SalesTerritoryKey], sum([SalesAmount])
FROM [dbo].[NoCompression_10m]
group by [SalesTerritoryKey]
Option (MAXDOP 8);
GO
SET STATISTICS IO OFF;
GO
SET STATISTICS TIME OFF;
GO

Message Results:

Table ‘test10m’. Scan count 9, logical reads 204082, physical reads 0, read-ahead reads 204082, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 8016 ms,  elapsed time = 14522 ms.

Follow this simple formula:

Logical Reads  *  8 (single DB page) / 1024 (MB)

204,082 * 8 / 1024 = 1,594 MB

Amazingly if you check the size of the table in SQL Server Management Studios  it reads: 1,594 MB as well !! In other words this SQL query returns all the data from the database table.

image

Summary:

SQL Data Warehouse Database Servers are built to move large amounts of data from storage to the CPUs and then aggregate the data. If you know the throughput of the pipe that connects storage to the processors (MB/sec) and the amount of data returned by the target SQL query, then you can start the process of planning how to tune a scalable and predictable SQL Server Data Warehouse database. Now you can accurately set expectations upfront with the users.

Note: there many other factors that will effect SQL Server database  performance, this is just one of the most fundamental. No amount of SQL query tuning or index creation will ever make the SQL query preform faster than the SQL Server Data Warehouse server’s throughput MB/sec.

Also see: Performance Tuning a SQL Server Data Warehouse

Advertisements

SQL Saturday # 160

September 16, 2012

I will be giving the following presentation on Step 22 in Kalamazoo:

Scaling SQL Server to HUNDREDS of Terabytes!

Provide an introduction to the Massive Parallel Processing (MPP) Architecture. Describe how Microsoft’s MPP Appliance, Parallel Data Warehouse (PDW), allows SQL Server to scale to hundreds of terabytes. The presentation will also contain real world performance metrics and lessons learned directly from someone who actually developed a Parallel Data Warehouse Solution.

Session Level: Intermediate

Garrett Edmondson

Garrett Edmondson, MCITP has 10+ years experience working with the full Microsoft BI Stack. During the past year, he has been working on Microsoft’s Parallel Data Warehouse (PDW) Appliance, which allows SQL Server to scale to hundreds of terabytes.

Welcome to SQLSaturday #160

SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. This event will be held Sep 22 2012 at Kalamazoo Valley Community College, 6767 West O Avenue, Kalamazoo Township, MI 49009. Admittance to this event is free, but we do charge a lunch fee of 10.00 so that we can provide a lunch – not pizza! Please register soon as seating is limited, and let friends and colleages know about the event.

Update: The slides from my presetantion can be found on SQL Saturday 160 website (linked above) or on my sky drive : https://skydrive.live.com/redir?resid=906EB2F189E165B7!223

SQL Server Database Performance Tuning: SQL Server Columnstore Index Throughput

September 2, 2012

SQL Server 2012 ColumnStore Indexes are the best thing to be added to SQL Server for Data Warehousing since … well ever. One of the greatest benefits  of ColumnStore Indexes is an incredible increase in performance. According to SQL Server Columnstore Index FAQ ColumnStore Indexes now rebranded as xVelocity can increase SQL query performance by a factor of 10x to 100x.

From my own experience these performance gains  are not unrealistic but the exact performance gains are dependent on the data cardinality (number of distinct values) and data types in the columns. The lower the data cardinality (few distinct values) the greater the performance gains.

Here is the claim from SQL Server Columnstore Index FAQ:

“When data is stored in column-wise fashion, the data can often be compressed more effectively than when stored in row-wise fashion. Typically there is more redundancy within a column than within a row, which usually means the data can be compressed to a greater degree. When data is more compressed, less IO is required to fetch the data into memory. In addition, a larger fraction of the data can reside in a given size of memory. Reducing IO can significantly speed up query response time. Retaining more of your working set of data in memory will speed up response time for subsequent queries that access the same data.”

Let’s test it out.

Scenario

Create a fact table

select top 100000000 a.*

into [100millionFact]

from [AdventureWorksDWDenali].[dbo].[FactInternetSales] as a,
[AdventureWorksDWDenali].[dbo].[FactInternetSales] as b ;
Go

I created a 100 million row fact table based on the 2012 AdventureWorks FactInternetSales table. It might not be very real world but it is still useful for demonstrating the capabilities of Columnstore Indexes.

Create a Columnstore Index on all columns

CREATE NONCLUSTERED COLUMNSTORE INDEX [NonClusteredColumnStoreIndex-20120723-215255] ON [dbo].[100millionFact]
(
[ProductKey],

[ShipDate]
)WITH (DROP_EXISTING = OFF) ON [PRIMARY]
GO

Execute Simple Aggregate Query

DBCC dropcleanbuffers;
GO
SET STATISTICS IO ON;
GO
SET STATISTICS TIME ON;
GO

SELECT [SalesTerritoryKey], sum([SalesAmount])
FROM [Test].[dbo].[100millionFact]
group by [SalesTerritoryKey]
OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX)
GO
SET STATISTICS IO OFF;
GO
SET STATISTICS TIME OFF;
GO

Notice that I used dropcleanbuffers to remove anything from cache and I enabled to IO and TIME stats so that we can see the important query stats.

Query without Columnstore

image

2,040,817 logical reads * 8K per page / 1024 = 15,943 MB of data scanned into memory and then aggregate by the CPU in > 2 minute.

Query with SQL Server 2012 Columnstore Indexes

Now run the the same SQL query without the IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX hint.

image

1020 logical reads * 8k per page / 1024 = 7.9 MB of data scanned into memory and then aggregated in < 1 second.

Summary

In the simplistic example above, the throughput requirements for the aggregate SQL query dropped from about 16 GB to just 8 MB and CPU processing time from over 2 minutes to under 1 second by implementing a SQL Server 2012 Columnstore Index. Columnstore Indexes represent a tremendous performance increase for SQL Server data warehousing databases  because the most expensive, complex, and important part of  a SQL Server data warehouse server is getting the maximum throughput from storage to the CPU.

Code