locked
VLDB RRS feed

  • Question

  • Hi,

       I have a very large database. Can anyone suggest me any plan to reduce the size of the database. By doing some architectural change of DB's or anyother modifications..

    Wednesday, November 20, 2013 7:23 AM

Answers

  • Yes but may hit performance as well, the best solution for him would be adding more disk space.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, November 21, 2013 5:00 AM
  • That's a very good point Uri.  The documentation indicates "...data compression can help improve performance of I/O intensive workloads", but we found a 5-8% increase in duration with one of our I/O intensive workloads.  It's a trade-off.

    I think the message would be, test it with your data, see what effect it has on your performance.  If it's negative, decide if you want to make the trade-off.  For example, if you have an overnight batch window, it might not matter too much but if you have a web-facing application requiring low-latency, then it could be important.  I guess this is just another way of saying "it depends" : )

    Thursday, November 21, 2013 12:47 PM

All replies

  • Hello,

    Why do you want to reduce the size, are you low on space / backup resources or .. ?

    Suggestions:
    - Use the smallest data type for your data
    - Use as less indexes as possible, but as much as required for performant working queries.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, November 20, 2013 7:35 AM
    Answerer
  • yes, I have low space.

    Wednesday, November 20, 2013 7:52 AM
  • What is the size of log file? Is it Full or Simple recovery mode?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, November 20, 2013 7:57 AM
  • You can also look at data compression to reduce the size of your tables in the database and backup compression to reduce the size of your backups.
    Wednesday, November 20, 2013 11:56 AM
  • Yes but may hit performance as well, the best solution for him would be adding more disk space.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, November 21, 2013 5:00 AM
  • That's a very good point Uri.  The documentation indicates "...data compression can help improve performance of I/O intensive workloads", but we found a 5-8% increase in duration with one of our I/O intensive workloads.  It's a trade-off.

    I think the message would be, test it with your data, see what effect it has on your performance.  If it's negative, decide if you want to make the trade-off.  For example, if you have an overnight batch window, it might not matter too much but if you have a web-facing application requiring low-latency, then it could be important.  I guess this is just another way of saying "it depends" : )

    Thursday, November 21, 2013 12:47 PM