Office Hours Speed Round: Text Edition

Got questions for me? Post ’em at https://pollgab.com/room/brento and upvote the ones you’d like to see me cover. I filter out the ones that are too short for video answers, and here’s the latest batch:

Thomas Franz: When I have a very large table with no statistics (or not on a specific column in the WHERE) and want to show the estimated execution plan it takes minutes because it builds the statistics first. How can I prevent / speed up it (want the create stats asynchron as stats updates)?

There’s no such thing as asynchronous stats creation, only updates. SQL Server needs the stats first to create the plan, or else the plan would literally be based on random guesses.

Kinneret: Is table ordering (Table B appears before Table A) within a view definition ever important for query performance?

Yes, read this all the way through.

SQLSteve: I have inherited a system that has an unusual index setup. Most tables have a clustered index but also have a PK non-clustered index which is the same as the clustered. IndexUsageStats show 0 for some NCI’s but can also show usage up to 50% of the Clustered.

Cool. As long as we’re just sharing stuff we got from others, I got a metal pineapple with an unusual inside compartment:

A metal pineapple - shout out to the fine folks at é

I don’t have any questions about what I have either.

Luca: Are Azure DR nodes more prone to “replica not healthy” errors? Our nodes are in a different region & every ~2 wks we see: Always On Availability Groups connection…terminated / The local replica of availability group … is preparing to transition to the resolving role

My gut feeling is that sysadmins who use the cloud are just more reckless when it comes to network config. They’re more likely to just up and change firewalls, subnet configs, routing, etc and wing it as they go.

Midwest DBA: Hi Brent, Does a Senior Development DBA need to be better at writing T-SQL than developers? Do you need to be some sort of T-SQL Guru, or just know the most common anti-patterns. Also, any good book suggestions on T-sql Anti-patterns?

I think they need to better understand the execution plan ramifications of the T-SQL they write. For T-SQL books, get Itzik Ben-Gan’s.

WhyIsItAlwaysOnFire: Any recommendations to prevent Non Yielding Scheduler errors? Seeing occurrences of this and subsequent thread pool starvation occurring during index maintenance.

Patch SQL Server, Windows, and your hardware drivers/firmware/BIOS.

Film_Buff: Hello! I’ve heard you say that doing sorting in SQL Server is expensive since one pays for licensing, and that it should be offloaded to the app. Is this a difficult task for developers to workout? What are the things to consider in regards to design and implementation? Thanks!

Your developers should be able to sort a list or an array fairly easily. That isn’t something that requires a lot of design planning. You’re overthinking it.

Midwest DBA: Will my SAN admin try and get me fired if I make all Identity columns Big Int going forward? Or is there a case when you can trust the business and set Identity to Tiny INT or INT?

Read how row compression works.

Martin: Our company has 3rd part DBA management. They do one size fits all index maintenance( reorg >5% and rebuild >30%) for all user DBs once a week. Is it better to allow them to do this, or not? They also run a nightly update stats job against all user DBs. Is this helpful or harmful

If you don’t trust your remote DBA firm, I can help you find a better one.

Garry Kasparov: I have a query that calculates Instance wide signal wait time as a % of clock time per period. if (instance wide signal wait time / Clock time )*100 = 100, then I’m thinking the instance would benefit from an additional CPU core. Is this a easy way to determine more cpu?

Without looking at the queries using the most CPU to see if you can tune them? That seems like a pretty expensive script.

DBA douglas: Hello Brent, I’m currently in the USA. I’m wondering if I can move to Europe or New Zealand, and provide American Companies off hours DBA support? Do you think companies would find it valuable to have an American Citizen Working in a different time zone?

Why would their DBA need to be an American citizen?

I_wish_I’d_done_better_in_school: What are the 3 most common issues that clients hire you to fix?

Slow performance (#1 by far), unreliable SQL Servers, and planning for the future.

I_wish_I’d_done_better_in_school: Hey Brent, Do you see many productions DBA’s skilling up in powershell still? Or are there new tools that make this unnecessary? I’m trying to get my team to adopt DBAtools, but my manager says that all the DBAs would have to learn powershell and that is too much to ask.

Be the change you want to see in the world. Consistently solve problems with PowerShell faster than your teammates, and they’ll want to be more like you. (Assuming, that is, that you can – if you can’t, then there’s your answer too, but you don’t need them to adopt it first in order to improve your own skills.)

Greef Karga: What are your pros / cons for generating / executing dynamic SQL in the C# client app vs doing so in a stored proc?

Developers are best at the language they use the most. What language do your developers use the most?

Qaiser: Rolling upgrade or Backup Restore for database migration in AG from sql 2012 SP4 to sql 2019 Enterprise. Some experts say that Rolling upgrade is risky as no rollback option.

Read this.

sqldeo: Hi Brent,I got unusual issue for my tempdb,i have 8 tempfile for 8 core cpu but not growing all equally (initial size and growing size equal across all files)using sql 2019 so traceflag 1117 also not recommended by MS,any solution to look the problem !thanks

Grow ’em out manually.

Seriously, just do it once and be done. Grow ’em out to 25% of the total size of the databases on the server for starters, and just be done with it. Why waste a minute of your life trying to solve this problem? If they continue to grow beyond that size, take my Fundamentals of TempDB class to understand what’s using the space and what you can do about it.

Valdemar: Should stored procs that create local temp tables also drop the local temp tables upon completion?

You can if you want, but it doesn’t really matter, as Paul White discusses in these posts. SQL Server keeps the structure & statistics around for other sessions. The drop only affects your ability to access the temp table after your proc finishes.

WBurgh: Good old MDS, any word on the street on when it’s being killed off? Asking since I’m not seeing anything in the Azure pipeline from MS.

No, but I certainly wouldn’t do any new implementations of it.

LegalEagle: Good Morning Brent! I have an availability group running on physical hardware and wish to add an additional node but add it as a VM to offload reporting. Can I mix a physical SQL with a VM SQL in the same AG? Google doesn’t seem to have information on that. See you at PASS!

Yes, you can, period. See you in Seattle!

Scrootch: My friend is struggling with PAGELATCH on the tempDB, leading to GAM contention. Server spec: 2019 Enterprise, 24 CPU/tempDB files, 320GB memory, 760 batch req/s. Would adding more tempDB files than CPUs have a negative effect to address this issue? Eg adding 32 file vs 24 CPUs.

Check out my Fundamentals of TempDB class.

Maksim: Has moving lob data off row for a given table ever moved you across the performance finish line?

No.

CakeAndEatItToo: The ‘missing index’ dmv is limited to 600 results, but for a SaaSy server with 1000+ db’s there’s a lot of cpu cycles spent cleaning up every couple of minutes. Is it possible to increase the limit or scale back the cleanup or do ANYthing without turning the whole thing off?

I’d be curious as to how you’re measuring that. Post a question on DBA.StackExchange.com with your evidence showing the cleanup of that DMV is causing overhead on your system.

Bill: I have an OLTP system with cache instability, but plenty of cpu to cover it. I had a frequently run proc that runs in <10ms., been stable for years, start blocking, and queries to timeout after 30s., seemingly due to it being Recompiled. indexstats are current. Where to start?

Run sp_BlitzCache, look at the Priority 1 warnings, and read the URLs in each of those.

Kinneret: Have you ever written an epic SQL query only to see SSMS freeze up or crash?

Click File, Save. Save your work every few minutes. No different than the advice I’d give to anyone using any computer application, ever.

Mollusk: What are your thoughts on running SQL HA technology like AG’s or a failover cluster instance in a public cloud. Issues are that VMs could be vmotioned (a lot). How will this affect auto-failovers or cluster health? Can I just set healthcheck and timeout settings higher?

I haven’t done hands-on AG work in years, sorry. I got out of production DBA work because complexity kept rising, fragility kept getting worse, and I kept hitting weird undocumented problems. I got tired of being on-call to fix that kind of thing.

J Katz: How to get a job starting out with perf tuning? Im a Senior infrastructure DBA, and I want to get experience perf tuning –I love your courses BTW, best investment ever!. Does Microsoft have positions like this? Should I expect to take a pay cut?

Use the Buddy System. Call people in your network who know you know your stuff. They’ll get you past the stack of resumes. Generally speaking, Microsoft doesn’t tune the T-SQL or indexes that runs on their customers’ servers.

DataPayload: Hello Brent, My friend understand that 1=1 help to comment out and 1=2 in create table without data in where clause. He is not sure if these helps the query logic or plan any other way. Can you please help him. Thank you.

Read this.

SQLServile: Our server is to be replaced with a newer one, which is fine. The staff doing this though, say it will be “too hard” to retain the original servername in this exercise. A new name will break countless connections (but hey, not their problem). Is there a less disruptive way?

They’re right. Instead, immediately after migrating to the new server, rename the old server as something else, like PROD_OLD. Create a DNS CNAME (alias) for the old name, and have it point to the new server’s name.

Yousef: Is it ever OK to specify an additional / redundant WHERE condition value to get a better index selection?

I would want to see an example.

Dan Griswold: Should Managed Service accounts like NT SERVICE\…. be disabled for security purposes? Why or why not? I have searched all over and can’t seem to find a clear answer.

I don’t do security work.

Raguel: Do you know of anyone that has ported the Stackoverflow or AdventureWorks DBs to other platforms (PosgreSQL, etc) for bakeoff performance testing purposes against SQL Server?

Read this.

Galina: What are your pros/cons of streaming on Youtube vs Twitch?

In my experience, Twitch pays better. When I was streaming regularly, I was getting $500-$750/month from Twitch, where as I don’t think I ever cracked $200/mo from YouTube.

Stacey Q: What is the largest DB you have seen that had enough server memory to cache the entire DB in RAM?

About 1.5TB. I’ve seen much larger databases, but they just weren’t cached in RAM.

Shaheen: How does index tuning for data warehouse queries differ from index tuning for OLTP queries?

These days, data warehouse tuning tends to start with clustered columnstore and go from there. OLTP tuning starts with multiple rowstore indexes.

Don’t Blame Anthony: How does SQL server determine row order for “Select Top” or “Delete Top” when no order by clause is specified? I see this pattern frequently.

Whatever rows the query plan pulls first, that’s what comes out. Order isn’t guaranteed without an order by. It can change based on parallelism, available indexes, merry-go-round reads, all kinds of stuff.

Stockburn: Hi Brent, company has 600K+ databases in Azure SQL DB. How would you change your perf tuning methods, investigation when faced with so many DBs. They all use a similar schema but may be used more / less heavily. We have been using query store, interested in your advice.

Read this.

Marc Spector: Sometimes when we run sp_whoisactive we see long duration sessions from our .NET apps with a sql_command of “sys.sp_reset_connection;1” and status of “dormant”. What causes this? Is this anything to be concerned with?

Read this.

TheCaptain: Hi Brent, Some developers at my organisation have written a module to encapsulate some common client parameters, making them optional and default: timeout=0, connection_timeout=0. What’s your opinion and do you know of any articles with guidance on best practices?

No.

MikeNM: I was watching your talk about debugging stored procedures, and your laments about the death of debugging in SSMS. Have you worked with SQL debugging in VS Code? If not, what do you recommend?

I use the technique I describe in the video.

Cats_Everywhere: Brent, Do you know any DBAs who have taken advantage of the WFH movement to move to a low cost of living city/area? Any suggestions? Must have great restaurants!

Yes, but it’s typically been to move closer to family.

Tefnut: When SQL connections are re-used via .NET connection pooling, does the sproc author need to take care to reset connection level settings that were enabled in the executed stored procedures? Ex: At the end of the the sproc do SET NOCOUNT OFF, SET XACT ABORT OFF, etc.

No, but remember that the procs might be called by other things, not just .NET.

Yourbiggestfan: Hi Brent, Can you point me to any website/blogsite which has performance tuning challenges/exercises for SQL Server where we can test our knowledge? Your invisible indexes challenge is the kind I am looking for.

Yes, click Training at the top of the site.

Emelio: What is the best book on Data Warehouse Design?

I have no idea.

NotCloseEnought2RetirementToStopLearning: Hi Brent My shop has been mainly SQL 2012 and 2016 (nagging my manager to upgrade). Can you recommend key\cool features released in SQL 2017, 2019 and 2022 that I should focus on so I Iook super smart when we do upgrade? One feature per version please.

Read this. 

Taweret: What are the top rules / guidelines from older SQL Servers that you see used on new SQL Server versions but shouldn’t be?

To monitor Page Life Expectancy and to lower Fill Factor in order to reduce fragmentation.

Khonsu: What are the pros / cons of creating NC temp table indexes inline vs a separate step after table creation?

Read this and/or attend my Fundamentals of TempDB class.

Steven Grant: Do you have a recommended sample DBA calendar that shows admin functions to perform daily, weekly, monthly, yearly for SQL shops with little previous DBA structure?

No. Neat question, but no.

James: Hi Brent – With the non stop surge towards cloud, companies desiring and hiring more towards BI/Data Teams/Engineers etc, and being adamant that we are not needed, is it time to accept the inevitable that all respect and understanding of a DBA is gone and we need to move on?

Yes, as you can tell by the complete lack of database questions above, there’s no work left to be done. You should move on. You go on ahead without me. Best wishes, hugs, thoughts, and prayers.

Midwest DBA: Who is the Brent Ozar of Data Warehousing? Do you have any recommendations on training resources?

Data warehousing is being reinvented, and there are tons of approaches. For example, check out James Serra’s video on what a modern data warehouse entails:

Previous Post
Office Hours: Dodging Work Edition
Next Post
Who’s Hiring in the Database Community? August 2022 Edition

10 Comments. Leave new

  • A metal pineapple, that is just too funny hahahaha

    Reply
  • Just a thought on your answer to dba Douglas: a lot of USA companies will only hire people with rights to work in the USA (true for many other companies too). I assume it’s a HR/legal thing. As someone without the right to work in America, I can’t apply, even for a 100% remote job. I would imagine being a citizen would give you that right, even living remotely.

    Reply
    • Chris Wilson
      July 30, 2022 3:07 pm

      Be aware that US citizens working remotely in other countries has implications for the company as far as taxes and other things. Be completely up front about it as you don’t want something like that to come back and bite you down the road.

      Reply
  • James Carroll
    July 30, 2022 5:26 pm

    Hi Brent, Perhaps I misunderstood this reply.

    Taweret: What are the top rules / guidelines from older SQL Servers that you see used on new SQL Server versions but shouldn’t be?

    To monitor Page Life Expectancy and to lower Fill Factor in order to reduce fragmentation.

    Are you saying PLE is not important? I agree with the the caveat not to lower fill factor, but PLE is still very important in my opinion. If you disagree would you please explain your thought process?

    Reply
  • Edward Miller
    July 31, 2022 6:33 pm

    If your developers are not “able to sort a list or an array fairly easily” then at least one of these two things is true and perhaps both are true: 1) you have the wrong developers, 2) you work for the wrong company.

    Reply
  • Edward Miller
    July 31, 2022 6:43 pm

    “Why would their DBA need to be an American citizen?” In my consulting days I did work from companies like Honeywell Aerospace. Lots of their data that relates to government contracts can only be viewed by U.S. citizens. No security clearance needed, just U.S. citizenship. Anybody who is doing work in the military industrial complex is going to have this requirement for some/most of their projects. Now, these companies don’t typically hire independent consultants but rather larger consulting firms that can offer a broad range of expertise because these consultants are coming in to figure something out the $200K+ W-2 employees are struggling with and needs fixed post haste. So moving to the outback, hanging out your “off hours DBA” shingle, and hoping for these guys to come knocking sounds like a bad plan to me. Those large firms have 24/7/365 support.

    Reply
  • Hi Brent – thanks for taking the time to answer my question, I have been a fan and student of you for some time and appreciate all the time and effort you put in over the years. I wish my first ever question was technical one rather than a more abstract but I have to say I was somewhat disappointed with the rather brisk response to a concerning trend in the market, maybe you thought I was agreeing with them?
    Having been a DBA for 15 years and worked for many companies not only do people in IT, IT management and business owners think this job is an irrelevance now (last 5 years particularly vocally) but also I am seeing document proposals of architecture stating as such being signed off by employers.
    There is no point preaching to each other how ridiculous this idea is and how empirically it is untrue, however this is one the attitudes of our time….I appreciate this will never affect someone like yourself but it does concern me about the future and future employers.
    Once again, really appreciate everything you do.

    Reply
    • Gotcha – can you rephrase your question then in a way that more accurately reflects what you’re looking for from me?

      Reply
    • The reason I say that is because I’m not sure that you actually have a question here – it feels like you’re looking for a platform to make a statement, or you’re asking me to make some kind of statement.

      Office Hours is definitely here for you if you have a question – just ask it in the form of a question.

      Reply

Leave a Reply

Your email address will not be published.

Fill out this field
Fill out this field
Please enter a valid email address.