Website Technology Profiler

{
  "PageContent": "Rainer Unwin's Thoughts On SQL Server And AgilityAs another post mentioned I was recently at SQL Saturday in Manchester. One of the presentations raised a very interesting question about what statistics io shows when you have a forwarded record. I thought it would make an interesting blog post to investigate this question and find an answer as best I can. Fair warning, this is a long post and you can download the scripts and read the summary at the end. Most of the explanation is in the scripts. This is because for some reason at the moment code tags don’t seem to work on this theme and a nice syntax highlighter I thought might work breaks my site (I’ll get there and update previous posts though).Lets start with the setup. First we create a view to make these scripts shorter (Trust me they’re long enough). We then create a simple table with two columns and show that it has one IAM page (I will post about IAM pages in more depth in future but Paul Randal has an excellent article on them) and one data page. You will need to run the setup for each script and each script starts with part 2. The below, standard blurb, applies to all of these scripts: –These scripts were written by Rainer Unwin (c) 2015You may alter this code for your own non-commercial purposes (e.g. in afor-sale commercial tool).You may republish altered code as long as you include this copyright andgive due credit, however you must obtain my prior permission before bloggingthis code.This code and information are provided “as is” without warranty ofany kind, either expressed or implied, including but not limitedto the implied warranties of merchantability and/or fitness for aparticular purpose. You use the script at your own risk and shouldalways test in a pre-production or test environment before runningin production.The view: –[code language=”sql”]use tempdb;goif object_id( N’dbo.reads_pages’,’V’ ) is not null drop view dbo.reads_pages;gocreate view dbo.reads_pagesasselect is_iam_page, allocated_page_file_id, allocated_page_page_idfrom sys.dm_db_database_page_allocations(2, object_id( ‘dbo.reads’, ‘U’ ), 0, 1, ‘detailed’)where is_allocated = 1;go[/code]Lets get an understanding of what stats io normally shows usScript 1: –[code language=”sql”]— :: PART 1 – setupset nocount on;gouse tempdb;go— create an object to play withif object_id( N’dbo.reads’, ‘U’ ) is not null drop table dbo.reads;gocreate table dbo.reads( i int, data char( 5000 ) );go— :: PART 2 – check with a single page— insert a single record and check the structure of the table— then confirm that we get a single read (The data page only)insert dbo.reads( i, data ) values( 1, ‘a’ );goselect *from dbo.reads_pages;goset statistics io on;goselect *from dbo.reads;go— just 1 read (The data page). The IAM page is not read.— lets try again with a second page of data.— :: PART 3 – Now try with 2 data pagesinsert dbo.reads( i, data ) values( 2, ‘b’ );goselect *from dbo.reads_pages;goselect *from dbo.reads;go— again we see the number of reads of data pages only.— we need to prove this is the case when we exceed the— extent boundary as well so lets add another 10 pages— :: PART 4 – lets use 12 pages which exceeds the extent boundary and conclusioninsert into dbo.reads( i, data )values( 3, ‘c’ ), ( 4, ‘d’ ), ( 5, ‘e’ ), ( 6, ‘f’ ), ( 7, ‘g’ ), ( 8, ‘h’ ), ( 9, ‘i’ ), ( 10, ‘j’ ), ( 11, ‘k’ ), ( 12, ‘l’ );goselect *from dbo.reads_pages;goselect *from dbo.reads;go— If you have multiple files, as I do, you may also— find that you now have multiple IAM pages. In my— case I had 12 data pages and 2 IAM page. reads— from the select were 12 matching the number of— data pages[/code]Script 1 summarySo what this script shows is the behaviour of statistics io with no forwarded records. As you can clearly see it never shows that the IAM page is read. Logically, since we do an allocation order scan, this should be read. The take away is that only data pages are shown by statistics io.Script 2 – What is shown for forwarded records where each forwarded record occupies its own page[code language=”sql”]— :: PART 1 – setup, use a varchar column to allow us to forward recordsset nocount on;gouse tempdb;go— create an object to play withif object_id( N’dbo.reads’, ‘U’ ) is not null drop table dbo.reads;gocreate table dbo.reads( i int, data varchar( 8000 ) );go— :: PART 2 – check with a single page— insert 7 records and check the structure of the table— then confirm that we get a single read (The data page only)insert dbo.reads( i, data )values( 1, replicate( ‘a’, 7677 ) ) — fill the page, ( 2, replicate( ‘b’, 50 ) ), ( 3, replicate( ‘c’, 50 ) ), ( 4, replicate( ‘d’, 50 ) ), ( 5, replicate( ‘e’, 50 ) ), ( 6, replicate( ‘f’, 50 ) ), ( 7, replicate( ‘g’, 50 ) );goselect *from dbo.reads_pages;goset statistics io on;goselect *from dbo.reads;go— just 1 read (The data page). The IAM page is not read.— :: PART 3 – Now generate 1 forwarded recordupdate dbo.readsset data = replicate( ‘b’, 5000 )where i = 2go— 3 pages: 1 IAM, 1 data & 1 for the forwarded recordselect *from dbo.reads_pages;goselect *from dbo.reads;go— now we see 3 page reads. This could be because we— have read the IAM page to see where we should read— the forwarded page or it could be that we jumped— to the forwarded page and then back again— :: PART 4 – lets forward another record off the main data pageupdate dbo.readsset data = replicate( ‘d’, 5000 )where i = 4;go— 4 pages: 1 IAM , 1 data, 2 for forwarded recordsselect *from dbo.reads_pages;goselect *from dbo.reads;go— 5 reads but how did we get there? if we read the IAM— page then we would read just 4 pages. The IAM, the— data page and the 2 forwarded pages. No we must have— read the data page, followed the pointer, then back— to the data page and repeat for the other forward— pointer. Lets confirm with 1 more.— :: PART 5 – lets forward another record off the main data pageupdate dbo.readsset data = replicate( ‘f’, 5000 )where i = 3;go— 5 pages: 1 IAM , 1 data, 3 for forwarded recordsselect *from dbo.reads_pages;goselect *from dbo.reads;go— yes the pattern holds. Each new forwarded record adds— two reads. One to go the the forwarded page and one— to go back to the data page. In the next section you— will see another interesting effect[/code]Script 2 summaryWhat we have shown here is that we count reading the data page and each page that has a forwarded record each time that we look at a record on it. So if you have 7 records, and records 2 an 4 are forwarded, then we read the page as follows (And count 5 reads): –1: Read the main data page and id = 1 and 2 (Forwarding pointer).2: Follow the forwarding pointer and read that data page.3: Read the first data page again to read id = 3 and 4 (Forwarding pointer).4: Follow the forwarding pointer and read that data page.5: Read the first data page a third time to read id 5 through 7.Script 3 – what if several forwarded records are on to the same page[code language=”sql”]— :: PART 1 – setup, use a varchar column to allow us to forward recordsset nocount on;gouse tempdb;go— create an object to play withif object_id( N’dbo.reads’, ‘U’ ) is not null drop table dbo.reads;gocreate table dbo.reads( i int, data varchar( 8000 ) );go— :: PART 2 – check with a single page— insert 5 records and check the structure of the table— then confirm that we get a single read (The data page only)insert dbo.reads( i, data )values( 1, replicate( ‘a’, 7677 ) ) — fill the page, ( 2, replicate( ‘b’, 50 ) ), ( 3, replicate( ‘c’, 50 ) ), ( 4, replicate( ‘d’, 50 ) ), ( 5, replicate( ‘e’, 50 ) ), ( 6, replicate( ‘f’, 50 ) ), ( 7, replicate( ‘g’, 50 ) );goselect *from dbo.reads_pages;goset statistics io on;goselect *from dbo.reads;go— just 1 read (The data page). The IAM page is not read.— :: PART 3 – Now generate a forwarded recordupdate dbo.readsset data = replicate( ‘b’, 500 )where i = 2;go— 3 pages: 1 IAM, 1 data & 1 for both forwarded recordsselect *from dbo.reads_pages;goselect *from dbo.reads;go— 3 reads as expected.— :: PART 4 – lets forward another record off the page but onto the same forwarding pageupdate dbo.readsset data = replicate( ‘c’, 500 )where i = 4;go— 3 pages: 1 IAM, 1 data & 1 for both forwarded recordsselect *from dbo.reads_pages;goselect *from dbo.reads;go— 4 reads? But last time we got 5 reads. So it looks— like when we use the same forwarding page then we— get fewer reads. Lets try again— :: PART 5 – lets forward another record off the main data pageupdate dbo.readsset data = replicate( ‘f’, 500 )where i = 6;go— 3 pages: 1 IAM, 1 data & 1 for both forwarded recordsselect *from dbo.reads_pages;goselect *from dbo.reads;go— ok that is weird now we get 5 reads from the same— 3 pages as before. What is going on here? We don’t— count the forwarded page each time when it’s the— same page. So whereas before every forwarded— record had it’s own page now it doesn’t. We still— count the main data page whenever we read it, but— we don’t count the forwarding page if it is the— same. Wow, so misleading![/code]Script 3 summarySo what happens here is slightly different than before. We can show that if all the forwarded records are on the same page then we only count the page that contains the forwarded records once. This might not be what you would expect and that is because it is a lie! It is a big fat lie in fact. If you repeat the same test as in script 3 up to the end of part 4 and then do the below you can show that the reads are a lie: –Open a new query window (I’ll call it query bail) and update dbo.reads where i = 3 but don’t commitThen run the read from script 3, this will block at i = 3Open another new query (I’ll call it update) and set data = replicate( ‘z’, 500 ) for i = 4, let this commitNow rollback query bailYou will see that in fact we did re-visit the forwarded page to see the ‘z’s on there.So there you have it, statistics io… It’s great, it’s really helpful and yet it’s dishonest to some degree as well. At least you now know what’s going on though 🙂Scripts: –Stats IO reads part 0 – create viewStats IO reads part 1Stats IO reads part 2Stats IO reads part 3",
  "Url": "http://rmunwin.com",
  "IpAddress": "162.215.248.53",
  "ReverseDns": "162-215-248-53.unifiedlayer.com",
  "Asn": null,
  "Http2": false,
  "Http3": false,
  "ResponseTimeMs": 1936,
  "StatusCode": 200,
  "ServerHeaders": [
    "Apache"
  ],
  "Ssl": {
    "Issuer": null,
    "Subject": null,
    "NotBefore": null,
    "NotAfter": null,
    "SubjectAltNames": [],
    "Hsts": false,
    "Ciphers": [],
    "Protocol": null,
    "DaysUntilExpiry": 0
  },
  "Cdns": [],
  "HostingHints": [],
  "Cms": [
    "WordPress",
    "WordPress (robots.txt)"
  ],
  "Frameworks": [
    "React",
    "jQuery"
  ],
  "JsLibraries": [],
  "Analytics": [
    "Google Analytics GA4"
  ],
  "AdsAndMarketing": [],
  "PaymentGateways": [],
  "EcommerceHints": [
    "PrestaShop"
  ],
  "SocialSignals": [],
  "MediaProviders": [],
  "SeoTags": [
    "content-type: text/html; charset=UTF-8"
  ],
  "HtmlLanguage": "en-US",
  "HasRobots": true,
  "HasSitemap": true,
  "SecurityHeaders": [],
  "CookiesObserved": [],
  "XhrApiHints": [],
  "SitemapCounts": {
    "total_urls": 5
  },
  "ChatWidgets": [],
  "ABTesting": [],
  "PrivacyConsent": [],
  "EmailMarketing": [],
  "WebFonts": [],
  "DynamicJs": [
    "Web Server: Apache",
    "jQuery (script Resource)",
    "ES6+ JavaScript Features",
    "React (CDN Detection)",
    "jQuery (CDN Detection)"
  ],
  "ResourceHosts": [
    "rmunwin.com",
    "gmpg.org"
  ],
  "CssFrameworks": [],
  "BuildTools": [
    "Modern JS Build Tool (inferred from React)"
  ],
  "ServerTech": [
    "Generator: WordPress 6.9",
    "PHP (inferred from WordPress)"
  ],
  "DatabaseHints": [],
  "PageTitle": "Rainer Unwin's Thoughts On SQL Server And Agility | Musings on databases, data, architecture and agility",
  "MetaDescription": null,
  "ImageCount": 0,
  "ScriptCount": 6,
  "LinkCount": 10,
  "Errors": [],
  "UiFrameworks": [
    "D3.js",
    "Vuetify (Class Names)",
    "Angular Material (Class Names)"
  ]
}