September 15, 2008 at 9:25 am
Hi
I had something to do today and I decided to optimize a query and I tested "the use" of indexes.
I have 4 tables with aprox 50 000 records each.If what I know is correct I should use clustered indexes on PK and non-clustered on the columns in the joins,or for the selected fields.I did this and It wasn't even 1 sec difference between these cases.I removed completly the indexes and the time in sec was the same as when I had them...
I'm doing something wrong?Is there another way to ..."distribute" the indexes..?
Thank you in advance.
p.s. : I think there isn't necessary an example and you believe me:D.
September 15, 2008 at 9:44 am
Depends highly on the queries. It could be that your indexes aren't covering and that with the number of rows been returned, SQL decided that a scan of the cluster was faster than the seek and all the bookmark lookups that would have been necessary.
Did you have an index that had all of the columns used in the where clause as index keys and all the columns in the select as include columns? With nonclustered indexes, SQL will often find wider indexes more useful that single-column indexes.
The default for the clustered index is the PK. It doesn't have to be that way and sometimes there's a better place for the cluster.
If you'd like to post the query, the table defs and index defs, I can give you a more concrete and useful answer.
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 15, 2008 at 10:33 am
Have you looked at the execution plans to verify that the indexes were used at all?
For the clustered index, be sure that the column (or columns) that you put it on will benefit from being either grouped (like dates) or ordered (lists, versions, that sort of thing) since that's what the clustered index will do. Make sure you do keep one clustered index on the table because, in addition to it's utility as an index, it changes how SQL Server stores the table. It's usually, but not always, more effecient with the clustered index.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 16, 2008 at 12:55 am
in the execution plan there are only index scans
tables :
testa1
(
a1 uniqueidentifier not null, - pk
a2 int,
a3 varchar(10),
a4 char(2),
a5 varchar(20),
)
testb1
(
b1 uniqueidentifier not null, - pk
b2 int,
b3 varchar(10),
b4 char(2),
b5 varchar(20),
a1 uniqueidentifier
)
testb2
(
b1 uniqueidentifier not null, - pk
b2 bigint,
b3 varchar(15),
b4 char(2),
b5 varchar(20),
a1 uniqueidentifier
)
indexes:
the clustered idexes are on the 3 pk-default
create nonclustered index idx_b11
on testb1(a1)
create nonclustered index idx_b12
on testb1(b2)
create nonclustered index idx_b21
on testb2(a1)
create nonclustered index idx_b22
on testb2(b2)
SELECT distinct testa1.a1, testb2.b2, testb1.b2
FROM testa1
INNER JOIN testb1 ON testa1.a1 = testb1.a1
INNER JOIN testb2 ON testa1.a1 = testb2.a1
where testb2.b2/testb1.b2<10
the query is mainly for testing
I'm sure something's wrong...I think I don't know to "pick" the indexes...
So...I'm waiting for the advices.Thank you
September 16, 2008 at 2:08 am
Your query, as written, cannot use index seeks. The computation in the where clause means that the only index usage possible is a scan. Also, all or your indexes are single column. With NC indexes, the wider, the better quite often.
If you change the query as follows:
SELECT distinct testa1.a1, testb2.b2, testb1.b2
FROM testa1
INNER JOIN testb1 ON testa1.a1 = testb1.a1
INNER JOIN testb2 ON testa1.a1 = testb2.a1
where testb2.b2<10*testb1.b2
and then widen the indexes as follows
create nonclustered index idx_b22
on testb2(b2, a1)
create nonclustered index idx_b11
on testb1(a1,b2)
you may see index seeks. It's a first guess, and I may be wrong.
Uniqueidentifiers are a poor choice for the clustered index if you're populating them with random guids. Because of the randomness, inserts happen all over the index resulting in page splits and high fragmentation
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 17, 2008 at 2:35 am
So... using clustered indexes on guids is not such a good idea.
Roger that:)
I'm unsing mainly guids ...so I will have to drop the clustered indexes on PK and try to use them more wisely:)
And ...if the where clause is not a SAR index seeks don't have a chance...I didn't know that either
Thank you for your answer
September 17, 2008 at 5:36 am
Although, with the ordered guid introduced in 2005, you can get a lot less rearrangement if you put the guid into a clustered index. It's still a bit wide. There's no getting around that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply