[Video] Office Hours: Cabo Balcony Edition

Videos
1 Comment

Post your questions at https://pollgab.com/room/brento and upvote the questions you’d like to see me discuss.

The light balance on this video is all over the place, getting brighter and darker constantly, so you might wanna leave this one playing in the background and just listen to it rather than watch.

Here’s what we covered:

  • 00:00 Introductions
  • 00:32 SQL Crooner: What are the tell-tale signs that your SQL Server has been granted too much memory and the OS not enough?
  • 02:14 Gülnaz: Is ‘SELECT *’ ok or discouraged when used with ‘IF NOT EXISTS…’ ?
  • 03:27 Shingen Yashida: Is table partitioning largely unnecessary since modern SANs can move data to hot / cold storage tiers?
  • 04:14 sp_azuresql: Hi Brent – What is the best cut-over strategy in PROD migration scenarios from SQL Server AG to Azure SQL in terms of blocking the incoming traffic (if any) like disabling logins etc?
  • 05:53 TurnerBurn: Hi Brent. My company moved a database from a physical SQL 2016 SE cluster to a VM; same SQL ver. VM has fewer cores (24 vs 32) but processing power on the VM is better & memory is the same. I now see many, more deadlocks reported from sp_BlitzLock on the VM vs the physical box.
  • 07:38 Noam: On a scale of 1-10, how would you rate the online Microsoft SQL Server Documentation and the online PostgreSQL documentation?
  • 09:20 Piotr Rasputin: What is your opinion of DP-900 Azure Data Fundamentals certification?
  • 10:42 Q-Ent: Hi Brent, is there any scenario where enabling RCSI on a database that uses the default isolation level to turn your data into trash?
  • 11:30 Prohiller: Hi, can we somehow predict % decrease of overall CPU usage of a server when optimizing a query? Given that we know difference of CPU time of current and optimized query and number of executions per hour/day? I’m looking for additional KPIs when selling optimization to Business.
  • 12:37 MyFriendtheDBA: My friend has a table with 450,000,000 rows on a production server. They want to know the lease disruptive way to index this table.
  • 15:10 Yoshiya: What is your opinion of Azure Synapse? Does it compete well with Databricks and Snowflake?
  • 15:18 SQL Crooner: What are your top SQL best practices that you wish more clients would follow?

[Video] Office Hours: Cabo Pool Edition

Videos
0

You posted SQL Server  questions and upvoted the ones you’d like to see at https://pollgab.com/room/brento, and before the Cabo Clasico finals start for the day, I sat by the pool to talk through ’em:

Here’s what we covered:

  • 00:00 Introductions
  • 00:53 Neil: Does putting up a giant monitor on the wall with SQL Server info like CPU %, Disk activity, blocked sessions, top queries, etc, so non-DBA’s and Executives can see the server status sound like a good idea or is that just inviting trouble?
  • 03:07 Omer: What are the possible causes for sp_whoisactive to show a SPID with a long running INSERT statement (non blocked) but the SPID is in a sleeping state?
  • 03:42 Theo: Does the order of include columns ever matter for a non-clustered index?
  • 03:53 Uncle Leo: What are the pros/cons of using third party SQL backup software over native SQL backup?
  • 05:05 Mehdi: Hi Brent! Auto-update stats is enabled on a database. Does the database need up-to-date statistics with the maintenance plan?
  • 06:07 DBA_Mufasa: Hi Brent! What’s your best approach for tracking tables usage in SQL databases, in order to find tables that haven’t been touched (for eg. no select, updates, or inserts ) for a a defined amount of time. Considering that table statistics get cleared after server restart.
  • 07:49 default blame acceptor: Hi Brent, could you advise a KPI that I can show not-technical folks to convince them that SQL is not a root cause of the application slowness (when it seems that it is true!) and they should look for a solution somewhere else instead of convincing me to restart SQL’s VM…
  • 09:03 Yousef: How do you determine the optimal auto growth size for a given SQL data file running on NVME SAN?
  • 09:47 Ted Striker: What are the pros/cons of running sp_blitzfirst for longer periods of time (@Seconds = 300)? Any maximum value of @Seconds we should stay under?
  • 10:57 Wilma: What is the optimal monitor size / count / configuration for viewing large query plans in SSMS?
  • 11:25 Alain: Hi Brent, what are the advantages of using VMWare stretched cluster instead of AlwaysOn? Thank you
  • 12:56 TeeJay: We’re expecting to move from on-prem (2017 vintage) to azure VMs soon. What should I benchmark on our old servers that’s useful to assure us that the virtual hardware in azure is (at least) not going to leave us with worse performance than we currently have?
  • 13:56 Ain’t Lyin’: Got a tuning problem with a query inner joining some large tables to a table with 0 rows. Optimizer estimates 1 row anyway and does a lot of work before realizing there are no rows to return. I can’t change the query, how do I get the optimizer to figure this out early?
  • 16:29 Hamid: What is the fastest way to split a comma delimited string in in TSQL 2019 / 2014Compat level DB?
  • 17:56 Kfir: What is the maximum size key/include column you would consider adding to a non-clustered index?
  • 18:58 Thomas Franz: How much memory should I reserver for OS / other stuff when I configure the Max Memory settings for servers with 1 or 2 TB (!) RAM. Following the common recommendations I would end with ~256 GB for OS / 1.792 for SQL which seems a bit too much (most servers have much less RAM).
  • 20:55 CKI: Management wants to create a copy of the production database for reporting purposes. The new “reporting” copy should be refreshed nightly with production data. Production database is on AWS Web Edition 45GB. What is easiest way to do it? Thank you very much!
  • 22:04 Gerald Krobath: Is it recommended to install CU updates for MSSQL 2016/2019 automatically via the regular updates or should this always be done manually?
  • 23:47 Sir Galahad: Has OPTION (FAST N) query hint ever got you across the finish line?
  • 23:59 Buck: Have you noticed the annoying issue in SSMS 18.11.1 where it does not maintain your query plan scroll position when you switch off that tab over to another tab then back to the original tab with the originally displayed plan? Is this worth submitting to feedback.azure.com?
  • 27:27 Recap, discussing skimboarding

I’m Speaking at the PASS Data Community Summit This Year!

#SQLPass
3 Comments

For years, the biggest conference in the Microsoft data platform community has been the PASS Summit.

This November 15-18, it’s coming back to the Seattle Convention Center. You can attend in person, or virtually.

I’m going to be there in person. It’s not a decision I take lightly – I still get nervous when I see rooms full of unmasked people. However, I was so overwhelmed with happiness at the in-person SQLBits this spring that I’m ready to give it another shot. (HA! Get it? Booster shot! It’s a joke about… okay never mind.)

This year, I’m not going
to attend a single session.

Not one. I’m gonna spend every single conference hour hanging out in the Community Zone, talking to people in the hallways, and catching up with people that I haven’t seen for a couple of years.

Weird, right? Especially since this is a release year for SQL Server 2022, and there are probably gonna be a lot of good sessions from Microsoft. You know what? I don’t care. I don’t. I’ll watch the recordings and read the slide decks later. When I’m in Seattle, I’m gonna have the experience that I missed so much.

I wanna see y’all again, hear what you’ve been up to, take pictures with each other, and be as friendly as we can while still maintaining good precautions. (No hugs, no shaking hands, no kissing with tongue.)

I can’t talk about my pre-conference session just yet, nor can you register for it. However, now’s a good time to start having conversations with your company management and your significant other to talk about whether maybe an in-person event makes sense this year – or whether you’d rather do it virtually.

In the immortal words of the sisters Pointer, I’m so excited.


The 6-Year-Old T-SQL Book That Never Goes Out of Style

T-SQL
19 Comments

You’ve been working with Microsoft SQL Server for yeeears, and you’re pretty confident that you know how to write a query.

So when I tell you about a book called T-SQL Fundamentals, you’re all, “nah, man, I got this covered.”

You are incorrect.

Sure, the first 5 chapters of the book are about 1-2 table queries, CTEs, subqueries, etc, and you’ve probably got that stuff down cold. But here’s where chapter 6 and 7 go:

  • UNION, INTERSECT, EXCEPT
  • Windowing functions
  • Pivoting data
  • GROUPING SETS, ROLLUP

And subsequent chapters only get more detailed from there: how to properly modify data with transactions in mind, temporal tables, and more.

When you start your 3rd year of working with T-SQL, this is the book your manager should hand you. By that point, you’ve learned enough about querying that you’re getting cocky, and yet there are so many more awesome possibilities available to you.

And if you’re the one who’s the manager – if you have junior folks on your team – then it’s your responsibility to hand Itzik Ben-Gan’s book T-SQL Fundamentals to your team. They’re not getting any smarter on their own, and every day that they don’t read this, they’re producing more inefficient technical debt that you’re going to have to go back and fix later.


Office Hours Speed Round: Text Edition

I recorded an Office Hours stream answering a bunch of your questions quickly, but…I forgot to turn on my microphone, so the video only had the sound effects:

Oops.

So here are the questions in text form instead:

Q: 22DBA: what performance recommendation you see people give all the time but it actually doesn’t work

Lowering fill factor server-wide. They somehow think that making your table larger will make things go faster.

Q: Not the Licensing Police But….: I work for a company that has 18 SQL Servers (mix of Standard and Enterprise) that I know are not licensed. I’ve flagged it and management simply don’t care about them not being licensed, and want to leave as is. “We need to save money” -What would your approach be here?

Put it in writing. Email your manager with a list of SQL Servers, their core count, and the approximate licensing fee. Don’t do it as an accusation, just put in writing that we have this many SQL Servers, and I don’t think we’re licensed for it. It’s then the manager’s problem. If the company doesn’t solve it, then it’s up to you to decide whether you want to keep working for that company – because if they’ll screw Microsoft, they’ll probably screw you when they get a chance, too.

Q: Higgins: Is Disk Queue Length a good metric to monitor for SQL server? If so, what are recommended values?

No. (Good job on the Ferrari-related name though, hahaha.)

Q: Alf: For partitioned tables, do you ever see partition keys on fields other than dates? If so, what are they?

No. I’m sure they’re out there, but I just don’t see ’em.

Q: Wally: When should “DBCC UPDATEUSAGE” be run? Is it expensive like CHECKDB?

After you upgrade SQL Server 2005 to 2008. I haven’t run it in over a decade though.

Q: Keld Rasmussen: Hi Brent, thanks for great posts. When I do select I some times use Read uncommitted because otherwise I lock the database for the application and the application updates fails. – How do I read committed from database without locking for the application? Best regards Keld

Check out RCSI.

Q: NotCloseEnough2RetirementToStopLearning: Hi Brent Any advice on how to move from a production DBA role to a Data Architecture role?

Talk to the data architects at your company to start gradually transitioning into that work. You probably won’t be able to leave your current company and jump to a new company in a data architect role – that jump is too far.

Q: Krishna: How many hours do you sleep and work per day on average?

I usually sleep 8-9pm to 3-4am, plus a 1-hour nap in the afternoon. On the days when I work, it’s usually 8-10 hours, but I just try to work as few days as possible.

Q: .NET User: Hi Brent, Love your office hours, thanks it helps a lot. In previous session someone asked about soft delete and you suggested to add IsDeleted field to the table. I wonder what are your thought about using soft delete vs temporal tables approach? Thanks.

Temporal tables make copies of the entire row as the row changes – that’s a waste of space when you just need soft deletes.

Q: Ezra: The previous CTO convinced all the developers to always use NOLOCK in all their sp’s. How do we change the mindset for this bad practice?

Search for Brent Ozar nolock and you’ll find a ton of videos and demos.

Q: Wally: What is your opinion on using views as constants holders? e.g. CREATE VIEW vStatus AS SELECT 1 AS Active, 0 AS Inactive, 2 AS Paused. Could multiple CROSS JOINs to similar views cause compilation issues?

It’s dumb because there are no statistics on the output of views. Use tables instead.

Q: Midwest DBA: What are your thoughts on Ottertune.com? Seems like a cool technology. Do you foresee Microsoft employing Machine Learning in this way to improve database/server performance?

Love the idea of OtterTune. Microsoft doesn’t appear to be taking this approach. They’re hard at work figuring out Cost Threshold for Parallelism. Gotta learn to crawl before you learn to walk, I guess.

Q: DBA_Mufasa: Hi Brent! What’s your best approach for tracking tables usage in SQL databases, in order to find tables that haven’t been touched (for eg. no select, updates, or inserts ) for a a defined amount of time. Considering that table statistics get cleared after server restart.

What’s the problem you’re trying to solve? If you’re trying to drop tables, go talk to the users.

Q: Morty: How do you determine the optimal number of DBA’s for a given product / company?

This.

Q: CKI: Management wants to create a copy of the production database for reporting purposes. The new “reporting” copy should be refreshed nightly with production data. Production database is on AWS Web Edition 45GB. What is easiest way to do it? Thank you very much!

Nightly restores.

Q: Quincy: How do you determine which SQL changes are low risk and which changes are high risk?

Whoever makes the change needs to list at least 2 ways the change might screw things up. If they can’t come up with 2 ways, they don’t understand enough about the change. Then, think about the business risks for those problems.

Q: Brutus: How do you determine the optimal Windows OS page file size for a bare metal SQL server?

I read the documentation. Doesn’t work for everybody, I know – some people struggle to read.

Q: Sandeep Pawar: I am a SQL DBA with 12 years of experience and also learning and trying my hands on Postgres as well. Is SQL+ Postgres DBA combo would be beneficial? Thanks

I wouldn’t go learning random technologies. Ask what your company needs, or if you want to change companies, think about the technology you wanna use for the rest of your life. Don’t gamble on what unknown companies might like – companies need janitors. Does that mean you should pick up janitorial skills?

Q: Stimpy: For concurrently executing queries against the same tables, will SQL Server share the read operation data between the two different queries?

Read this.

Q: cyrpl: Moving SQL query analysis out of SSMS to Azure data studios will be a big hill to climb for most DBAs, where are you on that hill Brent? Have you written any Jupyter notebooks yet, it looks like Microsoft and others are moving all in on this, what do you think?

Yes, I’ve written blog posts with notebooks. I can’t really teach with notebooks yet because they don’t have execution plan support.

Q: Twiki: What are the top SAN concepts that are beneficial for the SQL DBA to know about?

Start learning the storage your company uses. Often, it’s not SAN at all, but instead it’s cloud-based storage, and that’s completely different.

Q: TJ: We have 1TB database containing 950 GB single table. This table has 95% of data that is not needed anymore. Application team is recommending a slow and gradual delete of the data from front end, but it can lead to performance issues as you know. Can you advise best approach

If you’re going from a 1TB database down to 100GB, don’t do deletes. Insert the data you’re keeping into a new database.

Q: WhatsUpDocs?: Hi Brent, have you ever needed to look at business documentation (check business rules/logic) when consulting or as an employee, but it was severely lacking? Recently joined a different team in work and trying to find simple answers to questions is an uphill struggle…

The vast, vast majority of companies don’t document their technology. The tech is in a constant state of flux, and it’s a miracle if the tech even works, let alone is documented accurately. If you’re the kind of person who needs accurate, up-to-date documentation on the tools you use, you’ll be happier working for very large, slow-moving companies with compliance needs. Think giant global financial corporations.

Q: Neil: When a query goes parallel, does it use the MAXDOP number of threads or only as many threads as it needs ?

Generally speaking, it starts at MAXDOP + 1 coordinating thread.

Q: Erdem: Why does multi column index only have single column histogram step value?

Because Microsoft chose to fit stats in an 8KB page, and that required design tradeoffs.

Q: Conan: Non Tech question: Next Travel plans if any?

Israel this fall, Antarctica this winter.

Q: Preben: What is your least favorite operator in an index plan? E.g: EriK Darling’s eager spools

I thought a lot about this, and to refresh my memory, I looked at Hugo Kornelis’s operator list. My current obsession is the Parallelism Gather Streams operator, but I’m still in the honeymoon phase with it because everything seems so cool. Each new thing I learn about it, I think, “Ooo, neat, I can understand why they made that tradeoff.”

When I was looking at Hugo’s list just now, I cringed a little when I saw the Bitmap operator. I’ve managed to get through almost 25 years of working with SQL Server without having to know what that operator did. I’d kinda always had it in the back of my mind as, “I should learn more about that someday,” and so I read through Hugo’s explanation just now. I nodded my way through it, and closed the browser tab like Grandpa Simpson walking back out of the club. If you made me pick, that’s probably my least favorite.


Don’t Treat SQL Like a Programming or Scripting Language.

T-SQL
6 Comments

In a programming language like C# or Java, you tell the computer what to do, in order.

Get the customers from California, then get their invoices, then sum the total.

SQL, on the other hand, is a declarative language where you declare the shape of your result set:

Get the total sales from Californian customers.

You’re declaring the output that you want, not the methods the database server uses to build it. Oh sure, you CAN use SQL to declare the shape of your query plan, but generally that leads to heartbreak and despair because:

  • You don’t know the database engine’s internals as well as you think
  • You’re backing the database server into a corner, forcing it to use specific techniques, when it has better ones available at its disposal
  • Your data distribution is going to change over time, meaning the method you use to process the data today won’t make sense in a year or two
  • Your database server is going to change over time as you patch and upgrade it, meaning it has newer and better methods to process data, and it needs the flexibility to leverage those
  • Your hardware is going to change over time, and the query plan that made sense with 16GB RAM won’t make as much sense with 512GB RAM

When your application is young (1-5 years), use as few engine-specific hints as possible in your queries. Don’t tell the database server which index to use, what order to do its processing, or what kinds of joins to prefer. Give it as much flexibility over time as possible by keeping your query simple and letting the engine make different decisions as your data, database version, and hardware changes.

When your application is mature (5-10 years), then your data distribution, database server version, and hardware has started to specialize. This is the time where you can start bossing the database engine around with specific hints to tell it how to do processing.

But if you use those hints when your application is young, you’ll find that you’re constantly revisiting old code, having to fix query performance, because the query hints you used on a 1-year-old application just don’t make sense anymore, and they’re making performance worse instead of better.


The Top Feature Requests for SQL Server

SQL Server
27 Comments

If you want to get a look at where Microsoft SQL Server is heading in the future, it helps to get an idea of what users are repeatedly asking for. Here’s a quick rundown of the top requests from feedback.azure.com as voted by the people who cared enough to log in and upvote ’em:

10: Enable Query Store for collection on a read-only replica in an Availability Group: 563 votes

9. Add an SSMS keyboard shortcut that executes the statement where the cursor is placed: 589 votes

Of course I have a straight face under here, why do you ask?

8: Improve NoSQL functionality: 652 votes

7: Run DBCC CHECKDB with PHYSICAL_ONLY automatically in the background: 672 votes

6. Support DISTINCT for STRING_AGG: 717 votes

5. Add a row position column to STRING_SPLIT: 747 votes

4. Develop an SSRS ReportViewer for ASP.NET Core: 1,262 votes

3. Restore a table from backup: 1,347 votes

2. Dark theme for SSMS: 1,958 votes

1. Put the Debugger back into SSMS: 2,041 votes

When thousands of folks band together to ask for something, Microsoft hears your voice. They know it’s important to keep the end user community satisfied so folks will keep evangelizing the database they know and love. That’s why in every release, Microsoft keeps bringing crowd-pleasing game-changers like SQL Server Ledger, blockchain technology in the database that keeps your transaction log around forever inside the database. I’m pretty sure that one was #11 in the list above. Maybe #12.

That’s why it’s so important that you create feedback requests to reflect what you’re interested, take time to read the requests from other folks, and upvote the ones you’d like to see implemented. Microsoft believes that your votes should matter, and that’s why they show the list of feedback items sorted by votes. Your voice matters: check out Microsoft Feedback today.


“Index Seek” Doesn’t Mean Much.

Execution Plans
13 Comments

When you see “index seek” on an execution plan, that doesn’t mean SQL Server is jumping to exactly the row you’re looking for. It only means that SQL Server is seeking on the first column of the index.

This is especially misleading on indexes where the first column isn’t very selective.

To explain, I’ll take one of the big Stack Overflow databases and create this index on the Users table:

And then I’ll run this query:

The actual execution plan shows an index seek:

But hover your mouse over the index seek to see the popup details:

Look at “Number of Rows Read” – we read 6,044,557 rows to produce exactly 0 rows. That’s not what you or I would really call an index seek – we read 2/3 of the entire table!

The problem is that the term “Index Seek” only refers to how we access the first column of the index. At the bottom of the screenshot, the term “Seek Predicates” indicates that we did indeed seek to Reputation = 1, but unfortunately, there are millions of users with that reputation.

A little higher up in the screenshot, the term “Predicate” indicates that we have a leftover predicate that we couldn’t seek into. I wish this was labeled as a scan predicate because we’re scanning all the rows that match our Seek Predicate – we’re scanning them all because they’re not in an order that helps our search.

Sure, technically DisplayName is in the index – but it’s not the second column in the index, so it doesn’t really matter whether it’s in the key or the includes.

When you see an Index Seek in a plan, before you jump to conclusions that it’s a good usage of the index, compare the Number of Rows Read versus the Actual Number of Rows. If your query is reading way more rows than it’s actually producing, you might be able to dramatically improve performance by tweaking the indexes.


Who’s Hiring in the Database Community? May 2022 Edition

Who's Hiring
24 Comments

Is your company hiring for a database position as of May 2022? 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.

If you want to be notified as people leave new jobs here, there’s a “subscribe without commenting” option where you leave a comment. I don’t use that for subscribing you to anything else.


Office Hours, Text Edition: No-Brainer Questions

For my Office Hours streams on my Twitch channel, y’all post questions at PollGab and upvote the ones you’d like to see me discuss on the stream. However, not all questions require a video answer. Here are some simple no-brainer ones:

Q: BehindTheScenesDBA: With all its limitations, what do you think about memory-optimized tables? Have you recommended to a client to use it?

A: They don’t make any sense for the vast, vast, VAST majority of clients, and no, I’ve never recommended them. In-memory temp tables are different though, and I cover when to use those in this module of my Fundamentals of TempDB class.

Q: Sultan: Does this mean Azure storage is now as fast as a Y2K SAN?

A: Well, a couple of VM types are now competitive with my laptop, so…that’s good, I guess?

Q: allmhuran: Azure SQL databases come with lots of connectivity issues with SSMS – a tenant MFA prompt (legit), then another MFA prompt for every other tenant where I have a guest account (?), then a firewall prompt (if I can add the rule, why even ask?). Do you know if MS is working on this? 

A: Did you call them to open a support case to get them to fix it?

Q: Florence Nightingale: What is your opinion of the stat histogram step limit (199) in SQL Server? Should this be changed in future versions?

A: It’s fine for most use cases. I think in the grand scheme of things, there are other things I’d rather have Microsoft focus on fixing instead. Most people don’t even have accurate knowledge about this kind of thing, let alone know that it’s a problem. Hell, in your own question, your facts are wrong, ha ha ho ho – the limit isn’t 199 steps.

Q: Levi: Will long running index reorg / rebuild jobs for large tables prevent the transaction log from clearing? Is this a risk?

A: For rebuilds, yes and yes.

Q: Mr. Bean: Hi Brent, do multiple data files per file group help with user DB performance like how they help with TempDB performance? Running SQL 2019 on NVME SAN.

A: Only if your workloads are the same as TempDB, meaning users create and drop thousands of tables per second.

Q: JerseyDba: Migration from 2016 to 2019 OR 2022 – I am currently ready to upgrade our SQL database servers and was wondering if it was worth upgrading to 2019 or waiting for a stable version of 2022?

A: Don’t hold out hope for something that doesn’t even have a public preview yet, let alone a release date.

Q: Sev A: Hi Brent, when Compilations/Sec are high, is there good way to view the recently compiled queries in real time / near real time?

A: sp_BlitzCache @SortOrder = ‘recent compilations’

Q: Dwight: Is analysis of the auto generated stats ever recommended or useful (i.e. WA_Sys_00000003_15502E72)?

A: Yes, and I discuss it in my Fundamentals of Query Tuning class.

Q: Mr. Ed: Hi Brent, what tool / script do you recommend for formatting SQL agent job query results to HTML for subsequent emailing?

A: If you want reports, use a reporting tool. You’re trying to use a job scheduling tool for reports. Sir, this is a Wendy’s.

Q: Augustus Gloop: Will the max of 160k query plans be increased to accommodate Parameter Sensitive Plan Optimization in SQL Server 2022?

A: Anything about 2022 is subject to change until release, so I wouldn’t worry about the specifics yet.

Q: Noam: What’s the easiest way to see the number of locks acquired for a given update statement (for lock escalation concerns)?

A: sp_WhoIsActive @get_locks = 1

Q: Unsigned Biggie: Hi Brent, worried about eventually running out of int64 values for an identity column on a new table in SQL. Should I seed the identity column value with the max negative value for an int64 to prolong the life of this table?

A: Sure.

Q: Sir Logs-Alot: Hi Brent, have you ever seen sp_whoisactive intermittently crash dump on SQL 2019 CU14?

A: No.

Q: Hatsune Miku: Hi Brent, what are your recommended settings for SQL Agent job history log size and job history rows per job?

A: I don’t have any opinion on that.

Q: Marco: How often does using the KEEP FIXED plan option get you over the finish line?

A: Never. Not even once.

Q: Brandon: Has a SQL Server service pack / cumulative update ever given you buyers remorse? If so, which one?

A: Many. Search for “breaking news” on this site.

Q: JAH: Do you know if Polybase suffers from the same performance limitations as linked servers? MS docs says “the benefit of Polybase is to allow the data to stay in its original location & format so that it can be queried in place like any other table in SQL Server.” But at what cost?

A: Yes.

Q: Doug: Hi Brent, is there a good tool or way to compare the estimated number of rows (equality) for each step in a stats histogram with the actual number of rows in the real table? Might be good to know when there is large variance between the two.

A: Nothing built in, no.

Q: OhLordI’mStuckInAzureAgain: My friend has a stored procedure that takes a minute to execute. The plan has a statement with a MEMORY_ALLOCATION_EXT wait where the WaitTimeMs is 400, but the WaitCount is almost 1 million. Should he be concerned by the high WaitCount even though the WaitTime is relatively low?

A: No.

Q: Voltron: Hola Brent! I inherited a SQL Server 2016 database that has the trustworthy database property enabled. The application vendor confirms this setting is required. What can be done to provide a secure environment for this database and our other SQL Servers?

A: I don’t do security work.

Q: Talip: What is your opinion of the SQL Virtual Machines best practices assessment tool for Azure? How does it compare with sp_Blitz?

A: I have no idea.

Q: Stanley Hudson: Sometimes we see log shipping alerts notifying us that we are more than X minutes out of sync on the log shipping secondary.Hard to tell what burst of transaction log activity (other than Index maintenance) causes these warnings. Is there a good way to track down the root cause?

A: Try logging sp_WhoIsActive to table and check out the Writes column.

Q: Robert: Do you know why, in the execution plan, the number in % of actual vs expected rows is stored as an integer instead of a bigint? It went negative on me today cause of int overflow 🙁

A: “Why?” For “Why” you would need to ask Microsoft, right?

Q: eshirvana: Why optimizer is using nested loops join when I join my tables? what can I do to force optimizer to use a better join algorithm? under which circumstances should I use join hits, If I should at all.thank you

A: Check out my Mastering Query Tuning class.

Q: Ralph Hinkley: Does sp_blitz check for high VLF counts?

A: Open the code and do a control-F for VLF.

Q: Dan Heile: Why can’t sql DMVs or monitoring software like SentryOne capture performance statistics on stored procedures that open symmetric keys / certificates to decrypt encrypted columns?

A: “Why?” For “Why” you would need to ask Microsoft, right?

Q: Bill B: On an alert 14 insufficient permissions for example is there a way to get the machine name instead of just the ip address? DESCRIPTION: Login failed for user ‘userxxx’ . Reason: Could not find a login matching the name provided. [CLIENT: 123.123.123.123]

A: I have no idea.

Q: Accidental_dba: Hi Brent,Thank you so much for this great work,I have question on memory table,my normal insert for 18m record taking 40 hour with 8core and 150 gb ram,top waitype as per blitzfirst are Sos_work_dispatcher,pwait_directlogconsumer_getnext Anything i can do here ?Feel like m baddba

A: That’s beyond what I can answer quickly here, but check out the Consulting section of the site.

Q: DBA_Europe: Hi! We have a few 3 node AGs(2 ha in sync & 1 DR cross site async). Cross site network throughput is too slow for replication to DR(log send queue accumulates). Can you combine AGs and log shipping? BOL doesn’t mention this in the interoperability Section.

A: Yes.

Q: Tobias Fünke: What is your opinion of the Azure SQL Database DTU Calculator app for lift and shift capacity planning a move from onprem SQL to AzureSQL? https://dtucalculator.azurewebsites.net/

A: I’ve never used it.

Q: Mark Tomlinson: Hi Brent – can you think of a situation where a sql server spid could exist without a query plan associated/created for it to operate?

A: Yes, system processes.

Q: RetiredDBA: I used to admin a third-party db which had hundreds of tables containing millions of rows and lots and lots of indexes. The vendor used guid’s exclusively for PK’s. What would be your advice on this choice of datatype for PK’s?

A: I explain that in the clustered indexes module of my Mastering Index Tuning class.

Q: GoingWalnuts: Brent, I have an Azure database where foreign keys are just not being enforced. All enabled/trusted. Full of bad data now. You can even drop and create FKs that should never EVER work, and they do. Am I missing a setting… or some medication? SQL just whistles on by. Weird..

A: I explain that in the foreign keys module of my Mastering Index Tuning class. (I know, I feel kinda bad when I link to those, but there’s a reason those modules are 30-40 minutes long.)

Q: CKI: My developer wants to have permission to Kill his own processes . Currently the user has only read/update/delete/insert permissions on the database. How can I grant the “Kill only his processes” permission ? Thank you!

A: Put the kill in a stored proc, and sign the proc with a certificate. Here’s an example in the sp_BlitzFirst documentation.

Q: Ben Cox: Hey Brent – What are the technical reasons why CDC is not supported for CCIs?

A: I have no idea.

Q: Timbalero: Hi Brent, a 1TB clustered table has a nvarchar(max) column and a nc index built of all base columns. Including the nvarchar column into the nc index was supposed to be a way to fix blocking on that column but I wiev it as a sledgehammer approach. Is there a more elegant solution?

A: Answering that would really require seeing the tables, queries, etc.

Q: Kamy k: Can’t find the answer anywhere, Is it possible to pass environment variables into Code snippets to autofill ie current date, user, DB. Specifically for use in generating header documentation.

A: I have no idea.

Q: Jr Wannabe DBA: Hi Brent, I see often wait stats like “For 2004 seconds over the last 5 seconds” on servers with 4 to 12 CPUs with sp_BlitzFirst, mostly CXPACKET or CXCONSUMER. How to read/understand this? It looks too much for that time frame.

A: Your server has multiple CPU cores, each of which can run multiple queries simultaneously. I go into more details on that in the first module of the Mastering Server Tuning class.

Q: DannyDing: Hi Brent did you have any experience with SQL Server supporting PeopleSoft ERP?

A: Yes.

Q: StanTheMan: Hello Brent,sa account.When master DB stores usernames and pswds,how can I secure the master DB before attack?Recommendation is not to use sa as users DBs owner+disable sa and the one and only DB account per server as DB owner in case of Princpiles of least priviligies.Thats all?

A: I don’t do security training, sorry.

Q: SeeCoolGuy: can sp_blitz help out to identify those long Network I/O sessions?

A: No, for that, use sp_BlitzWho or sp_WhoIsActive.

Q: PleaseAndThankYou: Hi Brent, It says online that cluster indexes are faster that non-cluster indexes. Does changing a non-CI to a CI have performance benefits? I ask, cause they want me to do this to see if the company program wil run faster.

A: Check out my free How to Think Like the Engine course.


[Video] Office Hours: New Las Vegas Home Office Edition

Videos
2 Comments

It’s the first webcast I’ve done from my new Vegas pad. You posted questions at https://pollgab.com/room/brento/ and I discussed the highest-upvoted ones:

  • 00:00 Introductions
  • 02:15 Jim D: Should I use full backups or log shipping for migrations?
  • 03:53 Sev A: What are the top misuses of SQL Server that are better accomplished without SQL Server?
  • 07:16 Greg: What are the best/worst KPIs for SQL Server DBAs?
  • 09:15 DBA_Mufasa: Why can’t we restore a table from backup yet?
  • 11:38 Tolga: Should we fix deadlocks by doing retries or changing the query?
  • 12:54 Anyong: Should I put clustered indexes on temp tables to avoid high forwarded fetches?
  • 14:45 Can I JOIN You: How can I showcase my worth when I improve things?
  • 17:32 You’re the man now dog: (something about SaaS databases, not clear)
  • 18:32 john doe: What are your thoughts on crypto currencies?
  • 19:36 Jeb: How do you convince someone to implement caching?
  • 21:57 Aslan: What are the cons of hosting a database on a USB drive?
  • 22:53 Sultan: Is it okay to run SQL Server 2019 Standard on a physical box with more than 24 cores?
  • 25:29 Recap and discussing the Las Vegas home office

What DBAs Need to Know About Snapshots

Storage
5 Comments
Brent Reading Book
Snapshots by Polaroid? That’s not right.

You’re a DBA responsible for making sure SQL Server databases are online, backed up, corruption-free, and fast. Your databases have gradually grown in size over time, and you’re starting to hit new size issues you haven’t encountered before.

Nightly maintenance windows are getting smaller, you’re not able to refresh your development environments quickly enough, and you’re not able to run DBCC CHECKDB as often as you’d like.

You’re starting to wonder – how do people with multiple terabytes of databases handle it?

In a free webcast on May 12, I will explain how I came to love storage snapshots. I’ll discuss how they work, why they’re safe to rely on, and how to tell when they’re a good fit for your workloads.

Register here free, and if you can’t make it live, you’ll also be emailed when the recording goes up.


[Video] Office Hours: Last San Diego Balcony Edition

Videos
0

It’s the last San Diego balcony edition of Office Hours! You posted your questions at https://pollgab.com/room/brento and upvoted the ones you’d like to see.

Here’s what we covered this week:

  • 00:00 Introductions
  • 00:50 Steve E: Hi Brent, How are you enjoying the Porsche 944?
  • 01:55 Midwest DBA: Hey Brent, The wife says my DBA oncall schedule is too intrusive to our family time. How do I switch to being a developer with no on-call? Is being a developer the only career transition I can make that would have no oncall? Will I make less or more? I’m currently at $90K
  • 04:25 NotCloseEnought2RetirementToStopLearning: Brent As a prod SQL DBA since 2001, I’ve expanded my skills into Azure SQL and Cloud generally to keep relevant. It hasn’t increased my marketability as I expected. What are technologies (Postgres? Synapse?) are worth learning to stay in high demand for the next 5-7 years?
  • 06:15 DBA_Mufasa: Salut Brent! What is your recommendation regarding failing over an Always-On Availability Group during Server OS patching for instance. I have “heard” it’s best practice to move the cluster resource within failover cluster manager first, before failing over the AG group in SSMS.
  • 06:43 Wally: How do you deal with justified / unjustified criticism as a DBA?
  • 08:18 Doug: Are there any good use cases for when stats auto update should be turned off for a given table?
  • 10:45 Doug E: Hi Brent, is there a good way to know when a plan operator has a hard coded estimate?
  • 11:01 Halim: What are your top two most painful mistakes as a DBA?
  • 15:21 SQLing Pigs: Third Party App is sending ad hoc queries with IN statement that has varying number of items. I’m getting thousands of execution plans from this. What are my options? More important plans keep getting pushed out.
  • 17:16 Tony Feuz: Brent – I know you are a fan of assigning one database to each customer instead of a multi-tenant model but if you are going down the path of multi-tenant: then is using an inline table valued function a good way to implement row level security? Are there performance issues?
  • 19:54 HerbyHoover: Hi Brent, What would you say are a few core performance metrics that a Jr. SQL Server DBA should focus on better understanding?
  • 20:41 Sultan: Hi Brent, should Microsoft put the TSQL debugger back in SSMS? Do you ever use the TSQL debugger in Visual Studio?
  • 23:08 Wally: Which SQL server docs do you read / focus on when a new version of SQL Server is released?
  • 24:25 Jim Ignatowski: Hi Brent, our top wait is PREEMPTIVE_OS_GENERICOPS (Wait time hours = 3746.1, Hours Sample = 812.6, Per Core Per Hour = 0.1, Physical SQL 2019 Enterprise with 64 cores). What can we do to reduce this wait or should we ignore it?
  • 25:09 Benny Hill: Do you or your clients ever run into issues with SQL Server / TLS?
  • 26:04 The F5 Masher: Hi Brent, should Microsoft enhance extended events to allow writing of session results to actual tables?
  • 27:45 Latka Gravas: Hi Brent, for a dedicated backup test / checkdb SQL Server (SQL 2019, 64 cores), is it a good idea to set Maxdop to 0 to maximize CPU use? Using dbatools sql agent job to do the backup restores / checkdb’s.
  • 30:18 Ralph Nader: Hi Brent, is there any safety value in running CHECKDB against TempDB and Model?
  • 31:41 Jacob: Does updating stats for a table have similar free space requirements to that of rebuilding an index?
  • 32:08 SQL Serenader: In one of your articles, you suggest 4 GB or 10% of RAM (whichever is LARGER) for MAX server memory. What about a server with 767 GB of RAM? Allocating 77 GB to non-SQL processes seems excessive. Could we get away with 16 GB (2%) if we don’t see memory pressure or paging to disk?
  • 34:32 Genady D.: Hi Brent Recent expensive queries in Activity Monitor miss (by my opinion) link to user/host who run it. How do you deal with this problem? Which way except profiler the best?
  • 35:50 Recap and goodbye to the balcony

What Do the LCK_M_SCH_M and LCK_M_IS Wait Types Indicate?

You’re investigating your SQL Server’s top wait stats, and you’re noticing a lot of LCK% waits. Normally, that indicates blocking and deadlocks, but you’re just not getting complaints from your end users, and you’re wondering what’s causing it.

It might be overzealous index rebuilds.

Let’s demo why by starting a new query in the Stack Overflow database:

After that query begins running, I’ll try to rebuild indexes in another window:

And after THAT begins running, I’ll start another reporting query in another window:

And while all 3 of those are trying to run, I’ll run sp_BlitzWho and see what’s happening:

Let’s zoom in on the sp_BlitzWho results and talk through ’em:

sp_BlitzWho’s output is arranged from the first running query to the latest started ones.

The first select is off and running, actively building its query results.

The second query is trying to rebuild the Votes index. However, to do that, it needs a schema modification lock, as indicated by the LCK_M_SCH_M in the wait_info column. It’s being blocked by the first select, and it can’t even begin its work until the first select releases its schema stability locks.

The third query, the select, simply needs a shared lock on the table. It isn’t trying to do anything fancy, just read it – but because the index rebuild is technically modifying the table’s schema, the select can’t even start. That third query will pile up LCK_M_IS waits the whole time query #1 does its work, and then query #2 does its work, cumulatively.

If your SQL Server is mostly bored during the daytime, and you’re doing full-blown index rebuilds every night, this can make LCK% waits inch up to the top of your wait stats metrics.

To solve it:

  • If you’re on Enterprise Edition, consider switching to online index rebuilds with WAIT_AT_LOW_PRIORITY on
  • If you’re on Standard Edition, consider rebuilding indexes at a lower-load time, or only on the weekends
  • And keep your wait time ratio in perspective: your server might just be bored, with low wait times overall, so the LCK% issues look like they’re high priority when they’re really not
  • Track your wait times by hour of day and day of week, and focus on the wait stats during hours when users care about query performance

Updated First Responder Kit and Consultant Toolkit for April 2022

First Responder Kit Updates
0

Wow, talk about stable! The last 3 months have only introduced a series of small bug fixes. I held this release back as long as I could, waiting to see if anything major might get added or if we might get a SQL Server 2022 CTP, but no dice. If there was ever a First Responder Kit release you could skip, it’s probably this one. If the specific issues listed here don’t affect you, take the morning off.

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

  • Fix: don’t warn people about multiple log files for the same database on the same drive if the sum of the log files is 2TB or more, since that’s the max log file size. (#3081, thanks Henrik Staun Poulsen.)
  • Fix: uninstall script works even if databases have different collations. (#3077, thanks Fiander.)
  • Fix: don’t crash if sys.plan_guides holds a hint with a double quoted index name. (#3059, thanks Frank Renesnicek.)
  • Fix: arithmetic overflow on large database growth sizes. (#3063, thanks David A. Poole.)

sp_BlitzFirst Changes

  • Fix: the recently updated stats check was only running in the current database. (#3076)

sp_DatabaseRestore Changes

  • Fix: @StopAt now works for differential backups. (#3061, thanks Rob Hague.)

Bonus changes: Anthony Green kept the SQL Server versions file up to date.

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 Speed Round: SQL Server Q&A

Videos
0

You posted questions at https://pollgab.com/room/brento and I picked the fastest ones to answer:

Here’s what we covered:

  • 00:00 Introductions
  • 00:16 Disgruntled App Developer: Hi Brent a friend was recently reprimanded for running a trace in production. The rationale was perf impact and other ‘potential issues’ that weren’t forthcoming. Any thoughts on why traces have an impact on perf, when that impact becomes a problem and any other potential issues?
  • 00:59 Krishna: What are the top issues you see with SQL Filestream?
  • 01:48 Dwight: Hi Brent, any recommendations for where / where not to place masterdb?
  • 02:09 StatisticsRules: Hola Companero! When running SQL Server on SSDs can I completely disregard fragmentation and literally never run index rebuilds if I update statistics continually (daily)? I’ve inherited DBs that have tables with 99% fragmentation, gut feeling is a bit queezy… need guidance.
  • 02:25 Isaac Wahnon: Have you had experience with SQL installations on Nutanix virtualization? Especially high end systems, in your opinion is there an upper limit for SQL in a virtual environment?
  • 03:27 Lucas: Hi Brent, what are your thoughts on the Microsoft Defender for SQL offering?
  • 03:40 All_Alone: Hey Brent, Are DBA’s still using Log shipping delay to protect against “oops” deletes? Are there any new developer specific tools that prevent “oops” deletes, so that one doesn’t need a log shipping delay Server for that reason alone?
  • 04:33 James: Hi Brent, is there any commercial training available for sp_HumanEvents?
  • 04:55 Frito Bandito: Hi Brent, do you know of a good way to overcome the TSQL batch size limit of 65,536 bytes? Trying to update several columns in a single record but wary of hitting the batch size limit.
  • 05:20 Louie De Palma: Is SQL in place upgrade (2016 to 2019) acceptable / low risk if the server is virtual and has a snapshot backup of all the DBs?
  • 06:02 Neil: Learned a lot from fundamentals classes and then just now fixing a CPU performance problem in Prod. Dev made a log table with no indexes. So I added a clustered index on the id column in Prod. But now I’m thinking I should tell dev to add a primary key to the id column instead?
  • 06:37 Rick: Hi Brent, what are the top SQL Server NUMA related issues you run into with clients?
  • 07:29 i_use_lowercase_for_select: Hi Brent, hope you’re feeling better already. I was wondering what the title of the happy tune is you play while letting us do our lab work during your training classes.
  • 08:30 Ferris B: When you were a DBA, did you keep any kind of journal / documentation to show management that you are actually doing work despite a smooth running system?
  • 09:50 The last Bothan: What is the oldest SQL server version you have parachuted in and worked on?
  • 10:25 Gizem: Any possibility of including sp_PressureDetector and sp_HumanEvents in future versions of the first responder kit?
  • 11:06 Malik: Hi Brent, what are your thoughts about Query Store being enabled by default in SQL Server 2022? Is this safe?
  • 12:28 Malik: Hi Brent, what is your opinion of the new SQL Server ledger functionality in SQL Server 2022?
  • 13:22 BehindTheScenesDBA: Hello Brent, Any plan to present the Cluster/AlwaysON training with Edwin this year or in the near future?
  • 13:52 Özlem: Should new new non-clustered indexes be created during business hours or strictly after hours? Any risks here?
  • 14:46 Tu?rul: Does the SQL Server 5k (approximate) row lock escalation mechanism use estimated number of locked rows or actual number of locked rows to make the escalation decision?

Who’s Hiring in the Database Community? April 2022 Edition

Who's Hiring
12 Comments

Is your company hiring for a database position as of April 2022? 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.

If you want to be notified as people leave new jobs here, there’s a “subscribe without commenting” option where you leave a comment. I don’t use that for subscribing you to anything else.


Get 25% Off Training – This Week Only!

Company News
0

I did huge price cuts this month across the board.

My individual Mastering class recordings used to be $995. Now it’s just $395 for Mastering Index Tuning, Mastering Query Tuning, Mastering Parameter Sniffing, or Mastering Server Tuning.

The Recorded Class Season Pass: Mastering used to be $2,395 for all of my Masters classes. Now it’s just $995!

I’ve also heard from a few of you that had Live Class Season Passes that expired this month, and you wanted to be able to watch the rest of my live classes scheduled through October. To make that happen, I’m temporarily selling a 7-month Live Class Season Pass for $895.

You can also pick up the Level 3 Bundle, which includes the 7-month Live Class Season Pass, plus a full year of Recorded Class Season Pass Masters & Fundamentals, SQL ConstantCare, and the Consultant Toolkit for $1,595.

I’m not bringing back the full Live Class Season Pass permanently – I just wanted to let folks continue to access my scheduled live classes through October of this year. After that, I’ll be taking a well-deserved break from live teaching – and drinking more of those cheap Mexican margaritas.


[Video] Office Hours: San Diego Office Edition

Videos
2 Comments

I’m back in the US at my home studio in San Diego, so I did a live Office Hours session. Post your questions at https://pollgab.com/room/brento and upvote the ones you’d like to see me cover.

Here’s what we covered today:

  • 00:00 Introductions
  • 00:41 WTF_SQLServer!?!: I have a some dbs that take nearly 6 hours each to finish recovery after patching. I’ve checked VLF counts and they are reasonable. The dbs are in SIMPLE recovery and logs are showing huge recovery times but millisecond times for analysis, redo and undo.
  • 02:04 Marcus: Is SQL Server the only relational DB that can have parameter sniffing issues?
  • 02:54 GrumpyDBAsFriend: A friend’s manager wants him to replace a SQL 2012 Enterprise server (24 cores) with a 2019 Standard server (12 cores). This is a heavily-used box with about 40 databases running multiple critical applications… should he just find a new job now?
  • 04:38 Ferhat Karatas: I use Ola hallengren’s index maintenance script every night. But I figure out that sql server unresponsive during script execution.
  • 06:00 Roland G: Hi Brent, is it ever a “good thing” to create one of clippy’s suggested indexes when the query already runs sub-second but consumes 200,000 logical reads.
  • 07:16 Miguel: Hi Brent, what are the top issues you see surrounding Microsoft Distributed Transaction Coordinator?
  • 08:38 Oscar G: Hi Brent, is ‘grumpy’ a justified stereotype for DBAs?
  • 10:53 Louis: To combat effects of param sniffing, is it acceptable strategy to proactively / automatically run a common query once to put it’s plan first into cache before outliers have a chance to be put into cache?
  • 11:55 Hoss Cartwright: Hi Brent, how can you detect when developers are hinting maxdop 0 in their queries?
  • 13:42 Beta Ray Bill: Hi Brent, I sometimes see articles where the author has the data file and transaction log file for a given DB on separate drives.
  • 15:05 Sultan: Hi Brent, what are the top gotcha’s / hurdles for a long time pessimistic locking SQL developer moving to “A whole new world” of optimistic locking (RCSI) in Azure SQL?
  • 16:02 TurnerBurn: Hi Brent. My friend is pulling his hair out (he’s quite bald already) over a deadlocking issue. sp_BlitzLock shows lots of deadlocks of 2 queries, but both are crazy-fast when executed so poor performance does not seem to be an issue.
  • 17:40 Egemen: Hi Brent, does Query Store work well with cross database queries?
  • 18:51 gserdijn: A database server of a customer has 8 processors and SQL Server Standard Edition. So 4 processors are not being used due to licensing issues.
  • 20:30 Sir Logs-A-Lot: Hi Brent, Will you consider writing a SQL anti-patterns book? It would be a New York Times best seller. 🙂
  • 21:53 Süleyman: Hi Brent, what are the top features of PostgreSQL and Oracle that you would love to see incorporated in a future version of SQL Server?
  • 23:40 FrankieG: In a recent server survey I found the option to use “legacy cardinality” was on.
  • 25:10 1440×1080: Hi Brent ! Is there a way to recreate a query text based on its execution plan ?
  • 25:53 SqlPadawan: Hi Brent. A career-related question: in your experience, what has helped you to decide when it’s time to move on to another place or to your own thing?
  • 27:31 Jason Stapley: Is the future of the DBA role Data engineer or Data Lead or Data Steward or other? I’m seeing a trend toward Data engineer. Nice seeing you at SQL Bits 2022 UK!!
  • 29:20 BehindTheScenesDBA: When/if you manage more DBAs under you, how do you keep the synergy/interaction among the DBAs as healthy as possible? Sometimes I see some members act like I am better than the others.
  • 30:46 Ned G: Other than “Do it out of hours and use nolock”, How would you suggest my friend queries a couple of error log tables (~5m rows) for Message like %SomeError%?
  • 32:24 GI Joe DBA: Management wants their first DWH in Azure. The data will be sourced from a few 100GB Azure SQL DBs. A non-dba name-dropped CDC, is it worth a look or is SSIS\DataFactory the common data feed sol?
  • 34:20 i’ll_talk_about_bruno: What is your favorite deprecated feature in SQL Server
  • 36:06 sqlsucks: Hello Brent, how was your SQLBits experience in London?
  • 39:45 dman: Say one node of your two-node sql cluster is suddenly dead (non-recoverable hardware failure) and you have a 24 hour old vm snapshot for that server.
  • 41:46 I want to be on a beach in Cabo: I have a legacy source system that used BIGINT’s for a PK. We switched to a new system that uses varchar as the new PK datatype. It’s still a bigint though. No joins to the source systems. Should I convert the new values to bigint
  • 43:09 gabriele: my friend’s dev team is developing the old apps in .net core with codefirst, this means the database structure is binded by the program and therefore my friend can not suggest query changes because the queries are made by .net
  • 45:23 Sir Logs-A-Alot: Brent, Nice fanboy shirt… Where do you purchase your funny shirts?
  • 46:07 Boutaga: Hi Brent, love your show from the beach. I see in the XML of the compiled plan of a very slow query a parameter data type varchar(20) compiled with a value C followed by 19 white space

[Video] Office Hours: Ask Me Anything at Sunset on the Balcony

Videos
0

Ah, sunset – time to kick back on the balcony, watch the ocean, and discuss SQL Server. You post questions at https://pollgab.com/room/brento/, upvote the ones you’d like to see me answer, and I discuss ’em:

  • 00:00 Introductions
  • 00:30 iAmGroot: Hello Brent, my friend has a VLDB and he’s asking what is a good strategy to implement in order to archive (offload) old data somewhere else (not on the same database) with minimal impact on the live database uptime; he also says he would avoid ETL jobs like the plague
  • 01:42 Mehdi: Hi Brent! The estimated number of rows is equal to the actual number of rows. The server has enough memory. Why does the sort operator spill to disk? It is common in creating the nonclustered index operation. (It is not a query). The actual execution plan is parallel.
  • 02:53 LLeopold: Hi Brent, can RCSI affect pure clustered index insert, and what tool (sp_blitz*?) can you recommend for troubleshooting slow inserts? Best regards, Igor
  • 03:50 WorthTheTuningEffort?: Hi Brent, my friend wonder where you would put your effort first (from sp_BlitzCache results)? In tuning a query that execute 7K time per minute with average duration of 0 or in a query that execute 3 times per minute with average duration of 200. We have control of the app. Tks!
  • 04:48 DefaultDBA: I have a question on how you would handle reducing parameter sniffing issues in Entity Framework. Our legacy system was all Sql Server objects (stored procs, views, etc) and with the new app, management overcorrected, and is using only EF (which is controlled by developers)
  • 05:47 Frankie Carbone: Hi Brent, is there much value for the MSSQL DBA in learning Entity Framework?
  • 07:07 Mr. Ed: Hi Brent, what are your favorite short-cut keys in SSMS? Do you assign any custom short cut keys? P.S. Please don’t eat me 🙂
  • 09:06 Dave: Hi Brent, I recall you talking about naming conventions for indexes in Mastering Index tuning and how you now drop IX_ from the beginning however I can’t find it, any chance you can point me in the direction of the blog you mentioned?
  • 10:10 DBA_Mufasa: Hi Brent, Do Microsoft really perform licensing audits? With everything they have going on, what are the odds they will send the “licensing police” to look after broke companies using SQL Developer as their production environment?
  • 10:55 Roy: Hi Brent, If I’ve deleted a large amount of rows, will SQL Server stop expanding the database files (they are set to Autogrow currently) and re-use the free space it or is further action required? Thank you
  • 11:10 Shamwow: Hi Brent, is there a good way to identify the UPDATE queries that are resulting in lock escalation for a given table?
  • 12:10 Booked out SQL DBA: Hi Brent, management is hiring a cybersecurity officer and planning to implement the CIS SQL checklist. Some of those rules are no good. I’m planning to partner with this person and encourage cost vs benefit discussion on each rule. Do you have any advice on this approach?
  • 13:45 Eckhart Tolle: Hi Brent, how is meditation going? Can you recommend some resources? PS: Stop fooling around on TikTok and be more in the Now! 1
  • 5:10 Victor Von Doom: Üdvözlöm Brent, Is it a bad idea to parameterize SELECT TOP(@N) when used against large (several million row) tables?
  • 15:30 The exhaustion is real: Do you have tips on time management? I struggle with balancing time with my kids, wife, work, and me time, in that order. I’m at a point in my life where it feels like I can’t go any further career-wise without sacrificing the things that I value more than work.
  • 19:08 Hamid: Hi Brent, what is the most accurate way to calculate space required for tempdb when running checkdb on a multi terabyte database?
  • 19:47 CacheBoy: Hi Brent, my friend was wondering what were your thoughts about SqlDependency and Service Broker for managing cache evictions on the application layer? Any recommendations for him for distributed L2 caches for apps (web site & utility apps) using EntityFramework? Thanks.
  • 20:39 Tobias Fünke: Hi Brent, is the SQL estimated (not actual) query plan ever useful for anything?
  • 21:30 Preben: Hi Brent, can you explain why SQL Server 2022 seem to be mostly focuses on improving GAM/SGAM updates? Do you think this is the true bottleneck they need to fix or do you have a different opinion?
  • 21:50 Ricardo: Ola Brent, are parallelism deadlocks diagnosed / resolved the same as regular deadlocks? Love your sp_blitzlock.
  • 23:06 Trushit: Do you have a recommendation for a T-SQL style guide?
  • 23:22 Martin: Hi Brent, what’s the impact on performance of different isolation levels? Love your new beach episodes 🙂
  • 24:05 Felix: Hi Brent, do you have any book recommendations for learning about all of the possible operators in a SQL query plan?
  • 25:01 Jasmine: Hi Brent, will you be dressing as Disney’s Aladdin for SQL Bits?

Menu