none
how can we tune a ~2TB DB with huge tables RRS feed

  • Question

  • Forum,

    How do we tune a huge DB with ~2TB with huge tables most of them are heaps and some have cluster and non clustered indexes, this is not about query tuning need to implement best practices to run this huge DB efficiently.

    Index rebuilt/reorg is in place and working as expected, archiving most unused tables and moving to a separate DB, heap tables im trying to create a PK/Clustered Index, Statistics are being updated and stale unused stats dropped , Unused PK being dropped.

    What am i missing and what are any other best practices that can be implemented to make this huge DB run smooth. I did not try compressing the DB file which is a no no

    Any pointers much appreciated and thanks in advance 

     

    Tuesday, May 19, 2020 8:48 PM

All replies

  • I don't understand what you want to "tune" here? Do you have any performance issues?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, May 20, 2020 6:41 AM
  • I agree with Olaf. Tuning is largely about having the right data structures (in your case heaps might not be beneficial, and I didn't see anything mentioned about columnstore indexes) and well-written queries. Id you are looking for something else, then please clarify.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, May 20, 2020 10:02 AM
  • Thanks Olaf & Tibor,

    Like i mentioned this DB is massive and queries are running fine as expected , issue here is the DB is chewing up massive amounts of disk space and everytime we keep adding 500GB. Of course the data is also piling up which is expected and i suspect it was not designed correctly in the first place . Moving forward i want to implement best practices and reclaim storage .

    thanks in advance 

    Wednesday, May 20, 2020 11:24 AM
  • Which edition are you using? If it's Enterprise then you can use Data Compression

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, May 22, 2020 7:51 AM

  • . Moving forward i want to implement best practices and reclaim storage .


    This is not a problem per se but yes you have to think about storage. If your database is growing because you are adding data and if there is not much of a free space in database then there is nothing much you can do apart from adding space and Compression like Olaf said. I would still prefer adding space than going to compression because later requires some round of testing to make sure you do not get any performance impact after enabling compression.

    Have you thought about archiving some of the data  ?


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, May 22, 2020 10:14 AM
  • Things to consider, look into, include:

    Do you have heaps and delete data? SQL server is often very bad at reclaiming space for heaps.

    Are you utilizing (clustered) columnstore indexes? Data compression?

    Are there indexes that aren't used, i.e. overhead?

    As you probably realize, one need lots more info to be more specific...


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, May 25, 2020 10:21 AM
  • Do you have heaps and delete data? SQL server is often very bad at reclaiming space for heaps.

    > We have a lot of them , the heap tables 

    Are you utilizing (clustered) columnstore indexes? Data compression?

    > I will start looking into compression and columstore indexes

    Are there indexes that aren't used, i.e. overhead?

    >We already have this in place and drop them that are not used .

    Tuesday, May 26, 2020 11:12 AM
  • partitioning and compression in combination is important in managing large databases. Proper use of Filegroups can help in managing large DB's

    when you say that you are adding 500GB of additional space periodically, so you are on managed storage?

    a powerful storage system is important, both in terms of random and sequential (large block IO) are important. in general, SAN and other managed storage systems are pitiful when it comes to large block IO performance (>10GBytes/s). They might tell you they have >10G, but these people tend to no understand the difference between bits and bytes.

    there are uses for heaps, but I would still put large heap tables in partitions. building and dropping clustered indexes on heaps is one method for reclaiming space. (may need to switch out, then back)

    Also: do you have lob? i.e. large char or binary columns > 8KB? these are very tricky in very large DBs.



    jchang

    Tuesday, May 26, 2020 12:12 PM