how to decide how many number of col in a table can be made keeping 8kb lenght to speed up. RRS feed

  • Question

  • hi,

    I am making one big table which will have good no of cols,

    There is one critaria that is, to seepd up keep the lengh of row less than 8 kb pages(now it has been raised).

    Q1)please tel me ,in this critaria ,collenght is checked or datalenght is checked , because i know that my col length will exceed 8 kb but data will never exceed , it is rare, may be few rows may exceed.

    yours sincerley.

    Wednesday, March 4, 2015 6:42 AM


All replies

  • It is unclear what you are trying to achieve? A disk space? In order to speed up the query you need proper;y created indexes...

    Calculate numbers  of rows that ca fit on an 8K data page
    8060/bytes per row= rows per page
    Calculate  the number of data pages needed 
    Number of estimated rows / rows per page =data page needed
    8192* data pages needed = Total bytes
    Total bytes\1024= Total KB

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, March 4, 2015 7:42 AM
  • " When you update a row with data that will cause the row to exceed the 8K limit, part of that row is moved to a new page. If you update a row that is split between pages and it now fits within the 8K limit, this may cause the split row to be merged back into the original data page, both of which cause performance degredation. Querying data or performing joins on data that have data allocated in the ROW_OVERFLOW_DATA also slows performance because these records are processed synchronously. If you were to normalize that data so that the data is split between tables and use a JOIN instead of using rows larger than 8K, this speeds your performance back up because JOINS are asynchronous operations." so i wanted to know that creating more number of cols can create problem if its total size exceeds the limit or it is the data in the table which should be considered. yours sincerely.
    Thursday, March 5, 2015 8:57 AM
  • Rajemessage,

    Did you use the info from Uri to calculate the data?


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Monday, March 9, 2015 4:00 AM
  • my question was diffrent.

    I wanted to find the row length, that is,is it made up of full col length, or the data stored in it?

    yours sincerley.

    Sunday, April 12, 2015 6:24 AM