Verificar el factor de relleno de un indice

USE MasivEmail
SELECT
OBJECT_NAME(ips.object_id) AS object_name
,si.name AS index_name
,ROUND(ips.avg_fragmentation_in_percent,2) AS fragmentation
,ips.page_count AS pages
,ROUND(ips.avg_page_space_used_in_percent,2) page_density
FROM sys.dm_db_index_physical_stats (
DB_ID(N'MasivEmail')
,NULL
,NULL
,NULL
,N'DETAILED') ips

CROSS APPLY sys.indexes si
WHERE
si.object_id=ips.object_id
AND si.index_id=ips.index_id
AND ips.index_level=0
AND ips.alloc_unit_type_desc=N'IN_ROW_DATA'


--Verificar el factor de relleno del indice
SELECT
s.name AS scheme_name
,o.name AS object_name
,i.name AS index_name
,i.fill_factor
FROM sys.indexes AS i
JOIN sys.objects AS o ON i.object_id=o.object_id
JOIN sys.schemas AS s ON o.schema_id=s.schema_id
WHERE o.is_ms_shipped = 0

--Reconstruir el indice y setear el fillfacto
ALTER INDEX NombreIndice ON NombreTabla REBUILD
WITH (FILLFACTOR = 70)

--Reconstruir el indice y setear el fillfacto online con espera
ALTER INDEX NombreIndice ON NombreTabla REBUILD
WITH (FILLFACTOR = 70, ONLINE=ON(
WAIT_AT_LOW_PRIORITY(
MAX_DURATION=1 MINUTES,ABORT_AFTER_WAIT=SELF
)
)
);

Particionamiento y densidad

USE NombreDb
SELECT
OBJECT_NAME(ips.object_id) AS object_name
,si.name AS index_name
,ROUND(ips.avg_fragmentation_in_percent,2) AS fragmentation
,ips.page_count AS pages
,ROUND(ips.avg_page_space_used_in_percent,2) page_density
FROM sys.dm_db_index_physical_stats (
DB_ID(N'NombreDb')
,NULL
,NULL
,NULL
,N'DETAILED') ips

CROSS APPLY sys.indexes si
WHERE
si.object_id=ips.object_id
AND si.index_id=ips.index_id
AND ips.index_level=0
AND ips.alloc_unit_type_desc=N'IN_ROW_DATA'

Particionamiento horizontal

ALTER DATABASE PruebaParticionamientoHorizontal
ADD FILEGROUP fgFebrero2016
GO
ALTER DATABASE PruebaParticionamientoHorizontal
ADD FILEGROUP fgMarzo2016
GO
ALTER DATABASE PruebaParticionamientoHorizontal
ADD FILEGROUP fgAbril2016
GO
ALTER DATABASE PruebaParticionamientoHorizontal
ADD FILEGROUP fgMayo2016
GO
ALTER DATABASE PruebaParticionamientoHorizontal
ADD FILEGROUP fgJunio2016
GO
ALTER DATABASE PruebaParticionamientoHorizontal
ADD FILEGROUP fgJulio2016
GO
SELECT name AS AvailableFilegroups
FROM sys.filegroups
WHERE type = 'FG'

ALTER DATABASE [PruebaParticionamientoHorizontal]
    ADD FILE
    (
    NAME = [PartEnero2016],
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SOFTWARE\MSSQL\DATA\PruebaParticionamientoHorizontal.ndf',
        SIZE = 3072 KB,
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [fgEnero2016]
ALTER DATABASE [PruebaParticionamientoHorizontal]
    ADD FILE
    (
    NAME = [PartFebrero2016],
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SOFTWARE\MSSQL\DATA\PruebaParticionamientoHorizontalFeb.ndf',
        SIZE = 3072 KB,
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [fgFebrero2016]

ALTER DATABASE [PruebaParticionamientoHorizontal]
    ADD FILE
    (
    NAME = [PartMarzo2016],
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SOFTWARE\MSSQL\DATA\PruebaParticionamientoHorizontalMar.ndf',
        SIZE = 3072 KB,
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [fgMarzo2016]

ALTER DATABASE [PruebaParticionamientoHorizontal]
    ADD FILE
    (
    NAME = [PartAbril2016],
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SOFTWARE\MSSQL\DATA\PruebaParticionamientoHorizontalAbr.ndf',
        SIZE = 3072 KB,
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [fgAbril2016]

ALTER DATABASE [PruebaParticionamientoHorizontal]
    ADD FILE
    (
    NAME = [PartMayo2016],
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SOFTWARE\MSSQL\DATA\PruebaParticionamientoHorizontalMay.ndf',
        SIZE = 3072 KB,
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [fgMayo2016]

ALTER DATABASE [PruebaParticionamientoHorizontal]
    ADD FILE
    (
    NAME = [PartJunio2016],
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SOFTWARE\MSSQL\DATA\PruebaParticionamientoHorizontalJun.ndf',
        SIZE = 3072 KB,
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [fgJUnio2016]

ALTER DATABASE [PruebaParticionamientoHorizontal]
    ADD FILE
    (
    NAME = [PartJulio2016],
    FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SOFTWARE\MSSQL\DATA\PruebaParticionamientoHorizontalJul.ndf',
        SIZE = 3072 KB,
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 1024 KB
    ) TO FILEGROUP [fgJulio2016]


SELECT
name as [FileName],
physical_name as [FilePath]
FROM sys.database_files
where type_desc = 'ROWS'
GO

CREATE PARTITION FUNCTION [PartitioningByMonth] (datetime)
AS RANGE RIGHT FOR VALUES ('20160101', '20160201', '20160301', '20160401',
'20160501', '20160601');

DROP PARTITION FUNCTION PartitioningByMonth


SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')



CREATE PARTITION SCHEME PartitionBymonth
AS PARTITION PartitioningByMonth
TO (fgEnero2016, fgFebrero2016, fgMarzo2016,
    fgAbril2016,fgMayo2016,fgJunio2016, fgJulio2016);


CREATE TABLE Reports
(ReportDate datetime PRIMARY KEY,
MonthlyReport varchar(max))
ON PartitionBymonth (ReportDate);
GO

INSERT INTO Reports (ReportDate,MonthlyReport)
SELECT '20150105', 'ReportJanuary' UNION ALL
SELECT '20160205', 'ReportFebryary' UNION ALL
SELECT '20160308', 'ReportMarch' UNION ALL
SELECT '20160409', 'ReportApril' UNION ALL
SELECT '20160509', 'ReportMay' UNION ALL
SELECT '20160609', 'ReportJune' UNION ALL
SELECT '20160709', 'ReportJuly'

SELECT
p.partition_number AS PartitionNumber,
f.name AS PartitionFilegroup,
p.rows AS NumberOfRows
FROM sys.partitions p
JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id
JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id
WHERE OBJECT_NAME(OBJECT_ID) = 'Reports'


SELECT * FROM Reports
WHERE ReportDate>'20160303'

DELETE FROM Reports