SQL Server Performance Tuning

Big Data.. is not new, for years enterprises are managing their data warehouses, however its only in recent past that advancement in cloud computing made almost every business consider cost effective means hence switching to this technology. It has also brought to the forefront on how to keep large databases efficient as well available all the time for transaction as well analysis purposes.

Common issues hindering optimal performance of database systems could be any of these viz. not knowing throughput and scale-ability of system, inefficient transactions, retrieving data more than required, too many or few indexes or inaccurate combinations, not separating different types of workloads, faulty and inefficient schema, inefficient disk controllers etc.

This document primarily focuses on best practices to have highly available efficient database systems.

Objective

Implementing lasting solutions for optimal database performance in OLAP & OLTP

Right Database Schema for Strong Foundation

• Always pre-plan and test new schema/tables before going to production as last minute changes could spell disaster and cost a lot
• Create separate servers for OLAP and OLTP with appropriate RAID configurations, preferably RAID 5 for OLAP and RAID 0+1 for OLTP. RAID 0+1 helps in appropriate data striping for parallel processing and mirroring for availability. RAID availabilities as on date RAID 0 (Striping), RAID 1 (Mirroring), RAID 1E (Striped Mirror), RAID 5 (Striping with parity), RAID 5EE (Hot Space), RAID 6 (Striping with dual parity), RAID 10 (Striped RAID 1 sets), RAID 50 (Striped RAID 5 sets), RAID 60 (Striped RAID 6 sets)
• There's a rule to follow "normalize first, de-normalize later", and use indexed views for de-normalization rather than starting with de-nomalized schema. At times, inappropriate schema costs more when DISTINCT is applied to get required query output.
• Declarative referential integrity (D.R.I.) performs much better than triggers hence always define relationships, define indexes on foreign keys, create unique and check constraints in place of triggers, it will enhance performance manifold as data integrity checks are performed prior to operation
• Use smaller data types e.g. use varchar instead of text if your data is going to be less than 8000 characters
• Apply logical data partitioning either horizontal or vertical as required to escape large index/table scans. Generally databases are partitioned on period for maximum efficiency as archived data is not accessed frequently

Correctly formed Queries for Quick Response

• Avoid using * and write only columns needed
• Avoid using following preceding wildcards '%ajay manocha%' and NOT operators instead use 'ajay manocha%' and convert your <>, NOT LIKE into IF EXIST or IF NOT EXISTS to avoid table scan and optimally use B+ tree i.e. indexes
• Avoid using expressions in WHERE clause e.g. WHERE DATEADD(day, 365, BirthDate) = '26/Feb/1977' instead use following to utilize index on date field WHERE BirthDate = DATEADD(day, -365, '26/Feb/1977')
• Use select statements WITH (NOLOCK) and WITH (READUNCOMMITTED) to avoid unnecessary locks and considerably increase performance. Other type of locks are UPDLOCK, TABLOCK
• Use sp_executesql for batch commands / stored procedure to benefit with resuse of execution plan and parameterized queries. In all case avoid SQL injection or user generated queries.
• Avoid cursors instead use looking as cursor cause row scans
• Use temporary tables and table variables
• Use fully qualified object names e.g. select employeeID, employeename from dbo.xcesstechnologies instead of select employeeID, employeename from xcesstechnologies

Right Index for Speed

• Besides indexing tips shared in schema considerations, never index on bits, text, ntext, and image
• Keep clustered key as small as possible, use multiple smaller indexes
• Index column/s with lowest All Density value. Use DBCC show_statistics
• Create index on columns often used in Order By, Group By, Where By clauses

Transactions

• Refer Queries Point 4

Stored Procedures

• Always use SET NOCOUNT ON
• Never prefix "sp_" as its considered system procedures and hence every-time masterdb is scanned for same

Read Execution Plans

• Make sure select statement returns minimum rows required for operation
• SET STATISTICS IO ON and check result for logical reads
• Avoid table scan, if happening create clustered index in table
• Clustered scan is far more efficient than table scan however for large tables clustered index seek should be added for performance
• Avoid non-clustered index scans to reduce unnecessary I/O instead apply non-clustered index seek to return only required number of rows for much better performance (only exception is in case of bookmark (RID, Key) look-ups when large number of rows are returned)
• RID look-ups should be avoided using clustered index, and if required with a covering index
• Key look-ups are faster than RID however for even better performance it should be eliminated using covering index
• Avoid execution plan re-compiles generally caused by change in query structure, re-index etc.

Using XML

• Avoid using OPENXML over large documents or concurrent OPENXML statements as it eats up a SQL buffer space

Miscellaneous

• Use DBCC OPENTRANS to look for long running transactions
• Use least restrictive locks; to check locks acquired by your query use sp_lock and sp_who2
• Separate your data physically from log file as I/O locks cause degradation of DDL with time
• Use fixed size grow for databases
• Maximize available memory using /3gb switch in boot.in
• Timely defrag indexes using DBCC INDEXDEFRAG

Concluding Remarks

This was just an overview of some issues affecting performance of SQL databases and probable workarounds. Please feel free to send your suggestions, feedback, comments to ajay [at] xcesstechnologies [dot] com. Thank you for your time and happy tuning !