performance tuning makes query slower?

Question

Post reply

performance tuning makes query slower?

Christopher Klein

SSCrazy

Points: 2648
More actions
April 10, 2007 at 1:11 pm

#117995

Greetings,
I have what you might call a 'limited' grasp on how the performance tuning wizard does its job.
I have a large query that takes roughly 4300ms to return 22 rows of data. built from 7 tables with varying amounts of data.
So I decide to run the wizard it gives me a number of statistics to create and a non-clustered index on some of the main columns.
So WITHOUT the index and stats profiler shows that the query took 4300ms(average) and 114872 reads
When I put the index and stats in, granted the profiler said that it should be a 58% increase in performance the query now takes 4900ms(average) and 37844 reads.
So, there are alot of other things going on the particular server I am working and without knowing exactly what ELSE might be running at the time, did I really gain anything?
Any thoughts?
thanks,
Chris

Viewing 5 posts - 1 through 4 (of 4 total)

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

Dan Puiu Old Hand Points: 320 More actions · Answer 1

Can you post that query?

I can also check the execution plan and see how the indexes are used.

Dan

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 2

The amount of reads are a good indication of I/O. Since it says 30000 instead of 110000 it should be faster. A look at the actual execution plan wouldn't hurt.

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 3

Dont use the 2000 wizard. What does the query plan show. Are there a lot of TABLE SCANS, or INDEX SCANS, HASH JOINS (memory intensive), BOOKMARKS?

Double check to make sure you're joining the smaller tables first.

Are the stats up to date?

Christopher Klein SSCrazy Points: 2648 More actions · Answer 4

Actually, the stats are not recently updated and the indexes are fragmented ALOT.

This is day 6 at a new job so I'm finding all kinds of things to complain to my new supervisor about

I think I'm going to see about clearing that up first, because you're right I would expect that if the reads were reduced that the speed should be improved. Gonna fix that and see if I get the same results (would be surprised if I did) and then come back.

Thanks!

Chris