Question About Indexing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Question About Indexing

Author

Topic

IMGrumpy
Starting Member

11 Posts

Posted - 2011-04-12 : 10:29:45

Greetings:
Is there a way to test or check that an index that you have set up is working?

I have a table in which I have specified that the first two columns be a UNIQUE and CLUSTERED index for that table. How can I then tell that this policy has been carried out? When I examime the table using the Server Management Studio, I don't see anything different that tells me that this new index has "kicked in".

Thank you,
Mark

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-12 : 10:51:39

In object explorer, expand the table and expand indexes. You'll see it listed there.

--
Gail Shaw
SQL Server MVP

IMGrumpy
Starting Member

11 Posts

Posted - 2011-04-12 : 11:04:20

Hi Gail:
I have done that and indeed the Index item is there. I guess what I'm looking for is some way to actually see the index in action, if that's possible, as a way to see that it is doing what I think it is supposed to be doing. I don't have enough data in the table (yet) to do any kind of benchmarking.

In cae you haven't already figured it out: I'm a bit of a novice at this SQL thing...

Thanks for your responce.
Mark
--
Gail Shaw
SQL Server MVP
[/quote]

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-12 : 11:51:56

In Management Studio there's an option to "Include Actual Query Plan" on the Query menu. If you turn that on and run your SELECT query, you'll get an extra tab in the results pane called Execution Plan. You'll find all the query operators used to process the query, starting from right-to-left. If you look at the rightmost operator you'll see if it's performing an index seek or scan, vs. a table or clustered index seek/scan. You can hover over the operator and get more information, or right-click it and choose Properties.

IMGrumpy
Starting Member

11 Posts

Posted - 2011-04-12 : 12:00:35

Hey, thanks a lot. I'll try that now. I'll do it before and after adding the index and compare what I see.

Thanks!
Mark

chris_wood99
Yak Posting Veteran

70 Posts

Posted - 2011-04-19 : 07:37:09

run this script and see if your index is helping or hindering your system...

if there are many more reads to writes its a good index. if writes heavily outweighing the reads, you probably want to consider using included columns in your index or removing it all together

SELECT
o.name
, indexname=i.name
, i.index_id
, reads=user_seeks + user_scans + user_lookups
, writes = user_updates
, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
, CASE
WHEN s.user_updates < 1 THEN 100
ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
END AS reads_per_write
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id
INNER JOIN sys.objects o on s.object_id = o.object_id
INNER JOIN sys.schemas c on o.schema_id = c.schema_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000
ORDER BY reads

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources