November 13, 2020 at 12:00 am
Comments posted to this topic are about the item How Do You Experiment?
November 13, 2020 at 9:57 pm
My method of experimenting is to get a database that has some sample data I can play with and just go at it.
We had a long blocking query a few weeks back which I was confused about. The query, normally completes in 200 ms, so it is not a long running query, but this one time, it was the lead blocker and ran for 2 hours. Digging led me to believe it was a problem with it going parallel when it didn't need to and the host OS didn't have a free CPU for all of the threads.
My experimenting at this point was to try to improve the performance of the query by not running it parallel, but forcing a single-threaded execution (MAXDOP 1). This brought the execution time down to 20 ms, so 10 times faster simply by removing the threading.
My experimenting method is to have the query in the original format running against the test system so I can see how it runs without changes. Get my baseline metrics without any code or config changes first because the test system has a lot less load than live, so it behaving slowly on live may not be true on test. I need to get my baseline execution times.
Once I have a good baseline, I tune. Sometimes my tuning makes things worse, sometimes it is better. Once I have confirmed it is consistently better through repeated execution, I simulate a load and see how it works when there are multiple executions of the query or query executing and another query looking to change the data at the same time. With queries that complete in 20 ms, hitting that window where blocking may become problematic is tricky, but if the query runs in 10 seconds, I can do some blocking testing too.
When tuning a query, I experiment a lot on the test/dev systems to make sure I don't impact production and once it seems "ready", I find an second person to confirm my findings just to make sure I'm not improving performance at the cost of data accuracy (things like NOLOCK).
My second "experiment" method is this forum actually. Sometimes, I will look at a forum post where someone wants help with a query and sometimes I have no idea how to help them. But I can spin up their query and play with it on a test instance and learn from there how to solve the problem. Sometimes it results in an GIANT query that needs a lot of tuning, sometimes I can get a decent query to help the OP. My methods are not always as efficient as some of the other ones out there, but I usually end up learning something as I go.
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply