Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Defining this in statistical terms is somewhat difficult, you can literally do the maths and work out the additional IO overhead.</p> <p>Let's take a table with 1 million rows, and assume no page padding, compression and use some simple figures.</p> <p>Given a table whose row size is 100 bytes, that contains 10 tinyints. The number of rows per page (assuming no padding / fragmentation) is 80 (8096 / 100)</p> <p>By using Bigints, a total of 70 bytes would be added to the row size (10 fields that are 7 bytes more each), giving a row size of 170 bytes, and reducing the rows per page to 47.</p> <p>For the 1 million rows this results in 12,500 pages for the tinyints, and 21277 pages for the Bigints.</p> <p>Taking a single disk, reading sequentially, we might expect 300 IOs per second sequential reading, and each read is 8k (e.g. a page).</p> <p>The respective read times given this theoretical disk is then 41.6 seconds and 70.9 seconds - for a very theoretical scenario of a made up table / row.</p> <p>That however only applies to a scan, under an index seek, the increase in IO would be relatively small, depending on how many of the bigint's were in the index or clustered key. In terms of backup and restore as mentioned, the data is expanded out and the time loss can be calculated as linear unless compression is at play.</p> <p>In terms of memory caching, each byte wasted on a page on disk is a byte wasted in the memory, but only applies to the pages in memory - this is were it will get more complex, since the memory wastage will be based on how many of the pages are sitting in the buffer pool, but for the above example it would be broadly 97.6 meg of data vs 166meg of data, and assuming the entire table was scanned and thus in the buffer pool, you would be wasting ~78 megs of memory.</p>
    singulars
    1. This table or related slice is empty.
    1. POStatistical analysis for performance measurement on usage of bigger datatype wherever they were not required at all
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USAndrew
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POStatistical analysis for performance measurement on usage of bigger datatype wherever they were not required at all
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COI would like to change accepted answer if some one can provide me some statistical data along with such a nice conceptual information provided by Andrew
      singulars
      1. PO
      1. USShantanu Gupta
    2. COIn what sense statistical data - if you detail the specifics of your table, we can calculate the difference in size, IO. Tell us what the clustered key is and we can also calculate what the affect on that is in terms of index levels and index storage. But to expec there to be one magical statistical value that tells you how to manage SQL performance is not going to happen.
      singulars
      1. PO
      1. USAndrew
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload