Acceptable IO Cost Guidelines 1

[vbkris]

Hi Guys,

I currently have a table that has around 177000 rows.

When i do a simple select * from the table, it gives me an IO cost of 11.6

The table has around 50 columns and is VERY frequently used.

Currently the DB is there on ONE file system.

Is this cost acceptable???

Known is handfull, Unknown is worldfull

 

[SQLDenis]

Wow 50 columns, how wide is that table? run a showcontig and post results

Don't use SELECT * if you don't need all the columns

do vertical partitioning (normalizing) by moving the columns that are not needed every time to a separate table this will greatly improve IO

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[TamedTech]

It was only yesterday that George confirmed to me that splitting tables into logical chunks can be a worth while exercise.

With SQL Server, data is stored in 8K pages. So, having smaller (narrower) tables, will allow you to fit more records in each page. This will reduce your file i/o and therefore increase your performance. By mashing all the fields in to 1 table, you will end up with less records per 8K page and your file i/o will increase.

50 Columns is a pretty fat old table, and with that many rows thats alot of data to be returned.

Rob

 

[vbkris]

The stats:

TBL 2098926649 PK_TBL 1 0 15674 NULL NULL NULL NULL NULL 0 2115 NULL NULL 92.627599243856324 1960 2116 0.60609924793243408 NULL
TBL 2098926649 IX_TBL 48 0 1569 NULL NULL NULL NULL NULL 0 1328 NULL NULL 14.823175319789314 197 1329 41.108985900878906 NULL
TBL 2098926649 IX_TBL_1 49 0 693 NULL NULL NULL NULL NULL 0 314 NULL NULL 27.61904761904762 87 315 15.584415435791016 NULL
TBL 2098926649 IX_TBL_2 50 0 636 NULL NULL NULL NULL NULL 0 245 NULL NULL 32.520325203252028 80 246 10.849056243896484 NULL
TBL 2098926649 IX_TBL_3 51 0 636 NULL NULL NULL NULL NULL 0 245 NULL NULL 32.520325203252028 80 246 10.849056243896484 NULL
TBL 2098926649 IX_TBL_4 52 0 537 NULL NULL NULL NULL NULL 0 149 NULL NULL 45.333333333333329 68 150 6.7039108276367188 NULL
TBL 2098926649 IX_TBL_5 53 0 610 NULL NULL NULL NULL NULL 0 100 NULL NULL 76.237623762376245 77 101 1.9672131538391113 NULL
TBL 2098926649 IX_TBL_6 54 0 534 NULL NULL NULL NULL NULL 0 127 NULL NULL 52.34375 67 128 4.8689136505126953 NULL
TBL 2098926649 IX_TBL_7 55 0 606 NULL NULL NULL NULL NULL 0 92 NULL NULL 81.72043010752688 76 93 1.1551154851913452 NULL
TBL 2098926649 IX_TBL_8 56 0 630 NULL NULL NULL NULL NULL 0 133 NULL NULL 58.955223880597018 79 134 5.0793652534484863 NULL
TBL 2098926649 IX_TBL_9 57 0 694 NULL NULL NULL NULL NULL 0 220 NULL NULL 39.366515837104075 87 221 13.832853317260742 NULL
TBL 2098926649 IX_TBL_10 58 0 607 NULL NULL NULL NULL NULL 0 156 NULL NULL 48.407643312101911 76 157 4.6128501892089844 NULL
TBL 2098926649 IX_TBL_11 59 0 643 NULL NULL NULL NULL NULL 0 208 NULL NULL 38.755980861244019 81 209 10.108864784240723 NULL
TBL 2098926649 IX_TBL_12 60 0 611 NULL NULL NULL NULL NULL 0 96 NULL NULL 79.381443298969074 77 97 2.1276595592498779 NULL


As regards to chaning the design, this is a maintanance app and this is the most important transaction table. i would PREFER not changing its structure...

Known is handfull, Unknown is worldfull

 

[vbkris]

command used:
DBCC SHOWCONTIG(TBL) WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS

the columns:
ObjectName ObjectId IndexName IndexId Level Pages Rows
MinimumRecordSize MaximumRecordSize AverageRecordSize
ForwardedRecords Extents ExtentSwitches AverageFreeBytes
AveragePageDensity ScanDensity BestCount ActualCount
LogicalFragmentation ExtentFragmentation

Known is handfull, Unknown is worldfull

 

[SQLDenis]

just run this

DBCC SHOWCONTIG(TBL)

and then

DBCC SHOWCONTIG(TBL) WITH TABLERESULTS


bacause you used fast it skipped some important stuff

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[vbkris]

the output:
DBCC SHOWCONTIG scanning 'TBL' table...
Table: 'TBL' (2098926649); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 15698
- Extents Scanned..............................: 1974
- Extent Switches..............................: 2118
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 92.64% [1963:2119]
- Logical Scan Fragmentation ..................: 0.61%
- Extent Scan Fragmentation ...................: 28.17%
- Avg. Bytes Free per Page.....................: 1598.0
- Avg. Page Density (full).....................: 80.26%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


Known is handfull, Unknown is worldfull

 

[SQLDenis]

yep your table is way to wide your average rowsize = 715 bytes

--this is not exact but pretty close because your pages are not 100% full
select 177000/15698.0 == average 11 rows per page


select 8060/11.27  == average 715 bytes per row


[code]

also do you really need 14 indexes on that table?
You could try to upp the fillfactor to 100%, right now you have 20% unused space, you might be able to squeeze another row in per page but will that really help you?
Without redesigning this table the only advice I can give is top get rid of unused indexes and move the nonclustered indexes to a separate drive




Denis The SQL Menace 
--------------------
[url=http://sqlservercode.blogspot.com/]SQL Server Code,Tips and Tricks, Performance Tuning[/url] 
[url=http://otherthingsnow.blogspot.com/2005/10/google-interview-questions-they-are.html]Google Interview Questions[/url]

 

[vbkris]

Hey Denis,

>>Without redesigning this table the only advice I can give is top get rid of unused indexes and move the nonclustered indexes to a separate drive

as regards the index it is very difficult to get rid of them as ALL of them identify with important fields used in reports. if i remove then reports are going to be slow.

At any given time this table will be accessed by minimum 200+ users.

Will give your idea of seperating table and indexes a try.

I also have a doubt in your calculation:
I thought each row can be of 8060 bytes in a page.

Then how is this calculation valid:
select 8060/11.27 == average 715 bytes per row

Known is handfull, Unknown is worldfull

 

[vbkris]

Hey,

Checked out with BOL, the calculation was correct. my mistake

Known is handfull, Unknown is worldfull

 

[vbkris]

so how many rows do you think a page must hold? What is the optimum number?

Known is handfull, Unknown is worldfull