DBA

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

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

 C:\>netsh wlan show profile YourWifiNetwordName(SSID name) key=clear Profile YourWifiNetwordName  on interface Wi-Fi: ======================================================================= Applied: All User Profile Profile information -------------------     Version                : 1     Type                   : Wireless LAN     Name                   : YourWifiNetwordName      Control options        :         Connection mode    : Connect automatically         Network broadcast  : Connect only if this network is broadcasting         AutoSwitch         : Do not switch to other networks         MAC Randomization  : Disabled Connectivity settings ---...

A application run a query which sometimes slow on one literal but fast on the other, this is the case study of SQL Server plan guide feature: Slow query : use 255 as literal, run duration is ~ 1 minute SELECT top 1 mh.intnum as intnum,rh.remark as remark,reportid FROM msghead mh inner join msgdetail md on md.intnum = mh.msgintnum left outer join rephistory rh on md.functionnum = 45000 AND md.functionref = rh.intnum WHERE readon < '1900/01/02' AND((isnull(rh.userid,0) = 255 AND md.functionnum = 45000 and rh.remark like 'http%'))  Fast query : use 167 or 66 as literal, complete in less than 1 second SELECT top 1 mh.intnum as intnum,rh.remark as remark,reportid FROM msghead mh inner join msgdetail md on md.intnum = mh.msgintnum left outer join rephistory rh on md.functionnum = 45000 AND md.functionref = rh.intnum WHERE readon < '1900/01/02' AND((isnull(rh.userid,0) = 167 AND md.functionnum = 45000 and rh.remark like 'http%'))  Thanks to this book : ou...