SQL query to classify and count logged messages I have an SQL query that fetches some stats, and those stats are displayed on a dashboard. The dashboard runs this query every 15 seconds to fetch the most up-to-date data.

The query is very CPU-heavy for the database server (according to the stats provided by Azure SQL Server), and eats 15% of the available CPU capacity of the server.

The query runs on a log (which records messages received from user and the answers returned by a chatbot). It returns four pieces of information, namely conversations, messages, licenses and failed:

SELECT 
    -- Total unique `conversationId`s:
    COUNT (DISTINCT conversationId) AS conversations,

    -- Total records in table:
    COUNT (*) AS messages,

    -- Total unique `licenseId`s:
    COUNT (DISTINCT licenseId) AS licenses,

    -- Total messages where `intent` meets certain criteria:
    (
        SELECT COUNT (*)
        FROM MessageLog
        WHERE intent LIKE 'None' OR intent IS NULL OR intent LIKE ''
      ) AS failed
    FROM MessageLog


What do you think is the most CPU intensive part of that query, and what are the possible ways to consume less CPU?

Update: Here's the DB schema:

CREATE TABLE [dbo].[MessageLog](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [licenseId] [int] NOT NULL,
    [message] [nvarchar](1000) NOT NULL,
    [timestamp] [datetime] NOT NULL,
    [intent] [nvarchar](70) NULL,
    [entities] [nvarchar](3000) NULL,
    [conversationId] [nvarchar](100) NULL,
    [confidence] [float] NULL,
    [failed] [bit] NOT NULL,
    [ipAddress] [varchar](25) NULL,
    [userAgent] [varchar](256) NULL,
    [nlpExecutionTime] [int] NULL,
    [answer] [nvarchar](1000) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MessageLog] ADD  CONSTRAINT [DF_MessageLog_failed]  DEFAULT ((0)) FOR [failed]
GO


There are non-clustered indexes on id, intent, conversationId, licenseId, but it doesn't seem to improve the performance.
Guessing:


  What do you think is the most CPU intensive part of that query, and
  what are the possible ways to consume less CPU?


We don't have to guess, you test it using SSMS and execution plans.

Capacity:


  The query is very CPU-heavy for the database server (according to the
  stats provided by Azure SQL Server), and eats 15% of the available CPU
  capacity of the server.


15% of 0 is still 0, how many CPUs do you have for this instance, if you only have 1 CPU 15% could be considered significant, however the issue is most likely not this query, it is the ration of CPU you've given it. 

Fundamentals:

When you have a table and you are TRYING to use a surrogate key as a primary key, you still have to have a primary key on the object for it to be considered a table. You are currently querying a heap.

Please start by adding a primary key to the table, identity(1,1) is not a primary key, it is a candidate for a primary key, you still have to tell SQL you want it to be the primary key.

Predicate:

How large is this table, it could be the case that you have 4 billion records in the table and you're asking this question with out a filter on date range. Do you intent for this to be a question for all time?  Is there a process that truncates this table, or starts the process over in some fashion?

Indexes:

Indexes, I would create a non cluster index on intent, you could even try a filtered index on the column. 

Performance tuning

You will want to compare the execution plans, find the equivalent of dropcleanbuffers and freeproccache for Azure. Subtreecost is what you're looking to improve, time it takes to return is a non-sequitur. You might need to provide table hints to force it to use the indexes to validate if the index is useful or not.

Tempdb:

What does your tempdb look like, I only ask because of the multiple DISTINCT you use in the queries. If there is a lot of data in the table its dumping to tempdb and disk, what does the I/O look like for tempdb, what does the space look like? 

Code Changes

I would also consider changing the code @Malachi offers sound suggestion. I would also consider SQL Server Window Functions.