July 11, 2012 at 7:43 am
I see my databases index size is growing too large on daily basis, I increased the data size in last 2 days, help is appreciated,
July 11, 2012 at 8:18 am
Can you be more specific about growing too large? If the volume of data included in the index is growing then the index will grow and as long as the index is being used then you probably don't want to drop the index.
Are you doing regular index maintenance?
My suggestion is monitor the growth and then size your database appropriately to have enough space for the anticipated growth for the next 6-12 months and make sure you have plans in place to have enough disk capacity. If adding disk space isn't possible then you need to determine a purging or archiving strategy.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 11, 2012 at 8:40 am
Could you please let me know how the index is related to the table, I mean if the table is used quite often, How the index size works,
July 11, 2012 at 8:53 am
bubby (7/11/2012)
Could you please let me know how the index is related to the table, I mean if the table is used quite often, How the index size works,
to help you understand further please consult the Stairway to indexes articles at this[/url] link
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2012 at 8:59 am
An index's size is dependent upon the size of the columns and the number of rows in an index. For instance a one column index of data type int (4 bytes) on a table with 1000 rows would be approximately 4000 bytes. That is an overly simplified example as there is more that goes into it, but gives you a general idea. Here are a couple of links about how to determine the size of indexes:
Non-clustered - http://msdn.microsoft.com/en-us/library/ms190620(v=sql.105).aspx
Clustered - http://msdn.microsoft.com/en-us/library/ms178085(v=sql.105).aspx
AS Perry has mentioned reading the Stairway to Indexes articles will get you much more information.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 11, 2012 at 9:15 am
Thanks a Lot
July 11, 2012 at 9:42 am
Jack Corbett (7/11/2012)
An index's size is dependent upon the size of the columns and the number of rows in an index. For instance a one column index of data type int (4 bytes) on a table with 1000 rows would be approximately 4000 bytes. That is an overly simplified example as there is more that goes into it, but gives you a general idea. Here are a couple of links about how to determine the size of indexes:Non-clustered - http://msdn.microsoft.com/en-us/library/ms190620(v=sql.105).aspx
Clustered - http://msdn.microsoft.com/en-us/library/ms178085(v=sql.105).aspx
AS Perry has mentioned reading the Stairway to Indexes articles will get you much more information.
also bear in mind with non clustered indexes that while the key columns are required to be under 900 bytes this does not stop you adding included columns which could make the index size huge.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply