tocroi72
asked on
Identify Index size
Hello all Experts,
I have a question regarding the index size:
i saparate 2 file group for my database, one is the primary the second one is for index ( i am not
sure what is good or bad about doing this) the innitial though was to just keeping it saparate i guess) -
so , every time i create an index , i create it on the filegroup that i create saparate for the indexes as the syntax below
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
[ ON | filegroup_name ---- this is where i replace the filegroup using my index file group]
now this file group seem to be getting too big (3 times bigger than my primary file group.
what should i do to reduce the size and how....
Please help.
I have a question regarding the index size:
i saparate 2 file group for my database, one is the primary the second one is for index ( i am not
sure what is good or bad about doing this) the innitial though was to just keeping it saparate i guess) -
so , every time i create an index , i create it on the filegroup that i create saparate for the indexes as the syntax below
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
[ ON | filegroup_name ---- this is where i replace the filegroup using my index file group]
now this file group seem to be getting too big (3 times bigger than my primary file group.
what should i do to reduce the size and how....
Please help.
chapmandew
why do you think you need to reduce the size? Any new tables you've created has likely been put on this filegroup as well if you made it the default. you can use sp_spaceused 'tablename' to find the table and index size.
the best option will be creating the Clustered Index on the Primary db file and the others on the secondary...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I ran the sp_spaceused on each table
Name Rows RESERVED data index_size unused
tbl1 3335227 2799576 KB 2232920 KB 497064 KB 69592 KB
tbl2 2635725 1889048 KB 1757152 KB 131720 KB 176 KB
tbl3 2632384 690440 KB 679760 KB 8 KB 10672 KB
tbl4 354 208 KB 168 KB 8 KB 32 KB
tbl5 784442 2157672 KB 2092104 KB 64368 KB 1200 KB
tbl6 507897 122784 KB 99104 KB 23592 KB 88 KB
tbl7 17971 3912 KB 3888 KB 8 KB 16 KB
tbl8 18 24 KB 16 KB 8 KB 0 KB
tbl9 278464 57160 KB 57128 KB 8 KB 24 KB
tbl10 8198 456 KB 392 KB 8 KB 56 KB
tbl11 13223 1480 KB 1472 KB 8 KB 0 KB
tbl12 18855 8904 KB 8880 KB 8 KB 16 KB
tbl13 16176 1352 KB 1288 KB 8 KB 56 KB
tbl14 4967 3336 KB 3312 KB 8 KB 16 KB
tbl15 32494 3536 KB 2800 KB 704 KB 32 KB
tbl16 267003 24904 KB 24840 KB 8 KB 56 KB
tbl17 600 136 KB 104 KB 8 KB 24 KB
tbl18 169 48 KB 32 KB 16 KB 0 KB
tbl19 286117 100552 KB 99520 KB 1000 KB 32 KB
tbl20 143231 163784 KB 163696 KB 8 KB 80 KB
tbl21 10000 1736 KB 1704 KB 8 KB 24 KB
tbl22 762 136 KB 120 KB 8 KB 8 KB
tbl23 30 16 KB 8 KB 8 KB 0 KB
tbl24 169 24 KB 16 KB 8 KB 0 KB
tbl25 18 16 KB 8 KB 8 KB 0 KB
tbl26 47 40 KB 32 KB 8 KB 0 KB
tbl27 267 200 KB 184 KB 8 KB 8 KB
tbl28 95 72 KB 64 KB 8 KB 0 KB
tbl29 104518 16776 KB 16728 KB 8 KB 40 KB
tbl30 181332 29064 KB 29016 KB 8 KB 40 KB
tbl31 458 40 KB 32 KB 8 KB 0 KB
tbl32 2 16 KB 8 KB 8 KB 0 KB
tbl33 169 32 KB 16 KB 16 KB 0 KB
tbl34 633 456 KB 424 KB 8 KB 24 KB
tbl35 0 0 KB 0 KB 0 KB 0 KB
tbl36 548 48 KB 40 KB 8 KB 0 KB
tbl37 61107 11144 KB 11112 KB 8 KB 24 KB
tbl38 4075 560 KB 520 KB 8 KB 32 KB
tbl39 4075 520 KB 480 KB 8 KB 32 KB
tbl40 2165 488 KB 464 KB 8 KB 16 KB
tbl41 95 48 KB 40 KB 8 KB 0 KB
tbl42 6376266 2770528 KB 2318648 KB 451784 KB 96 KB
tbl43 0 8 KB 0 KB 8 KB 0 KB
tbl44 5897293 2562552 KB 2144472 KB 417872 KB 208 KB
tbl45 8014786 3482536 KB 2914472 KB 567864 KB 200 KB
tbl46 70 40 KB 32 KB 8 KB 0 KB
tbl47 24870357 11080192 KB 9405216 KB 1674128 KB 848 KB
i sum up al the indexsize and data , they all add up to about 25 G , however, when i check my physical ndf and MDF, they are much bigger (about 58 G)
I don't understand why the different is so big.( does the 58G include the Reserve in there? ) -
Please explain for me, Thanks.
Name Rows RESERVED data index_size unused
tbl1 3335227 2799576 KB 2232920 KB 497064 KB 69592 KB
tbl2 2635725 1889048 KB 1757152 KB 131720 KB 176 KB
tbl3 2632384 690440 KB 679760 KB 8 KB 10672 KB
tbl4 354 208 KB 168 KB 8 KB 32 KB
tbl5 784442 2157672 KB 2092104 KB 64368 KB 1200 KB
tbl6 507897 122784 KB 99104 KB 23592 KB 88 KB
tbl7 17971 3912 KB 3888 KB 8 KB 16 KB
tbl8 18 24 KB 16 KB 8 KB 0 KB
tbl9 278464 57160 KB 57128 KB 8 KB 24 KB
tbl10 8198 456 KB 392 KB 8 KB 56 KB
tbl11 13223 1480 KB 1472 KB 8 KB 0 KB
tbl12 18855 8904 KB 8880 KB 8 KB 16 KB
tbl13 16176 1352 KB 1288 KB 8 KB 56 KB
tbl14 4967 3336 KB 3312 KB 8 KB 16 KB
tbl15 32494 3536 KB 2800 KB 704 KB 32 KB
tbl16 267003 24904 KB 24840 KB 8 KB 56 KB
tbl17 600 136 KB 104 KB 8 KB 24 KB
tbl18 169 48 KB 32 KB 16 KB 0 KB
tbl19 286117 100552 KB 99520 KB 1000 KB 32 KB
tbl20 143231 163784 KB 163696 KB 8 KB 80 KB
tbl21 10000 1736 KB 1704 KB 8 KB 24 KB
tbl22 762 136 KB 120 KB 8 KB 8 KB
tbl23 30 16 KB 8 KB 8 KB 0 KB
tbl24 169 24 KB 16 KB 8 KB 0 KB
tbl25 18 16 KB 8 KB 8 KB 0 KB
tbl26 47 40 KB 32 KB 8 KB 0 KB
tbl27 267 200 KB 184 KB 8 KB 8 KB
tbl28 95 72 KB 64 KB 8 KB 0 KB
tbl29 104518 16776 KB 16728 KB 8 KB 40 KB
tbl30 181332 29064 KB 29016 KB 8 KB 40 KB
tbl31 458 40 KB 32 KB 8 KB 0 KB
tbl32 2 16 KB 8 KB 8 KB 0 KB
tbl33 169 32 KB 16 KB 16 KB 0 KB
tbl34 633 456 KB 424 KB 8 KB 24 KB
tbl35 0 0 KB 0 KB 0 KB 0 KB
tbl36 548 48 KB 40 KB 8 KB 0 KB
tbl37 61107 11144 KB 11112 KB 8 KB 24 KB
tbl38 4075 560 KB 520 KB 8 KB 32 KB
tbl39 4075 520 KB 480 KB 8 KB 32 KB
tbl40 2165 488 KB 464 KB 8 KB 16 KB
tbl41 95 48 KB 40 KB 8 KB 0 KB
tbl42 6376266 2770528 KB 2318648 KB 451784 KB 96 KB
tbl43 0 8 KB 0 KB 8 KB 0 KB
tbl44 5897293 2562552 KB 2144472 KB 417872 KB 208 KB
tbl45 8014786 3482536 KB 2914472 KB 567864 KB 200 KB
tbl46 70 40 KB 32 KB 8 KB 0 KB
tbl47 24870357 11080192 KB 9405216 KB 1674128 KB 848 KB
i sum up al the indexsize and data , they all add up to about 25 G , however, when i check my physical ndf and MDF, they are much bigger (about 58 G)
I don't understand why the different is so big.( does the 58G include the Reserve in there? ) -
Please explain for me, Thanks.
The reserved amount is the total space taken by the table....database_size will always be larger than the sum of reserved + unallocated space because it includes the size of log files, but reserved and unallocated_space consider only data pages.
Pages that are used by XML indexes and full-text indexes are included in index_size for both result sets. When objname is specified, the pages for the XML indexes and full-text indexes for the object are also counted in the total reserved and index_size results.
Pages that are used by XML indexes and full-text indexes are included in index_size for both result sets. When objname is specified, the pages for the XML indexes and full-text indexes for the object are also counted in the total reserved and index_size results.
Sure. Please change this from a B to an A though....by EE guidelines, this was an A answer.
ASKER
how do i change it.. i don't see the option, please show me. Thanks
I'll put a request in for it...there is a request attention link at the top of the page.
ASKER
Thanks