Calculate size of an index for a new database?
How can you calculate size of an index in a database whenever a capacity planning exercise if performed?
By default the size allocated for each user objects depends on the user application and on the amount of space that is created by the application for user-defined tables, global and local temporary tables, indexes, and table variables. About indexes you need to vary about Clustered and Non_Clustered, as with SQL 2005 performing Clustered Index online operation do take fair amount of space in the database including the TEMPDB. It generates a special object called a mapping index in the user space.
You may be thinking to calculate the size of index why you need to concerned about TEMPDB?
As with 2005 features the Index build has an option to sort in tempdb. The sort requires about the same amount of tempdb space as does the index that is being built. For online index build, we may also need a mapping index. in this case you have to calculate the size of this index, multiply the average key size by the number of rows in the index.
So in this case when you have planned to spread the TEMPDB as multiple files of equal size, ensure to spread them out on different disks/spindles unless you are also encountering I/O bottlenecks as well. Technet documentation recommends that "...is to have one file per CPU because only one thread is active per CPU at one time. SQL Server allocates pages for tempdb objects in a round-robin fashion (also referred to as proportional fill) so that the latches on PFS and SGAM pages are distributed among multiple files. This is supported both in SQL Server 2000 and SQL Server 2005. There are improvements to the proportional fill algorithm in SQL Server 2005".
Further on the subject of this blog I recommend referring the updated Books Online for SQL Server 2005 will get you more information:
http://msdn2.microsoft.com/en-us/library/ms178085.aspx - Estimating the Size of a Clustered Index
http://msdn2.microsoft.com/en-us/library/ms187445.aspx - Estimating the Size of a Database
http://msdn2.microsoft.com/en-us/library/ms189124.aspx - Estimating the Size of a Heap
http://msdn2.microsoft.com/en-us/library/ms179325.aspx - Nonclustered Index Design Guidelines
http://msdn2.microsoft.com/en-us/library/ms190620.aspx - Estimating the Size of a Nonclustered Index