Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Bad query performance / odd index behavior (I assume)

Posted on 2017-10-12
31
Low Priority
?
46 Views
Last Modified: 2017-10-18
Having a really odd issue with a report for a client.
Running 2008R2, database is smallish, only like 3-4GB.  Largest table for OrderDetails is around 4 million rows.
Server is a bit underpowered, but not horrible, and in most cases performs fine.

One query on a velocity report is stumping me.

declare	@StartDate date = '2016-10-01',
	@EndDate date = '2017-11-01',
	@ClientID int = 3282


select p.ProductName, u.UOM, x.TimesOrdered as [Times Ordered], x.TotalQuantity as [Total Qty], x.TotalPrice as [Total Price], x.AverageQuantity as [Avg Qty], x.AveragePrice as [Avg Price]
from (
	select od.ProductID, od.UOMID, count(1) as TimesOrdered, sum(od.Quantity) as TotalQuantity, sum(od.Quantity * od.Price) as TotalPrice, avg(od.Quantity) as AverageQuantity, avg(od.Price) as AveragePrice
	from Orders o
	join OrderDetails od on o.OrderID = od.OrderID
	join ProductUOMs pu on pu.ProductID = od.ProductID and pu.UOMID = od.UOMID
	where ClientID = @ClientID and o.Active = 1 and o.Status = 1 and od.Active = 1 and o.OrderDate between @StartDate and @EndDate
	group by od.ProductID, od.UOMID
) x
join Products p on x.ProductID = p.ProductID
join UnitsOfMeasure u on x.UOMID = u.UOMID
order by p.ProductName, u.UOM

Open in new window


The inner query, executed by itself, which doing most of the heavy lifting, returns in a fraction of a second.
Joined with the outer one, I usually end up killing after about 5 - 6 minutes of runtime.

If I put the inner query into a temp table, and run from there instead of the subquery, again in a fraction of a second.
If i LEAVE the subquery in, and remove the "UnitsOfMeasure" table, AGAIN a fraction of a second.

Units of measure is by a longshot the smallest table of the entire group, not sure where to even start looking on where the issue is.
There are a few indexes with some fragmentation, but not the UOM one.

Any thoughts are appreciated.
0
Comment
Question by:Snarf0001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 8
  • 7
  • +1
31 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 42329227
Can you get and attach an execution plan file (.sqlplan)?
0
 
LVL 23

Author Comment

by:Snarf0001
ID: 42329234
For sure, already had them actually but forgot to attach.
These are only the estimated plans, client is still in use so I can't run the real plan on the problem query yet.

3 of them on there, "FullQuery" which is the problem one, "InterimTable" which is when I take the subquery out and put it into a holding table first, and "NoUOM" which is the same as full but without adding in the UOM table.

Thanks.
FullQuery.sqlplan
InterimTableQuery.sqlplan
NoUOMQuery.sqlplan
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 42329237
You don't seem to use this table:  join ProductUOMs pu on pu.ProductID = od.ProductID and pu.UOMID = od.UOMID

It is in the subquery but not used anywhere that I can see.

How does this perform?
      SELECT
            od.ProductID
          , u.UOM
          , COUNT(1)                    AS TimesOrdered
          , SUM(od.Quantity)            AS TotalQuantity
          , SUM(od.Quantity * od.Price) AS TotalPrice
          , AVG(od.Quantity)            AS AverageQuantity
          , AVG(od.Price)               AS AveragePrice
      FROM Orders o
      JOIN OrderDetails od ON o.OrderID = od.OrderID
      JOIN UnitsOfMeasure u ON od.UOMID = u.UOMID
      WHERE ClientID = @ClientID
      AND o.Active = 1
      AND o.Status = 1
      AND od.Active = 1
      AND o.OrderDate BETWEEN @StartDate AND @EndDate
      GROUP BY
            od.ProductID
          , u.UOM

Open in new window

0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 23

Author Comment

by:Snarf0001
ID: 42329244
It's not really being used in that query, it's more of a safeguard.
They have the ability to change that "ProductUOM".  This is for a produce company, so that's the link to say for example Head Lettuce is sold by the "Case" and by "Pound".  They may change that to "Piece" instead of pound, which for some reason they then do NOT want the pounds to show in the report.

And the query above returned very quickly.
There's a small fraction of times where they do actually have duplicated UOM names, which is why I was hesitant to group by the name instead of the ID... but unlikely it would be an issue for a single product.

I'm still just SUPER curious as to what in the hell the bottleneck is as the query was?
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 42329247
according the to full execution plan it is using an index scan, but it isn't an expensive operation
sqlplan-full.png
0
 
LVL 23

Author Comment

by:Snarf0001
ID: 42329250
Few things if it helps at all:

1) The actual report is in a stored proc, so my first thought was parameter sniffing issues, but all the plans I sent were running direct queries NOT in the proc, so I don't think that's the case.
2) Some of the more fragmented indexes ARE on the PKs for Orders and OrderDetails, but I would have expected that to cause the perf issues on the inner query, not when I joined to the small tables after that.
3) Their office is off for an hour or so, so I was able to wait for the full one to execute.  Real execution plan and client stats are attached.
REALFullQuery.sqlplan
ClientStats.png
0
 
LVL 23

Author Comment

by:Snarf0001
ID: 42329251
And my mistake, OrderDetails is not.  It's only at about 0.5% fragmentation, Orders is around 43%.
But same confusion, I would have expected that to have impact on the inner query.

And FYI, thanks a lot for the assist.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 42329254
The od.UOMID could be retained in the select & group by if needed.

I'm unable to do much more, perhaps others can. Note I an NOT a DBA.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 42329257
We cross over on posts.
I sense you have this well under control. Good luck, happy to help (not that I did much).

Cheers, Paul
0
 
LVL 23

Author Comment

by:Snarf0001
ID: 42329259
Thanks man, and no worries.  I'll reach out.
Really appreciate the effort ;)
0
 
LVL 60

Expert Comment

by:HainKurt
ID: 42329314
do you still have issues? is it ok now?
and is the inner select running very fast?

do you have appropriate indexes on all tables (all columns on joins)?

what is happening if you comment out last line, order statement?
0
 
LVL 23

Author Comment

by:Snarf0001
ID: 42329324
No, issues are still there.  I can get around the issue with making a temp table, and fix the problem, I'm more concerned / curious on why the problem is happening.
Indexes are on all tables and joins, in this particular case, almost everything involved is a PK.
Tried removing ordering before, zero difference.  It really seems to only be on the full system when I bring in UOMs (the smallest table).

And yes, the inner query returns in almost no time at all.
0
 
LVL 60

Expert Comment

by:HainKurt
ID: 42329328
what about

select count(1) from ( ... inner select here ...)

Open in new window


is it fast?

what about this? I moved "od.Active = 1" to join...

select count(*) from (
	select od.ProductID, od.UOMID, count(1) as TimesOrdered, sum(od.Quantity) as TotalQuantity, sum(od.Quantity * od.Price) as TotalPrice, avg(od.Quantity) as AverageQuantity, avg(od.Price) as AveragePrice
	from Orders o
	join OrderDetails od on o.OrderID = od.OrderID and and od.Active = 1
	join ProductUOMs pu on pu.ProductID = od.ProductID and pu.UOMID = od.UOMID
	where ClientID = @ClientID and o.Active = 1 and o.Status = 1 and o.OrderDate between @StartDate and @EndDate
	group by od.ProductID, od.UOMID
) x

Open in new window

0
 
LVL 23

Author Comment

by:Snarf0001
ID: 42329332
But that's still the inner query.  That's the one I would have figured was the problem, as it's doing most of the work on the heavy joins on much larger tables.  And as the query is, THAT one is returning in about 0.1 of a second.

It's when I bring in that to the outer query that the bottleneck happens.  And as per above, the issue ONLY happens when the UnitsOfMeasure table is brought in, which has about 50 rows in it.

Hence the total confusion.
0
 
LVL 60

Expert Comment

by:HainKurt
ID: 42329333
did you run the count queries, are they fast?

@ ID: 42329328
0
 
LVL 60

Expert Comment

by:HainKurt
ID: 42329334
what is the total count on

1. inner select
2. Products
3. UnitsOfMeasure

normally we should order them from smaller to larger :)
0
 
LVL 23

Author Comment

by:Snarf0001
ID: 42329336
The count on the entire query (not just the sub you hinted at, but the problem one) does return very fast.

The subquery results in 98 rows.
Products (whole table) has 3029.
UnitsOfMeasure has 29.

So why would the count return that fast when actually rendering results is that bad?
0
 
LVL 60

Expert Comment

by:HainKurt
ID: 42329337
what about this query?

select p.ProductName, u.UOM, x.TimesOrdered as [Times Ordered], x.TotalQuantity as [Total Qty], x.TotalPrice as [Total Price], x.AverageQuantity as [Avg Qty], x.AveragePrice as [Avg Price]
  from Products p
 inner join (
       select od.ProductID, od.UOMID, count(1) as TimesOrdered, sum(od.Quantity) as TotalQuantity, sum(od.Quantity * od.Price) as TotalPrice, avg(od.Quantity) as AverageQuantity, avg(od.Price) as AveragePrice
         from Orders o
        inner join OrderDetails od on o.OrderID = od.OrderID and od.Active = 1
        inner join ProductUOMs pu on pu.ProductID = od.ProductID and pu.UOMID = od.UOMID
        where ClientID = @ClientID and o.Active = 1 and o.Status = 1 and o.OrderDate between @StartDate and @EndDate
        group by od.ProductID, od.UOMID
       ) x on x.ProductID = p.ProductID
 inner join UnitsOfMeasure u on x.UOMID = u.UOMID
 order by p.ProductName, u.UOM

Open in new window

0
 
LVL 60

Expert Comment

by:HainKurt
ID: 42329340

The subquery results in 98 rows.
Products (whole table) has 3029.
UnitsOfMeasure has 29

with these numbers, even without any index, query should not take more than a few seconds :)
is there any lock on tables? restart the server...
0
 
LVL 23

Author Comment

by:Snarf0001
ID: 42329341
Still executing... on and on and on. So same issue.
0
 
LVL 23

Author Comment

by:Snarf0001
ID: 42329342
Sorry, responded to the last one before I saw the recent.
That's what's tripping me up.   There's zero reason I can see why the tiny UOM table could POSSIBLY effect the query that badly.
Everywhere there should be a bottleneck, there isn't one.  Only that small table.

Server was rebooted this morning, no difference.
Indexes seem fine, I'm totally lost.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 42329379
but if we moved the UOM table into the inner query: it is fast... correct? (& we can retain the od.UOMID in the logic)
      SELECT
            od.ProductID
          , od.UOMID
          , u.UOM
          , COUNT(1)                    AS TimesOrdered
          , SUM(od.Quantity)            AS TotalQuantity
          , SUM(od.Quantity * od.Price) AS TotalPrice
          , AVG(od.Quantity)            AS AverageQuantity
          , AVG(od.Price)               AS AveragePrice
      FROM Orders o
      JOIN OrderDetails od ON o.OrderID = od.OrderID
      JOIN UnitsOfMeasure u ON od.UOMID = u.UOMID
      WHERE ClientID = @ClientID
      AND o.Active = 1
      AND o.Status = 1
      AND od.Active = 1
      AND o.OrderDate BETWEEN @StartDate AND @EndDate
      GROUP BY
            od.ProductID
          , od.UOMID
          , u.UOM

Open in new window


Another thing to try would be to place your existing inner query into a CTE which may have a similar effect as a temp table.

By the way, it is the Order Details table that is large according to the execution plan for the REAL Full query.
0
 
LVL 60

Expert Comment

by:HainKurt
ID: 42329384
By the way, it is the Order Details table that is large according to the execution plan for the REAL Full query.

looks like ortder and orderdetails table are huge, 2-3 millions each...
products is like 3K
the others are tiny, lookup tables...

I am assuming we have some corrupt indexes here...
rebuilding all indexes may solve the issue
0
 
LVL 60

Expert Comment

by:HainKurt
ID: 42329389
and one more thing

even creating a new table as

select * into UnitsOfMeasure2 from UnitsOfMeasure

Open in new window


and using this in the query did not solve the issue...
using CTE did not solve the issue...

using CTE and left join solved the issue...

this one worked fine

with x as (
       select od.ProductID, od.UOMID, count(1) as TimesOrdered, sum(od.Quantity) as TotalQuantity, sum(od.Quantity * od.Price) as TotalPrice, avg(od.Quantity) as AverageQuantity, avg(od.Price) as AveragePrice
         from Orders o
        inner join OrderDetails od on o.OrderID = od.OrderID and od.Active = 1
        inner join ProductUOMs pu on pu.ProductID = od.ProductID and pu.UOMID = od.UOMID
        where ClientID = @ClientID and o.Active = 1 and o.Status = 1 and o.OrderDate between @StartDate and @EndDate
        group by od.ProductID, od.UOMID
)
select p.ProductName, u.UOM, x.TimesOrdered as [Times Ordered], x.TotalQuantity as [Total Qty], x.TotalPrice as [Total Price], x.AverageQuantity as [Avg Qty], x.AveragePrice as [Avg Price]
  from Products p
  left join x on x.ProductID = p.ProductID
  left join UnitsOfMeasure u on x.UOMID = u.UOMID
 order by p.ProductName, u.UOM

Open in new window


I am guessing we have issues with some indexes here...
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1000 total points
ID: 42330183
The essential problem for this specific query is that SQL has a very bad row estimate and thus is using the wrong type of join.  The quickest fix should be:

select
      p.ProductName,
      u.UOM,
      x.TimesOrdered as [Times Ordered], x.TotalQuantity as [Total Qty], x.TotalPrice as [Total Price], x.AverageQuantity as [Avg Qty], x.AveragePrice as [Avg Price]
from (
      select od.ProductID, od.UOMID, count(1) as TimesOrdered, sum(od.Quantity) as TotalQuantity, sum(od.Quantity * od.Price) as TotalPrice, avg(od.Quantity) as AverageQuantity, avg(od.Price) as AveragePrice
      from dbo.Orders o
      inner join dbo.OrderDetails od on o.OrderID = od.OrderID
      inner join dbo.ProductUOMs pu on pu.ProductID = od.ProductID and pu.UOMID = od.UOMID
      where ClientID = @ClientID and o.Active = 1 and o.Status = 1 and od.Active = 1 and o.OrderDate between @StartDate and @EndDate
      group by od.ProductID, od.UOMID
) x
inner join dbo.Products p on x.ProductID = p.ProductID
inner hash join dbo.UnitsOfMeasure u on x.UOMID = u.UOMID
order by p.ProductName, u.UOM

Please try that and let me know how it goes.

I'll post other performance notes on the tables later, when I get more time to more fully analyze them.  As usual, the biggest overall gains throughout your system would come from changing the clustered indexes.  The pernicious myth that tables should be clustered on identity by default has done more to damage db performance than anything else.  

For example, OrderDetails should be clustered on OrderID first; and the Orders table should likely be clustered by OrderDate first: the OrderID can still be the PK, it will just be nonclustered.

At any rate, for the further analysis, it would help greatly to have the results of the following queries for the tables used above:

SET NOCOUNT ON;
SET DEADLOCK_PRIORITY -8;

IF OBJECT_ID('tempdb.dbo.#index_specs') IS NOT NULL
    DROP TABLE dbo.#index_specs
IF OBJECT_ID('tempdb.dbo.#index_missing') IS NOT NULL
    DROP TABLE dbo.#index_missing
IF OBJECT_ID('tempdb.dbo.#index_usage')  IS NOT NULL
    DROP TABLE dbo.#index_usage

CREATE TABLE dbo.#index_specs (
    object_id int NOT NULL,
    index_id int NOT NULL,
    min_compression int NULL,
    max_compression int NULL,
    drive char(1) NULL,
    alloc_mb decimal(9, 1) NOT NULL,
    alloc_gb AS CAST(alloc_mb / 1024.0 AS decimal(9, 2)),
    used_mb decimal(9, 1) NOT NULL,
    used_gb AS CAST(used_mb / 1024.0 AS decimal(9, 2)),
    rows bigint NULL,
    table_mb decimal(9, 1) NULL,
    table_gb AS CAST(table_mb / 1024.0 AS decimal(9, 2)),
    size_rank int NULL,
    approx_max_data_width bigint NULL,
    UNIQUE CLUSTERED ( object_id, index_id )
    )

DECLARE @list_missing_indexes bit
DECLARE @list_missing_indexes_summary bit
DECLARE @include_schema_in_table_names bit
DECLARE @table_name_pattern sysname
DECLARE @order_by smallint --1=table_name; 2=size; -2=size DESC;.
DECLARE @format_counts smallint --1=with commas, no decimals; 2/3=with K=1000s,M=1000000s, with 2=0 dec. or 3=1 dec. places;.
DECLARE @debug smallint

--NOTE: showing missing indexes can take some time; set to 0 if you don't want to wait.
SET @list_missing_indexes = 1
SET @list_missing_indexes_summary = 0
SET @include_schema_in_table_names = 0
SET @table_name_pattern = '%'
SET @order_by = 1
SET @format_counts = 3
SET @debug = 0

PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)

DECLARE @is_compression_available bit
DECLARE @sql varchar(max)

IF CAST(SERVERPROPERTY('ProductVersion') AS varchar(30)) LIKE '9%'
OR (CAST(SERVERPROPERTY('Edition') AS varchar(40)) NOT LIKE '%Developer%' AND 
    CAST(SERVERPROPERTY('Edition') AS varchar(40)) NOT LIKE '%Enterprise%')
    SET @is_compression_available = 0
ELSE
    SET @is_compression_available = 1

SET @sql = '
INSERT INTO #index_specs ( object_id, index_id,' +
    CASE WHEN @is_compression_available = 0 THEN '' ELSE '
    min_compression, max_compression,' END + '
    alloc_mb, used_mb, rows )
SELECT 
    base_size.object_id, 
    base_size.index_id, ' +
    CASE WHEN @is_compression_available = 0 THEN '' ELSE '
    base_size.min_compression,
    base_size.max_compression,' END + '
    (base_size.total_pages + ISNULL(internal_size.total_pages, 0)) / 128.0 AS alloc_mb,
    (base_size.used_pages + ISNULL(internal_size.used_pages, 0)) / 128.0 AS used_mb,
    base_size.row_count AS rows
FROM (
    SELECT
        dps.object_id,
        dps.index_id, ' +   
        CASE WHEN @is_compression_available = 0 THEN '' ELSE '
        ISNULL(MIN(p.data_compression), 0) AS min_compression,
        ISNULL(MAX(p.data_compression), 0) AS max_compression,' END + '    
        SUM(dps.reserved_page_count) AS total_pages,
	    SUM(dps.used_page_count) AS used_pages,
	    SUM(CASE WHEN dps.index_id IN (0, 1) THEN dps.row_count ELSE 0 END) AS row_count
    FROM sys.dm_db_partition_stats dps ' +
    CASE WHEN @is_compression_available = 0 THEN '' ELSE '
    INNER JOIN sys.partitions p /* WITH (NOLOCK)*/ ON
        p.partition_id = dps.partition_id ' END + '
    WHERE dps.object_id > 100
    GROUP BY 
        dps.object_id,
        dps.index_id
) AS base_size
LEFT OUTER JOIN (
    SELECT 
        it.parent_id,
        SUM(dps.reserved_page_count) AS total_pages,
	    SUM(dps.used_page_count) AS used_pages
    FROM sys.internal_tables it /* WITH (NOLOCK)*/
    INNER JOIN sys.dm_db_partition_stats dps /* WITH (NOLOCK)*/ ON 
        dps.object_id = it.parent_id
    WHERE it.internal_type IN ( ''202'', ''204'', ''211'', ''212'', ''213'', ''214'', ''215'', ''216'' )
    GROUP BY
        it.parent_id
) AS internal_size ON base_size.index_id IN (0, 1) AND internal_size.parent_id = base_size.object_id
'
IF @debug >= 1
    PRINT @sql
EXEC(@sql)

UPDATE [is]
SET approx_max_data_width = index_cols.approx_max_data_width
FROM #index_specs [is]
INNER JOIN (
    SELECT index_col_ids.object_id, index_col_ids.index_id, 
        SUM(CASE WHEN c.max_length = -1 THEN 16 ELSE c.max_length END) AS approx_max_data_width
    FROM (
        SELECT ic.object_id, ic.index_id, ic.column_id
            --,object_name(ic.object_id)
        FROM sys.index_columns ic
        WHERE
            ic.object_id > 100
        UNION
        SELECT i_nonclus.object_id, i_nonclus.index_id, ic_clus.column_id
            --,object_name(i_nonclus.object_id)
        FROM sys.indexes i_nonclus
        CROSS APPLY (
            SELECT ic_clus2.column_id
                --,object_name(ic_clus2.object_id),ic_clus2.key_ordinal
            FROM sys.index_columns ic_clus2
            WHERE
                ic_clus2.object_id = i_nonclus.object_id AND
                ic_clus2.index_id = 1 AND
                ic_clus2.key_ordinal > 0 --technically superfluous, since clus index can't have include'd cols anyway
        ) AS ic_clus
        WHERE
            i_nonclus.object_id > 100 AND
            i_nonclus.index_id > 1
    ) AS index_col_ids
    INNER JOIN sys.columns c ON c.object_id = index_col_ids.object_id AND c.column_id = index_col_ids.column_id
    GROUP BY index_col_ids.object_id, index_col_ids.index_id    
) AS index_cols ON index_cols.object_id = [is].object_id AND index_cols.index_id = [is].index_id

UPDATE ispec
SET table_mb = ispec_ranking.table_mb,
    size_rank = ispec_ranking.size_rank
FROM #index_specs ispec
INNER JOIN (
    SELECT *, ROW_NUMBER() OVER(ORDER BY table_mb DESC, rows DESC, OBJECT_NAME(object_id)) AS size_rank
    FROM (
        SELECT object_id, SUM(alloc_mb) AS table_mb, MAX(rows) AS rows
        FROM #index_specs
        GROUP BY object_id
    ) AS ispec_allocs        
) AS ispec_ranking ON
    ispec_ranking.object_id = ispec.object_id

IF @list_missing_indexes = 1
BEGIN
    SELECT
        IDENTITY(int, 1, 1) AS ident,
        DB_NAME(mid.database_id) AS Db_Name,
        CONVERT(varchar(10), GETDATE(), 120) AS capture_date,
        ispec.size_rank, ispec.table_mb,
        CASE WHEN @format_counts = 1 THEN REPLACE(CONVERT(varchar(20), CAST(dps.row_count AS money), 1), '.00', '')
             WHEN @format_counts = 2 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix 
                 WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS int) AS varchar(20)) + ca1.row_count_suffix
             WHEN @format_counts = 3 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix 
                 WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS decimal(14, 1)) AS varchar(20)) + ca1.row_count_suffix
             ELSE CAST(dps.row_count AS varchar(20)) END AS row_count,
        CASE WHEN @include_schema_in_table_names = 1 THEN OBJECT_SCHEMA_NAME(mid.object_id) + '.' 
             ELSE '' END + OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,
        mid.equality_columns, mid.inequality_columns, mid.included_columns,        
        user_seeks, user_scans, cj1.max_days_active, unique_compiles, 
        last_user_seek, last_user_scan, 
        CAST(avg_total_user_cost AS decimal(9, 2)) AS avg_total_user_cost,
        CAST(avg_user_impact AS decimal(9, 2)) AS [avg_user_impact%],
        system_seeks, system_scans, last_system_seek, last_system_scan,
        CAST(avg_total_system_cost AS decimal(9, 2)) AS avg_total_system_cost,
        CAST(avg_system_impact AS decimal(9, 2)) AS [avg_system_impact%],
        mid.statement, mid.object_id, mid.index_handle
    INTO #index_missing
    FROM sys.dm_db_missing_index_details mid /*WITH (NOLOCK)*/
    CROSS JOIN (
        SELECT DATEDIFF(DAY, create_date, GETDATE()) AS max_days_active FROM sys.databases /*WITH (NOLOCK)*/ WHERE name = 'tempdb'
    ) AS cj1
    LEFT OUTER JOIN sys.dm_db_missing_index_groups mig /*WITH (NOLOCK)*/ ON
        mig.index_handle = mid.index_handle
    LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs /*WITH (NOLOCK)*/ ON
        migs.group_handle = mig.index_group_handle
    LEFT OUTER JOIN sys.dm_db_partition_stats dps /*WITH (NOLOCK)*/ ON
        dps.object_id = mid.object_id AND
        dps.index_id IN (0, 1)
    CROSS APPLY (
        SELECT CASE WHEN dps.row_count >= 1000000 THEN 'M' WHEN dps.row_count >= 1000 THEN 'K' ELSE '' END AS row_count_suffix
    ) AS ca1
    OUTER APPLY (
        SELECT ispec.table_mb, ispec.size_rank
        FROM dbo.#index_specs ispec
        WHERE
            ispec.object_id = mid.object_id AND
            ispec.index_id IN (0, 1)
    ) AS ispec
    WHERE
        1 = 1 
        AND mid.database_id = DB_ID()
        AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern
        AND OBJECT_NAME(mid.object_id) NOT LIKE 'tmp%'
    ORDER BY
        --avg_total_user_cost * (user_seeks + user_scans) DESC,
        Db_Name,
        CASE WHEN @order_by IN (-2, 2) THEN ispec.size_rank * -SIGN(@order_by) ELSE 0 END,
        Table_Name,
        equality_columns, inequality_columns,
        user_seeks DESC
    SELECT *
    FROM #index_missing
    ORDER BY ident
    IF @list_missing_indexes_summary = 1
    BEGIN
        SELECT 
            derived.Size_Rank, derived.table_mb,
            derived.Table_Name, derived.Equality_Column, derived.Equality#, derived.User_Seeks, 
            ISNULL((SELECT SUM(user_seeks)
             FROM #index_missing im2
             CROSS APPLY DBA.dbo.DelimitedSplit8K (inequality_columns, ',') ds
             WHERE im2.Size_Rank = derived.Size_Rank AND
                 LTRIM(RTRIM(ds.Item)) = derived.Equality_Column
            ), 0) AS Inequality_Seeks,
            derived.User_Scans, derived.Last_User_Seek, derived.Last_User_Scan,
            derived.Max_Days_Active, derived.Avg_Total_User_Cost, derived.Approx_Total_Cost
        FROM (
            SELECT 
                Size_Rank, MAX(table_mb) AS table_mb, Table_Name, LTRIM(RTRIM(ds.Item)) AS Equality_Column, 
                SUM(user_seeks) AS User_Seeks, SUM(user_scans) AS User_Scans,
                MAX(last_user_seek) AS Last_User_Seek, MAX(last_user_scan) AS Last_User_Scan,
                MIN(max_days_active) AS Max_Days_Active,
                MAX(avg_total_user_cost) AS Avg_Total_User_Cost,
                (SUM(user_seeks) + SUM(user_scans)) * MAX(avg_total_user_cost) AS Approx_Total_Cost,
                MAX(ds.ItemNumber) AS Equality#
            FROM #index_missing
            CROSS APPLY DBA.dbo.DelimitedSplit8K (equality_columns, ',') ds
            WHERE equality_columns IS NOT NULL
            GROUP BY size_rank, Table_Name, LTRIM(RTRIM(ds.Item))
        ) AS derived
        ORDER BY Size_Rank, Table_Name, Approx_Total_Cost DESC        
    END --IF
END --IF



PRINT 'Index Usage Stats @ ' + CONVERT(varchar(30), GETDATE(), 120)

-- list index usage stats (seeks, scans, etc.)
SELECT 
    IDENTITY(int, 1, 1) AS ident,
    DB_NAME() AS db_name,
    --ispec.drive AS drv,
    ispec.size_rank, ispec.alloc_mb - ispec.used_mb AS unused_mb, 
    CASE WHEN @include_schema_in_table_names = 1 THEN OBJECT_SCHEMA_NAME(i.object_id /*, DB_ID()*/) + '.' 
         ELSE '' END + OBJECT_NAME(i.object_id /*, i.database_id*/) AS Table_Name,    
    CASE WHEN @format_counts = 1 THEN REPLACE(CONVERT(varchar(20), CAST(dps.row_count AS money), 1), '.00', '')
         WHEN @format_counts = 2 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix 
             WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS int) AS varchar(20)) + ca1.row_count_suffix
         WHEN @format_counts = 3 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix 
             WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS decimal(14, 1)) AS varchar(20)) + ca1.row_count_suffix
         ELSE CAST(dps.row_count AS varchar(20)) END AS row_count,
    ispec.table_gb, ispec.alloc_gb AS index_gb,
    SUBSTRING('NY', CAST(i.is_primary_key AS int) + CAST(i.is_unique_constraint AS int) + 1, 1) +
    CASE WHEN i.is_unique = CAST(i.is_primary_key AS int) + CAST(i.is_unique_constraint AS int) THEN '' 
         ELSE '.' + SUBSTRING('NY', CAST(i.is_unique AS int) + 1, 1) END AS [Uniq?],
    REPLACE(i.name, oa1.table_name, '~') AS index_name,
    --fc_row_count.formatted_value AS row_count,
    i.index_id,
    ispec.approx_max_data_width AS [data_width], 
    CAST(CAST(ispec.used_mb AS float) * 1024.0 * 1024.0 / NULLIF(dps.row_count, 0) AS int) AS cmptd_row_size,
    key_cols AS key_cols,
    LEN(nonkey_cols) - LEN(REPLACE(nonkey_cols, ',', '')) + 1 AS nonkey_count,
    nonkey_cols AS nonkey_cols,
    ius.user_seeks, ius.user_scans, --ius.user_seeks + ius.user_scans AS total_reads,
    ius.user_lookups, ius.user_updates,
    dios.leaf_delete_count + dios.leaf_insert_count + dios.leaf_update_count as leaf_mod_count,
    dios.range_scan_count, dios.singleton_lookup_count,
    DATEDIFF(DAY, STATS_DATE ( i.object_id , i.index_id ), GETDATE()) AS stats_days_old,
    DATEDIFF(DAY, CASE 
        WHEN o.create_date > cj1.sql_startup_date AND o.create_date > o.modify_date THEN o.create_date 
        WHEN o.modify_date > cj1.sql_startup_date AND o.modify_date > o.create_date THEN o.modify_date 
        ELSE cj1.sql_startup_date END, GETDATE()) AS max_days_active,
    dios.row_lock_count, dios.row_lock_wait_in_ms,
    dios.page_lock_count, dios.page_lock_wait_in_ms,    
    ius.last_user_seek, ius.last_user_scan,
    ius.last_user_lookup, ius.last_user_update,
    fk.Reference_Count AS fk_ref_count,
    i.fill_factor,
    ius2.row_num,
    CASE 
        WHEN ispec.max_compression IS NULL THEN '(Not applicable)'
        WHEN ispec.max_compression = 2 THEN 'Page'
        WHEN ispec.max_compression = 1 THEN 'Row'
        WHEN ispec.max_compression = 0 THEN ''
        ELSE '(Unknown)' END AS max_compression,
    ius.system_seeks, ius.system_scans, ius.system_lookups, ius.system_updates,
    ius.last_system_seek, ius.last_system_scan, ius.last_system_lookup, ius.last_system_update,
    GETDATE() AS capture_date
INTO #index_usage
FROM sys.indexes i /*WITH (NOLOCK)*/
INNER JOIN sys.objects o /*WITH (NOLOCK)*/ ON
    o.object_id = i.object_id
CROSS JOIN (
    SELECT create_date AS sql_startup_date FROM sys.databases /*WITH (NOLOCK)*/ WHERE name = 'tempdb'
) AS cj1
OUTER APPLY (
    SELECT CASE WHEN EXISTS(SELECT 1 FROM #index_specs [is] WHERE [is].object_id = i.object_id AND [is].index_id = 1)
        THEN 1 ELSE 0 END AS has_clustered_index
) AS cj2
LEFT OUTER JOIN dbo.#index_specs ispec ON
    ispec.object_id = i.object_id AND
    ispec.index_id = i.index_id
OUTER APPLY (
    SELECT STUFF((
    SELECT
        ', ' + COL_NAME(ic.object_id, ic.column_id)
    FROM sys.index_columns ic /*WITH (NOLOCK)*/
    WHERE
        ic.key_ordinal > 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        ic.key_ordinal
    FOR XML PATH('')
    ), 1, 2, '')
) AS key_cols (key_cols)
OUTER APPLY (
    SELECT STUFF((
    SELECT
        ', ' + COL_NAME(ic.object_id, ic.column_id)
    FROM sys.index_columns ic /*WITH (NOLOCK)*/
    WHERE
        ic.key_ordinal = 0 AND
        ic.object_id = i.object_id AND
        ic.index_id = i.index_id
    ORDER BY
        COL_NAME(ic.object_id, ic.column_id)
    FOR XML PATH('') 
    ), 1, 2, '')
) AS nonkey_cols (nonkey_cols)
LEFT OUTER JOIN sys.dm_db_partition_stats dps /*WITH (NOLOCK)*/ ON
    dps.object_id = i.object_id AND
    dps.index_id = i.index_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius /*WITH (NOLOCK)*/ ON
    ius.database_id = DB_ID() AND
    ius.object_id = i.object_id AND
    ius.index_id = i.index_id
LEFT OUTER JOIN (
    SELECT
        database_id, object_id, MAX(user_scans) AS user_scans,
        ROW_NUMBER() OVER (ORDER BY MAX(user_scans) DESC) AS row_num --user_scans|user_seeks+user_scans
    FROM sys.dm_db_index_usage_stats /*WITH (NOLOCK)*/
    WHERE
        database_id = DB_ID()
        --AND index_id > 0
    GROUP BY
        database_id, object_id
) AS ius2 ON
    ius2.database_id = DB_ID() AND
    ius2.object_id = i.object_id
LEFT OUTER JOIN (
    SELECT
        referenced_object_id, COUNT(*) AS Reference_Count
    FROM sys.foreign_keys /*WITH (NOLOCK)*/
    WHERE
        is_disabled = 0
    GROUP BY
        referenced_object_id
) AS fk ON
    fk.referenced_object_id = i.object_id
LEFT OUTER JOIN (
    SELECT *
    FROM sys.dm_db_index_operational_stats ( DB_ID(), NULL, NULL, NULL )
) AS dios ON
    dios.object_id = i.object_id AND
    dios.index_id = i.index_id
OUTER APPLY (
    SELECT OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name
        --, CASE WHEN dps.row_count >= 1000000 THEN 'M' WHEN dps.row_count >= 1000 THEN 'K' ELSE '' END AS row_count_suffix
) AS oa1
CROSS APPLY (
    SELECT CASE WHEN dps.row_count >= 1000000 THEN 'M' WHEN dps.row_count >= 1000 THEN 'K' ELSE '' END AS row_count_suffix
) AS ca1

WHERE
    i.object_id > 100 AND
    i.is_hypothetical = 0 AND
    i.type IN (0, 1, 2) AND
    o.type NOT IN ( 'IF', 'IT', 'TF', 'TT' ) AND
    (
     o.name LIKE @table_name_pattern AND
     o.name NOT LIKE 'dtprop%' AND
     o.name NOT LIKE 'filestream[_]' AND
     o.name NOT LIKE 'MSpeer%' AND
     o.name NOT LIKE 'MSpub%' AND
     --o.name NOT LIKE 'queue[_]%' AND 
     o.name NOT LIKE 'sys%' 
    )
ORDER BY
    --cj2.has_clustered_index, ispec.size_rank, --heaps first, by size
    db_name,
    --i.index_id,
    --ius.user_seeks - ius.user_scans,
    CASE WHEN @order_by IN (-2, 2) THEN ispec.size_rank * -SIGN(@order_by) ELSE 0 END,
    --ius.user_scans DESC,
    --ius2.row_num, --user_scans&|user_seeks
    table_name, 
    -- list clustered index first, if any, then other index(es)
    CASE WHEN i.index_id IN (0, 1) THEN 1 ELSE 2 END, 
    key_cols


SELECT *
FROM #index_usage
ORDER BY ident

PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)

SET DEADLOCK_PRIORITY NORMAL

Open in new window

1
 
LVL 23

Author Comment

by:Snarf0001
ID: 42330566
Wow, thanks a tonne Scott.  The hash returns instantly.
So basically (if I got that), because OrderDetails does have a clustered index on the ID column, SQL is isn't able to properly pick out that there's only a tiny subset of rows that would actually be coming back, and is making it's plan badly?

Results from the query are attached as an Excel.  Looking forward to digging more into that script to see what you're doing.

Much appreciated.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 42330666
you forgot the attachment
0
 
LVL 23

Author Comment

by:Snarf0001
Thanks Paul, thought I had, maybe for some reason it got blocked.  In any case, worked this time.
IndexResults.xlsx
0
 
LVL 23

Author Closing Comment

by:Snarf0001
Thanks again Scott.  Would be curious about the results of the stats I posted, but you gave me a killer starting point to hunt down what's going wrong.
Incredible query to pull the indexing issues, still going through that, but found quite a few issues already.

Much appreciated.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
I will post it as soon as I can.  I'm really busy at work and moving houses IRL.
0
 
LVL 23

Author Comment

by:Snarf0001
No worries, and no problem even if you don't.
As I said, what you provided gave me more than enough to start looking (and finding) issues.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Join & Write a Comment

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question