We've inherited some SQL server 2014 databases, they are running on Azure IaaS. We're getting complaints about performance and trying to get a grip on it. We're starting to look at CPU performance, and run the following test (all tests are running on database machine itself, ie not via network):
SET NOCOUNT ON DECLARE @Loops INT SET @Loops = 1 DECLARE @RESULT INT WHILE @Loops <= 2000000 BEGIN IF COALESCE('123', '456') = '456' SET @RESULT = 1 SET @Loops = @Loops + 1 END GO
On Production machine, 16 Virtual Processors (20 to 30% busy), we get the following result:
Completes in 3 minutes
On test machine, 4 Virtual Processors (also around 20 to 30% busy) we get the following:
Completes in 1 second
I've tried running a few benchmarks on the production machine, but they don't show any problems, and always show the production machine CPU at least twice as powerful as test machine.
Is there some SQL Server setting that might be configured wrongly? Any idea how to troubleshoot this issue?
Other Infrastructure Information:
- Both databases have
- "max degree of parallelism"set to 1
- "cost threshold for parallelism" set to 5
- Azure VM storage is Premium SSD (P30)
- Production (slow) is Standard_D14_v2
- Test (fast) is Standard_D12_v2
- Data, temp and logs are separated
- Data is striped across 3 striped P20 or P30
- temp on 4 striped P20 or P30
- log on single P20 or P30
Note the 'problem' database is running always on Availability Group, we are wondering if that may be a factor here.