How can I determine what is using a particular resource (table, view, or function, etc) on my SQL Server?

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) ''
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 = @

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!

StackExchange Database Administrators Q#39017, answer score: 3