September 14, 2021 at 2:52 pm
I am not seeing any high resource utilization like CPU/Memory and Disk queue length. But seeing a waits during the performance issues. Is that be related to the issue? Please advise?
September 14, 2021 at 4:37 pm
The wait stats I am seeing is LCK_M_U, WRITELOG and LCK_M_S are they normal behavior or something to be looked into it?
September 14, 2021 at 5:58 pm
There is a lot of area to go with your question. Not seeing high CPU, memory, or disk queue may or may not tell you much. For example, if your server has 1 TB of memory and SQL Server is configured to use 1 GB, you won't see memory pressure on the SERVER, but you will on the instance.
Similarly if you have 4 cores and SQL Server is configured to use 1, the highest your CPU will go is 25% due to SQL server. You still have CPU pressure, it is just less apparent.
As for seeing those locks, that doesn't tell you much. Seeing a shared lock or an update lock or a write log isn't bad unless you are seeing them excessively. Excessively is very system dependent though. Excessive on my system will be different than excessive on your system.
What I would recommend looking at is how many queries are "slow"? Is it only a small number OR is it all? If it is all queries, then something is likely misconfigured on the OS or SQL instance. If it is a small number, then it is likely a poorly written query or missing indexes or bad statistics... Does the performance get better after a reboot or is it worse? Is your AV configured to scan the database files? Did the performance slowdown happen suddenly or gradually? Did something change on the system to cause the slowdown (updates, software installs, etc)?
Might not hurt to find a slow query, turn on the actual execution plan and statistics IO and statistics time and analyze where the slowness is coming from in the query. Execution plan will tell you the expensive part of the query (may not be the "slowest" part mind you) and give you an idea of what to look at. Statistics IO and time will tell you how many reads you are doing and how long the query is actually taking to complete (CPU and elapsed time).
Knowing what is slow changes what I look at for troubleshooting. If the ENTIRE system is slow, then I would be looking at hardware and instance/database configurations. If it is only 1 query, then I'd work to tune that query. If it is a handful of queries being slow, but others are fine, I'd look for common objects in them or common operations. For example, if all the slow queries use the same view, I would test the view and see if it is slow. If all of the slow queries are doing INSERTS and the quick ones are only doing SELECTs, it could be something with writing to the database that is causing the slowness such as disk I/O or antivirus or backups or...
If you have a database monitoring tool, I'd check it out to see what it says as it could be excessive blocking (for example). If you don't have a database monitoring tool, I recommend you buy one as they are VERY helpful.
Worst case, hiring a consultant to review your system may be beneficial. I am not a consultant, but hiring a good consultant is often much cheaper than trying to fix things on your own when you don't know where to start looking. And a consultant will sometimes offer some training for what they did and how you can improve your systems!
I think the above gives you a bit to check. The above is not an exhaustive list of things to check mind you and some performance tuning experts can probably provide more advice.
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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply