March 11, 2021 at 2:01 pm
This is a generic question to know more about SQL server performance.Nobody has complained of any performace issues on our servers, but there is an agenda to know the existing performance and how that can be improved upon.
What are the metrics that need to be captured to identify a baseline for a sql server performance and how do we improve from there.
To improve from the existing performace. Can the following be a start.
Finding out the most accessed tables/indexes - slotting them by time periods and frequency of their access and then utilizing resource governor and allocating resources accordingly to get better performance metrics by allocating resources as per the access requests and their time periods.
Any feedback is appreciated. Thanks
March 11, 2021 at 5:00 pm
There are multiple ways, but I like the "don't reinvent the wheel" approach.
Get a SQL Monitoring solution in place. It will tell you about blocking, deadlocks, long running queries, etc and you can go from there. Good SQL monitoring tools usually come at a cost, but the benefit of the tool is that you know your systems are not running into problems behind the scenes.
Personally, I wouldn't be looking at resource governor if end users seem to think things are running good enough for them. I would be looking at what is running slowly that end users aren't bothering to report.
Free tools like "Database Health Monitor" (http://databasehealth.com/) can give you a nice insight into your database and how it is performing and what can be improved upon. For example, you can check your statistics and see if they need updating and if you should be scheduling that update to be more frequently or if it is fine. You can also check index fragmentation and you may want to defragment those or change the fill factor. You can see the inefficient indexes and unused indexes too and removing unused indexes can help performance. WARNING - removing unused indexes may cause performance problems too if the indexes were created to help with infrequently used critical queries.
The other fun thing you can tackle (if you find this sort of stuff fun) is technical debt. For example, if you are using the TEXT datatype in a column, Database Health Monitor can tell you about that and it would be a good thing to fix. Or old join syntax, or even just bad habits (lowercase keywords for example).
But, like with all tools telling you something should be fixed, you need to proceed with caution. Changing code in production is a good way to end up getting a call at 2:00 AM telling you about a bug. I would make sure you test things as best as you can on a test environment to ensure your changes are non-breaking changes. Changing "select" to "SELECT" should be non breaking, but if that is in a stored procedure, the change may result in a new query plan. If statistics are out of date, that new plan may cause the query to think there is 1 row returned and get back 1 billion, causing a poor plan to be chosen and getting poor performance.
One other cool thing that Database Health Monitor can do is historical analysis. You need to configure it on the database level and it will have a slight performance impact, but it will give you historical information about your database such as waits, plan cache hit ratio, page life expectancy, and CPU load.
NOTES - I do not work for a company that produces SQL Tools (including Monitoring tools or Database Health Monitor). I am just recommending tools that I use and like and think should be in most DBA's back toolkits.
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.
March 12, 2021 at 5:02 am
This was removed by the editor as SPAM
March 16, 2021 at 3:21 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply