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 |
|
darrenstewart
Starting Member
11 Posts |
 Posted - 2010-05-16 : 09:14:35
|
| Quick question, if tables were in a poor state and required a reindex, would it be normal to see read only access performing fine but write access performing very badly.i.e. Select statements on tables fine but Write statements timing out?Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-16 : 11:10:33
|
| Yeah, I guess its possible. Huge number of extensions blocks on the index taking time to traverse, lots of moving blocks around to accomodate inserting a row, that type of thing.Blocking on writes would be the other thing that could slow it down.I presume this relates to your recent upgrade SQL2000 to SQl2005? (this thread)It is possible to query the fragmentation on the index (not got a query to hand, but Google should help)If it is Reads-blocking-Writes it would be worth considering READ_COMMITTED_SNAPSHOTBut if and indexes have not been rebuilt since the upgrade that ought to be done first.You mentioned that a low of records had been added to the DB on Friday, that may have skewed the indexes. |
 |
|
|
darrenstewart
Starting Member
11 Posts |
Posted - 2010-05-16 : 11:26:04
|
| Yes its related to the upgrade. A reindex has been run following the upgrade, however a lot of records have been added this weekend - taking policy count from 130,000 to 155,000 in one long transaction.What's your thought on running an UPDATE STATS following a re-index, there seems to be very different thoughts around? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-16 : 11:30:04
|
| We run Reindex on Update Stats every night on any table that is fragmented. Although we are 24/7/365 we have significantly lower activity overnight and it seems to us that reshaping tables to "optimal structure" should not delay any longer than necessary.Reindex creates huge demands on TLog, and thus Tlog backup, so on large tables we only "defrag" rather then "reindex" to reduce the impact (plus only doing the rebuilds on fragmented tables, so actually only a small proportion of our tables/indexes are rebuilt each night)I suppose it is likely that your new policies will have been added "in order", and any new policies will now be inserted after them - so that could be a serious number of index extension blocks, or page-splits. I'm guessing a bit though! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-16 : 11:41:56
|
| DBCC showcontig('[/i]MyTable[/i]') WITH ALL_INDEXESshould show you the fragmentation of indexes on a given table (this command is deprecated, but probably the easiest way to see them)I have this note from SQL 2000, haven't checked for correctness on SQL 2005, but hopefully still useful / accurateScan Density -- 100% is best, 0% worstLogical Scan Fragmentation - Lower is betterExtent Scan Fragmentation - Lower is betterAvg. Bytes free per page - Lower is betterAvg. Page density (full) - Higher is better |
|
|
|
|
|
|
|
|
Read/Write difference in performance