1 / 16

SQL Database Optimization Justin Kovacich

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 .

tilden
Download Presentation

SQL Database Optimization Justin Kovacich

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Database OptimizationJustin Kovacich

  2. Overview • Sizing the SQL Server Hardware • Table Design • Index Design • Query Design

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

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

  5. Table Design - Normalization • 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

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

  7. Poor Design • Wide tables • Predetermined maximums

  8. A Better, Relational Design

  9. Joining Tables Together • Physically Separated Tables • Logically Combined Data

  10. SQL Join SELECTb.CustomerID, n.LoanNumber, n.TotalCommitment, c.collat_id FROM customer b, loan n, collateral c WHEREb.date = '02-28-2007' and b.date = n.date and b.date = c.date and b.customerid = n.customerid and b.customerid = c.customerid and n.loannumber = c.loannumber order by b.customerid, n.loannumber, n.totalcommitment

  11. 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

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

  13. 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

  14. Questions?

  15. References • http://www.sql-server-performance.com • http://www.webopedia.com/term/r/raid.html - Information on RAID hard drives • http://technet.microsoft.com/en-us/magazine/cc165445.aspx • - Information on query optimization • http://www.edbarlow.com/document/optimize.html - Information on normalization

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

More Related