Product has been added to your cart.

Blog

The Top Feature Requests for SQL Server

Brent Ozar
SQL Server
35 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.

Brent Ozar
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

Brent Ozar
Who's Hiring
25 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

Brent Ozar
Production Database Administration
10 Comments

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

Brent Ozar
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

Brent Ozar
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

Brent Ozar
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?

Brent Ozar
Locking, Blocking, and Isolation Levels, Wait Stats
5 Comments

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

Brent Ozar
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

Brent Ozar
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

Brent Ozar
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!

Brent Ozar
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

Brent Ozar
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

Brent Ozar
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?

Wanna Speak at the PASS Summit This Year? Move Fast.

Brent Ozar
#SQLPass
3 Comments

The PASS Summit is November 15-18 in Seattle this year. This time around, it’s a hybrid conference, with both speakers and attendees in a mix between in-person and remote.

I’m really excited to get back to in-person events. I recently attended SQLBits in London, and I actually got emotional at the sight of so many community people that I’ve known over the years. It was just amazing to see people in person again.

Here’s a PDF explaining how to submit a session for the PASS Summit, and here’s the Call for Speakers.

The deadline is March 31. I know. It’s coming up quickly. But block off some time in your calendar to think about what you wanna talk about, and knock out at least one abstract.

The Summit’s new owner, Redgate Software, is making some changes that I really love:

  • For the first time, session speakers are getting paid for their work (not just a free conference ticket, but $250 in person, $125 online.)
  • Pre-conference workshop speakers now get $200 per attendee (way, way up from past events.)
  • Pre-conference workshops will be conducted in-person, but attendees can be either in-person or online. The sessions will be recorded as they happen, and available to attendees for one week.
  • You (yes, you) can submit a session to be Community Keynote.

And Redgate’s being really transparent about what kinds of sessions they’re looking for, and how many will be accepted from in-person and remote speakers:

The call for speakers closes March 31st, and you’ll find out if yours made the cut on June 15-17. Hope to see you in Seattle!

SQLDay Poland Workshop: How I Use the First Responder Kit

Brent Ozar
Conferences and Classes
2 Comments
Watch and learn

I’m presenting remotely at the upcoming SQLDay Poland on 9-11 May!

I’m teaching a one-day workshop, How I Use the First Responder Kit.

You’ve downloaded sp_Blitz, sp_BlitzIndex, and sp_BlitzCache from the First Responder Kit, and you’ve run them a few times. You know there’s a ton of advice in there, but…it’s kinda overwhelming. Where should you even start? What should you tackle first? Are there parameters that would help diagnose your specific situation?

In this one-day course, I’ll walk you through running them on your production server, and we’ll interpret your results together. Space is limited in these particular live classes because I’ll be giving you advice on your specific server.

Here’s how the day will go:

  • You’ll run sp_Blitz to do a server-wide health check
  • You’ll run sp_BlitzFirst to look at your server’s wait stats and storage throughput since it started up
  • You’ll run sp_BlitzCache to find the queries causing your top wait types, using my favorite parameters for deeper diagnosis and trending
  • You’ll run sp_BlitzIndex to diagnose the most urgent indexing issues specifically related to your top wait types
  • You’ll write up a report to hand off to the rest of your team about what you learned about your server today, and what actions everyone needs to take in order to see a performance boost

Throughout the day, as you have questions about the data you’re seeing, you can ask questions and get advice from me (and the rest of the students!) You’ll learn not just from your own questions, but also from the data that the other students are sharing about challenges they’re facing, too.

This course is 100% demos: the only slides are the introductions at the start of the day, and the recap at the end of the day. The rest of the time, we’ll be working in SQL Server Management Studio. Roll up your sleeves and join me!

Register here.

Live Office Hours Next Week

Brent Ozar
Company News
3 Comments

Office Hours - ask me anything.Tuesday & Wednesday afternoon at 2PM Pacific, 5PM Eastern, I’ll be streaming Office Hours live in my Twitch channel. I’ll post the recordings on my YouTube channel.

Post your questions and upvote the ones you’d like to see here.

Here are calendar reminders for Tuesday and for Wednesday.

I’ve been doing these disconnected – recording them whenever it’s convenient for me from the beach or wherever – but Tuesday & Wednesday, I’ll be in San Diego, so I figured I’d do ’em from my home office there. See you in the stream!

[Video] Office Hours: Ask Me Anything About SQL Server

Brent Ozar
Videos
0

Ask me questions at https://pollgab.com/room/brento and upvote the ones you’d like to see covered. I can pretty well guarantee that your favorite question this time is going to be the last one I answered.

Here’s what we discussed today:

  • 00:00 Introductions
  • 00:57 Sean W: New app with data model TBD; what is the thought process one should go thru and questions to be asked to determine if relational or nonrelational DB should be used? Thanks!!
  • 02:42 MyFriedAsk: Hi Brent, My friend server has 1 database and getting and several applications connecting to that database. For some reason they get sql timeouts in the applications on Tuesdays. How can we figure out what causing SQL timeouts?
  • 03:20 Goose: What is the danger zone rate for inserts per second in SQL Server? What’s the highest you have seen?
  • 04:54 i_use_lowercase_for_select: Hi Brent, my friend is looking at your alerting script and wants to implement it. However they also want their system (SSMS/PRTG) monitoring system to get notified using SNMP traps. Do you have any advise on this? PS looking forward to the upcoming live sessions!
  • 05:51 Sohil: Do physical reads vs logical reads ever matter for performance tuning?
  • 07:12 Robert S.: Hi Brent, do you have a Goldilocks methodology for determining the optimal number of inserts to perform in a transaction so that transaction log writes are optimized (i.e. not too few, not too many).
  • 08:23 RenegadeLarsen: What is best Brent…CPUs, Memory or Fast disks.
  • 09:00 Kendra Little: What is your favorite isolation level, and why is it NOLOCK?
  • 09:31 Sqlsucks: Hello Brent, In today’s world where things are changing so fast, what would you recommend to learn new to someone who has 5-6 years of experience with SQL server? I would assume cloud is the future, but please explain where should I focus? Thanks
  • 12:17 Greg Dodd: The leading column of an index is very selective (only around 5 rows for over 95% of the rows) but the stats in the histogram shows 100’s or 1000’s of rows expected. If I rebuild stats, it gets a lot better if I do a fullscan. Thoughts on how to make it better without fullscan?
  • 14:51 Leroy Jenkins: Hi Brent, what’s your opinion on some peep’s advise of “Do not install SSMS on the SQL Server / Do not remote desktop into the SQL Server” ?
  • 16:54 Megurine Luka: Hi Brent, I can run the same query in SSMS 18.x and Sentry One Plan explorer. Sentry one will show residual io bang – warnings that I don’t see in SSMS. Have you experienced this as well?
  • 18:32 ProductionDBA: Hi Brent, I’m very exited for the new SQL2022. I wanted to test it but I’m still new to the SQL world. I was wondering have you ever participated in an early adoption program of any of the old versions and what is expected to do and to test, what feedback you need to provide.
  • 20:59 VISHAKHAPATNAM: Hi Brent, what options should be used to create a new index in the fastest time possible? Use SORT_IN_TEMPDB?
  • 21:58 marcus-the-german: Hi Brent, could you tell us anything about the persisted log buffer or Non-Volatile Memory SQL Server Tail Of Log Caching on NVDIMM? Is it worth thinking about?
  • 24:30 LifelessDBA: Hi Brent, what would be the reason(s) behind creation of statistics on a column in read-only secondary database, while primary database already has a statistics on that very same column?
  • 25:50 Sean C: My (dumb) friend inherited an Azure instance with auto-create idx enabled/drop disabled. The sys created idx have non-standard names and the idx keys,etc are different in each DB. He’s can’t think of a way to implement DEATH. Any thoughts on how to manage/script this out? Thanks!
  • 28:50 Maurice Moss: Do you see same or different reoccurring issues in Azure SQL that you see in self managed / installed SQL Server?
  • 31:38 del-a-nooch: Hi Brent. Long time listener, first time caller. My backup admins are asking if they can exclude DB data files from the daily server backups. I’m taking regular SQL backups (to two different data centres), can you foresee any problems with this approach? Thanks, del-a-nooch.
  • 33:40 Kendra Little: Which breed of dog is SQL Server?

[Video] Office Hours: Morning in Mexico Edition

Brent Ozar
Videos
2 Comments

Got SQL Server questions? Post ’em at https://pollgab.com/room/brento and upvote the questions you’d like to see me discuss.

Here’s what we covered this morning:

  • 00:00 Introductions
  • 00:45 SQL Simian: Everybody loves free advice, but we want war stories. I have heard your 174 indexes story twice now during recorded classes. Got any more? What are the weirdest things you have seen implemented? OR: What are the most exotic places you have visited when you ‘parachute in’?
  • 02:49 CaveOfWonders: MS SQL Synonyms are a new concept to my friend and I (insert Brent’s “A Whole New World” line). We were wondering what your thoughts are on synonyms and if you had any pointers to keep in mind when writing or tuning queries that use them.
  • 04:27 Andrew: For some of our actual query plans in SSMS, the sum of the node costs add up to over 100% (116% in one case). Is this a bug in SQL Server or SSMS? Running SSMS 18.10, SQL Server 2014 SP2
  • 05:40 Rick: Brent, what is your experience with letting the SAN compress large (multi TB) SQL backups instead of doing the compression with SQL server?
  • 07:58 SwedeDBA: Hi! Our application has one database per customer. Currently, we run about 20-30 customers per instance, and three instances per physical server. Would running all this on one instance per server be more beneficial instead, to allow the server to balance resources better?
  • 08:58 Mahir: Brent, besides price, what criteria should we use when evaluating SQL monitoring software?
  • 09:39 Gabriele: If my friend had to monitor 500 remote clients postgres and 500 remote sql server what tool would you recommend?
  • 13:17 Calvin: Hi Brent, when should should you disable a non-clustered index vs drop it like it’s hot? Does disabling free up any space?
  • 14:55 Neil: My company runs 15 merges every minute to sync an online store with the ERP database. What’s the alternative? Can SSIS be used for this? What is SSIS? What is love?
  • 16:38 Drew: SQL is randomly throwing 824 corruption errors on tempDB on a brand new SAN. Vendor says there is no issue with the SAN, what are our next steps?
  • 18:21 Mihir: Hi Brent, We are running into an unusual tempdb contentions in multiple environments where despite having tempdb files equal to number of logical cores (28 in our case) we still get contentions on PFS and GAM page. What else can we do to resolve this?
  • 20:05 Jim: What should we configure MAXDOP to? Our 2019 physical server has 64 total cores including hyperthreaded cores.
  • 20:50 Count Chocula: Hi Brent, is there an easy way to clear the server’s index stats (used by sp_blitzindex) without rebooting the server? Don’t want to failover after making a bunch of index changes just to get updated index stats.
  • 22:14 Neil: Should we get a bigger, faster drive on Azure VM, or build a software raid in Windows with a bunch of smaller, slower ones ? Is there a way to trial azure hardware to test this? We tried the big drive with data, log, tempdb together and it was fast, havent tested raid
  • 23:21 Chris Thomas: Are there levels of Enterprise licensing for a VM Guest vs VM Host? We currently only license our VM, but have sufficient licenses to cover the host (we’re the only VM on the host). I’d like to try HT, but our Infra Manager says that we would need a different license to do this
  • 24:01 Margaret: Hi Brent — We have some Always on clusters with readonly secondaries SQL Server 2016. We run an index reorg daily and quite often that causes a lag in the secondary. Would changing the synchronization to async while the reorg runs, then changing it back help with this?
  • 26:00 Recap and plugging

Announcing Quest Database Training Days 2022

Brent Ozar
Conferences and Classes
6 Comments

Wanna learn for free?

On April 13, I’m doing a session called My Cloud Database Costs are Too High.

You’ve moved to the cloud, and now management is asking questions about your monthly bills. They’ve asked you to cut costs. Now what? In this webcast, Brent Ozar will examine the top expense-causing configurations and workloads for Azure SQL DB and Microsoft SQL Server. You’ll learn how to tell when you’ve got too much hardware, and where you can safely cut costs without sacrificing end user performance experiences.

There are also sessions through April & May from Janis Griffin, Neil Buchwalter, and Pinal Dave, and we finish up with a panel Ask the Experts session. All of the sessions will be recorded, and if you register, you’ll have access to the recordings when they’re available.

Register now for free.

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?