ten things you do that make sql server cry. jen mccown microsoft sql server mvp recipient sql server...

TEN T

HINGS

Y OU

DO

TH

AT

MA

KE

SQ

L SE

RV

ER

CR

Y

JEN MCCOWN

Microsoft SQL Server MVP recipient

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

1. Design

2. Ugly code

3. ! Performance

4. ! Documentation

5. ! Testing

6. ! Understanding

7. Disk

8. Profiler

9. Indexing

10.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 CODE

Ugly code is scary

THING 3: PERFORMANCE

Cursors

Nested views

Lock hints

Just not bothering with it…

THING 4: ! DOCUMENTATION

Nobody 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: ! UNDERSTANDING

Know before you GO

THING 7: DISK

Disk Partition Alignment

RAID levels

Much more

THING 8: PROFILER

Don’t run SQL Profiler on production

SQL Profiler can bring down production

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

THING 9: ! INDEXING

Huh. 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 / MAINTENANCE

You need backups

You need maintenance

You need alerting

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

OTHER RESOURCES

All 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