Performance Test - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Performance Test

Author

Topic

ksr39
Posting Yak Master

193 Posts

Posted - 2011-11-29 : 10:24:31

Hi experts,

I’m not much aware of performance as we have gone through a test on one of our server in the morning.
As I can see that in the performance monitor I found

Average disk Queue length - max:-0.65 and Avg: - 0.137
Page splits/sec Avg:-23 and Max:-1129
Full scan/sec Avg:-3.98 and Max:- 159
Lock request/sec Avg:-450.534 Max:-3499

We have rebuild index job on every week end and update stats also but couldn’t find what the reason for increase is in Full scan and page splits.

Please let me know if it can be reduced and the performance can be good.

Thanks in advance.

james_wells
Yak Posting Veteran

55 Posts

Posted - 2011-11-30 : 11:24:18

----------

What is your fill factor

high page splits could be caused by a low fill factor
Try increaseing the fill factor and monitor the page splits again to see if this helps.

----------

full table scans can be caused by missing indexes

This should help track down and
show the cost of the those missing indexes

select top 10
round(avg_total_user_cost * avg_user_impact
* (user_seeks + user_scans), 0) "Total Cost",
avg_user_impact, statement "Table Name",
equality_columns "Equality Columns",
inequality_columns "Inequality Columns",
included_columns "Include Columns"
from sys.dm_db_missing_index_groups g
join sys.dm_db_missing_index_group_stats s
on s.group_handle = g.index_group_handle
join sys.dm_db_missing_index_details d
on d.index_handle = g.index_handle
order by "Total Cost" desc;

----------------------------------------------

if you are rebuilding your indexes rather then a re-org then the stats are normally updated on a rebuild

-------------------------------------------------

ksr39
Posting Yak Master

193 Posts

Posted - 2011-11-30 : 11:52:03

Hi James,

Thanks for the reply, as if now my fill factor is 0 and to how much i need to increase it.and i have run the query which you gave i got the result in total cost column
1202888
930411
846976
430510
9562
447
402
297
200
186

and avg user_impact column is
67.73
75.61
98.76
69.38
15.8
40.1
98.72
62.79
13.48
12.54

what does it mean, should i need to recreate the indexes on the perticular database. as i got the info on a single database.

james_wells
Yak Posting Veteran

55 Posts

Posted - 2011-11-30 : 13:57:50

with fill factor 0 is the same as 100 and represents what percentage of the leaf level index is filled. In the case of 0 or 100 the index leaf page is filled to capacity.

The best fill factor to use depends on your system for example indexes with an indentity collumn will add data to the end of the table therefore page splits should not cause a decrease in performance.

OLTP systems that are heavy transactionally may benefit by reducing the fill factor to 85% whilst rembering that the fill factor is only taken advantage of when an index is rebuilt or recreated there a re-org would have no effect whilst this should reduce page splits.

OLAP systems - fill factor 100

With reference to the missing index script i posted the field
avg_user_impact is the expected improvement the query will benefit from by creating the index. Before adding the index , how many indexes are already allocated to the table , consider removing unused indexes.

--- unused indexes -----
DECLARE @dbid INT
SELECT @dbid = DB_ID(DB_NAME())
SELECT OBJECTNAME = OBJECT_NAME(I.OBJECT_ID),
INDEXNAME = I.NAME,
I.INDEX_ID
FROM SYS.INDEXES I
JOIN SYS.OBJECTS O
ON I.OBJECT_ID = O.OBJECT_ID
WHERE OBJECTPROPERTY(O.OBJECT_ID,'IsUserTable') = 1
AND I.INDEX_ID NOT IN (
SELECT S.INDEX_ID
FROM SYS.DM_DB_INDEX_USAGE_STATS S
WHERE S.OBJECT_ID = I.OBJECT_ID
AND I.INDEX_ID = S.INDEX_ID
AND DATABASE_ID = @dbid)
ORDER BY OBJECTNAME,
I.INDEX_ID,
INDEXNAME ASC

Kristen
Test

22859 Posts

Posted - 2011-11-30 : 19:45:56

Be very careful reducing Fill Factor on OLTP from 100% to, say, 80%.

There is conflicting advice on this, but heavy OLTP users I speak to say they run ALL indexes at 100% fill because the time saved on reduced numbers of Page Splits is MORE than taken up by the increased Reads (because there are more pages in the index)

james_wells
Yak Posting Veteran

55 Posts

Posted - 2011-12-01 : 05:57:28

Kristen

I agree there is conflicting reports on fill factor and thats wht i stressed is all about "YOUR SYSTEM" and there was not enough info posted to give sound advice.

The origainal statement by the logger suggested that there was high page splits. My point was IF THIS was actually causing a performance issue then reducing the fill factor may help and i did point out that this can degrade read performance however depending on how indexes have been applied the read performance dip could be compensated by less index fragmentation on heavy OLTP systems.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources