10 Things Not To Do With SQL

Download 10 Things Not To Do With SQL

Post on 06-Jan-2016

29 views

Category:

Documents

5 download

DESCRIPTION

10 Things Not To Do With SQL. SQLBits 7. Some things you shouldnt do. Others you can but will be messy. Simon Sabin. Principal Consultant for SQL Know How Training and Development for SQL Server Database design and development, Business Intelligence, Performance tuning and troubleshooting - PowerPoint PPT Presentation

TRANSCRIPT

PowerPoint Presentation10 Things Not To Do With SQLSQLBits 7Some things you shouldnt doOthers you can but will be messySimon SabinPrincipal Consultant for SQL Know HowTraining and Development for SQL ServerDatabase design and development, Business Intelligence, Performance tuning and troubleshootingSQL Server MVP since 2006Email: Simon@SqlKnowHow.comBlog: http://Sqlblogcasts.com/blogs/simonsTwitter: @simon_sabinTruncating transaction LogTruncating transaction logReasons whyNo going backTransaction log provides point in time recoveryWithout transaction logCan only go back to a full/differential backupRunning with full/ bulk logged recoveryYour transaction log will growUnless you back it upIn Full All operations are fully logged (slower)Truncating transaction logActionsDecide on your recoveryIf you want point in time then backup your logMirroring does not backup your logIf your log grows then back it up more frequentlyIf you dont then use simple recoveryreindexingIve read all the inside sql books and once had an email from Karen Beleeney and so I know what Im taking aboutYou probably have extent fragmentation in your clustered and not clustered indexes due to LOB allocations and forward pointersYou need to setup a daily maintenance plan that re-indexes all the tables in your database. That will remove fragmentation and performance will be greatMy queries are suddenly running slow what do I do?Ah you sure ???**##@@~%?What do I do?Re-indexing isnt MagicIt just generates a lot of workRe-Index to solve all problemsRe-indexing causesStatistics to be updatedPlans to be purgedThis means you get a new query planSo it appears it solves your problemsButRe-Index to solve all problemsButIt Just causes a pile of workSlows down mirroring, and log shippingAnd you may have only needed to update statisticsOnly needed when Lots of scanningHelp prevent page splitsRe-Index to solve all problemsActionsConsider if fragmentation is a problemDo your query plans have scans in themAre they for large tablesDoes the data in those tables change so muchIs it that your just getting bad plansWhat my SQLBits 5 session on car crash queriesReduce to weekly/monthlyImplement reorganisationsImplement statisticsShrinking FilesYou got a nice big ladderhttp://commons.wikimedia.org/wiki/File:Leiter_ladder.jpg17Its too big so you make it smallerYour ladder is now too shortShrinking filesA file has grown for a reasonRegular shrinking is wrongThe file will just have to grow againFor transaction log, growing blocks transactionsFor Data files growth can if instant file initialisation is not onshrinking causes fragmentationShrinking filesActionsIf its big, its big for a reason, re-indexing perhaps, a large batch jobUnderstand why and resolve thatEnsure operations are minimally loggedBack up the log more frequentlyPre size files and stick with themScalar User Defined FunctionsScalar functions are EvilPoor PerformanceUser defined functionsInterpreted codePrevent parallelismPerform awfullyEspecially for large queriesUser defined functionsActionsImplement as inline table valued functionsChange to CLR functionsWatch my SQLBits 6 session on high performance functionsIndexing lots of columnsOver indexOver indexIndexing is great for readingIndexes only useful for certain queriesBad for writingEach index can result in 3+ IOs, worst case 20+ IOs10 indexes = 30-200 IOsThats 1 disks worth of IOOver indexingActionsConsider indexes carefullyIf you need lots do you have the correct table designSplit tables to reduce problemDont implement ALL the missing indexes from the DMVThey will be overlapping greatlyDocument what queries use them and how (seek/scan)Not using parametersNot using Parameters - SQL InjectionDont use parametersSQL InjectionDont get plan reuseCompilation every timeSQL cant optimise the planParameters in queriesActionsChange your app to use parametersCant change your appEnable optimise for adhoc workloadsTurn on forced parameterisationMake sure your database is secureWatch my car crash query session from SQLBits 5Using the INSERT UPDATE PATTERNDuplicates write effortThe insert and update patternUpdates are expensiveYou have to build your data setThe find the row to updateIs likely to cause page splitsSQL may have to prevent Halloween effectNo such thing as a minimally logged UPDATEInsert Update patternActionsChange to a INSERT, INSERT patternTurn Trace flag 610 onIf not pre assign fieldsPotentially SELECT INTOConsider your indexes carefullyUse temp tables or table variables & hintsApplying Functions to FILTER ColumnsApply functions to filter columnsApply functions to columns when being used to filterIndex generally cant be usedBad performanceApplies to WHERE clause AND the ON clauseAlso applies to data type conversionsSeen as implicit conversionsApply functions to columns when being used to filterActionsRewrite queries so column is left aloneUse the correct data typeshttp://tinyurl.com/FindImplicitConversions Not indexing foreign key columnsNot Indexing foreign keysNot indexing foreign key columnsOnly applies when you can delete parentEngine has to see is the parent is being usedWill check ALL the child tablesNot indexing foreign key columnsActionsApply indexes where you are deletingIf you have a batch process Consider indexing only during the processReduces write overhead during normal timeDisabling FKS is not the thing to doDuplicatesUse distinct to get rid of duplicateCauses really bad performanceOften reading/processing more than neededPredicates not consideredSimplification preventedIs very CPU intensiveMakes query optimisation hardEspecially when nested in viewsGet your joins rightUse distinct to get rid of duplicateActionsIdentify why you have duplicatesIs your use of DISTINCT validAmend your queryAmend your schemahttp://tinyurl.com/MultiJoinPerfClustered Index on Date ColumnsThe 10 things you shouldnt doTruncating transaction logRe-indexing frequentlyShrinking FilesUser defined functionsOver indexDont use parameterised SQLUse the Insert Update coding patternApply functions to columns in a where clauseNot index foreign keysUse DISTINCT to remove duplicatesCan always do 1 moreClustered index on a date columnClustered index on a date columnIndexing 101 you were taughtClustered index on a range columnWrongsort ofNon-clustered and Clustered indexes are the sameClustered keys are included in ALL NC indexesAdditional Uniqueifier is added if not uniqueIf range column is first key columnOther key columns are pointlessClustered index columnsActionsMake clustered index small uniqueConsider a covering non clustered indexUse included columnsPut equality keys before range keys Examine the index DMVs and look at the equalitySummaryDont take everything you hear as trueSituations change with each releaseKeep up to date from blogs, forums, twitterEngage with user groupsAsk questionsQ&ANowLaterany time afterwardsEmail: Simon@SqlKnowHow.comBlog: http://Sqlblogcasts.com/blogs/simonsTwitter: @simon_sabin