Index Fragmentation

Question

Index Fragmentation

ramana3327

SSCoach

Points: 19722
More actions
March 26, 2014 at 5:37 pm

#289742

Hi,
I am working in the test environment. They are doing the performance testing (Perf environment). In perf environment do we need to consider about the index fragmentation or no need? Is it better to tell the team to check about the indexes or anyway it is perf environment so we don't need to worry about the fragmentation issues. Please let me know.
Thanks,
Ramana

Viewing 8 posts - 1 through 7 (of 7 total)

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

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 1

It depends on what is being tested. If they are performance testing queries against the database, then I would recommend you rebuild/reorganize your indexes. If they are testing performance of the disk subsystems using a tool like IOMeter or SQLIO then you wouldn't need to bother.

What is the nature of the test(s)?

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

ramana3327 SSCoach Points: 19722 More actions · Answer 2

Hi Jessie,

Thanks for you reply. They are doing some load test. Not exactly database they are doing load test of a tool but that tool brings the data from SQL database.

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 3

If they haven't told you the nature of the test, I'd leave it

(although if you're in a DBA-type role, you ought to have inquired as to the nature of the test(s))

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

tony28 SSCertifiable Points: 7112 More actions · Answer 4

ramana3327 (3/26/2014)
Hi Jessie,
Thanks for you reply. They are doing some load test. Not exactly database they are doing load test of a tool but that tool brings the data from SQL database.

I think that if they will select from database, it is needed update statistics and rebuild index. But depends on which tables, because if they will use sys view I think that is not needed.

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 5

Possibly true, but the OP doesn't really state (or know) anything about the tests at this point. For all we know there's nothing but system databases in existence on the server (unlikely)

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

free_mascot One Orange Chip Points: 27169 More actions · Answer 6

I guess testing of tool performance should not be based on the fregmentation. The reason is that index fragmentation is always vary through out the day due to many DML. But surely after the reindex it will help to guage howmuch reindex can help.

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 7

Assuming you're trying to replicate the production environment, I'd have whatever type of fragmentation management you have in production in place on the test machine. Same with statistics maintenance and even backups. But, as has already been said, it depends on what you're trying to do with the tests.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning