Index to data ratio - 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

General SQL Server Forums

New to SQL Server Administration

Index to data ratio

Author

Topic

antman123
Starting Member

1 Post

Posted - 2011-04-13 : 11:09:13

Hi all,

I'm looking for a microsoft document that states what a 'healthy' index to data ratio is.

Does anybody know if this exists? And where I can find this?

Thanks,

Anthony

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-04-14 : 09:57:27

Not sure what you mean by "index to data ratio".. can you elaborate what you are looking for?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-14 : 16:05:54

It depends on the database. Read-heavy will likely have more indexes than write-heavy, etc

--
Gail Shaw
SQL Server MVP

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-14 : 16:29:23

The ratio would depend upon your environment, including IO subsystem. If your writes are super fast even with 100+ indexes on a table, then it isn't really causing a problem.

My old rule of thumb was no more than 10 indexes per table, but that was on 2000 and lower. 2005/2008 can support a lot more than that, but "it depends".

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-15 : 14:27:07

First of all

Every table should have Clustered Index and indexes create on the needs of the query,if you will not create the clustered index then non-clustered index will not any advantage of performance

Non-Clustered index based on the where clause ,means if a table have 5 columns c1,c2,c3,c4,c5 and your query is

select * from tab1 where c4 = 10

then you will create the index on C4 column and include the remaining column with this index

This is the basics of Index,you can tune the query by DTA(Database Tuning Advisor) and sys.dm_db_missing_index_details

For Indexes Script
http://aureus-salah.com/2010/09/15/sql-server-generate-auto-indexes/

Regards,
Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-16 : 06:24:38

quote:
Originally posted by Jahanzaib

if you will not create the clustered index then non-clustered index will not any advantage of performance

That is not true. Nonclustered indexes on a heap do improve performance providing they are appropriate for queries

quote:
Non-Clustered index based on the where clause ,means if a table have 5 columns c1,c2,c3,c4,c5 and your query is

select * from tab1 where c4 = 10

then you will create the index on C4 column and include the remaining column with this index

With select *, if you include all the other columns you are duplicating the entire table. In all but very exceptional circumstances, that's stupid.

Very large indexes take space, take time.

That's one reason why queries should not be select *. If the minimum number of columns is selected, it's possible to cover the queries without going insane on include columns.

quote:
For Indexes Script
http://aureus-salah.com/2010/09/15/sql-server-generate-auto-indexes/

That's one of the worst things you can do (right up there with running DTA and accepting all suggestions)

Missing indexes are suggestions. They should not be blindly implemented. Test, examine, think first. It's a great place to start, emphasis start. Missing indexes often contains duplicate indexes, indexes that are near-duplicates of existing ones and ones that you would never in a million years willingly implement.

--
Gail Shaw
SQL Server MVP

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-16 : 15:21:25

Yes Offcourse,you will not create blindly,If you are DBA then you should know the basics of DBA like backup,performance,hours etc then go on.

I was talking about the INCLUDE() option is there when you create an index,thats not stupid ,the way you get to understand is actually stupid,check the index create statement

sp_helpindex tablename will provide indexes information on a table.

DTA is a very good tool to check the indexes and statistics,You can take a backup of database and then restore on a location and then use DTA

That is not true,DTA is worst thing

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-04-16 : 15:26:34

Your script is really bad. Use a script like this instead: http://sqlserverpedia.com/wiki/Find_Missing_Indexes

The reason why the SQL Server Pedia script is better is because it outputs information that you'll need to use to determine which indexes to actually add. The most important thing being impact, and then reads and writes. Once you've identified the ones that look good, you then weed out "duplicates".

By the way, you really need to fix your scripts that you provide on your blog. No one can copy/paste them due to the numbering.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-16 : 15:33:03

This script just to automate the generation of index script nothing else.

you should just create scripts then you will find yourself then this index is required or not

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-16 : 18:23:12

quote:
Originally posted by Jahanzaib

DTA is a very good tool to check the indexes and statistics,You can take a backup of database and then restore on a location and then use DTA

That is not true,DTA is worst thing

You say DTA is a good tool, then that it's the worst thing you can do to a database. Which is it?

More logic, more checking goes into the recommendations from DTA than the suggestions from missing index. Missing index is based on a single query at a time, DTA on the entire workload submitted to it.

DTA is bad, very bad. Blindly creating every index in missing indexes is worse.

--
Gail Shaw
SQL Server MVP

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-17 : 05:37:57

This is very helpfull tool and dynamic management views and functions are also very helpfull

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources