1 / 11

Major Myths About Microsoft SQL Server

Major Myths About Microsoft SQL Server. Kevin Kline, Technical Strategy Manager Twitter @ kekline Blog at http://KevinEKline.com. Your Speaker: Kevin Kline. Free Stuff!. Free posters, guides, and other goodies. http://www.quest.com/backstage/promotion.aspx. March 2010. July 2010.

nira
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. 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. Major Myths About Microsoft SQL Server Kevin Kline, Technical Strategy Manager Twitter @kekline Blog at http://KevinEKline.com

  2. Your Speaker: Kevin Kline

  3. Free Stuff! Free posters, guides, and other goodies. http://www.quest.com/backstage/promotion.aspx March 2010 July 2010 Over 8 hours of free DVD Training available. Follow-on details at http://db-management.com/live

  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://www.SQLServerPedia.com SQL Server Community – Online discussion forums, customization library, and beta programs. HTTP://SQLServer.quest.com SQL Server Backstage – All things SQL Server at Quest including our Pain of the Week Webcasts. HTTP://www.quest.com/BackStage

More Related