Accidental SQL DBA, 12/22/2023

Breaking News!

SQL Saturday Austin 2024 (#1073) – March 9, 2024 – We are a Platinum sponsor…come say hello and attend the lunch session Kevin Hill will be doing!

SQL Server Resource Interaction

I touched on some of this in my “Memory is King” LinkedIn post (link below), and there were some great bits in there, as well as in the comments.

All three of the “big” resources (memory, CPU, disk) have a crucial role in SQL Server.

·???????? Disk – data storage and persistence

·???????? Memory – hold data while it is being “processed”

·???????? CPU – Do the actual processing of the query

A common misconception is that faster disks will make queries faster. While this is true, its not necessarily true for the reason most non-SQL Server people I talk to think it is.? Data lives in 8K pages. This is Table Data (Users, Sales, etc.) as well as Index data. For now, we’ll be ignoring all the system objects and processes.

SQL Server does not read directly from disk to satisfy a query. It pulls the data page into memory and then reads it to find the relevant row(s). It is very possible it is pulling 8K in for just one row of only a few bytes. That 8K page will sit in memory until SQL Server needs that space for a different page. Now, multiply that 8K page times all the queries and all the tables across your workload.? Yes, faster disk helps (especially SSD vs HDD), but its more indirect.

If SQL Server is churning through a really big query (Monthly sales, grouped by region and salesperson), by nature this is going to read a LOT of pages. What do I mean by a LOT?? It Depends ??. That is “you” specific. If the query needs more memory for those pages than has been allocated to it, it will “spill” pages into tempDB. This is where proper tempdb configuration on your fastest drives IS important.? Also, these complex reporting queries might be using a TON of CPU. With any luck, they will use multiple threads…but that’s a different article.

All 3 resources interact all the time. I recommend getting as much memory as you can afford for Enterprise edition installs, and at least 192GB for Standard Edition, 2014 and up. There are some edge cases where having too much RAM can be a problem, but I’ve not seen that until 1TB or more of RAM.

Let me know if you would like some clarification! There are various ways to measure all these numbers…some built into SQL Server, some written by the DBA community.

My recent LinkedIn post(s):

Speaking of Competition

Memory is King

Happy Code Freeze! – a new holiday I’m trying to get started for tech people

Interesting stuff I read this week:

Dow Hits Record high last Friday! ?And followed up with several more

Erik Darling has an “Art of the SQL Server Stored Procedure” series that is worth reading

Wayfair CEO: Employees need to work longer hours | CNN Business – When will the C-Suite learn this demotivates most people?

If you are interested in any of Dallas DBAs consulting services, reach out to Kevin to lock in 2023 pricing while we talk about your needs.

SQL tidBITs:

SQL Server 2019, CU 24 is out. Don’t jump into it right away! We always recommend waiting to install a CU for 2-3 months. Microsoft does occasionally release a buggy patch, and I would rather others find those bugs…not you. CU 23 should be just fine at this point.

Sponsored by: