TEN THINGS

Y O U DO T

H A T MA K E S

Q L SE R V E R C

R Y

JEN MCCOWNMicrosoft SQL Server MVP recipientSQL Server developer and DBA MidnightDBA.com Jen@MidnightDBA.com MidnightDBA.com/Jen Twitter.com/MidnightDBA webshow.MidnightDBA.com Fridays 11pm CST

1. Design2. Ugly code3. ! Performance4. !

Documentation

5. ! Testing

6. ! Understanding

7. Disk8. Profiler9. Indexing10.Backups and

maintenance

OVERVIEW: THE 10 THINGS

THING 1: DESIGN Unreadable names Bad datatypes Duplicate columns Multipurpose columns No relational integrity And much, much more!

THING 2: UGLY CODEUgly code is scary

THING 3: PERFORMANCECursorsNested viewsLock hintsJust not bothering with it…

THING 4: ! DOCUMENTATIONNobody likes making documentation:• It's a pain• It gets out of date• Nobody reads it• “Don't need it!“• "I can do it when the project is done!“• “I'll document myself out of a job!"

THING 5: ! TESTING 1. Code on dev or on a sandbox. Don't make changes in

production! 2. Run your code...on dev. 3. Script out your deployment, and test that. 4. Have a rollback script, and test THAT. 5. Especially if you don't have a decent QA, watch the changes

in production. Remember, "it always runs fast on the dev box".

THING 6: ! UNDERSTANDINGKnow before you GO

THING 7: DISKDisk Partition AlignmentRAID levelsMuch more

THING 8: PROFILERDon’t run SQL Profiler on productionSQL Profiler can bring down production

Instead: Server Side Trace: The What, Why, and Howbit.ly/19x2wBD

THING 9: ! INDEXINGHuh. Apparently this is an “index” clipart.

Indexes are good! Use them…But there's a balance.Don't index every column of a table individually!

THING 10: ! BACKUPS / MAINTENANCEYou need backupsYou need maintenanceYou need alerting

Suggested: SQL Server Maintenance Solution http://ola.hallengren.com

OTHER RESOURCESAll session materials available at MidnightDBA.com/Jen

Contact me: Jen@MidnightDBA.comTwitter.com/MidnightDBA Other resources: •MidnightDBA.com • SQLServerSamples.codeplex.com (AdventureWorks)• SSMSToolsPack.com•Ola.Hallengren.com