none
Check scheduler_id's for parrallel queries using Extended Events

    Question

  • Hi,

    we followed the rule to set the maxdop = numer of schedulers per NUMA node. I now want to check how often parallel queries can use different schedulers within the same NUMA node. Following scripts works fine for me.

    select DISTINCT session_id , scheduler_id
    from sys.dm_os_tasks 
    WHERE parent_task_address IS NOT NULL
    ORDER BY 1,2

    But this is ad-hoc. I would like to be able to setup an extended event session on the sql_statement_completed event to collect the same info. But I noticed the scheduler_id collected here is only the one linked to the parent_task, but I'm more interested in the parallel tasks.

    Any ideas?


    Geert Vanhove DCOD ------ http://geertvanhove.wordpress.com/ ----------- Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!


    Monday, July 15, 2013 12:50 PM

Answers

  • While it is possible that this could be done with Extended Events, the level of effort involved is not worth even attempting it.  First you would have to collect events other than sql_statement_completed to get information about the child tasks and the only one that I can think to add would be wait_info.  Then you'd also have to add the sql_statement_starting event and use causality tracking to be able to do end to end mapping of the events for a single query from start to finish based on the activity_id.  However, it's not that simple because you could have id transfers occur and then you have to join through those transfers as well, and the complexity of trying to map it all back together is not worth the effort, nor does it guarantee that you catch all the sub-tasks.  By the way you'd also have to add the scheduler_id action to the additional events to track where they were running.


    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server


    Wednesday, July 17, 2013 10:06 PM

All replies

  • Hello,

    Please refer to the following blog about troubleshooting parallel query. Hope it helps.
    http://sqlmag.com/sql-server/troubleshooting-parallel-queries-sql-server-2005

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click  here.


    Fanny Liu
    TechNet Community Support

    Tuesday, July 16, 2013 5:39 AM
  • -- Create the event session
    CREATE EVENT SESSION EE_LongrunningQueries ON SERVER
    ADD EVENT sqlserver.sql_statement_completed
    (ACTION (sqlserver.sql_text, sqlserver.plan_handle)
    WHERE sqlserver.database_id =  /*DBID*/
    AND cpu > 10 /*total ms of CPU time*/)
    ADD TARGET package0.asynchronous_file_target
        (SET FILENAME = N'C:\EE_LongrunningQueries.xel', 
        METADATAFILE = N'C:\EE_LongrunningQueries.xem')
    WITH (max_dispatch_latency = 1 seconds);
    GO

    -- Start the session
    ALTER EVENT SESSION EE_LongrunningQueries ON SERVER
    STATE = START;
    GO

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, July 16, 2013 7:47 AM
  • Thanks for the input, but the plan doesn't tell me which schedulers were involved in executing the parallel query as the sys.dm_os_tasks view does. Unless I overlooked something...

    Geert Vanhove DCOD ------ http://geertvanhove.wordpress.com/ ----------- Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    Tuesday, July 16, 2013 9:23 AM
  • While it is possible that this could be done with Extended Events, the level of effort involved is not worth even attempting it.  First you would have to collect events other than sql_statement_completed to get information about the child tasks and the only one that I can think to add would be wait_info.  Then you'd also have to add the sql_statement_starting event and use causality tracking to be able to do end to end mapping of the events for a single query from start to finish based on the activity_id.  However, it's not that simple because you could have id transfers occur and then you have to join through those transfers as well, and the complexity of trying to map it all back together is not worth the effort, nor does it guarantee that you catch all the sub-tasks.  By the way you'd also have to add the scheduler_id action to the additional events to track where they were running.


    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server


    Wednesday, July 17, 2013 10:06 PM
  • Thanks Jon,

    indeed, as this was just something out of curiousity, this is not worth the effort. I'll poll every 1 minute during a few days using the query above and store the results in some table in tempdb for analysis.


    Geert Vanhove DCOD ------ http://geertvanhove.wordpress.com/ ----------- Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    Friday, July 19, 2013 1:12 PM
  • we followed the rule to set the maxdop = numer of schedulers per NUMA node. I now want to check how often parallel queries can use different schedulers within the same NUMA node. Following scripts works fine for me.

    Hello Geert,

    Almost  all the time. 

    Can you clarify why you are trying to find the schedulers used within same NUMA node for parallel queries?

    Threads created for parallel query will distribute across the schedulers of all the NUMA node, so you should focus on how the threads are distributed across the schedulers of all the node.

    Am I missing something?


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem


    Tuesday, August 06, 2013 8:39 AM
  • we followed the rule to set the maxdop = numer of schedulers per NUMA node. I now want to check how often parallel queries can use different schedulers within the same NUMA node. Following scripts works fine for me.

    Hello Geert,

    Almost  all the time. 

    Can you clarify why you are trying to find the schedulers used within same NUMA node for parallel queries?

    Threads created for parallel query will distribute across the schedulers of all the NUMA node, so you should focus on how the threads are distributed across the schedulers of all the node.

    Am I missing something?


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem



    If SQL Server is 'really' NUMA aware, it should at least try to assign its threads to workers/schedulers on the same NUMA node. By limmiting the MAXDOP, SQL Server would theoretically be able to do so.

    Geert Vanhove DCOD ------ http://geertvanhove.wordpress.com/ ----------- Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    Tuesday, August 06, 2013 8:47 AM
  • we followed the rule to set the maxdop = numer of schedulers per NUMA node. I now want to check how often parallel queries can use different schedulers within the same NUMA node. Following scripts works fine for me.

    Hello Geert,

    Almost  all the time. 

    Can you clarify why you are trying to find the schedulers used within same NUMA node for parallel queries?

    Threads created for parallel query will distribute across the schedulers of all the NUMA node, so you should focus on how the threads are distributed across the schedulers of all the node.

    Am I missing something?


    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem



    If SQL Server is 'really' NUMA aware, it should at least try to assign its threads to workers/schedulers on the same NUMA node. By limmiting the MAXDOP, SQL Server would theoretically be able to do so.

    Geert Vanhove DCOD ------ http://geertvanhove.wordpress.com/ ----------- Please click the Mark as Answer or Vote As Helpful if a post solves your problem or is helpful!

    That's not the correct method to check if server applications like SQL Server is NUMA aware :) Did Microsoft document somewhere that limiting Max DOP would assign all the threads of parallel plan from same MUMA node?

    If I remember correct that was only case with soft NUMA in SQL2005.SQL Server has never reserved worker thread from single NUMA node at least from 2008. When you have single parallel operator It's possible that all the worker threads would get assigned from single NUMA node but again that could be different from controlling worker thread. If you had multiple parallel operators, each one could have its own node assignment and they still could run on different nodes in parallel too.

    If you have X number of nodes, each with Y number of CPUs, don't let your maxDOP value to be greater than Y (unless you have tested and noticed benefit) is all we are saying . If it is greater most likely all your parallel threads are getting assigned from same NUMA node (at least for single operator), then those threads are not running in parallel anyway and will just cause unnecessary context switches.



    Thank you,

    Karthick P.K |My blogs|My Scribbles|Twitter|My Facebook Group|

    www.Mssqlwiki.com

    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Tuesday, August 06, 2013 10:32 AM