April 18, 2008 at 5:12 am
As a SQL Server DBA, what are the steps you can take to avoid letting poorly designed queries from swamping the server. These are users/programmers that need and have access to query the database, but may unintentionally (or through ignorance) launch a much too long running process. These are ad-hoc queries, so performance tuning, if and when they come to you for help, is the long range solution; but for the immediate need...?
Short of firing them (for the incorrigible offenders), or spending all day monitoring and killing processes, what steps can you take to limit each users use of system resources, and keep them from impacting the performance of the server?
April 18, 2008 at 5:27 am
Limit permissions so that users can't write ad-hoc code.
Code reviews for all code written by the developers
Training
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2008 at 5:36 am
That's the chauvinistic IT department high priest answer!
I don't care about the job title of the author of the problematic query, I just want to limit the impact.
(And please don't misinterpret that as a personal attack; I am always impressed with, and grateful for, your knowledge and willingness to help.)
April 18, 2008 at 5:39 am
Jim Russell (4/18/2008)
As a SQL Server DBA, what are the steps you can take to avoid letting poorly designed queries from swamping the server. These are users/programmers that need and have access to query the database, but may unintentionally (or through ignorance) launch a much too long running process. These are ad-hoc queries, so performance tuning, if and when they come to you for help, is the long range solution; but for the immediate need...?Short of firing them (for the incorrigible offenders), or spending all day monitoring and killing processes, what steps can you take to limit each users use of system resources, and keep them from impacting the performance of the server?
There are several steps I take to limit or minimise this:
First of all I provide them with a daily copy of the database that sits on a test server. Each night I copy the backup from live and restore it on a test server. Obviously the data is a day old and may not satisfy all needs but I've found that more often than not they go to that database. From my point of view they can then run any queries without me having to worry about it affecting the live database.
If you need more up to date data another option is to use log shipping. Currently we log ship to two different servers - one local and one remote. What you can do is, set up log shipping to restore every 15 minutes say. As long as you use standby (rather than norecovery) users can query the log shipping secondary database, which is relatively up to date. The only thing to be aware of is that every 15 minutes users will be kicked off when the log restore runs.
If you're using enterprise edition of SQL Server you can take a snapshot of the database - which admittedly will reside on the same server so it could still impact the server as a whole. Or you could use a combination of mirroring and snapshots to achieve the same but on a different server.
If neither of these are viable options then I do the following.
As much as possible, insist on the most regularly run queries being encapsulated in stored procedures, which you verify and approve. You then force the users to use the procedures.
And finally, force users to use with(nolock) hints on all of their queries. Running a long-running query is bad enough but running a query that places shared locks on resources and prevents transactions from going through in a timely fashion is far from ideal. Obviously you have to bear in mind the consequences of using nolock hints.
April 18, 2008 at 5:45 am
i agree with Karl about having a separate database for the developers to run their queries. But have to think twice about nolocks...
"Keep Trying"
April 18, 2008 at 5:51 am
Jim Russell (4/18/2008)
That's the chauvinistic IT department high priest answer!I don't care about the job title of the author of the problematic query, I just want to limit the impact.
If you want to see it that way. The way I look at it, if I'm the one who's going to be screamed at (and possibly fired) if the server is slow, then I'm not going to permit ad-hoc access. If one bad query can cause massive financial loss (I work for a bank), then it would be highly irresponsible of me to allow that to happen.
If that means I spend part of the day sitting with the users in question, writing their queries for them and running them for them, so be it.
Maybe it means I sit with the users, work out their requirements and write them views that they can query
Maybe it means that we identify the users who do need ad-hoc access, give them training so that they can write good queries and then give them access to the box.
In SQL 2008, you'll be able to use the query governer to limit resource usage by ad-hoc queries. In SQL 2005 that's not possible. The only way to prevent a user writing a query that takes over the server is to ensure that they don't have access to do it.
Other possibilities - a seperate server, either logshipped, replicated or mirrored for ad-hoc queries. An analysis server with cubes, if they want to be able to slice their data.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2008 at 5:59 am
The only fool-proof way to is restrict the users and developers from querying the production system.
As unrealistic as this sounds, it is relatively simple to configure log shipping or replication of some sort for a simple reporting system. Once this has been done, there is no need for anything other than production applications to access production in most cases. Some companies don't like the sound of spending money, but the server and software is pretty cheap these days and the elimination of this issue and the possibility of some redundancy can be pretty easily sold in most cases.
As far as the NOLOCK query hint - I think this is very over-used and not well understood. Make sure you understand the lock architecture, the risk of dirty-reads, and the user needs before using it.
April 18, 2008 at 5:59 am
I second Gila on securing the db from ad-hoc querying. I had this L2 support person who ran a select scan on a 27 million log records table on a highly visible OLTP prod db. And to add, he wasnt bothered of seeing the result; he just fired the query and left the office. It took sometime to detect the issue and find out the cause. I didnt think twice; just went and pulled out his desktop's network cable. I was that mad! Prevention is always better than cure
April 18, 2008 at 6:03 am
Thank you so much.
Query Governor (2008!) sounds exactly like what I need.
Frankly, I expected someone to point me to some overlooked system parameter to limit elapsed time, cpu, i/o, etc. for selected (or even all) users.
I guess I'm amazed that such a capability hasn't existed forever. Are db systems from all vendors so unprotected?
April 18, 2008 at 6:03 am
Jim Russell (4/18/2008)
That's the chauvinistic IT department high priest answer!I don't care about the job title of the author of the problematic query, I just want to limit the impact.
(And please don't misinterpret that as a personal attack; I am always impressed with, and grateful for, your knowledge and willingness to help.)
Be that it may... they're still the ONLY correct answers. Doesn't matter what you do to server settings, hardware settings... those are the ONLY two answers. Skip those two and you will ALWAYS have performance problems. Be the "high priest" or be the "slave"... it's your choice.
And, no, I'm not trying to be a smarta55 here either nor pick a fight... it's just that there is no other answer that will be nearly as correct.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 18, 2008 at 6:17 am
Fight! Fight!
I actually think the query governor in SQL 2008 will give people a false sense of security and be completely mis-used.
Configuring a proper environment and training the users is still the right thing to do.
April 18, 2008 at 7:12 am
Geez, when both Jeff and Gail (both of whom I respect) gang up on me, I must be wrong!
But I'm hard to convince, and I see it as a huge flaw that a db server can be so vulnerable. I think even Multics (before Unix) had user resource limits. I'll bet even Grace Murray Hopper considered them.
April 18, 2008 at 7:29 am
(Pig Pile on Jim Russell!!!!)
Gail's right on this one.
Either no ad hoc queries at all, or set them up on a separate server. I prefer the second option.
Heck, I don't like it when I'm asked to run ad hock queries on the production server. I'm human, it's possible for me to miswrite a join and end up with several million rows of cartesian products. I haven't done it yet, but that doesn't mean it won't ever happen. And I certainly know more about SQL than anyone else here.
As to the server being so vulnerable, yeah, that's a problem, but what happens when you do need to run a query that's going to take all night to compile its data? If you limit the queries at the engine, you eliminate a lot of important reporting/warehousing options.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 18, 2008 at 8:06 am
Jim Russell (4/18/2008)
But I'm hard to convince, and I see it as a huge flaw that a db server can be so vulnerable.
I don't see it as a vulnerability. The database engine's job is to run queries as fast as possible. The idea of limitations goes conterary to that.
Giving users free reign to run anything on a production server is just a bad idea. Even ignoring the performance problems, what about security?
The practices of minimal access and encapsulation are not new ones
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2008 at 8:07 am
"...but what happens when you do need to run a query that's going to take all night to compile its data?"
Then you get your favorite high-priced DBA (if you have one) to bless the query, or the stored procedure you put the query in, and increase (or remove) the resource limits for that user/procedure/query, after determining that there is no more efficient way to get the data you require.
"I haven't done it yet, but that doesn't mean it won't ever happen."
That is exactly the point, all of us (users, developers, programmers, DBAs) are fallible and without resource limits the only two options are:
1. Hope to hell nobody screws up.
2. Keep everybody out of the data -- in which case you might as well make your final backup to a 2-by-4, 'cause the data is not going to serve any purpose for anyone!
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply