Accidental SQL DBA, 12/22/2023

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:


Noor Muhammad

Sr.Database Administrator Team Lead at Blazeo (Formerly ApexChat)

1 年

Great

回复
Ife Adeniyi

| Data Architecture and Strategy | Fabric | Azure | AWS | GCP | SQL Server 2025 |

1 年

I'm curious, how did you come by the 192GB value? Kevin Hill

回复
Anil Mahadev

Oracle ACE PRO ? | Principal Cloud Architect @ IDERA Software | Innovating Database & Multi-Cloud Solutions | Expert in OCI & Data-Driven Strategies | Bridging Technology with Business Impact

1 年

Looking forward to meeting you in person Kevin! Merry Christmas and prosperous 2024!!

回复
John Sterrett

Removing your database, data warehousing, and analytics problems as a Managed Services Provider using only Sr. Data Architects. No wasted billable time, no Jr. Resources.

1 年

Looking forward to catching up in person in March.

Alex Wilson

Sr. Database Administrator | SQL Server | PostgreSQL | MongoDB| MySQL | DBRE | Azure | AWS | Powershell

1 年

Your piece on resource interaction is one of, if not the best descriptions I’ve read on the subject.

要查看或添加评论,请登录

Kevin Hill的更多文章

  • SQL Server Source Control on a $0 Budget

    SQL Server Source Control on a $0 Budget

    The Source Control Dilemma Every DBA has been there. Trying to keep track of database schema changes while developers…

    3 条评论
  • My SQL Server is Slow! What Do I Do First?

    My SQL Server is Slow! What Do I Do First?

    It’s a vague but common complaint, frequently with no additional details Before blindly restarting SQL Server, let's…

    16 条评论
  • What IT Leaders Need to Know About SQL Server Performance

    What IT Leaders Need to Know About SQL Server Performance

    IT leaders have a lot on their plates! Budgets, staffing, security, uptime, and keeping everything running smoothly…

    4 条评论
  • Mistakes IT Shops Without a DBA Make

    Mistakes IT Shops Without a DBA Make

    We get it. There’s no budget for a DBA on staff, or even a contract support firm like Dallas DBAs.

    10 条评论
  • SQL Server Time Bombs!

    SQL Server Time Bombs!

    Common Reasons for Emergency SQL calls If you are a production DBA (or Accidental prod DBA) you’ve gotten that frantic…

    10 条评论
  • Careers in Data

    Careers in Data

    Some of the highest paying careers in the field of "data". These are not necessarily specific to SQL Server, or even…

    1 条评论
  • High Availability for Databases: Pros, Cons, and Hidden Costs

    High Availability for Databases: Pros, Cons, and Hidden Costs

    High Availability is a must for critical databases, but is it right for your business? Every company says they want…

    10 条评论
  • Managing Stress for IT Workers

    Managing Stress for IT Workers

    IT professionals often face demanding schedules, tight deadlines, and the pressure of maintaining critical systems…

    3 条评论
  • Critical Thinking is Not Going Away

    Critical Thinking is Not Going Away

    In the ever-changing world of IT, tools and technologies evolve at a breakneck pace. New solutions are introduced…

    5 条评论
  • Staying Focused in 2025

    Staying Focused in 2025

    Top 3 Ways to Stay Focused and Make Progress as an IT Professional System administrators, developers and DBAs are no…

社区洞察

其他会员也浏览了