SQLCAT Blogs

How to Enable RCSI for a Database with Database Mirroring

Microsoft SQL Server Development Customer Advisory Team — Tue, 16 Mar 2010 21:36:00 GMT

Author: Sanjay Mishra Reviewers: Prem Mehra, Alexei Khalyako, Kun Cheng, Mike Ruthruff, Thomas Kejser, Sunil Agarwal To enable read committed snapshot isolation (RCSI) on a database, one needs to execute the following command: ALTER DATABASE <db_name>...(read more)

Enabling Partition Level Locking in SQL Server 2008

Microsoft SQL Server Development Customer Advisory Team — Wed, 03 Mar 2010 23:54:00 GMT

Author: Thomas Kejser Reviewers: Juergen Thomas, Sanjay Mishra, Stuart Ozer, Lubor Kollar, Kevin Cox, Kun Cheng In this blog, we will provide additional details on a well-hidden feature in SQL Server 2008 – partition-level locking. But before we illustrate...(read more)

SQL Server 2008 R2 UNICODE Compression – what happens in the background?

Microsoft SQL Server Development Customer Advisory Team — Wed, 03 Mar 2010 00:06:00 GMT

SQL Server 2008 R2 added a much requested feature: Unicode compression. It addresses the need to compress Unicode strings. It is implemented as part of ROW compression, which was added in SQL 2008. That is; if ROW compression (on SQL 2008 R2) is enabled...(read more)

Performance tips of using XML data in SQL Server

Microsoft SQL Server Development Customer Advisory Team — Mon, 01 Mar 2010 23:52:00 GMT

Author: Kun Cheng Reviewers: Peter Carlin, Mike Ruthruff, Thomas Kejser, Nicholas Dritsas XML data type is usually used to store semi-structured data with great flexibility and query capabilities. It’s a good choice for developing platform agnostic applications...(read more)

Full Text Indexing Terabytes of Files with SQL Server and Cloud Storage

Microsoft SQL Server Development Customer Advisory Team — Wed, 03 Feb 2010 02:52:00 GMT

Author: Darko Sancanin, Nicholas Dritsas Reviewers and contributors: Lubor Kollar, Stuart Ozer, Michael Thomassy Business Case There are currently over 50 million files (over 10 Terabytes of data) that the customer is migrating into a custom project management...(read more)

Whitepaper About Star Join Optimizations

Microsoft SQL Server Development Customer Advisory Team — Wed, 16 Dec 2009 20:20:03 GMT

When we talk to Data Warehouse customers – they often raise questions about the star joins improvements SQL Server 2008. Star join is a set of optimizations that provide some interesting performance gains in data warehouse workload. Instead of digging...(read more)

Monitoring free space in tempdb transaction log

Microsoft SQL Server Development Customer Advisory Team — Mon, 14 Dec 2009 13:58:36 GMT

Author: Thomas Kejser Reviewers: Peter Byrne, Sunil Agarwal, Prem Mehra, Peter Scharlock, Lindsey Allen, Mark Souza As part of your database monitoring, you may be keeping track of the free space in the transaction log. One reason to do this, is to detect...(read more)

Performance Comparison between Data Type Conversion Techniques in SSIS 2008

Microsoft SQL Server Development Customer Advisory Team — Tue, 01 Dec 2009 13:59:00 GMT

Authors : Sedat Yogurtcuoglu and Thomas Kejser Technical Reviewers : Kevin Cox, Denny Lee, Carl Rabeler, Dana Kaufman, Jesse Fountain, Alexei Khalyako, Dana Kaufman Overview Data type conversion is one of the most common ETL tasks used in Microsoft® SQL...(read more)

PowerPivot for SharePoint Installation Guides DRAFT

Microsoft SQL Server Development Customer Advisory Team — Fri, 20 Nov 2009 15:18:00 GMT

The installation of PowerPivot for SharePoint can be a little complex at times - especially the PowerPivot for SharePoint multi-server farm scenario. In order to help with this, the Analysis Services team and the SQLCAT team came together and created...(read more)

PowerPivot Jump Start!

Microsoft SQL Server Development Customer Advisory Team — Tue, 10 Nov 2009 23:41:00 GMT

As you may already know, the new name for Project Gemini is PowerPivot and the official names are PowerPivot for Excel and PowerPivot for SharePoint. For information and to sign up for notification on the release dates, check out the official PowerPivot...(read more)

Using Filtered Statistics with Partitioned Tables.

AlexeiK — Tue, 20 Oct 2009 20:26:00 GMT

Author: Alexei Khalyako

Reviewers and contributors: Eric Hanson, Dimitri Artemov, Burzin Patel, Thomas Kejser

Introduction

We’ve worked with a customer on architectural design review and preparing a migration of a big Data Warehouse from SQL Server 2005 to SQL Server 2008. Base on the customer’s requests, there were various advantages of the migration to SQL Server 2008 identified. One of the requests was related to control of the query resource consumption.

The main role of the Data Warehouse in the customer’s scenario is to collect Call Data records, which have to be analyzed later and used by several other business applications. So, in general once per day new data come into a several Terabyte partitioned table and then business apps and business users were running report-type of queries. Some of the relatively small reports were taking very long times. As DBA started analyzing those queries they recognized that the difference between REQUIRED and REQUESTED memory by the query was huge (like amount of required memory was 18Mb and requested – 10 GB)!

As the part of optimization we were able to lower the appetite of those queries by using Resource Governor. We even managed to get more of the heavy queries executed at the same time. However this didn’t address the other question – why queries were requesting way more memory?

Analyzing query plans we found out that the Statistics were disabled on this table. The reason for this was that customer had to load every day very big amount of data into the new partition. Statics was becoming not valid and should have been updated. Update process was starting on the entire table (which is several Tb) and by the tome update was finished – the new portion of the data was arriving. And so on, and so on... Customer decided to disable and delete statistics on the entire table.
Another important moment to note: customer stated that most of the report queries run against only one partition in partitioned table.

Base on the AdwentureWorks database and the table SalesOrderHeader I created a little sample to illustrate this situation. I made a partitioned table SalesOrderHistory partitioned by OrderDate by date and covering one month 2008-07.
The table structure looks like:

CREATE TABLE [SalesOrderHistory](
              [SalesOrderID] [int] NOT NULL,
              [RevisionNumber] [tinyint] NOT NULL,
              [OrderDate] [datetime] NOT NULL,
             [DueDate] [datetime] NOT NULL,
              [ShipDate] [datetime] NULL,
              [Status] [tinyint] NOT NULL,
              [SalesOrderNumber] [nvarchar](25) NOT NULL,
              [CustomerID] [int] NOT NULL,
              [ShipToAddressID] [int] NULL,
              [BillToAddressID] [int] NULL,
              [CreditCardApprovalCode] [varchar](15) NULL,
              [SubTotal] [money] NOT NULL,
              [TaxAmt] [money] NOT NULL,
              [Freight] [money] NOT NULL,
              [TotalDue] [money] NOT NULL,
             [Comment] [nvarchar](max) NULL,
              [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
              [ModifiedDate] [datetime] NOT NULL)
              ON day_scheme ([OrderDate])

The query will look like a:

Select b.Name, a.SalesorderId, a.OrderDate from SalesOrderHistory a
Inner Join Store b on a.CustomerID = b.CustomerID
WHERE a.OrderDate = '2008-07-13 00:00:00.000'and a.SalesOrderID = 43659
Group by b.Name, a.SalesorderId, a.OrderDate

Query executed in 5 sec.

Let’s see the plan with no statistics on the table:

So, we see the warning that statistics is missing, if we check the estimated number of rows in the output of SELECT is 393 rows

If we run DMV to check memory requested and granted to this query we will see following:

select session_id,requested_memory_kb,required_memory_kb,granted_memory_kb,query_cost

from sys.dm_exec_query_memory_grantswhere session_id >50

session_id	requested_memory_kb	required_memory_kb	granted_memory_kb	query_cost
52	1360	1280	1360	5,737777

Now we will try to fix it. In SQL 2008 we’ve got new Filtered Statistics. In order to learn more about this feature please read http://msdn.microsoft.com/en-us/library/ms190397.aspx Having in mind that customer told us that 80% of the queries are hitting only one partition we may apply filtered statistics to improve those queries performance. I can try to create different statistics per each column which is included in SELECT statement:

CREATE STATISTICS Day0713_SalesOrderID on SalesOrderHistory (SalesOrderID )where OrderDate = '2008-07-13 00:00:00.000'

CREATE STATISTICS Day0713_OrderDate on SalesOrderHistory (OrderDate) where OrderDate = '2008-07-13 00:00:00.000'

CREATE STATISTICS Day0713_CustomerID on SalesOrderHistory (CustomerID ) where OrderDate = '2008-07-13 00:00:00.000'

After creating those statistics and executing the same query as mentioned above we see that the query execution time was few milliseconds and the plan looked much better:

Let’s check how much memory this query requested now and how much was granted:

select session_id,requested_memory_kb,required_memory_kb,granted_memory_kb,query_cost

from sys.dm_exec_query_memory_grantswhere session_id >50

session_id	requested_memory_kb	required_memory_kb	granted_memory_kb	query_cost
52	1024	512	1024	5,678132

We see that comparable to running the same query with no statistics now with filtered statistics on the table enabled has the query much lower expectations on how much memory it may need.

One of the great advantages of the filtered statistics is that if we switch in new partition into the partitioned table filtered statistics built on the older partitions does not become invalid and you don’t need to update it. You only need to create new filtered statistics for the new partition which is much faster process comparable to if you need to update or build statistics for entire table. In case of the real data in the customer’s lab we were able to get even better results using filtered statistics on much bigger amount of data. If you have similar workload, where most of the queries are going against single partition in partitioned table you may want to play with the filtered indexes and check if it will help you increasing performance of your queries.

Are you going to SQL PASS Nov. 3rd- Nov. 5th?

Microsoft SQL Server Development Customer Advisory Team — Mon, 19 Oct 2009 13:47:00 GMT

Are you going to SQLPASS in Seattle on Nov 3 rd -Nov 5 th ? If you are going or even thinking about it, then read about how you can spend some quality time with the SQLCAT team. We will be there with our famous ugly lime green shirts, so you won’t miss...(read more)

Looking deeper into SQL Server using Minidumps

Microsoft SQL Server Development Customer Advisory Team — Fri, 11 Sep 2009 15:43:24 GMT

Author: Thomas Kejser Reviewers and Contributors: Bob Ward, Michael Thomassy, Juergen Thomas, Hermann Daeubler, Mark Souza, Lubor Kollar, Henk van der Valk (Unisys) and Peter Scharlock For advanced troubleshooting and understanding of SQL Server, you...(read more)

Using HierarchyID in SQL Server

Microsoft SQL Server Development Customer Advisory Team — Thu, 27 Aug 2009 14:48:00 GMT

Implementing a hierarchy structure in a relational data base normally takes a bit of work. The new SQL Server data type for hierarchyID gives a good shortcut to the old methods, makes it faster to get a solution in place and makes it much easier to maintain...(read more)

Gemini - Self Service BI!

Microsoft SQL Server Development Customer Advisory Team — Sun, 16 Aug 2009 00:25:00 GMT

Gemini is the code name for the new breakthrough Self-Service Business Intelligence (BI) capabilities being delivered in the SQL Server 2008 R2 release. Gemini enables end users to build BI applications by integrating data from a variety of sources, modeling...(read more)