Are there any performance test tools that can check the performance of a SQL Box? We are trying to benchmark a 2008 R2 Enterprise and a 2017 Standard box.
Any help would be greatly appreciated.
Note: You posted in SQL 2022 forum, though neither one of your target servers is 2022.
April 22, 2024 at 10:18 pm
I would also like to add that tools will give you a benchmark of how the server performs under load. This is a good benchmark, but if your server doesn't ever get a heavy load on it, a better benchmark for your environment may be a targeted benchmark (ie "build it yourself").
What I do when benchmarking my SQL instances is to pick a query that I know takes some time to complete on production, run that on the current test system, and run it on my "improved" system. My opinion - targeted testing is MUCH more important than just a generic benchmark tool in your scenario is that you are testing 2 different versions (2008 R2 vs 2017) with 2 different editions (Enterprise vs standard). The benchmarking tools are (usually) designed to push a load or run a ton of queries against the system. While this may be interesting data, it isn't what I would be looking at when comparing those things. What I would be more interested in is what does a normal workload "look like" on both systems. If I run stored procedure ABC on 2008 R2, what does statistics IO and time look like compared to 2017? I am also interested in the overall server load while running my tests - how does the CPU, disk IO, network IO, and memory look when I run my targeted load on each system?
Sure, I may have 100's or 1000's of stored procedures to test for performance degradation, but those benchmarking tools aren't going to do dry runs of each SP. I will also want to do testing of all applications that touch the database. In theory, they shouldn't care as long as the SP's come back nice, but I can't say with 100% confidence that the applications won't have some bugs due to the new version OR due to going from enterprise to standard. Another fun thing with enterprise to standard is the loss of functionality such as the query optimizer picking an index on a view automatically or resumable index maintenance.
Just my 2 cents though. You MAY be more interested in the "hammer the server and see the performance" approach. I find those tools are better suited for hardware changes than software version upgrades (2008 R2 to 2017) as newer software versions will almost always perform better with benchmarking tools.
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.
April 22, 2024 at 10:27 pm
This was removed by the editor as SPAM
April 25, 2024 at 2:22 pm
Thank for your insightful answer - I would have marked it as an answer but I couldn't mark more than one.
What I do when benchmarking my SQL instances is to pick a query that I know takes some time to complete on production, run that on the current test system, and run it on my "improved" system. My opinion - targeted testing is MUCH more important than just a generic benchmark tool in your scenario is that you are testing 2 different versions (2008 R2 vs 2017) with 2 different editions (Enterprise vs standard). The benchmarking tools are (usually) designed to push a load or run a ton of queries against the system. While this may be interesting data, it isn't what I would be looking at when comparing those things. What I would be more interested in is what does a normal workload "look like" on both systems. If I run stored procedure ABC on 2008 R2, what does statistics IO and time look like compared to 2017? I am also interested in the overall server load while running my tests - how does the CPU, disk IO, network IO, and memory look when I run my targeted load on each system?
I'm more of a SQL and .NET Developer who also maintains our database for our system. How can I check CPU and disk IO while running a query that lasts 1-8 seconds? We are doing our testing locally so the network should not be involved. Obviously I could add more rows and make it take longer. Is it just a matter of checking task manager or is there a way to see that and record it through SQL Server Profiler?
In theory, they shouldn't care as long as the SP's come back nice, but I can't say with 100% confidence that the applications won't have some bugs due to the new version OR due to going from enterprise to standard. Another fun thing with enterprise to standard is the loss of functionality such as the query optimizer picking an index on a view automatically or resumable index maintenance.
I didn't know Enterprise has features that might make it faster. I always thought it was just extra features, the engine was identical.
Is it possible that Enterprise is much faster, in general, even for the simplest of operations or does Standard throttle performance?
On the table we use there's just a primary clustered key (we applied the KISS principle to eliminate all variables) with 2 fields with the identity Id - a text and a date. The PK qualifies as an index but the table is empty so I'm not sure how it could impact inserts which are 2.5x slower. I guess updates and deletes could use the index to find the data for the row being deleted as the where clauses uses the id.
April 25, 2024 at 2:34 pm
For simple operations Standard Edition is fine. Differences can be found at https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2022?view=sql-server-ver16#choose-sql-server-features
Is the execution plan between 2008 and 2017 the same? Are they fully patched?
April 25, 2024 at 5:57 pm
For simple operations Standard Edition is fine. Differences can be found at https://learn.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2022?view=sql-server-ver16#choose-sql-server-features
Is the execution plan between 2008 and 2017 the same? Are they fully patched?
Thank you, I've looked at the differences and a lot of the Enterprise features seemed like overkill and way beyond what we'd need which is why we went with the Standard Edition. Of course, if it's 2.5x slower, then by all means we'd prefer the Enterprise:-)
I'm not entirely proficient with GDRs and CUs - I read about them. We installed SQL Server using a GDR version from Oct 10th,2023 and there's a CU (#31) dated the same day. Do I need to install a CU in addition to the GDR?
https://sqlserverbuilds.blogspot.com/2017/01/sql-server-2017-versions.html
Here's the @@Version
Microsoft SQL Server 2017 (RTM-GDR) (KB5029375) - 14.0.2052.1 (X64) Aug 1 2023 11:40:43 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)
April 25, 2024 at 7:32 pm
Something to note - if you HAVE indexed views in place, you can update your queries to use query hints in Standard that will use the indexes on the view. Just Enterprise grabs those automatically and Standard requires you to explicitly ask for them.
From what I've seen, Enterprise (Developer as I don't have enterprise licenses) performs nearly the same as Standard under all the workloads I have. You may have workload that requires enterprise (like resource governor) or you have boatloads of CPU cores and/or RAM.
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.
April 25, 2024 at 7:43 pm
Out of curiosity, our 2017 box shows a CPU cap of 25% with 4 CPUs which leads me to believe that it's not able to use all 4 CPUs and it's CPU bound. How can I test the max limit of CPU usage in SQL Server and see if it's able to use all CPUs.
April 25, 2024 at 8:01 pm
Out of curiosity, our 2017 box shows a CPU cap of 25% with 4 CPUs which leads me to believe that it's not able to use all 4 CPUs and it's CPU bound. How can I test the max limit of CPU usage in SQL Server and see if it's able to use all CPUs.
Like 5 years ago there was a topic in these Forums about a query to load test the CPU. Summarizing across large sequences could be appropriate
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply