{"id":2259,"date":"2020-11-01T23:54:08","date_gmt":"2020-11-01T23:54:08","guid":{"rendered":"http:\/\/manualsqlserver.com\/?p=2259"},"modified":"2020-11-01T23:57:11","modified_gmt":"2020-11-01T23:57:11","slug":"reconstruir-indices-en-sql-server","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=2259","title":{"rendered":"Reconstruir \u00edndices en SQL Server"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"220\" height=\"260\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_ReconstruirIndices__D.png\" alt=\"\" class=\"wp-image-2260\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Reconstruir los \u00edndices en SQL Server<\/h2>\n\n\n\n<p class=\"has-medium-font-size\"><strong>\u00cdndices en SQL Server<\/strong><\/p>\n\n\n\n<p>Un \u00edndice de SQL Server es una estructura en disco o en memoria asociada con una tabla o vista que acelera la recuperaci\u00f3n de filas de la tabla o vista. Un \u00edndice contiene claves generadas a partir de una o varias columnas de la tabla o la vista.<br>El dise\u00f1o eficaz de los \u00edndices tiene gran importancia para conseguir un buen rendimiento de una base de datos y una aplicaci\u00f3n, es por ese motivo que no solamente es muy \u00fatil crearlos sino cada cierto tiempo reconstruirlos, este tiempo para a depender de la cantidad de informaci\u00f3n que cambia en el indice en la tabla o vista.<\/p>\n\n\n\n<!--more-->\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p style=\"font-size:18px\"><strong>Para mayor informaci\u00f3n ver<\/strong><br><a href=\"https:\/\/manualsqlserver.com\/?p=158\" target=\"_blank\" rel=\"noreferrer noopener\">\u00cdndices en SQL Server<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=557\" target=\"_blank\" rel=\"noreferrer noopener\">\u00cdndices particionados en SQL Server<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=1673\" target=\"_blank\" rel=\"noreferrer noopener\">\u00cdndices en vistas<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=161\" target=\"_blank\" rel=\"noreferrer noopener\">Modificaci\u00f3n de \u00cdndices<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=343\" target=\"_blank\" rel=\"noreferrer noopener\">Cursores en SQL Server<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=522\" target=\"_blank\" rel=\"noreferrer noopener\">Variables tipo tabla<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=314\" target=\"_blank\" rel=\"noreferrer noopener\">Procedimientos almacenados<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=113\" target=\"_blank\" rel=\"noreferrer noopener\">Esquemas en SQL Server<\/a><\/p>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicio<\/strong><\/p>\n\n\n\n<p>Para este ejercicio se va a usar la base de datos Northwind, se va a crear un cursor que guarde los datos de los \u00edndices para crear la instrucci\u00f3n de reconstrucci\u00f3n del \u00edndice, todo ser\u00e1 en un procedimiento almacenado para reconstruir los \u00edndices.<\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Los metadatos<\/strong><\/p>\n\n\n\n<p>SQL Server guarda los datos de los objetos de la base de datos en lo que se conoce como metadatos, estos metadatos se almacenan en vista del sistema, para este ejemplo vamos a usar las siguientes vistas del sistema:<br><strong>sys.tables<\/strong> que tiene la informaci\u00f3n de las tablas.<br><strong>sys.schemas<\/strong> que tiene la informaci\u00f3n de los esquemas<br><strong>sys.indexes<\/strong> que tiene la informaci\u00f3n de los \u00edndices<br><strong>sys.dm_db_index_physical_stats<\/strong> que tiene la informaci\u00f3n de las estad\u00edsticas de los \u00edndices.<\/p>\n\n\n\n<p><strong>Usando la base de datos Northwind<\/strong><br>Abrir la base de datos<\/p>\n\n\n\n<p>use northwind<br>go<\/p>\n\n\n\n<p><strong>El procedimiento almacenado recibe de manera opcional dos par\u00e1metros, uno que indica que los indices se van a reconstruir y el otro con el valos de fragmentaci\u00f3n m\u00e1xima del \u00edndice. Se crea la variable tipo tabla llamada @ListaIndices y se incluyen los datos necesarios para crear la instrucci\u00f3n para reconstruir el \u00edndice.<\/strong><\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Instrucci\u00f3n Alter index<\/strong><\/p>\n\n\n\n<p>Permite modificar el \u00edndice.<br>Para el ejemplo ser\u00e1:<br>Alter Index NombreDelIndice ON [Esquema].[Tabla] Rebuild WITH (ONLINE = OFF)<\/p>\n\n\n\n<p style=\"font-size:24px\"><strong>Procedimiento almacenado para reconstruir los \u00edndices.<\/strong><\/p>\n\n\n\n<p>El procedimiento almacenado es como sigue:<\/p>\n\n\n\n<p>CREATE or alter PROCEDURE dbo.ReconstruirIndices<br>@MostrarRecontruir nvarchar(10) = &#8216;Mostrar&#8217;,<br>@FragmentacionMaxima decimal(20, 2) = 20.0<br>AS<br>BEGIN<br>&#8212; Declarar variables<br>SET NOCOUNT ON<br>DECLARE<br>@Esquema nvarchar(128), @Tabla nvarchar(128),<br>@Indice nvarchar(128), @InstruccionAlterIndex nvarchar(4000),<br>@IdBaseDatos int, @IdEsquema int,<br>@IdTabla int, @lndexId int;<br>&#8212; Variable tipo tabla para los indices<br>DECLARE @ListaIndices TABLE<br>(<br>BaseDatos nvarchar(128) NOT NULL,<br>IdBaseDatos int NOT NULL,<br>Esquema nvarchar(128) NOT NULL,<br>IdEsquema int NOT NULL,<br>NombreTabla nvarchar(128) NOT NULL,<br>IdTabla int NOT NULL,<br>NombreIndice nvarchar(128),<br>IdIndice int NOT NULL,<br>Fragmentacion decimal(20, 2),<br>PRIMARY KEY (IdBaseDatos, IdEsquema, IdTabla, IdIndice) )<br>&#8212; Llenar la lista de Indices, la informaci\u00f3n se obtiene de las vista de sistema &#8212; Tables, Schemas, Indexes y dm_db_index_physical_stats <\/p>\n\n\n\n<p class=\"has-text-align-left\">INSERT INTO @ListaIndices         <br>            (BaseDatos, IdBaseDatos, Esquema,  IdEsquema, <br>             NombreTabla, IdTabla,  NombreIndice, IdIndice, Fragmentacion)     <br>SELECT          db_name(), db_id(), S.Name, S.schema_id,          T.Name, T.object_id, I.Name, I.index_id,          MAX(F.avg_fragmentation_in_percent)          FROM sys.tables As T          Join sys.schemas As S  ON T.schema_id = S.schema_id          Join sys.indexes As I ON T.object_id = I.object_id          Join sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL)  As F              ON F.object_id = T.object_id AND              F.index_id = I.index_id WHERE F.database_id = db_id()         Group by S.Name, S.schema_id, T.Name, t.object_id, I.Name, I.index_id;<\/p>\n\n\n\n<p>&#8212; Si recibe el par\u00e1metro comprobar si es Rebuild para reconstruir.<br>IF @MostrarRecontruir = &#8216;Rebuild&#8217;<br>BEGIN<br>&#8212; Cursor para crear las instrucciones SQL<br>DECLARE CursorInstruccionesIndices Cursor Fast_Forward<br>FOR SELECT Esquema, NombreTabla, NombreIndice<br>FROM @ListaIndices<br>WHERE Fragmentacion &gt; @FragmentacionMaxima<br>Order by Fragmentacion DESC, NombreTabla Asc, NombreIndice Asc<br>OPEN CursorInstruccionesIndices<br>Fetch Next From CursorInstruccionesIndices INTO @Esquema, @Tabla, @Indice<br>&#8212; Recorrer el cursor<br>While (@@FETCH_STATUS = 0)<br>BEGIN<br>&#8212; Crear la instrucci\u00f3n Alter index<br>SET @InstruccionAlterIndex = &#8216;Alter Index &#8216; + QUOTENAME(RTRIM(@Indice)) +<br>&#8216; ON &#8216; + QUOTENAME(RTRIM(@Esquema)) + &#8216;.&#8217; + QUOTENAME(RTRIM(@Tabla)) +<br>&#8216; Rebuild WITH (ONLINE = OFF) &#8216;<br>PRINT @InstruccionAlterIndex<br>&#8212; Para mostrar el en panel de mensajes la instrucci\u00f3n.<br>&#8212; Ejecutar la instrucci\u00f3n<br>Execute (@InstruccionAlterIndex)<br>Fetch Next From CursorInstruccionesIndices INTO @Esquema, @Tabla, @Indice<br>END<br>CLOSE CursorInstruccionesIndices<br>DEALLOCATE CursorInstruccionesIndices<br>END<\/p>\n\n\n\n<p>&#8212; Mostrar resultados<br>SELECT<br>L.BaseDatos As &#8216;Base de datos&#8217; , L.Esquema As &#8216;Esquema&#8217;,<br>L.NombreTabla As &#8216;Tabla&#8217;, L.NombreIndice As &#8216;\u00cdndice&#8217;,<br>L.Fragmentacion As &#8216;Fragmentaci\u00f3n Inicial&#8217;,<br>Max(Cast(F.avg_fragmentation_in_percent As Decimal(20, 2))) As &#8216;Fragmentaci\u00f3n Final&#8217;<br>FROM @ListaIndices As L<br>join sys.dm_db_index_physical_stats(@IdBaseDatos, NULL, NULL, NULL, NULL) As F<br>ON L.IdBaseDatos = F.database_id AND L.IdTabla = F.object_id<br>AND L.IdIndice= F.index_id<br>Group by L.BaseDatos, L.Esquema, L.NombreTabla, L.NombreIndice, L.Fragmentacion<br>Order by L.Fragmentacion Desc, L.NombreTabla Asc, L.NombreIndice Asc<br>Return<br>End<br>go<\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejecutar el SP para reconstruir los \u00edndices de la base de datos abierta.<\/strong><\/p>\n\n\n\n<p>Execute ReconstruirIndices Rebuild<br>go<br>El resultado se muestra en la siguiente imagen.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_ReconstruirIndices_01-1024x692.png\" alt=\"\" class=\"wp-image-2261\" width=\"770\" height=\"520\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_ReconstruirIndices_01-1024x692.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_ReconstruirIndices_01-300x203.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_ReconstruirIndices_01-768x519.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_ReconstruirIndices_01-795x537.png 795w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_ReconstruirIndices_01.png 1216w\" sizes=\"auto, (max-width: 770px) 100vw, 770px\" \/><\/figure>\n\n\n\n<p>Las instrucciones creadas mostradas con el comando Print se muestran en la siguiente imagen.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_ReconstruirIndices_02-1024x381.png\" alt=\"\" class=\"wp-image-2262\" width=\"832\" height=\"309\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_ReconstruirIndices_02-1024x381.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_ReconstruirIndices_02-300x112.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_ReconstruirIndices_02-768x285.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_ReconstruirIndices_02-795x296.png 795w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_ReconstruirIndices_02.png 1216w\" sizes=\"auto, (max-width: 832px) 100vw, 832px\" \/><\/figure>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Importante<br><\/strong>Se recomienda crear un plan de mantenimiento e incluir una tarea para ejecutar el SP que reconstruya los \u00edndices.<br><a href=\"https:\/\/manualsqlserver.com\/?p=1512\" target=\"_blank\" rel=\"noreferrer noopener\">Ver Planes de mantenimiento.<\/a><\/p>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n","protected":false},"excerpt":{"rendered":"<p>Reconstruir los \u00edndices en SQL Server \u00cdndices en SQL Server Un \u00edndice de SQL Server es una estructura en disco o en memoria asociada con una tabla o vista que acelera la recuperaci\u00f3n de filas de la tabla o vista. Un \u00edndice contiene claves generadas a partir de una o varias columnas de la tabla &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=2259\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":2260,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[232,7,233,8],"tags":[260,261,262],"class_list":["post-2259","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cursoressql","category-indices","category-sp","category-programacion","tag-alter-index","tag-rebuild","tag-reconstruir-indices","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2259","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2259"}],"version-history":[{"count":3,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2259\/revisions"}],"predecessor-version":[{"id":2267,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2259\/revisions\/2267"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/2260"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2259"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2259"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2259"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}