Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Sarat
Constraint Violating Yak Guru
265 Posts |
 Posted - 2003-12-22 : 11:51:42
|
| Hi, I have a table with about a million rows which I reindex once a week and defrag once in 2-3 weeks. Recently I noticed that in one of the processes, a paricular sql statement was taking longer to run and it contained this table. When I checked its logical scan fragmentation, it was about 12.5% and extent scan fragmentation was about 6.8%. So I ran a defrag and it brought it down logical scan to 0% and extent scan to 4%. This happened last Friday night. Then a reindex job ran on sunday which brought logical scan back up to 12.5 and extent scan to 8%. I tested this again today. The index job is causing some fragmentation. Is it normal? Should I choose index vs. fragmentation for most used tables on a case by case basis?Thanks,Sarat.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-12-26 : 13:20:41
|
| Any thoughts? Anyone?-Sarat**To be intoxicated is to feel sophisticated, but not be able to say it.** |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-12-27 : 21:11:35
|
| What command are you running for the defrag? DBCC DBREINDEX is the command to use. DBCC INDEXDEFRAG does something similar but the reindex is the more powerful of the two commands. Some systems can not afford to have the DBREINDEX run due to the lock held on the tables. I am not sure if what you are seeing is normal or not.Tara |
|
|
|
Sarat
Constraint Violating Yak Guru
265 Posts |
Posted - 2003-12-29 : 14:11:35
|
| I use DBREINDEX for rebuilding indexes and INDEXDEFRAG to control fragmentation. I realize DBREINDEX is better. I just saw this peculiar thing and wanted to make sure if I have a sound solution implemented since I am having so many performance issues.Thanks.**To be intoxicated is to feel sophisticated, but not be able to say it.** |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-05 : 12:24:10
|
| I can't remember if you posted information about this, but...What does your hardware look like? Have you done performance monitoring to determine if a hardware bottleneck exists? There is only so much that you can do with the database especially if it is a third party one. Got a beefy server?Tara |
|
|
|
|
|
|
Indexing affects Logical Scan Fragemtation