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.
