Windows – Run SQL Server BLOG

7th September 20217th September 2021

DISTRIBUTED TRANSACTION: DTC in SQL server

There is two SQL servers and DTC is enabled both of them.

One SQL server is local and another one is a linked server named test.

Run a Distributed Transaction in SQL server for test purpose:

USE AdventureWorks2014;  

Select * from  [AdventureWorks2014].[Production].[WorkOrder] 
where WorkOrderID <100



USE AdventureWorks2014;  
GO  
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION;  
-- Delete candidate from local instance.  
DELETE [AdventureWorks2014].[Production].[WorkOrder]
     WHERE WorkOrderID = 2;  
-- Delete candidate from remote instance.  
DELETE test.[AdventureWorks2014].[Production].[WorkOrder]
      WHERE WorkOrderID = 2;   
COMMIT TRANSACTION;  
GO

USE AdventureWorks2014;

Select * from [AdventureWorks2014].[Production].[WorkOrder]

where WorkOrderID <100

USE AdventureWorks2014;

SET XACT_ABORT ON

BEGIN DISTRIBUTED TRANSACTION;

-- Delete candidate from local instance.

DELETE [AdventureWorks2014].[Production].[WorkOrder]

WHERE WorkOrderID = 2;

-- Delete candidate from remote instance.

DELETE test.[AdventureWorks2014].[Production].[WorkOrder]

WHERE WorkOrderID = 2;

COMMIT TRANSACTION;

after running the T-SQL transaction you can check in the DTC report.

you can check the DTC connection and firewall settings:

DTC ping tool

In powershell:

Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -Verbose
Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -RemoteComputerName "SERVER0058" -ResourceManagerPort 17100 -Verbose
Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -RemoteComputerName "SERVER0059"  -ResourceManagerPort 17100 -Verbose
Test-Dtc -RemoteComputerName "SERVER0059"  -ResourceManagerPort 17100 -Verbose
Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -RemoteComputerName "APPSERVER418"

Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -Verbose

Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -RemoteComputerName "SERVER0058" -ResourceManagerPort 17100 -Verbose

Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -RemoteComputerName "SERVER0059" -ResourceManagerPort 17100 -Verbose

Test-Dtc -RemoteComputerName "SERVER0059" -ResourceManagerPort 17100 -Verbose

Test-Dtc -LocalComputerName "$env:COMPUTERNAME" -RemoteComputerName "APPSERVER418"

If the remote server is another domain, you will get an error:
New-CimSession : WinRM cannot process the request. The following error with errorcode 0x80090322 occurred while using Kerberos authentication: An unknown security error occurred.

In this case you can set: No Authentication Required.

7th September 20217th September 2021

DTC and Java

How to enable DTC in SQL server for Java:

– copy sqljdbc_xa.dll to sql server binn folder from the EXTRACTED (but not installed) jdbc driver
– enable xa transactions in component services > computerts > Distributed Transaction Coordinator > Local DTC (properties, security tab) Enable XA transactions
– restart sql server (all components to be sure)
– run script on master db. (xa_install.sql it is also in the jdbc driver, next to the .sql)
– the user that will perform distributed transactions must have the role “SqlJDBCXAUser” set on the MASTER db!

20th August 202115th September 2021

Windows Performance tools

install WPT on the affected host. WPT is part of ADK. Or they can just download this archive – extract it on the server into C:\msdata folder. They should have at least 5Gb free space on that disk
Run elevated CMD and execute:

#perf_counters

logman delete %computername%_IO

logman create counter %computername%_IO -c “\LogicalDisk(*)\*” “\Memory\*” “\Network Adapter(*)\*” “\Network Interface(*)\*” “\Paging File(*)\*” “\PhysicalDisk(*)\*” “\Process(*)\*” “\Processor Information(*)\*” -max 512 -f bincirc -si 00:00:01 -ow

logman start %computername%_IO

#etw_trace

C:\msdata\xperf -on PROC_THREAD+LOADER+PROFILE+INTERRUPT+DPC+FILENAME+FILE_IO+FILE_IO_INIT+FLT_IO_INIT+FLT_IO+FLT_FASTIO+FLT_IO_FAILURE+DISK_IO+DISK_IO_INIT -stackwalk Profile+FileCreate+FileCleanup+FileClose+FileRead+FileWrite+FileSetInformation+FileDelete+FileRename+FileDirEnum+FileFlush+FileQueryInformation+MiniFilterPreOpInit+MiniFilterPostOpInit+DiskReadInit+DiskWriteInit+DiskFlushInit -BufferSize 1024 -MinBuffers 256 -MaxBuffers 2048 -MaxFile 4096 -FileMode Circular

In the folder C:\msdata create data_stop.cmd with following content:

logman stop %computername%_IO

C:\msdata\xperf -d C:\msdata\io.etl

Then ask customer to find an event id 17883 in the application event log, do right click -> attach and “attach task to this event”, in action for the task point it to the script – C:\msdata\data_stop.cmd
Also make sure following options set for the task:
Run whehet user is logged on or not
Runt with Hihgest privileges

Test it, write to the app event log:
Write-EventLog -LogName Application -EntryType Error -Source MSSQLSERVER -EventId 17883 -Message “TEST ERROR EVENT – PLEASE IGNORE IT.”

Test if they are running:

xperf -loggers “NT Kernel Logger”
or
logman query “NT Kernel Logger” -ets

If session exist – trace is running.

9th August 2021

SQL server or agent or any other login can access a fileshare?

Yes, but you need to check:

SELECT CURRENT_USER;  
xp_cmdshell 'whoami';

EXECUTE AS user = 'domain\user'
SELECT CURRENT_USER;  
xp_cmdshell 'whoami'
xp_cmdshell 'dir \\server\sharename'

Revert; -- exit from current user 
SELECT CURRENT_USER;

SELECT CURRENT_USER;

xp_cmdshell 'whoami';

EXECUTE AS user = 'domain\user'

SELECT CURRENT_USER;

xp_cmdshell 'whoami'

xp_cmdshell 'dir \\server\sharename'

Revert; -- exit from current user

SELECT CURRENT_USER;

19th October 202019th October 2020

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”

30th April 2018

Szabad tárhely az SQL szerveren

Tárolt eljárás, amely ütemezhető rendszeres ellenőrzésre:
Continue reading “Szabad tárhely az SQL szerveren”

24th April 201824th April 2018

Powershell 6 telepítése

Powershell telepítése:

Első lépésként telepítsük a .Net Framework-ot.
Jelenleg a legutolsó verzió 4.7.1

Majd töltsük le a nyílt forráskódú telepítőt: https://github.com/PowerShell/Powershell

Windows verzió: PowerShell-6.0.2-win-x64.msi

Powershell-t akár linux-ra és macOS-re is telepíthetünk a 6-os verziótól!

powershell 6 command line — powershell 6

17th April 2018

Performance Monitor for SQL Server

SQL szerver és windows os performancia figyelés:

<?xml version="1.0" encodINSTANCE="UTF-16"?>
<DataCollectorSet>
	<Status>0</Status>
	<Duration>1209600</Duration>
	<Description>
	</Description>
	<DescriptionUnresolved>
	</DescriptionUnresolved>
	<DisplayName>
	</DisplayName>
	<DisplayNameUnresolved>
	</DisplayNameUnresolved>
	<SchedulesEnabled>-1</SchedulesEnabled>
	<LatestOutputLocation>C:\PerfLogs\Admin\sql\sqlB\SQL07_20170319-000003</LatestOutputLocation>
	<Name>SQL</Name>
	<OutputLocation>C:\PerfLogs\Admin\SQL\SQLSRV01_20180307-000001</OutputLocation>
	<RootPath>%systemdrive%\PerfLogs\Admin\SQL2</RootPath>
	<Segment>0</Segment>
	<SegmentMaxDuration>0</SegmentMaxDuration>
	<SegmentMaxSize>2000</SegmentMaxSize>
	<SerialNumber>4</SerialNumber>
	<Server>
	</Server>
	<Subdirectory>
	</Subdirectory>
	<SubdirectoryFormat>3</SubdirectoryFormat>
	<SubdirectoryFormatPattern>yyyyMMdd\-NNNNNN</SubdirectoryFormatPattern>
	<Task>
	</Task>
	<TaskRunAsSelf>0</TaskRunAsSelf>
	<TaskArguments>
	</TaskArguments>
	<TaskUserTextArguments>
	</TaskUserTextArguments>
	<UserAccount>SYSTEM</UserAccount>
	<Security></Security>
	<StopOnCompletion>0</StopOnCompletion>
	<PerformanceCounterDataCollector>
		<DataCollectorType>0</DataCollectorType>
		<Name>DataCollector01</Name>
		<FileName>SQLdatacollector01</FileName>
		<FileNameFormat>3</FileNameFormat>
		<FileNameFormatPattern>yyMMdd</FileNameFormatPattern>
		<LogAppend>0</LogAppend>
		<LogCircular>0</LogCircular>
		<LogOverwrite>0</LogOverwrite>
		<LatestOutputLocation>C:\PerfLogs\Admin\sql\sqlB\</LatestOutputLocation>
		<DataSourceName>
		</DataSourceName>
		<SampleInterval>5</SampleInterval>
		<SegmentMaxRecords>0</SegmentMaxRecords>
		<LogFileFormat>3</LogFileFormat>
		<Counter>\LogicalDisk(*)\Avg. Disk Queue Length</Counter>
		<Counter>\Memory\Available MBytes</Counter>
		<Counter>\Memory\% Committed Bytes In Use</Counter>
		<Counter>\Memory\Free System Page Table Entries</Counter>
		<Counter>\Memory\Pages/sec</Counter>
		<Counter>\Memory\Pool Nonpaged Bytes</Counter>
		<Counter>\Memory\Pool Paged Bytes</Counter>
		<Counter>\System\Processor Queue Length</Counter>
		<Counter>\PhysicalDisk(*)\Avg. Disk sec/Read</Counter>
		<Counter>\PhysicalDisk(*)\Avg. Disk sec/Write</Counter>
		<Counter>\PhysicalDisk(*)\Avg. Disk sec/Transfer</Counter>
		<Counter>\PhysicalDisk(*)\Disk Reads/sec</Counter>
		<Counter>\PhysicalDisk(*)\Disk Writes/sec</Counter>
		<Counter>\Network Interface(*)\Bytes Received/sec</Counter>
		<Counter>\Network Interface(*)\Bytes Sent/sec</Counter>
		<Counter>\Network Interface(*)\Bytes Total/sec</Counter>
		<Counter>\Processor(_Total)\% Processor Time</Counter>
		<Counter>\Processor(_Total)\% Interrupt Time</Counter>
		<Counter>\Processor(_Total)\% User Time</Counter>
		<Counter>\MSSQL$INSTANCE:General Statistics\User Connections</Counter>
		<Counter>\MSSQL$INSTANCE:Memory Manager\Memory Grants PendINSTANCE</Counter>
		<Counter>\MSSQL$INSTANCE:SQL Statistics\SQL Compilations/sec</Counter>
		<Counter>\MSSQL$INSTANCE:SQL Statistics\SQL Re-Compilations/sec</Counter>
		<Counter>\MSSQL$INSTANCE:Buffer Manager\Buffer cache hit ratio</Counter>
		<Counter>\MSSQL$INSTANCE:Buffer Manager\Page life expectancy</Counter>
		<Counter>\MSSQL$INSTANCE:Locks(_Total)\Lock Waits/sec</Counter>
		<Counter>\MSSQL$INSTANCE:Locks(_Total)\Number of Deadlocks/sec</Counter>
		<Counter>\MSSQL$INSTANCE:Latches\Latch Waits/sec</Counter>
		<Counter>\MSSQL$INSTANCE:Latches\Total Latch Wait Time (ms)</Counter>
		<Counter>\MSSQL$INSTANCE:Latches\Average Latch Wait Time (ms)</Counter>
		<Counter>\MSSQL$INSTANCE:SQL Statistics\Batch Requests/sec</Counter>
		<Counter>\MSSQL$INSTANCE:Transactions\Longest Transaction RunnINSTANCE Time</Counter>
		<Counter>\Process(sqlservr)\% Privileged Time</Counter>
		<Counter>\Process(sqlservr)\% Processor Time</Counter>
		<Counter>\Process(sqlservr)\% User Time</Counter>
		<Counter>\Process(sqlservr)\Private Bytes</Counter>
		<Counter>\Process(sqlservr)\Thread Count</Counter>
		<Counter>\Process(sqlservr)\Virtual Bytes</Counter>
		<Counter>\Process(sqlservr)\Handle Count</Counter>
	</PerformanceCounterDataCollector>
	<DataManager>
		<Enabled>0</Enabled>
		<CheckBeforeRunnINSTANCE>0</CheckBeforeRunnINSTANCE>
		<MinFreeDisk>0</MinFreeDisk>
		<MaxSize>0</MaxSize>
		<MaxFolderCount>0</MaxFolderCount>
		<ResourcePolicy>0</ResourcePolicy>
		<ReportFileName>report.html</ReportFileName>
		<RuleTargetFileName>report.xml</RuleTargetFileName>
		<EventsFileName>
		</EventsFileName>
	</DataManager>
</DataCollectorSet>

100

101

102

103

104

105

<?xml version="1.0" encodINSTANCE="UTF-16"?>

</Description>

</DescriptionUnresolved>

</DisplayName>

</DisplayNameUnresolved>

<LatestOutputLocation>C:\PerfLogs\Admin\sql\sqlB\SQL07_20170319-000003</LatestOutputLocation>

<OutputLocation>C:\PerfLogs\Admin\SQL\SQLSRV01_20180307-000001</OutputLocation>

<RootPath>%systemdrive%\PerfLogs\Admin\SQL2</RootPath>

</Server>

</Subdirectory>

<SubdirectoryFormatPattern>yyyyMMdd\-NNNNNN</SubdirectoryFormatPattern>

<Task>

</Task>

</TaskArguments>

</TaskUserTextArguments>

<UserAccount>SYSTEM</UserAccount>

<Name>DataCollector01</Name>

<FileName>SQLdatacollector01</FileName>

<FileNameFormatPattern>yyMMdd</FileNameFormatPattern>

<LatestOutputLocation>C:\PerfLogs\Admin\sql\sqlB\</LatestOutputLocation>

</DataSourceName>

<Counter>\LogicalDisk(*)\Avg. Disk Queue Length</Counter>

<Counter>\Memory\Available MBytes</Counter>

<Counter>\Memory\% Committed Bytes In Use</Counter>

<Counter>\Memory\Free System Page Table Entries</Counter>

<Counter>\Memory\Pages/sec</Counter>

<Counter>\Memory\Pool Nonpaged Bytes</Counter>

<Counter>\Memory\Pool Paged Bytes</Counter>

<Counter>\System\Processor Queue Length</Counter>

<Counter>\PhysicalDisk(*)\Avg. Disk sec/Read</Counter>

<Counter>\PhysicalDisk(*)\Avg. Disk sec/Write</Counter>

<Counter>\PhysicalDisk(*)\Avg. Disk sec/Transfer</Counter>

<Counter>\PhysicalDisk(*)\Disk Reads/sec</Counter>

<Counter>\PhysicalDisk(*)\Disk Writes/sec</Counter>

<Counter>\Network Interface(*)\Bytes Received/sec</Counter>

<Counter>\Network Interface(*)\Bytes Sent/sec</Counter>

<Counter>\Network Interface(*)\Bytes Total/sec</Counter>

<Counter>\Processor(_Total)\% Processor Time</Counter>

<Counter>\Processor(_Total)\% Interrupt Time</Counter>

<Counter>\Processor(_Total)\% User Time</Counter>

<Counter>\MSSQL$INSTANCE:General Statistics\User Connections</Counter>

<Counter>\MSSQL$INSTANCE:Memory Manager\Memory Grants PendINSTANCE</Counter>

<Counter>\MSSQL$INSTANCE:SQL Statistics\SQL Compilations/sec</Counter>

<Counter>\MSSQL$INSTANCE:SQL Statistics\SQL Re-Compilations/sec</Counter>

<Counter>\MSSQL$INSTANCE:Buffer Manager\Buffer cache hit ratio</Counter>

<Counter>\MSSQL$INSTANCE:Buffer Manager\Page life expectancy</Counter>

<Counter>\MSSQL$INSTANCE:Locks(_Total)\Lock Waits/sec</Counter>

<Counter>\MSSQL$INSTANCE:Locks(_Total)\Number of Deadlocks/sec</Counter>

<Counter>\MSSQL$INSTANCE:Latches\Latch Waits/sec</Counter>

<Counter>\MSSQL$INSTANCE:Latches\Total Latch Wait Time (ms)</Counter>

<Counter>\MSSQL$INSTANCE:Latches\Average Latch Wait Time (ms)</Counter>

<Counter>\MSSQL$INSTANCE:SQL Statistics\Batch Requests/sec</Counter>

<Counter>\MSSQL$INSTANCE:Transactions\Longest Transaction RunnINSTANCE Time</Counter>

<Counter>\Process(sqlservr)\% Privileged Time</Counter>

<Counter>\Process(sqlservr)\% Processor Time</Counter>

<Counter>\Process(sqlservr)\% User Time</Counter>

<Counter>\Process(sqlservr)\Private Bytes</Counter>

<Counter>\Process(sqlservr)\Thread Count</Counter>

<Counter>\Process(sqlservr)\Virtual Bytes</Counter>

<Counter>\Process(sqlservr)\Handle Count</Counter>

</PerformanceCounterDataCollector>

<ReportFileName>report.html</ReportFileName>

<RuleTargetFileName>report.xml</RuleTargetFileName>

</EventsFileName>

</DataManager>

</DataCollectorSet>

Felesleges részek kivétele: pl Network Interface.