Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been looking for tables that could use better indexing. I've created a stored procedure that provides a list of tables that have no indexes other than a primary key:

CREATE PROCEDURE [dbo].[GetTablesWithPoorIndexing]
(
    @Database sysname
)
AS
BEGIN
    DECLARE @TableList TABLE (
        TableName sysname
    );
    DECLARE @IndexList TABLE (
        ObjectName sysname, 
        IndexName sysname NULL, 
        user_seeks int,
        user_scans int,
        user_lookups int,
        user_updates int,
        last_user_seek datetime,
        last_user_scan datetime,
        last_user_lookup datetime,
        last_user_update datetime
    );
    INSERT INTO @TableList 
    EXEC GetTablesWithoutIndexes @Database;
    INSERT INTO @IndexList 
    EXEC dbo.GetIndexStats @Database;

    SELECT TL.TableName
        , IL.user_lookups + IL.user_scans + IL.user_seeks AS ReadActivity
        , IL.user_updates AS WriteActivity
    FROM @TableList TL 
        INNER JOIN @IndexList IL ON TL.TableName = IL.ObjectName
    ORDER BY 2 DESC, 3 DESC;
END

CREATE PROCEDURE [dbo].[GetTablesWithoutIndexes]
(
    @Database sysname = ''
)
AS
BEGIN
    SET NOCOUNT ON;
    IF @Database = '' SET @Database = DB_NAME(DB_ID());
    SET @Database = QUOTENAME(@Database);
    DECLARE @cmd nvarchar(max);
    SET @cmd = '';
    SET @cmd = '
SELECT o.name
FROM ' + @Database + '.sys.indexes i
    INNER JOIN ' + @Database + '.sys.dm_db_index_usage_stats ius 
        ON i.index_id = ius.index_id AND i.object_id = ius.object_id
    INNER JOIN ' + @Database + '.sys.objects o 
        on i.object_id = o.object_id
GROUP BY o.name
HAVING COUNT(ius.index_id)<=1;
';
    PRINT @cmd;
    EXEC sp_executesql @cmd;
END

CREATE PROCEDURE [dbo].[GetIndexStats]
(
    @Database sysname = ''
    , @Object sysname = ''
    , @IndexName sysname = ''
)
AS
BEGIN
    SET NOCOUNT ON;
    IF @Database = '' SET @Database = DB_NAME(DB_ID());
    DECLARE @cmd nvarchar(max);
    DECLARE @where nvarchar(max);
    DECLARE @connector nvarchar(max);
    SET @cmd = '
SELECT o.name AS ObjectName, i.name AS IndexName, ius.user_seeks, 
    ius.user_scans, ius.user_lookups, ius.user_updates, ius.last_user_seek,
    ius.last_user_scan, ius.last_user_lookup, ius.last_user_update
FROM ' + @Database + '.sys.objects o 
    INNER JOIN ' + @Database + '.sys.indexes i 
            on o.object_id = i.object_id
    LEFT JOIN ' + @Database + '.sys.dm_db_index_usage_stats ius 
            ON i.object_id = ius.object_id AND i.index_id = ius.index_id
';
    SET @where = '';
    SET @connector = 'WHERE '
    IF @Object <> ''
    BEGIN
        SET @where = @where + @connector + 'o.name = ''' + @Object + '''';
        SET @connector = ' AND ';
    END
    IF @IndexName <> ''
    BEGIN
        SET @where = @where + @connector + 'i.name = ''' + @IndexName + '''';
        SET @connector = ' AND ';
    END
    IF @where <> '' SET @cmd = @cmd + @where;
    SET @cmd = @cmd + CHAR(13) + CHAR(10) + 'ORDER BY i.index_id;'
    EXEC sp_executesql @cmd;
    PRINT @cmd;
END

The results of running these stored procs has raised some questions about several objects on SQL Server that are being called repetitively.

I can no longer remember what system is using them, and although the purpose is easily identified by looking at the source code for the given object, I'd like to identify what process is using them.

Since I'm using SQL Server 2012, I thought I could use Extended Events to find the culprit. The following statement sets up an Extended Event. This Extended Event definition does not save it's results anywhere, so you must right-click the event and "Watch Live Data" to see events as they happen (or very near to as-they-happen).

DROP EVENT SESSION FindCaller ON SERVER;

CREATE EVENT SESSION [FindCaller] ON SERVER 
ADD EVENT sqlserver.sql_batch_starting
(
    ACTION
    (
        sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.database_name
        , sqlserver.nt_username
        , sqlserver.session_id
        , sqlserver.sql_text
    )
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'ObjectInQuestion'))
)
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=OFF
    , STARTUP_STATE=OFF
);

In the above code, ObjectInQuestion should be changed to the text you are looking for - this could be the name of a table, view, Stored Proc, etc that appears in the SQL Text executed by the server.

I ran this extended event session, but it returns no events, even if I manually execute SQL text with the ObjectInQuestion in a new session.

Since Extended Events are the "best new thing" in SQL Server for diagnostics, I must be missing something in my definition.

share|improve this question
add comment

1 Answer 1

I did a DuckDuckGo.com search for the like_i_sql_unicode_string since there appears to be no documentation that describes it included with SQL Server, and came across this Connect item, https://connect.microsoft.com/SQLServer/feedback/details/699053/sql-batch-completed-event-returns-0-for-row-count#details - it contains a sample extended event in the "steps to reproduce" section of the Connect item. In the sample extended event is this text, which wraps the search terms in question in the standard SQL Server wildcard, %.

 WHERE ([sqlserver].[like_i_sql_unicode_string]([batch_text],N'%sys.objects%')))

I modified my extended event definition like:

DROP EVENT SESSION FindCaller ON SERVER;

CREATE EVENT SESSION [FindCaller] ON SERVER 
ADD EVENT sqlserver.sql_batch_starting
(
    ACTION
    (
        sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.database_name
        , sqlserver.nt_username
        , sqlserver.session_id
        , sqlserver.sql_text
    )
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ObjectInQuestion%'))
)
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=OFF
    , STARTUP_STATE=OFF
);

Now, when I 'Watch Live Data' I see the culprit statements, along with the actual SQL text being executed, the name of the application, the client machine name, etc. Quite useful.

The take-home for me is the Extended Event GUI needs better documentation!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.