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 |
|
keithc1
Yak Posting Veteran
88 Posts |
 Posted - 2004-06-28 : 00:13:39
|
| Hey all i do selects on a certain column that the execution plan was showing this result from statistics IO(68650 row(s) affected)Table 'Tablename'. Scan count 1, logical reads 34325, physical reads 34, read-ahead reads 7856.I placed a non clustered index on this with a fillfactor of 50and now it is much improved but was wondering if the logical reads is still an acceptable value.Table 'Tablename'. Scan count 1, logical reads 376, physical reads 0, read-ahead reads 0.It is a logging table and is very busy and outside of the logical reads being acceptable I am wondering is there a way to know how many page splits are occuring with this fillfactor value and does the amount of page split occurance relate to rebuilding index's in anyway? I'm trying to figure out as well wehn and how often I should rebuild the index'sKeithc MCSE MCSA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-28 : 13:42:33
|
| Fillfactor of 50 is pretty low. MS has an article that suggests rebuilding indexes does not have much of an impact on small scale systems (10GB to them is small apparently). Here's the article I'm referring to:http://www.microsoft.com/sql/techinfo/productdoc/2000/systables.aspIt's lengthy but worth a read for DBCC DBREINDEX and DBCC INDEXDEFRAG.Tara |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-06-28 : 15:20:06
|
| the 376 logical reads means that 376 8k pages were scanned in memory to return your resultset. That's not bad considering your earlier query scanned 34325 8k pages. I would say that this is a very nice improvement in your query.you can see how many page splits are occuring by monitoring the perfmon counter SQLServer:Access Methods:Page Splits/sec.-ec |
|
|
|
|
|
|
Index's