Database Size Issues
-
SQL Server 2005 Database sizes 25-30Gb There are only two sites running the software at the moment. I've recently joined a very small new company, and taken on several existing software products. I'm very concerned about the sizes of their customers databases. I've checked that the log file has not grown excessivly which was not the case, after that i have looked through all the tables in the database and found that 95% of the storage is in two tables. Checking the storage allocation for both those tables i have found that each has around 25% of the sorage being used for data, and 75% for indexing. This seemed very wrong to me, can any one recomend a way of re indexing the tables so that the storage being used is lower. If its just a case of removing all the indexes and starting again is there any thing i should be aware of. Thanks in advance for any replies. If more specific information is needed please let me know.
-
SQL Server 2005 Database sizes 25-30Gb There are only two sites running the software at the moment. I've recently joined a very small new company, and taken on several existing software products. I'm very concerned about the sizes of their customers databases. I've checked that the log file has not grown excessivly which was not the case, after that i have looked through all the tables in the database and found that 95% of the storage is in two tables. Checking the storage allocation for both those tables i have found that each has around 25% of the sorage being used for data, and 75% for indexing. This seemed very wrong to me, can any one recomend a way of re indexing the tables so that the storage being used is lower. If its just a case of removing all the indexes and starting again is there any thing i should be aware of. Thanks in advance for any replies. If more specific information is needed please let me know.
Sometimes the reality is indexing needs more space than the data itself, so 75% for indexing may not be out of line. You can simply backup the database and restore it (to a different location) so the database table and indexes are re-created. That way you can find out if the indexing really needs that much space or the original database needs compacting. Chances are if the indexing part of the original database needs compacting, the data part also needs compacting. The ratio may change but indexing still needs more space than the data itself.
-
SQL Server 2005 Database sizes 25-30Gb There are only two sites running the software at the moment. I've recently joined a very small new company, and taken on several existing software products. I'm very concerned about the sizes of their customers databases. I've checked that the log file has not grown excessivly which was not the case, after that i have looked through all the tables in the database and found that 95% of the storage is in two tables. Checking the storage allocation for both those tables i have found that each has around 25% of the sorage being used for data, and 75% for indexing. This seemed very wrong to me, can any one recomend a way of re indexing the tables so that the storage being used is lower. If its just a case of removing all the indexes and starting again is there any thing i should be aware of. Thanks in advance for any replies. If more specific information is needed please let me know.
Get to know Dynamic Management Views ... This view may be very helpful: sys.dm_db_index_usage_stats Check this article out ... http://www.mssqltips.com/tip.asp?tip=1239[^]
-
SQL Server 2005 Database sizes 25-30Gb There are only two sites running the software at the moment. I've recently joined a very small new company, and taken on several existing software products. I'm very concerned about the sizes of their customers databases. I've checked that the log file has not grown excessivly which was not the case, after that i have looked through all the tables in the database and found that 95% of the storage is in two tables. Checking the storage allocation for both those tables i have found that each has around 25% of the sorage being used for data, and 75% for indexing. This seemed very wrong to me, can any one recomend a way of re indexing the tables so that the storage being used is lower. If its just a case of removing all the indexes and starting again is there any thing i should be aware of. Thanks in advance for any replies. If more specific information is needed please let me know.
If the data is forced into two main tables with little else going on I would expect them to be indexed to the hilt in order to get the data back out. It all depends on the database construction and the associated business rules.
If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850) [My Articles] [My Website]
-
Sometimes the reality is indexing needs more space than the data itself, so 75% for indexing may not be out of line. You can simply backup the database and restore it (to a different location) so the database table and indexes are re-created. That way you can find out if the indexing really needs that much space or the original database needs compacting. Chances are if the indexing part of the original database needs compacting, the data part also needs compacting. The ratio may change but indexing still needs more space than the data itself.
this database has been freshly restored from a backup so i guess from what you have said that would rule out the compacting being required. To give some more of an idea of the issue the tables that are being affected are used to store financial transactions, one table contains 7.7 million rows, the storage cost is 3.9gb for the data, and 16.4gb for the indexing. This amount of data is resulting in very slow processing of even simple select queries, and my thoughts are that to resolve the problem some kind of archiving needs to be implemented. This is probably a dumb question but do the data types being indexed have a direct affect on the storage space required for the indexing. i.e. would indexing a column of the type guid be significantly more storage intensive than an int column. Sorry if the question is overly simple, i've worked with sql databases in the past and never seen storage requirements even close to this.
-
Get to know Dynamic Management Views ... This view may be very helpful: sys.dm_db_index_usage_stats Check this article out ... http://www.mssqltips.com/tip.asp?tip=1239[^]
thank you, i'll have a thorough read through this asap.
-
this database has been freshly restored from a backup so i guess from what you have said that would rule out the compacting being required. To give some more of an idea of the issue the tables that are being affected are used to store financial transactions, one table contains 7.7 million rows, the storage cost is 3.9gb for the data, and 16.4gb for the indexing. This amount of data is resulting in very slow processing of even simple select queries, and my thoughts are that to resolve the problem some kind of archiving needs to be implemented. This is probably a dumb question but do the data types being indexed have a direct affect on the storage space required for the indexing. i.e. would indexing a column of the type guid be significantly more storage intensive than an int column. Sorry if the question is overly simple, i've worked with sql databases in the past and never seen storage requirements even close to this.
The data types being indexed does not have impact on the size used to store the indexes. What you described indicates even more indexing is needed for the database table since some simple "select" queries are very slow. You may need to remove some indexes not being used by the program and add some new indexes based on the queries frequently conducted. When the needed index does not exist, "select" queries on such large tables will be very slow. I was once amazed by how much performance gains was brought by adding proper indexes when I was working on a large database table (similar to your case). So it may worth a try to get a close look at the database to find out what you need for it.