Physical Database Design and Tuning – Oracle or SQL Server

Troubleshooting Database Performance  – 3 Broad Categories

1. Physical Database Design
2. Query Statement Tuning
3. DB Configuration

Physical Database Design

1. INDEXING – Look for fragmentation, If frag > 0% , try rebuild indices.
2. Filegroups (File Placement, Object Placement) – Ldf (log files) and mdf (data files) on separate drives – SQL Server
3. Partitioning – Horizontal or vertical (columnar).
4. Denormilazation

1. RE-INDEXING Notes – Look for fragmentation, If frag > 0% , try rebuild indices. Dbcc indexdefrag (allows db to stay online). dbcc dbreindex (also rebuilds statistics, also works with constraints on indices)
2. FILEGroups Notes – SYSTEM Objects – Primary Filegroup, USER Objects in a separate filegroup,, TRANSACTION Log – Separate Volume – Lessens I/O load. Also, TEXT/IMAGE Data best in a separate filegroup.

Query Statement Tuning

1. Look primarily for full table scans and nasty joins.
2. Find Queriies that have a high execution count (run frequently)  – e.g. Select execution_count, physical_reads, logical_reads FROM SYS..dm_exec_query_stats a, SYS.dm_exec_cached_plans b, SYS.syscacheobjects c
3. Subqueries vs Joins – While both do the same thing, look at the explain plan for efficiency.  Subquery is only better if an aggregate is being calculated and fed back on the fly. JOIN is better when columns from different tables are needed.

Physical Reads vs Logical Reads

Physical Reads should only happen if data is not in the buffer cache (logical read). High Physical Reads is also a symptom

Truncate vs. Shrink – Reduce Log Sizes

Truncate and Shrink Full Transaction Log  (SHRINK is what actually reduces the file size).

Indices – Clustered vs. Non Clustered

• Clustered Seeks are fastest, unless a non-clustered includes two or more columns, in which case non clustered could be faster. INSERTS and UPDATES always faster on a clustered index.
• Clustering – Active Active vs. Active Passive

Primary Index vs. Unique Index

Primary always creates a clustered index. Unique can be non clustered.

Buffer Pool vs. Buffer (Data) Cache Hit Ration.

Overall Process Space – Buffer Pool in SQL Server

Memory used for data cache – Data Cache.  The hit ration here is important (can obtain it from a Windows Perf Counter – Buffer Manager)

DB Configuration – Recovery Model

• Simple – Most recent backup
• Full – Regular Backup upto a point of failure
• Bulk Logged –

Potential Data Type Mismatches (Oracle to SQL Server )

bfile -> Not in SQLserver

nClob –> nText

raw -> Varbinary

Special Data Types – Spatial Data Types

• Need special treatment – User Data Types e,g, UDT
• e.g. Geometry and Geography. STGeomFromText(‘LINESTRING(…..)

Summary

This is meant to be a quick recap of the first places to look for tuning your database performance.

Need an expert to help out with your Database Design or  Strategy? Set up a time with Anuj Varma.