Sort by:
View:
  • Home
  • Posts Tagged "Microsoft"

Tag: Microsoft

https://blog.mehedy.com/wp-content/uploads/2024/03/RPC-960x696_c.webp

Simplifying RPC Testing with PowerShell Scripts: A Step-by-Step Guide


Welcome to our comprehensive guide on Simplifying RPC Testing with PowerShell effectively. PowerShell scripts are powerful tools for managing server infrastructure efficiently. In this tutorial, we’ll delve into the intricacies of RPC (Remote Procedure Call) scripts, exploring how to automate server tasks seamlessly.

In today’s dynamic IT landscape, ensuring the seamless functioning of Remote Procedure Call (RPC) services is paramount. However, manually testing RPC connections across various ports can be time-consuming and prone to errors. Fortunately, PowerShell scripts offer a robust solution to automate this process, allowing IT professionals to conduct thorough Simplifying RPC Testing effortlessly.

Unlocking the Potential of PowerShell RPC Scripts

Here, we present two PowerShell scripts – Start-RpcServices.ps1 and Test-RpcPorts.ps1 – designed to streamline RPC testing. Let’s delve into each script’s functionality and how to effectively utilize them.

Start-RpcServices.ps1

This script initiates RPC services on random ports and sends the generated port list to a testing server for verification.

Script Explanation:

  1. Test-ServerReachability Function: Checks the reachability of the testing server using Test-Connection cmdlet.
  2. Prompt for Testing Server Hostname: Prompts the user to enter the hostname of the testing server until a reachable server is provided.
  3. Prompt for Number of Random Ports: Prompts the user to enter the number of random ports to generate within the valid range (1 to 65535).
  4. Start RPC Services: Initiates RPC listeners on random ports and displays the port numbers.
  5. Send Port List to Testing Server: Saves the generated port list and hostname to the testing server’s share path.
  6. Stop RPC Services: Waits for a keystroke to stop the RPC services and closes the listeners.

How to Use the Script:

  1. Run the script.
  2. Enter the hostname of the testing server.
  3. Specify the number of random ports to generate.
  4. Press any key to stop the RPC services once testing is complete.

Test-RpcPorts.ps1

This script tests RPC connections on the specified ports against the testing server.

Script Explanation:

  1. Read Hostname and Ports: Retrieves the hostname and random ports from the testing server’s share path.
  2. Test RPC Connection: Attempts to establish RPC connections on each port and displays the connection status.

How to Use the Script:

  1. Run the script.
  2. Ensure the hostname and port list files are available in the specified share path.
  3. The script will automatically test RPC connections and display the results.

By leveraging these PowerShell scripts, IT professionals can efficiently validate RPC services, enhancing network reliability and minimizing downtime.

Now, let’s make RPC testing a breeze with the provided scripts:

Start-RpcServices.ps1:

# Start-RpcServices.ps1

# Function to test reachability of the server
function Test-ServerReachability {
param([string]$serverName)
$pingResult = Test-Connection -ComputerName $serverName -Count 1 -Quiet
return $pingResult
}

# Prompt for the testing server hostname
do {
$testingServerHostname = Read-Host "Enter the hostname of the testing server"
$serverReachable = Test-ServerReachability -serverName $testingServerHostname

if (-not $serverReachable) {
Write-Host "Server '$testingServerHostname' is not reachable. Please provide a reachable server."
}
} until ($serverReachable)

# Prompt for the number of random ports
do {
$portCount = Read-Host "Enter the number of random ports to generate (between 1 and 65535)"
if (-not ($portCount -as [int])) {
Write-Host "Please enter a valid number."
continue
}
if ($portCount -lt 1 -or $portCount -gt 65535) {
Write-Host "Please enter a number between 1 and 65535."
}
} until ($portCount -as [int] -ge 1 -and $portCount -as [int] -le 65535)

# Start RPC services on dynamic ports
$rpcDynamicPortRange = 49152..65535

# Select random ports based on user input
$randomPorts = Get-Random -InputObject $rpcDynamicPortRange -Count $portCount

# Start RPC listeners on each random port
$rpcListeners = @{}
foreach ($port in $randomPorts) {
$rpcListener = [System.Net.Sockets.TcpListener]::new([System.Net.IPAddress]::Any, $port)
$rpcListener.Start()
$rpcListeners[$port] = $rpcListener
Write-Host "RPC service started on port $port"
}

# Send the list of generated ports to Testing Server's C$ share path
$randomPorts | Out-File -FilePath "\\$testingServerHostname\C$\RandomPorts.txt" -Force

# Save the hostname to the Testing Server's C$ share path
$hostname = hostname
$filePath = "\\$testingServerHostname\C$\Hostname.txt"
$hostname | Out-File -FilePath $filePath -Force

# Wait for a keystroke to stop the RPC services
Write-Host "Press any key to stop the RPC services..."
$null = $Host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown")

# Close the listeners
foreach ($rpcListener in $rpcListeners.Values) {
$rpcListener.Stop()
Write-Host "RPC service stopped on port $($rpcListener.LocalEndpoint.Port)"
}
Write-Host "All RPC services stopped"

Test-RpcPorts.ps1:

# Test-RpcPorts.ps1

# Read the hostname of the testing server from the C$ share path
$filePath1 = "C$\Hostname.txt"
$ServerName = Get-Content -Path "\\$env:COMPUTERNAME\$filePath1"
$targetServer = $ServerName

# Read the Ports to check from the C$ share path
$filePath2 = "C$\RandomPorts.txt"
$randomPorts = Get-Content -Path "\\$env:COMPUTERNAME\$filePath2"

# Test RPC connection for each random port
foreach ($port in $randomPorts) {
    try {
        $client = New-Object System.Net.Sockets.TcpClient($targetServer, $port)
        $client.Close()
        Write-Host "RPC connection successful to $targetServer on port $port"
    } catch {
        Write-Host "RPC connection failed to $targetServer on port $port"
    }
}

Empower your IT infrastructure with streamlined RPC testing using PowerShell automation. Say goodbye to manual testing hassles and embrace efficiency in network management.

Ready to simplify RPC testing? Execute these scripts and experience the difference firsthand!

https://blog.mehedy.com/wp-content/uploads/2017/04/image007-960x745_c.png

Why Increase MAX Worker Threads of SQL Server ?


When Should We Increase The Max Worker Threads?

Why will we Increase MAX Worker Threads ? When all are active with long running queries, SQL Server might appear unresponsive until a worker thread completes and becomes available.

If SQL Server reaches its maximum capacity of 960 threads [for 32 processors / settings default value to 0] and does not take any more connections as it has run out of worker threads SQL Server may seem unresponsive.

It is not a defect of SQL Server but to improve SQL server performance. One should consider using Indexes in SQL Query which is running, or measure the Query cost and improve index or query being executed.

If nothing can be done from the application or query end, you can manually set the worker thread to a larger number, but it will significantly slow down your SQL Server, as now it will use Worker Thread more than your CPU can handle. Or you might consider adding processor resources to your Server.

Use Below Tsql To Monitor And Set Max Worker Threads As Per Your Requirement.

USE master; //choose the database you want to set max worker threads for. master database will set it at instance level.
GO
EXEC sp_configure 'show advanced option'//shows the current value of the advanced options
GO
EXEC sp_configure 'show advanced option', '1'; //enables the advanced options
GO
RECONFIGURE WITH OVERRIDE; //reconfigure the change
GO
EXEC sp_configure 'show advanced option' //verify that the advanced options have been enabled
GO
EXEC sp_configure //shows all the advanced options available
GO
EXEC sp_configure 'max worker threads' //shows the current value of max worker threads
GO
EXEC sp_configure 'max worker threads', 3500; //choose the value appropriately 
GO
RECONFIGURE WITH OVERRIDE; //reconfigure the change
GO 
EXEC sp_configure 'max worker threads' //verify that max worker threads reflect the new value
GO
EXEC sp_configure 'show advanced option', '0'; //disable the advanced options
GO
RECONFIGURE WITH OVERRIDE; //reconfigure the change
GO

To See The Current Connections And Current Max Worker Threads.

SELECT
DB_NAME(dbid) as DBName,
COUNT(dbid) as NumberOfConnections,
loginame as LoginName
FROM
sys.sysprocesses
WHERE
dbid > 0
GROUP BY
dbid, loginame

SELECT SUM(current_workers_count) as [Current worker thread] FROM sys.dm_os_schedulers

Do Lots Of Connections Slow Down Your Sql Server? [ Increase MAX Worker Threads ]

I sometimes hear database administrators say, “This database must be involved in our performance problem: it has too many connections.”

Lots of connections might cause a problem for your SQL Server, or it might not. The good news is that there’s a way to clearly tell if they’re dragging down performance or not.

How Can I Prove That Lots Of Connections Are Causing A Performance Problem?

SQL Server can manage a lot of connections without too many issues. Yes, it’s better if an application cleans up its connections over time. We recommend tracking the User Connections performance counter, as patterns in this counter may coincide with other events and be useful information.

But just having a high amount of connections doesn’t prove that was the cause of performance problems. For a smoking gun as to why SQL Server is slow, look in two places:

  • SQL Server Wait Statistics (look for THREADPOOL waits)
  • The SQL Server Error Log (look for the creepy error below)

Threadpool Waits: A Sign Of Trouble

When lots of connections want to run queries at the same time, SQL Server may need to allocate more worker threads. This process can be slow, and at a certain point SQL Server may reach the maximum worker threads allowed for your configuration.

If this happens on your server, when you query your wait statistics since startup.

“New Queries Have Not Been Picked Up By A Worker Thread”: Thread Starvation

When this gets really bad, you may see a nasty error message in your SQL Server Error Log. This message says something like this:

New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 60 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the “max worker threads” configuration option to increase number of allowable threads, or optimize current running queries.

The message neglects to mention a few critical things:

  • Microsoft doesn’t recommend that you change max worker threads, as a general rule
  • The amount of worker threads needed has a lot to do with your parallelism settings (and changing parallelism settings is far more common than the amount of worker threads)
  • Periodic problems like locking and blocking can also drive up worker threads. Raising worker threads might just mask the real root cause of a blocking problem!

What To Do If You Have Threadpool Waits Or Thread Starvation

First, don’t panic. Avoid changing any settings before you know exactly what they’ll do.

Here’s where to start:

How’s your parallelism? If you have the “max degree of parallelism” setting at the default value of 0 (or a very high number), a good first step is to learn about CXPACKET waits and options for configuring parallelism.

Do you have lock waits? If you have high lock waits (query), consider which tables have the most blocking and how to track down the queries involved.

Have you identified the most frequently run queries in the problem period? Good indexing or improved TSQL for those queries can dramatically reduce needed threads.

What do the perf counters say? Take a look at those performance counters we recommend to see if there’s a jump in the number of connections when the problem occurs or not.

Approaching the problem this way dispels the mystery of whether all those connections really are the problem (or not).

https://blog.mehedy.com/wp-content/uploads/2017/04/image001-960x426_c.png

Max Worker Threads – SQL Server


What Is A Worker Threads In SQL Server?

Worker threads are the agents of SQL Server which are scheduled in CPU and they carry out the tasks. Memory for Worker threads come from Non-Buffer Pool region of SQL Server. – MAX Worker Threads

Purpose Of Max Worker Thread Option

Thread pooling helps optimize performance when large numbers of clients are connected to the server. Usually, a separate operating system thread is created for each query request. However, with hundreds of connections to the server, using one thread per query request can consume large amounts of system resources. The max worker threads option enables SQL Server to create a pool of worker threads to service a larger number of query request,

The max worker threads option configures the number of worker threads that are available to SQL Server processes. The default value for max worker threads is 0. This enables SQL Server to automatically configure the number of worker threads at startup. The default setting is best for most systems. However, depending on your system configuration, setting max worker threads to a specific value sometimes improves performance.

If you leave ‘Max. Worker threads’ to 0 then SQL Server will decide the worker thread count based on formula below:

Max Worker Threads For 32 bit operating system:

Total available logical CPU’s <= 4 : max worker threads = 256

Total available logical CPU’s > 4 : max worker threads = 256 + ((logical CPUS’s – 4) * 8)

Max Worker Threads For 64 bit operating system:

Total available logical CPU’s <= 4 : max worker threads = 512

Total available logical CPU’s > 4 : max worker threads = 512 + ((logical CPUS’s – 4) * 16)

 

Before You Begin

Limitations and Restrictions

  • When the actual number of query requests is less than the amount set in max worker threads, one thread handles each query request. However, if the actual number of query request exceeds the amount set, SQL Server pools the worker threads so that the next available worker thread can handle the request.

Recommendations

  • This option is an advanced option and should be changed only by an experienced database administrator or certified SQL Server technician.
  • Thread pooling helps optimize performance when large numbers of clients are connected to the server. Usually, a separate operating system thread is created for each query request. However, with hundreds of connections to the server, using one thread per query request can consume large amounts of system resources. The max worker threads option enables SQL Server to create a pool of worker threads to service a larger number of query requests, which improves performance.
  • The following table shows the automatically configured number of max worker threads for various combinations of CPUs and versions of SQL Server.
Number of CPUs32-bit computer64-bit computer
<= 4 processors256512
8 processors288576
16 processors352704
32 processors480960
64 processors7361472
128 processors42244480
256 processors83208576

 

  • When all worker threads are active with long running queries, SQL Server might appear unresponsive until a worker thread completes and becomes available. Although this is not a defect, it can sometimes be undesirable. If a process appears to be unresponsive and no new queries can be processed, then connect to SQL Server using the dedicated administrator connection (DAC), and kill the process. To prevent this, increase the number of max worker threads.

If You Have Set Max. Worker Thread To 0, You Can Check The Worker Thread Count Calculated By Sql Server Using The Query

SELECT max_workers_count FROM sys.dm_os_sys_info

Max Worker Thread

When a request is received, SQL Server will try to locate a idle thread to schedule the request. If an idle worker was not located, new worker will be created. If all workers have been created and none is idle, then the request is queued which will be picked up by any worker thread from the pool of worker threads created.

Query Below Gives The Amount Of Worker Threads Created At The Moment In Your Sql Server:

SELECT SUM(current_workers_count) as [Current worker thread] FROM sys.dm_os_schedulers

Current Worker Thread Query

General recommended to leave Max. worker threads to 0.

Configure Using Sql Server Management Studio

To configure the max worker threads option

  • In Object Explorer, right-click a server and select Properties.
  • Click the Processors node.
  • In the Max worker threads box, type or select a value from 128 through 32767.

Configure Max Worker Threads - Using Sql Server Management Studio

Configure Max Worker Threads - Using Sql Server Management Studio

 

Use the max worker threads option to configure the number of worker threads available to SQL Server processes. The default setting for max worker threads is best for most systems. However, depending on your system configuration, setting max worker threads to a smaller value sometimes improves performance.

Configure Using Transact-Sql

To configure the max worker threads option

  • Connect to the Database Engine.
  • From the Standard bar, click New Query.
  • Copy and paste the following example into the query window and click Execute. This example shows how to use sp_configure to configure the max worker threads option to 900.

Tsql

USE DATABASE-NAME ;
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'max worker threads', 900 ;
GO
RECONFIGURE;
GO

 

The change will take effect immediately without requiring the Database Engine to restart.

Menu