1


I've run into an issue with one of my tableau users. His queries have taken down the DB a few times yesterday.
My solution is twofold.

  • First, I'd like to limit his rights. Is there a way for me to limit the maximum length of his queries before they timeout?
  • Second, I'd like to setup an alert for the database. I know how to setup an alert, however I'm not sure what thresholds to use.

Is there some way for me to see recent usage in order to estimate where I should set the threshold?

Thanks!

migrated from stackoverflow.com Dec 13 '16 at 15:42

This question came from our site for professional and enthusiast programmers.

  • When you say his queries have taken down the DB, what exactly do you mean? Where was the pressure (memory, cpu, disk)? – SQLChao Dec 13 '16 at 15:41
  • I mean that the query took up too many resources, and other programs such as slx would timeout because of it. – yoelbenyossef Dec 13 '16 at 16:17
  • This sounds like it could also be blocking, so you should also check if processes are being blocked for a long time. – James Z Dec 13 '16 at 16:46
2

This sounds like a job for SP_BLITZ,SP_WHOISACTIVE etc. By Brent Ozar & Team.

https://www.brentozar.com/first-aid/

Edit, I'm not advertising for them (it's a free resource anyway). This is exactly what I've used for this exact scenario. 3rd party apps and their rogue developers killing the DB.

You might also want to limit them to using views/procedures that you write/have control of in order to get their data.

Edit2: And I just realized this is an old question that got bumped somehow.
My answer stands.

0

Here's a query I wrote up awhile ago that does something similar that lets you filter on the username or the database if you look at the dm_exec_sessions table. There's a link below that will walk you through setting up alerts and emailing on SQL Server. The 300 in the where clause is seconds for where the query has been running.

You can set up resource governor to limit his CPU or RAM per query. You can put into effect stored procedures or triggers to automatically kill his SPIDS if they go over, but that gets dangerous. I'd start by limiting resources and alerts to manually intervene.

Ideally, you can go and teach him how to properly query without bringing down the database.

SELECT
    'SPID=' + CONVERT(VARCHAR , a.session_id) + ' has been running the following for ' + CONVERT(VARCHAR , DATEDIFF(SS ,
                                                                                                        a.start_time ,
                                                                                                        CURRENT_TIMESTAMP)) +
    ' seconds: ' + CONVERT(VARCHAR , b.text)
    ,CONVERT(VARCHAR , DATEDIFF(SS , a.start_time , CURRENT_TIMESTAMP))
    ,login_name
FROM
    sys.dm_exec_requests a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
INNER JOIN sys.dm_exec_sessions AS c
ON  a.session_id = c.session_id
WHERE
    a.status <> 'background' AND
    DATEDIFF(SS , a.start_time , CURRENT_TIMESTAMP) > 300 AND c.login_name = 'username' AND c.database_id = 'id of database'

Source on setting up alerts

Source on sessions

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.