Microsoft and Oracle SQL systems performance and tuning

by Mountain Computers Inc, Publication Date: Saturday, February 25, 2023

View Count: 567, Keywords: Microsoft, Oracle, SQL, Tuning, Performance, Hashtags: #Microsoft #Oracle #SQL #Tuning #Performance

In the last few years, I have witnessed several SQL database architectures that have been, shall I say, out of balance, not normalized nor indexed properly or basically in terms of database refactoring.

For me, it's easy to see when you have some SQL execution plan, trace logs, I/O metrics, all of that in front of you, both in production and test, and you are puzzled as to why the performance is so slow; yes, "so slow" is a valid KPI term. Expectations aside, I prefer data to be processed within chucks and batches, not linear.

a funny note: when I see slow performing systems, I ask myself, it there some sort of debugging enabled as to interfere with production? It means, when you enabled debugging and logging, it sometimes quadruples or more the processing throughput and performance, killing the streamlined optimization that currently exists.

What does that mean? I prefer to analyze the execution plan results and explore the data schema within it.

Soon, you realize, wow, how did this happen and then you go about fixing it. How does one go about doing that?

First, Ask questions (don't make statements), and show observations, explore the history of why things are the way they are, and if no one has a valid idea, then make suggestions, and then based on everyone's nod of acceptance, backup your architecture, complete the changes in your test environment (and if you don't have one, figure out to make one inexpensively and in your licensing compliance), and then implement the change, look for positive results, and hope nothing breaks. If all goes well, implement in production in between any and all things important that is already on the schedule.

I know that's a long paragraph above, yet its comprehensible. I'll drop in some references shortly.

ref:

side-note of the day:

A slacker was 20 minutes late and received two math problems… His solutions shocked his professor.

ref: https://www.youtube.com/watch?v=O_bVUeQw38c

Andy Flagg, the official and original GGPCTU

i once spent 8 hours programming an algorithm to process some data, and after 6.5 hours had solved it, due that day by close of business, and was happy to have solved it, though the data processing did take some 45 minutes which was rather slow in my mind, and while finishing and cleaning up my code... accidentally deleted it all, and my backup was 4 hours old... ugh.. so knowing the answer in my head, i started the re-programming and had 45 minutes to achieve it, and re-wrote all the code and in a different way, all in about 30 minutes, and the data processing time was reduced from 45 minutes to 7 minutes... serendipity... an epiphany.. whatever.. i learned two valuable lessons, 1) backup your finish product immediately, 2) refactoring your code is a good thing... even given deadlines... and if you have the time to spare...

more to come...

Microsoft and Oracle SQL systems performance and tuning

by Mountain Computers Inc, Publication Date: Saturday, February 25, 2023

View Count: 567, Keywords: Microsoft, Oracle, SQL, Tuning, Performance, Hashtags: #Microsoft #Oracle #SQL #Tuning #Performance

A slacker was 20 minutes late and received two math problems… His solutions shocked his professor.

Andy Flagg, the official and original GGPCTU

if you found this article helpful, consider contributing $10, 20 an Andrew Jackson or so..to the author. more authors coming soon

FYI we use paypal or patreon, patreon has 3x the transaction fees, so we don't, not yet.