In the meantime however, I still need to find a way to throttle those runaway jobs to the background (or even better yet, shut them down) when they occur. But when it comes down to responsible query development, there is a core knowledge point that is missing. new comers and coders breaking into the SQL space, the language is not hard to learn, nor use. I was working with a young man a few days ago (another that thought waiting an hour for a query was "OK"), and although he knew what a left outer join was, he could not explain what it actually did, or more importantly when it was warranted. One of the things I've noticed about new SQL/Analysts is that there is an overwhelming lack of fundamental knowledge (theory) about how things work, and why things do what they do. haven't spent enough time (and money) on training these poor souls simply trying to produce results.Īll of those things are underway. they don't understand the data model, and to the institutions discredit.
#Resource governor to control etl processes code#
I could build a patrol dog sql agent job that cycles every 15-30 minutes looking for queries that have been running for too long and then cut them off (and trying to code for things that DO run for long periods, like backups and index reorgs), but that seems awfully severe.and not without a whole lot of potential negative side effects.Īny ideas? ( besides dealing with the cowboys poor techniques? I'm already on that one.) But what about these scheduled things that kick off in the middle of my jobs? (That happened last night, my ETL job was 45 minutes in to a 90 minute process and one of these 4 hour queries locked one of the tables I was trying to fill for four hours, pushing my warehouse fill completion from 4:30 am to 9am). My next step is to add KILL statements to that look up to kill those connections during my time. I've already built a procedure that runs at the beginning and end of my maintenance and ETL jobs to detect open queries, so I know who my cowboys are and I have captured copies of their offensive SQL. is there a away as a native SQL Server config setting, to automatically kill any query that runs for more than n minutes? I looked into the Query timeout setting in Adv options, but I'm not entirely certain that will do what I need. The environment is tuned.indexes in place, and so far I have been able to rebuild these 4 hour queries to run in a minute or two.Īnyway, before I get on that soapbox. All you other DBA's out there know what I'm talking about, the problem isn't the database nor 's the programmers lack of respect for the environment.
In every case it is a problem of query construction, and the excessive use of sub selects (these guys are using subselects in one query more than I've used last year). Some have even gotten clever and used their book/classroom knowledge to schedule these mega long running queries to kick off in the middle of the night so they don't have to wait for them which interferes with maintenance plans, backups, and warehouse fill jobs. These guys seem to think it's ok to build a query that runs for 2-4 hours. but don't really know what they are doing, especially in dealing with very large tables.
Recently, several of my customers have hired new SQL cowboys that "know SQL". I remotely manage several data warehouse platforms for Credit Unions around the country. I'm not really sure this is a "performance tuning" topic, but I didn't know where else to drop it so I figured I'd start here.
0 kommentar(er)
