Post your Azure SQL DB and SQL Server questions at https://pollgab.com/room/brento and upvote the ones you’d like to see me discuss. In this episode, I’m at Jökulsárlón Glacial Lagoon, a wonder of nature, watching the icebergs pile up, stuck in the lagoon due to the incoming high tides.
Here’s what we covered:
- 00:00 Start
- 01:15 Oli-the-dba: I hear “clustering is dumb and complex” on azure IaaS all the time from non dbas. One can only assume they hate HA/DR. However, Have you had any clients go clusterless ?
- 03:14 Ozan: Hi Brent, do you prefer to access external data via linked server or if possible via polybase? What is your experience with polybase? Thanks
- 04:01 Jag B: When avoiding blocking, is READPAST hint better than NOLOCK?
- 04:50 Xavier P: What are the top issues your clients run into when deploying Power BI Gateway?
- 05:37 Jerry Mathers: What is your opinion of DBeaver (
- https://dbeaver.io/) for working with SQL Server VM and Azure SQL?
- 06:22 Wally: Should Azure drives that host SQL VM data files be formatted with 4k, 8k, or 64k block size?
- 07:29 Shehroz Sabzwari: During the building / installing of a new SQL Server instance, when should you run sp_blitz?
- 08:21 Oli-the-dba: Hi Brent, do you see a use case for Azure PaaS if you have a team of 4 experienced dba’s. I’m at a 500 server shop about to embark on our azure journey, performance preferred over cost savings.
- 09:25 Analyse T: Do RPO/RTO numbers usually improve when migrating from Log Shipping to Always On Availability Groups?
- 10:25 Mahtab Keramati: What test apps do you like to run on prospective new SQL VM Hosts for CPU, Network, and Disk performance?
- 11:06 Izzy G: Enjoy how you relate technical problems to every day experiences. Did you have to learn this or does it come naturally?
2 Comments. Leave new
for Wally.. we published some testing recently with different block sizes on AWS: https://aws.amazon.com/blogs/modernizing-with-aws/do-aws-customers-benefit-from-64kb-block-size-for-sql-server/
One small caveat there – the tests involved a VM with 1TB RAM hosting 8TB of data, and the workload style was OLTP.
That strikes me as really odd for a few reasons:
* 8TB of data is fairly large
* An OLTP access pattern for that is fairly unusual (most databases that large are warehouses or reporting)
* 1TB of memory is also pretty dang big
* The observed average read/write size of 9KB confirms that the testing is OLTP (which would do small reads, as opposed to reporting reads which would be larger, especially with Enterprises’s read-ahead reads)
So I thought, “Well, yeah, if you’re reading just 8KB, it doesn’t matter too much what the allocation unit size is, because you’re reading way, way less than that.” I think the test is still valid *for OLTP*, for what that’s worth.
I hate to look a gift horse in the mouth, but it seems like if you’re going to do an 8TB set of databases for testing, I’d aim for more reporting workloads, which would read data 64-256KB at a time, which might also make the allocation unit size actually matter. (Or, test database backups, which would also have much larger read sizes.)
I truly don’t know what to expect from a test like that either – I have no idea whether it would matter or not.