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

NHibernateManagerSql.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate.Cfg;
using NHibernate;
using FluentNHibernate.Cfg;

namespace Migration.Target.Dao.UnitOfWork
{
    public sealed class NHibernateManagerSql
    {
        internal sealed class NHibernateSettings
        {
            public Type DbContextType { get; private set; }
            public string ConfigFileName { get; private set; }
            public Configuration Configuration { get; private set; }
            public ISessionFactory SessionFactory { get; private set; }

            internal NHibernateSettings(Type dbContextType, string configFileName, Configuration configuration, ISessionFactory sessionFactory)
            {
                this.DbContextType = dbContextType;
                this.ConfigFileName = configFileName;
                this.Configuration = configuration;
                this.SessionFactory = sessionFactory;
            }
        }

        #region Singleton

        private static readonly object sync = new object();

        private static NHibernateManagerSql _instance;
        public static NHibernateManagerSql Instance
        {
            get
            {
                if (_instance == null)
                {
                    lock (sync)
                    {
                        if (_instance == null)
                            _instance = new NHibernateManagerSql();
                    }
                }
                return _instance;
            }
        }

        #endregion


        private IList<NHibernateSettings> _dbs;

        private NHibernateManagerSql()
            : base()
        {
            this._dbs = new List<NHibernateSettings>();
        }

        internal ISession GetNewSession(Type dbUnitType, string configFileName, Action<FluentMappingsContainer> modelBuilder)
        {
            NHibernateSettings dbItem = this._dbs.FirstOrDefault(x => x.DbContextType == dbUnitType && x.ConfigFileName == configFileName);

            ISessionFactory sessionFactory;
            if (dbItem != null)
            {
                sessionFactory = dbItem.SessionFactory;
            }
            else
            {
                Configuration configuration = new Configuration();
                configuration.Configure(configFileName);

                FluentConfiguration fluentConfiguration = Fluently.Configure(configuration);
                fluentConfiguration.Mappings(m => modelBuilder(m.FluentMappings));
                sessionFactory = fluentConfiguration.BuildSessionFactory();

                this._dbs.Add(new NHibernateSettings(dbUnitType, configFileName, configuration, sessionFactory));
            }

            return sessionFactory.OpenSession();
        }

        internal Configuration GetConfiguration(Type dbUnitType, string configFileName)
        {
            NHibernateSettings dbItem = this._dbs.FirstOrDefault(x => x.DbContextType == dbUnitType && x.ConfigFileName == configFileName);

            return dbItem.Configuration;
        }
    }
}

NHibernateContextSql.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate;
using FluentNHibernate.Cfg;
using NHibernate.Cfg;
using Migration.Target.Dao.UnitOfWork.Mappings;

namespace Migration.Target.Dao.UnitOfWork
{
    public class NHibernateContextSql : IDisposable
    {
        private string _configFileName;
        public string ConfigFileName { get { return this._configFileName; } }

        private ISession _session;
        public ISession Session { get { return this._session; } }

        public NHibernateContextSql(string configFileName)
        {
            this._configFileName = configFileName;
            this._session = NHibernateManagerSql.Instance.GetNewSession(this.GetType(), this._configFileName, this.OnModelCreating);
        }

        protected void OnModelCreating(FluentMappingsContainer modelBuilder)
        {
            modelBuilder
                .Add(typeof(EmpleadoMap))
                .Add(typeof(TrabajoMap))
                ;
        }

        public ITransaction BeginTransaction()
        {
            return this.Session.BeginTransaction();
        }

        public void Dispose()
        {
            if (this.Session.IsOpen)
                this.Session.Close();
            this.Session.Dispose();
        }



        #region Métodos estáticos exclusivos para crear y/o resolver instancias

        private static Func<NHibernateContextSql> _resolveInstance;

        public static void Configure(Func<NHibernateContextSql> resolveInstance)
        {
            _resolveInstance = resolveInstance;
        }

        public static NHibernateContextSql ResolveInstance()
        {
            if (_resolveInstance != null)
                return _resolveInstance.Invoke();
            return null;
        }

        #endregion



        #region Métodos estáticos exclusivos para instalar y/o configurar la base de datos

        public static void RunDbSetup(bool restore)
        {
            NHibernateContextSql dbContext = NHibernateContextSql.ResolveInstance();
            using (ITransaction trx = dbContext.BeginTransaction())
            {
                Configuration configuration = NHibernateManagerSql.Instance.GetConfiguration(dbContext.GetType(), dbContext.ConfigFileName);

                //if (restore)
                //{
                //    //Regenera el esquema, borra datos
                //    NHibernate.Tool.hbm2ddl.SchemaExport schemaExport
                //        = new NHibernate.Tool.hbm2ddl.SchemaExport(configuration);
                //    //schemaExport.Drop(true, true);
                //    //schemaExport.Create(true, true);
                //    using (FileStream fs = new FileStream(@"c:\Temp\MonitorDB.script", FileMode.Create, FileAccess.Write))
                //    using (StreamWriter sw = new StreamWriter(fs))
                //    {
                //        schemaExport.Create(sw, false);
                //    }
                //}
                //else
                //{
                //    //Comprueba esquema
                //    NHibernate.Tool.hbm2ddl.SchemaValidator schemaValidator
                //        = new NHibernate.Tool.hbm2ddl.SchemaValidator(configuration);
                //    schemaValidator.Validate();
                //}

                //Sincroniza enumeradores

                if (restore)
                {
                    //Crea datos desde cero
                    CreateInitialData();
                }

                //Salva todos los cambios
                trx.Commit();
            }
        }




        private static void CreateInitialData()
        {


        }

        #endregion
    }
}

Mapping.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FluentNHibernate.Mapping;
using Migration.Target.Dao.Entities;

namespace Migration.Target.Dao.UnitOfWork.Mappings
{
    internal abstract class Mapping<TEntity> : ClassMap<TEntity>
        where TEntity : Entity
    {
        public Mapping()
            : base()
        {
            this.LazyLoad();
        }
    }
}

Repository.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Migration.Target.Dao.Entities;
using Migration.Target.Dao.UnitOfWork;
using NHibernate;
using NHibernate.Linq;

namespace Migration.Target.Dao.Repositories
{
    public class Repository<TEntity>
        where TEntity : Entity
    {
        protected NHibernateContextSql GetDbContext()
        {
            NHibernateContextSql dbContext = NHibernateContextSql.ResolveInstance();
            return dbContext;
        }


        #region Miembros de IEntityRepository<TEntity>

        public TEntity Get(long id)
        {
            NHibernateContextSql dbContext = this.GetDbContext();
            ISession session = dbContext.Session;
            return session.Get<TEntity>(id);
        }


        public IQueryable<TEntity> GetQuery()
        {
            NHibernateContextSql dbContext = this.GetDbContext();
            ISession session = dbContext.Session;
            IQueryable<TEntity> queryBase = session.Query<TEntity>();
            return queryBase;
        }

        public TEntity Create(TEntity entity)
        {
            NHibernateContextSql dbContext = this.GetDbContext();
            ISession session = dbContext.Session;
            entity = session.Save(entity) as TEntity;
            return entity;
        }

        public TEntity Update(TEntity entity)
        {
            NHibernateContextSql dbContext = this.GetDbContext();
            ISession session = dbContext.Session;
            session.Update(entity);
            return entity;
        }

        public TEntity Delete(TEntity entity)
        {
            NHibernateContextSql dbContext = this.GetDbContext();
            ISession session = dbContext.Session;
            session.Delete(entity);
            return entity;
        }

        #endregion
    }
}