List all Objects and Indexes per Filegroup / Partition

— List all Objects and Indexes — per Filegroup / Partition and Allocation Type — including the allocated data size SELECT AS DataSpaceName ,AU.type_desc AS AllocationDesc ,AU.total_pages / 128 AS TotalSizeMB ,AU.used_pages / 128 AS UsedSizeMB ,AU.data_pages / 128 AS DataSizeMB , AS SchemaName ,OBJ.type_desc AS ObjectType , AS ObjectName ,IDX.type_desc AS IndexType , […]

Move Tables To Filegroup

1. Move all tables, indexes and heaps, from all schemas into the filegroup named SECONDARY: EXEC dbo.sp_MoveTablesToFileGroup @SchemaFilter = ‘%’, — chooses schemas using the LIKE operator @TableFilter = ‘%’, — chooses tables using the LIKE operator @DataFileGroup = ‘SECONDARY’, — The name of the filegroup to move index and in-row data to. @ClusteredIndexes = […]

Gradually Deleting Data in SQL Server

If you have a situation where you have a very large table in SQL Server, where you need to periodically delete tens of millions of rows of data, there are several ways to do it. If you have a maintenance window (or your database is not required to be available 24 x 7 x 365), […]

Rebuild Indexes

SQL Server Backup, Integrity Check, and Index and Statistics Maintenance Rebuilding Indexes using the SSMS Database Maintenance Wizard Rebuild or Reorganize: SQL Server Index Maintenance SQL Server Maintenance Plan Best Practices Index Fragmentation Recommended SQL Server Maintenance Plan

Drop all user tables statistics

SELECT ‘DROP STATISTICS ‘ + Schema_NAME(d.Schema_id) + ‘.’ + OBJECT_NAME(a.object_id) + ‘.’ + colum_name FROM sys.stats a INNER JOIN sys.Objects d ON d.Object_id = a.object_id inner join sys.tables t ON t.object_id = a.object_id WHERE t.type = ‘U’ AND auto_created = 1 and LIKE ‘_WA_Sys%’