I'm looking for a way to reorganize a MS SQL Server database. By re-organize I mean that both tables and indexes are left with no fragmentation.
The resulting database will only be used in read-only mode. One of the aims of the exercise is to release space in the database and the other is to try to make it go as fast as possible. (assuming here that a contiguous layout of both tables and indexes actually has a positive impact on performance, I understand there's some debate about this in modern SSD world … but at the very least it doesn't have a negative impact on performance). All in all the result will be a frozen copy of its source. The clone-and-compact operation must take place periodically and must be able to run unattended.
The size of actual data (tables and indexes) is approx 500 GB but there's a lot of unused space so the on-disk size is almost 2 TB. I don't care if the clone is inaccessible during the reorganization operation, nor do I care if it takes days to execute the reorg. But it must be automated (meaning it must run unattended, please don't point me to MS SSMS)
I come from PostgreSQL background. In PostgreSQL I would use pg_dump/ pg_restore to export/import the whole database and such operation would by its very nature re-write tables and indexes to be contiguous. In Oracle world a so-called exp / imp would achieve the same if I remember correctly. Isn't there are similar export/import tool in MS SQL world ?
I've looked at:
BACKUP/RESTORE. Not applicable. This method retains table and index fragmentation.DBCC SHRINKFILE/DBCC SHRINKDATABASE. These methods release unused space at the tail. Not quite what I'm after. They are also insanely slow as they are meant to execute while the database is operational. … I appreciate the reorg-while-online feature but it is not what I need. (I haven't been able to find any docs if such operations can be made go faster if I put the db into SINGLE_USER mode or whatever)bcp. Can only export table data, not a full database with all its tables, constraints, indexes, users, triggers, stored procedures, etc.
I'm looking for a Microsoft way of doing this. Buying a third-party tool just to do this type of export/import doesn't seem fair to me. SQL Server is expensive enough as it is.
Perhaps I'm just hung up on how this is done in other database engines and therefore looking for information in the wrong place?
Best Answer
Edit 1: Aaron noted in a comment that compression can be beneficial here. Good point. Compression options include columnstore indexes (clustered or nonclustered - let's not go into details at this point), row compression and page compression.
As you know, backup and restore will give you a binary copy of the database, so it won't help you.
Shrink will fragment the indexes even more since it move pages towards the beginning of the file without consideration of the page ordering of an index.
So you are left with index reorg or rebuild. Rebuild is more thorough since it creates a new index. But it need space for this. So if you shrink and then rebuild, then the rebuild will create a new index "at the end of the file" possible making the file grow (depending on how much free space you left when you did the shrink).
So, it is a bit of a contradiction to both have stuff nicely aligned and have no free space. What you can hope for is to have free space corresponding to you your largest allocation (index/heap) in the file. I.e., "working space" for the largest one.
(If that isn't acceptable, then you are left with deciding which allocations are too large to have spare room for (remember, you only need spare room for one). For indexes you can drop them, shrink and then create. For the data (clustered index and heap) you'd have to export outside SQL Server, truncate the table, shrink and then import the data. Edit 2: Nikita added a good point on moving the data to a different filegroup as an option to exporting it outside SQL Server. Good point and definitely worth considering. If you decide to go the "get the data out of the filegroup" route.)
How to go about this? Use Ola's scripts as suggested by Nikita for your B-trees. And for your heaps (I hope you don't have that many) you can for instance use my stored procedure.
So, shrink first, then rebuild your b-trees and heaps. And it can take a very long time, especially the shrink (depends on if you have LOBs, heaps etc).
Come to think about it, we have two options for exporting and importing the data:
BACPAC. This will give you a zipped file containing an XML file describing your tables and BCP files with the data. And then you use a tool to import based on this. You can use BACPACs from SSMS (right-click a database and the databases folder), sqlpackage.exe and of course there are a few Powershell options. BACKPACs are designed to work with Azure SQL database so it will choke on anything what isn't available there, AFAIK.
SSMS Generate Script. You find this if you right-click a database. This will create a .sql file containing DDL and INSERT. Cross your fingers that it does it right. And for 500 GB..., I have my doubts. Anyhow, you can also access this functionality using SSIS, if that is beneficial for you.