¡Buenas a todos de nuevo! En el artículo de hoy vamos a ver un poco más acerca de nuestros queridos índices. Éstos son responsables de la velocidad en las consultas SELECT, pero causantes (en algunos casos) de cuellos de botella en los INSERT, DELETE y UPDATE. Descubre cómo identificarlos.
Preparando el entorno
Empezaremos creando la siguiente tabla, la cual, usaremos para realizar las pruebas:
CREATE TABLE [dbo].[Posts]( [Id] [int] IDENTITY(1,1) NOT NULL, [AcceptedAnswerId] [int] NULL, [AnswerCount] [int] NULL, [Body] [nvarchar](max) NOT NULL, [ClosedDate] [datetime] NULL, [CommentCount] [int] NULL, [CommunityOwnedDate] [datetime] NULL, [CreationDate] [datetime] NOT NULL, [FavoriteCount] [int] NULL, [LastActivityDate] [datetime] NOT NULL, [LastEditDate] [datetime] NULL, [LastEditorDisplayName] [nvarchar](40) NULL, [LastEditorUserId] [int] NULL, [OwnerUserId] [int] NULL, [ParentId] [int] NULL, [PostTypeId] [int] NOT NULL, [Score] [int] NOT NULL, [Tags] [nvarchar](150) NULL, [Title] [nvarchar](250) NULL, [ViewCount] [int] NOT NULL, CONSTRAINT [PK_Posts__Id] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
SQL Server creará un índice cluster por la PK ordenando así la tabla.
Ahora, si realizamos una consulta sobre la tabla, vemos que toma como opción principal el índice pues la tabla está ordenada y es la manera más rápida de encontrar los datos.
¿Qué pasaría si mientras hay una transacción abierta de tipo UPDATE realizamos la una consulta SELECT de la tabla?
Ejecutaremos la siguiente consulta SELECT mientras se ejecuta una consulta UPDATE sin terminar:
select top 100 [Title],[CommentCount],[Score] from [dbo].[Posts] where Score > 500
La consulta SELECT quedará bloqueada hasta que la sentencia UPDATE termine. SQL Server esperará pacientemente hasta que termine, eternamente si es necesario.
Como la query de la consulta SELECT utiliza el índice de la PK tiene que esperar a que se confirme la transacción para que lo libere y así leer los registro. Una vez detectado el problema. ¿Qué podemos hacer nosotros para evitar esta situación?
Creando índices no cluster
La solución es crear un índice non-clustered sobre los datos que vamos a consultar en la query porque aunque esté bloqueado el índice clúster, podremos acceder a los datos del índice creado.
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20191203-154043] ON [dbo].[Posts] ( [Score] ASC, [CommentCount] ASC, [Title] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
Como podemos ver, aunque la transacción sigue con la tabla bloqueada, podemos seguir consultando los datos de otros índices.
Puedes ver más detalles sobre la creación de índices en la página oficial de Microsoft.
Si quieres que evaluemos tu entorno, no dudes en contactar con nosotros para comprobar si tienes este u otros problemas en las bases de datos de tu entorno. No te pierdas estos artículos suscribiéndote a nuestra newsletter. Con solo un email al mes, estarás al día de todas nuestras publicaciones.
¿Aún no conoces Query Performance? Descubre cómo puede ayudarte en tu entorno Oracle. Más información en su página de LinkedIn.
Sígue a GPS en LinkedIn
Esperamos que os haya servido de ayuda. Nos vemos en próximas entradas.
Un saludo,
Equipo de Base de datos.