July 1, 2008 at 9:33 am
I'm having to run my maintenance plan to rebuild indexes once or twice a day to keep performance inline.
Anyone know what things could possibly be causing this?
July 1, 2008 at 11:22 am
Heap tables or poorly designed clustered indexes is the first thing that comes to my mind.
If, for example, the clustered index is on an alpha column, and inserts regularly have to re-order the table because of inserting into the middle of the clustered index, rebuilding your indexes frequently would be necessary.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 1, 2008 at 12:54 pm
Do you have primary keys (clustered index) on guids? (uniqueidentifier default to NewID())
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
July 1, 2008 at 10:38 pm
Also check the fillfactor on your indexes - could be causing lots of page splits if this is too high. Note: a value of 0 is almost equivalent to 100.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 3, 2008 at 2:26 am
Hi there,
sorry to (slightly)swerve from the question, but isn't a fill factor of 0 exactly the same as 100 and not almost the same as? I thought I read somewhere a couple of months ago that 0 and 100 are treated the same by SQL Server.
regards
GermanDBA
Regards,
WilliamD
July 3, 2008 at 8:23 am
GermanDBA (7/3/2008)
Hi there,sorry to (slightly)swerve from the question, but isn't a fill factor of 0 exactly the same as 100 and not almost the same as? I thought I read somewhere a couple of months ago that 0 and 100 are treated the same by SQL Server.
regards
GermanDBA
Per this link in BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/237a577e-b42b-4adb-90cf-aa7fb174f3ab.htm
Note:
Fill factor values 0 and 100 are the same in all respects.
To the OP: are you doing a lot of data modifications? This could change statistics and perhaps a simple update stats would be just as effective. Also, are you sure you aren't suffering from 'parameter sniffing', whereby cached query plans that have a given input value lead to horrible query plans for a different input?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 3, 2008 at 1:44 pm
Yes you are right - 0 and 100 are exactly the same. I thought I had read where 0 would actually leave a small amount of space, but I guess I was mistaken.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 3, 2008 at 3:27 pm
Another possibility for you to check
Microsoft® SQL Server™ 2005 collects statistical information about indexes and column data stored in the database. These statistics are used by the SQL Server query optimizer to choose the most efficient plan for retrieving or updating data.
You might want to review
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx#EHKAE
This may give you a clue to improving performance
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply