We’ve been working with Relativity for at least 4 or 5 years now here at Straight Path. Helping clients with their Relativity SQL Server environments. Helping folks get the most performance out of their Relativity SQL Servers. Running our SQL Server Health Checks for many firms. From small firms to a few on the AmLaw 100 to big Relativity PHPs and a couple agencies.
It’s about that time of year again – Relativity Fest! Exciting to be there again this year. We’ll be there as sponsors, at our booth chatting up SQL Server health, Relativity SQL Server performance and whatever you want to chat about. I’ll also be speaking at Fest for the second year this year – this time I’m participating in a session with a couple great Relativity resources in the support/IE area (Jared Lander and Carlo Ramos). We’ve called our session “Very Large Relativity Work-spaces: Performance Takes a Team“.
Before the session, though, I was pondering a question. What do I wish many of the “accidental DBAs” (it’s not a disparaging term! It’s what many DBAs start out as!!) knew about SQL Server? Especially for folks managing Relativity SQL Servers? Well, the hardest part was narrowing the list to just 5.
But here they are. Even if you already have a DBA or a technical team, it’s still good to review from the lit-support angle and make sure you are covering these bases. 5 things that will make your Relativity SQL Server experience a bit better.
SQL Server Corruption is Real and it Happens
I’ll start with the scary one first. The heading says it all. It’s real. And it’s not very spectacular. One of the sad parts of my job as a SQL Server consultant is the phone call that goes something like this, “Hey. I just found out about you from a person who used your company before. I hope you can help. I have this really weird error message about corrupt pages. Not sure where the backups are. Not sure when this started. But this case is heating up and we need the data in there. Can you help?”
Sometimes the answer is “yup” and they dodged a bullet. Sometimes it’s not as great an answer.
Here’s the deal, you can’t necessarily prevent corruption (I mean putting SQL Server on a properly tuned storage subsystem, following well-established SQL Server and storage best practices and being attentive to patching and security and ensuring it all stays healthy helps!), but you can be ready for it. I talk a lot about that in an entire blog post on Corruption Resistant backup strategies. Let me say it this way, every single one of the folks who has had corruption and heard bad answers from me could have had a minor outage and zero data loss if they followed that advice. Are you prepared?
Index Fragmentation Isn’t Your SQL Server Performance Bottleneck
I have to be careful here. This industry loves index rebuilds. “Oh, things are slow? Hang on, let me run my index rebuild job in the middle of the day!” No!!! Please don’t do that. Sometimes it can be a mighty help – but I am willing to place a decent wager that it isn’t actually the index rebuild helping you here. It’s the Statistics Update you get along with it. There’s a bit of a story here – but the quick version – SQL Server has statistics about the way the data is distributed in a table. These statistics tell the part of SQL server that determines the best way to go about doing what you are asking it to do (the optimizer). When data changes, these statistics get stale. A certain number of rows have to change in order for the statistics to automatically trigger a recalculation. But you can often have enough data change that means SQL would have benefited, but you don’t get the auto update. So on a regular basis, you should be updating your statistics, and your index rebuilds tend to include that step for non-index statistics and when you rebuild an index, the statistics get rebuilt.
Indexes are VITAL to SQL Server but on modern storage systems and with the amount of memory you probably have on your machine, a little index fragmentation isn’t the worst thing in the world (now I still recommend running this job but not for emergencies). Answer here? Have a separate statistics update job and run it a bit more frequently than the index rebuild if you have to pick one or the other. Also? If you have random problems during the day – don’t go right to index rebuild. It may be that you haven’t followed other best practices. Maybe some user someplace is running a really bad idea search on a strange set of nested search terms. Maybe someone has been adding fields and changing templates without properly indexing for frequently searched columns.
The Environment Optimization Guide is Your Friend
Seriously! I remember reviewing this and giving some advice about it. I love it. So many vendors basically tell you “Congratulations, you are using SQL Server, good luck!” and leave you alone. Relativity isn’t like those other vendors (their conferences are pretty swell, too, the best of any vendor conference I’ve been to). They actually have realized that a happy SQL Server means a happy customer. A happy customer calls support less and recommends their product more. So they give you all sorts of best practice tips. INCLUDING the Relativity Environment Optimization Guide. I cannot tell you how many SQL Server Health Checks I’ve done where these items weren’t done. And it made a difference. It’s basically a free health check tool for you – go download it for your version and make sure you are following it! This link will take you to one example.
… so Is Regular Maintenance
The environment optimization guide talks about this. Many webinars talk about this. SQL Server is not the Ronco Rotisserie Chicken machine – it’s not meant to be “set it and forget it” – you can’t just click next-next-next-Finish and have a well tuned, well run SQL Server. It doesn’t work that way. Follow the environment Optimization guide. Then get yourself over to Ola Hallengren‘s site, download his SQL Server maintenance solution. Learn about it, and implement maintenance. Keeping statistics updated, keeping indexes rebuilt, recycling logs, cleaning agent history, doing regular backups including log backups – all of this care and feeding will keep your users happy longer and your phones a bit less busy. And it’s all free to do!
SQL Server Really Cares about Templates, Indexes, and Fields!
I get it, you love that Relativity is so easy to customize. I do, too. Think about this, though, when Relativity ships – it ships with tables a certain way, fields a certain way, SQL Server indexes a certain way. When you change a template (and by all means, you should!) when you add fields to a template and then build all your workspaces off of that template, every matter that goes through relativity will now have whatever good or bad things you did to that template database. The most important SQL Server factors here are:
1.) Data Types and Field Lengths – these things matter. Don’t go excessively just because you want to. Think strategically. The bigger a field, the more risk you have of row-overflow data – not the end of the world, but it is a technical term for “may give you some slight hassles later needlessly”. Data types matter as well. For performance and storage space. Don’t go big just in case you want to be a hoarder later. Add the field you need and that makes sense. You can always add later.
2.) When you add a field that is searchable? If you expect users to often search on it, but never add a SQL Server index for that field? You’ve just guaranteed slower queries and a spillover impact that potentially can make other queries and other searches run slower. Work with your DBA team, look at query plans, use a tool that gives you SQL Server query performance and missing index analysis and add indexes which make sense from the start. Then save your template and pat yourself on the back, every new workspace created from the template will have your best practice index applied!
Make the most of your eDiscovery experience. Make your users and lit support teams love you. Follow these simple tips! If you want to know more or get an outside opinion, we’re always happy to help do a SQL Server Health Check. I also just blogged about other ways to get that done if you want to do some of the legwork and prioritization yourself and have the time. Sometimes we’re a high value, inexpensive insurance policy that proves to your clients you have done all the right things. Sometimes we’re a source of tips to figure out what to do next. Every so often, though, our health check could be the thing that keeps your environment from going over the cliff. Frankly, for our list price of $1,797 for up to 3 SQL Server instances, why not? (Oh! and if you are coming to fest, stop by our booth when Mike is not giving his presentations. We’d love to chat, answer any burning SQL Server questions and get to know you. Grab a health check “appointment card” from our booth for a discount on the health check while there, too.)
