As posted many times before, continous professional development through training and education is absolutely core of our IT consulting business, The Full Circle (www.thefullcircle.com).
Today is a good day for that mantra as we are attending an excellent training seminar by SQL MVP’s and founders of www.SQLskills.com – SQL Server MasterClass
My scribbles from the day.. and the formal agenda, speaker bio’s, etc. at the end
Bridging the gap between Development & Production..
DBA’s & Developers not communicating and the reluctant/involentary DBA..
The dilema of development, change control, production scheduling, and customer demands for new features
4 features that break an Enterprise to Standard restore – Partitioning, data compression, …, …
How Design Affects Production
Design considerations such as capacity planning up front to scale databases correctly, try to avoid autogrowth! & Log file fragmentation (LVF’s etc.)
Use of correct data types e.g. Use of bigint for small numbers, datetime when you only really need date, etc. – why use 16-bytes when you can use 4!
Disk maybe cheap, but memory overhead across a system is not.
Indexing – design them, not ad-hoc!
Resouce Governer – limit ad-hoc SQL query users
Naming conventions – obvious in any system (or should be!), makes long term management easier
Design choices affecting maintenance e.g. Fixed vs. Variable length impacting the ability to perform online index rebuilds or not!
Even some some MS apps break this such as SharePoint!
Nested Transactions – avoid! ..they are evil! (nested begins, only free resource at end of first transaction)
Instant File Initialisation (IFI) – restores create the file the full with zeros unless IFI is enabled.
Parameterization – trying to get your plan in cache, optimize at dB level.
Phyical dB layout – as dB’s become larger partioning into multiple filegroups is necessary – what’s a large dB? over 100GB you need to consider, TB absolutely!
Measuring your restores to check against your SLA’s – restores take longer than backup, ballpark 25% more, you can break your maintenance window (& SLA’s) by underestimating the restore time.
Primary Key Choice – obvious but not always followed.
Indexing Strategies – difficult!
Database Maintenance – lack of causes problems!
Real Testing!!! – developers need access to similar environment to live otherwise
Myth Buster – top 10 myths debunked, taken from Paul’s Myth a day posts in April at SQLskills.com/blogs/Paul/Misconceptions
e.g. Failover Clustering is best HA for SQL..? It depends!
Read more at “Proven SQL Server Architectures for High Availability and Disaster Recovery – http://download.microsoft.com/download/5/B/D/5BD13FFA-5E34-4AE1-9AA0-C6E6951B8FC8/SQL%20Server%202008%20R2%20High%20Availability%20Architecture%20White%20Paper.docx”
Tempdb or transaction log should be x% of the size of the largest dB – it depends! If you had a 10TB dB would you have 25% allocated for the log? e.g. 2.5TB wasted on logs.. NO!
use of single user mode, and admin connections.. the :m. and :a. startup switches
DR handbook should be written by the most experienced member of the team, but tested by the most junior – who’d going to be holding the pager when the system goes bang at 02:00 on a Sunday…? likely not the architect!
GUIDs: Use, Abuse, and How To Move Forward
Dilema of GUIDs everywere, great for uniqueness, not so great if used for non-clustered indexes and used as a primary key (due to it’s uniqueness) – back to using the correct data types, why use a 64-byte key when a 4-byte key will suffice – for a 10,000 row table this can mean an internal system index of 400Mb, a 64-byte key will be nearer 5GB – just for the system!
Massive performance and storage gains to be had from correct use of GUIDs – see Kimberly’s blog post on this for more info at http://www.sqlskills.com/BLOGS/KIMBERLY/post/GUIDs-as-PRIMARY-KEYs-andor-the-clustering-key.aspx
The Top-Ten List…
10) Data File Management – you can have up to 32768 files per database, and a minimum of two being the MDF & LDF, but just because you can doesn’t mean you should! However, careful placement of physical files on different storage types can add huge value to your SQL environment..
9) Transaction Log file management – only one log file is necessary, try and isolate from the data files to avoid disk head contention, use appropriate RAID (not RAID 5 as has high overheads for write & logs are write intensive)
8) Tempdb – tempdb is a special case as it is very susceptible to contention issues because only 1 per instance and 1 tempdb file per processor core, you can now create multiple files to spread the contention. KB328551 maybe suitable in your environment.
Guideline for number of tempdb files, .25-.5 the number of cores.
7) Indexing Best Practice – dont’t go crazy with indexes, as with data files, just because you can.. doesn’t mean you should! if anything try to reduce your indexes! Good workload analysis to determine characteristics and query priority as over-indexing can be worse than under indexing. Poorly maintained indexes can be more trouble in the long term – be sure to automate index maintenance. A narrow index has very few issues, a wide one has the potential for many more!
6) Statistics
5) Index Fragmentation – Fragmentation Defined.. two broad types: internal (wasted space on data/indexes aka physical defragmentation) & logical (..)
Main causes.. GUID as high-order key, updates to variable length columns, badly configured FILLFACTOR, wide schemas that only fit a small number of rows per page
Keys to success.. how to read the data
How to correct.. create index with drop_existing, rebuild the index, defrag the index
Most folks rebuild or reorganise but their are tradeoffs.. how much disk space have you got? 😉
4)
3) I/O errors – 823/824 errors have already been retried 4 times before you find out that the I/O subsystem has transient problems. monitor for error 825 – read errors
2) DBCC CHECKDB – see the blog post http://sqlskills.com/BLOGS/PAUL/category/CHECKDB-From-Every-Angle.aspx
How often should you run CHECKDB – at least once per week – yikes!, how long will it take? – it depends! but if it starts to take longer it normally means a problem.
1) Recovering Using Backups – best way to avoid data loss but not necessarily the best way to avoid downtime so consider other mechanisms to full backups such as log shipping, mirroring, etc. Recommend at least Full & Log backups but frequently e.g. full weekly, logs as often as every 5 minutes… however Differentials are better – redundancy in the backups
Q&A – I asked about placement of tempdb in a failover cluster and if there is a downside to putting it on non-cluster shared storage e.g. a local SSD drive or RAMdisk – no, this is fine as long as the same drive letter is available on each node as the tempdb is flushed and recreated everytime SQL starts anyway & got yet another geek polo shirt – my wife will love that… 😉
more info at.. www.SQLskills.com
www.SQLskills.com/webcasts.asp – over 50hrs of free online training resources
Formal Event notes, background, agenda, speaker bio’s, etc.
www.regonline.co.uk/kimtrippsql
This special one-day event will focus on some of the top issues companies face when implementing and maintaining a SQL Server-based solution. Very often there is no dedicated DBA and so the IT managers struggle to keep the data tier performing well and the data available. This can be especially troublesome when the development team is unaware of how application design choices affect database performance.
SESSIONS SNAPSHOT
During the day Kimberly and Paul will:
Debunk many of the ingrained misconceptions around SQL Server’s behaviour
Show you disaster recovery techniques critical to preserving your company’s life-blood – the data
Explain how a common application design pattern can wreak havoc in the database
Walk through the top-10 points to follow around operations and maintenance for a wellperforming and available data tier
SESSIONS ABSTRACTS
KEYNOTE: Bridging the Gap Between Development and Production
Applications are commonly developed with little regard for how design choices will affect performance in production. This is often because developers don’t realize the implications of their design on how SQL Server will be able to handle a high workload (e.g. blocking, fragmentation) and/or because there’s no full-time trained DBA that can recognize production problems and help educate developers. The keynote sets the stage for the rest of the day. Discussing some of the issues that can arise, explaining how some can be avoided and highlighting some of the features in SQL 2008 that can help developers and DBAs make better use of SQL Server, and troubleshoot when things go wrong.
SESSION ONE: SQL Server Mythbusters
It’s amazing how many myths and misconceptions have sprung up and persisted over the years about SQL Server – after many years helping people out on forums, newsgroups, and customer engagements, Paul and Kimberly have heard it all. Are there really non-logged operations? Can interrupting shrinks or rebuilds cause corruption?
Can you override the server’s MAXDOP setting? Will the server always do a table-scan to get a row count? Many myths lead to poor design choices and inappropriate maintenance practices so these are just a few of many, many myths that Paul and Kimberly will debunk in this fast-paced session on how SQL Server operates and should be managed and maintained.
SESSION TWO: Database Recovery Techniques Demo-Fest
Even if a company has a disaster recovery strategy in place, they need to practice to make sure that the plan will work when a disaster does strike. In this fast-paced demo session Paul and Kimberly will repeatedly do nasty things to databases and then show how they are recovered – demonstrating many techniques that can be used in production for disaster recovery. Not for the faint-hearted!
SESSION THREE: GUIDs: Use, Abuse, and How To Move Forward
Since the addition of the GUID (Microsoft’s implementation of the UUID), my life as a consultant and “tuner” has been busy. I’ve seen databases designed with GUID keys run fairly well with small workloads but completely fall over and fail because they just cannot scale. And, I know why GUIDs are chosen – it simplifies the handling of parent/child rows in your batches so you can reduce round-trips or avoid dealing with identity values. And, yes, sometimes it’s even for distributed databases and/or security that GUIDs are chosen. I’m not entirely against ever using a GUID but overusing and abusing GUIDs just has to be stopped! Please, please, please let me give you better solutions and explanations on how to deal with your parent/child rows, round-trips and clustering keys!
SESSION 4: Essential Database Maintenance
In this session, Paul and Kimberly will run you through their top-ten database maintenance recommendations, with a lot of tips and tricks along the way. These are distilled from almost 30 years combined experience working with SQL Server customers and are geared towards making your databases more performant, more available, and more easily managed (to save you time!). Everything in this session will be practical and applicable to a wide variety of
databases. Topics covered include: backups, shrinks, fragmentation, statistics, and much more! Focus will be on 2005 but we’ll explain some of the key differences for 2000 and 2008 as well.
SPEAKER BIOGRAPHIES
Paul and Kimberly are a husband-and-wife team who own and run SQLskills.com, a world-renowned SQL Server consulting and training company.
They are both SQL Server MVPs and Microsoft Regional Directors, with over 30 years of combined experience on SQL Server. Paul worked on the SQL Server team for nine years in development and management roles, writing many of the DBCC commands, and ultimately with responsibility for core Storage Engine for SQL Server 2008. Paul writes extensively on his blog
(SQLskills.com/blogs/Paul) and for TechNet Magazine, for which he is also a Contributing
Editor. Kimberly worked on the SQL Server team in the early 1990s as a tester and writer before leaving to found SQLskills and embrace her passion for teaching and consulting. Kimberly has been a staple at worldwide conferences since she first presented at TechEd in 1996, and she blogs at SQLskills.com/blogs/Kimberly. They have written Microsoft whitepapers and books for SQL Server 2000, 2005 and 2008, and are regular, top-rated
presenters worldwide on database maintenance, high availability, disaster recovery, performance tuning, and SQL Server internals. Together they teach the SQL MCM certification and throughout Microsoft.
In their spare time, they like to find frogfish in remote corners of the world.
SPEAKER TESTIMONIALS
“To call them good trainers is an epic understatement. They know how to deliver
technical material in ways that illustrate it well. I had to stop Paul at one point and
ask him how long it took to build a particular slide because the animations were so
good at conveying a hard-to-describe process.”
“These are not beginner presenters, and they put an extreme amount of
preparation and attention to detail into everything that they do. Completely,
utterly professional.”
“When it comes to the instructors themselves, Kimberly and Paul simply have no
equal. Not only are they both ultimate authorities, but they have endless
enthusiasm about the material, and spot on delivery. If either ever got tired they
never showed it, even after going all day and all week. We witnessed countless
demos over the course of the week, some extremely involved, multi-step
processes, and I can’t recall one that didn’t go the way it was supposed to.”
“You might think that with this extreme level of skill comes extreme levels of
egotism and lack of patience. Nothing could be further from the truth. … They
simply know how to teach, and are approachable, humble, and patient.”
“The experience Paul and Kimberly have had with real live customers yields a lot
more information and things to watch out for than you’d ever get from
documentation alone.”
“Kimberly, I just wanted to send you an email to let you know how awesome you
are! I have applied some of your indexing strategies to our website’s homegrown
CMS and we are experiencing a significant performance increase. WOW….amazing
tips delivered in an exciting way! Thanks again”
Kimberley L. Tripp
Paul S.Randal
