index creation
-
I created a non-clustered index for a column in SQl Server management Studio. However, when I ran showplan_all it still showed that the table is doing a full scan. Also, where can I go to see how much disk space this index took up? Any help would be greatly appreciated. Thanks.
-
The problem may not be the table, but the query you are running. If you could post the DDL for the table, some sample data, and your query; we could probably help you out.
-
Select the db, goto Report, disk usage. On that report Disk space used by tables... it will show space used by indexes in there.
-
Try this... I am not sure it is accurate or not..
exec sp_MSindexspace <Table Name>
MohammedU
Microsoft SQL Server MVP -
Possible reasons for using a table scan instead of an index:
- The query plan is cached and it is reusing the cached plan instead of looking for a better way. See sp_recompile in Books Online
- The table scan is just as fast or faster than reading the index.
- The query is searching more than one column and therefore has to scan the table anyway. Try creating a composite index.
- The query is performing some opearation on the field the prohibits it from using the index.
For example, if you have a datetime field with a nonclustered index on it, this will use the index:
- Select * From MyTable Where MyDate > '1/1/2006'
These may not use the index:
- Select * From MyTable Where MyDate Between '1/1/2006' And '12/12/2006'
- Select * From MyTable Where DateAdd(dd, -1, MyDate) > '1/1/2006'
- Select * From MyTable Where DateDiff(dd, MyDate, '1/1/2006') <= 5
- Select * From MyTable Where MyDate > '1/1/2006' And MyOtherField = 'Jack'
Good tip about sp_MSIndexSpace. That's a new one to me.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Why would this one not use the index (besides the fact that it could return too many rows for a seek to be usefull)?
Select * From MyTable Where MyDate Between '1/1/2006' And '12/12/2006'
-
The too many rows would be the big one. I can't offhand think of another reason.
Say the NC is on MyDate and returns 100 rows. That means over 100 random IOs to lookup the clustered index. Let's say, for simplicity, that would be 200 reads (100 reads of the entire tree, say the tree is 2 levels deep)
Random IOs are a lot more expensive than sequential (I've heard a figure of 6 times more expensive)
If a scan of the entire table would take less than 1200 IOs then the scan is cheaper than the seek.
There are probably optimisations that I've ignored, but that's the general idea.
Additionally, the queries
Select * From MyTable Where MyDate > '1/1/2006'
Select * From MyTable Where MyDate > '1/1/2006' And MyOtherField = 'Jack'
might use the NC index, or might not. Depending on the number of rows that the date argument returns (and in the case of the second one, the other indexes on the table).
Abbout the only query that will definatly, without exception use the NC index is a query of the form
SELECT MyDate, from MyTable Where MyDate '2006/01/01'
because it can be satified completely from the NC index without any need to look up to the cluster.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
"Abbout the only query that will definatly, without exception use the NC index is a query of the form
SELECT MyDate from MyTable Where MyDate = '2006/01/01'
because it can be satified completely from the NC index without any need to look up to the cluster."
We agree on this one. However I think we should point out that it may still need to perform a scan depending on the data distribution.
-
>> Select * From MyTable Where MyDate > '1/1/2006' And MyOtherField = 'Jack'
might use the NC index, or might not.
That's what I said. I said these queries MAY NOT use the index.
I would go even further and say that the only query that would definitely use the NC index would be:
SELECT MyDate from MyTable with(Index(ix__MyTable__MyDate)) Where MyDate = '2006/01/01'
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Lol, now you may be going a little too far
! -
You are right. I should add that I'm not advocating using an index hint nor would I ever advocate that to someone.
Index hints should only be used by trained professionals, and a trained professional is someone that knows better than to use index hints. In other words, don't try this at home.
My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url] -
Yup that sums it up pretty good!
-
Sorry, for some reason I read that as Will Not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I c said the blind woman
.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply