SQL Server DTA tool recomendations

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).
Sri MSSQLDBAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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?
0
Sri MSSQLDBAAuthor 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
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

ste5anSenior DeveloperCommented:
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
)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sri MSSQLDBAAuthor 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
0
ste5anSenior DeveloperCommented:
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.
0
Sri MSSQLDBAAuthor 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
0
ste5anSenior DeveloperCommented:
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?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Sri MSSQLDBAAuthor 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
0
ste5anSenior DeveloperCommented:
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

1
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Sri MSSQLDBAAuthor 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
0
ste5anSenior DeveloperCommented:
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.
0
Sri MSSQLDBAAuthor Commented:
I have done changes as per your suggestion, still no luck :) Can you please review the attached table and indexes?
Table.sql
0
ste5anSenior DeveloperCommented:
You need to check the other tables also. They should not use the referenced NEWID value as clustered index.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Any news of this?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
A lot of good inputs provided in the above comments.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.