SQL Server DTA tool recomendations

Sri MSSQLDBA
Sri MSSQLDBA used Ask the Experts™
on
Hi

We are facing performance issues (slow running queries), so we have executed DTA tool and tool is recommending to drop indexes with 1% estimated improvement (tool is not recommended to create new indexes and stats). By dropping recommended indexes will help us to improve the performance?

SQL Version: SQL 2014

DB size: 700 GB (Its MS CRM DB).
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
Due to the database size: How many users and what are your hardware specs?

Did you start with health checks? E.g. Brent Ozar's sp_blitz?
Did you look at the standard metrics? Memory usage, outstanding disk IO, CPU usage?
Did you look at the TOP IO/Performance queries in SSMS standard reports? Are they optimal?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Your database may have repeated indexes or indexes that are already covered by other existing indexes.
Can you provide the Query Execution Plan for one of your long running queries?

Author

Commented:
please find the attached execution plan with query.

Hardware spec: 256 GB RAM (2.80Ghz 6 processors).  CPU usage and IO usage is normal during the query execution.
SQL Server allocated memory (Min memory: 0 MB and Max memory: 185000 MB). MAXDOP value is 1 and we are executing index rebuild job every day to Defragment indexes.
We have hosted 2 DB's on this DB box (Microsoft Dynamics CRM DB)  and total two DB size is around 1.20 TB).
ExecutionPlan.sqlplan
Query.sql
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Senior Developer
Commented:
Is MAXDOP 1 an vendor recommendation? Otherwise it is unusual at the first glance.

Do you  use Ola Hallengren's scripts? They are the de facto standard for index maintenance.
Do you run regular healt checks? You can get an overview and some detail by using Brent Ozar's sp_Blitz.
For analyzing slow preforming queries, did you look at the Top Queries reports in SSMS? Or you may use sp_BlitzCache to identify them.

As far as I can see, there is no supporting index used for the main table. Does an such an index exists?

businessinteraction ( isdeleted, businessinteractiondiscriminator , createdate )

or

businessinteraction ( isdeleted, businessinteractiondiscriminator, createdate )
include (
    id,
    businessinteractiontype,
    code,
    description,
    status,
    substatus,
    submitdate,
    sessionid,
    lockingbusinessinteraction,
    issubbi,
    createuserid,
    createdate,
    updateuserid,
    updatedate,
    extensiondata,
    businessinteractionspecid
)

Author

Commented:
MAXDOP 1 an vendor recommendation and I am using Ola Hallengren's scripts for DB maintenance.

Currently I am in the process of analyzing top expensive queries using suggested Brent Ozar's sp_Blitz.

Btw, I am herewith attaching table structure and current indexes of businessinteraction table. Do you have any suggestions on current indexes? do you want me to create new index with include columns (above your suggestion)? Row count of businessinteraction table: 111950721
Tbl_indexes.sql
ste5anSenior Developer
Commented:
As the order of the columns in an index is important for the selectivity, I would at least try creating businessinteraction ( isdeleted, businessinteractiondiscriminator , createdate). While it will take some space, but it should be tested. Cause currently you have only table scans. And the most expensive operation is on businessinteraction.

The only other performance test: Use a temporary table/table variable to materialize

SELECT   columnList
FROM     businessinteraction businessin0_
         INNER JOIN [BusinessInteractionRequest] businessin0_1_ ON businessin0_.id = businessin0_1_.id
WHERE    businessin0_.businessinteractiondiscriminator IN ( 61, 55, 18, 3, 33, 63, 5, 11, 25, 75, 13, 30, 31, 1, 17, 28, 26, 2, 12, 27, 64, 62, 15, 68, 4, 10, 19 ,
                                                            16 , 6, 8, 7, 9, 1001 )
         AND businessin0_.isdeleted = @p0
         AND businessin0_.createdate >= @p1
         AND businessin0_.createdate <= @p2
ORDER BY businessin0_.createdate DESC OFFSET @p3 ROWS FETCH FIRST @p4 ROWS ONLY;

Open in new window


but this depends on the number of returned rows average.

btw, the payment table needs seems to have missing statistics.

Author

Commented:
I have done performance test step by step. The only below 4 columns in the attached query is taking more time (If I comment below columns in the attached query, I am getting output in 0 sec for 11 rows, If I have not comment below 4 columns in the query, the attached query is taking 25 min for 11 rows)

4 Columns:
businessin1_3_.isrequired                         AS isrequired186_1_, 
businessin1_4_.isjournaled                        AS isjourna2_187_1_, 
businessin1_5_.policy                             AS policy188_1_, 
businessin1_7_.policy                             AS policy190_1_

Open in new window


Tried to create Indexes for above 4 columns but still no luck
create nonclustered index ndx_BusinessInteractionCustomerSpec on 
BusinessInteractionCustomerSpec  (isrequired) /*isrequired datatype BIT - NUMBER OF ROWS IN THIS TABLE:5 */
go
create nonclustered index ndx_BusinessInteractionJournaledSpec on 
[BusinessInteractionJournaledSpec]  (isjournaled) /*isjournaled datatype BIT - NUMBER OF ROWS IN THIS TABLE:5*/
go
create nonclustered index ndx_BusinessInteractionPriceSpec on 
[BusinessInteractionPriceSpec]  (policy) /*policy datatype INT - NUMBER OF ROWS IN THIS TABLE:5*/
go
create nonclustered index ndx_BusinessInteractionReceiptSpec on 
[BusinessInteractionReceiptSpec]  (policy) /*policy datatype INT - NUMBER OF ROWS IN THIS TABLE:5*/

Open in new window


Can you please advise?
Query_modi.sql
ste5anSenior Developer

Commented:
In this case you should for locking or other wait issues during execution. Either execute sp_who2 or use the activity monitor in SSMS.

I would not have expected that those further indices can help. How many rows contain each of those four tables?
btw, what data type has your ID column in those tables and the base tables (businessinteraction, BusinessInteractionRequest)? I hope it's not UNIQUEIDENTIFIER.

p.s. why one earth are you storing passwords?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
By the Query Execution Plan that you've posted, it doesn't seems to me that you're having issues only because of the indexes.
IMHO, it's have to do with your database design also.
But lets focus on the indexes for the BusinessInteraction table. You really have too many indexes and you can easily drop some.
For example, on the below example, the 2nd index it doesn't need because id is the clustered index for the table, so already ordered physically by the id column:
/****** Object:  Index [IX_BusinessInteraction_CreateDate]    Script Date: 4/10/2018 11:36:34 AM ******/
CREATE NONCLUSTERED INDEX [IX_BusinessInteraction_CreateDate] ON [dbo].[BusinessInteraction]
(
	[CreateDate] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
/****** Object:  Index [IX_BusinessInteraction_CreateDate_Id]    Script Date: 4/10/2018 11:36:47 AM ******/
CREATE NONCLUSTERED INDEX [IX_BusinessInteraction_CreateDate_Id] ON [dbo].[BusinessInteraction]
(
	[CreateDate] DESC,
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Open in new window


On the next example, you don't need to include Id as the PK is always included automatically in any index. Besides that, the 2nd index has all columns for the 1st one, so the 1st one can be dropped. The 3rd index can also be dropped as the SQL Server engine is smart enough to use the 2nd index to obtain the same:
/****** Object:  Index [IX_BusinessInteraction_BusinessInteractionDiscriminator_IsDeleted]    Script Date: 4/10/2018 11:35:52 AM ******/
CREATE NONCLUSTERED INDEX [IX_BusinessInteraction_BusinessInteractionDiscriminator_IsDeleted] ON [dbo].[BusinessInteraction]
(
	[BusinessInteractionDiscriminator] ASC,
	[IsDeleted] ASC
)
INCLUDE ( 	[Id],
	[CreateDate]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

/****** Object:  Index [IX_BusinessInteraction_CreateDate_IsDeleted_Discriminator]    Script Date: 4/10/2018 11:37:03 AM ******/
CREATE NONCLUSTERED INDEX [IX_BusinessInteraction_CreateDate_IsDeleted_Discriminator] ON [dbo].[BusinessInteraction]
(
	[CreateDate] DESC,
	[IsDeleted] ASC,
	[BusinessInteractionDiscriminator] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

/****** Object:  Index [IX_BusinessInteraction_IsDeleted_BusinessInteractionDiscriminator]    Script Date: 4/10/2018 11:37:29 AM ******/
CREATE NONCLUSTERED INDEX [IX_BusinessInteraction_IsDeleted_BusinessInteractionDiscriminator] ON [dbo].[BusinessInteraction]
(
	[IsDeleted] ASC
)
INCLUDE ( 	[BusinessInteractionDiscriminator]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Open in new window


Now, about your Clustered Index for this table. Do you really need it to be the id? Btw, are you filtering or order by id in any other query? For the one you've posted, you don't. And as far as you're ordering by date DESC, this column might be the best candidate to be clustered index for the table.

And for this example, if the rest of your long running queries have the same issue, is more than justified why they are taking so long. You might need to review them all, one by one, so they can be tuned.

Author

Commented:
I have not found any locking or deadlock problems during the query execution. The data type of ID column is UNIQUEIDENTIFIER (in both tables - businessinteraction, BusinessInteractionRequest). All those 4 table having 5 rows in each table.

I have removed suggested indexes from table, still there is no change in query execution time. Currently this query only taking more than 25 min for 11 rows. the other queries are taking 1 min 33 sec for 100 rows. I am herewith attaching table structure of BusinessInteraction table and we need ID column with clustered.
BusinessInteractionTable.sql
ste5anSenior Developer
Commented:
Using NEWID as clustered key will give you a random physical sort order. This means performance issues while reading sets, cause you need to read a lot of data pages. According to the given scripts I strongly recommend- as also Vitor does - to change the clustered index (a primary key must not be clustered!). You have also performance issues when writing data to disk, cause you also need to touch many data pages. And lots of data pages means memory pressure and I guess you have heavy IO for 111 million rows table.

I would consider using SubmitDate for BusinessInteraction, when it is NOT NULL, otherwise I would use also the CreateDate.

p.s.
- NTEXT is deprecated, use NVARCHAR(MAX) instead.
- BIT's are in most cases (99%) NOT NULL and have a DEFAULT value (IsDeleted, IsSubBI).
- Constraints should always be explicitly named.
- Why can Status be NULL, when SubStatus cannot?
- Also Status should have an explicit unkown value to avoid SQL tristate logic.
- Why is the SubmitDate NULL?
- Why has IsDeleted multiple states, shouldn't that be a BIT instead?
- UpdateUserID and UpdateDate are the same as their creation counterparts after an INSERT. Thus also NOT NULL.
- Sure that [Description] can be NULL?
- Is your Code column really variable in length?

CREATE TABLE dbo.BusinessInteraction
    (
        ID UNIQUEIDENTIFIER NOT NULL
            DEFAULT ( NEWID()) ,
        BusinessInteractionDiscriminator INT NOT NULL ,
        BusinessInteractionSpecId UNIQUEIDENTIFIER NOT NULL ,
        BusinessInteractionType INT NOT NULL ,
        Code NVARCHAR(20) NOT NULL ,
        CreateDate DATETIME NOT NULL ,
        CreateUserId UNIQUEIDENTIFIER NULL ,
        ExtensionData NVARCHAR(MAX) NULL ,
        IsDeleted BIT NOT NULL ,
        IsSubBI BIT NULL ,
        LockingBusinessInteraction UNIQUEIDENTIFIER NULL ,
        SessionId UNIQUEIDENTIFIER NULL ,
        SubStatus INT NOT NULL ,
        SubmitDate DATETIME NULL ,
        UpdateDate DATETIME NOT NULL ,
        UpdateUserId UNIQUEIDENTIFIER NOT NULL ,
        [Description] NVARCHAR(1000) NULL ,
        [Status] INT NULL ,
        CONSTRAINT PK_BusinessInteraction
            PRIMARY KEY NONCLUSTERED ( ID ASC ) ,
        CONSTRAINT FK_BusinessInteraction_BusinessInteractionSpec
            FOREIGN KEY ( BusinessInteractionSpecId )
            REFERENCES dbo.BusinessInteractionSpec ( id )
    );
GO

ALTER TABLE dbo.BusinessInteraction
ADD CONSTRAINT DF_BusinessInteraction_IsDeleted
    DEFAULT ( 0 ) FOR IsDeleted;
GO

CREATE CLUSTERED INDEX CIX_BusinessInteraction
    ON dbo.BusinessInteraction ( ID ASC );
GO

Open in new window

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I have removed suggested indexes from table, still there is no change in query execution time.
Hardly removing indexes will improve a query performance. It's only a cleansing procedure as well a way to reduce table size.

Currently this query only taking more than 25 min for 11 rows
But you're retrieving the 11 row from hundred of millions of records. It's a slow process, specially when your database design isn't the best.

Author

Commented:
i have redesigned BusinessInteraction table as per your suggestion. still query is taking same execution time. please find the attached new table structure with indexes and execution plan of the query. Do I need to redesign any other tables that are involved in attached query to reduce the query execution time?
Table.sql
execution-plan.sqlplan
Query_modi.sql
ste5anSenior Developer
Commented:
You are still using ID as clustered index:

CREATE CLUSTERED INDEX [CIX_BusinessInteraction] ON [dbo].[BusinessInteraction]
(
        [ID] ASC
);

Open in new window


You should test

CREATE CLUSTERED INDEX [CIX_BusinessInteraction] ON [dbo].[BusinessInteraction]
(
        [CreateDate] ASC
);

Open in new window

instead.

Caveat: this will change the physical sort order on disk. Thus it will generate some heavy IO doing it for 111 million row table, when you use a DROP/CREATE constraint approach.

Author

Commented:
I have done changes as per your suggestion, still no luck :) Can you please review the attached table and indexes?
Table.sql
ste5anSenior Developer

Commented:
You need to check the other tables also. They should not use the referenced NEWID value as clustered index.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
i have redesigned BusinessInteraction table as per your suggestion. still query is taking same execution time.
What you did redesign? The query still the same and still spending the same costs in the same places (70% in the WHERE clause).

What for do you need the following index?
CREATE NONCLUSTERED INDEX [ndx_businessinteraction] ON [dbo].[BusinessInteraction]
(
	[IsDeleted] ASC,
	[BusinessInteractionDiscriminator] ASC,
	[CreateDate] ASC
)
INCLUDE ( 	[ID],
	[BusinessInteractionType],
	[Code],
	[Description],
	[Status],
	[SubStatus],
	[SubmitDate],
	[SessionId],
	[LockingBusinessInteraction],
	[IsSubBI],
	[CreateUserId],
	[UpdateUserId],
	[UpdateDate],
	[ExtensionData],
	[BusinessInteractionSpecId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Open in new window

It has too many rows included and the order doesn't respect the WHERE clause order. I would create the following simple index instead:
CREATE NONCLUSTERED INDEX [ndx_businessinteraction] ON [dbo].[BusinessInteraction]
(
	[BusinessInteractionDiscriminator] ASC,
	[IsDeleted] ASC,
	[CreateDate] ASC
)

Open in new window


By the way, your query is ordering the results by CreateDate on a Descent order so your clustered index should be also on the same descending order (you've created it with ascending order):
CREATE CLUSTERED INDEX [CIX_BusinessInteraction] ON [dbo].[BusinessInteraction]
(
	[CreateDate] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Open in new window

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Any news of this?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
A lot of good inputs provided in the above comments.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial