search

microsoft / SqlNexus

SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues. It loads and analyzes performance data collected by SQL LogScout, SQLDiag or PSSDiag. It can dramatically reduce the amount of time you spend manually analyzing data.
MIT License
345 stars 95 forks source link

Adding ignorable waits CHECKPOINT_QUEUE, PARALLEL_REDO_WORKER_WAIT_WORK, QDS_ASYNC_QUEUE, PWAIT_EXTENSIBILITY_CLEANUP_TASK #266

Closed PiJoCoder closed 7 months ago

PiJoCoder commented 8 months ago

The goal is to not show these as noise in the Bottleneck Analysis report.

One wait to test this is to create a SQL LogScout perfstats collection and manually go in the output file and add these wait types in the output. Then import and watch them not show up. You can also run this query after importing SQL Nexus and check if they are showing as "IGNORABLE" in the wait_category column

  use sqlnexus
  go
  select  wait_type, wait_category from tbl_OS_WAIT_STATS 
  where wait_type in 
  ('CHECKPOINT_QUEUE', 
  'QDS_ASYNC_QUEUE',
  'PARALLEL_REDO_WORKER_WAIT_WORK', 
  'PWAIT_EXTENSIBILITY_CLEANUP_TASK')
asavioliMSFT commented 7 months ago

Compared bottleneck analysis from a customer's data

Current nexus version

image

PR version - we can see Parallel_redo_worker_wait_work is no longer there

image

Performed some updates to force having a QDS_ASYNC_QUEUE on one of my collections.

With current nexus build

image

With PR build

image

PiJoCoder commented 7 months ago

PR approved.

It doesn't cause a problem, but I noticed that that CHECKPOINT_QUEUE was already there at the beginning of the IN clause, which means this wait was already ignored. So, adding it again was not required

Thank you, good catch.