April 21, 2008 at 9:23 am
The response I liked best was from Matt, and later Oberion.
My initial question suffered from a lack of full disclosure:
My current DB is for query only (OLAP if you like, but no cubes or any of the stuff the big kids play with); in other words, I do not have an OLTP db that I can create a clone of for reporting.
I don't have a DBA. so by default I play that role, as well as being a developer and a user.
The access is controlled at a detailed level by by roles, and no access is granted beyond select.
My users are relatively small community, and other than myself and a couple of coworkers, most queries are predefined in Access or Excel.
The bottom line is that the rogue query problem is not one that I currently face, and if it happens, I would probably have no one but myself to blame.
What I am facing are paranoid manager types (who know little of databases, but figure they can wing it) who insist that some future pipe dream production data warehouse could not allow direct query access by users (like me, in that context.)
My reply to that has been that any DBA worth his salt would have no problem establishing sufficient limits to defend against rogue queries. I guess (based on most of the replies here) I was blowing smoke, and that the prohibition against user queries is the norm.
In the meantime, I'll install the kind of background monitor proposed by Matt and Oberion so I can now say that any DBA worth his salt can at least implement the kind of monitoring in my DB, to defend against rogue queries.
Thanks much for everyone's input.
April 21, 2008 at 1:27 pm
It sounds like you've got a decent handle on what to do.
- 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 28, 2008 at 6:27 am
GilaMonster (4/18/2008)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.
Hi,
Why are you saying that you can use QG starting from 2008? It's possible to use in earlier versions also, like 2000 and 2005. It doesn't work very well but I do not see any improvement in 2008 or am I missing something here?
From BOL 2008 (CTP):
SQL Server 2008 Books Online (February 2008)
query governor cost limit Option
[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]
Use the query governor cost limit option to specify an upper limit on the time period in which a query can run. Query cost refers to the estimated elapsed time, in seconds, required to complete a query on a specific hardware configuration.
If you specify a nonzero, nonnegative value, the query governor disallows execution of any query that has an estimated cost exceeding that value. Specifying 0 (the default) for this option turns off the query governor, and all queries are allowed to run without any time limitation.
If you use sp_configure to change the value of query governor cost limit, the changed value is server wide. To change the value on a per-connection basis, use the SET QUERY_GOVERNOR_COST_LIMIT statement.
The query governor cost limit option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change query governor cost limit only when show advanced options is set to 1. The setting takes effect immediately (without a server restart).
From BOL 2005:
SQL Server 2005 Books Online (September 2007)
query governor cost limit Option
Use the query governor cost limit option to specify an upper limit on the time period in which a query can run. Query cost refers to the estimated elapsed time, in seconds, required to complete a query on a specific hardware configuration.
If you specify a nonzero, nonnegative value, the query governor disallows execution of any query that has an estimated cost exceeding that value. Specifying 0 (the default) for this option turns off the query governor, and all queries are allowed to run without any time limitation.
If you use sp_configure to change the value of query governor cost limit, the changed value is server wide. To change the value on a per-connection basis, use the SET QUERY_GOVERNOR_COST_LIMIT statement.
The query governor cost limit option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change query governor cost limit only when show advanced options is set to 1. The setting takes effect immediately (without a server restart).
Regards,
Hanslindgren
April 28, 2008 at 8:28 am
Gail was referring to resource governor ... new in 2008. It lets you assign connections to different workloads, then place those workloads into groups which can be assigned min and max cpu and memory limits - that's a very basic description.
April 28, 2008 at 9:32 am
matt stockham (4/28/2008)
Gail was referring to resource governor ... new in 2008. It lets you assign connections to different workloads, then place those workloads into groups which can be assigned min and max cpu and memory limits - that's a very basic description.
Ahh, okay. So it's Resource Govenor is a new feature and she didn't meant Query Govenor?
April 29, 2008 at 9:06 am
Hi. I too had seen the SET QUERY_GOVERNOR_COST_LIMIT value out there in BOL . My question would be how could you be sure that all ad-hoc queries would contain such a statement with their being ad-hoc and all?
As an aside when it comes to the cost/value of testing, I too have encountered many arguments against spending the effort and resources to accomplish a thorough test including something as silly not enough time in the project's schedule.
I have been very successful by telling the holder of the money and/or owner of the applications (especially if done in a venue where there are witnesses like in a status meeting) that... testing is going to happen in any case. Would you prefer it be done by your developers/users/customers in a live situation at a possible cost of revenue and lost time or invest the time and resources in a test system and/or performing a thorough test?
It is usually very effective in getting their attention to the issue.
Toni
April 29, 2008 at 9:16 am
Toni - Until you pointed out that QUERY_GOVERNOR_COST_LIMIT had to be set in the query, it had not occurred to me. If the new Resource Governor falls into the same category, it will not be much help.
I continue to maintain (since I'm not a DBA, I only play one at work) that administrative restrictions are the wrong answer. If the DB is to be useful it needs to be queried, and if some of the queries may be hogs, they need to be detected and defended against.
April 29, 2008 at 11:17 am
toniupstny (4/29/2008)
Hi. I too had seen the SET QUERY_GOVERNOR_COST_LIMIT value out there in BOL . My question would be how could you be sure that all ad-hoc queries would contain such a statement with their being ad-hoc and all?
Well, since you can set it on a global basis with sp_configure, you do not have to have it in every query!
Anyways it is very unreliable. The estimated cost is NEVER is a good estimate of the run duration of a query, in seconds, no mater how much MS tries to convince you!
April 29, 2008 at 11:49 am
Thanks Hans! I guess I'll need to play with that and (yet again) try to educate myself.
April 29, 2008 at 2:10 pm
Jim Russell (4/29/2008)
Toni - Until you pointed out that QUERY_GOVERNOR_COST_LIMIT had to be set in the query, it had not occurred to me. If the new Resource Governor falls into the same category, it will not be much help.
It's not. It's a server-wide or database-wide (not sure which) setting. Despite the similarity of the names (which it why I got confused) the query governor and the resource governor are very different things.
The resource governor allows the admin to set up resource groups, based on properties of the login (user name, app name, host name, ...) These resource groups can then be given priorities and restrictions.
It's nice, but it won't prevent all problems caused by runaway queries. If a query is run by someone in a restricted group, then that query will run longer than it would if it were unrestricted. If that query takes locks all over the system, then that query can cause major slowdowns, despite being restricted from hogging the system's resources.
It's also not going to stop someone asking for 20 million rows, thereby flushing out the data cache and flooding the network for a few minutes. It's not going to stop hundreds of ad-hoc query plans from polluting the plan cache.
What it limits are memory grants for processing the query, CPU time, DoP and number of simultaneous requests.
Here's a few good references:
http://blogs.msdn.com/psssql/archive/2008/01/10/sql-server-2008-resource-governor-questions.aspx
http://blogs.technet.com/andrew/archive/2007/11/25/sql-server-2008-resource-governor.aspx
http://msdn2.microsoft.com/en-us/library/bb933866(SQL.100).aspx
I continue to maintain (since I'm not a DBA, I only play one at work) that administrative restrictions are the wrong answer. If the DB is to be useful it needs to be queried
Sure it does. That's what stored procedures and views are there for.
If the DB is intended for and only used as an repository of data for ad-hoc queries, then ad-hoc access can be allowed, providing all the users of the database accept that query performance may be erratic at times.
If the database is supporting a time-critical OLTP application that runs the company, then no. If people want to query it, I'd set them up a separate system with a copy of the data.
If they want to be able to aggregate the data anyway possible, then maybe a cube with an excel front end is more appropriate.
and if some of the queries may be hogs, they need to be detected and defended against.
If you like unexpected database slowdowns and sudden, unexplained outages, and you enjoy the excitement of fixing things under pressure and the acclaim for being the hero, sure. Myself, I prefer a quieter, calmer life. I really don't like crises. BTDT, got the T-shirts.
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 29, 2008 at 2:12 pm
Hans Lindgren (4/29/2008)
toniupstny (4/29/2008)
The estimated cost is NEVER is a good estimate of the run duration of a query
No, it's not. The cost is based on the CPU usage, the IO usage and the execution time. All three are estimated when the query is compiled and the cost calculated from those. There are a number of things that can cause the estimates to be inaccurate, and that's not even considering plan reuse.
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 29, 2008 at 9:03 pm
Let me flog the almost dead horse a bit more Hans. The cost limit can be set for each for individual connections and/or for the entire server.
From BOL:
"Using SET QUERY_GOVERNOR_COST_LIMIT applies to the current connection only and lasts the duration of the current connection."
The Query Governor Cost Limit is a configuration option that applies to the server (sp_configure). If you did it for the entire server it would seem to apply to ALL (including your production) queries and flipping it on and off with sp_configure is not something which most would be likely to trust others to do.
The Query Governor Cost Limit is a configuration option that applies to the server (sp_configure)
Not that I think either of these are really workable.
Toni
Hans Lindgren (4/29/2008)
toniupstny (4/29/2008)
Hi. I too had seen the SET QUERY_GOVERNOR_COST_LIMIT value out there in BOL . My question would be how could you be sure that all ad-hoc queries would contain such a statement with their being ad-hoc and all?Well, since you can set it on a global basis with
sp_configure, you do not have to have it in every query!Anyways it is very unreliable. The estimated cost is NEVER is a good estimate of the run duration of a query, in seconds, no mater how much MS tries to convince you!
May 1, 2008 at 10:42 am
In SQL Server 2005 they added "Auditing Logon" feature (http://www.sqlprof.com/blogs/sqlserver/archive/2008/03/29/auditing-logins-in-sql-server-2005.aspx)
We can also add/use this feature to ensure the user DOES NOT take way too many connection too. Not sure if there is a Feature to capture "LOGOFF"
paul
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply