October 6, 2011 at 9:35 am
Hi Everyone,
Hope all is well.
I have a situation where I am seeing that the size of the indexes that I have on my production db is equal to or greater than the size of the actual table...can any one tell me what could be the reason...I am seeing that there are not more than 2 indexes on a table and some of them have included columns in them...
Thanks for your great help in advance...want to clear my mind with the solution and start tweaking in...
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 6, 2011 at 10:55 am
Sapen (10/6/2011)
Hi Everyone,Hope all is well.
I have a situation where I am seeing that the size of the indexes that I have on my production db is equal to or greater than the size of the actual table...can any one tell me what could be the reason...I am seeing that there are not more than 2 indexes on a table and some of them have included columns in them...
Thanks for your great help in advance...want to clear my mind with the solution and start tweaking in...
Generally when I have seen that the total size of all indexes is greater than the size of the table data you have a lot of indexes that include a lot of columns. The question is are all the indexes being used? Are all the indexes necessary? There are some decent articles on sqlservercentral about how to see if indexes are being used or not. I would probably start there.
October 6, 2011 at 11:31 am
It will really depend on your table structure, index structure, and what type of data you are storing.
If, for example, you have a clustered index with a fill factor of 0, and a non-clustered with a fill factor of 50%, it would be very easy for the non-clustered to be about twice the size of the clustered index (the table), if the index has most of the columns of the table in it.
Also, because of the way that b-tree structures store data, indexes will often have multiple copies of the data in the key columns. This is especially true where you have very low density (high cardinality) for the data being indexed. Are you familiar with b-trees?
Add those two things together, and indexes larger than the table are very easy to achieve.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 6, 2011 at 2:33 pm
Thanks for your reply...I will do some reading on how B-tree structures store data.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 6, 2011 at 6:49 pm
Hi Kubicek,
I have written a cursor that identifies all the indexes on tables that are not being used. Interesting in our case nothing showed up. The results were blank on each table. I still dont get what could be the issue.
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(4000)
DECLARE TableCursor CURSOR FOR
SELECT name AS TableName FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql='SELECT OBJECT_SCHEMA_NAME(I.OBJECT_ID) AS SchemaName,
OBJECT_NAME(I.OBJECT_ID) AS ObjectName,
I.NAME AS IndexName
FROM sys.indexes I
WHERE -- only get indexes for user created tables
OBJECTPROPERTY(I.OBJECT_ID, '''+@TableName+''') = 1
-- find all indexes that exists but are NOT used
AND NOT EXISTS (
SELECT index_id
FROM sys.dm_db_index_usage_stats
WHERE OBJECT_ID = I.OBJECT_ID
AND I.index_id = index_id
-- limit our query only for the current db
AND database_id = DB_ID())
ORDER BY SchemaName, ObjectName, IndexName'
--PRINT(@sql)
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 6, 2011 at 7:50 pm
You might read this blog posting by Kimberly Tripp, it may be very useful for you. It also contains some T-sQL code that could be used as you work towards solving your problem.
http://www.sqlskills.com/BLOGS/KIMBERLY/post/Spring-cleaning-your-indexes-Part-I.aspx
October 6, 2011 at 9:01 pm
Sapen (10/6/2011)
Hi Everyone,Hope all is well.
I have a situation where I am seeing that the size of the indexes that I have on my production db is equal to or greater than the size of the actual table...can any one tell me what could be the reason...I am seeing that there are not more than 2 indexes on a table and some of them have included columns in them...
Thanks for your great help in advance...want to clear my mind with the solution and start tweaking in...
When calculating your index size are you including clustered indexes in your calculation? The CI is the equivalent of your table.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 6, 2011 at 9:02 pm
bitbucket-25253 (10/6/2011)
You might read this blog posting by Kimberly Tripp, it may be very useful for you. It also contains some T-sQL code that could be used as you work towards solving your problem.http://www.sqlskills.com/BLOGS/KIMBERLY/post/Spring-cleaning-your-indexes-Part-I.aspx
Just fixed the url
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 11, 2011 at 9:23 am
Yes I am..sorry for the delay..i thought i already replied
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 11, 2011 at 9:23 am
I did include the clustered indexes
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
October 11, 2011 at 9:39 am
Your CI represents the data of the table so it should be the size of your table. That is why your calculations seem a bit off.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply