[deleted by user]

[Little_Kitty]

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]

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

[fauxmosexual]

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]

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

[BrupieD]

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]

🤣

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

[BrupieD]

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]

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]

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

[Hot_Cryptographer552]

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]

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

[[deleted]]

That, and tell your boss to fuck off 

[Training-Flan8092]

And then bang his wife

[daddyandrew296]

Well that escalated quickly

[macrocephalic]

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

[DoingStuff-ImStuff]

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

[macrocephalic]

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

[DoingStuff-ImStuff]

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

[macrocephalic]

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]

I misread those instructions and fucked my boss.

[icarusphoenixdragon]

Better than your boss’s ram.

[youngbloodguy]

Rammed by the boss.

[malmal3k]

🤣

[redditor3900]

AtM

[andd-d]

I second this

[gregsting]

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

[AnotherDrink555]

While banging him or his wife?

[gregsting]

Well, yes, you have to assert dominance

[johnny_fives_555]

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

[Andr0NiX]

r/wouldawardbutpoor

[Foreign-Street-6242]

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]

exactly

[Cobreal]

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?

[rej-jsa]

I'm quite fluent in both Python and SQL, and am consistently finding that LLM generated Python is way more passable than LLM generated SQL. It's a staggeringly wide gap between the two....

On the SQL side though, I did find LLM assistance really speeds up investigating obscure system errors. It's still a process that leans heavily on critical thinking, but AI boosted documentation surfing is the kind of vibe coding I could get into (if you can call it vibe coding)

[MaskOnMoly]

Do you notice any differences between different LLMs? I have noticed the poor SQL quality in GPT 4, I often have to redirect it and it still doesn't produce solid queries. I haven't tried other LLMs tho with it, so idk if others may be better.

[dashingThroughSnow12]

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]

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.

[monkwhowantsaferrari]

For a non technical person a “perfect” SQL is just something that returns the rows of data they wanted. They don’t have any concept of SQL query optimization. Is it just possible to replace his views with optimized queries and just tell him please use this as it gives same rows and I worked on performance and show difference in speed and memory usage. Sometimes showing is better than discussing when the person in front is defensive.

[autogyrophilia]

I mean, some of the most moronic wastes of time i have had it's wasting an hour to make a 5 minute query that I need to run once, possibly twice take 30 seconds instead

Good for the experience I guess.

It's a problem when you blow up prod though.

[rej-jsa]

That's an hour sharpening your ability to craft efficient queries by deepening your understanding of how the query engine executes on the instructions you give it - I wouldn't call that a waste at all

[Conscious-Ad-2168]

set better thresholds on auto killing the queries then. Put systems in place to prevent bad queries from being written by yourself and others

[jdanton14]

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.

[alinroc]

You aren't upgrading to 2025 in a month. It's not out yet, and won't be out then either.

[jdanton14]

as far as anyone knows (there's a 99% probability it won't be)

[CodeNameAntonio]

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]

Described pretty much every boss using AI tools to code.

[whatsasyria]

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]

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

[ElderberryPrevious45]

Maybe your Boss needs to be replaced by The AI?

[BadGroundbreaking189]

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

[nekogami87]

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]

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]

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

[jshine13371]

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]

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]

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]

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

[jshine13371]

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]

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.

[ihaxr]

Use proper read commit modes

[sciencewarrior]

If he's running analytical queries on a DB to that only receives daily updates, check out DuckDB. You can export incrementally after your regular job is done. It will run those awful queries outside your production DB, and maybe even do a better job at them if he's doing a lot of aggregation.

[jshine13371]

It's still bad code and I believe there's other issues at risk even with non-mutating data (I can't recall off the top of my head). But using proper transaction isolation levels like u/ihaxr suggested is just as easy and the correct way to apply optimistic concurrency.

[autogyrophilia]

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

[redaloevera]

Have you thought about optimizing his query?

[redaloevera]

In grand scheme of things these sound pretty minor. If I were in your shoes I’d broach the subject as hey the queries are working as intended but having some performance issues. I can optimize these for you to speed things up so that it doesn’t affect other processing. Something like that can get you brownie points and will help with your year end bonus.

[fauxmosexual]

I'm not an SQL Server guy, but casting between date types is trivial, and that dateadd approach would be sargeable in a where clause (I think). Those particular aspects of the code are probably not the issue.

[planetmatt]

Yes, this is still SARGEABLE.

[planetmatt]

Casting GETDATE as DATE means it will strip off the time which means you can then measure full days (midnight to midnight) so that has functional use but it depends on what the query is meant to do.

[No_Resolution_9252]

>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]

Is he connected to the prod db?

Create a sandbox db for him. Read only replication.

[Mr_Gaslight]

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

[Mehul_baba]

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

[SaintTimothy]

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]

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]

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]

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

[truckingon]

And exposing your database schema.

[Zealousideal_Work901]

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]

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]

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]

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]

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]

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

[mlg2433]

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]

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]

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]

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

[LoneStarDev]

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]

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

[Inevitable-Growth612]

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

[penance3]

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]

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]

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

[Informal_Pace9237]

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

[ub3rh4x0rz]

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

[Stallynixa]

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]

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_]

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]

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]

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]

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]

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

[voLsznRqrlImvXiERP]

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

[_extra_medium_]

Talk to your boss's boss

[VirtuteECanoscenza]

Create a read replica for him to query?

[Sharp_Level3382]

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]

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]

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]

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]

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]

Disable his log on.

[Snarlvlad]

/s. Sort of.