Ygthkb

How long has this character been impersonating a Starfleet Officer?

How can guns be countered by melee combat without raw-ability or exceptional explanations?

Why did Luke use his left hand to shoot?

Plausible reason for gold-digging ant

How bad is a Computer Science course that doesn't teach Design Patterns?

How can I differentiate duration vs starting time

Was there a pre-determined arrangement for the division of Germany in case it surrendered before any Soviet forces entered its territory?

Taking an academic pseudonym?

How can find the 2D Voronoi cell area distribution?

Is .NET Framework 3.5 still needed with a SQL Server 2017 installation to utilize Database Mail?

Other than edits for international editions, did Harry Potter and the Philosopher's Stone receive errata?

Equivalent of "illegal" for violating civil law

Word for something that's always reliable, but never the best?

How can I deduce the power of a capacitor from its datasheet?

Democratic Socialism vs Social Democracy

How can I give a Ranger advantage on a check due to Favored Enemy without spoiling the story for the player?

How do dictionaries source attestation?

Is the percentage symbol a constant?

How do I narratively explain how in-game circumstances do not mechanically allow a PC to instantly kill an NPC?

Why are mages sometimes played bot instead of traditional ADCs?

Was Claire Dearing blamed for any of Jurassic World's failings?

Is the fingering of thirds flexible or do I have to follow the rules?

Is it legal to point a domain to someone else's ip (website)?

Why is "rm -r" unable to delete this folder?

SQL Server CPU Issues

SQL Server Connectivity IssuesA Difficult Case of “A transport-level error has occurred when receiving results from the server” SQL ServerSQL Server Memory IssuesCannot tune database any further; what next?MS SQL Server Express CPUSQL Server 2016 slow restoreBest Way To Migrate Existing Production Database To New Physical Storage StructureUnderutilized CPU on SQL serverHow do I change a processor count on a stopped SQL Server instance?SQL Server Blocking issues

0

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:

DECLARE @Loops INT SET @Loops = 1

DECLARE @CPU INT SET @CPU = @@CPU_BUSY

DECLARE @StartDate DATETIME SET @StartDate = GETDATE()



WHILE @Loops <= 100000

BEGIN

    IF COALESCE('123', '456') = '456'  

            PRINT 1

    SET @Loops = @Loops + 1   

END



PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)  

PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE())) 

PRINT ''   

GO

On Production machine, 16 Virtual Processors (20 to 30% busy), we get the following result:

Total CPU time: 1150

Total milliseconds: 13473

On test machine, 4 Virtual Processors (also around 20 to 30% busy) we get the following:

Total CPU time: 2

Total milliseconds: 1183

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?

Note the 'problem' database is running always on Availability Group, we are wondering if that may be a factor here.

sql-server

share

asked 6 mins ago

PatrickPatrick

1563

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Have you checked MAXDOP and Cost Threshold for Parallelism setting on your SQL server?
  
  – Learning_DBAdmin
  5 mins ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  No I haven't don't really see how they are relevant. Will SQL Server be parallelizing that testcase?
  
  – Patrick
  27 secs ago
  
  
  

  
  
  
  

add a comment | 

0

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:

DECLARE @Loops INT SET @Loops = 1

DECLARE @CPU INT SET @CPU = @@CPU_BUSY

DECLARE @StartDate DATETIME SET @StartDate = GETDATE()



WHILE @Loops <= 100000

BEGIN

    IF COALESCE('123', '456') = '456'  

            PRINT 1

    SET @Loops = @Loops + 1   

END



PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)  

PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE())) 

PRINT ''   

GO

On Production machine, 16 Virtual Processors (20 to 30% busy), we get the following result:

Total CPU time: 1150

Total milliseconds: 13473

On test machine, 4 Virtual Processors (also around 20 to 30% busy) we get the following:

Total CPU time: 2

Total milliseconds: 1183

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?

Note the 'problem' database is running always on Availability Group, we are wondering if that may be a factor here.

sql-server

share

asked 6 mins ago

PatrickPatrick

1563

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Have you checked MAXDOP and Cost Threshold for Parallelism setting on your SQL server?
  
  – Learning_DBAdmin
  5 mins ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  No I haven't don't really see how they are relevant. Will SQL Server be parallelizing that testcase?
  
  – Patrick
  27 secs ago
  
  
  

  
  
  
  

add a comment | 

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:

DECLARE @Loops INT SET @Loops = 1

DECLARE @CPU INT SET @CPU = @@CPU_BUSY

DECLARE @StartDate DATETIME SET @StartDate = GETDATE()



WHILE @Loops <= 100000

BEGIN

    IF COALESCE('123', '456') = '456'  

            PRINT 1

    SET @Loops = @Loops + 1   

END



PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)  

PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE())) 

PRINT ''   

GO

On Production machine, 16 Virtual Processors (20 to 30% busy), we get the following result:

Total CPU time: 1150

Total milliseconds: 13473

On test machine, 4 Virtual Processors (also around 20 to 30% busy) we get the following:

Total CPU time: 2

Total milliseconds: 1183

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?

Note the 'problem' database is running always on Availability Group, we are wondering if that may be a factor here.

sql-server

share

asked 6 mins ago

PatrickPatrick

1563

DECLARE @Loops INT SET @Loops = 1

DECLARE @CPU INT SET @CPU = @@CPU_BUSY

DECLARE @StartDate DATETIME SET @StartDate = GETDATE()



WHILE @Loops <= 100000

BEGIN

    IF COALESCE('123', '456') = '456'  

            PRINT 1

    SET @Loops = @Loops + 1   

END



PRINT 'Total CPU time: ' + CONVERT(varchar, @@CPU_BUSY - @CPU)  

PRINT 'Total milliseconds: ' + CONVERT(varchar, DATEDIFF(ms, @StartDate, GETDATE())) 

PRINT ''   

GO

Total CPU time: 1150

Total milliseconds: 13473

Total CPU time: 2

Total milliseconds: 1183

share

asked 6 mins ago

PatrickPatrick

1563

share

asked 6 mins ago

PatrickPatrick

1563

asked 6 mins ago

PatrickPatrick

1563

asked 6 mins ago

PatrickPatrick

1563