1 / 54

SQL Server Infernals A Beginner’s Guide to SQL Server Worst Practices

This guide explores the worst practices in SQL Server administration, design, development, installation, and more, helping you learn from others' mistakes.

jtrout
Download Presentation

SQL Server Infernals A Beginner’s Guide to SQL Server Worst Practices

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. SQL Server InfernalsA Beginner’s Guide to SQL Server Worst Practices Gianluca Sartori @spaghettidba

  2. Gianluca Sartori Independent SQL Server consultant Data Platform MVP, MCTS, MCITP, MCT Works with SQL Server since version 7 DBA @ Scuderia Ferrari Blog: spaghettidba.com Twitter: @spaghettidba

  3. Agenda • Best practices or Worstpractices? • What can go wrong? • Design • Development • Installation • Administration

  4. Disclaimer: • Noteverythingisblack or white • «Itdepends» is the mostlikelyanswer There are edgecaseswhen some of theseworstpractices are the onlypossiblesolution, or notsuch a bad idea…

  5. Best Practices vs. WorstPractices • Why Best Practices are notenough • Too many • No time • Lack of experience • Notalways clearwhathappensifwedon’tfollowthem • WhyWorstPractices help • They show the mistakes to avoid • We can learn from someoneelse’smistakes

  6. WorstPracticesAreas Administration Design Development Installation Environment Schema design HW validation Recovery Data Types Code OS configuration Security Naming Test SQL installation Capacity Performance Monitoring

  7. SQL Server Infernals • Worst Practices are sins that will put you in the SQL Server hell!! • I will guide you through the circles, as Virgil did with Dante

  8. SQL Server InfernalsBINGO! • Check your sins in the your SQL Server Infernals BINGO card! • Download it from https://git.io/vARkN • Special treats for worst sinners!

  9. CIRCLE 1:Undernormalizers

  10. Schema Design • Notnormalizing the schema • 1NF:A primarykey, atomicattributesonly • 2NF:Everyattributedepends on the wholekey • 3NF:Everyattributedependsonly on the key «The key, the wholekey, nothingbut the key, so help me Codd»

  11. Clues of denormalization • Repeating data  redundancies • Inconsistent data betweentables anomalies • Data separated by «,» • Ex: john@gmail.com, john@business.com • Structured data in «notes» columns • Columns with a numericsuffix • Ex: Zone1, Zone2, Zone3 …

  12. CIRCLE 2:Generalizers

  13. LookupTables One lookuptable for eachattribute

  14. OTLT: One True LookupTable CREATETABLELookupTable( table_namesysname, lookup_codenvarchar(500), lookup_descriptionnvarchar(4000) ) One lookuptable for allattributes

  15. OTLT: One True LookupTable • No Foreign Keys • Generic data types nvarchar(SomeHighNumber) ImplicitConversions, Incorrect Data, Hugememorygrants… • CHECK constraintsmay help to a point… • Locking CHECK( CASE WHENlookup_code='states'ANDlookup_codeLIKE'[A-Z][A-Z]'THEN 1 WHENlookup_code='priorities'ANDlookup_codeLIKE'[0-9]'THEN 1 WHENlookup_code='countries'ANDlookup_codeLIKE'[0-9][0-9][0-9]'THEN 1 WHENlookup_code='status'ANDlookup_codeLIKE'[A-Z][A-Z]'THEN 1 ELSE 0 END= 1 )

  16. EAV: Entity, Attribute, Value

  17. EAV: Entity, Attribute, Value Disadvantages: • Generic data types Ex: varchar(4000) • No Foreign Keys • No CHECK constraints • Multiple accesses to the sametable • One access per attribute Advantages • Dynamic schema: no need to alter the database • Replication, distributedenvironments

  18. EAV: Entity, Attribute, Value • Reporting isinsanely hard. • Writing to the EAV schema is a mess • Workaround: • Reads: PIVOT / Crosstab • Writes: View + INSTEAD OF triggers • Alternatives: • SPARSE columns • XML/JSON • Key-valuestoredatabases • Document-orienteddatabases

  19. CIRCLE 3:ShakyTypers

  20. Data type Worst Practices • Numeric data types for non-numeric data • Storing data as their human-readable representation • Using deprecated data types • Using larger data types “just in case” • Using variable length data types for fixed size data • Storing durations in date/datetime columns • Getting Unicode wrong • Using different data types for the same data in different tables

  21. CIRCLE 4:Anarchic Designers

  22. Chaos Belongs to Hell • No PrimaryKey o surrogate keysonly «identity» isnottheonlypossiblekey! • No Foreign Keys They’re «awkward» • No CHECK constraint The applicationwillguaranteeconsistency… • Wrong data types • Data typeis the 1° constraint on the data • Use of NULL wherenot appropriate • Use of «dummy» data (ex: ‘.’ , 0)

  23. CIRCLE 5:InconsistentBaptists

  24. Damnation by Namification • HungarianNotation(AKA «tibbing») • Insanely short names • Insanely long names • Mixing languages • Using the «sp_» prefix • Using reservedwords or illegalcharacters • Using systemgeneratedconstraintnames • No naming convention or multiple namingconventions Hungary is a nice str_country

  25. CIRCLE 6:Environment Pollutors

  26. Pollutors will be prosecuted • Developing in production • Using the test environment for development • Using a shared database for development • No source control • Developing with sysadmin privileges • Developing on a different version/edition from production(less problematic after 2016 SP1)

  27. CIRCLE 7:OverlyOptimisticTesters

  28. Pessimists are Optimists with Experience • Nottestingall the code Use meaningful data volumes • Testing in production Can alter production data Interferes with production users • Testing in developmentenvironment Usefulatmost for unittests

  29. CIRCLE 8:Indolentdevelopers

  30. Development WorstPractices • No transactions • No errorhandling @@ERROR is a thing of the past! • Wrongisolationlevels NOLOCK = no consistency! • SELECT * • Dynamic SQL with concatenatedparams • Code vulnerable to SQL injection • No abstractionlayer Views, Functions, StoredProcedures It’s all about laziness

  31. CIRCLE 9:Stingy buyers

  32. HW WorstPractices • Using inadequate or unbalanced HW • Reusingdecommissionedservers for new installations • SlowerCPUs (licensecosts the same on fast CPUs) • Less RAM supported • Planning storage with capacity in mind • Choosing the wrong RAID level

  33. CIRCLE 10:Nextnextfinishinstallers

  34. Installation WorstPractices • Installingacceptingall the defaults • Data files on the system drive • MAXDOP = 0 • Max Server Memory = +∞ • Installingunusedcomponents • Installing multiple services on the same machine • Giving up easy wins on I/O • Partitionmisalignment • Using the default allocationunit (4Kb)

  35. CIRCLE 11:Carelesscaretakers

  36. Whatdoes a database need?

  37. Backup and RecoveryWorstPractices • No backup • With FULL recoveryit’s a timebomb • Ignoring RPO and RTO (it’snotyourdecision!) • No test restores • No consistencychecks • DBCCREPAIR_ALLOW_DATA_LOSS as default response to corruption Ourresponsibilityis to performrestores, not backups!

  38. Security WorstPractices • Too manysysadmins • Everyone authenticating as ‘sa’ • Using SQL Authentication • Weakpasswords • 123 • P4$$w0rd • Sameas username • No auditing on sensitive data

  39. Capacity Management WorstPractices • Notchecking disk space • No spaceleft = database halted! • FULL recovery and no log backups? • Relying 100% on autogrowth • Autoshrink • Autoclose • Notpresizingtempdb Different file size = latching (and striping) penalty

  40. MaintenanceWorstPractices • Notmaintainingindexes and statistics • Obsessing over maintainingindexes and statistics • Using catch-allmaintenanceplans

  41. CIRCLE 12:Performance Killers

  42. Performance Tuning More effective Easier to implement

  43. Query OptimizationWorstPractices RBAR: RowBy AgonizingRow • Cursors • WHILE loops • App-side cursors • Scalar and multi-statement functions

  44. Query OptimizationWorstPractices Views on views on views… Might look like a brilliant idea at first (code re-use FTW!) • You can end up losing control • Unneeded multiple accesses to the same tables • Unnecessary JOINs

  45. Neverendingnestedviews… SalesOrderHeader: 2 1 1 2 Customer:

  46. Query OptimizationWorstPractices • One query to rulethemall The optimizerisgood, notperfect «divide et impera» deliversbetter performance • DISTINCT in allqueries … because “who wants stinkin’ duplicates?” • Query HINTsall over the place Especiallyindexhints

  47. IndexingWorstPractices • Acceptingallsuggestions from Tuning Advisor • Duplicate indexes • An index for eachcolumn • Indexes are not for free! • SuboptimalClustered Index • Unique • Small • Unchanging • Everincreasing or decreasing

  48. Server TuningWorstPractices • «Throwing HW» at the problem • A 2x faster machine mightmake RBAR code 2x faster • Using set-based code might make it 500x faster • Using «advanced» optionswithouttesting • NT Fibers (lightweightpooling) • PriorityBoost

  49. Resources Detailed blog posts on spaghettidba.com One post for each circle: https://spaghettidba.com/category/sql-server/sql-server-infernals/

  50. Resources Free Tool: Best Practices Analyzer • Highlightsconfigurationparametersthatdon’tcomply with best practices • Highlightspotentialproblems • Offersrecommendations http://www.microsoft.com/en-us/download/details.aspx?id=15289

More Related