How to save SQL query analytics in the background while still delivering records to user?

I am interested in knowing if there is an efficient way to record the analytics from a SQL query while still providing the records to the requester? I’m currently on MS SQL Server 2012.

Let’s say for example you have a number of ways users an pull records from the database (SSRS, SSIS, web-page based, etc.), for the lack of a better term let’s just call them reports. Over time it would be nice to know which reports are being run, when they are run, how long does it take for a report to run, what is the number of records returned for a given report, etc. so on and so forth. These analytical data-points would allow me to keep an eye on things — most requested, longest running, most active user, etc.

Right now I have set up a system where I have a View that is the full set of “base records” for the report, and the Stored Procedure the end user calls that operates on the View — it calls the View and dumps the filtered results into a @TempTable inside the SProc. In addition to filtering the View returned records based on the User’s needs, the SProc will record the time of execution, duration, record count, supplied arguments, etc., all of which is saved to a separate “Analytics” table for separate analysis. At the end, the SProc simply returns the records from the @TempTable to the User.

While this does work, it just feels clunky and does add a second or two to the total processing time. I am hoping there is a more efficient way to accomplish this kind of background capturing of information?

Note: Third-Party software in not an option for my environment.

Answer

I had a requirement where all requests had to be under 1 second. No small task considering the complexity.

Anyway, this is a dramatically stripped down version. The key here is that I store the request and parameters as XML. You can expand or contract as needed the columns and/or the XML portion.

Take a peek at the dbFiddle

Log Table Structure

CREATE TABLE [dbo].[uniLog](
    [LogNr] [int] IDENTITY(1,1) NOT NULL,
    [LogUTC] [datetime] NULL,
    [LogMS] [int] NULL,
    [LogRows] [int] NULL,
    [LogParam] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

A Dummy Stored Proceedure

Notice the @TimeBeg in the top of the procedure and the INSERT after the END

CREATE PROCEDURE [dbo].[prcDummyProcedure] (
    @SomeStr varchar(50),
    @SomeInt int,
    @AnotherInt int,
    @FromDate date
)

As

Begin

    Set NoCount On
    Declare @TimeBeg datetime = getutcdate()

    -- My Super Awsome Query
    Select Top 5000 *,N=Row_Number() Over (Order By (Select NULL)) From master..spt_values n1, master..spt_values n2

    Declare @Rows int = @@RowCount
End

Insert Into [dbo].[uniLog] (LogUTC,LogMS,LogRows,LogParam) 
values ( GetUTCDate()
        ,DateDiff(MILLISECOND,@TimeBeg,getutcdate())
        ,@Rows
        ,(Select ProcName   = '[dbo].[prcDummyProcedure]'
                ,SomeStr     = @SomeStr
                ,SomeInt     = @SomeInt
                ,AnotherInt  = @AnotherInt
                ,FromDate    = @FromDate
            For XML Raw )
       )

And Now the Test

Exec [dbo].[prcDummyProcedure] 'SomeString',100,200,'2020-01-31'

Select * from [dbo].[uniLog]

The Results

[The results will be what over the SP was designed to do]

The log table was update with

LogNr   LogUTC                   LogMS  LogRows LogParam
6       2020-03-10 19:55:35.423  13     1000    <row ProcName="[dbo].[prcDummyProcedure]" SomeStr="SomeString" SomeInt="100" AnotherInt="200" FromDate="2020-01-31" />