Before closing all the Browser Tabs that has been cluttering my Desktop, I went back and looked at Jason Strate’s “Index Resources” available here.
He has links to Microsoft’s resources.
I found the ones under the “Microsoft Certified Master (MCM) Readiness Videos” to be especially useful.
The one we will cover in this post is “Indexing Strategies“.
Here is a link to the Video Archives that are related to SQL Server.
SQL Server Video Archive
And, here is a snapshot of the ones related to Indexes:
Databases support targeted row level, set level, or data mining queries. And, they either return individual or aggregated sets.
I found the delineation she makes between Stream and Hash Aggregates to be profound.
And, so wanted to see if we have Warnings as a result of Aggregates.
There are a few guides that we can use to determine the extent to which we have Hash Aggregates; which she says is the less performant than Stream aggregates.
Our toolsets includes:
- SQL Profiler
- Query Plans
Let us query the Query Plans for Warnings and project the existence of Stream and Hash aggregate operators.
declare @databaseID int declare @database_MSDB sysname declare @databaseID_MSDB int set @database_MSDB = 'msdb' set @databaseID_MSDB = db_id(@database_MSDB) set @databaseID = db_id() ;WITH XMLNAMESPACES (DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT cp.plan_handle , qp.query_plan , qs.last_elapsed_time , qs.last_worker_time , time_difference = (qs.last_elapsed_time - qs.last_worker_time) , qs.execution_count , last_execution_time , [databaseID] = db_name(queryText.[dbid]) , [objectName] = object_schema_name ( queryText.[objectid] , queryText.[dbid] ) + '.' + object_name ( queryText.[objectid] , queryText.[dbid] ) , [sqlText] = queryText.[text] , [node] = i.query('.') , [warning] = cast(i.query('local-name(.)') as varchar) , [databaseName] = db_name( (cast (tblDEPA_DB.value as int )) ) , [StreamAggregateExist] = Query_Plan.exist('//RelOp[@PhysicalOp="Stream Aggregate"]') , [HashMashAggregateExist] = Query_Plan.exist('//RelOp[@PhysicalOp="Hash Match"][@LogicalOp="Aggregate"]') FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp CROSS APPLY qp.query_plan.nodes('//Warnings/*') x(i) CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) as tblDEPA_DB CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) queryText INNER JOIN sys.dm_exec_query_stats qs on cp.[plan_handle] = qs.[plan_handle] where qp.query_plan.exist('//Warnings')=1 and ( ([querytext].[text] is null) or ( querytext.[text] not like ( '%sys%' ) ) ) and tblDEPA_DB.[attribute] = 'dbid' --skip databases --msdb and tblDEPA_DB.[value] not in ( @databaseID_MSDB ) --narrow search to current database and tblDEPA_DB.[value] = @databaseID go
- We explicitly skipped msdb has it has warnings; and how to address those warnings is up to MSFT
Here is the Query Plan…
The query that generated the warning is
select @tranIDMax = max(tblSVVL.[tranID]) from [dbo].[StudentVoiceVerificationLog] tblSVVL
We can see that the query is not so indexable as it is not filtering on anything. It is simply asking for the max on the TranID column.
Here are existing indexes on the table.
exec sp_helpindexinfo 'dbo', 'StudentVoiceVerificationLog'
- We can see that we do not have any helpful indexes on the column we are aggregating (tranID)
And, here are the statistics.
declare @object sysname set @object = 'dbo.StudentVoiceVerificationLog' ; with cteStatColumn ( [object_id] , stats_id , name , columnNames ) as ( SELECT s.[object_id] , s.stats_id , s.name , STUFF( ( SELECT ', ' + [c].[name] from sys.stats_columns AS sc INNER JOIN sys.columns AS c ON sc.object_id = c.object_id AND c.column_id = sc.column_id WHERE s.object_id = sc.object_id AND s.stats_id = sc.stats_id ORDER by stats_column_id FOR XML PATH('') ) ,1,1,'' ) as [columnNames] FROM sys.stats AS s ) SELECT [object] = quoteName ( object_schema_name ( s.object_id ) ) + quoteName ( object_name ( s.object_id ) ) , [statID] = s.stats_id , [statistic] = s.name , s.columnNames FROM cteStatColumn as s WHERE s.object_id = OBJECT_ID(@object);
- Also, we do not have any statistics on same column (tranID)
Let us quickly create an index on TranID.
The Fill Factor can be 100% depending on your use-case.
We also targeted a different FileGroup, bit skipped that delineation for simplicity.
create index [INDX_DBA_TranID] on [dbo].[StudentVoiceVerificationLog] ( [TranID] ) with ( FILLFACTOR=95 ) ;
Query – Post Index Creation
No more warnings.
The creation of an helpful index also allows for a more focused Index Scan.
Keep in mind it is not a seek, still a Scan.
Whether you choose to create yet another Index is on you.
But, either way be on the lookout for the Stream and Hash Aggregate Operators and the cost of each.
No spoiler alerts on Kimberly Tripp’s video as I know it is worth viewing.