VMWare CPU Limit effect on VM computing and disk speed – CPU stolen time

Symptoms:
Customer complaining about slow SQL server and event application timeout.
Hihg CPU usage and slow disk with bad disk time response.
Let’s check the the 4 CPU core:
Hihg CPU usage at night

At first glance, it seemed like a heavily used server.
The lack of server resources caused timeouts for the applications.

I suspect VMWare virtualization cause performance issue:

VMWare Virtualization CPU Stolen time — CPU Stolen time is high

Thanks for the teamwork, we found the settings which caused a poor performance: Continue reading “VMWare CPU Limit effect on VM computing and disk speed – CPU stolen time”

1st July 20209th July 2021

DBATools – powershell

Taking notes – some example:

Download and Install to my computer:

https://dbatools.io/download/

Set-PSRepository -Name PSGallery -InstallationPolicy Trusted

Install-Module dbatools

if necessary:
update-module dbatools

OFFLINE:

Copy this directory to offline server:
C:\Program Files\WindowsPowerShell\Modules\dbatools

Set-Location ‘C:\Program Files\WindowsPowerShell\Modules\dbatools’ ##.\install.ps1 ## with internet access
Get-ChildItem -Recurse | Unblock-File

Continue reading “DBATools – powershell”

1st February 20201st February 2020

Install SQL Server 2019 Developer Edition

Install SQL Server 2019 Developer Edition – download ISO image and install it offline.

You can download the installer file from:

Start the downloaded SQL2019-SSEI-Dev.exe in an administrator command prompt with the following parameter:

SQL2019-SSEI-Dev.exe /action=download

1	SQL2019-SSEI-Dev.exe /action=download

So you can download the ISO image:

SQL Server Developer Edition ISO image download

17th January 20201st February 2020

Test your MAXDOP – SQL Server max degree of parallelism

The default value of your SQL server is MAXDOP=0. It means there is no limit for parallelism. Your query can use all of your CPU core.

Scenario:
If your SQL Server is not used, than your CPU utilization is low,
your query can use all of the server’s resources.
I run a cross join query in a table which has 40.000 rows.
The server has 8 core.
I set hint for the query to simulate the MAXDOP server setting.

The query and the result:

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 8) 
--  result 33520 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 6)  
-- result 43461 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 4) 
-- result 59837 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 3) 
-- result 80219 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 2) 
-- result 116805 ms

SELECT SUM(CONVERT(BIGINT, o1.szamolo) + CONVERT(BIGINT, o2.szamolo) )
FROM szamolotabla o1
CROSS JOIN szamolotabla o2  option (maxdop 1)  
-- result 232825