SQL: Performance tuning guide

http://www.sql-server-performance.com/tips/all_main.aspx

1. Query optimisation can be achieved using the Query optimiser on most common queries. Will determine best indexes to build.

INDEXES
2. ALL table should have a CLUSTERED INDEX - this is an index on PK which is increasining (only 1 per table).
3. Index should be considered on any column in WHERE, ORDER BY, GROUP BY, TOP and DISTINCT experssions. (may be optmial for one query but not for other).
4. Setting different settings on index can affect performance. Clustered, non clustered, and composite indexes as well as setting FILLFACTOR and PAD_INDEX.
5. Static tables (dont change frequently) set FILL and Pad_index to 100 and can be heavily indexed as update/insert/delete not a problem.
6. Drop indexes not used by the Query optimiser.

INDEXED VIEWS
1. Create indexed view. Must set up the following:
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
2.
CREATE VIEW Vdiscount1 WITH SCHEMABINDING AS
SELECT SUM(UnitPrice*OrderQty) AS SumPrice, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS SumDiscountPrice, COUNT_BIG(*) AS Count, ProductIDFROM Sales.SalesOrderDetail GROUP BY ProductID
GO
CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount1 (ProductID,colb,..)

3. When to use indexed view:
Data marts, data warehouses, decision support, data mining, OLAP applications.
Views that join two or more large tables.
Views that aggregate data.
Repeated patterns of queries.

Triggers
1. 2 triggers(Instead and After).
If likely only a few rolebacks then use instead otherwise after as overhead is less.
2. When ordering first trigger set it to the most likely rollback trigger so doesn't have to rollback all triggers throughout.
3. Run Query anaylizer to see performance of trigger on system.

Check Constraint
1. Faster than trigger for referential integrity

Use SP for client calls
1. As SP reduce network traffic because SQL in SP and not over network.
2. More secure
3. Better logic.

**Turn off nocount
1. Everytime SP is run returns rowcount to client. This is rarely useful
2. Switch off either putting this is each sp:
SET NOCOUNT ON
or switch off:
SP_CONFIGURE 'user options', 512RECONFIGURE

Check running latest version:
SELECT @@Version

Database tuning Advisor
1. Run profiler to pick up all current queries. (SQL:BatchCompleted and the RPC:Completed)
2. Then save trace and point DB tuning advisor to it.