none
SQL Compilations per second RRS feed

  • 问题

  • Hello,

    I am getting more & more alerts from our monitoring system about the SQL Server:

    Number of times that Transact-SQL compilations occurred, per second (including recompiles). The lower this value is the better. High values often indicate excessive adhoc querying and should be as low as possible. If excessive adhoc querying is happening, try rewriting the queries as procedures or invoke the queries using sp_executeSQL. When rewriting isn’t possible, consider using a plan guide or setting the database to parameterization forced mode. Alerts if the compilations is greater than 10% of BatchRequests, and there are more than 1 batch requests/s.

    98

    1

    95

    2

    219

    3

    124

    4

    80

    5

    115

    6

    233

    7

    119

    8

    101

    9

    221

    10

    264

    11

    99

    12

    31

    13

    227

    14

    290

    15

    Does the number of compilations per second is always high in SCCM 2002?

    Is there an average value which is correct and expected which could allow to decrease the number of alerts as by default the monitoring system is set to 0!?

    27,000 Clients

    Primary Server (1)

    Management Servers (2)

    Distribution Points Servers(13)

    IBCM (1)

    Software Updates Point Servers (3)

    etc...

    Thanks,
    Dom


    Security / System Center Configuration Manager Current Branch / SQL


    • 已编辑 Felyjos 2020年7月3日 22:04
    2020年7月3日 22:02

全部回复

  • Hi,

    This might be a question more inclined to SQL.
    A high number of compilations (greater than 100 per second) can indicate a high server workload or that there is more recompilation overhead than necessary.
    I will do more research about the number of compilations in SCCM 2002.

    Regards,
    Allen

    Please remember to mark the replies as answers if they help.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    2020年7月6日 9:18
  • Hello,

    I asked the same question in the SQL forum and they send me to the Application team support to know if they have some clue...

    Thanks,

    Dom


    Security / System Center Configuration Manager Current Branch / SQL

    2020年7月10日 21:12