ten things you do that make sql server cry. jen mccown microsoft sql server mvp recipient sql server...
TRANSCRIPT
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 [email protected] 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:
[email protected]/MidnightDBA Other resources:
•MidnightDBA.com • SQLServerSamples.codeplex.com (AdventureWorks)• SSMSToolsPack.com•Ola.Hallengren.com