0

We are noticing below SQL is causing very High CPU and its been executed 10,000 times. Is there a way we can tune this T-sql?

SELECT ISNULL(max(trans_seq),0) +1  
FROM inv_inventory_journal  
WHERE organization_id = @P0  
AND wkstn_id = '600' 

From Execution Plan, it is suggesting to create the following index.

/*
Missing Index Details from ExecutionPlan1.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 98.8039%.
*/

/*
USE [LKY_Xcenter]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[inv_inventory_journal] ([organization_id],[wkstn_id])
INCLUDE ([trans_seq])
GO
*/

enter image description here

Table Structure

enter image description here

  • 3
    Have you considered using an IDENTITY column? You'll find it has a lot less contention than this max()+1 solution. Also, this query is in a serializable transaction, right? On its own, you can get multiple sessions retrieving the same value under the default isolation level. – Aaron Bertrand Jan 18 '16 at 1:32
  • The suggestion seems correct. The parallelism might have occurred because of missing index forcing SQL Server to prepare this plan. Please create NCI as suggested and again run the query and look at actual execution plan. Also please add table structure and information about any index DDL – Shanky Jan 18 '16 at 4:18
  • @Shanky so, you are suggesting to create the index? – VeerM Jan 18 '16 at 16:47
  • What @AaronBertrand said. Also, please post your table definitions as create statements instead of screenshots – Tom V - Team Monica Jan 18 '16 at 22:57
1

Based on your schema it looks like trans_seq can't be null so I would remove the isnull from the select as that would have a small amount of CPU overhead in just checking the value that is returned from the max function.

SELECT max(trans_seq) +1  
FROM inv_inventory_journal  
WHERE organization_id = @P0  
AND wkstn_id = '600' 

Also if your query is always setting the wkstn_id to a constant 600. You could create a filtered index on that column.

USE [LKY_Xcenter]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[inv_inventory_journal] ([organization_id],[wkstn_id])
INCLUDE ([trans_seq])
WHERE [trans_seq] = 600;
GO

The nice thing about filtered indexes is that from an IO standpoint they only contain the rows that satisfies the where statement defined in the index so you could get better performance from them.

One thing of note with the filtered indexes is that in order for the query optimizer to use them the value in the where clause must be a constant in the query.

Here is a couple of references to using filtered indexes: MSDN

Brent Ozar

0

From what i experienced using indexes in big table.
It seems the sql engine will optimally used index in a set of columns.

For example, your primary key consist of set of columns. But the index of that set will not be used in sql optimation unless the whole set of column is used in the query.

That is why your original query did not use the index.

So, yes. To optimize the use big data table , index must be created for each specific set of columns used in query.

Sql server execution plan analysis help a lot to analize wich columns should be indexed.

See SQL server design guide

This basic index design guide would also applied to other rdbms engine

Other way to optimize query that use computing or formula is by alter the table to include computed columns. Compute column can be added to index.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.