This is my go-to script when there are SQL performance issues. Sadly, I can't remember where I found it.
/***** Script: Database Wise CPU Utilization report *****/
/***** Output:
CPUPercent: Let's say this instance is using 50% CPU and one of the database is using 80%. It means the actual CPU usage from the database is calculated as: (80 / 100) * 50 = 40 %
*****/
WITH DB_CPU AS
(SELECT DatabaseID,
DB_Name(DatabaseID) AS [DatabaseName],
SUM(total_worker_time) AS [CPU_Time(Ms)]
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY(SELECT CONVERT(int, value)AS [DatabaseID]
FROM sys.dm_exec_plan_attributes(qs.plan_handle)
WHERE attribute =N'dbid')AS epa GROUP BY DatabaseID)
SELECT
DatabaseName AS [DBName], [CPU_Time(Ms)],
CAST([CPU_Time(Ms)] * 1.0 /SUM([CPU_Time(Ms)]) OVER()* 100.0 AS DECIMAL(5, 2))AS [CPUPercent]
FROM DB_CPU
WHERE DatabaseID > 4 -- system databases
AND DatabaseID <> 32767 -- ResourceDB
ORDER BY CPUPercent desc
OPTION(RECOMPILE);
/***** Script: Top 10 queries that causes high CPU Utilization *****/
/***** Note: This script returns list of costly queries when CPU utilization is high enough from last 10 min ****/
SET NOCOUNT ON
DECLARE @cpu bigint = 50
DECLARE @ts_now bigint
DECLARE @AvgCPUUtilization DECIMAL(10,2)
SELECT @ts_now = cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info
SELECT TOP(10) SQLProcessUtilization AS [SQLServerProcessCPUUtilization]
,SystemIdle AS [SystemIdleProcess]
,100 - SystemIdle - SQLProcessUtilization AS [OtherProcessCPU Utilization]
,DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [EventTime]
INTO #CPUUtilization
FROM (
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
'int')
AS [SQLProcessUtilization], [timestamp]
FROM (
SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE '%<SystemHealth>%') AS x
) AS y
ORDER BY record_id DESC
SELECT @AvgCPUUtilization = AVG([SQLServerProcessCPUUtilization] + [OtherProcessCPU Utilization])
FROM #CPUUtilization
WHERE EventTime > DATEADD(MM, -10, GETDATE())
IF @AvgCPUUtilization >= @cpu
BEGIN
SELECT TOP(10)
CONVERT(VARCHAR(25),@AvgCPUUtilization) +'%' AS [AvgCPUUtilization]
, GETDATE() [Date and Time]
, r.cpu_time
, r.total_elapsed_time
, s.session_id
, s.login_name
, s.host_name
, DB_NAME(r.database_id) AS DatabaseName
, SUBSTRING (t.text,(r.statement_start_offset/2) + 1,
((CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), t.text)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset)/2) + 1) AS [IndividualQuery]
, SUBSTRING(text, 1, 200) AS [ParentQuery]
, r.status
, r.start_time
, r.wait_type
, s.program_name
INTO #PossibleCPUUtilizationQueries
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
INNER JOIN sys.dm_exec_requests r ON c.connection_id = r.connection_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE s.session_id > 50
AND r.session_id != @@spid
order by r.cpu_time desc
SELECT *
FROM #PossibleCPUUtilizationQueries
END
IF OBJECT_ID('TEMPDB..#CPUUtilization') IS NOT NULL
drop table #CPUUtilization
IF OBJECT_ID('TEMPDB..#PossibleCPUUtilizationQueries') IS NOT NULL
drop table #PossibleCPUUtilizationQueries
Use case
When you have an API that doesn't start and doesn't show an error log.
Solution
Update the web.config
and set the ASPNETCORE_DETAILEDERRORS
environment variable to true
.
<system.webServer>
<httpErrors errorMode="Detailed" />
<aspNetCore processPath="dotnet">
<environmentVariables>
<environmentVariable name="ASPNETCORE_DETAILEDERRORS" value="true" />
</environmentVariables>
</aspNetCore>
</system.webServer>
Use case
You want to periodically the refresh data in the NgRx Store.
Solution
Create a NgRx Effect that retrieves the data via a service every x minutes, this can be done with the RxJS timer operator.
refresh$ = createEffect(() => {
// every 10 minutes
return timer(0, 600000).pipe(
switchMap(() =>
this.customersService.get().pipe(
map((data) => refreshSuccess(data)),
catchError((response) => refreshFailed(response)),
),
),
)
})
Use case
I want to delete a property from an object in a pure (immutable) way.
Solution
Use a destructuring assignment to assign the to be removed property to a variable, while cloning the "rest" properties to a new variable. The _
is used to prevent a linter giving the warning "variable is declared but its value is never read".
const { password: _, ...user } = {
id: 47,
username: 'tim',
password: 'iliketrains',
}
console.log(user)
// |> { id: 47, username: 'tim' }
For more examples see Destructuring assignment on MDN
Use case
An action to fetch multiple entities at once, but the service has only an endpoint to fetch one entity at a time.
Solution
Use the RxJS merge operator to flatten all request streams and concurrently emit all values to a single output stream.
refresh$ = createEffect(() =>
this.actions$.pipe(
ofType(CustomerActions.refresh),
exhaustMap(({ customerIds }) =>
merge(
...ids.map((id) =>
this.customersService.getCustomer(id).pipe(
map(CustomerActions.getCustomerSuccess),
catchError((err) =>
of(CustomerActions.getCustomerFailed(id, err.message)),
),
),
),
),
),
),
)