DBA

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   ...