A poorly written query can code trouble if the SQL server cannot predict a good execution plan because of too many type conversation and varchar max parameters:
After changing the code
Dynamically generated code T-SQL code can cause trouble
Index usage statistics
Do you have unused index? Do you have ineffective index? How many times was it used for select or update?
Is it used by scan , seeks or lookups?
Index usage statistics :
|
1 2 3 4 5 6 7 8 |
-- index usage statistics SELECT d.name AS DatabaseName, t.name AS TableName, i.name AS IndexName, ius.* FROM sys.dm_db_index_usage_stats ius JOIN sys.databases d ON d.database_id = ius.database_id JOIN sys.tables t ON t.object_id = ius.object_id JOIN sys.indexes i ON i.object_id = ius.object_id AND i.index_id = ius.index_id WHERE d.database_id = (DB_ID(N'DataBaseName')) and t.name='TablesName' -- Database Name and Table ORDER BY user_updates DESC |
How to start a stored procedure when SQL server start?
You can start a stored procedure that is set to automatic execution runs every time an instance of SQL Server is started.
|
1 2 3 4 5 6 7 8 |
EXEC sp_procoption @ProcName = N'<procedure name>' , @OptionName = 'startup' /* The only value for option is startup. */ , @OptionValue = 'on'; /* (true or on) or off (false or off) */ EXEC sp_procoption @ProcName = N'<procedure name>' , @OptionName = 'startup' , @OptionValue = 'off'; |
Is there any autostart stored procedure on your SQL Server?
You can query:
|
1 2 3 |
SELECT ROUTINE_NAME FROM MASTER.INFORMATION_SCHEMA.ROUTINES WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStartup') = 1 |
Replace a MS SQL Database in AlwaysOn AG with DBATools
There is a database in AG and you need to drop it and restore from backup (which is sometimes a transition from developer to prod )
You don’t need any prerequisite for cluster nodes and AG names.
The script find the instance in local windows server and find the db AG nodes and listener.
So finally you need the database name and the location of the backup folder which can be an Ola backup solution folder.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
$db="AdventureWorks" $BackupDirectory= "\\backupserver01\mssql_bkp\SQLSERVER15\AdventureWorks" $SqlServersOnline = (Get-ClusterNode).name | Find-DbaInstance | Where-Object {$_.Availability -eq "Available" } #find AG: $AGGroup =Get-DbaAgDatabase -SqlInstance $SqlServersOnline[0] | Where-Object {$_.Name -eq $db} $SQLListener = Get-DbaAgListener -SqlInstance $SqlServersOnline[0] | Where-Object {$_.AvailabilityGroup -eq $AGGroup.AvailabilityGroup} # connect to listener to get primary and secondary $availabilityGroupReplicas = Get-DbaAgReplica -SqlInstance $SQLlistener.Name -AvailabilityGroup $AGGroup.AvailabilityGroup $PrimaryReplica = ($availabilityGroupReplicas | Where-Object role -eq 'Primary').Name $SecondaryReplica = ($availabilityGroupReplicas | Where-Object role -eq 'Secondary').Name $AvailabilityGroup = $AGGroup.AvailabilityGroup # remove db from AG-s Remove-DbaAgDatabase -SqlInstance $PrimaryReplica -AvailabilityGroup $AvailabilityGroup -Database $db Remove-DbaDatabase -SqlInstance $secondaryReplica -Database $db -Confirm:$false # check restore script with -OutputScriptOnly # Restore-DbaDatabase -SqlInstance $primaryReplica -DatabaseName $db -Path $BackupDirectory -WithReplace -MaintenanceSolution -OutputScriptOnly # usefull paratmeters if needed: -RestoreTime '2022-01-26 07:15:00' -WithReplace -OutputScriptOnly -MaintenanceSolution Restore-DbaDatabase -SqlInstance $primaryReplica -DatabaseName $db -Path $BackupDirectory -WithReplace -MaintenanceSolution # -OutputScriptOnly # check and set recovery model if needed if ( (Get-DbaDbRecoveryModel -SqlInstance $PrimaryReplica -Database $db).recoverymodel -eq "Simple") ` { Set-DbaDbRecoveryModel -SqlInstance $PrimaryReplica -Database $db -RecoveryModel Full ; Backup-DbaDatabase -SqlInstance $PrimaryReplica -Database $db -FilePath NUL } # add database to primary AG Add-DbaAgDatabase -SqlInstance $PrimaryReplica -AvailabilityGroup $AvailabilityGroup -Database $db -SeedingMode Automatic |
Setup.exe error with using ConfigurationFile.ini
SQL server setup error with using setup.exe /CONFIGURATIONFILE=D:\Install\ConfigurationFile.ini
Error message:
Error result: -2054422506
Result facility code: 1420
Result error code: 22
Please check the ini file:
Reason:
; Setup will not display any user interface.
QUIET=”True”
; Setup will display progress only, without any user interaction.
QUIETSIMPLE=”False”
; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block.
UIMODE=”Normal”
Possible Solution :
QUIET=”False”
Send email if performance monitor counter value average is high
Do you need an alert for CPU, for network?
Here is an example for one network card.
You can put in a ps1 file and run it from task scheduler.
You should set the email and SMTP addresses:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
######## task scheduler ####################### # # prereq for powershel by task scheduler : # !! - Set-ExecutionPolicy Unrestricted - !! # # user: SYSTEM # # run whereher user is logged on or not # run with highest privileges # # action: start a program # program/script powershell # add arguments (optional) : -file D:\Budapest_DBA\MonitoringDiskSpaceReporting\volumespace-monitoring-smtp-mail.ps1 # Start in (optional): D:\Budapest_DBA\MonitoringDiskSpaceReporting\ ################################################ $perfvalue= (Get-Counter -counter "\\server0056\network interface(hp nc375i integrated quad port multifunction gigabit server adapter)\bytes total/sec" -MaxSamples 3 -SampleInterval 5 | select -expand CounterSamples | Measure-Object -average cookedvalue).Average Write-Output $perfvalue if( $perfvalue -ge 5000){ $body ="Automata monitoring alert email. Network Traffic is high, maybe take a look:<br> \Network Interface(*)\Bytes Total/sec value:<b> $perfvalue </b><br> Gábor" Send-MailMessage -Body $body -BodyAsHtml -From "sqlserver@company.com" -SmtpServer "smtp.company.com" -Subject "performance alert $env:computername Network Traffic" -To "dbateam@company.com" -Encoding utf8 } |
SQL AO AG failover in cluster group and check the synchronization status
Get info from Cluster:
|
1 2 3 4 5 6 |
Get-Cluster Get-ClusterNode Get-ClusterGroup Get-ClusterResource Get-ClusterResource | Select-Object * | Sort-Object cluster,ownernode,name | Out-GridView Get-ClusterResource | Select-Object cluster,ownernode,name, ownergroup,resourcetype, state,characteristics | Sort-Object cluster,ownernode,name | Out-GridView |
Failover general example, you can use Invoke-DbaAgFailover for SQL server:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
# Invoke-DbaAgFailover dbatools powershell command to failover # Switch the all cluster group to the another node in verbose: # do NOT do in production environment: Get-ClusterGroup | Move-ClusterGroup -verbose # Switch the cluster to the particular Node: # do NOT do in production environment: # Get-ClusterGroup | Move-ClusterGroup -Node ideanodeneve # move one group example: Move-ClusterGroup "Available Storage" Move-ClusterGroup "Cluster Group" |
DBATools makes your life happy, you can find the SQL server instances and you can failover them.
To find all of the SQL instances in a cluster server . To Check AG before and after the failover, and failover the SQL Server AO AG :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
# cluster:::::: # (Get-ClusterNode).name | Find-DbaInstance # find SQL server instances in the cluster nodes $SqlServers = (Get-ClusterNode).name | Find-DbaInstance # remote cluster server example: # $SqlServers = (Get-ClusterNode -cluster wvvzrz0020cl).name | Find-DbaInstance $SqlServers $SqlServersOnline = $SqlServers | Where-Object {$_.Availability -eq "Available" } $SqlServersOnline if ($SqlServers.Count -ne $SqlServersOnline.Count) { Write-Host "There are unavailable instances!!!!!" -ForegroundColor Red -BackgroundColor Yellow} # failover GUI Get-DbaAvailabilityGroup -SqlInstance $SqlServersOnline | Out-GridView -Passthru | Invoke-DbaAgFailover # -Confirm:$false -Whatif # instance ellenorzes Get-DbaAgDatabase -SqlInstance $SqlServersOnline | Out-GridView |
if you have to wait a lot for synchronization you can check how many servers are synchronized.
|
1 2 3 4 5 6 7 8 9 |
$SqlServersOnline = (Get-ClusterNode).name | Find-DbaInstance | Where-Object {$_.Availability -eq "Available" } do { Write-Host; Write-Host "*********************************************************************************************";` $Databases= Get-DbaAgDatabase -SqlInstance $SqlServersOnline; Start-Sleep -Seconds 1.0 ; ` Start-Sleep -Seconds 0.5 ; ` Write-Host -NoNewline (Get-Cluster).Name "has" $Databases.Count " AG databases. " ; ` Write-Host ($Databases | Where-Object {$_.SynchronizationState -eq "Synchronized" }).count "databases are synchronized at" (Get-Date).ToString(); ` Write-Host -NoNewline "Out of Synchron:" ($Databases | Where-Object {$_.SynchronizationState -ne "Synchronized" }) | Select-Object Name ; ` } ` while ( ($Databases | Where-Object {$_.SynchronizationState -ne "Synchronized" }).count -ne 0 ); |
What is my default backup BUFFERCOUNT and MAXTRANSFERSIZE Data Transfer Options?
Error message: BackupIoRequest::ReportIoError: write failure on backup device \\filer\sharename\.. bak Operating system error 8(Not enough memory resources are available to process this command.).
It may you want a better performance for backup (less cpu and shorter backup time, so you can tune the BUFFERCOUNT and MAXTRANSFERSIZE
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15
–Data Transfer Options BUFFERCOUNT = { buffercount | @buffercount_variable } | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
Books Online gives the following definition for these two options:
BUFFERCOUNT = { buffercount | @ buffercount_variable }
Specifies the total number of I/O buffers to be used for the backup operation. You can specify any positive integer; however, large numbers of buffers might cause “out of memory” errors because of inadequate virtual address space in the Sqlservr.exe process.
Based on the values that you provide or do not provide, SQL Server specifies the size contiguous buffers that it will use to perform the backup. This is of utmost importance in 32-bit environments as large amount of contiguous memory allocation can prove to be fatal to the non-Buffer Pool region of the SQL Virtual Address Space. This can cause your backups to fail. When SQLVDI is being used, this is of utmost importance because we cannot change the MAXTRANSFERSIZE after the VDI Configuration has been completed. The amount of contiguous virtual memory is determined by the number of backup devices and by the number of volumes on which the database files reside on.
You can use Trace Flag 3213 to review your backup/restore configuration parameters while performing a backup/restore operation. I shall proceed to show you how specifying the incorrect BUFFERCOUNT values or not providing it can prove to be fatal.
So how to get the buffercount?
|
1 2 3 4 5 |
DBCC TRACEON (3605); DBCC TRACEON (3213); BACKUP DATABASE WideWorldImporters TO DISK = 'WideWorldImporters.bak'; DBCC TRACEOFF (3213); DBCC TRACEOFF (3605); |
Sample result:
Monitor the active session scheduled by SQL agent service
It is very common that you should investigate what is going on the server at a certain time and you can not find any scheduled job. Yes, the application run a big job. If you want to see current processes, you can run:
|
1 |
EXEC sp_WhoIsActive |
If you want to log it, you can schedule for every 1-10 minutes (you have to set the @destination_table and @destination_database, and the table will be created at the first running time):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
SET NOCOUNT ON; DECLARE @retention INT = 7, @destination_table VARCHAR(500) = 'WhoIsActive', @destination_database sysname = 'SQLMonitor', @schema VARCHAR(MAX), @SQL NVARCHAR(4000), @parameters NVARCHAR(500), @exists BIT; SET @destination_table = @destination_database + '.dbo.' + @destination_table; --create the logging table IF OBJECT_ID(@destination_table) IS NULL BEGIN; EXEC dbo.sp_WhoIsActive @get_full_inner_text = 1, @get_plans = 1, @get_outer_command = 1, @find_block_leaders = 1, @get_transaction_info = 1, @return_schema = 1, @schema = @schema OUTPUT; SET @schema = REPLACE(@schema, '<table_name>', @destination_table); EXEC ( @schema ); END; --create index on collection_time SET @SQL = 'USE ' + QUOTENAME(@destination_database) + '; IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(@destination_table) AND name = N''cx_collection_time'') SET @exists = 0'; SET @parameters = N'@destination_table varchar(500), @exists bit OUTPUT'; EXEC sys.sp_executesql @SQL, @parameters, @destination_table = @destination_table, @exists = @exists OUTPUT; IF @exists = 0 BEGIN; SET @SQL = 'CREATE CLUSTERED INDEX cx_collection_time ON ' + @destination_table + '(collection_time ASC)'; EXEC ( @SQL ); END; --collect activity into logging table EXEC dbo.sp_WhoIsActive @get_full_inner_text = 1, @get_plans = 1, @get_outer_command = 1, @find_block_leaders = 1, @get_transaction_info = 1, @destination_table = @destination_table; --purge older data SET @SQL = 'DELETE FROM ' + @destination_table + ' WHERE collection_time < DATEADD(day, -' + CAST(@retention AS VARCHAR(10)) + ', GETDATE());'; EXEC ( @SQL ); |
Install or update PowerShell 7 on Windows Server (v10)
You can use this single command in Windows PowerShell to install PowerShell 7.
|
1 |
iex "& { $(irm https://aka.ms/install-powershell.ps1) } -UseMSI" |