April 6, 2024 at 3:17 pm
I'm about to make some changes to a set of related databases (yes, the change is tested in QA and it does improve the performance) and would like to get some numbers comparing the real-world, production performance before and after the changes. I don't need to dig to the database level or even lower, the totals for the whole SQL Server 2016 instance are fine. Something like "comparing the workdays in the week before and after, the overall CPU time used was 5 % less, the IO 16 % less, the average query time dropped by 6 %, the average query time during the busiest 2 hours of a day dropped by 9 %..."
There's a lot of ad hoc SQL too complicated for SIMPLE parametrization being sent to the databases so while I can compare the average times for some queries as kept in Query Store, this will miss a huge part of the real load. While I do intend to capture and compare those numbers too, I need some global numbers as well.
There are several SQL instances on the same virtual machine, and I do know there will be no other changes to either of the instances or anything else within those two weeks and there is no reason the workloads ought to be different in those two weeks (no end of tax term date, no release of anything related, ...)
What data would you suggest I capture to compare and present as the real-world outcome of the changes?
Thanks for your suggestions!
P.S.: It's one of the first steps in a cleanup of some databases that are not in a particularly good shape. In this particular case there's a lot of tables missing a clustered index, tables with non-unique clustered index and duplicate indexes. So we'll be deleting some duplicate indexes and change lots of indexes to clustered and need some APPROXIMATE numbers regarding the overall result of the change in production. It's some municipality office stuff, so no load on weekends or outside office hours.
April 6, 2024 at 4:23 pm
Baselining is a large topic so you might want to look at third party tools. This may get you started:
https://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/
https://www.sqlservercentral.com/articles/perfmon-using-sql-server-performance-counters
June 21, 2024 at 4:53 am
Yes, good answer from Ken. You may check the "perfmon" graphs for
+ Batch request per second.
+ Physical and Logical reads
+ Buffer Cache hit ratio
+ Page life expectancy
+ Average Wait Time (ms)" & "Lock Waits / Sec
+ SQLServer:Memory Manager "Memory Grants Pending
+ SQLServer:Access Methods "Page Splits / Sec
The subtree cost represents the estimated cost of a plan. It can be useful when investigating why the query optimizer chose one plan over another
DBASupport
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply