November 1, 2019 at 6:53 am
Hi All,
This is a non-technical question and want to know your sincere opinions on this.
In our org, as DBA support Team, we have enough work performing SQL Installations. migrations, upgrades, server builds, Automation's, DB Refreshes, Security etc... and all of a sudden once in a while, some 'X' app team working in the org, comes back on us and say we need help in tuning these bunch of queries which are running slow in the database? As you people are maintaining the databases, tell us why this query is running slow. These queries used to work or run fine within a min or 2 and from last week these queries are running dead slow? We do ask if any changes happened to the code. The immediate answers from them will be NO changes.
And we have to break our heads. So, I want to know, if you were in such a scenario, what would you ask to the application team or how would you tackle such situation so that everyone knows what they have to do and asking the right questions and right help?
Though it was uncomfortable, I try saying, that I am afraid to help in re-writing these huge business logic queries as the code will be changing for every release and that's something Dev team has to revisit and re-write the queries. However, we can try helping in looking at the execution plans and recommend best practices, missing index or covering indexes, identify key performance killers like index fragmentation issues, was the query is getting blocked, deadlocked, key lookups, cursor usage, usage of unwanted hints NOLOCK etc.
Sometimes I feel like saying direct 'NO' and this is not part of our job. Sometimes, you genuinely don't know how to approach or solve that problem. And sometimes, you feel like helping them in all possible ways, but end up wasting time and effort, you don't get any credit and sometimes even end up in an Escalation on your team saying they haven't helped us on timely basis.
So, I want to know, how will you tackle such situations within an organization if you working alone or managing too many things or getting minimal support from your management etc ... ? Please don't say, quit the job and find a new one 🙂
I would like to know what kind of right questions you might ask to the app team? How do you set that right expectations so that you don't end up taking things too Personal and affecting good relationships between co-workers or within teams ( like Dba team and Systems Team ).
Thanks,
Sam
November 1, 2019 at 9:01 am
Sam
Yes, you can help with execution plans, code smells (of which I've found many in vendor code), deadlock analysis, table and index designs, and so on. However, if you don't own the code and the database structure, the most you'll usually end up doing is suggesting to the development team or third-party suppliers modifications that can be made.
You can also help them to understand why performance can vary from one moment to the next even with no changes to the DDL and DML. You'll want to ensure that statistics and indexes are being maintained in the most appropriate way.
John
November 1, 2019 at 11:56 am
a few things you can do
1: put static code analysis in place, if the devs can see their code isn't great, they'll have less of an argument
2:use the sql performance dashboard to keep on top of issues
3: get some metrics on data growth - this could be why "it ran great last week"
4: keep an eye on your indexes - maybe automate a report about missing indexes
5: MOST IMPORTANT : never take a developers word for it when they say "nothing has changed". create a DDL database trigger to track changes to the code
CREATE TABLE [dbo].[DDL_Logging](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[dta] [varchar](max) NULL,
[datestamp] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG1]
) ON [FG1] TEXTIMAGE_ON [FG1]
GO
ALTER TABLE [dbo].[DDL_Logging] ADD DEFAULT (getdate()) FOR [datestamp]
GO
CREATE TRIGGER [ddlReleaseCapture_allchanges]
ON DATABASE
FOR ALTER_PROCEDURE ,ALTER_TABLE, ALTER_TRIGGER
AS
DECLARE
@eventdata VARCHAR(MAX) = 'Database objects have been changed !<br><br>' + CONVERT(VARCHAR(MAX), EVENTDATA())
INSERT INTO DDL_logging(dta,datestamp) SELECT @eventdata,GETDATE()
GO
ENABLE TRIGGER [ddlReleaseCapture_allchanges] ON DATABASE
GO
MVDBA
November 1, 2019 at 12:50 pm
Honestly, this sounds like the DBA job of the future. The easily automated stuff, server allocation, SQL Server installs, backups, consistency checks, all these things, should be already automated. The only time spent on these should be maintaining the automation. Then, what good are you to the company? Two places, emergencies and development. Disaster recovery really can't be automated (too much, a bunch of it can be, and should be). Development also can't be automated (I don't mean the deployment of developed apps, that also should be automated). You have to build databases the right way. You have to put the right indexes in place. You have to code to those structures correctly. Specialized knowledge of how databases behave make you employable over the long term.
Don't, don't, don't dismiss development and say "NO!" to developers. Not only is embracing development a fundamental part of any DBAs job, it's a smart move. On multiple levels. First, you get to more directly influence the crap they're doing to your systems. Better to fix things in development than let them get to production, catch fire, and then deal with it. Second, you'll be future-proofing your career by learning necessary skills. Third, you're showing the organization that you're not about gatekeeping and fencing, but you're concerned primarily with supporting the organization. Again, long term career growth here.
I'm sure there are a ton more reasons why than this, but heck yes. Get on top of this. Don't miss this opportunity and recognize that it is an opportunity. Your time should be spent coding automation and coding databases, not setting up security and doing installs. If that's where you are spending your time, change it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 1, 2019 at 1:54 pm
Don't, don't, don't dismiss development and say "NO!" to developers.
I learnt this from a wise old network manager who told me that you catch more flies with honey than you do with vinegar
he also told me "keep your powder dry, save it for when you are in a fight"
MVDBA
November 1, 2019 at 2:16 pm
Like others have said don't flat out say no especially when there are easy wins there for you like just looking at some execution plans for obvious weirdness. Not only does that give you some good exposure outside of your specific focus which will help prevent you from ending up siloed you might also identify some more systematic issue that's impacting more than just their specific queries like oops a maintenance job hasn't been running or something.
November 1, 2019 at 4:52 pm
Along with the excellent suggestions above, also look very carefully at the query plans to see if you're hitting a "tipping point" query. That is, one query that returns, for example, 32,845 rows works very fast, but then when it tries to return 38,917 rows it is extremely slow. It's very possible you've tipped into doing a full scan / full table scan rather than a seek / index scan. Updating stats will not ultimately help if that is the situation.
Often the best way to deal with that is to select different key(s) for the clustering index, to make the best possible choice there, rather than one maybe defaulted to earlier. It's also possible you might need to just an included column to an existing index or create a new non-clus index.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 1, 2019 at 5:15 pm
Grant Fritchey wrote:Don't, don't, don't dismiss development and say "NO!" to developers.
I learnt this from a wise old network manager who told me that you catch more flies with honey than you do with vinegar
But, as "Married with Children" noted, ""if you pull off their wings, you can make them do anything you want." 🙂
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 1, 2019 at 5:32 pm
I would say look for patterns that cause problems. Whether in code structure/smells, indexing, or something else. Then try to show the developers why things are problematic and let them then alter the code.
As Grant says, I think this is the DBA of the future.
November 1, 2019 at 8:58 pm
I love those "nothing changed" comments. SOMETHING changed or the code wouldn't be slower. Well, most of the time.
When I get those types of things coming to my desk, I usually ask "how urgently do you need this resolved?". This way I know if this is a "drop everything and fix it" problem or a "look at it when you have time" problem. After that I ask for sample data and if I can run it on live during company uptime. If the stored procedure is a Read Only stored procedure, then I can try to tune it on live without impacting the live system too badly. If it is a read write or write only, I will clone live to test and try to reproduce the problem. If it runs fine on test, but is slow on live, the problem is likely going to be related to system use (ie blocking or resource starvation). Next, I will look at our audit tool (DLM Dashboard) to see what has changed on the system.
Our DBA's are also developers, so I can also poke around in git to see if the application has changed. Sometimes C# changes that SHOULDN'T affect SQL performance are where the problem is. Best one I saw was a developer told me "something changed in the database because this code worked earlier today but doesn't now". So spent a good day digging through SQL code to see what changed, restoring from backup to a test system and doing an object level comparison and not seeing anything different. Started digging through data changes and then thought "what if a new app version went out". And yep. App broke right after lunch, new version went out at noon. Roll back to the previous app version and everything is running great again.
I usually try to be as diplomatic as possible when telling them what I can do and what I need them to do. For example, I ask them to add a timer into their code to measure how long the SQL call is actually taking, or if they have access to Extended Events or Profiler, get them to tell me the actual execution time of the SQL query. Execution plans are also a good way to go (as recommended by others here).
If you can train the developer to do some digging on their own without you, you can encourage them to solve problems on their side OR come to you with a better set of questions.
Also, having a DBA look over developer code is never a bad idea. All it takes is for a developer to google and get a result telling them to add "OPTION (RECOMPILE)" to their query, or worse - NOLOCK. Trying to convince someone to break a bad habit after they find one forum post that helped their query run faster and therefore MUST be the right solution can be pretty difficult. But when they come to you saying "sometimes this stored procedure gives me incorrect results or misses some valid ones", you can tell them to remove NOLOCK.
All of that being said, a stored procedure that takes 1-2 minutes to complete sounds incredibly slow. To me that sounds like a good target for query optimization. All of the stored procedures we have that face end users we try to keep under 5 second execution time. If it takes longer than that, it doesn't hit live. Sometimes this results in some crazy refactoring, sometimes it is a simple thing like adding a temp table to store a linked server table in or using OPENQUERY vs 4 part naming.
TL;DR - I think end user training is one of the best things you can do. If they say it is slow, work with the developer to figure out why it is slow. Maybe it is their code, maybe it is stale statistics, maybe it is blocking... work with them (when possible) to get to a good solution.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 2, 2019 at 6:58 pm
I agree with Grant and Mike. I would especially agree with Grant about troubleshooting code being part of the tasks of the DBA of the future but that is and always has been incorrect, IMHO. It has ALWAYS been the job of even pure system DBAs to correctly identify the source of performance issues.
The difference between being a "DBA" and being a great or exceptional DBA is how you do that. You can quickly identify a bad stored procedure and tell the Developers that it needs to be fixed and you've done your job, right? Well, consider this... it was the Developers that originally wrote the code in question... what makes you think they have the ability to fix it? I consider such an "Ivory Tower" approach to being a DBA to be not only non-productive but actually counter productive and that type of DBA ends up being hated and further increases the "great divide" between Developers and DBAs.
Now, I also understand large companies and system DBAs that work for large companies. They are typically up to their eyes in work and actually have little time to help or mentor Developers with their code.
That also means that the company is broken and it will get more and more broken as time wears on, data grows, and more functionality is demanded from the code.
Ok... so what's the fix? The answer is wicked simple and a whole lot of companies that know the cost of everything and the value of nothing might never get it right. You simply need someone in the company that actually knows how to write good, accurate, high performance, low resource usage code and how it all inter-plays with with disks, indexes, and memory including such things as when recompiles will occur (and how to fix them), when page splits will occur (an how to fix them), when and how indexes will be used, and, most importantly of all, how to teach and mentor others without coming across as an arrogant, self-important, seemingly know it all, haughty jerk.
To simplify, you need someone dedicated to bridging the gap between Developers and DBAs. In smaller companies, this may be a Developer that has the necessary "DBA licks" or a Hybrid DBA that also knows T-SQL and the other things I mentioned to the 9's. Either method can work great in larger companies except that management doesn't get it. They think the difference between Developers and DBAs is as large as the difference as between Cooks and Janitors and it's just stupid for them to think that way. It's even more incredibly stupid when Developers and DBAs think that way.
From the tone of things in the original post, I'm thinking that both the company and the DBAs are thinking the wrong way. What needs to happen is that the company does actually need to hire a "hybrid" or two. I'm not talking about someone that can write both T-SQL and C# (for example) and I'm not talking about hiring someone that also has to write front end code on a project basis. It would actually be better if they no longer know how to write front end code but did way back when.
Then, leave these "hybrids" alone. Let them find the performance issues. Have them sit with the Developers but let them have the same privs as the DBAs. Let them do code reviews for the Developers and give the time to use such in-person reviews as one-on-one mentoring sessions. Make sure that the Developers are given the time to take part in such sessions. Make the goal of producing code that works the first time and will work for all time the goal no matter how big or small the code is.
Do all that and build both the trust and the culture (some call it DevOps... I don't because what people call DevOps and Agile and a thousand other things is actually badly broken) between Developers, the Hybrids, and the DBAs and you see quite literally that the slower you go, the faster you'll be able to pump out product that your customers will rave about. Every talks about continuous releases but that has no chance of working correctly if you don't also adopt the habits of building and releasing code with ZERO DEFECTS.
I'm not just shooting the breeze here or making some silly-assed speculation... This has worked everywhere that I've been allowed to implement it. And, yeah... it takes a little time to implement it because it IS a culture change and it DOES require everyone from GM/CEO right down to the janitor to make a change but the rewards have been fantastic. You also be amazed when the great divide between Developers, DBAs, AND management evaporates.
It's also going to require that there be certain people that can evaluate code and actually say "No... I don't care what the schedule is, this code is broken (or non-scalable or whatever...) and needs to be fixed or I won't allow it to be deployed". Without that power, there will be no change worth a hoot. That sounds really bad and it will be in the early month or two but, if you do it right, such an incident will become an incredibly rare thing in the near future and the benefits of "Zero Defects" will quickly be realized.
But, before you can think outside of the box, you must first realize... your in a box. Start breaking down the silos even if management doesn't agree. Realize that you're all in this together and that everyone needs to help.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2019 at 7:20 am
Hi All,
Thank you very much for expressing honest thoughts.Many thanks. It means a lot for people like me.
-Sam
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply