1. Cada índice creado en la tabla incrementa el tiempo en que se llevan acabo los insert, updates o deletes, por lo que el número de índices en una tabla no deben de ser muchos. Trata de usar de 4 a 5 índices, si la tabla es de solo lectura entonces el número de índices se puede incrementar.
2. Manteen los índices lo más pequeño que puedas, reduciéndolo se reducen el número de IO para leerlo.
3. Trata de que los índices creados sean sobre campos de tipo enteros y no de carácter.
4. Si se crean índices compuestos el orden en que se creen es muy importante. Trata de colocar primero la columna que sea más selectiva, es decir donde no se repitan mucho los valores y después la menos selectiva. También debes de tomar en consideración la forma en que escribes tus queries.
5. Los índices clustered funcionan mucho mejor que los nonclustered si se necesitan hacer consultas sobre un rango de valores o se necesitan ordenar datos por medio de GROUP BY o ORDER BY.
6. Para tablas que son modificadas con mucha frecuencia (UPDATE, INSERT, DELETE) es recomendable usar un FILLFACTOR diferente de 0 o 100.
7. No utilizar SELECT * en las consultas, siempre se deben de definir las columnas que se quieren extraer, esto reduce el I/O mejorando el performance.
8. Evitar el uso de cursores, es mejor usar loops con sentencias WHILE, es más rápido, pero para poder hacer uso de esto es necesario tener definida en la tabla una llave que identifique al registro (primary key o unique).
Como realizar un loop para leer registros sin necesidad de un cursor
--Usando cursores
declare @bd varchar(50)
DECLARE bdcursor cursor for
select name from master..sysdatabases where name not in ('master','tempdb','msdb')
open bdcursor
fetch bdcursor into @bd
WHILE @@FETCH_STATUS = 0
BEGIN
print 'Base de Datos: ' + @bd
fetch bdcursor into @bd
END
DEALLOCATE bdcursor
--Sin cursores
DECLARE @dbName VARCHAR(50)
SET @dbName = ''
WHILE @dbName IS NOT NULL
BEGIN
SELECT @dbName = MIN( Name )
FROM master..sysdatabases
WHERE name not in ('master','tempdb','msdb') and Name > @dbName
IF @dbName IS NOT NULL
BEGIN
print 'Base de Datos: ' + @dbName
END
END
9. Evitar el uso de tablas temporales ya que generan más IO, considerar primero el uso de queries más avanzados, vistas, como tipo de dato tabla, tablas derivadas.
Tipo de dato tabla
DECLARE @TableVar TABLE
(Cola int PRIMARY KEY,
Colb char(3))
INSERT INTO @TableVar VALUES (1, 'abc')
INSERT INTO @TableVar VALUES (2, 'def')
SELECT * FROM @TableVar
GO
Tabla derivada
SELECT ST.stor_id, ST.stor_name
FROM stores AS ST,
(SELECT stor_id, COUNT(DISTINCT title_id) AS title_count
FROM sales
GROUP BY stor_id
) AS SA
WHERE ST.stor_id = SA.stor_id
AND SA.title_count = (SELECT COUNT(*) FROM titles)
10. Evitar el uso de ‘%’ al inicio de la palabra cuando se busca por medio de un LIKE ya que provoca un index scan, el cual elimina el propósito del índice, en cambio el segundo ejemplo realiza un index scan.
SELECT LocationID FROM Locations WHERE Specialities LIKE '%pples'
SELECT LocationID FROM Locations WHERE Specialities LIKE 'A%s'
Evite en las búsquedas usar operadores del tipo <> y NOT ya que resultan en table scan e index scan.
11. Hacer uso de SHOWPLAN_TEXT o SHOWPLAN_ALL para analizar los queries
12. Utilizar SET NOCOUNT ON en los sp’s
13. No poner a los sps el prefijo sp_, ya que las búsquedas de llevan a cabo primero en master.
14. Tratar de no hacer uso de campos tipo text o ntext para almacenar datos largos, ya que no se pueden realizar updates directamente sobre ellos o hacer inserts. Para esto se debe hacer uso de READTEXT, WRITETEXT y UPDATETEXT. También SQL Server tiene demasiados BUGS para replicar tipos de datos de este tipo. Por lo que si no se tiene piensa guardar un dato mayor a 8K utilice char(8000) o varchar(8000).
15. Tratar de no usar tipos de datos binary o image dentro de la base de datos. En su caso almacenar la ruta de los archivos ya que tiene mucho mejor performance.
16. Siempre en las sentencias de insert colocar las columnas a las que se va a insertar.
CREATE TABLE EuropeanCountries
(
CountryID int PRIMARY KEY,
CountryName varchar(25)
)
Here's an INSERT statement without a column list , that works perfectly:
INSERT INTO EuropeanCountries
VALUES (1, 'Ireland')
Now, let's add a new column to this table:
ALTER TABLE EuropeanCountries
ADD EuroSupport bit
Server: Msg 213, Level 16, State 4, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Esto se evita de la siguiente manera
INSERT INTO EuropeanCountries
(CountryID, CountryName)
VALUES (1, 'England')
17. Es preferible usar constraints para mantener la integridad referencial que los triggers ya que son más rápidos. Solo usare los triggers para auditar, validaciones que no se puedan hacer con constraints.
18. Siempre acceder las tablas en el mismo orden en todos los sp’s y triggers, de esta manera evitamos generar deadlocks asi como también mantener las transacciones lo más pequeñas posibles. También hacer que las aplicaciones reintenten la transacción en caso de generarse un deadlock (error 1205).
19. No hacer llamado de funciones repetitivamente en los sp’s o triggers, en lugar de eso hacer el llamado una sola vez y guardarlo en alguna variable.
20. También toma en cuenta el consumo de CPU que generan tus consultas.