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 by qsp.query_id, QSP.plan_id,QST.query_sql_text, qsq.query_hash, qsp.query_

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

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        ,[AllocUnitName]        --,[Begin Time]        --,[End Time]        ,[Trans

 (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] SET QUERY_STORE CLEAR; --ALTER DATAB

SQL Server is having severe performance issue during testing, found out the PLE is under 500, one db session hung for 2 hours. I use the following queries to find out what's going on:   Thanks to this link about query the buffer pool https://simplesqlserver.com/2016/01/04/query-the-buffer-pool/ select * from sys.dm_os_waiting_tasks where wait_type like 'PAGE%' --select * from sys.dm_os_waiting_tasks  SELECT TOP 50 qs.execution_count, AvgPhysicalReads = isnull( qs.total_physical_reads/ qs.execution_count, 0 ), MinPhysicalReads = qs.min_physical_reads, MaxPhysicalReads = qs.max_physical_reads, AvgPhysicalReads_kbsize = isnull( qs.total_physical_reads/ qs.execution_count, 0 ) *8, MinPhysicalReads_kbsize = qs.min_physical_reads*8, MaxPhysicalReads_kbsize = qs.max_physical_reads*8, CreationDateTime = qs.creation_time, SUBSTRING(qt.[text], qs.statement_start_offset/2, (  CASE  WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2  ELSE qs.statement_end

The following script check if some AD  users are a member of local group, what sqlserver agent group do they have, and grant the group the role. foreach ($sqlserver in "servername1","servername2") { echo $sqlserver invoke-command -computername $sqlserver  -scriptblock {get-localgroupmember -name TCCSDBwriter*|format-list -property name|findstr /I "vaketi amohamme dmorad"} &sqlcmd -S $sqlserver -i query_agentjob_role.sql -Y 40 &sqlcmd -S $sqlserver -i grant_agentjob_role.sql } The query_agentjob_role.sql: SELECT DP1.name AS DatabaseRoleName,       isnull (DP2.name, 'No members') AS DatabaseUserName     FROM msdb.sys.database_role_members AS DRM    RIGHT OUTER JOIN msdb.sys.database_principals AS DP1      ON DRM.role_principal_id = DP1.principal_id    LEFT OUTER JOIN msdb.sys.database_principals AS DP2      ON DRM.member_principal_id = DP2.principal_id   WHERE DP1.type = 'R' and DP1.name like 'SQLAgent%' and