Extended events sort warning

• as_1234

  SSCrazy

  Points: 2871

  More actions

  April 17, 2025 at 8:55 am

  #4580331

  I've setup an extended event using the following code:

  CREATE EVENT SESSION [Performance_Monitoring] ON SERVER
   
  ADD EVENT sqlserver.sort_warning(
      ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.sql_text))
  ADD TARGET package0.event_file(SET filename=N'',max_file_size=(5))
  WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
  GO
  

  I've taken the file path out for my post but there is one specified in what I setup.

  The results are showing quite a few sort warnings but if I look at the execution plans I can't see any sort warnings.

  If I understand this extended event correctly it's designed to identify when the memory grant for the query wasn't accurate and a sort has spilled to tempdb which should be visible in the sort operators on the execution plans?

  The plans do have quite a few type conversion warnings so I'm wondering if that could be causing it as that will affect the cardinality estimates and therefore the memory allocated to the query? Maybe SQL server triggers a sort warning because of the possibility of a spill to tempdb rather than because one actually occurred? I've done quite a bit of searching online and not seen type conversions as a possible explanation so I don't think it's that but thought it was worth mentioning.

  Thanks

• Site Owners

  SSC Guru

  Points: 80342

  More actions

  April 18, 2025 at 9:10 am

  #4580846

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.