August 29, 2011 at 4:49 am
Hi there,
I have a select stateent which goes like this:
SELECT TOP 1 *
FROM table1
WITH (READUNCOMMITED)
WHERE Colx = @somevalue
order by Colx, Coly
The table holds millions of rows. There is an index on that table on both Colx, Coly. However, the WHERE clause only gives a value on Colx.
I get a Clustered Index Scan in my ShowPlan. It's considered a slow query, the average WAITTIME is 130.000 ms and this query is executed1000s of time daily.
I wonder if things would improve if I would create an extra index just on Colx. There are not many updates on that table, and sofar 10 indices already exist. I have no proper test environment to just create it and try it.
Any help is appreciated
Greetz,
Hans Brouwer
August 29, 2011 at 5:07 am
If ColX is the leading column of that index, won't help at all. If your index is on (colX, Coly) (in that order) that query should not scan, it's perfectly suitable for a seek. Execution plan and index definitions?
p.s. Why nolock? http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
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
August 29, 2011 at 6:17 am
OK, here comes:
/****** Object: Index [$13] Script Date: 08/29/2011 14:05:06 ******/
CREATE UNIQUE NONCLUSTERED INDEX [$13] ON [dbo].[Table1]
(
[Colx] ASC,
[Coly] ASC
)WITH
(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99)
ON [Data Filegroup 1]
BTW, this is a table/query generated by Navision.
Greetz,
Hans Brouwer
August 29, 2011 at 7:14 am
Please give me the actual index definition. I can't match a made-up one to the execution plan (that has the real table and column names in it).
In fact, please post the definitions of all the indexes on [ABX_NAV_PRD].[dbo].[ABX$Item]
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
August 30, 2011 at 12:57 am
OK, the real thing:
/****** Object: Index [$13] Script Date: 08/30/2011 08:54:40 ******/
CREATE UNIQUE NONCLUSTERED INDEX [$13] ON [dbo].[ABX$Item]
(
[EAN-Code] ASC,
[No_] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [Data Filegroup 1]
Greetz,
Hans Brouwer
August 30, 2011 at 3:39 am
What's the clustered index definition?
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
August 30, 2011 at 7:44 am
Perhaps you have data skew, where say 70% of your Colx values are a single thing and the others are spread around? Or there are just 4 evenly-distributed values of Colx? Those would make it such that the optimizer would never use the index seek/bookmark lookup type of plan since a scan will be much more efficient. You can see this for yourself by forcing the index usage in your statement. use SET STATISTICS IO ON to see total reads for each plan.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 31, 2011 at 12:32 pm
Hans,
Are you sure the index hasn't been disabled? Since the query is a TOP 1, it really should have used the index for a seek, even if there aren't any rows that have an empty string as EAN-CODE.
Todd Fifield
September 1, 2011 at 12:03 am
agree with Todd
September 3, 2011 at 2:27 am
Colx has about 11000 unique values on several million rows.
On being disabled, I don't know, I'll have to check that. Never thought of that.
Greetz,
Hans Brouwer
September 3, 2011 at 3:49 am
I can't see any reason why that index wouldn't be used there. Doesn't make sense...
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply