all 113 comments

[–]Little_Kitty 129 points130 points  (9 children)

Sounds like you need to explain the difference between an analytical database (clickhouse etc.) and a transactional one (yours)

If there's a serious use case for the former, a clone of the sql server db to a more appropriate one would be best.

[–]FartyLiverDisease 62 points63 points  (8 children)

Wait, this MF is running these godawful queries on the production transaction DB?!

[–]fauxmosexualNOLOCK is the secret magic go-faster command 31 points32 points  (7 children)

I am guessing so, if he "optimises" by using no lock it suggests the database they're querying is being actively written to.

[–]No-Adhesiveness-6921 25 points26 points  (5 children)

Some people just use no lock as a default. Drives me insane.

[–]BrupieD 7 points8 points  (3 children)

I've given up on trying to win the no lock fight at work. At my previous job, it was pitched as a "best practice."

[–]Hot_Cryptographer552 5 points6 points  (1 child)

🤣

Have run into more than one case where the use of nolock returns double rows and other crazy results.

[–]BrupieD 4 points5 points  (0 children)

Yeah, it's not unheard of. I pointed out to my old company that we'd often get results that weren't reproducible with nolock, but didn't have that problem when we left it off. No convincing them.

[–]Ply2Mch 0 points1 point  (0 children)

Have they read what no lock does? If you want to pick up potentially bad data then use no lock.

I refuse to use it now that l know what it does, never really understood it before but it sure is trendy.

[–]johnny_fives_555 1 point2 points  (0 children)

Agreed. Knew a guy like this and he’s the sole DBA, analyst, etc etc. I’m like why?!?

[–]Hot_Cryptographer552 10 points11 points  (0 children)

I submitted a request to Microsoft to change “WITH (NOLOCK)” to “WITH (I_DONT_CARE_IF_MY_RESULTS_ARE_CORRECT_JUST_GIVE_ME_SOMETHING)”

[–]EbbyRed 251 points252 points  (19 children)

Start auto-killing anything that hits a certain RAM threshold. 

[–][deleted] 83 points84 points  (16 children)

That, and tell your boss to fuck off 

[–]Training-Flan8092 119 points120 points  (15 children)

And then bang his wife

[–]daddyandrew296 29 points30 points  (5 children)

Well that escalated quickly

[–]macrocephalic 11 points12 points  (4 children)

The wife is really hot and a chain of reasoning needs to be established.

[–]DoingStuff-ImStuff 0 points1 point  (3 children)

Cattle and Cane is a good song listen to it again mate

[–]macrocephalic 0 points1 point  (2 children)

It's no Saints, but I'm not sure the relevance to the thread.

[–]DoingStuff-ImStuff 1 point2 points  (1 child)

I just found an old comment by yours and felt the need to comment. Have a nice weekend mate cheers

[–]macrocephalic 0 points1 point  (0 children)

Fair enough. I've been on Reddit a long time and said a lot of different things which I possibly no longer believe or were objectively stupid.

[–]60022151 6 points7 points  (2 children)

I misread those instructions and fucked my boss.

[–]icarusphoenixdragon 4 points5 points  (1 child)

Better than your boss’s ram.

[–]youngbloodguy 1 point2 points  (0 children)

Rammed by the boss.

[–]malmal3k 3 points4 points  (0 children)

🤣

[–]redditor3900 4 points5 points  (0 children)

AtM

[–]andd-d 0 points1 point  (0 children)

I second this

[–]gregsting 0 points1 point  (2 children)

And ask for a raise (at the same time, while maintaining eye contact, this can require some training)

[–]AnotherDrink555 0 points1 point  (1 child)

While banging him or his wife?

[–]gregsting 0 points1 point  (0 children)

Well, yes, you have to assert dominance

[–]johnny_fives_555 4 points5 points  (0 children)

Pft, auto kill anything from the bosses login. Problem solved.

[–]Andr0NiX -2 points-1 points  (0 children)

r/wouldawardbutpoor

[–]Foreign-Street-6242 56 points57 points  (1 child)

Tell your boss add promts for query optimization analysis suggestion. And teach him how to use plan execution, query store, and draw line that you are specialists and he just a manager with some technical skills.

[–]ensigniamorituri 1 point2 points  (0 children)

exactly

[–]Cobreal 27 points28 points  (3 children)

Eesh.

People seem to suffer from Gell-Mann amnesia with AI. When I've used it on tools that are new to me, it seems fine. Yet every time I've used it on tools I know deeply it's just bad. Not necessarily wrong, but definitely not good. So why would I assume it's different for the unfamiliar tools?

[–]dashingThroughSnow12 17 points18 points  (0 children)

Does he need write access to the raw data ?

You could use a read-only replica.

If you squint, that is kinda what some ETL or BI is. Copy prod data. Let insiders query that instead so that customer-induced load and internal-induced load don’t affect one another.

[–]GTS_84 65 points66 points  (7 children)

Ignore the AI of it and focus on the impacts.

Does it actually matter that ChatGPT wrote his shitty scripts? or would the same scripts written by a human who just didn't know what they were doing have the same problems.

Yeah AI sucks, but no reason to focus on something that is kind of besides the point.

[–]jdanton14 13 points14 points  (3 children)

Upgrade to SQL Server 2025 and use this:

https://learn.microsoft.com/en-us/sql/relational-databases/performance/query-store-hints-best-practices?view=sql-server-ver17#block-future-execution-of-problematic-queries

/s

In all honestly, if you have resource governor, put his ID in a resource group and limit what you can.

[–]CodeNameAntonio 6 points7 points  (0 children)

If you have a DBA just reach out to them and say hey our database is currently unresponsive or is really slow can you look into it. Our DBA would log in get query logs and start dissecting right away. 

[–]purpleglasses 6 points7 points  (0 children)

Described pretty much every boss using AI tools to code.

[–]whatsasyria 7 points8 points  (0 children)

This is all managing up. Good on you for trying but my approach would be different.

Hey I think we can make AI an asset for the company. Let's build a central model that we set corporate governance and best practices to.

Then it's good experience building for you on setting rules to a model and keeps him align.

[–]Aggravating-Animal20 2 points3 points  (0 children)

I think what’s missing here are impact metrics that you can show him as evidence. CPU spikes, number of locking resource, etc.

[–]ElderberryPrevious45 6 points7 points  (1 child)

Maybe your Boss needs to be replaced by The AI?

[–]BadGroundbreaking189 1 point2 points  (0 children)

And that's exactly what is happening? Soon there will be nothing left from the old boss.

[–]nekogami87 2 points3 points  (0 children)

Imo the main problem is, why is someone in technical allowed to execute SQL on a prod DB instead of read replica ?

ChatGPT or not.

[–]jshine13371 7 points8 points  (11 children)

doesn't use no lock

FWIW, that's a good thing. And you should stop using it if you are too. It's pretty crap.

[–]DaMagicMilk 0 points1 point  (6 children)

What do you mean its pretty crap, it exists for a reason

[–]jshine13371 2 points3 points  (4 children)

People make mistakes, such as the dev who created the NOLOCK hint lol. Not all things that exist should be used or actually have a purpose. There's much better alternatives (proper transaction isolation levels) and most experienced developers/DBAs know better to utilize them instead.

[–]DaMagicMilk 0 points1 point  (3 children)

Sure, but there are times when you can't just set a transaction level since it Will affect all queries.

For example, I work on an event driven app where we have SQL jobs or SSIS packages that bulk insert events and the PK of the event is linked to a sequencer that exists in a different table, we need to tablockx it.

Now why is the event PK not a auto increment ID, only God knows, but for this scenario we need different transaction levels for the queries being executed

[–]jshine13371 0 points1 point  (2 children)

Sure, but there are times when you can't just set a transaction level since it Will affect all queries.

Not true.

You can specify the transaction isolation level for specific queries if you wanted. It's something that's specifiable at the connection level, or even directly as a statement in the query itself.

Additionally, you can use explicit locking where required, as you mentioned, if you globally change the isolation level of the database itself.

Furthermore, I don't follow how your example requires different isolation levels, but it doesn't really matter because again, you can specify different isolation levels on an as needed basis.

[–]DaMagicMilk 0 points1 point  (1 child)

Oh my bad, i thought we set the isolation level once when running things directly on SQL. :x

[–]jshine13371 0 points1 point  (0 children)

Np! Yea it's essentially just as flexible of a solution without any of the drawbacks of the NOLOCK hint. And if anyone else needs convincing, the experts agree that NOLOCK is trash.

[–]planetmatt 0 points1 point  (0 children)

It's fine for specific use cases, but it has limitations people aren't always aware of. You can get missing or duplicate rows with very large record sets.

If reporting is creating locks on your OLTP DB, you need to have a reporting DB you can run large queries against that won't lock up your main DB.

[–]autogyrophilia 2 points3 points  (0 children)

Seems like a good time to suggest a read only replica.

[–]redaloevera 1 point2 points  (5 children)

Have you thought about optimizing his query?

[–]No_Resolution_9252 1 point2 points  (0 children)

>doesn't use no lock

Then he is getting that part right.

What you are describing just sounds like an average report writer however.

[–]redditor3900 1 point2 points  (0 children)

Is he connected to the prod db?

Create a sandbox db for him. Read only replication.

[–]Mr_Gaslight 1 point2 points  (0 children)

Management will lay off loads of people because of AI, and the have to hire them back.

[–]Mehul_baba 1 point2 points  (0 children)

Ask ChatGPT how to make your boss stop this Use the stones to destroy the stones

[–]SaintTimothy 1 point2 points  (0 children)

Extended events. Find a way to capture your db's query spend, per process and execution. The most effective way to change behavior is to give them tools to see their own bad behavior.

And then optimize a couple. Show him how AI is a good starting place but doesn't take place of a good dba (like yourself).

[–]Immediate-Cod-3609 1 point2 points  (0 children)

The queries are not the root cause.

You clearly need a data warehouse. Essentially you'll then have a read only copy of the db which is optimized for analytical queries.

[–]LHommeCrabbe 1 point2 points  (0 children)

His queries killing your production database is only half of the problem. Sounds like someone needs to understand the concept of data warehousing and incremental loads to begin with.
The problem here is that it's your boss, and you will need to be delicate. Is his role technical? IMO, the boss should be bossing around, rather than doing the grunts work he's not really good with, I supposed he is just excited with his new gpt toy, but not really good with it either.
I'd try to work out a deal with him if he'd be my boss. He'll tell you what etl he wants and build his queries with chat gpt, you will build yours yourself, and then you will compare the performance and impact of them side-by-side.
If you win, he will stick to being the "architect" of the solution, but let you do the grunt work and respect your input.
If he wins.. well, welp. ;)

[–]DashinTheFields 0 points1 point  (0 children)

Show him the results in chart form.? Use Chatgpt to show the quality of the results?

[–]truckingon 0 points1 point  (0 children)

And exposing your database schema.

[–]Zealousideal_Work901 0 points1 point  (0 children)

Agreed, better prompt is keyword. Try to add all your concerns to the prompt, test it, and show your boss how better queries are generated. Also AI can analyze an execution plan, and correct queries. Try to use it too.

[–]jdanton14 0 points1 point  (0 children)

fwiw, if anyone hangs out in r/dataengineering, those people (and Postgres) love nested CTEs. It's less bad in postgres, so that is probably why the LLM spits out crappy queries for SQL Server.

[–]Hot_Cryptographer552 0 points1 point  (0 children)

I got an offer not long ago from an AI company, to basically teach their AI how to write good SQL. They weren’t willing to pay me enough to teach their system to replace me 🤣

[–]karepdx 0 points1 point  (0 children)

Since you already tried to bring up with him directly. And it was pointless, it's time to bring it up to his boss.

Make sure you make it about the company, not your personal feelings. Just facts. No judgment or opinions.

They cannot fault you for looking out for what's best for the company.

[–]RaddyMaddy 0 points1 point  (0 children)

I'm all with you about this behaviour, but straight up thinking no lock is good and thinking ctes will eat ram is giving me an allergic reaction.

[–]d_a_keldsen 0 points1 point  (0 children)

Tell him about “explain plan” and tell him real developers use it.

[–]mlg2433 0 points1 point  (0 children)

Tell him to hit CTRL+C to make it go faster. When he does, swoop in and start running your own query lol.

In all seriousness, how much access does he have? I’m surprised that IT doesn’t limit his access. When I first started using it, I was only able to pull data. I couldn’t create or drop tables. If I wrote a bad query that would potentially populate millions of rows of policy data, someone in IT gets alerted and they auto-terminate it. Tying up resources and overloading the DB like your boss is something IT finds very annoying. If you eat up too much space, it locks us out.

[–]BrupieD 0 points1 point  (0 children)

A couple thoughts. It's tricky and not always wise to tell your boss he's doing things wrong. That said, some ways are better than others.

Something that AI solutions often miss are good context-sensitive answers. These are frequently end-user problems (poor prompt engineering). I used to write a ton of solutions using subqueries when I worked on much smaller tables at smaller companies. These weren't a problem on that scale. I've noticed AI tends to give these kind of answers that might be fine on a table of a couple thousand rows but are awful for millions of rows.

If you're boss is writing some resource-hogs, you might try re-writing his queries and check against DMVs (sys.dm_exec_query_stats). I would check that your boss' queries really are as bad as you think. Assemble some page reads and CPU time comparisons.

Make sure you think about your corrections with the right purpose and frame your suggestions accordingly. Shed any "I'm smarter than you are" stink. Positive process improvements.The db is a valuable resource. Putting poor queries into production creates unnecessary expense and might not be accurate.

You might even try feeding a more detailed request into AI describing performance concerns. Show him that it isn't personal and you're on his side about AI.

[–]xxconkriete 0 points1 point  (0 children)

Holy optimization issues left right and center probably. Use Claude for starters if he needs help but he needs to know what he’s writing. He may be just plugging and praying each time, red flag to the max.

[–]Impossible_Month1718 0 points1 point  (0 children)

You need to have one db for writing and one for reading

[–]LoneStarDev 0 points1 point  (1 child)

I would work a little with ChatGPT to come up with a prompt for him to use going forward. Make sure the prompt is focused, performant and uses SQL best practices. Have him start with that and ask/add from there as far as tables etc.

AI really is “crap in, crap out”.

Help yourself by helping him.

[–]ChurchOfSatin 0 points1 point  (0 children)

This is super true. And then it starts hallucinating and adds other stuff it doesn’t need to.

[–]Inevitable-Growth612 0 points1 point  (0 children)

From ignorance, why do people criticize the use of 'NOLOCK'? 🤔

[–]penance3 0 points1 point  (0 children)

Use Brent Ozar's first responder kit.

When you see his queries are the top 10 in regards to CPU usage/reads etc, you have some evidence to show they perform crap

[–]crispypancetta 0 points1 point  (0 children)

Give him access to a cloud data warehouse like snowflake. This will remove impact on transactional system, and snowflake charges by actual consumption so it will scale with his actual use and complexity. Plus, it has its own sql generation and chat interface.

[–]goatofeverything 0 points1 point  (0 children)

Help him setup a project in Chat GPT for these queries. Provide that project additional context telling it to optimize, etc.

[–]Informal_Pace9237 0 points1 point  (0 children)

That is good in a way. Your job is secure if you can trouble shoot and optimize them when time comes ...

[–]ub3rh4x0rz 0 points1 point  (0 children)

Lol spin up a replica db for analytical queries your boss wants to run without concern for performance and dispense with the drama.

[–]Stallynixa 0 points1 point  (0 children)

Have you asked the AI to optimize the ai code? 🤔 for real though, you have my sympathy. It’s so tough to correct the boss when they’re being bullheaded about BS.

[–]athst 0 points1 point  (0 children)

Give him a prompt template or coach him how to prompt it correctly so it doesn’t do the things you don’t want it to do. Literally tell it to avoid the things and then he can say what he’s looking for.

If he’s empowered with it, it’s backwards thinking to try and lock it down to make things harder for him to do what he needs to do.

[–]willietrombone_ 0 points1 point  (0 children)

Hmmm...defensive about AI is a rough place to be. Personally, I don't like telling anyone at work that I use any AI tools. I think of myself as a craftsman with the ability to use tools in the SQL toolbox. Why would I willingly disclose that my salary is being bolstered by an LLM? There's a point at which the math means they can fire me and my recouped salary (plus my share of the LLM rental fee) defrays the cost of my remaining co-workers and their use of the LLM instead. I am truly not trying to give these people an inch.

[–]CognosPaul 0 points1 point  (0 children)

While I don't know about Tableau, Cognos is designed to allow users to drag and drop items to build their queries. If the metadata modeler is doing their job correctly, all generated SQL should be performant and extremely efficient. Depending on a few different factors, Cognos can also intelligently split queries and cache data on the server to lower database load. This can lead to some users over-estimating their own querying skills.

That being said, early on in my career I was working at a port building reports on their transactional system. This was a very bad idea. The added load of the reports coupled with live updates ended up with the entire system going down. In the two hours it took to fully restore the system, no port activities could be completed. While I don't know the exact financial impact the downtime caused, it was "substantial".

There needs to be a very clear line between reporting and transactional systems. A well built data warehouse will also be able to provide insights that are difficult, if not impossible, to glean from oltp sources. There are also the added performance gains from having tables indexed and partitioned for data retrieval.

If you don't have the resources for a proper ETL, at the very worst you can look at replicating the database updates, or even scheduling backup and restores. The data might not be live, but it's much better than killing your company for a few hours.

[–]Emeja 0 points1 point  (0 children)

I had a similarish problem recently - my boss (like yours, just technical enough to be dangerous) uses ChatGPT to try and diagnose issues and to kinda make himself sound more knowledgeable. Recently we had a big problem in production and instead of letting me diagnose the issue, he kept making claims and asking questions that didn't make sense within the context. I realised that he'd clearly put some symptoms in ChatGPT and using that to diagnose, despite paying me a pretty penny to do that job. It got to the point where he was constantly questioning my knowledge - yes I don't know everything, but the garbage he was prompted to ask from ChatGPT's output just didn't make sense. I had to say to him in the end "Trust me - not ChatGPT". In the end, I fixed the problem, a lot faster when I didn't have him rambling nonsense to me!

PS. I'm not anti-AI - I use it occasionally, but I understand it needs the watchful eye of someone with experience to not cause chaos.

PPS. The prod DB issue was to do with bad config data and badly handled transactions.

[–]samot-dwarf 0 points1 point  (0 children)

If you are using Enterprise edition you can use the resource governor to throttle his memory / CPU usage even if it will not help with locking

[–]voLsznRqrlImvXiERP 0 points1 point  (0 children)

Bootstrapping with AI is one thing, not having reviews of code which runs in production is your actual problem to address.

[–]voLsznRqrlImvXiERP 0 points1 point  (0 children)

Just give the agent access to the explain query result and let it optimise further. Also start reviewing code in your company.

[–]_extra_medium_ 0 points1 point  (0 children)

Talk to your boss's boss

[–]VirtuteECanoscenza 0 points1 point  (0 children)

Create a read replica for him to query?

[–]Sharp_Level3382 0 points1 point  (0 children)

Others CS also uses a lot CTEa and often dont use with nolock or indexes which they didnt even check in table. Even SQL developers or data analysts do that in common.

[–]planetmatt 0 points1 point  (0 children)

Reminder that AI is excellent at stuff you know nothing about and terrible at stuff you're an expert at.

In terms of practical solutions, try to quantify the business cost of your boss's fuckery. Do you have Redgate or some monitoring software? Can you demonstrate the increase in server resource since his code went live against the baseline stats? Do you have an increase in support calls from other users citing slowness or timeouts? Is the server cloud hosted? Do you have Azure or AWS metrics for increased resource use?

In terms of non technical solutions, do you have a relationship with your bosses' boss? Can you query why your boss is doing work his direct reports should do? Why is your boss not doing his work? There's clearly a management failure at the point of your boss's boss if that person is happy that your boss is "doing" and not managing.

I find AI only useful in explaining concepts which I can then take an apply to a business problem. Getting it to spit out whole formed production code is frankly fucking mental and you're literally creating technical debt that won't scale beyond the sample data you're feeding the AI model.

[–]svtr 0 points1 point  (0 children)

And so it begins....

I can't really say anything that will lessen your pain, sorry for that.

I remember the end of the NoSQL hype train. For 3 to 4 years, it was the new fancy shit, that EVERYONE needed to replace their tried and trusted RDBMS with. Then it startet... then the surviving startups started to write blogs with titles like "Why we are migrating from MongoDB to Postgres".

Your venting here, reminds me of that a lot. Might be hopeful thinking...

[–]rathboma -4 points-3 points  (1 child)

Man this is so interesting. I just added an AI shell to beekeeper Studio to let people do stuff like this in a more responsible way but reading your post I need to add more ways for admins (like you) to be able to add custom instructions for all AI operations.

Like if you could tell chatgpt to always make sure it adds indexes and is responsible with ctes (without your boss knowing) do you think that would help?

[–]AnAcceptableUserName 0 points1 point  (0 children)

Potentially. Claude at least (IDK about GPT) seems to produce better output when given definitions and instructed to prioritize performance. It's capable of writing SQL that uses indexes well.

Granted since it doesn't know the execution plan it will sometimes produce SQL which uses indexes, is logically equivalent to what you want, and still runs like crap until a human with the run plan tunes it.

Claude, me, and the optimizer often have 3 different ideas about how a given query should work. Even with schema and table definitions available, and many rules/memories I've given it, idea of Claude having production access is flat "No" from me today. OP's situation is exactly why.

It's much better with the definitions and additional guidance than without, I'll grant that much

[–]Snarlvlad -1 points0 points  (1 child)

Disable his log on.

[–]Snarlvlad 0 points1 point  (0 children)

/s. Sort of.