Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

John Sansom - SQL Server DBA in the UK

John Sansom (Blog | Twitter) is a Microsoft Certified Master (MCM) of SQL Server and publisher of the free SQL community ebook DBA JumpStart, an inspiring collection of advice for Data Professionals, written by 20 SQL Server experts. Awarded the Microsoft Community Contributor(MCC) award, John is a prolific blogger and can be found regularly writing about SQL Server and Professional Development over at www.johnsansom.com.

5 Things Every DBA Should NOT Do

Earlier in the month I shared with you 10 Things That Every DBA Should Do and so it only seems natural to also consider some things that every Database Administrator should NOT be doing right.

I’m going to let you in on a little secret here, I have actually done some of these things myself once upon a time and I’m sure other DBAs have too. It’s important that as a DBA you understand that you will inevitably make a wrong call at some point. It is how you handle it that is truly telling.

In no particular order, here are 5 things that I really really really hope you are NOT doing if you’re a DBA.

1. Shrinking Your Databases

If you’re shrinking your SQL Server databases on a regular basis then you either have a storage shortage to address, poor transaction log management, a dodgy process or a screw loose. Either way you need to get things sorted.

I’m not got to repeat what many others have already said on the matter (Why you should not shrink your data files / Why you want to be restrictive with shrink of database files), just heed my advice that you should really not be doing this unless you have a very good and exceptional reason for doing so.

2. Giving Regular Users sysadmin Rights

Just like you, I too have heard all the excuses. Whether they be from developers or power users that have been at the company since the dawn of time thereby having been granted full blown administrative rights as if it were some sort of right of passage.

The truth that we all know as DBAs of course is that there’s just no need for this level of privileges to be granted. The overwhelming majority of functionality that your users genuinely need in order to go about their work can be provided for without granting sysadmin level rights. The same can even be said for a large proportion of the tasks performed by us DBAs. Take the responsibility for your environment and data assets seriously.

3. Performance Tuning Queries in Production

Stop Sign

"Some things you just shouldn't do"

As tempting as the urge may be, it’s really not a good idea to query tune directly in your production environment. You’re making changes to your code base without actually knowing for sure, the consequences of your actions. Don’t even get me started on a DBA running Ad-hoc queries live on a production server.

So you’ve identified an index that will make a currently poorly performing query run like lightning, great! Now go away and test it properly in your performance tuning environment so you can see how well it plays with the other queries and be confident that there will be no adverse affects.

4. Making Changes Without Testing (a.k.a Winging-it)

Even if you are a T-SQL Ninja, if you’re making changes to production that you have not tested then in my opinion that makes you a fool. You may think I’m being harsh but the experienced folk out there know that I speak the truth. If you are not testing your changes before they go to production then you are putting the data assets that you are ultimately responsible for at unnecessary risk. Doing so goes completely against your primary responsibility as a DBA. Why take the chance?

5. Rebuilding All Indexes Every Time

If  a couple of bricks had come loose and fallen off of the garden wall, would you knock down the entire wall first in order to repair the damage? Of course not! So why do it to your indexes? If a given index only has a small amount of fragmentation say 15% then why not repair the damage (using REORGANIZE) rather than REBUILD the entire index completely from scratch. If your current index maintenance strategy is to bulldoze your entire database on a nightly basis then you are likely generating a lot of wasted effort and unnecessary transaction log activity.

Invest some time in your index maintenance strategy so that you are performing a suitable amount of reorganization or rebuilding, dependant on the needs expressed by the current state of each index.

As with a lot of things in life, there are some great index tuning procedures out there, such as the excellent Index Defrag Script V4.0 and there are some not so great ones. Whether you choose to devise your own index maintenance strategy or to use one that another DBA wrote, it’s important that you make sure you understand how the process works so that you can be confident that you are making informed choices for optimizing your environment.

5.1 Restarting the SQL Server Service to “fix” issues

I was originally going to allow this one to slide from the list but then I thought I would be doing you readers a genuine disservice by ignoring what I consider to be one of the most frustrating habits that I encounter in DBAs. So just to make certain and be absolutely clear here, restarting the SQL Server service in order to “fix” or remedy a problem is NOT an acceptable solution. You are fooling yourself if you think that this is an acceptable practice for a DBA to perform and in the long run you are doing yourself a real disservice. If you break it down, what the person is actually doing here by their action is accepting the fact that they don’t know how to solve the problem. Don’t be a quitter and accept defeat, take the time to investigate and troubleshoot the problem, like the Outstanding DBA that I know you are!

There you have it, 5 things that I don’t think you should be doing as a DBA, some of which I may even have learned about the hard way myself. I will leave you guessing as to which…..

In your experience, what are some of the things that you think Database Administrator’s should not do?

If you enjoyed this post then you'll love Email inbox or favourite RSS Reader.

Comments

Posted by Dukagjin Maloku on 22 February 2011

Thanks for sharing, there are 5 & 1/2, not just 5 things!

Posted by John Sansom on 22 February 2011

I know, I know, it was just too good(or bad) to not be included :-)

Posted by Jason Brimhall on 22 February 2011

Excellent list.

I might add another one - but it is really more of an excuse.  The "that's the way we have always done it" syndrome should be avoided.

Posted by John Sansom on 22 February 2011

Thanks Jason!

Ah yes, good one. You'd be surprised (or maybe not :-) ) just how often that phrase rears it's ugly head, sometimes even disguised controversially as "best practice"....  

I encourage Data Professionals to challenge the perceived norms rather than to just accept them at face value.

Posted by eirikureiriksson on 27 February 2011

This is a fine listing of No No’s.

The underlying fact is that these bad practices often are the results of bad planning, black-boxing and mismanaged server resources. It is sad how many development/testing databases slide into production without the proper sizing, testing and implementation.  

Posted by michaelballard on 27 February 2011

As much as I hate doing it, I have to regularly do #5.

I have an OLEDB driver that doesn't properly 'free' memory buffers allocated for its use.

 I've been in multiple three-way conversations with Microsoft SQL Server Engineering and the OLEDB vendor in which the two engineering groups point fingers at each other and demand source code with which to prove the other is at fault.  Even though there are NDAs in place, neither side has provided their code to the other.

 A nightly ETL process fetches data via a linked server that uses the OLEDB connection.  Over some number of days (depending on source database activity), the ETL process gets slower and slower.  Also, SQL users notice slower login and much slower execution of large queries.  If allowed to go on long enough, the ETL process will eventually fail with a 'provider ran out of memory' error message and SQL users will be unable to connect to the SQL Server instance.

 Microsoft SQL Server Engineering recommended the service restart in order to purge the list of allocated memory buffers.  For some of our biggest customers, we've set up scheduled tasks to do the service restart before and after each night's ETL job.

 If you have a better solution, I'd like to hear about it.

Posted by John Sansom on 27 February 2011

Hi Michael,

Thanks for your comments.

It sounds like you have a bit of tricky situation on your hands there. Being caught in the middle of a vendor stalemate whilst all the while just wanting to get things running smoothly for your users, I can imagine that’s quite frustrating.

For a solution to exist it has to solve the core problem, not just address the symptoms. Based on the details you have shared it would certainly seem that the responsibility for a genuine solution rests in the hands of the vendors.

I think it fair to say that your case is the exception, rather than the norm. As with most things SQL Server guidelines are just that, guidelines and your specific circumstance will dictate what is best for you. With comments of your reluctance to perform sql service restarts I think you know that what you have to work with right now is not ideal but then what other choice do you have. I wonder if other readers have had similar experiences.

It’s a tough one.......and I wish you the best of luck with it.

Posted by kigzivan on 28 February 2011

Thank you very much for sharing such a wonderful experience.

Posted by Jeremy Quaid on 28 February 2011

Hi Michael,

I had a similar problem a few years back in Oracle 9i. We found a workaround by having a Staging database - i.e. the ETL using OLEDB connects to a  Staging DB. The Staging DB could just have difect ODBC views of the Live tables required by the ETL. Stopping and restarting the Staging database is trivial and doesn't affect other users of Live, but frees up the memory.

Jeremy

Posted by Jeremy Quaid on 28 February 2011

FYI - that should be "direct" not "difect"

Posted by Martin Hawley on 28 February 2011

Thanks, good article. So tempted to send your comments on shrinking the database to the person who set up our maintenance plans to shrink all databases every day!

Posted by John Sansom on 28 February 2011

Hi Martin, thanks for your comments! Maybe you should.

Posted by David McCullam on 1 March 2011

Point 3. Performance Tuning Queries in Production assumes you have a "Performance Tuning environment"... I wish !

Our Testing and Development environments are virtualized (and we are lucky to have the resources for those), and so can't be used for real world testing.

Posted by Mr.Phantomblot on 1 March 2011

Avoiding the "Performance Tuning Queries in Production"-step is a bit to handle in some circumstances.

If you have customer with a million dollar SAN and billion dollar real-time equipment spewing events into you SQL Server, you'll have your hands full trying to get funding for a performance testing environment. At least one that will mimic and handle the work load of the production environment.

Posted by kev.eames on 1 March 2011

On another (admittedly non-sqlserver) we transfer the db statistics to test environments to test out performance tweaks. whilst this is not a supported feature within sqlserver, it is possible to copy the live sysindexes table to a dev database and use that to manipulate stats if you're brave (or stupid!!!) enough to allow system table updates. Not easy ir ideal but it can help with the investigation of difficult performance issues.

Posted by John Sansom on 1 March 2011

Hi David, absolutely. Sometimes you have to make do with the best of what you have available to work with.

Thanks for your comments!

Posted by John Sansom on 1 March 2011

Hi Kev,

There is a technique in SQL Server, similar to what you describe, that can be used to create a "Shell" database. It's the same method that the Database Engine Tuning Advisor(DTA) uses to offload testing to another database when run from the command line.

When you script out a database schema, you can also script out the statistics for the database tables. This way when you generate the Estimated Execution Plan against the test database it will provide a plan as if it were based on your actual production database data.

Thanks for your comments!

Posted by Dustin on 1 March 2011

Good article.  Especially the part about granting sysadmin to power users!  But remember, DBAs (plural) does not have an apostrophe ;)

Posted by John Sansom on 1 March 2011

Thanks svtcobra50!

Posted by Charles Kincaid on 1 March 2011

Thanks for this list.

I have had lots issues with customers that use Express and have had the drive filled up because of crap, or no, maintenance.

One other reason NOT to restart instances is that SQL Server keeps track of things like miising and unused indexes.  Those values are discarded on every restart.

Posted by jparker2 on 1 March 2011

I love the restart SQL server one.  

I would have put it at the top.  The way the DB engine caches things you will only make an issue worse with a restart unless there is a problem with the servers memory or disk configuration.

Posted by SAinCA on 1 March 2011

Re #4 - super-bad idea especially if you're using your all-powerful credentials and not testing like a low-privilege user. Oops, that new go-faster SP in the new utility DB isn't available to the lower-classes - how embarrassing, all that red-ink on the user's page!

Thanks for the reminder to a sometimes-should-know-better "fool".

Posted by ludo.van.den.ende on 1 March 2011

Very good.

Ideas for a new article: 5 things your app should not do

1) SELECT * FROM

2) Define all columns with NOT NULL

3) ..

Posted by John Sansom on 1 March 2011

Charles, excellent additional consideration, thanks for sharing it.

Posted by John Sansom on 1 March 2011

jparke2 and SAinCA, thanks for your comments gents!

Posted by John Sansom on 1 March 2011

ludo, that's a great idea for a future post, thanks.

Posted by Kenneth Fisher-475792 on 2 March 2011

Good list.  Although I hate to admit it I find myself doing 3 and 4 far to often.  

I do have a question about #1 though.  We have a regular shrink process on our dev & test environments that runs once a week.  Our logic is that A) We have limited space on these environments and B) Our developers tend to bloat the databases during testing, and it is a fairly random thing which database is being bloated so I can't just say "Well they will just do it again next week so might as well leave the space there."  Given that it is our dev/test environment and the above reasoning do you still feel that regular shrinking is a bad idea in this instance?  If so why?

Thanks

Kenneth

Leave a Comment

Please register or log in to leave a comment.