July Question: What Do You Think are the Most Common DBA Pain Points?
Question of the Month | July 1, 2011 | 1:36 pmWhile being a DBA is challenging and often rewarding, we often experience a lot of pain points that we wish would go away. So, for this month’s question, “What do you think are the most common DBA pain points?”, please share with us areas in your job where you experience the most pain. If you have any suggestions on how to make the pain go away, then offer them.
Post your responses to the SQL Server Question of the Month in the comments section below (at www.bradmcgehee.com if you are viewing this from a syndicated newsfeed). And don’t forget to enter your e-mail address when you post your response, so I can contact you if you win.
Because there is no right or wrong answer, this month’s winner will be selected randomly from all the entries that are received.
|
|
The prizes this month are an Amazon.com voucher worth US$50.00 and a license for SQL Virtual Restore 2.3 with one year of support and upgrades. SQL Virtual Restore lets you mount your backups as live, fully functional databases – relieving 8 common causes of DBA Pain. Find out how. |
- February Question: If you weren’t a DBA, what would you like to do?
- Winner of the July Question of the Month
- July Question: Tell us your story of how you happened to become a DBA.
- November Question: What is your favorite, and not so favorite hardware to run SQL Server on?
- July Question: If you could pick your manager, what ideal qualities would he or she have?
RSS
Twitter
Linked In
My first pain point:
1st one is the CHECKDB for the VLDBs.
Possible solutions:
(a) PHYSICAL_ONLY of the DBCC CHECKDB
(b) check each table little by little with DBCC CHECKTABLE
2nd one is collect user requirements (for example during a report design)
Possibile solution:
talk a lot with many users and compare their thinking.
3rd one is database/dwh naming convention. Everyone use their own, so in many cases you have to work with many existing naming convention.
There’s no solution, but re-write database objects. That is too difficult in some cases.
and so on…
I think lack of understanding on how indexing affects query performance, or indexing in general. In my place of employment indexes tend to be somewhat of an afterthought. 9 times out of 10, under-performing queries are due to badly designed indexes, or no indexes at all.
We’re a pretty lean and mean shop, so unfortunately I don’t have the time to evaluate each and every query before it is pushed. We try to use the dmv’s to find missing indexes as much as possible. It’s no panacea, but better than the alternative, which is only being aware of an issue when the customer tells you. I don’t think I’m alone in this.
Bender
My biggest challenge is being responsible for so many servers and not having the proper time to do house keeping on them. Checking for long running queries, optimizing indexes, etc. I can hardly keep up with the number of daily requests of new reports, processes, new systems coming in, upgrading instances, etc.
I wouldn’t say this is a common DBA pain point but I work heavily with SQL Server Replication and one of the biggest problems I face is ensuring convergence of data in all nodes in my topologies. Typically if a customer’s requirements call for replication I can assure you that they expect their data to be in sync at all times. This is sometimes easier said than done. There are a variety of reasons for non-convergence to occur such as network related issues, conflicts, improper filters, bugs, and/or poor database design.
If and when non-convergence does occur it’s important to identify the root cause and correct it. After doing this you’ll likely need to manually synchronize your non-converged databases before setting everything back up again. This can be done with the tablediff utility but an even better tool is Red Gate’s SQL Data Compare. This tool has saved my life and has got me up and running quickly after replication disasters. I highly recommend it.
Mine would be the ability to manage multiple remote servers for performance, backups, index and statistic management. Out home grown solution is ok, but does not catch everything.
Getting vendor buy in on how we want to run things. Most vendors are good but when we have a vendor that doesn’t want to use our standard job and their doesn’t back up all databases or do integrity checks on them it’s frustrating.
The biggest problem I face is I stepped into this DBA job where there was no DBA and the developers were all admins on the SQL servers.
I have been having a terrible time trying to step down the permissions they use. They all have the service account password as well so they can just bypass there stripped down accounts and use that to do whatever they want to.
I am close to fixing this by using proxy accounts and mapping out every data integration point and file system access their jobs use.
Trying to create a baseline and then continuing to monitor performance as more databases are added to servers. Also, working through performance and maintenance best practices. I can’t ever seem to get through any list of BP’s before something changes and I find myself starting over.
The corporate culture that how developer thought they should have permission to do their work straight against the production is my major pain point. Management that endorses this sort of “out of controlled development” or thought-to-be-but-not-really Agile development culture is hurting me every single day. It hurts even more when they requested that performance in production is the top priority focus.
Second pain point would be the off-the-shelf system. The change cycle goes forever without an end. I don’t have the full control on query or index tuning. It’s even worse if the support person from the vendor was trying to start an argument with you on technical issue while they don’t have the knowledge in the area.
1. Lack of documentation.
2. Lack of clarity in requirements.
What we’re seeing is an explosion of complexity without documentation, or documentation only few could understand. We’re another lean and mean shop and also an internet startup, which means we’re flying low and fast in the clouds without air traffic control. We’re not looking at backups the way we should be, we’re not pro-active on system performance, but we’re accommodating literally dozens of requests per day from developers and upper management and somehow making everything fit. We’ve have about 1200 processes that kick off daily with thankfully few failures.. but….
Essentially we’re a house of cards… I hope the wind doesn’t kick up.
Being just a DBA and only allowed to do SQL/Stored procedures and needing to scrape tools to automate things from everyone else’s scraps due to not being a developer. For example often only developers are allowed to get Visual Studio (well aside from Business Intelligence stuff) installed. Also even if these things are installed, often only developers are permitted to check in/deploy any type of code. So to automate things I am left with whatever is installed by default e.g. VBScript/Batch Files for automating DBA tasks (or using xp_cmdshell to do stuff like manage files…VERY PAINFUL). Ultimately there is a lot of pain because comare Python/Perl/Ruby to VBScript or even worse batch files… Even C# or VB.NET would be a huge improvement compared to VBScript. Sometimes you luck out and get power shell, but in my case, people run Win 2000/2003 for their servers mostly and do not install power shell by default.
This is a pain point because DBA’s are expected to automate things. But with stone tools!!! Next time I look for a job, I am planning to go for a developer. But if I was going to become a DBA, I would definitely start filtering out employers based on what is available to automate stuff. If they don’t permit the use of C# or a scripting language besides vbscript, cya. In fact I might ask that before even agreeing to an interview.
With most Unix databases this isn’t an issue because most are using Shell/Perl Scripts/something else automatically available as part of unix/linux/etc… Only on windows does some type of scripting support seem to be an after thought.
I would have to say my biggest pain point would have to be those developers who say ” Its MS SQL Server, click and create” and think that they are fully fledged DBA’s until such time as there application runs slow because they have not thought about how they have created thier indexes. Or oops I thought it automatically created my back ups can you recover for me !!! *sigh*
The biggest pain point to being a SQL Server DBA is working with Microsoft products which are the worst software products I have ever worked with. The programmers at Microsoft should be ashamed for producing such junk. I compare Microsoft to McDonalds and Starbucks: they dominate their respective markets but produce inferior and lower quality/value products. The solution is to get rid of all Microsoft software and switch to other platforms. Many companies and DBAs know this to be true but they can not make the financial and time commitments to make it happen or else they are just too embedded with Microsoft software to make it feasible to switch.
I worked in the company where my tasks are System Administrator and DBA as well. These are the tasks in which me and collegues have to work everyday. I think these tasks is not quite new for all accidental DBAs out there. But for me, I’m not an accidental DBA. I was a SQL developer for about 8 years and have this function as DBA for about 6 years now. I’m quite new in the company, I found out that there are lots of developer here doesn’t know how to script SQL in an optimized way. These are the things that I did to avoid this kind of problem:
* Implement a document for standards and best practices in using MS SQL.
* We’ll check their codes if it is within our standards before implementing it to our production servers.
But still there are some developers that are hard-headed. They think that it works normally when deploying it to production.
This is the most painful things as DBA.
- Too many people with administrator rights
- Ad hoc indexing without a preliminary analyze
- Too fast growing databases
- Limited maintenance windows
1) Time – so many databases, so little time.
2) Control – the business controls the box, and suprise databases ‘appear’ like magic on the enterprise cluster without warning. The developers should hold up signs, “Have copy of SQL Management Studio, will create databases for food.” This seems pretty common when companies don’t have a philosophy or discipline around code/application management. It’s an uphill battle, but if it all ran smoothly, I’d be unemployed.
3) Documentation – we don’t need no stinking documentation! If we could get all the admins and developers to agree on a collective documentation bucket…and convince them the exercise of is worth effort, hundreds of hours of investigation and frustration would be saved.
4) Data Scope/Data Retention – “Disk is cheap!” seems to be the post USB-flash-drive-tipping-point mantra of the last few years. Execs see cheap one-off disks in a retail computer store flyer over the weekend and translate this to a thought of procuring and ‘plugging-in’ these devices at will to run the enterprise. This paradigm is immediately followed by “If you can store it, they will come.” Then when it crashes, folks wonder why the restore takes so long. Scoping the data required for projects and assigning a solid retention/archive plan is a much more useful process on the front-end. Coming in after the fact is like appearing in an episode of Data Hoarders.
Not getting to be a DBA often enough. I’m sure many of you are in the same boat, because by default DBAs often have elevated permissions across many machines, hence many extra duties creep in. My group provides production support for a number of custom developed systems (read: we fill in the gaps that invariably exist), we are responsible for deploying software updates to both staging and production environments, we maintain database and web server environments for development, staging and production, we develop and maintain a BI tool, etc. etc. etc. We like to say that DBA means “Does Basically Anything”, and it often feels that way.
What we’d like to be spending more time on is the proactive tasks that end up saving so much time and money. Things like creating performance baselines and doing regular performance monitoring against them. Or looking for those rogue queries, finding missing (or unused) indexes, auditing security & database/server settings. And all those other things that we know a good DBA does. We think we’re pretty good, we just don’t get to be “Real DBAs” enough because of our other responsibilities.
Biggest pain point is consultants who contradict my recommendations.
Guys, if page life expectancy is very low, stolen pages isn’t particularly high and you’ve got 4GB of RAM looking after a 256GB database you haven’t got enough memory. Capiche?
No amount of opinion, no matter how expensive, should override a fact.
2nd biggest pain point is CTOs who believe the consultants.
Our biggest pain point is working with third-party vendors.
From the ones who insist their products have to run under ‘sa’ to our main application vendor who grants ‘public’ access to everything, our DBA group seems to spend a lot of our time securing databases.
Our biggest pain points :
-facing sql server performance issue when site viewers increases..
-handle high volume of data in single table .
-There is no option to store nvarchar(4500) size data. We can use only nvarchar(4000) or nvarchar(max). I dont want to use max size for 4500.
DBA’s operating area is mid-way between operating system layer (including hardware, storage) and application developement layer. DBA is often incorrectly targetted even if problem area is outside the boundary of SQL Server (Network, I/O latencies at storage layer, Application design issues etc).
DBA should have right set of performance monitoring tools to quickly diagnose the problem and prove that problem area is outside the scope of SQL Server.
The biggest pain point pattern I have seen is good communication to varying levels such as end users, devlopers, managers, executives, and customers. From a DBA perspective your are expected to keep everything running, with ample space, and fast too. These are always taken for granted. We play the mechanic, only the car is always on and never allowed off the road. The DBAs that have great communication skills and touch base with their devlopers and customers regularly will have better success at being proactive rather then being force to react to an issue. For example, if you know team X is doing an upgrade and loading a large amount of data, it makes life a lot easier to prepare and deal with them before hand. Let’s face it, project managers do not do a good job at identifying all the risks and the database is mostly takend for granted. I need this to go faster, more space please, upgrade my OS please, patch my application, does that complete your order? Would you like to super size that?
Soft skills matter and the better they are the easier the DBA can get back to being effective. If we train the customers, developers,and managers and do a good job it will become a habit to talk to the technical staff before jumping into a project, or trying something risky with out proper advice from the DBA.
Trying to shoehorn bad software into our support model… like software that requires SA to run “as designed”, even though it should not. (SharePoint, TFS)
Another thing is schema change management. I use Redgate tools, but we still haven’t figured out a good way that isn’t just a huge burden shift either toward developers, or the DBAs or some expensive automated build system to create developer DB’s from source control.
Lastly, it’s application developers who design a database, not to store data, but as a driver of application functionality. (i.e. the app is designed foremost and the data model is an afterthought that is generated while code is being written) . this yeilds bad models without foreign keys, good indexing, good normalization, a bunch of NULLable columns, etc. And the tables sometimes contain redundant data that already exists in other tables, just in a less consumable fashion. Then the app needs to keep the two tables in sync…. sigh… just plain bad.
For me a constant problem I’ve experienced is eliminating Database Debris when taking ownership of a new system. It’s similar but i believe worse than the issue Brad raised in his Poll about dropping unused Indexes. At least we know we can re-add a dropped index if we realise it was a mistake.
This pain point occurs when a previous DBA has created a ‘backup’ copy of a table before performing some kind of change.
An example being …
USE AdventureWorks
GO
SELECT * INTO Sales.CurrencyRate_bkp FROM Sales.CurrencyRate
The above assumes the DBA has given some semi-meaningful name to the table and not ..
SELECT * INTO Sales.CurrencyRate_xx FROM Sales.CurrencyRate
How many times have you seen this?
In no time this table is being backed-up each night. In due course the data gets copied into UAT, Test, Dev environments and if this was against a ProductCatalogue table or similar wide table then the storage implications can easily run into gigabytes of unnecessary disk. I believe most good DBAs are risk averse and there is always a risk in dropping that table further down the line so if the creator is not still around to ask – then that simple SELECT INTO has resulted in a painful investigation for the new DBA and he probably wont want to chance dropping a table in the first few months of managing a new system.
So next time you think about taking a copy of your data into your production Database have a think
i) can I park it somewhere else which means I guarantee it wont still be there in 2-3 years
ii) will I return and delete the data after my changes are confirmed as successful
iii) at least give the table a meaningful name which indicates the DeploymentID that brought about its creation and even consider spending that extra 30 seconds and adding some extended properties to the table.
There are couple of areas which is an actual pain point for a DBA. For example, during the initial phase, the database capacity planning could be a pain point in the sense that changing the predefined storage at some later point of time could be cumbersome and time consuming. There are chances of data loss also.
Another could be, generally database servers are open with elevated privileges for everyone (SA type access). This could be a pain point again for a DBA to track the activities from various users.
A good backup-restore planning could also a pain point. Apart from that, his planning should also consider all the factors that could impact the backup restore strategy. For example, backup is useless if it is corrupt and he cannot restore that.
DR strategy could be another pain point for a DBA. A bad DR strategy will led to the outage, data loss etc and ultimately the revenue loss for the customer.
Thanks
Prashant Thakwani
As a new DBA. Im struggling to get a grip of what data we have. getting monitoring running on all the servers. checking the basics are right so i can move on to some of the fun stuff you all do with Indexing and Performance tuning. As i read and find out more, theres more that i want to do on the basic level, things like setting auto growth, getting the right sized logs and mdfs. getting a good backup plan (and testing restores!). Setting maintenance plans (stopping using the SQL built in plans and trying the Ole or sqlfool. (Then working out a good way to install them on all the servers without doing it manually).
Once I have these. up the pain will be monitoing all the servers, making sure the replication is working, Making sure the log shipping is working, checking SSIS packages are working. (I think!)
My biggest pains are the ever changing requirements. Let’s say we need a new report that looks like X. So, after a few hours, I have something up on SSRS that is X. Sadly, the user really didn’t want X, but Y. Back to the drawing board. >_<
Being able to manage multiple databases (Backups, jobs, mussing indexes, long running queries, performance) from one location for all boxes within Management Studio.
Currently you can run queries against multiple servers, but that just isnt quite the solution i invisage.
Only way i can seem to do what i want is off the shelf packages which are expensive and dont integrate with MS. Ok if you have 2 servers – but then the issue isnt so bad. We have around 35 servers so the cost is too high.
Dan
Biggest pain would have to be the difference between what we’re told by other DBA’s and people in the community what we should do, versus what, if any management thinks a DBA should do.
My bosses think DBA = Administration + BI + Develoment + Code Review + Training + Mr. Fix it.
We have a very fluid, fast developing shop where there are many “emergency” requests being made all day long. Because of this I have a hard time finding the time to get documentation and processes set up to automate some of the mundane tasks. I know in the long run the automation will help with time management but when every request is an emergency it is hard to find the time for things that can be put aside for now, like projects.
My biggest pain point is monitoring performance and determining where the issue is and how to resolve. Most of our software is 3rd party and it is difficult to determine performance problems within those systems. The vendor typically states that it is our hardware so we have to go through an extensive process to identify the issue – looking at server, database, software functions etc. It is even more challenging when you do not have access to the internals of the software to actually see what function/query is being performed. Sometimes I feel very helpless when it is a problem within the software that I cannot fix and the vendor will “put it on their list of enhancements in a future upgrade”.
I hope this isn’t too generalized, but standards seem to always be my biggest pain point.
There seems to always be something in the way of getting to the 100 percent baselined standard that I would like to be at. That could be anything from backups, monitoring, alerts, PBM, file locations, and many other misc items.
I guess an even bigger pain point would be not having the time to get all those standards in place.
The phone ring…. users complain “My computer is slow” or “My application is slow” or “My database is slow”, and then I would have to review the trace, check log, alerts to answer my users.
I setup variety of alerts email, so sometime I got them before users noticed.
My Biggest Pains are:
1. Disk space. I’m always juggling around diskspace.
2. Since were Virtural, Memory / CPU. It’s a battle for what I need and what I can have.
Finding enough time to do all the regular maintenance tasks such as performance tuning, backup verification and space analysis. Less distractions during the day would be helpful.
Diskspace/file size followed closely by documentation.
A few:
1) Lack of standards or adherance to
2) Permissions, realated to # 1
3) Poor performing vendor app’s
First there no enough time to follow and check the maintenance and backup process.
second the daily increasing size of DB and the slowing process of managing data transactions
Thanks to everyone who participated in this month’s Question of the Month.The randomly selected winner of this month’s question is Brandon Williams, who as a website at http://www.sqlrepl.com. Be sure to check out the August Question of the Month.