Sql-server – Troubleshooting extremely long running query (SQL Server 2012)

performancequery-performancesql server

We have a purchased reporting application that executes a query that just recently is taking an extremely long time (>2 hours). A week ago, this same query completed in less than a minute. I've restored the DB from last week to confirm the end user's story, and sure enough, even with similar row counts with last weeks and yesterday's backup, the difference in query run time is there.

Table1 contains 1.4 million rows. Table2 contains 16k rows. In this case, none of the records from Table2 exist in Table1, so when the query is actually allowed to complete, no records are returned.

The table schemas are identical, with the exception that Table1 has a non-unique, non-clustered index on Field10. There are no other indexes that exist on either table. All fields in the join are (NVARCHAR(40), null) types.

The query looks like this:

DELETE Table1  
FROM Table1 t1  
INNER JOIN Table2Temp t2
    ON      t1.Field1 = t2.Field1 AND t1.Field2 = t2.Field2 
        AND t1.Field3 = t2.Field3 AND t1.Field4 = t2.Field4
        AND t1.Field5 = t2.Field5 AND t1.Field6 = t2.Field6 
        AND t1.Field7 = t2.Field7 AND t1.Field8 = t2.Field8
        AND t1.Field9 = t2.Field9 AND t1.Field10 = t2.Field10 
        AND t1.Field11 = t2.Field11

If I simply do a "SELECT * INTO Table3 FROM Table1" and "SELECT * INTO Table4 FROM Table2" and repeat the query about, substituting Table3 for Table1, and Table4 for Table2, the same query (against the new tables) takes 4 seconds.

I'm not an expert at Execution plans, but it looks okay to me. No suggested indexes, index seek on Table1 (cost of 89%), and a table scan on Table2 (but cost is only 9%). Here's a link to the actual execution plan that has been anonymised. http://pastebin.com/jcyATezX

There is no blocking that is taking place. When using sp_WhoIsActive, I can see that the Reads are sky-rocketing into the billions.

Again, this is a boxed application, so I can't really change the query. Any ideas on what I'm missing here? It's driving me nuts that I can't figure this out.

Best Answer

After some excellent help from Kin, wBob, and Aaron Bertrand, I finally found my root cause: Table1 statistics needed updating.

I had already confirmed that statistics were updated 2 days prior during our weekly index maintenance, so didn't really think anything of it. However, I gave it a try, and that made the query run time go from 2hrs 30min down to 2 seconds!

Command run was:

UPDATE STATISTICS Table1;

Thanks for all the help, particularly in the article "The "Runaway" Query"

Related Solutions

Sql-server – Logical reads amount difference with almost identical indexes

It all depends on the definitions and the key (and non-key) columns defined in the nonclustered index. The clustered index is the actual table data. Therefore it contains all of the data in the data pages, whereas the nonclustered index is only containing columns' data as defined in the index creation DDL.

Let's set up a test scenario:

use testdb;
go

if exists (select 1 from sys.tables where name = 'TestTable')
begin
    drop table TestTable;
end
create table dbo.TestTable
(
    id int identity(1, 1) not null
        constraint PK_TestTable_Id primary key clustered,
    some_int int not null,
    some_string char(128) not null,
    some_bigint bigint not null
);
go

create unique index IX_TestTable_SomeInt
on dbo.TestTable(some_int);
go

declare @i int;
set @i = 0;

while @i < 1000
begin
    insert into dbo.TestTable(some_int, some_string, some_bigint)
    values(@i, 'hello', @i * 1000);

    set @i = @i + 1;
end

So we've got a table loaded with 1000 rows, and a clustered index (PK_TestTable_Id) and a nonclustered index (IX_TestTable_SomeInt). As you've seen in your testing, but just for thoroughness:

set statistics io on;
set statistics time on;

select some_int
from dbo.TestTable -- with(index(PK_TestTable_Id));

set statistics io off;
set statistics time off;
-- nonclustered index scan (IX_TestTable_SomeInt)
-- logical reads: 4

Here we have a nonclustered index scan on the IX_TestTable_SomeInt index. We have 4 logical reads for this operation. Now let's force the clustered index to be used.

set statistics io on;
set statistics time on;

select some_int
from dbo.TestTable with(index(PK_TestTable_Id));

set statistics io off;
set statistics time off;
-- clustered index scan (PK_TestTable_Id)
-- logical reads: 22

Here with the clustered index scan we have 22 logical reads. Why? Here's why. It all matters on how many pages that SQL Server has to read in order to grab the entire result set. Get the average row count per page:

select
    object_name(i.object_id) as object_name,
    i.name as index_name,
    i.type_desc,
    ips.page_count,
    ips.record_count,
    ips.record_count / ips.page_count as avg_rows_per_page
from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.TestTable'), null, null, 'detailed') ips
inner join sys.indexes i
on ips.object_id = i.object_id
and ips.index_id = i.index_id
where ips.index_level = 0;

Take a look at my result set of the above query:

enter image description here

As we can see here, there are an average of 50 rows per page on the leaf pages for the clustered index, and an average of 500 rows per page on the leaf pages for the nonclustered index. Therefore, in order to satisfy the query more pages need to be read from the clustered index.

Mysql – poor and/or unreliable InnoDB performance

innoDB

is bad for dml statement, #MyISAM is better. Innodb is try to commit all INSERTing data to disk and returns success after it pined.

when you need a big insert data you can avoid auto-commit=0 to get faster. or you can check this for hint.

Best Answer

Related Solutions

Sql-server – Logical reads amount difference with almost identical indexes

Mysql – poor and/or unreliable InnoDB performance

innoDB

Related Question