Product has been added to your cart.

Blog

New Amazon X2iedn High-Memory Instances

Brent Ozar
Amazon Web Services (AWS)
4 Comments

Amazon just announced new X2iedn instance types for Amazon RDS SQL Server. They’re a sweet deal for people who want a high-performance managed database, with really fast cores and more memory per core than you can get in Azure’s managed SQL offerings.

Say your workload needs 8 CPU cores. Here’s a quick comparison between a few instance types, all priced with SQL Server Standard Edition licensing included:

So if you want >64GB RAM, but you want to stay with a low core count, you can.

However, if you want to use as much of that memory as possible, there’s going to be a price to pay. SQL Server Standard Edition is limited to around 128GB of memory for the buffer pool, although it can go over that amount for stuff – that’s beyond the scope of this blog post. If you’re looking to upgrade to Enterprise to leverage every last byte:

Or, another way to think of your 8-core options:

  • 64GB: $35/GB
  • 128GB: $30/GB
  • 256GB: $19/GB

The more you buy, the more you save. Go figure. And of course, along those lines, you can save big on those by using Amazon’s Reserved Instances, as I discuss in my Running SQL Server in AWS & Azure class.

Does this mean Amazon RDS SQL Server is better than Azure SQL Managed Instances? Not at all: it’s just one of a bunch of attributes to consider. For example, even today, Amazon still doesn’t offer SQL Server 2022 in RDS – although I do have to point out that it’s been almost a year, and SQL Server 2022 still isn’t ready.

[Video] Office Hours at Svínafellsjökull Glacier in Iceland

Brent Ozar
Videos
0

Got Azure SQL DB and SQL Server questions? Post them at https://pollgab.com/room/brento and I’ll answer the top-voted ones. This episode comes to you from the most hike-friendly glacier in Iceland, one where you can park your car and step onto the glacier 15-20 minutes later after an easy walk. (I’m not standing on the glacier in this – I’m in front of it.)

Here’s what we covered today:

  • 00:00 Start
  • 01:32 Kenny Blankenship: What is your opinion of the Microsoft Azure AG deployment experience that walks you through creating the cluster, VM’s, and availability group for SQL 2022?
  • 02:43 Mrs. Watanabe: What is your opinion of using Windows S2D as storage host for Azure SQL VM data and TLog files? Do you run into perf issues with this?
  • 03:21 sandimschuh : Where is the cut-off point in SQL Server where a nested loop with key look up becomes more expensive than a scan? Or to put it another way, what criterion does SQL Server use to decide that a scan is cheaper?
  • 05:21 CuriousDBA: Hello Brent, Given server A and server B What is the best solution in your opinion to archive data from server A to server B and purge data on server A?
  • 05:55 DerekSQLDBA: Hi Brent – We have some dev servers in availability groups with a lot of databases (200-250+) so we need a lot of worker threads and are paying for 16 cores for each server, but overall usage is under 10%. Is there any way to deal with the worker thread bottleneck for cheaper?
  • 07:22 Mrs. Watanabe: What is your opinion of using network storage such as Azure NetApp and Silk to bypass azure disk throughput limits for Azure SQL VM?
  • 07:58 Clippy: What is your opinion of the Azure SQL VM Best Practices Analyzer (part of SQL Agent extension)? Is this comparable in quality to sp_blitz?

12 Office Hours Questions I Don’t Have Answers For

Brent Ozar
SQL Server
27 Comments

Hey, you! You look smart. Here are highly-upvoted questions that fellow readers submitted at https://pollgab.com/room/brento that I don’t have answers for. If you’d like to jump into the comments, you can reference questions by number with your answers. Time to show off your brains!

  1. Kansas4444 asks: Hi Brent, do you have any advice on calculating checksum for an entire row on SQL Server ? (Brent says: I’ve never had to do this, so no idea.)
  2. Margaret asks: Hi Brent — we are in the process of doing a lot of VLF reductions across the farm and I wanted to ask if issuing a CHECKPOINT command before trying to shrink the log was anything that might cause problems going forward?
  3. Stanley Burrell: What is your opinion of Microsoft PerfInsights for load testing prospective new SQL Server VMs? (Brent says: never even heard of it.)
  4. BamDBA asks: When using Transparent Data Encryption for databases in Availability Groups in a busy environment, are there any tips/best practices to increase performance?
  5. I’m_Trying asks: Are you aware of any way to implement multifactor authentication for versions previous to SQL 2022 for on prem databases?
  6. LCK_Tess_Monster: RE: MAXDOP=0 & Mastering Indexing – before moving to Azure, I saw intensive queries cause blocking to its internal threads in ActMon without causing problems for others. Under what circumstances could blocking internal to a SPID slow down IX creation or was it a false positive?
  7. Ricardo: To refresh our SSRS server I want to move from backup /restore to Replication but am in 2 minds whether to replicate into the current database or a new one? A new one would be cleaner but we seem to have many reports with environmental requirements like hard-coded database names. (Brent says: this is the kind of thing I’d do a consulting engagement to really understand the requirements, available resources, budget, etc, but maybe some of y’all have free answers for them.)
  8. DGW in OKC: What is your opinion of Azure AD? Does it work well with on-prem SQL Server security?
  9. hamburger sandwich asks: How do you automate windows server OS updates for SQL Server high availability group clusters? (Brent says: this question comes up a lot, and I haven’t ever seen a good answer that didn’t involve possibly rolling back open transactions or backups, but maybe y’all have seen something I haven’t.)
  10. Junior DBA wannabe asks: How would you script the creation of a database and add it to AG replication from a single node? Same for app login creation
  11. HotPotatoJuggler asks: DBA says SQL Server DB wait states are negligable. Webserver has resource, and is barely moving the dial. Connection from webserver to DB <8ms. Running query <2ms. 300ms for the .net adapter to fill for each call (x20). AWS just old instance – do they slow them down? 🙂 Any clues? (Brent says: I’m not even sure what the hell this is asking.)
  12. Frozt asks: Hi Brent, Do you have any gotcha’s we got to be aware regarding Always Encrypted with Enclaves? Also is this better than application level encryption?

Curious to see what y’all say!

Announcing SQLBits 2024: 19-23 March, Near London

Brent Ozar
SQLBits
3 Comments

I hate picking favorites, but I gotta be honest: SQLBits is my favorite conference.

Sure, like any conference, it has workshops, training days, and even a free community day, but the icing on the crumpet is the attendee party. Every year, Bits goes all out to pick a theme and throw a monster Friday night party where people dress up, take part in games, and just blow off steam.

When is SQLBits 2024? The conference will be 19-23 March, 2024.

What is the SQLBits theme this year? The 2024 theme is Aviation. It ties into the Farnborough location, which is home of the Farnborough Airshow. Cue up your Top Gun, Airplane!, and Snakes on a Plane ideas for costumes…

Where is SQLBits 2024? The conference will be held at Farnborough Exhibition & Conference Center within an hour southwest of London. You can fly into London, and then hop a train to Farnborough where there are plenty of affordable hotels.

How much is SQLBits 2024? There will be a limited number of early bird tickets available at a discount. Bits will announce the pricing soon, and from the moment pricing is announced, we’ll have 5-6 weeks before ticket sales go online. That gives you a few weeks to convince your managers to spring for a ticket, and then when sales goes live, MOVE FAST to secure one of the limited discounted seats. (It isn’t like other conferences that sell unlimited tickets based on date ranges.)

Is SQLBits 2024 in-person? The event will be hybrid – you can attend either in-person or online.

When is the SQLBits call for speakers? It opens 25 Sept, and concludes 1st November. Check out the SQLBits Speakers page for more details.

The registration, call for speakers, and agenda aren’t available just yet, but now’s a good time to block out your calendar just in case. You never know when your boss will actually say yes! I’ve blocked it out on my calendar already, and I’m excited to get back to see everybody again.

[Video] Office Hours at Breiðamerkursandur, Diamond Beach

Brent Ozar
Videos
1 Comment

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 overlooking Breiðamerkursandur, Diamond Beach, one of the most Instagrammed places in Iceland. Here’s a shot from a prior trip when I climbed up on one of the icebergs:

This time around, I’m staying dry so I can answer your tech questions:

Here’s what we covered:

  • 00:00 Start
  • 01:40 Ozan: Hi Brent, can you recommend a book or resources to learn more about the internals of SQL Server in depth (SQL OS, block allocations, write and read logic on memory and disk). Does aligned block allocation (8*8K=64K) on storage still matters on SSD? Thanks
  • 03:57 JustWondering: Have vendor app that selects all fields of a 2TB table.Plan says it’s doing a CI seek.Table has a non seq GUID as the cluster key, includes an ntext field (XML data) + 5 other GUID fields.Qry runs 10000x each cycle.  Where do I start to improve performance since I can’t tune it?
  • 05:51 Balaji Ram: Is it possible to implement SQL FCI using Windows Storage Replication in AWS? I tried but couldn’t add gp3 disk to Cluster. Cannot find any useful documentation. Can use AWS-FSX but storage is very expensive – $4.5 for 1 MB/Sec per month. Any blog/book/documentation pls? Thanks!
  • 06:45 Faysal Qureshi: Do agree with Chat GPT’s ranking of 10 worst database management systems? 10. Microsoft Access 9. FileMaker 8. Oracle 7. MongoDB 6. MySQL 5. SQLite 4. dBASE 3. Paradox 2. FoxPro 1. Lotus Approach
  • 07:23 Henry Jones Jr.: Which is more popular : Office Hours Twitch Stream or Office Hours Youtube?  Which has more fortune and glory?
  • 07:54 Begrudgingly-Learning-MySQL :-(: I hate even asking this here, but your one of the best authorities in the database space in my opinion.  Do you have any recommended courses/classes that can help someone, begrudgingly transitioning from SQL Server to MySQL, write and tune queries?
  • 08:39 SQL_Stormlight: What approach do recommend to troubleshoot this error when you have 1000s of dbs and no idea which query is causing the issue: “The query processor ran out of internal resources and could not produce a query plan.” This alert has been raised 100s of times over the last week.
  • 09:35 GermanDBA: Hi Brent, What are some things to consider when weighing Transactional Replication vs ETL tools for keeping a copy of various table columns up to date on a secondary server?
  • 10:32 Mr. SqlSeeks: I have been tasked to determine how our back-end processes perform with 2x and 3x data volume.  Do you have any suggestions as to the most efficient way to essentially copy/paste from one instance to another for the purpose of doubling the data volume?
  • 12:20 YY: Hi Brent, For a typical OLTP application, What is your recommendation on: How many filegroups? How may files per filegoup? What table types in each filegoup?
  • 14:41 Red U: What are the top issues your clients run into when lifting and shifting SQL server to Azure SQL VM?
  • 15:30 PrayEveryMorningToSQLGod: With a database that as a table of files (aka images) that is ~80% of it’s size (around 1.6TB). Do you recomend moving it to FILESTREAM ( tech looks dead ) or just leave it alone and pray to SQL Gods? Trying to improve: Backup Performance and Performance on getting the imagens
  • 16:23 LostInSpace: How can I determine when to use Optimize for Ad hoc workloads?

“Oops, I Lost My Indexes in Azure SQL DB.”

Brent Ozar
Azure SQL DB, Backup and Recovery
10 Comments

I got an interesting request for consulting, and I’m going to paraphrase it:

We were using Azure SQL DB with automatic index tuning enabled for months. Things were going great, but… we just deployed a new version of our code. Our deployment tool made the database schema match our source control, which… dropped the indexes Azure had created. How do we get them back?

First, restore a pre-deployment backup somewhere else. The indexes will still be present in that other database. Don’t overwrite the production database – I need to mention that in case SQL Server users are reading this to solve a similar problem.

Then, decide whether you want all of the old indexes, or only some.

If you want all indexes back, use a tool like sp_BlitzIndex @Mode = 2 to script out all of the index definitions, or Redgate Schema Compare or SQL Server Data Tools to compare the prod & backup databases. This lets you quickly put all of the indexes back, in the shortest amount of time, with the least amount of thought. However, it’ll also include indexes that may no longer be useful to you with your new version of app code.

If you only want some of the indexes back, keep that backup around – but wait to apply indexes until one of two things happens:

  • A query shows up on your top-10 most resource intensive queries from tools like sp_BlitzCache or Query Store, or
  • A user complains about a query

In either case, go into Query Store and try to find the prior versions of that query plan. Look to see which indexes it used to use, and then pull those index definitions from your pre-deployment backup. (Query Store won’t include the index definitions.)

If you can’t find prior plans in Query Store, you’ll need to review the tables used in the query, then review the indexes on those tables in the pre-deployment backup. Pluck the ones out that you think will be most useful, and then apply those in production.

After the emergency’s over, it’s time to think about how you’re going to prevent this problem the next time you do a deployment.

Change your deployment tooling to only make constructive changes, not destructive. If an index already exists, leave it in place. Frankly, I think this is the smart long-term thing to do regardless of how your deployments are done, or where your database is hosted. This same problem has existed for ISVs hosting on-premises applications for decades because customers will apply their own indexes just like Azure does.

Or, source control Azure’s changes as they happen. You can log Azure’s telemetry somewhere (or poll it on demand) and then build a process to apply those changes to a dev database, and open pull requests with Azure’s changes. That’s an easy sentence for me to write, but a challenging process to build. (I’ve certainly never bothered.)

What’s Faster: IN or OR? Columnstore Edition

Brent Ozar
Columnstore Indexes, Execution Plans
16 Comments

Pinal Dave recently ignited a storm of controversy when he quizzed readers about which one of these would be faster on AdventureWorks2019:

I laughed so hard when I saw the storm of responses on Twitter. People sure do get passionate about this kind of thing. If you ever wanna witness patience and generosity in action, look at Pinal’s responses to this tweet.

While the answer for the default AdventureWorks2019 database is that they perform identically, I wanted to extend the quiz a little.

Does columnstore change the answer?

Folks who’ve been through my Fundamentals of Columnstore class will remember that queries are performed differently when a columnstore index is involved – sometimes better, sometimes worse. (Usually better, though.)

We’ll start by dropping the existing indexes FKs, and triggers, then creating a clustered columnstore index on Sales.SalesOrderHeader, then running the two queries:

And then compare the logical reads done by both queries:

Both queries do 731 logical reads. (For clarity, I’ve removed a lot of the irrelevant IO stats.)

That means the queries are performing identically in terms of reads, and their execution plans both look the same too:

Both plans perform equally.

They both suck.

They’re both retrieving all of the rows in SalesOrderHeader – even though no rows actually match our query! If you test this against the default rowstore indexes in AdventureWorks2019, you’re going to come away with the conclusion that columnstore is worse than rowstore – and frankly, for many kinds of queries, that’s true.

Does SQL Server 2022 compat level fix that?

AdventureWorks2019 ships in compat level 150 (2019). Let’s switch over to 160 (2022), and try that again. Here are the new logical reads:

BOTH QUERIES DO ZERO READS. ZERO.

That’s because SQL Server 2022 (in 2022 compat) is able to skip the segments altogether. Note the line “segment skipped 1”, as opposed to the 2019 version which actually read the data.

How? Well, the 2022 execution plans look different than the 2019 versions:

These new simpler plans don’t have a filter because the data’s already being filtered earlier in the plan, at the index scan level. If you hover your mouse over the Columnstore Index Scan operators, you’ll see a new Predicate option that we didn’t have before:

This is called “predicate pushdown”, and it’s something that Microsoft has been gradually adding in to more and more columnstore plans over the years. They’ve been pretty good about tracking what’s new in columnstore with each version, but I gotta confess that I actually thought integer predicate pushdowns were already happening, hahaha. It might be a regression in 2022, but frankly, I don’t have multiple machines handy with different versions to test it. (I’m finishing up my Norway vacation, getting ready to fly back to the US, but Pinal’s blog post tickled me so much that I had to bang this post out quickly.)

So in summary, when you’re using columnstore indexes, what’s faster, IN or OR? The answer: 160 compatibility level.

[Video] Office Hours at Jökulsárlón Glacial Lagoon

Brent Ozar
Videos
2 Comments

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?

[Video] Office Hours at the Blue Lagoon

Brent Ozar
Videos
3 Comments

Road trip time! I’m recovering from jet lag after flying over to Iceland, so I went through your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

  • 00:00 Start
  • 00:55 Shehroz Sabzwari: What are your pros / cons for running on-prem SQL Server in a VM versus bare metal?
  • 02:04 adba: what are your recommendations for windows performance plan for sql server running on a VM. My private cloud vendor says it is not required to be set to high performance.
  • 03:25 Holy: Can’t you agree that all RDBMS databases are approaching their limits, just as ISAM databases did in the 1980s and 1990s?
  • 05:47 sandimschuh: Hi Brent, is it always safe to change the database setting from Read Committed to RCSI? What kind of queries or query patterns will be negatively affected by this change?
  • 07:04 Mike Conrad: Hi Brent, My team uses system versioned tables a lot. Our ORM, EFcore, sometimes will write updates that don’t change any data, but this does generate a new HISTORY table row, bloating our audit trail. Are instead of triggers a viable way to prevent this?

Who’s Hiring in the Microsoft Data Platform Community? September 2023 Edition

Brent Ozar
Who's Hiring
6 Comments

Is your company hiring for a database position as of September 2023? Do you wanna work with the kinds of people who read this blog? Let’s set up some rapid networking here.

If your company is hiring, leave a comment. The rules:

  • Your comment must include the job title, and either a link to the full job description, or the text of it. It doesn’t have to be a SQL Server DBA job, but it does have to be related to databases. (We get a pretty broad readership here – it can be any database.)
  • An email address to send resumes, or a link to the application process – if I were you, I’d put an email address because you may want to know that applicants are readers here, because they might be more qualified than the applicants you regularly get.
  • Please state the location and include REMOTE and/or VISA when that sort of candidate is welcome. When remote work is not an option, include ONSITE.
  • Please only post if you personally are part of the hiring company—no recruiting firms or job boards. Only one post per company. If it isn’t a household name, please explain what your company does.
  • Commenters: please don’t reply to job posts to complain about something. It’s off topic here.
  • Readers: please only email if you are personally interested in the job.

If your comment isn’t relevant or smells fishy, I’ll delete it. If you have questions about why your comment got deleted, or how to maximize the effectiveness of your comment, contact me.

Each month, I publish a new post in the Who’s Hiring category here so y’all can get the latest opportunities.

Why Australian Azure SQL DBs Went Down for 8+ Hours

Brent Ozar
Azure SQL DB
5 Comments

On August 30, Azure’s Australia East data center had a big problem, affecting customers like Bank of Queensland and Jetstar. Here’s the timeline:

  • 30 August 2023 @ 08:41 – Voltage sag occurred on utility power line
  • 30 August 2023 @ 08:43 – Five chillers failed to restart
  • 30 August 2023 @ 10:30 – Storage and SQL alerted by monitors about failure rates
  • 30 August 2023 @ 10:57 – Cosmos DB Initial impact detected via monitoring
  • 30 August 2023 @ 11:15 – Attempts to stabilize the five chillers were unsuccessful after multiple chiller restarts
  • 30 August 2023 @ 11:34 – Decision was made to shutdown infrastructure in the two affected data halls
  • 30 August 2023 @ 20:29 – All but two SQL nodes recovered
  • 31 August 2023 @ 04:04 – Restoration of Cosmos DB accounts to Australia East initiated
  • 31 August 2023 @ 04:43 – Final Cosmos DB cluster recovered, restoring all traffic for accounts that were not failed over
  • 31 August 2023 @ 08:45 – All external customer accounts back online and operating from Australia

Note that 11:34, the decision was made to shut down infrastructure without Microsoft failing your databases over elsewhere. If you were an Azure SQL DB or Cosmos DB user, and you weren’t paying for replicas in another data center, it was up to you to follow Microsoft’s disaster recovery guidance.

Controversial opinion: I actually love that and I think it’s great.

I see a lot of Azure SQL DB users make the mistake of assuming that Azure includes disaster recovery, but it does not. It’s on you, and as a result, you save money. (Same thing in AWS Aurora PostgreSQL.) I’m sure there are plenty of small business databases that don’t need disaster recovery within a day or two. Heck, even Bank of Queensland probably has some databases that fit into that category, although… probably not as many as actually went down, hahaha.

There’s a problem with that, though: Microsoft didn’t notify affected customers about which of their databases were down, or that the customers should start their DR processes. Microsoft couldn’t notify customers because … they didn’t know who those customers were. Microsoft’s Azure status history doesn’t let you easily link to a single event, but if you expand the outage on 30 Aug, the preliminary writeup is really detailed, and explains why they were flying blind:

From a SQL perspective… Some databases may have been completely unavailable, some would have experienced intermittent connectivity issues, and some databases would have been fully available. This uneven impact profile for databases in the degraded ring, meant that it was difficult to summarize which customers were still impacted, which continued to present a challenge throughout the incident.

Boy, I have been there. When multiple databases and servers go down, one of the first thing management wants to know is, “Which specific apps are down?” When you can’t answer that question, it makes management pretty nervous, and adds even more stress to the situation.

As we attempted to migrate databases out of the degraded ring, SQL did not have well tested tools on hand that were built to move databases when the source ring was in degraded health scenario. Soon this became our largest impediment to mitigating impact.

It might be tempting to point and say, “Well, Microsoft, you should have that” – and they should – but I don’t see a lot of shops with well-tested automated DR failover tools.

I’ve long said that Azure SQL DB does a better job of database administration than not having a DBA altogether, and this is a good example. Customers who didn’t have a DBA wouldn’t have been any better off managing their own DR in a situation like this, and frankly, most customers who do have a DBA wouldn’t have been better off either. (If you smugly think you’d be fine, point to your current list of production servers & databases, and prove that every single database you have is also synced with DR. Go ahead. I’ll wait.)

Because every DB moved required manual mitigation via scripts, it seriously undermined our ability to move fast even once impacted DBs were identified, and DB moves were scheduled.

Elsewhere in the post, they mention that over 250,000 databases were involved in just one of these troubled rings of databases alone. You just can’t manually do anything with 250,000 databases, so I can only imagine how stressful it was to try to write the automation code under fire. Props to the folks working that night.

Overall, this kind of incident – and how Microsoft responded to it afterwards – is why I think that if you don’t have a DBA, you could do a lot worse than relying on Microsoft, Amazon, and Google doing that job for you instead. Platform-as-a-Service lets someone else stress out about the outage, troubleshoot it as quickly as they can, then build better processes to shorten the next outage.

[Video] Office Hours: Overly Caffeinated Edition

Brent Ozar
Videos
1 Comment

After consuming waaaay too much coffee, I went through your top-voted questions from https://pollgab.com/room/brento:

Here’s what we covered:

  • 00:00 Start
  • 03:53 It’s ‘a me: Is there a feature in SQL that Microsoft abandoned that you wish they had perfected?
  • 05:43 TheEveryDayDBA: My friend asks, he is using sp_blitzIndex on a OLAP db workload. Quite a lot of Indexaphobia: High Value Missing Index are trigger for a table, that contains a Clustered ColumnStore index on it. Does sp_blitzIndex look for ColomStore indexes when it runs?
  • 07:01 RenegadeLarsen: Hi Brent Any plans on going to SQLBits next year? Kind Regards RenegadeLarsen
  • 08:06 BrentIsMyHero: Hi Brent! You are my hero! 🙂 Can you please recommend your top 3 books for a Production DBA role. Thanks and have a great day!
  • 09:28 ascme: I’ve been a mssql DBA for 15+ years and have dabbled in other platforms. My company is moving most DB services to IBM LUW and MariaDB. I am half interested in learning more about them, but not sure that is a career building positive for me. What would you do?
  • 10:30 Ozan: Hi Brent, you recommend performing snapshot backups if the database size is greater than 1 TB. Why not also for databases with smaller sizes?
  • 11:18 Miles: Hi Brent,How do you handle egoistic managers or toxic peers who take credit for your work, don’t appreciate it, and hinder your promotion despite hard work? Have you faced such situations? Can you share your experiences and advice on dealing with them?
  • 13:16 unspoiled: I had an issue with excessive blockings, I tracked this down to be related to compile locks of which I have never heard of before. how do compile locks cause a wide spread blocking issues and how could it be prevented?
  • 15:41 Blue K: Is Azure SQL any better/worse for implementing one DB per customer than traditional SQL VM?
  • 16:59 Kaysar R: You mentioned using snapshot backup over native SQL backup for large terabyte DB’s. Do you recommend first upgrading to SQL 2022 before attempting snapshot backups?
  • 17:41 Chips Ahoy!: In a recent office hours you recommended that XML and JSON should be stored in the DB as a blob. You are also known to say “don’t put data in the DB unless there will be joins or filters on it” Seems contradictory. Did I misunderstand something?
  • 18:31 Red U: Have you seen anyone successfully automate the scale up / scale down process for Azure SQL VM? What were the lessons learned?
  • 21:04 Izzy G: What are the top issues your clients run into when querying SQL Server over a WAN instead of a LAN?
  • 21:32 It’s ‘a me: Hi Brent. You talk about backups a lot, but I’ve never heard you mention backing up to URL? Is there a reason you don’t mention it as an option? Drawbacks, reliability etc
  • 23:15 Faroek: Is the query store purely a logging feature, or does SQL Server also use it to re-use plans or check for plans to help with query executions?
  • 24:06 Grubsnik: In a recent office hours, you mentioned that SQL server has become much better a prioritizing ram over disk for tempdb in the last 5-10 years. Can you tell which version specifically? We’ve been running TempDB on ramdrives and I’m wondering if that is an antipattern for SQL2016
  • 24:56 Doug E: What are your thoughts on Microsoft purchasing Activision? Does this permanently hurt the competition?
  • 25:47 sandimschuh: What is a good way to identify queries that pose the risk of a sudden change in execution plan (switch from NL to a scan or visa verse)? The problem occurs with queries that use at least one index with heterogeneously distributed data (bad estimates) and nested subqueries.
  • 27:38 Lance Boil: What is your favorite cruise ship line and destination?

[Video] Office Hours: All About Miles Edition

Brent Ozar
Videos
6 Comments

Today’s webcast featured an awful lot of questions from one particular person who was super-active at https://pollgab.com/room/brento:

Here’s what we covered:

  • 00:00 Start
  • 00:03 Miles: Hi Brent, I am aware of “sp_statement” completed and sp_batch_completed events in trace and extended events. But, what is the use of rpc_completed event? How this event will be helpful while collecting traces and when should we capture this event? please explain.
  • 01:37 Mike: Azure SQL Managed Instance has uptime SLA of 99.99%. If we are okay with maximum of 4.5 minutes of downtime per month (0.01%) – that means we do not need to add any secondary replicas for HA at all, and single instance is sufficient?
  • 05:16 Miles: Hi Brent, we have a dev team always say, its the SQL problem. Eg:DB locks is the root cause of app API slowness, why SQL Server got restarted,we can’t afford it. How to handle such teams? How we can do a much better job?
  • 07:16 Pradeep M.: When log shipping gets behind by 24+ hours do you try to determine why it got behind or focus on improving network bandwidth so that it doesn’t get behind in the future?
  • 08:16 Miles: Hi Brent, what is process involved in base-lining a query performance. I really want to know how to do it and why it is important to do it. Please explain with an example.
  • 08:51 JimLic: I have seen performance issues when using more than 3 CTEs stacked on top of each other. Is there some documentation that says “don’t use more than 3 CTEs in a single query”. Does “It depends” apply here? What have you seen with CTEs?
  • 10:45 Miles: Hi Brent, Any piece of advice for new DBA’s with 5+ years of experience to increase their credibility and visibility?
  • 13:25 Rapid Dragon: What’s your opinion of SQL stretch DB? Do and of your clients use it / like it?
  • 13:57 Miles: Hi Brent, How to find a right mentor? How to approach them? Difference between teacher & mentor? If one agrees to be a mentor what things mentee should be prepared for?If mentor charges more, find another? Have you faced situations where mentor affordability was an issue? Thanks!
  • 18:15 Miles: Hi Brent, based on your work experience, what are the top 5 essential qualities a good DBA should have?
  • 20:16 who knows: I have a lot of old c# apps that are forcing SQL transactions to go ReadCommitted, is this preventing my SQL server from using row versioning? I have RCSI enabled in my databases.
  • 21:26 Hjordis: What is your opinion of SQL Trace Flag 834 – “Use large-page allocations for the buffer pool, columnstore, and in-memory tables”?
  • 23:03 zlobnyfar: Hello Brent! If the money is not a problem – what is the best HA and DR solutions you can recommend. A few small conditions: SQL Server 2017 or 2019 ENT Edition, NO downtime and NO Data loss. Thank you for comprehensive answer!
  • 24:11 Julie Berthelsen: What are the pros / cons of implementing first responder kit in TSQL vs PowerShell? Microsoft SQL support tools tend to prefer PowerShell for some reason.
  • 25:30 Ciaran O: Microsoft recently mentioned potential shortage of GPUs as a risk factor to it’s business. Should Microsoft do like Apple and start making it’s own chips?
  • 28:08 Drax: How do you know if your Azure SQL VM is too overprovisioned (i.e. paying for unused resources)?
  • 29:04 Tony Feuz: Would you recommend turning on TDE? We are facing some changes for SOC2 and we are wondering if we should go this far. Our backups are currently encrypted and I did read your blog post – but the post does not seem to specify if you are for or against it…. Thanks!

Free Webcast: Avoiding Deadlocks by Tuning Queries

Brent Ozar
Conferences and Classes
3 Comments

You’ve been getting more and more deadlock errors, and users are starting to complain. You’re wondering if your queries are the root cause, and if so, what you should do to fix ’em.

In a fast-paced session, I’ll explain the 3 causes for deadlocks, and the 3 ways to get relief. We’ll use 2 demo queries to show unrealistically simple deadlocks, plus more real-world deadlocks that are harder to solve.

Register here to attend free on September 21. If you can’t make it live, the recording link will be emailed to the registrants as well. See you there!

[Video] Office Hours: I Need Your Help with These

Brent Ozar
Videos
2 Comments

Today, I need your help with some of the top-voted questions from https://pollgab.com/room/brento. Chime in in the comments:

  • 00:00 Start
  • 00:52 Accidental DBA: Hello Brent. Thank you for keeping your Q& A sessions entertains and informative. My question is non-technical…Have you ever said this to your client? You keep using that word. I don’t think it means what you think it means. : )
  • 02:04 Peter: Hi Brent, I would like to cobble some in-house SQL training from your fundamental and mastering courses I have attended. Are you okay with this? And if so, what credits do I need to attribute to you?
  • 02:45 JimLic: My employer is moving from SQL Server to AWS (postgres/aurora/glue/lambda). When would it be an appropriate time for a seasoned MS SQL Server DBA to jump ship? [Old dog, new trick]
  • 07:55 Anatole Taubman: What are your thoughts on office vs remote vs hybrid work requirements for SQL DBA’s? Has Covid forever changed this requirement?
  • 10:10 Fundamentals Newb: Hey Brent, I get a “Many Duplicate Plans” warning when I run Sp_Blitzcache (Around 55K plans in cache). We have 1 dev, his resources are spent. How do I determine if forcing parameterization is the way to go versus forcing plans via Query Store? How would you test this?
  • 11:19 Miles: Hi Brent, How to tell my SQL Server needs more memory? what factors or metrics do we need to measure and what are those thresholds we should be looking at?
  • 11:48 Miles: Hi Brent, How can we determine when a query can’t be further tuned? When do you decide that the query needs more hardware resources? Where do we stop tuning a query?
  • 13:27 BJ: Hi Brent, what do you thing about Entity Framework CRUD (single insert/update/delete) operations as solution for high performance OLTP database with large number of transactions?
  • 15:09 Confused Dev: Hi Brent, my friend says, a query is faster if its only using tables from 1 DB rather than 2 DBs on the same server. Is there any truth to that?
  • 16:03 Miles: Hi Brent, we have usage of MSDTC transactions inside our SQL Server for a vendor specific db.These txns sometimes cause heavy blocking. My question is, why do people use distributed transactions and what are the use cases of MSDTC based on your experience? Can we avoid them?
  • 17:38 Don’t Bother Asking (DBA): Hello Brent. Today, my requested me do something sessions that were stuck in rollback. As far as I know, there is nothing that can be done, we must all wait for the rollback/killed session to complete. Is there any new functionality in SQL 2022 for long-running rewind operations?
  • 19:00 Miles: Hi Brent,When do implicit data type conversions become costly, even for bookmark lookups/RID lookups? Our vendor database lacks control over design and data types. How to convince the app team about scaling issues with growing data?
  • 20:23 DadJokerDetroit: If a large number of DBAs go to a restaurant (at a conference for example), should they be allowed to join tables?
  • 20:55 Ramesh: Is there a good way to identify which SQL clients are using older outdated SQL driver against SQL 2019?
  • 21:38 MightBeAStupidQuestion: Hey Brent! I know you typically say to store files in a file system. With the popularity of data lakes would you feel comfortable storing files here too?
  • 24:23 Don’t Bother Asking (DBA): Is it preferable to have a DBA team for all sorts of databases, such as SQL Server, Oracle, MongoDB, Cassandra, and so on, or is it better to have distinct DBA teams for each? My friend posed this question since he intends to combine all DB support teams into a single DBA team.
  • 24:24 Don’t Bother Asking (DBA): Is it preferable to have a DBA team for all sorts of databases, such as SQL Server, Oracle, MongoDB, Cassandra, and so on, or is it better to have distinct DBA teams for each? My friend posed this question since he intends to combine all DB support teams into a single DBA team.
  • 25:47 Manto: Do you prefer to use Azure ephemeral disk for local tempdb or disk read cache in Azure SQL VM?
  • 26:20 Manto: For performance reasons, does sector size matter for Azure SQL VM ephemeral drive used for TempDB? If so, what is your recommendation?
  • 27:32 Don’t Bother Asking (DBA): Sorry for my typo on previous question, Have you had any experience with Liquibase as a tool for CI/CD for databases? or have any other alternative solution for this purpose on SQL Server?
  • 27:56 Philip: Hi Brent – VM Snapshots and Database Backups (Full/Differential/Transaction Log) – how do you see the best setup for a stand-alone SQL VM where the Infrastructure team want to do VM Snapshots as well as Database Backups?

[Video] Office Hours: Really Long (and Really Short) Answers

Brent Ozar
Videos
4 Comments

Today’s batch of questions from https://pollgab.com/room/brento requires some really long answers, and some are right to the point.

  • 00:00 Start
  • 02:13 Eh? Aye…: As DBAs, how can we best prepare for the AI world in terms of data? Should we start with AI as a concept fundamentals, or what ua the ‘data type’ we should just jump right in and start to learn to work with? E.g. if we were into mapping, we would look to learn geospatial.Ta much
  • 03:37 Tim Gitchel: What is the best method for configuring Linux or K8S to connect to FCI with MultiSubnetFailover=True? I get mixed results when adding this to the connection string and am not sure of all the moving parts and options.
  • 05:04 Jorge: What are your pros / cons of deleting old backup files before vs after OLA SQL backup for multi TB db backups?
  • 06:40 Bekim Fehmiu: Do you recommend page or row compression for new tables in SQL Server on prem?
  • 07:31 Miles: Hi Brent, App db grew suddenly from 3TB to 5TB, filling 90% disk. How to size data&log drives for such sudden growths? What proactive measures can we take to monitor&plan for sufficient space? what questions to ask the app team to avoid unnecessary downtime’s? Thank you.
  • 09:22 Hjordis: Is it ok to delete from a CTE (instead of a view) when doing fast ordered deletes?
  • 10:46 Don’t Bother Asking (DBA): Hi, Do you advocate using storage replication as a data resiliency/availability strategy for SQL Server data/log files? Does replication at the storage level work with SQL Server?
  • 12:34 Miles: Hi Brent,pls explan how RCSI works when readers &writers are involved and when writers & writers are involved? Unable to visualize how all this works. RCSI is enabled on app db.We don’t see any opentran in use tempdb go dbcc opentran go but tempdb size keeps growing and growing.
  • 14:13 RacerX: What’s the fastest you have ever driven and which car was it in?
  • 16:32 Urs Fischer: Have you ever seen any successful large code migrations from SQL Server to PostgreSQL for money saving purposes? What were the lessons learned?
  • 17:44 planCacheObliteration: Batched queries (eg below) flush our plan cache. How should our devs fix this? One query per batch? Batched a different way? (@p1 int,@p2 int,@p3 int… …@p1000 ); delete tbl where id = @p1; select @@rowcount; delete tbl where id = @p2; select @@rowcount; update…
  • 19:26 Confusing DBA: Hi Brent, we ran into performance issues recently. We used steps from How I Use the First Responder Kit to teach our developers to solve those issues, but they only want direct answers / spoon-feed solutions and like to ask random questions. What do you suggest to deal with this?
  • 20:18 Miles: Hi Brent, We have a vendor specific db and we see lot of query hints being used in their code,lots of (NOLOCK) (ROWLOCK) & (UPDLOCK). hints.Is this fine or do we need to ask the developers to remove those hints? In recent times with multiple integrations,this db is becoming slow.
  • 21:38 Mike: In Azure SQL Managed Instance, should I still perform DBCC CheckDB checks?
  • 23:24 Mike: In Azure, SQL Managed Instance (32 vCore, 224 GB RAM, $8K) of comparable size to SQL VM (32 vCore, 256 GB RAM, $11.5K), is cheaper. Isn’t Managed Instance supposed to be more expensive, given that backups, patching and HA are taken care of by Azure?
  • 25:09 Ornella Muti: What are the top use cases for SQL SYNONYM you see in the wild?
  • 26:56 Tim Taylor: What’s it like living in a new house during major renovations? Would you do it again?
  • 27:42 Q-Ent: Hi Brent, Do you have any suggestion on how we can update our DWH in Near Real Time from Staging environment? I use transactional replication right now but the amount of changes is too high and causes performance issues on distribution database.

Updated First Responder Kit and Consultant Toolkit for August 2023

Brent Ozar
First Responder Kit Updates
2 Comments

Turns out y’all actually work over the summer – there are actually cool new features this month! I think I’m actually going to have to record updated sp_BlitzIndex, sp_BlitzLock, and sp_BlitzQueryStore modules for my “How I Use the First Responder Kit” class because these features are pretty awesome.

How I Use the First Responder Kit
Wanna watch me use it? Take the class.

To get the new version:

Consultant Toolkit Changes

I updated it to this month’s First Responder Kit, but no changes to querymanifest.json or the spreadsheet. If you’ve customized those, no changes are necessary this month: just copy your spreadsheet and querymanifest.json into the new release’s folder.

sp_Blitz Changes

  • Improvement: better chance of running to completion when you’re not SA. sp_Blitz has always required sysadmin permissions in the past, and this is our first pass at it, so if you have limited permissions and you run into errors, feel free to file ’em as issues and we’ll see if we can work around it. (#3292, thanks Erik Darling.)
  • Fix: reduce possibility of missing replication in use. (#3316, thanks DavidSchanzer.)

sp_BlitzCache Changes

  • Fix: more accurate estimation of memory grant used overall across multiple executions of a query. (#3313, thanks mdpenguin.)
  • Fix: more accurate percentage of duplicate query plans. We’ve been battling this issue for yeeeears, and I still can’t reproduce the issue in the lab, so I don’t think this is the last chapter on this issue. (#3314, thanks WaldenL.)
  • Fix: skip warnings of ResourceDB queries. (#3315, thanks WaldenL.)
  • Fix: better compatibility with AG secondary replicas. (#3291, thanks mbambion.)

sp_BlitzIndex Changes

  • Enhancements in #3294: in the columnstore visualization section, each rowgroup’s state, trim reason, etc are now shown:

And in that result set, the size of the secondary dictionary (if required) for a column is now included in its size. Here’s the Users.DisplayName column before – note the sizes in the far right:

Now, with the secondary dictionary size included, the DisplayName column’s true size shows up as being dramatically larger because the strings are relatively unique and don’t compress well:

sp_BlitzLock Changes

  • Improvement: if the queries involved in deadlocks are still in the plan cache, sp_BlitzLock now shows an additional result set with their plans. (#3293 and #3311, thanks Erik Darling.)
  • Improvement: when targeting one session, make sure it exists first. (#3306, thanks Erik Darling.)
  • Improvement: faster XML queries. (#3305, thanks Erik Darling.)
  • Fix: time searches are now UTC compatible. (#3307, thanks Erik Darling and Mike Hodgson.)

sp_BlitzQueryStore Changes

  • Improvement: Parameter Sensitive Plan Optimization (PSPO) compatibility! If you search for a single @StoredProcName, we now show all of the plan variants for it. (#3312, thanks sqljared.)

sp_DatabaseRestore Changes

  • Improvement: new @KeepCdc parameter restores that attribute too. (#3289, thanks SQLSlinger.)
  • Fix: compatibility with SQL Server 2022’s new undocumented columns in RESTORE HEADERONLY. (#3190, thanks Razvan Socol.)
  • Fix: incorrect error message in debugging output. (#3320, thanks Greg Dodds.)

For Support

When you have questions about how the tools work, talk with the community in the #FirstResponderKit Slack channel. Be patient: it’s staffed by volunteers with day jobs. If it’s your first time in the community Slack, get started here.

When you find a bug or want something changed, read the contributing.md file.

When you have a question about what the scripts found, first make sure you read the “More Details” URL for any warning you find. We put a lot of work into documentation, and we wouldn’t want someone to yell at you to go read the fine manual. After that, when you’ve still got questions about how something works in SQL Server, post a question at DBA.StackExchange.com and the community (that includes me!) will help. Include exact errors and any applicable screenshots, your SQL Server version number (including the build #), and the version of the tool you’re working with.

[Video] Office Hours in Telluride, Colorado

Brent Ozar
Videos
0

We headed up to the mountains to get away from the Vegas heat. Before the sun rose, I took your top-voted questions from https://pollgab.com/room/brento.

 

Here’s what we covered:

  • 00:00 Start
  • 00:36 Ozan: Hi Brent, how should Volume Snapshot Backups (VSS) be configured correctly that it will not freeze the database’s IO for a couple of seconds or minutes? Best regards from Berlin
  • 02:00 Miles: Hi Brent, As a DBA (5+ years), how do you track your work? Any tool or Excel? for ex: manager asks send me the list of items worked over last 10 months. things like that. Any piece of advice for visibility and credibility, dealing with a biased manager?
  • 03:01 Yullka Mujo: What are the top issues you see with using FileStream in SQL Server?
  • 03:24 Logar The Barbarian: What kind of problems have you dealt with and resolved for clients utilizing Dynamics GP for their ERP systems?
  • 04:43 Pavle Vuisic: Is it ok to update to latest SQL CU when you are four updates behind or should you install one CU then the next, etc?
  • 05:13 Rotnei: Do you have any suggestions for dealing with Azure Alert fatigue? Email rules / folders seems potentially heavy handed.
  • 05:55 Mahlagha Jaberi: What is your opinion of performing Azure SQL VM backups using crash-consistent snapshots?
  • 06:51 Miles: Hi Brent,Dev team is try to DELETE a log table based on co-related sub-query. It read 4TB data and It caused heavy blocking for more than an hour. Eventually had to kill the session. Even Rollback it took almost 30 mins. Db is part of AG. Any advice to make the DELETE’s faster?
  • 07:41 Hjordis: When should you put new tables in a file group other than PRIMARY?
  • 09:01 Ozan: Hi Brent, regarding MAX DOP I get in a 16 CPU VM environment with MAX DOP = 0 (16) better, faster results than with 8 which is the max value recommended by Microsoft. What problems could I face when I stay with 16 or 0? Thanks
  • 09:31 BobbyTables: You mention that while you haven’t done them recently, you’ve heard of potential issues when doing an In Place Upgrade for MS SQL OS. What issues have you seen reported?
  • 10:53 Shahab Hosseini: What is the top batch request per second (sustained) you have seen at your clients? How many DBAs did they have?
  • 11:38 Ingvar Vittfarne: Do you have a recommended naming convention for SQL Agent Job Schedule names?
  • 12:13 Nomzamo M: How do you know if the SQL engine has inlined your user function or not?

Office Hours: Before Heading Out on Vacation

Brent Ozar
Videos
1 Comment

I took a break from packing my bags long enough to answer your top-voted questions from https://pollgab.com/room/brento.

Here’s what we covered:

  • 00:00 Start
  • 02:42 T-man: Hi BO. A friend has multiple Distributed AGs (on separate business division servers) but needs to share ODS data with each of the DAG DBs. Any near-real time architecture ideas so they can stop batch ETL? Adding replication on top of AGs can work, but yuk. Thanks, you da best.
  • 04:11 BigData: Adding a server login during peak times is causing deadlocks with random user sprocs. It appears related to SCH-M on security cache. Server is 1.5tb and security cache is 13+ gb. Can’t recreate issue on small dev server. Are there any gotchas when clearing security cache?
  • 04:59 reluctantly_tolerant : We are using AWS FSxN (managed NetApp ONTAP) provisioned for 512MBs throughput with SQL 2019. The DB files are so fragmented that 2k iSCSI IOPS causes 50k-60k disk IOPS and AWS is throttling to 20k IOPS and ~130MBs throughput. How can I fix this and prevent recurrence?
  • 06:59 Eli: Hi Brent, observed a hash match operator performing 1000x slower when Table B is first in a join, but no spill! Join is many-to-many, with one duplicate value in Table A showing up 40M times, 200M times in Table B. From an internals perspective, what makes B-first so much slower?
  • 07:43 Tim Gitchel: Is there a way to capture any connection to the DAC? I am seeing an error from a nightly Tenable scan that there is another connection preventing access, but I can never seem to catch it. Could something else be causing this same error?
  • 09:01 pete: Why would a bit-wise operation in a table join cause a query to run long?
  • 10:37 Sonakshi Sinha: What did DBA Brent do for physical fitness?
  • 13:47 Golshifteh Farahani: I addition to Ozar training, what are the top things SQL DBA’s should be doing to future proof their careers?
  • 15:32 OliTheDBA: At what point do you think the cons outweigh the pros of automation ? For example, fancy multi-thousand line powershell scripts to snap restore db’s are great and save time, but the up keep and maintenance isn’t enjoyable and can often take hours a week. (Rant)
  • 16:50 Bilal Lashari: What are your top memories from your visits to the Microsoft Redmond campus?
  • 19:28 MooneyFlyer: Hey Brent, kind of a silly question, so roasted if needed. Why STATISTICS IO ON doesn’t give any information about written pages? Is there a way to get this information? I’m trying to give hard evidence to indexing naysayers on the real impact of adding an index to a table.
  • 21:08 Bjorn: Is it ok to use PAGLOCK hint in conjunction with TSQL – DELETE TOP (@SomeVar) FROM dbo.Foo to prevent lock escalation?
  • 21:57 Grimlock : What is the optimal nap time and duration? Have you always followed this?
  • 23:13 Alex M: Do you have any experience/thoughts on using non-MS SQL replication technology with SQL Server, such as Zerto for DR?
  • 25:08 Vangelis Protopappas: What are the top SQL full text issues you see with your clients?
  • 26:00 Demet Evgar: Does the DBA role for non-relational DB’s differ much from that of relational DB’s?
  • 27:57 Pareesh Patel: When doing one DB per customer for multi-tenant, what is the max number of DB’s you would not exceed when using SQL A.G.?
  • 29:18 Stu: At what point do you think a software developer should look for a new job, what are the tell tale signs?
  • 30:53 west: What are your thoughts about Microsoft Fabric and its dwh ability?

The PASS Summit Lineup Is Out and Registration is Open!

Brent Ozar
#SQLPass
5 Comments

This year’s PASS Data Community Summit is November 13-17 in Seattle. (No online version is available this year – you gotta be there in person.)

If you’ve attended in person before, you already know the value: it’s like a family reunion for the Microsoft data community. It’s your chance to see the authors you’ve read so many times, hear their latest lessons, and network with people who can help you the next time your company has hard times. Go register.

If you’ve never attended before, here’s my sales pitch:

  • The pre-conference workshops on Mon/Tues are $595 each, and that’s a steal for a full day of in-depth training on topics that you just won’t find locally.
  • The full 3-day pass is $1,995, and the sessions are organized into learning pathways so you can quickly figure out if there are topics that are a good fit for you
  • Pick your pre-cons and your learning pathway, write them down, and then show your manager, “Here’s what I’m going to learn for $3,185.”

Send that to your manager and see what comes back. Then, the negotiation starts. Here are techniques I’ve heard folks use in order to get their companies to pay for some, if not all, of the conference, hotel, travel, and meal expenses:

  • “I understand that we don’t have a travel budget, so how about the company buys the conference ticket out of the training budget, and I’ll pay for my own travel?”
  • “I understand we only have a limited training & travel budget for the whole team. How about I go, and when I return, every Friday for the next 2 months, I’ll teach a lunch & learn for the rest of the staff, covering one of the sessions I attended?”
  • “I’m going to attend whether the company pays for it or not, and I’m going to take vacation to attend. If it’s important to you that I’m reachable during that time, you can pay for the conference ticket and travel, and I won’t take vacation, and I’ll be reachable via email while I attend the sessions.”
  • “I understand we don’t have any budget, so how about I pay for it, but you don’t make me take vacation time while I attend, and I won’t be reachable?”

I know you might be reading that and going, “There’s no way my company would go for that.”

But they do – it’s just a matter of finding the right combination that works for you and them. Go make it happen, because you need to start making connections and gaining knowledge that will help your career.

(Unfortunately, I won’t be there – Summit is the same week as the Las Vegas F1 night race. I’m super excited to see F1 cars racing down the Strip!)

Brent Ozar

Hi! I’m Brent Ozar.

I make Microsoft SQL Server go faster. I love teaching, travel, cars, and laughing. I’m based out of Las Vegas. He/him. I teach SQL Server training classes, or if you haven’t got time for the pain, I’m available for consulting too.

Want to advertise here and reach my savvy readers?