Last updated on August 1st, 2022 at 02:50 pm
Not long ago, I was dealing with a performance problem involving a very simple SQL Server query. To give you an idea about what I mean, this query took over six hours to complete! This didn’t make any sense to me especially when its “twin” query took only 25-seconds to complete. After a lot of troubleshooting, I began to wonder, “Does the SQL Server database compatibility level matter?”
This blog post will describe the problem, how I eventually discovered the solution and finally was able to reduce the query’s time down to 29-seconds!
Background
I created an Asset Intelligence (AI) dashboard report set and it worked perfectly in my small lab of 40+ computers. When I tested the same dashboard, however, in a 20,000+ computer environment, it kept timing out after about 30 minutes.
At this point I decided to test the queries, so I ran each query directly on the server. As I explained earlier, one of the queries took over six hours to complete. Next, I stripped this troublesome query to the bare minimum, ignoring SCCM query guidelines. The super-simplified query then took less than a minute to run!
Let’s look at these two queries. This way I can refer to them as Query 1 and Query 2 from this point on.
You can see that these two queries are essentially the same. The only difference is that one has a join for dbo.v_R_System_Valid and the other one doesn’t.
In order to get accurate results, views should always be joined with v_R_System_Valid. If not, the results will include inactive and deleted computers. For more details, please see my blog post entitled, Get the Most Accurate and Up-to-Date Data Using the v_R_System_Valid SQL Query in Configuration Manager 2012.
Query 1 Results
As I explained earlier, I ran this query on the SQL Server. I did it, again, and let it run overnight in order to show you exactly how long it took. You can see from the screenshot above that the execution of Query 1 took 6 hours, 8 minutes and 28 seconds or 22,108 seconds. Gasp! Are you serious?
Query 2 Results
Next, I ran the super-simplified Query 2. It only took 31-seconds.
What was my take-away from this test? In a nutshell, using v_R_System_valid added 22,077 seconds to the query’s execution time. That can’t be right!
Troubleshooting
Where to begin? I did everything including adding more RAM to BOTH the SQL Server and the VM itself. As well, I added more vCPUs.
I confirmed that the VHD was healthy and I updated the statistics on the SQL Server database by running sp_updatestats. Of course I re-indexed the database using Ola Hallengren’s SQL Server Maintenance tool. You can read more about how to install this tool here: Installation Guide to Ola Hallengren’s SQL Server Maintenance Solution.
If you were at the Midwest Management Summit in May, you might have seen Benjamin Reynolds’ and Steve Thompson’s presentation on SQL performance. Since Ben gave a great session at MMS, I asked him to run a few tests for me. His results for both queries were under six minutes each for 300,000+ computers! I was starting to get seriously frustrated. Why was the query within my environment taking so long?
SQL Server Database Compatibility Level
I ended up walking away from this problem for a bit. Then it hit me that the SQL Server database was originally a SQL Server 2012 database, but the server that I’m now using is SQL Server 2016. That shouldn’t make a big difference, but I tested it anyway. WOW was I ever shocked!
Simply by changing the database compatibility level from SQL Server 2012 to SQL Server 2016, I was able to get the query’s execution time down to something much more reasonable!
Query 1 Results
The execution time of Query 1 is now 29 seconds. That’s 22,079 (22,108 – 29) seconds faster!
Query 2 Results
You can see from above that the execution time for Query 2 is only five seconds! That is 26-seconds faster than my original time.
I wanted to prove that the SQL Server database compatibility level was indeed the problem, so I changed the compatibility level back and re-ran the queries. In both cases the queries returned back to their original times! In answer to my question, “Does the SQL Server database compatibility level matter?” Yes, it does matter.
I can’t say that I have ever seen it make such a difference before, but…
As a side note, I also tested the compatibility level with v_R_System instead of v_R_System_Valid and the results were similar. I still don’t think that adding v_R_System_Valid to a query should increase the execution time from five to 29-seconds. In my opinion, perhaps one or two seconds at best, but for now, 29-seconds is a HUGE improvement over 22,000 seconds!
Here’s my lesson:
If you ever upgrade your SQL Server don’t forget to upgrade the SQL Server database compatibility level too.
How to Change the SQL Server Database Compatibility Level
It’s really simple to change the database compatibility level. In SQL Server Management Studio (SSMS), right-click on the database name, select Properties, select the Options node, click on the drop-down next to Compatibility level and select the level that matches your SQL Server. In my case, it’s SQL Server 2016 (130). Finally, click on the OK button. That’s it! Trust me when I say that you will see the performance difference ASAP! By the way, there is no need to restart the SQL Server.
If you have any questions, please feel free to contact me @GarthMJ.