use dbx;
select foo
from db1.dbo.table
join db2.dbo.table on condition
where some_function();
This query consumed lots of CPU and requested a large memory grant. In which database? The information you want simply doesn't exist as a concept. As a human with insight knowledge and with hindsight benefit, you probably would be able to explain why 75% of CPU is due to db1 and 15% is due to db2. But ultimately you just can assign queries to a database. The fact that some (ok, most) queries are 100% contained inside a db does not mean that all query resources can be assigned deterministically to a db.
However, for practical means is relatively simple to automate exactly what you did in your post: inspect the plans and identify all physical access operators locations and use this info to assign the query resources to a DB.
with xmlnamespaces (default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
select x.value(N'@NodeId',N'int') as NodeId
, x.value(N'@PhysicalOp', N'sysname') as PhysicalOp
, x.value(N'@LogicalOp', N'sysname') as LogicalOp
, ox.value(N'@Database',N'sysname') as [Database]
, ox.value(N'@Schema',N'sysname') as [Schema]
, ox.value(N'@Table',N'sysname') as [Table]
, ox.value(N'@Index',N'sysname') as [Index]
, ox.value(N'@IndexKind',N'sysname') as [IndexKind]
, x.value(N'@EstimateRows', N'float') as EstimateRows
, x.value(N'@EstimateIO', N'float') as EstimateIO
, x.value(N'@EstimateCPU', N'float') as EstimateCPU
, x.value(N'@AvgRowSize', N'float') as AvgRowSize
, x.value(N'@TableCardinality', N'float') as TableCardinality
, x.value(N'@EstimatedTotalSubtreeCost', N'float') as EstimatedTotalSubtreeCost
, x.value(N'@Parallel', N'tinyint') as DOP
, x.value(N'@EstimateRebinds', N'float') as EstimateRebinds
, x.value(N'@EstimateRewinds', N'float') as EstimateRewinds
, st.*
, pl.query_plan
from sys.dm_exec_query_stats as st
cross apply sys.dm_exec_query_plan (st.plan_handle) as pl
cross apply pl.query_plan.nodes('//RelOp[./*/Object/@Database]') as op(x)
cross apply op.x.nodes('./*/Object') as ob(ox)
Best Answer
Yes, statistics based on indexes can be used to help with query plan creation even if the underlying index isn't used to access data in the plan. Consider that the query optimizer may consider many different query plans and data access paths while creating a query plan. The compiled query plan may end up not using one of the indexes that was considered. That certainly doesn't mean that any query plan that benefited from the statistics of that index needs to be invalidated, right?
An example might be helpful as well. First I'll throw about 6.5 million rows into a heap:
Next I'll create an index on one of the columns and look at the histogram for the statistics object that is automatically created.
Here's the histogram:
Based on the statistics there are 5806440 rows in the table with a value of 1 for
INDEXED_COLUMN
. Now consider this query:The query optimizer has a few different access paths for the data. It also has a few choices for how to calculate the aggregate. One of the considerations for the picking an algorithm for the agggregate is the cardinality estimate of the data. Here's a screenshot of the query plan:
Note that the estimate matches the histogram exactly even though the index isn't used to access data. Newer versions of SQL Server show which statistics were considered during optimization in the query plan. You can see that the statistic associated with the index was used:
However, the
sys.dm_db_index_usage_stats
dmv doesn't report any end user activity on the index.