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.

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.

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  

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'

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 

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.