Seeing no change after applied DTA recommendation

Question

Seeing no change after applied DTA recommendation

Maple07-598122

SSC Enthusiast

Points: 156
More actions
August 23, 2011 at 10:27 am

#390252

I used DTA to tune a slow query in SQL 2005. DTA recommend to create an index, and it would improve performance 60%. I applied the recommendation, the index was created, but the performance stays as same, no improvement at all. If i run the DTA again, it says no recommendation. Any ideas?
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Drop the indexes and stats that you created. DTA is not always right, far from it. Recommendations should be tested and only those that actually make a difference implemented.

If you want additional help with that particular query, post it along with table structure, index structure and exec plans and we'll take a look.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Maple07-598122 SSC Enthusiast Points: 156 More actions · Answer 2

Maple07-598122

SSC Enthusiast

Points: 156

August 23, 2011 at 11:13 am

#1372164

It is a simple query SELECT count(ics_id) FROM

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Any reason you can't use COUNT(*)?

This will always use the smallest index to return the result (least reads, hence the fastest).

Can't say it's the best option here since I don't see the where clause.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

GilaMonster (8/23/2011)
If you want additional help with that particular query, post it along with table structure, index structure and exec plans and we'll take a look.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Maple07-598122 SSC Enthusiast Points: 156 More actions · Answer 5

Here is the detail:

Query:SELECT count(ICS_ID) FROM dbo.Tl_ProgramDetail WHERE FunddateID not in (SELECT funddateID from dbo.t_FundingDates)

Table: Tl_ProgramDetail ics_id, pk, int not null

CenterID, fk, int not null

ProgramID, fk,int not null

PDate, datetime,null

Hours, float, null

FundDateID, int, null

Indexs

PK_Ts_ProgramDetail(Unique, Non-Clustered) on ics_id asc

Tl_ProgramDetail2 clustered, on PDate asc

Tl_ProgramDetail32 Nonclustered on centerID asc

Table: T_FundingDatesFundDateID, PK, int not null

CenterID, FK, int not null

FundingDate, datatime not null

Index

IX_T_FundingDates(Unique, non-clustered) on centerID, fundingdate

PK_Tl_FundingDates(unique, non-clustered) on FundateDateID

[highlight=#ffff11][/highlight]

Actual execuation plan is in attached file: cluster index scan on tl_programdetail cost 80%.

Thanks for all your help.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

I've rarely if ever saw the need to get an actual count like that.

What do you need it for later in the code?

Any reason why you can't do if exists (...)?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Try changing the query to Count(*) and adding an index on Tl_ProgramDetail (FunddateID)

That's about the only thing that's going to help there. It'll still be a scan, but a scan of a much smaller index.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Maple07-598122 SSC Enthusiast Points: 156 More actions · Answer 8

Using count(*) makes no difference.

I will try the index.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Maple07-598122 (8/23/2011)
Using count(*) makes no difference.

No, it won't. It just makes it clearer what you're doing (counting all rows, not counting non-null values) and prevents things breaking if a future change makes that column nullable.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Maple07-598122 SSC Enthusiast Points: 156 More actions · Answer 10

I should not use NOT IN in the query. Either IN or NOT IN hurts the performance really bad.

Thanks for all your reply.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

Maple07-598122 (8/23/2011)
I should not use NOT IN in the query. Either IN or NOT IN hurts the performance really bad.

Since the columns are nullable, rather use NOT EXISTS than NOT IN. http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

As for IN, it's about the most efficient way to get rows where a particular column has values in another table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass