SQL Server index advice performance
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!
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.
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.
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.
精彩评论