Installing SQL Server 2008 Reporting Services on Failover Cluster in already clustered instance

SQL Server

http://brodaric.blogspot.com.es/2011/04/installing-sql-server-2008-r2-reporting.html

Installing SQL Server 2008 Reporting Services on Failover Cluster in already clustered instance

A way of achieving SQL Server 2008 High Availability is installing SQL Server on top of Windows Server 2008 failover cluster. Only Database Engine Services and Analysis Services are cluster aware while SQL Server 2008 Reporting Services and shared components (Integration Services, Management Studio, Business Intelligence Development Studio etc.) are not.

According to available documentation installing Reporting Services in already clustered SQL Server instance is not supported. If you want the Reporting Services on the other nodes you must install them in separate SQL Server instances. If you want the highly available Reporting Services they should be deployed in a farm which provides both availability and load balancing. For details see Planning and Architecture (Reporting Services) .

What I needed was High Availability, but I didn’t have additional servers to create the farm (with this scenario you need at least 2 additional servers for farm or NLB). Also I didn't want to manage multiple SQL Server instances side by side. Multiple instances mean a lot of administration because each instance must be handled separately. There are scenarios when you should or want to use multiple instances, but not in my case. Use of existing Windows/SQL failover cluster seemed logical. Described solution is tested and deployed using both SQL Server 2008 and R2 Enterprise Edition. Standard Edition of SQL Server does not support Scale-Out deployment. For details see Planning for Scale-Out Deployment.

Setup of SQL Server 2008 failover clustering has changed in this release. To install or upgrade a SQL Server failover cluster, you must run the Setup on each node of the failover cluster. You create a failover cluster by running Setup on first node and selecting all desired components.  As I already mentioned only the Database Engine and Analysis Services support failover clustering, while other components run as a stand-alone feature without failover capability. Shared components are installed on all other nodes while adding those nodes to the existing failover cluster. To install the Reporting Services on other nodes, after adding those nodes to the existing SQL Server failover cluster, you must run setup once again.

Running setup on the second node and selecting the Reporting Services caused setup to fail with following error:

" StandaloneInstall_HasClusteredOrPreparedInstanceCheck
Checks if the selected instance name is already used by an existing
cluster-prepared or clustered instance on any cluster node.

Failed - The instance selected for installation is already installed and
clustered on computer . To continue, select a different instance to
cluster."

The Solution is to run setup and skip this check to install the Reporting Services in already clustered instance.

Run the following command at the command prompt to start setup and add Reporting Services on other nodes:

Setup.exe /SkipRules=StandaloneInstall_HasClusteredOrPreparedInstanceCheck /Action=Install

This will run Setup in full interaction GUI mode while skipping cluster instance check. Just follow setup to install Reporting Services in SQL Server clustered instance.

General steps for installing SQL Server failover cluster with Reporting Services on each node are:

1.    Create SQL Server failover cluster by installing first node. Start setup and select option "New SQL Server failover cluster installation". Make sure you select Reporting Services while installing first node.

2.    Install SQL Server failover clustering on other nodes. Start setup and select option "Add node to a SQL Server failover cluster". You can not chose any features. You will add Reporting Services later.

3.    Install Reporting Services on all nodes except first node in SQL Server clustered instance. Run setup from command prompt using command mentioned above.

4.    Configure Reporting Services on each node. Run "Reporting Services Configuration tool" on each node.

After installing Reporting Services on failover cluster please keep in mind:

• Reporting Services running on an Active-Passive cluster handle requests on each cluster node on which the service is deployed.
• Report server must be configured to use SQL failover cluster virtual name to connect to the report server database. This is because it is hosted on a SQL Server that is part of a failover cluster. If not, the report server will be unable to connect to the report server database if a failover occurs.

This solution provides the highly available Reporting Services with default SQL server instance and uses already deployed hardware. It is not substitute for a true Scale-out deployment of the Reporting Services, but a way of achieving high availability (we just used the existing high availability platform). Scale-out enables you to increase the number of users who can concurrently access/invoke reports and improves the availability of the report server.


Error al inciar RS después de instalar SP2
Una vez instalado el RS en el nodo pasivo, al iniciar el servicio se produce un error:

El workarround aplicado para solucionar el fallo del inicio del servicio de Reporting, es el Método 2 del siguiente enlace:

http://support.microsoft.com/kb/2745448/EN-US

SQL Server

http://msdn.microsoft.com/en-us/library/ms157293(SQL.90).aspx

Planning a Reporting Services Deployment

SQL Server 2005 Reporting Services offers two deployment models:

• A standard deployment consists of a single report server instance that uses a local or remote SQL Server Database Engine to host the report server database. You can use SQL Server 2000 or SQL Server 2005 to host the report server database.
• A scale-out deployment consists of multiple report servers that share a single report server database. The database can be installed on a remote SQL Server instance or locally with one of the report servers. The SQL Server instance that hosts the report server database can be part of a failover cluster.
  Scale-out deployment is supported in the following editions: Enterprise Edition, Developer Edition, and Evaluation Edition.

To simplify the deployment process, you can use checklists that describe the sequence of tasks that must be performed in order to complete a standard deployment. For more information, see Server Deployment Checklist. To learn more about scale-out deployment, see Configuring a Report Server Scale-Out Deployment. For more information about SQL Server 2005 licensing, see the SQL Server 2005 Licensing section on the How to Buy page of the SQL Server Microsoft Web site.

Standard Deployment

The following diagram shows the standard deployment model where the report server database is located on a remote server. You can also install it locally so that all server components are on the same computer.

Primary considerations in choosing where to host the report server database are:

• Processing resources
• Disk space availability

Both the report server and the Database Engine compete for processing resources such as CPU time, memory, and disk access. Some report server operations are resource intensive. For example, a report server attempts to use all available memory for report rendering operations. Running the report server on a separate computer can reduce competition for processing resources.

Report server requirements for disk space provide a second reason for using a remote SQL Server Database Engine to store report server data. Although the footprint of a report server database may be small initially, disk space requirements can grow significantly at run time depending on how you run reports and the number of users accessing the report server. For more information, see Report Server Database Requirements.

Scale-Out Deployment

You can deploy Reporting Services in a scale-out deployment to create a highly available and scalable report server installation. Configuring a scale-out deployment is also useful if you want to improve the performance of scheduled operations and subscription delivery. A report server scale-out deployment consists of multiple report servers that share a single report server database. Each report server in the deployment is referred to as a node. Nodes participate in the scale-out if the report server is configured to use the same report server database as another report server.

Report server nodes can be load balanced to support high volume reporting. You can also create the report server database on a failover cluster to support high availability requirements.

Unsupported cluster configurations include deploying a complete report server installation (that is, a report server and its database) on each node of a multi-node cluster. Specifically, you cannot deploy Reporting Services on a two-node cluster that consists of an active node and a passive node that is used when the active node fails.

Deploying on a Network Load Balanced (NLB) Cluster

You can run report server nodes on an NLB cluster. You can use a software or hardware solution to implement the NLB cluster. To run the report servers on an NLB cluster, you must use software and tools that support that functionality. Reporting Services does not provide server cluster or virtual server management, nor does it provide a way for you to define a virtual server name that provides a single point of entry to all of the nodes in a report server scale-out.

NLB is only necessary if you want to improve report server performance for on-demand reporting and interactive reports (such as drillthrough and matrix reports). Scheduled reports and subscription processing are faster on a scale-out deployment, but do not necessarily require an NLB cluster to get faster performance.

Deploying on a SQL Server Failover Cluster

SQL Server 2005 provides failover clustering support so that you can use multiple disks for one or more SQL Server instances. Failover clustering is supported only for the report server database; you cannot run the Report Server Windows service or Web service as part of a failover cluster.

To host a report server database on a SQL Server failover cluster, the cluster must already be installed and configured. You can then select the failover cluster as the server name when you create the report server database.

Although the Report Server Windows service and Web service cannot participate in a failover cluster, you can install Reporting Services on a computer that has a SQL Server failover cluster installed. The report server runs independently of the failover cluster. If you install a report server on a computer that is part of a SQL Server failover instance, you are not required to use the failover cluster for the report server database; you can use a different SQL Server instance to host the database.

Scale-out Deployment Diagram

The following diagram shows multiple report servers and report server databases deployed in separate server clusters.

Indices en SQL Server: diferencias entre Rebuild y Reorganize

SQL Server

http://blogs.solidq.com/elrincondeldba/post.aspxc?id=209&title=indices+en+sql+server%3A+diferencias+entre+rebuild+y+reorganize

Introducción

Debido a que toda instancia de SQL Server necesita mantenimiento para mantener unos niveles de rendimiento razonables, este post nace con la intención de profundizar en la fragmentación, definir sus tipos y aplicación de soluciones. La reorganización y reconstrucción nos van a permitir paliar este problema en función de la magnitud de la fragmentación. Veremos como funcionan y sus diferencias.

¿Qué es la fragmentación?

La fragmentación es un fenómeno por el cual un todo se disgrega en partes.

¿Porque es perniciosa la fragmentación?

La fragmentación provoca una degradación del rendimiento ya que los índices se vuelven ineficaces. El grado de fragmentación de un índice determina si este va a ser usado o no por el optimizador de consultas de SQL Server.  En algunos casos el optimizador puede usar un índice que se encuentra muy fragmentado y que puede afectar el rendimiento de la base de datos. Otro caso que puede ocurrir es que el optimizador de consultas de SQL Server no utilice el índice. En esta situación estaríamos manteniendo un índice que no estamos usando, con lo cual estamos desperdiciando recursos del servidor, (cpu, disco, memoria).

En el caso particular de la informática tenemos varias clasificaciones:

Clasificación por “Tipo de Objeto”

• A nivel de fichero, fragmentación tradicional, un fichero está disperso en el disco duro. Este tipo de fragmentación se solucionan con herramientas del sistema operativo. Por ejemplo Defrag.
• De Densidad, se denomina así a la fragmentación que se produce en el espacio libre. A continuación tenemos una ilustración donde se podemos observar fragmentación a nivel de fichero y en el espacio libre.

• 
  
• 
  
  
• Lógica, se produce en los índices, este tipo de fragmentación es el que vamos a tratar en el post. El orden lógico (clave del índice) y el orden físico (páginas) son diferentes.
• 
  

• Fragmentación de extensión. Las extensiones en SQL Server son conjuntos de 8 páginas, al igual que en el caso anterior las extensiones de memoria no son contiguas.

Clasificación por el “Lugar Donde se Produce”

Este tipo de clasificación solo aplica a índices, tenemos:

• Fragmentación Interna. Cuando se realiza un borrado de un registro en una página se libera espacio. Este hecho implica que hay parte de la página que se está utilizando ya que tiene datos y parte que no. Cuando existen huecos en generados por borrados en páginas se denomina fragmentación interna. Cuando tenemos fragmentación interna no estamos utilizando el espacio óptimamente. La defragmentación elimina esos huecos. Una vez eliminados tendremos la misma información en menos páginas. Al tener menos páginas en el caso que tuviéramos que leer esos datos recorreríamos menos páginas dedicando menos tiempo y obteniendo una mejora de rendimiento y utilización de recursos en nuestra instancia de SQL Server.

Una vez realicemos un "Alter table rebuild" o "Alter table reorganice" tendremos:

• Fragmentación Externa. Este tipo de fragmentación se produce con inserciones. Cuando se realiza un inserción los datos como hemos visto se guardan en páginas. Como los índices deben mantener el orden lógico por definición, si no cabe en la página se toma una página vacía y se inserta el valor nuevo. Esta página no suele estar junto a la original por lo que para leer se producirían saltos en el orden de lectura de las páginas, lo cual no es eficiente sobre todo si hay muchos (Ver imagen anterior de la fragmentación lógica). A este fenómeno se le denomina “Page Split”. Es como leer un libro donde tienes que ir dando saltos una vez te has leído la página. El no leer páginas contiguas exige que el disco tenga que trabajar más para ir situándose en el sector de disco donde está cada página. En entornos donde hay muchas inserciones es conveniente ajustar el parámetro fill factor delíndice. Este parámetro le indica a SQL Server que debe dejar el porcentaje de espacio libre de página que le especifiquemos. De esta manera cuando se tenga que insertar un registro en la página utilizaremos este espacio en lugar de tener que utilizar una página nueva.

Rebuild vs Reorganize en SQL Server 2008

Rebuild (Reconstrucción)

Como su nombre indica regenera la estructura del índice completamente. En el caso de los índices agrupados (clustered index) es mucho más óptimo que eliminar el índice (drop index) y volver a crearlo (create index), pues los índices no agrupados sólo sereconstruirán una única vez en lugar de dos. La sentencia “Alter index rebuild” pretende sustituir a “DBCC DBREINDEX” ya que desaparecerá en futuras versiones.

Reconstruir un índice es una operación atómica, esto es, si se interrumpe habrá que comenzar de nuevo. Desde SQL Server 2005, y al utilizar “Alter Index”, se puede realizar con conexión (opción ONLINE=ON, manteniendo el acceso de los usuarios) o sin conexión. Se necesita disponer de espacio suficiente y admite paralelismo.

Cuando se reconstruye un índice se recalculan las estadísticas. El motivo por el que SQL Server hace esto es que ya que tiene que leerse todas las páginas del índice aprovecha esa lectura para tomar los datos de estadísticas y actualizarlos. Si se hiciera aparte tendría que hacerse otra pasada adicional por todas las páginas.

A continuación presento una tabla resumen proporcionada por Rubén Garrigós y Eladio Rincón (también de SOLIDQ) en la que muestran las diferencias de comportamiento destacables entre las distintas versiones cuando en una tabla tenemos índices y los reconstruimos. Existen diferencias entre realizar el REBUILD mediante el comando “DBCC REINDEX” y el “ALTER INDEX REBUILD” supongo que por motivos de “retrocompatibilidad”.

La siguiente tabla conjuga los distintos escenarios en las versiones 2000, 2005 y 2008:

Scenario	SQL Server 2008 R2	SQL Server 2008	SQL Server 2005	SQL Server 2000
DBCC REINDEX to rebuild the Clustered Index ix_Col1	Update all Index Statistics	Update all Index Statistics	Update only the Clustered Index	All statistics updated
DBCC REINDEX in the NonClustered Index	Update only the NonClustered Index	Update only the NonClustered Index	Update only the NonClustered Index	Update only the NonClustered Index
DBCC REINDEX without specify the index. That means all index must be updated	All statistics updated	All statistics updated	All statistics updated	All statistics updated
ALTER INDEX REBUILD to update the Clustered Index	Update only the Clustered Index	Update only the Clustered Index	Update only the Clustered Index	Not Apply
ALTER INDEX REBUILD to update the NonClustered Index	Update only the NonClustered Index	Update only the NonClustered Index	Update only the NonClustered Index	Not Apply
ALTER INDEX ALL REBUILD to update the ALL Index	Update all Index Statistics	Update all Index Statistics	Update all Index Statistics	Not Apply

Hay que tener en cuenta esto de cara a planes de mantenimiento para no hacer trabajo doble o no presuponer que algo se hace y luego no.

Por último una observación importante respecto del comando rebuild es que si bien permite regenerar particiones esta operación la hace offline. Según Microsoft el que estéoffline ( ONLINE = OFF) tiene mucha importancia, cito textualmente “se impide el acceso de todos los usuarios a la tabla subyacente durante la operación”. El valor predeterminado es OFF.

Reorganize (Reorganizar)

La reorganización consiste en defragmentar el índice a nivel de hoja, esto es, ordena físicamente las páginas del índice para que coincidan con el orden lógico. SQL Server hace esto sin asignar nuevas páginas, se reorganiza con las páginas existentes.

La reorganización compacta y si resultado de esta compactación quedan algunas vacías se eliminan mejorando el rendimiento ya que para leer todos los datos tenemos menos páginas.

La reorganización se realiza automáticamente en línea. Como en todo hay excepciones, no se puede realizar esta operación sobre índices deshabilitados e índices con la opción“ALLOW_PAGE_LOCKS”.

La sentencia que nos va a permitir reorganizar es “Alter index reorganize”, como en el caso anterior suplanta a “DBCC INDEXDEFRAG”.

En la reorganización no se actualizan las estadísticas por defecto ya que no lee todas las páginas del índice, solo los nodos hoja.

¿Cuando utilizar Rebuild o Reorganize?

Existen distintos límites para utilizar Rebuild o Reorganize pero para ello es necesario cuantificar la fragmentación. Con la siguiente consulta identificamos los campos necesarios para medir la fragmentación:

Medir la fragmentacion

1. SELECT
2.     si.name,
3.     si.index_id,
4.     index_level,
5.     index_type_desc,
6.     avg_fragmentation_in_percent,
7.     avg_page_space_used_in_percent,
8.     fragment_count,
9.     page_count,
10.     record_count
11. FROM sys.dm_db_index_physical_stats (
12.     db_id ('Adventureworks'),
13.     object_id('HumanResources.Employee'),
14.     NULL, NULL, null) v
15. JOIN sys.indexes si
16. ON v.object_id = si.object_id
17. and v.index_id = si.index_id;
18. go

Como se puede ver estamos utilizando la dmv “sys.dm_db_index_physical_stats” que toma los siguientes parámetros de entrada:

• database_id. Id de la base de datos. El tipo es smallint
• object_id. Id de la tabla o vista. El tipo es int
• index_id. Id del índice. El tipo es int
• partition_number. Id de la partición. El tipo es int
• mode. Acepta los siguientes valores DEFAULT, NULL, LIMITED, SAMPLED o DETAILED

Los campos importantes son:

• Avg_fragmentation_in_percent, determina si el índice contiene fragmentación externa
• Avg_page_space_used_in_percent, muestra la fragmentación interna

En base a estos campos podemos establecer los siguientes límites:

• Si la fragmentación externa es mayor que el 10% (Avg_fragmentation_in_percent > 10) se aconseja reorganizar (Alter index Reorganize)
• Si la fragmentación interna es menor que el 75% (Avg_page_space_used_in_percent < 75) se aconseja reorganizar (Alter index Reorganize)

• Si la fragmentación externa es mayor que el 15% (Avg_fragmentation_in_percent > 15) se aconseja reconstruir (Alter index Rebuild)
• Si la fragmentación interna es menor que el 60% (Avg_page_space_used_in_percent < 60) se aconseja reconstruir (Alter index Rebuild)

Conclusiones

• Existen distintos tipos de fragmentación. Las que afectan a SQL Server son lafragmentación lógica y la fragmentación de extensión. Otra clasificación es lafragmentación externa e interna
• Para los índices en entornos con una gran actividad a nivel de inserciones se aconseja fijar un porcentaje de “fill factor” para evitar los “page splits” y con ello la fragmentación
• La manera de eliminar la fragmentación es mediante las sentencias “Alter index Rebuild” o “Alter index Reorganize”
• Dependiendo de la versión de SQL Server con la que trabajemos el comportamiento ante la reconstrucción es distinto y nos condicionará el mantenimiento de los índices
• Para niveles bajos de fragmentación es aconsejable la “reorganización” y cuando estos aumentan la “reconstrucción”

SQL Server

http://jboca.blogspot.com.es/2012/10/fragmentacion-y-desfragmentacion-de.html

Fragmentación y desfragmentación de índices

Una de las tareas más comunes y necesarias durante el proceso de optimización y mantenimiento de las bases de datos es la desfragmentación de los índices, es así mismo quizá la tarea más olvidada por los administradores de bases de datos.

Los índices altamente fragmentados pueden afectar de manera negativa el rendimiento del motor de bases de datos e incluso causar que su aplicación no responda de la manera adecuada. 

Fragmentación: Proceso mediante el cual el motor de base de datos debido a las constantes tareas de Insert, Update y Delete, a medida que estas instrucciones se van ejecutando dentro de nuestra base de datos, la misma sufre un proceso de dispersión de los datos, más conocida como fragmentación. La fragmentación ocurre cuando los índices tienen páginas que se encuentran ordenadas de forma lógica, y basándose en la llave estos no coinciden con el orden físico dentro del archivo de datos.

La fragmentación se puede solucionar mediante 2 opciones, reorganizar y/o reconstruir los índices, para los índices particionados esta tarea se puede ejecutar tanto en el índice completo como en la partición del mismo.

Reconstrucción del índice (Rebuild): Este proceso elimina y crea nuevamente el índice, remueve la fragmentación y recupera espacio en disco compactando las páginas basándose en la configuración del fill factor o en el parámetro de la instrucción.

Reorganización del índice (Reorganize): Este proceso requiere menos recursos del sistema y realiza la desfragmentación al nivel de la hoja de la página, reorganizando a nivel físico las hojas para que coincidan con el orden lógico de las mismas, la reorganización también compacta las páginas de los índices, esta se da basándose en la configuración del fill factor.

Detección de la fragmentación de los indices

Lo primero es determinar que método de desfragmentación usar, para esta tarea se puede utilizar la función sys.dm_db_index_physical_stats, esta nos devuelve la fragmentación de un índice, de los índices en una tabla, de los índices en una base de datos  o de todos los índices en todas las bases de datos, de igual manera para los índices particionados, esta función nos devuelve el estado de cada una de las particiones asociadas al índice.

Columna	Descripción
avg_fragmentation_in_percent	Porcentaje de fragmentación lógica
fragment_count	Cantidad de fragmentos
avg_fragment_size_in_pages	Numero promedio de páginas en un fragmento de un índice.

  

Tenga en cuenta las siguientes recomendaciones para determinar si debe reorganizar o reconstruir su índice. 

Porcentaje de fragmentación	Instrucción a ejecutar
Entre 5% y 30%	ALTER INDEX REORGANIZE
Mayor al 30%	ALTER INDEX REBUILD

Consulta para determinar el porcentaje de fragmentación (En toda la base de datos)

WITH INDICES (BD, INDICETIPO, FRAGMENTACION, INDICE, TABLA)

AS (

SELECT DBS.NAME BASEDEDATOS, PS.INDEX_TYPE_DESC, PS.AVG_FRAGMENTATION_IN_PERCENT,

IND.NAME INDICE, TAB.NAME TABLA

FROM

SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(), NULL, NULL, NULL, NULL) PS

INNER JOIN SYS.DATABASES DBS

ON PS.DATABASE_ID = DBS.DATABASE_ID

INNER JOIN SYS.INDEXES IND

ON PS.OBJECT_ID = IND.OBJECT_ID

INNER JOIN SYS.TABLES TAB

ON TAB.OBJECT_ID = IND.OBJECT_ID

WHERE IND.NAME IS NOT NULL AND PS.INDEX_ID = IND.INDEX_ID

AND PS.AVG_FRAGMENTATION_IN_PERCENT > 0)

SELECT DISTINCT 

      CASE

      WHEN FRAGMENTACION > 5 AND FRAGMENTACION <= 30 THEN 'ALTER INDEX ' + INDICE + ' ON ' + TABLA + ' REORGANIZE'     

      WHEN FRAGMENTACION > 30 THEN 'ALTER INDEX ' + INDICE + ' ON ' + TABLA + ' REBUILD'

      END QUERY, FRAGMENTACION, BD, INDICE, TABLA

FROM (SELECT FRAGMENTACION, INDICE, TABLA, BD FROM INDICES

      WHERE FRAGMENTACION > 5) A

ORDER BY FRAGMENTACION DESC

Los índices pueden ser reconstruidos en línea o fuera de línea, la reorganización siempre se da en línea, para mantener niveles de disponibilidad similares a la de los índices reorganizados, la reconstrucción debe darse en línea y mediante la instrucción.

ALTER INDEX REBUILD WITH (ONLINE = ON)

Como especificar el fill factor en un índice

La opción de fill factor permite el afinamiento del rendimiento y almacenamiento de los índices, cuando un índice es creado o reconstruido, el fill factor determina el porcentaje de espacio a nivel de la hoja de cada página que será llenada con datos; reservando el espacio restante en cada página como espacio libre y disponible para futuro crecimiento.

Ejemplo

Si tenemos un índice con un fill factor de 80, esto significa que el 20% de la página será reservado para el momento en que se agreguen datos que deban ser guardados en ese espacio.

Lleno

Lleno

Lleno

Lleno

Lleno

Lleno

Lleno

Lleno

Libre

Libre

El fill factor es un número que va de 1 a 100, a nivel de servidor el valor predeterminado es 0, esto significa que las paginas serán llenadas en su totalidad – El valor 0 y 100 significan lo mismo para el fill factor –

Consideraciones para temas de rendimiento

Page splits (Contador: \SQLServer:Access Methods\Page Splits/sec)

Elegir correctamente el fill factor para los índices puede reducir notablemente los page splits, aprovisionando suficiente espacio para la expansión de los índices a medida que más datos sean agregados a la tabla; cuando una fila es agregada a una página de índice que se encuentra llena, el motor mueve aproximadamente la mitad de las filas a una nueva página con el fin de abrirle espacio a la nueva fila; el proceso de reorganización sobre las paginas es conocido como page split, este proceso abre espacio para nuevas filas, pero puede tomar tiempo además de ser costosa a nivel de recursos de máquina, además, puede causar fragmentación, lo que aumenta las operaciones de I/O.

Cuando ocurren operaciones de tipo page split de forma frecuente, se debe considerar la reconstrucción del índice (ALTER INDEX REBUILD) utilizando un valor diferente de fill factor para redistribuir los datos; para más información vea el articulo fragmentación y desfragmentación de índices.

Tener en cuenta para determinar el fill factor

Un valor para el fill factor diferente al 100%, es decir distinto de 0 y de 100, puede ser positivo para el rendimiento de la base de datos siempre y cuando la información que se va agregando a la tabla se distribuya sobre la misma; sin embargo, si la información que se va insertando a la tabla siempre va al final de la misma, los espacios vacíos nunca serán llenados ni aprovechados, por ejemplo, si estamos agregando información con una columna de tipo IDENTITY y que esta corresponde a la llave de la tabla, está siempre será incremental y las filas del índice serán agregadas al final del índice.

Si las filas actuales serán actualizadas con datos que aumenten el tamaño de las filas, es recomendable utilizar un fill factor menor a 100, estos bytes extra en cada página ayudaran a minimizar los page splits causados por el crecimiento del tamaño de fila.

SQL Server

http://dbasqlserver.wordpress.com/2012/03/09/optimizacion-de-indices-sql-server/

Optimización de Indices SQL SERVER

¿Que significa Reindexar?

¿Que es mejor Reindexar o Defragmentar?

¿Que diferencia existe entre Defragmentar y Reindexar?

Una de los topicos mas importantes a la hora de administrar una base de datos es implementar indices y hacerlo de manera adecuada, y despues de esto mantenerlos con el mejor rendimiento posible,  precisamente en este tema veremos las consideraciones que se tiene que tomar a la hora de realizar esta tarea, incluyendo consideraciones para tablas sin indices(heap) y tablas con indices agrupados (clustered index), de que manera comprobar la fragmentación (DBCC SHOWCONTIG y sys.dm_db:index_physical_stats),  ademas de las opciones que se tienen para corregir la fragmentacion, es decir :

DROP INDEX Y CREATE INDEX (Eliminar y crear de nuevo los indices)

DBCC REINDEX (Reconstruir el indice)

CREATE INDEX WITH DROP_EXISTING (Borrar indice actual y crear uno nuevo)

ALTER INDEX REBUILD (Modificar indices existente  reconstrullendo)

DBCC INDEXDEFRAG (desfragmentar los indices)

ALTER INDEX REORGANIZE  (Modificar indices existente  Reorganizando)

En el mundo productivo evitar el problema de la fragmentación es un factor de exito,  regularmente en ambientes de producción las bases de datos son muy grandes y tienen una actividad muy demandante que ocasionan la fragmentación del los indices y por ende afectan el rendimiento.

En que nos afecta que nuestros indices esten demasiado fragmentados?   La respuesta es tiempo,  las ejecuciones empezarian a ralentizarse por que harian entradas y salidas innecesarias, los bloqueos durarian mas tiempo, las consultas tardarian mas en regresar resultados y esto se convierte en un efecto domino donde una defecto lleva a otro y tendriamos un gran problema en las manos.

Lo primero es detectar donde esta el problema y como lo podemos solucionar, es decir vamos a detectar cuales de nuestros indices requieren de mantenimiento,  pero antes de esto vamos a entender un punto el mantenimiento de desfragmentacion y reindexado se realizara a los indices no a las tablas o vistas, que por que ???  sigue leyendo,,

• Las tablas que no tienen indices utilizan una estructura llamada heap o monton , es decir sus datos no estan ordenados logicamente, recordemos que uno de los beneficios de implementar indices en nuestras tablas es que a partir de este nuestros datos se ordenaran con base en este y si por el contrario tenemos una tabla sin indices los datos son amontonados como van llegando sin orden podrian entrar en cualquier parta de la estructura, esto lo puedes comprobar creando una tabla sin indices e insertar datos ahora realiza una consulta inmediata e inserta nuevos datos vuelve a consultar y los datos estan en desorden total, el objetivo de la desfragmetanción es ordenar los datos y poner todos juntos pero como podria SQL realizar esto si no tiene un criterio para ordenar ????
• Las tablas con indices agrupado o clustered index, es diferente ahora ya tenemos un criterio de ordenación entonces podras desfragmentar o reindexar sin ningun problema, y no solo eso si no que por buenas practicas siempre es recomendable tener al menos un indice clustered en cada tabla.

Bien ahora lo que sigue es detectar el problema vamos a comprobar la fragmentación, estos lo podemos realizar con el comando DBCC SHOWCONTIG

como se muestra en el siguiente ejemplo:

USE DB

GO

DBCC SHOWCONTIG(‘NOMBRE_TABLA’)

GO

si ejecutas el comando sin parametros podras obtener la fragmentación de todas las tablas de la base de datos en la que estes posicionado.

Si requieres entrar mas a fondo con este comando puedes visitar el post anterior:http://dbasqlserver.wordpress.com/2012/03/08/como-determinar-la-densidad-de-un-indice/

Por otra parte el comando DBCC SHOWCONTIG a pesar de que se puede usar en las versiones actuales 2000, 2005 y 2008 ,  este sera descontinuado en versiones posteriores, por lo que a partir de 2005 la recomendación para determinar la fragmentación de un indice es utilizar la función del sistema: sys.dm_db_index_physical_stats

A continuacion un ejemplo de la sintaxis:

USE DB

GO

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N’TBL_HIS_FACTURACION’), NULL, NULL, ‘LIMITED’)

GO

Por ultimo una vez que hemos encontrado indices fragmentados, tenemos diferentes alternativas para poder dar el tratamiento adecuado dependiendo del caso:

• Eliminar y volver a crear el índice agrupado de la tabla (DROP INDEX y CREATE INDEX). Si la tabla contiene un índice agrupado, esta es la alternativa más lenta, pero la que ofrece un mejor rendimiento. Al eliminar y volver a crear el índice agrupado, se volverán a reconstruir también los índices no agrupados de la tabla. El inconveniente, es que el índice estará sin conexión(no estará disponible) y la operación es atómica (si se interrumpe, será necesario empezar de nuevo desde el principio). Además, se necesita disponer de espacio suficiente para mantener una copia de los datos de la tabla.

• Recontruir el índice (DBCC DBREINDEX, CREATE INDEX WITH DROP_EXISTING o ALTER INDEX REBUILD). En el caso de los índices agrupados (clustered index) es mucho más óptimo que eliminar el índice (DROP INDEX) y volver a crearlo (CREATE INDEX), pues los índices no agrupados sólo se reconstruirán una única vez en vez de dos veces (una con el DROP INDEX y la otra con el CREATE INDEX). Para esta alternativa, se necesita utilizar el comando DBCC DBREINDEX, o el comando CREATE INDEX WITH DROP_EXISTING, o el comando ALTER INDEX REBUILD. El comando DBCC DBREINDEX desaparecerá en futuras versiones, por lo que la recomendación es utilizar ALTER INDEX REBUILD. El comando ALTER INDEX es nuevo en SQL Server 2005, ya que en ediciones anteriores sólo existían los comandos CREATE INDEX y DROP INDEX, pero no existía ALTER INDEX. Es una operación atómica. Hasta SQL Server 2000, implica que el índice estará sin conexión (no estará disponible). Desde SQL Server 2005, y al utilizar ALTER INDEX, se puede realizar con conexión (opciónONLINE=ON, manteniendo el acceso de los usuarios) o sin conexión. Tambiénnecesita disponer de espacio suficiente. Esta operación admite paralelismo.

• Defragmentar el índice (DBCC INDEXDEFRAG o ALTER INDEX REORGANIZE). SQL Server 2000 introdujo el comando DBCC INDEXDEFRAG, que permitedefragmentar sólo los nodos hoja de los índices y no soporta paralelismo, pero tiene la ventaja de ser una operación en línea (los usuarios pueden seguir accediendo a nuestras tablas e índices). Permite la realización de operaciones como BACKUP LOG mientras está en marcha (potencialmente beneficioso para tablas muy grandes), y además, en caso de verse interrumpido, puede continuarse sin perderse el trabajo realizado. Dependiendo de la fragmentación, DBCC INDEXDEFRAG puede ser considerablemente más rápido (o también más lento). El comando DBCC INDEXDEFRAG desaparecerá en futuras versiones, por lo que la recomendación es utilizar ALTER INDEX REORGANIZE.

También es importante tener en cuenta que con DBCC INDEXDEFRAG no se actualizan las estadísticas, mientras que con DBCC DBREINDEX si se actualizan estadísticas.

En cualquier caso, aunque DBCC DBREINDEX, CREATE INDEX WITH DROP_EXISTING y ALTER INDEX REBUILD puedan parecer iguales, del mismo modo que DBCC INDEXDEFRAG o ALTER INDEX REORGANIZE también puedan parecer iguales, en caso de necesidad revisar la documentación, pues sí existen pequeñas diferencias (ej: poder cambiar o no la definición del índice, etc.).

Es de suma importancia mantener el rendimiento de nuestras bases de datos y realizar la inspección de nuestros indices fragmentados es una clave importantisima,  la recomendación es realizar esto con algun trabajo programado o un plan de mantenimiento que se ejecute en horarios de poca afluencia tal vez en las noches, la periodicidad la podras determinar dependiendo del nivel de operacion en tus bases de datos, por ejemplo yo tengo un plan que ejecuta los domingos en las noches.

Espero que les halla sido de utilidad, dejen sus comentarios.

fuentes:http://www.guillesql.es/Articulos/SQLServerFAQ_Reindexar_Defragmentar_indices.aspx

EJEMPLOS

REORGANIZE
==========
SI el valor de avg_fragmentation_in_percent>5 y avg_fragmentation_in_percent<30 nbsp="" span="">

ALTER INDEX IX_index_name ON Table_Name REORGANIZE;


REBUILD
=======
SI el valor de avg_fragmentation_in_percent>30

ALTER INDEX IX_index_name ON Table_Name rebuild;


REBUILD ALL INDEXES
===================
ALTER INDEX ALL ON Production.Product 
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, 
STATISTICS_NORECOMPUTE = ON);