DEFINITIONS

Definitions More Info.
Definition ID11.060
TitleSQL
CategoryNOTES
Definitionmssql tablo table compression
Definition DescriptionDECLARE @sql VARCHAR(2000)
DECLARE @dbname VARCHAR(100)
DECLARE @row_count bigint
DECLARE @tbl_name VARCHAR(200)
DECLARE @sch VARCHAR(200)

set @dbname='[gtpbrdb_2017]'

DECLARE tbl_compression CURSOR FOR
SELECT schema_name(o.schema_id) sch, o.name, sum(ps.row_count) as row_count
FROM sys.partitions sp
INNER JOIN sys.tables st ON st.object_id = sp.object_id
INNER JOIN sys.objects o on o.object_id = st.object_id
INNER JOIN sys.dm_db_partition_stats ps on o.object_id = ps.object_id
WHERE sp.data_compression = 0
GROUP BY schema_name(o.schema_id), sp.object_id,o.schema_id, o.name
ORDER BY row_count
OPEN tbl_compression
FETCH NEXT FROM tbl_compression INTO @sch, @tbl_name,@row_count
WHILE @@FETCH_STATUS=0
BEGIN
SET @sql='USE '+ @dbname+'
ALTER TABLE ['+@sch+'].['+@tbl_name+'] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = PAGE,MAXDOP=4)
GO'
PRINT ('-- tablo_Adi: '+@tbl_name+' row_count: '+convert(varchar(50),@row_count))
PRINT (@sql)
FETCH NEXT FROM tbl_compression INTO @sch, @tbl_name,@row_count
END
CLOSE tbl_compression
DEALLOCATE tbl_compression





---- Tum Tablolar
select CONCAT('ALTER TABLE [', schema_name(o.schema_id), '].[',
o.name, '] REBUILD PARTITION = ALL WITH(DATA_COMPRESSION = PAGE, MAXDOP=8 )
GO'),
sum(ps.row_count) as row_count
from sys.dm_db_partition_stats ps
join sys.objects o on o.object_id = ps.object_id
where o.type='U' and ps.index_id in (0,1)
group by ps.object_id,o.schema_id, o.name
order by row_count


--- Tüm Indexler

SELECT DISTINCT
s.name,
t.name,
i.name,
i.type,
i.index_id,
p.partition_number,
p.rows
,i.fill_factor
, '
ALTER INDEX [' + i.name + '] ON [' + s.name + '].[' + t.name + '] REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE, MAXDOP = 4)
GO'
FROM sys.tables t
LEFT JOIN sys.indexes i
ON t.object_id = i.object_id
JOIN sys.schemas s
ON t.schema_id = s.schema_id
LEFT JOIN sys.partitions p
ON i.index_id = p.index_id
AND t.object_id = p.object_id
WHERE t.type = 'U'
AND p.data_compression_desc = 'NONE'
ORDER BY p.rows






















select CONCAT('ALTER TABLE [', schema_name(o.schema_id), '].[',
o.name, '] REBUILD PARTITION = ALL WITH(DATA_COMPRESSION = PAGE )'),
sum(ps.row_count) as row_count

from sys.dm_db_partition_stats ps
join sys.objects o on o.object_id = ps.object_id
where o.type='U' and ps.index_id in (0,1)
group by ps.object_id,o.schema_id, o.name
order by row_count



SELECT name, index_id
FROM sys.indexes

SELECT name,
s.used / 128.0 AS SpaceUsedInMB,
size / 128.0 - s.used / 128.0 AS AvailableSpaceInMB
FROM sys.database_files
CROSS APPLY
(SELECT CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)) s(used)
WHERE FILEPROPERTY(name, 'SpaceUsed') IS NOT NULL



SELECT DISTINCT
s.name,
t.name,
i.name,
i.type,
i.index_id,
p.partition_number,
p.rows
,i.fill_factor
FROM sys.tables t
LEFT JOIN sys.indexes i
ON t.object_id = i.object_id
JOIN sys.schemas s
ON t.schema_id = s.schema_id
LEFT JOIN sys.partitions p
ON i.index_id = p.index_id
AND t.object_id = p.object_id
WHERE t.type = 'U'
AND p.data_compression_desc = 'NONE'
ORDER BY p.rows desc


EXEC sp_estimate_data_compression_savings
@schema_name = 'Sales',
@object_name = 'SalesOrderDetail',
@index_id = NULL,
@partition_number = NULL,
@data_compression = 'ROW'

EXEC sp_estimate_data_compression_savings
@schema_name = 'Sales',
@object_name = 'SalesOrderDetail',
@index_id = NULL,
@partition_number = NULL,
@data_compression = 'PAGE'


ALTER INDEX PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
ON Sales.SalesOrderDetail
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE);

ALTER INDEX IX_SalesOrderDetail_ProductID
ON Sales.SalesOrderDetail
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE);





/*
SON HALI
*/

RecordBycunay
Record Date19-10-2021 16:11:00
Düzenle
Kopyala
Sil