SQL Database Optimization Justin Kovacich Overview Sizing the

SQL Database Optimization Justin Kovacich

Overview Sizing the SQL Server Hardware Table Design Index Design Query Design

Hardware Considerations Available memory on the server Number of CPUs Desired processor utilization % Clustering Average growth % per year

Hardware Continued Data Storage Capacity (Now and Future) Speed of Hard Drives Number of Physical Hard Drives RAID array

Table Design - Normalization is the concept of abstracting data out of a table and having a pointer to that shared value Assume everyone has collateral of a car and a house for their loan. Instead of each record carrying car and house, there is a pointer to those names. If “car” changes to “automobile”, it is a simple matter of changing one column in one row, rather than one column in thousands of rows

Table Design - Continued Bad Solutions ◦ Large rows with many columns Good Solutions ◦ Greater number of tables with fewer columns

Poor Design • Wide tables • Predetermined maximums

A Better, Relational Design

Joining Tables Together • Physically Separated Tables • Logically Combined Data

SQL Join SELECT FROM WHERE and and and order b. Customer. ID, n. Loan. Number, n. Total. Commitment, c. collat_id customer b, loan n, collateral c b. date = '02 -28 -2007' b. date = n. date b. date = c. date b. customerid = n. customerid b. customerid = c. customerid n. loannumber = c. loannumber by b. customerid, n. loannumber, n. totalcommitment

Adding Indexes Speeds access when joining tables Speeds sort operations Slows INSERTs and DELETEs Indexes are easily added and removed without impacting the data itself

Index Considerations How frequently will the fields be referenced: ◦ WHERE clause ◦ ORDER BY clause Are these tables used more frequently for READs than INSERTs?

Query Design Try to join tables on indexed fields Try to join as few tables as possible Try to join on numeric values (Instead of text) The more specific a WHERE clause, the quicker your search will be. Never SELECT * from a table, always specify the desired columns

Questions?

References http: //www. sql-server-performance. com http: //www. webopedia. com/term/r/raid. html - Information on RAID hard drives http: //technet. microsoft. com/enus/magazine/cc 165445. aspx - Information on query optimization http: //www. edbarlow. com/document/optimi ze. html - Information on normalization

Homework What are two benefits of indexes? T/F: Adding indexes changes the data.