catalog

Most frequently used articles

AZURE – SQL – Frequently User SQL Commands: https://vijredblog.wordpress.com/2016/10/27/sql-commands-in-azure/

Azure – comonly used Powershell Commands:
Frequently used Azure SQL Powershell Commands: https://vijredblog.wordpress.com/2017/07/27/frequently-used-azure-powershell-commands/

Posted in Azure, Powershell, SQL, Uncategorized | Tagged , | Leave a comment

Maximum capacity specifications for SQL Server

Ever wonder what are the Maximum capacity specifications for SQL Server?
Here you go: https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15

 

Posted in Uncategorized, SQL, Productivity, SQL Server | Tagged , | Leave a comment

SQL – AlwaysOn Availability Group synchronization delay troubleshooting

I learned detailed step by step performance metrics and xevents that can be used to troubleshoot  AlwaysOn Availability Group synchronization delay.

 

https://docs.microsoft.com/en-us/archive/blogs/sql_server_team/troubleshooting-high-hadr_sync_commit-wait-type-with-always-on-availability-groups

 

Some network configuration that can be updated to address network challenges:

https://docs.microsoft.com/en-us/windows-server/networking/technologies/network-subsystem/net-sub-performance-tuning-nics

 

 

Posted in Performance, Referene, SQL, SQL Server, Uncategorized | Tagged , , , | Leave a comment

Very Large Database in SQL Server – How is it different

Following are a few considerations while managing a very large database on Microsoft SQL Server. I am not recommencing any configuration as different configurations are good fit for different workloads and business requirements but these are the items that can be reviewed!

  • I am referring to the databases with at least with 2 TB in size with average of a few thousand transactions a second, and at least 16 Logical CPUs.
  • Maintenance duration: Based on the database size and transaction volume duration for maintenance can be significantly, that needs customization.
  • Backups:  Backup strategy can be different for VLDB considering the amount of time it takes to backup. introduce Striped Backups to save time! Think innovative of how Full, Differential and transactional backups frequency can be changed based on Recovery Point and Recovery time requirements.
  • DBCC CheckDB: DBCC CheckDB may fail at times, https://support.microsoft.com/en-us/help/2002606/os-errors-1450-and-665-are-reported-for-database-data-files is a very good article about different options to address the problem. Consider using Physical-Only option, change number of threads.
  • MAXDOP / Cost Threshold for Parallelism can be different from normal workload databases.
  • Data file and Log file growth size or %: Review growth patterns to understand the impact and correct these settings. Use Instant File Initialization.
  • Index Maintenance configuration settings: Choose correct thresholds for Index Re-Organize and Rebuild. ReOrg is single threaded and may take long and replacing with Rebuild can use parallel threads to reduce Index maintenance duration.
  • Synchronization challenges in High performance servers: Control flow can result in replication delays. Ref: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/monitor-performance-for-always-on-availability-groups?view=sql-server-ver15#flow-control-gates
  • VMWare – Paravirtual SCSI settings if running on VM – enhance network performance by adjusting  queue depth – Ref:  https://kb.vmware.com/s/article/2053145
  • Number of database files – Consider creating maximum of ~1TB per file, create multiple data files
  • Database compatibility level: Argeed after long debate, at least on a specific workloads latest compatibility level can impact performance, using legacy cardinality estimator can improve performance.
  • Partitioning: Consider partitioning the data and mark old partitions as read-Only.
  • Archive Strategy: Re-Consider archive strategy as well
  • Full Factor for very large Tables/Indexes: Updating fill factor settings to change Storage, Fragmentation levels, Performance. Verify how Fill Factor can be modified for very large tables.
  • Storage / mount point size: By default Bytes Per Cluster configuration is 4K, meaning maximum size of the disk/mount point will be 16 TB. If we have to increase the size beyond 16 TB this value needs to be updated. I recommend configuring Bytes Per Cluster to 64 KB to avoid 16TB size limit and potential dbcc integrity check failures. Note: This change needs disk formatting, incurs downtime! Bytes Per FileRecord Segment value to be updated to 4096 while formatting (Update command: Format <Drive:> /FS:NTFS /L, Verify command: fsutil fsinfo ntfsinfo <Drive>:)

 

 

 

 

 

Posted in Performance, SQL, SQL Error, Uncategorized | Tagged , , , | Leave a comment

MSSQLSERVER Error Catalog

https://docs.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors?view=sql-server-ver15

Posted in SQL, SQL Server, Uncategorized | Tagged , , , , , | Leave a comment

SOLUTION – Write-EventLog : The source name X does not exist on computer “localhost”

While attempting to create a new event log using write-eventlog cmdlet you may hit this error if the Source is not already registered.

New-EventLog can be used to add anew source, following is the example


#
# Sample Write event 
Write-EventLog -LogName Application -Source "myNewSource" -EventID 3001 -Message "myNewSource new message"

# Sample Error
# Write-EventLog : The source name "myNewSource" does not exist on computer  "localhost".

# View available Source names registered, if anything available can be used 
(Get-WmiObject -Class Win32_NTEventLOgFile |
  Select-Object FileName, Sources |
  ForEach-Object -Begin { $hash = @{}} -Process { $hash[$_.FileName] = $_.Sources } -end { $Hash })["Application"]

# Solution, Add new Source Naem 
New-EventLog -LogName Application -Source "myNewSource"


#Verify if new source is registed
(Get-WmiObject -Class Win32_NTEventLOgFile |
  Select-Object FileName, Sources |
  ForEach-Object -Begin { $hash = @{}} -Process { $hash[$_.FileName] = $_.Sources } -end { $Hash })["Application"] | findstr "myNewSource"


# Write new event - Success 
Write-EventLog -ComputerName "Server01" -LogName Application -Source "MyApp" -EventID 3001 -Message "MyApp added a user-requested feature to the display."

#
Posted in Uncategorized | Leave a comment

Capture ping results with time – ping test

#
Ping.exe -t ServerName | ForEach {"{0} - {1}" -f (Get-Date),$_} | tee C:\Temp\Filename.txt
#

This captures ping results along with time, adds to file and displays results on the window.

Posted in Powershell, Productivity | Tagged , , , , | Leave a comment

SQL Job history – Getting end time from sysjobhistory

Run_date, run_time, run_duration are stored in int format in sysjobhistory table, not user friendly. For a quick solution I had to pull the history from last X hours.

This simple TSQL helped to find end time of the step!


select top 10000 instance_id, job_name = sj.name, run_date, run_time, run_duration 
--,convert(DATETIME,RTRIM(run_date)) as myrundate 
--,convert(int,(run_time / 10000)) as run_time_hour 
--,convert(int,(run_time / 100))%100 as run_time_minutes 
--,convert(int,(run_time / 1))%100 as run_time_seconds 
--,convert(int,(run_duration / 1000000)) as run_duration_days
--,convert(int,(run_duration / 10000))%100 as run_duration_hour 
--,convert(int,(run_duration / 100))%100 as run_duration_minutes 
--,convert(int,(run_duration / 1))%100 as run_duration_seconds 
, dateadd(second,  ( convert(int,(run_time / 10000)) * 3600 )
+ (convert(int,(run_time / 100))%100) * 60 
+  (convert(int,(run_time / 1))%100)
+ ( convert(int,(run_duration / 1000000))) * 3600 * 24
+  (convert(int,(run_duration / 10000))%100) * 3600
+ (convert(int,(run_duration / 100))%100) * 60
+ (convert(int,(run_duration / 1))%100)
,convert(DATETIME,RTRIM(run_date))) as StepCompleteDateTime
FROM msdb.dbo.sysjobs sj
INNER JOIN msdb.dbo.sysjobhistory sjh
ON sj.job_id = sjh.job_id

Posted in Productivity, SQL, SQL Server, Uncategorized | Tagged , , , , , , , , , | Leave a comment