1

On MSSQL (our DB size is 520g), there is one filestore table that holds tons of files. 65-70% of this 520g is sitting in there. So essentially, it's a 200g database with 320g worth of files in this one table.

I have ran a script that removed around 120g worth of old records from this table. The DB is still sitting at 520g, but there's this 120g hole inside that is available for writes.

  1. Should there be a significant improvement in overall DB performance after clearing up this much space in the DB?

  2. How does having a huge amount of data in one table effect the performance of queries that have nothing to do with that table?

Improve this question
3
  • 3
    Can you be elaborate on what you mean by "filestore table"? Commented Nov 22 at 0:27
  • 2
    You write you already removed a bunch of data. Did it improve performance for you? Commented Nov 22 at 12:37
  • The filestore table is holding raw data for file attachments (mostly PDF's, but some excel sheets and email MSG files as well). The performance seemed the same, but it's hard to quantify on test environment because it's not getting hammered with queries constantly like production. Commented Nov 25 at 18:05

2 Answers 2

Reset to default
1

(1) Should there be a significant improvement in overall DB performance after clearing up this much space in the DB?

Not really.

If you have suitable indexing in place, potential performance gains are negligible.

If you don't have suitable indexes, potential performance gains are larger, but you should add/tune your indexes for even greater performance improvements anyway.

(This is assuming you work with small amount of records at the time. Which I imagine is intended usage pattern for such table.)

How does having a huge amount of data in one table effect the performance of queries that have nothing to do with that table?

Not at all.

If you won't touch the data, it will quietly sit on disk and have basically no overhead.

Improve this answer
3
  • Thank you for the input. There's a third party application running complex queries that are not index optimized, so hoping these will improve. I got an AI response that mentioned a performance gain (from large allocated space created by deletes) due to avoiding "auto-growth storms"? Is there a benefit to system writing to available space from delete VS normal way to just using more unallocated disk space? Commented Nov 25 at 18:19
  • 1
    @JamesJohnson Growing the database file can be costly, reusing free space inside the file is cheaper (resource-wise). But that's applicable only for writes - it's not like random queries will be faster if there is more free space inside the data file. Commented Nov 25 at 18:46
  • 1
    TL;DR: If you can see the space being used again in foreseeable future, do not shrink the data file. Commented Nov 25 at 18:48
0

Queries to the table this table will probably be faster. But nothing else should change. You may see better improvement if you shrank that file as well.

Improve this answer
1
  • 1
    I believe shrinking the file will not help. Commented Nov 24 at 6:31

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.