DBA

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

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