tag:blogger.com,1999:blog-3617908412741200959.post5224830302823884610..comments2024-03-18T22:03:43.359-07:00Comments on sql.sasquatch: #sqlserver tempdb: "insert into... select" vs "select... into" vs index sort_in_tempdb write behaviorSQL_Sasquatchhttp://www.blogger.com/profile/13470482959972282429noreply@blogger.comBlogger2125tag:blogger.com,1999:blog-3617908412741200959.post-55300852001504532922017-03-30T14:10:20.119-07:002017-03-30T14:10:20.119-07:00Exactly right about the saturated bandwidth result...Exactly right about the saturated bandwidth resulting in very high latency.<br /><br />Interesting that you should bring up the resource governor :-)<br />General IOPs limits to prevent write floods tend to work poorly. In this instance I have a database spread evenly over 8 files in a filegroup, each on own Windows volume and RDM.<br /><br />RG gives proportional governance to IOPs per volume if defined and if there is competition.<br /><br />In this case a single CREATE INDEX at maxdop 4 can flood with writes - if uncontested, governance won't kick in.<br /><br />VMware sioc is another approach (one I'm not fond of because consolidation isn't a goal of mine). If latency crosses the threshold in the datastore, IO against the datastore gets throttled. Also wouldn't work too well here.<br /><br />Many storage arrays have QoS settings to limit bytes/sec or IOPs for a host or LUN at the front end adapter. Host level wouldn't work well since I want to max out reads/writes to persistent database files, transaction logs. And reads from tempdb haven't been a problem (at least not yet).<br /><br />So where does that leave the idea of throttling tempdb spill writes?<br /><br />Maybe... just maybe... the -k startup option might work. Haven't tried it yet. We'll see. It would also potentially affect max write rate for checkpoint to persistent databases though, so thorough testing is warranted. :-) SQL_Sasquatchhttps://www.blogger.com/profile/13470482959972282429noreply@blogger.comtag:blogger.com,1999:blog-3617908412741200959.post-53350608585260247882017-03-30T02:32:30.635-07:002017-03-30T02:32:30.635-07:00Interesting, thanks! It makes sense that the laten...Interesting, thanks! It makes sense that the latency is so high for the create index, precisely because of the fast flash storage -- it maxed out the adapter bandwidth, so the I/O queued up on the VM and/or the hypervisor.<br />Could be especially dangerous if you're maxing out the bandwidth of all the blades on the chassis, if you're using a blade. May be a good use for resource governor to limit the IOPS.Alex Friedmanhttps://www.blogger.com/profile/10891211264360944996noreply@blogger.com