SQL Server index advice performance

Question

Hi Guys,

I'm looking for some advice to how to get the indexes running better on this query... SQL Server 2005/8 some customers have 5 some 8...

SELECT    sales.ChainStoreId, 
          sales.CashBoxId, 
          dbo.DateOnly2(sales.BonDate), 
          MAX(sales.BonDate), 
          SUM(sales.SumPrice) 
FROM      [BACK_CDM_CLEAN_BOLTEN].[dbo].[CashBoxSales] sales
WHERE     sales.BonType in ('B','P','W')             
AND       Del = 0 
AND       sales.BonDate >= @minDate
GROUP BY  sales.ChainStoreId, 
          sales.CashBoxId, 
          dbo.DateOnly2(sales.BonDate)

Table looks like the following

CREATE TABLE [dbo].[CashBoxSales](
    [SalesRowId] [int] IDENTITY(1,1) NOT NULL,
    [ChainStoreId] [int] NOT NULL,
    [CashBoxId] [int] NOT NULL,
    [BonType] [char](1) NOT NULL,
    [BonDate] [datetime] NOT NULL,
    [BonNr] [nvarchar](20) NULL,
    [SumPrice] [money] NOT NULL,
    [Discount] [money] NOT NULL,
    [EmployeeId] [int] NULL,
    [DayOfValidity] [datetime] NOT NULL,
    [ProcStatus] [int] NOT NULL,
    [Del] [int] NOT NULL,
    [InsertedDate] [datetime] NOT NULL,
    [LastUpdate] [datetime] NOT NULL,

What would be the correct ordering of the index columns, covered or composite etc. The table has up to 10 mil rows. There are other similar selects but I'm hoping from the advice getting this one up to speed (Its the most important) I can tweak a few others.

Many thanks!

Answer 1

My best advice is to run this query through the SQL Profiler. It will recommend some indexes for you to try.

Also, you might try setting up a partitioned table and use one of your GROUP BY columns as the partitioning key.

Answer 2

Off the top of my head I would start with

INDEX (BonType, Del, BonDate)

Or even just

INDEX (BonType, BonDate)

I would recomend using an Index Analyzer, Proflier and Benchmarking various combinations.

Answer 3

When you have your query in SQL Server Management Studio, just select "Analyze Query in Database Tuning Advisor" from the context menu, and off you go!

Mind you: this only tweaks this one single query in isolation! Adding indices here to speed this one query up might adversely affect other parts of your application. An index always comes with overhead - inserts and deletes tend to be slower.

Also, don't blindly implement all the recommendations of the DTA - use your own judgment as to whether an index makes sense or not.

And lastly: measure, measure, measure! Measure your performance before any changes as a baseline, then measure again and again after you've made the changes and compare.