July 25, 2022 at 1:43 pm
Thanks for all the feedback everyone. Appreciated.
Me, I love tuning queries.
Yes Jeff, working on the new book. Chapter 20 right now, Graph data.
@Grant ,
Awesome... I don't know how you find the time.
Once upon a time, you had a couple of write-ups for how to write a book and what the process entails but the links I found are dead. Do you still have that information in a link somewhere that I could get to?
--Jeff Moden
Change is inevitable... Change for the better is not.
July 25, 2022 at 2:43 pm
Lately, there is little need for tuning. At least here. Unless there is a query that is absolutely causing an issue, there is no time spent on performance. Hurry up, get the latest new feature completed, and make sure you take in all of the accolades.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 25, 2022 at 4:42 pm
I work with what I consider a large number of third party databases. I still troubleshoot poorly performing queries. I now have DBA acquaintances at those companies and they have all received article links with names like Grant Fritchey, Brent Ozar, Phil Factor, and even Jeff Moden (for some reason splitting strings is still a popular need in SQL). I do some work for them and also provide sources for them to verify the logic and start a conversation. I am the change I want to see. My manager and his peers are happy that I don't just say, "It's the vendor's fault. Tell them to fix it on their own." Many of their database professionals are now members of the larger SQL community. We have mutual respect for each other because offering solutions and having open communication channels is always good business policy. For some of those vendors, we now get discounts because we will run tests in our dev environment with them (yes, I know how lucky I am to have a dev environment). This allowed me to monetize query performance tuning. I got a nice raise that year. Having a need for query performance improvement brings you to the community. The community keeps you engaged. Engaged business partners make better businesses.
I also have a strong need to make square things fit into round holes. I crave the puzzle. I don't know if I could stop if my manager told me to.
July 25, 2022 at 8:39 pm
In every job I've had as a SQL DBA, I was the company's first DBA, and the main reason they hired me is that they realized that the C# developers (who knew just enough SQL to be dangerous) and Systems Administrators just didn't know how to make the databases go faster, other than by throwing more money at hardware (or scaling up in Azure SQL DB), which cost more than my salary and wasn't sustainable. So they had a good business reason for performance tuning: users were complaining and any other fix would cost too much.
I do it because I enjoy it, and have gotten reasonably good at it over time (with help from you, Brent, Erik, the Pauls, Erin, Michael J. Swart, Itzik, and many others). I love the SQL Community!
July 25, 2022 at 9:22 pm
I have found that >95% of the time, software optimization (index tuning) will scale better than throwing hardware at it. It's going to be cost prohibitive to keep throwing thousands at additional CPU or VM's (and associated licensing to support those CPU's) when compared to the education and development time to implement tuning. I can't count how many queries I've tuned from other developers that went from >60 seconds to milliseconds. Yes, it took a couple of hours and I have some extensive training, but I'll let you calculate how much hardware you'll need to get a gain of that magnitude.
Plus, I had that solution in place with relief on the server within a couple of hours. Who else has the resources to scale that with hardware in that time-frame with an open checkbook for cloud resources?
Scaling with hardware is done once software optimizations have reached their limit. You can only get so much performance with a terabyte database and 8GB of memory, so it's logical to turn up the hardware. But, at least in my scenarios, there have been plenty of resources available on the server. They just aren't being used to their full potential.
July 25, 2022 at 11:46 pm
I work with what I consider a large number of third party databases. I still troubleshoot poorly performing queries. I now have DBA acquaintances at those companies and they have all received article links with names like Grant Fritchey, Brent Ozar, Phil Factor, and even Jeff Moden (for some reason splitting strings is still a popular need in SQL). I do some work for them and also provide sources for them to verify the logic and start a conversation. I am the change I want to see. My manager and his peers are happy that I don't just say, "It's the vendor's fault. Tell them to fix it on their own." Many of their database professionals are now members of the larger SQL community. We have mutual respect for each other because offering solutions and having open communication channels is always good business policy. For some of those vendors, we now get discounts because we will run tests in our dev environment with them (yes, I know how lucky I am to have a dev environment). This allowed me to monetize query performance tuning. I got a nice raise that year. Having a need for query performance improvement brings you to the community. The community keeps you engaged. Engaged business partners make better businesses.
I also have a strong need to make square things fit into round holes. I crave the puzzle. I don't know if I could stop if my manager told me to.
I've had good success with working with a lot of the 3rd party vendors in my past lives. I also got really disgusted when I suggested to one vendor that was getting ready to install their junk in one of our systems. None of the tables had clustered indexes. When I suggested that they should, their lead "developer" said the wouldn't do it because it would make their code "non-portable". Their code also did a SELECT * with no search criteria on what would become a huge table. They explained that indexes slow down inserts without looking to see that the selects occurred a thousand time more than their inserts, which went in at the end of the table anyway because it was a heap.
Most adventures with 3rd party vendors where much better than that with many being just like the ones you described. It was to the "mutual benefit" of both companies.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 26, 2022 at 11:27 am
Grant Fritchey wrote:Thanks for all the feedback everyone. Appreciated.
Me, I love tuning queries.
Yes Jeff, working on the new book. Chapter 20 right now, Graph data.
@Grant ,
Awesome... I don't know how you find the time.
Once upon a time, you had a couple of write-ups for how to write a book and what the process entails but the links I found are dead. Do you still have that information in a link somewhere that I could get to?
Hmmmm... Not sure. I'll have to look around.
"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
July 26, 2022 at 1:32 pm
m60freeman wrote:
"In every job I've had as a SQL DBA, I was the company's first DBA, and the main reason they hired me is that they realized that the C# developers (who knew just enough SQL to be dangerous) and Systems Administrators just didn't know how to make the databases go faster, other than by throwing more money at hardware (or scaling up in Azure SQL DB), which cost more than my salary and wasn't sustainable. So they had a good business reason for performance tuning: users were complaining and any other fix would cost too much.
I do it because I enjoy it, and have gotten reasonably good at it over time (with help from you, Brent, Erik, the Pauls, Erin, Michael J. Swart, Itzik, and many others). I love the SQL Community!"
"developers (who knew just enough SQL to be dangerous) " - precious, really precious 🙂
Zidar's Theorem: The best code is no code at all...
August 12, 2022 at 3:22 am
Our C# developers (who know just enough SQL to be dangerous) write code using Entity Framework and I have trust that Microsoft will generate good SQL. So far so good - and it's getting better with every version on EF Core. This approach of strongly typed code that will give syntax errors when I change something in the model beats stored procedures every time. Maintenance is king!
Occasionally, I would love to get an advice from a humble DBA who can analyze the generated SQL and work with the developers to help them tune their C# code. Instead, I usually get the feedback from a DBA (who doesn't know any C# and is extremely dangerous) that C# code should just invoke a stored procedure that will be much more efficient.
August 12, 2022 at 7:09 am
Our C# developers (who know just enough SQL to be dangerous) write code using Entity Framework and I have trust that Microsoft will generate good SQL. So far so good - and it's getting better with every version on EF Core. This approach of strongly typed code that will give syntax errors when I change something in the model beats stored procedures every time. Maintenance is king!
Occasionally, I would love to get an advice from a humble DBA who can analyze the generated SQL and work with the developers to help them tune their C# code. Instead, I usually get the feedback from a DBA (who doesn't know any C# and is extremely dangerous) that C# code should just invoke a stored procedure that will be much more efficient.
There is plenty of evidence out there that EF does not always generate good SQL. Later versions are miles better, but still not great. If the parameters to the stored procedure and the result sets are properly typed, they will also give syntax errors when the model changes. Strongly typed code that uses stored procedures will always be more maintainable than a complex EF model if you have someone who understands SQL. It will also almost always perform better.
Chris
August 12, 2022 at 12:49 pm
Our C# developers (who know just enough SQL to be dangerous) write code using Entity Framework and I have trust that Microsoft will generate good SQL. So far so good - and it's getting better with every version on EF Core. This approach of strongly typed code that will give syntax errors when I change something in the model beats stored procedures every time. Maintenance is king!
Occasionally, I would love to get an advice from a humble DBA who can analyze the generated SQL and work with the developers to help them tune their C# code. Instead, I usually get the feedback from a DBA (who doesn't know any C# and is extremely dangerous) that C# code should just invoke a stored procedure that will be much more efficient.
Virshu, OK, so you knock the developers and you knock the DBA's. And you think an automated tool can write better code. Got it.
Better be careful. Someday you may actgually NEED one of those 'extremely dangerous' DBA's. I was a dba for years, but long before that I was a developer fluent in at least five programming languages that I can remember, so been there done that. In my time we had to learn to be good developers before we got the opportunity to even dream of being DBA's and SQL Developers.
Right, I'm NOT a humble DBA, but was a pretty good one. I would no longer claim to have front-end languaage developer skills, but do still have the understanding and empathy for those who do. In my day I saw some really, reallly bad SQL development and spent lots of time on improvements.
One thing for sure is that long run it's far less expensive to fix and improve bad SQL than it is to buy more haradware and software resources to hide bad code.
My last gig was in a development effort in which we had created and maintained nearly 500 stored procedures, none of which were generated by software, and only a few of which were created by developers. I'd match the SQL skills of our DBA group against any and all other sources.
Instead of criticizing the developers, pitch in and help them be better.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
August 12, 2022 at 2:12 pm
Jeff Moden wrote:Grant Fritchey wrote:Thanks for all the feedback everyone. Appreciated.
Me, I love tuning queries.
Yes Jeff, working on the new book. Chapter 20 right now, Graph data.
@Grant ,
Awesome... I don't know how you find the time.
Once upon a time, you had a couple of write-ups for how to write a book and what the process entails but the links I found are dead. Do you still have that information in a link somewhere that I could get to?
Hmmmm... Not sure. I'll have to look around.
Only one I could find: 3rd Edition, SQL Server Execution Plans, a Story - Grant Fritchey (scarydba.com)
"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
August 12, 2022 at 2:16 pm
Our C# developers (who know just enough SQL to be dangerous) write code using Entity Framework and I have trust that Microsoft will generate good SQL. So far so good - and it's getting better with every version on EF Core. This approach of strongly typed code that will give syntax errors when I change something in the model beats stored procedures every time. Maintenance is king!
Occasionally, I would love to get an advice from a humble DBA who can analyze the generated SQL and work with the developers to help them tune their C# code. Instead, I usually get the feedback from a DBA (who doesn't know any C# and is extremely dangerous) that C# code should just invoke a stored procedure that will be much more efficient.
I'm with you. EF does about 95 to 98% of the T-SQL code really well.
However!!
That last 2-5%, it really is best handled within a stored procedure. No offence intended in any way, but that's how it goes. I'm a fan of EF and an advocate for it (which gets me a lot of bad words from some quarters). I see the utility and I've worked on teams that made that tool scream. Those teams, let me create procs where they were needed. It's all about cooperation. Humility should run both ways.
"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
August 12, 2022 at 4:24 pm
Grant Fritchey wrote:Jeff Moden wrote:Grant Fritchey wrote:Thanks for all the feedback everyone. Appreciated.
Me, I love tuning queries.
Yes Jeff, working on the new book. Chapter 20 right now, Graph data.
@Grant ,
Awesome... I don't know how you find the time.
Once upon a time, you had a couple of write-ups for how to write a book and what the process entails but the links I found are dead. Do you still have that information in a link somewhere that I could get to?
Hmmmm... Not sure. I'll have to look around.
Only one I could find: 3rd Edition, SQL Server Execution Plans, a Story - Grant Fritchey (scarydba.com)
Awesome. Thanks, Grant.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 12, 2022 at 7:02 pm
One more small thing: if we are dealing with poor database design, sometimes neither tuning nor hardware helps.
Zidar's Theorem: The best code is no code at all...
Viewing 15 posts - 16 through 30 (of 44 total)
You must be logged in to reply to this topic. Login to reply