September 4, 2011 at 10:27 pm
Hi,
As I'm new to SQL Server with Oracle background, I've been trying to understand how to optimize a SELECT statment with indexes.
Basically my question is:
in order to make SQL Server use an index for a SELECT statment, should the CREATE INDEX command have the
column list used in the SELECT statement included in its INCLUDE keyword?
Following the what I tested:
-- the select to optimize is:
SELECT ORDER_TOTAL
FROM SA.ORDERS
WHERE ORDER_DATE BETWEEN GETDATE()-60 AND GETDATE()
-- first, the following index created:
CREATE NONCLUSTERED INDEX [IX_ORDER_DATE]
ON [SA].[ORDERS] ([ORDER_DATE])
-- the execution plan showed the index wasn't used
-- secondly: the index re-created using the following code:
CREATE NONCLUSTERED INDEX [IX_ORDER_DATE]
ON [SA].[ORDERS] ([ORDER_DATE])
INCLUDE ([ORDER_ID],[ORDER_MODE],[CUSTOMER_ID],[ORDER_STATUS],[ORDER_TOTAL],[SALES_REP_ID],[PROMOTION_ID])
-- the execution plan showed the index was used
Test on SQL Server 2008 on Windows 2003
September 5, 2011 at 12:00 am
Few Questions...
1. How many records are there in the table?
2. Why cant you have only order_total alone in the included and see the usage of index rather than giving all the columns?
3. Attach the execution plan for analysis.
September 5, 2011 at 12:11 am
>> 1. How many records are there in the table?
one million
>> 2. Why cant you have only order_total alone .. ?
either way, I got the same result... index is being used only when INCLUDED keyword is used
>>3. Attach the execution plan for analysis.
no way to add attachment here... if u wish, I can email it to u.
September 5, 2011 at 12:38 am
Depends on the selectivity of the predicate - what percentage of the rows the query returns.
http://qa.sqlservercentral.com/articles/Indexing/68636/
http://sqlinthewild.co.za/index.php/2009/01/09/seek-or-scan/
p.s. To attach, see the box right under the Post Reply text field. There's an 'Edit Attachments' button. Click that and you get a window that allows uploading of files.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 5, 2011 at 1:11 am
Hi Gila,
Thanks for the wonderful title. It was quite informative.
One related question: is there a way to force the optimizer to use an index in a select statment?
September 5, 2011 at 2:13 am
You can force the index using option. However its not advicable to do so until you are very sure of the same. (I always believe SQL will do better job for us)
September 5, 2011 at 2:26 am
ahmed_b72 (9/5/2011)
One related question: is there a way to force the optimizer to use an index in a select statment?
Yes, there's a WITH (INDEX = <index name>) hint. However, unless you are 100% absolutely, completely, totally sure you know better than the optimiser, don't use them. I do SQL performance tuning for a variety of clients, I've used a with index hint once in 6 years.
If you don't know what you're doing, or why SQL didn't chose the index itself, you're likely to make performance way worse, not better.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 5, 2011 at 5:19 pm
Thanks for the explanation.
Have a good day.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply