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 |
|
miranwar
Posting Yak Master
125 Posts |
 Posted - 2006-11-20 : 09:12:33
|
| Hi,Last week I ran Re-Index Job on the database. Although general peformance has improved. Uploads to the database are taking slower and are knocking out the web servers. I ran the reindex for all tables with a 90% fill factor. I'm not sure how a reindex would have an adverse effect on uploads. Does anybody know the reason why as the uploads were working fine before the reindex.Thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-20 : 09:16:24
|
| Uploads? Are you using FTP?Peter LarssonHelsingborg, Sweden |
 |
|
|
miranwar
Posting Yak Master
125 Posts |
Posted - 2006-11-20 : 09:22:21
|
| No I think it uses a DTS JOB |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-20 : 09:43:07
|
| "I ran the reindex for all tables with a 90% fill factor"Not a good idea for any index which is based on an incrementing, rather than a random, key - such as IDENTITYYou didn't shrink the database after the Reindex did you?I can't remember if you have to Update Statistics after a Reindex, or not.Kristen |
|
|
|
miranwar
Posting Yak Master
125 Posts |
Posted - 2006-11-20 : 10:12:56
|
| Nope i did not shrink the database. I switched off AutoShrink and also switched off AUto Update Stats. I now run a job manually to update statistics each night. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-20 : 10:19:40
|
| Best to leave AUto Update Stats ON and to run the overnight job.Have you any incrementing PKs? If so suggest you recreate the indexes on those back to 100%I would also defragment the physical files (sysinternals CONTIG.EXE is good for that), as all the shrinking and reindexing they have had may have caused them to become fragmented over time (CONTIG.EXE will tell you how many fragments it is combining, so you will know whether the file was fragmented or not [in which case that wasn't the issue of course ...]So what are these "uploads" you refer to?Kristen |
|
|
|
miranwar
Posting Yak Master
125 Posts |
Posted - 2006-11-20 : 15:17:14
|
| Thanks Kirsten..I will have to check the Clustered key tomorrow as I am not in the office right now. However, I was thinking of reducing the fill factor to 75% for the specfic table. before the reindex the table might of been internally fragmented due to heavy page splits. As the pages were not being used to thier capacity uploads had no problem. Now that the reindex has compacted the pages to 90% it's causing pages to be split on the upload.Surley with regards to setting Fill factor at 100% for asc PK's, will this not slow down any updates on rows that have increased size? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-21 : 12:30:24
|
| "Surley with regards to setting Fill factor at 100% for asc PK's, will this not slow down any updates on rows that have increased size?"Fill factor is only for the index part, AFAIK, but if not its a good point!!Kristen |
|
|
|
pootle_flump
1064 Posts |
Posted - 2006-11-21 : 12:59:07
|
quote:
Originally posted by Kristen
"[i]I can't remember if you have to Update Statistics after a Reindex, or not.
A bit late I know but you don't need to - only after indexdefrag. |
|
|
|
|
|
|
|
|
DBCC REINDEDX having adverse effect on Uploads