major myths about microsoft sql server n.
Skip this Video
Loading SlideShow in 5 Seconds..
Major Myths About Microsoft SQL Server PowerPoint Presentation
Download Presentation
Major Myths About Microsoft SQL Server

Loading in 2 Seconds...

play fullscreen
1 / 11

Major Myths About Microsoft SQL Server - PowerPoint PPT Presentation

Download Presentation
Major Myths About Microsoft SQL Server
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. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Major Myths About Microsoft SQL Server Kevin Kline, Technical Strategy Manager Twitter @kekline Blog at

  2. Your Speaker: Kevin Kline

  3. Free Stuff! Free posters, guides, and other goodies. March 2010 July 2010 Over 8 hours of free DVD Training available. Follow-on details at

  4. LET’S PLAY A GAME! • Five true/false lightning rounds with five questions each • Winner of each round gets their choice of an ebook of:

  5. Round 1: General Myths • The top-end SKU for MSSQL is DataCenter Edition. • The TIMESTAMP data type tracks the exact time a record is written or modified. • The maximum rowsize in MSSQL is approximately 8060. • The Transact-SQL programming language was originally developed by Sybase. • TOAD is for Oracle.

  6. Round 2: Transaction & Lock Myths • Locks are escalated from rows to pages, and then from pages to tables. • SELECT * FROM foo always returns data in clustered index order. • TRUNCATE, SELECT…INTO, and BULK INSERT are not logged. • Checkpoint writes all of the dirty pages from committed transactions. • Quest’s Log Reader (in TOAD and LiteSpeed) can reverse specific transactions still in the transaction log.

  7. Round 3: Backup & Recovery Myths • Simple recovery mode is fast because it doesn’t log. • Backup database will shrink the transaction log. • It’s easy to restore a single table if you have a single filegroup. • Restoring a database removes index fragmentation and updates statistics. • Quest’s LiteSpeed for SQL Server can recover specific records from a backup file.

  8. Round 4: Query Myths • Estimated query plans and actual query plans are basically the same. • Subqueries are executed in the order they appear inside of a parent query. • The time needed to execute a query directly correlates to the cost of the query plan. • Clustered index scans are really fast. • Kevin Kline once fell off the stage while presenting on translating Oracle PL/SQL code into MSSQL Transact-SQL code.

  9. Round 5: Index Myths • Primary keys are also clustered indexes. • Fill factor of 0 is the same as 100. • Rebuilding a clustered index rebuilds the non-clustered indexes too. • Primary keys, unique constraints, and identities all ensure non-duplicate values. • Dynamic Management Views (DMVs) have been in SQL Server since version 2000.

  10. Round 6: Performance Myths • Tempdb should have one data file per processor core. • Setting compatibility mode to a pre-MSSQL2005 version prevents the use of on-line indexing and other performance improvements of later versions. • Resource governor is great for controlling IO, memory, and CPU. • Parallelization is always good for code performance. • It really is true that “the SAN will fix everything!”

  11. Quest Software Resources for SQL Server SQLServerPedia – SQL Server knowledge base, straight from the experts: HTTP:// SQL Server Community – Online discussion forums, customization library, and beta programs. HTTP:// SQL Server Backstage – All things SQL Server at Quest including our Pain of the Week Webcasts. HTTP://