DBA

example: command:  "C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe" add argument -NoProfile -NoLogo -NonInteractive -ExecutionPolicy Bypass -ExecutionPolicy Bypass -file D:\dba\SQLScripts\AlertOnNoDBBackup.ps1

As Oracle DBA, I did not expect this lock wait, but it's happening on SQL Server. This link has answer. the solution is to create index. https://stackoverflow.com/questions/62852902/update-with-rowlock-in-mssql-server specifically explained here: Lock hints are only hints. You can't "force" SQL to take a particular kind of lock. You can see the locks being taken with the following query: select tl.request_session_id, tl.resource_type, tl.request_mode, tl.resource_description, tl.request_status from sys.dm_tran_locks tl join sys.partitions pt on pt.hobt_id = tl.resource_associated_entity_id join sys.objects ob on ob.object_id = pt.object_id where tl.resource_database_id = db_id() order by tl.request_session_id OK, let's run some code in an SSMS query window: create table t(i int , j int ); insert t values ( 1 , 1 ), ( 2 , 2 ); begin tran; update ...

To find query duration average.  declare @searchSQL varchar(max) ='%SELECT%vwVesselsEditableForms%join%tblEmployeePOAAccess%' SELECT qsp.query_id, QSP.plan_id, min(QSRS.first_execution_time) min_first_execution_time, max(QSRS.last_execution_time)  max_last_execution_time, sum(qsrs.count_executions*QSRS.avg_duration/1000000) total_seconds,  qsq.query_hash, qsp.query_plan_hash, avg(QSRS.avg_duration)/1000000 avg_duration_sec, sum(qsrs.count_executions) total_executions, min(qsrs.min_duration)/1000000 min_duration_sec,max(qsrs.max_duration)/1000000 max_duration_sec, QST.query_sql_text, qsp.query_plan FROM sys.query_store_plan AS QSP  JOIN sys.query_store_query AS QSQ      ON QSP.query_id = QSQ.query_id   JOIN sys.query_store_query_text AS QST      ON QSQ.query_text_id = QST.query_text_id  JOIN sys.query_store_runtime_stats  QSRS   ON QSP.plan_id =QSRS.plan_id where  qst.query_sql_text like @searchSQL group...

 I have 700GB sql server database dump file need to be copied from prod server to test, the copy speed is only 5MByte/s,  I asked network admin, here is his response: " Based on a queue depth of approximately 20% of our 1Gbps WAN link and a latency of 12ms between Data Centers the speed you're receiving is in alignment with a standard TCP Window of 64KB.   BDP (200 Mbit/sec, 12.0 ms) = 0.30 MByte required tcp buffer to reach 200 Mbps with RTT of 12.0 ms >= 293.0 KByte maximum throughput with a TCP window of 64 KByte and RTT of 12.0 ms <= 43.69 Mbit/sec.   == 5.46125 MB/s " To understand it better, this link explain what BDP is: https://youtu.be/vPCKWhXSAEo This link below explain how BDP and tcp receiver buffer works: http://ce.sc.edu/cyberinfra/workshops/Material/perfSONAR/Lab%206.pdf Then lead to the solution how to change it, described here:  https://learn.microsoft.com/en-us/troubleshoot/windows-server/networking/description-tcp-fe...

One sharepoint database transaction log file and backup is getting huge, sharepoint admin turned off the auditing and other features and still having big tran log generated every day. I dig into it and found what is responsible: Thanks to this: https://blog.coeo.com/inside-the-transaction-log-file SELECT [Operation], count(*) AS [No of Records], SUM([Log Record Length]/1024.00/1024.00) AS [RecordSize (MB)] FROM fn_dblog(NULL,NULL) GROUP BY Operation ORDER BY [RecordSize (MB)] DESC so it's the LOP_shrink_noop that generated 7GB transaction log today since last backup, that's because the database auto shrink is set to true. select name,is_auto_shrink_on, * FROM    sys.databases Turn off the autoShrink fixed the problem. If the cause is DML, you can find what table is it. SELECT        [Operation]        ,SUM([Log Record Length]/1024.00/1024.00) AS [RecordSize (MB)]          ,count(*) OperationCount   ...

 (get-wmiobject Win32_Service -computername $servername -Filter "Name='MSSQLSERVER'").startname

Here are some reference links and script I used to fix performance issue by force query plan using query store feature:  --https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver16 --https://www.sqlshack.com/force-query-execution-plan-using-sql-server-2016-query-store/ --ALTER DATABASE [system-abccompany] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE); --ALTER DATABASE [system-abccompany] SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON ); --SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,    max_storage_size_mb, readonly_reason, interval_length_minutes,    stale_query_threshold_days, size_based_cleanup_mode_desc,    query_capture_mode_desc --FROM sys.database_query_store_options; --select count(*) from sys.query_store_query --ALTER DATABASE [system-abccompany] SET QUERY_STORE (MAX_STORAGE_SIZE_MB =100); --ALTER DATABASE [system-abccompany] SE...