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:
Corrupted: Should DBCC CheckDB be run on secondary replicas in AG as well ? Is it recommended to attach corrupted database to Prod server, to check how your integrity check job will react ?
You should run it anywhere you want to fail over to. Read this.
Khaled Budajaja: Brent Ozar Office Hours is more informative then many hours spent on reading blogs and technical articles. Did you create this? I don’t recall any body else doing office hours. Many thanks Brent
Aww, thanks. It’s definitely not my idea though – it’s common for college professors to have open office hours.
David: Where can I find information about the master and msdb databases similar to what you present in Fundamentals of TempDB?
What are the pain points you’re having with those databases that you need to solve? Leave ’em in the comments.
Bill Hicks still lives: God created the Earth in 7 days. Do you think it’s possible to migrate from Azure SQL Database to CosmosDB or Table Storage in 7 days? Apart from the joke have you ever done that and what are the pros and cons? The goal is to have reports go faster.
CosmosDB or Table Storage for fast reports?!? That is not even wrong.
Unut: Is indexing bit fields like “IsActive” on large tables a futile effort when the large majority of records are active?
Generally, single-column indexes are less effective than multi-column indexes, regardless of the data type.
Khepri: Does FORCE ORDER query hint ever have any practical uses?
Yes, if you’re low on time, and it gets you across the finish line quickly, and you’re willing to live with the technical debt.
Not_a_DBA_but_I_play_one_on_tv: I restore a 600gb database backup to a test server every morning. Typically the restore takes 45 minutes to run. One day last week, it only took 15 minutes. Everything appeared to have run correctly, size didn’t change. What could cause that one restore to run quicker?
Less competition for shared hardware resources.
Unut: What types of issues do you like to use the SQL default trace for?
James Adams: What are the powershell’ish things a senior SQL DBA should know?
I stopped teaching production database administration work years ago because I focus on development DBA work now. I’d start with DBAtools.io. I will note, though, that it’s interesting that a lot of these questions are still about production database administration. I just don’t wanna do that work anymore – I don’t find it enjoyable because it’s basically on-call break/fix stuff. Like, I could go the rest of my life without getting woken up because somebody ran a server out of drive space. It’s 2022. Quit treating drive space like it’s unobtainium.
sqldeo: Hi Brent Any suggestion on tempdb growth,not clearing the space even though developer dropping the temp table after doing ETL stuff SQl 2019, initials size same for all 8 files, auto growth enabled. db are aprox 5 TB in total size, snapshot iso. enable for 1 db. any suggestion?
Attend my Fundamentals of TempDB class.
T: Brent, currently we have SQL Server Failover Cluster with 2 nodes. We are planning to migrate it to Availability Group solution. Do we need to reinstall instances on both nodes or can we reuse it ?
Before going live on any HA/DR platform, you should test it repeatedly. That means you should build a new environment from scratch and test the bejeezus out of it before you go live.
Murad: What is the widest varchar column you would ever consider as a key for a new non-clustered index?
I wouldn’t set limits on that without knowing more about the business problem we’re facing. I can totally imagine scenarios where I’d index a couple of NVARCHAR(2000) columns, for example.
Milind: Hi Brent, how are you? How to do 2.5M+ records update without table lock? It is about account balance limit reset overnight daily. During midnight schedule, other processes are getting hampered due to table lock. How to do such operation without impact and possibly quick? Thxs
Mekhit: Is there a recommended way to identify the hot (or not) columns for a given NC index?
There’s no instrumentation in SQL Server for that. You’ll be forced to use common sense. I know, terrifying, right?
Sothis: What are the signs that a given operator’s estimated number of rows was a hard coded estimate provided by the engine?
Take the estimated number of rows, divide that by the number of rows in the table, and look for suspiciously round numbers that don’t change regardless of the predicates you’re searching for.
Sothis: Is there a good way to determine the appx tipping point (row count) for index seek + key lookup vs index scan operation?
Livnat: Any tips for tuning for “PARTITION BY” performance in TSQL?
Waldemar: Do you have any query tuning / index tuning tips for linked server queries?
Don’t run them. Connect to the server that has the data you want. If you want to get something done quickly, you don’t turn to someone else and say, “Hey, can you ask that person over there to do this for me?” It’s just idiotic.
Nephthys: When passing multiple args to a stored procedure via a single delimited string and using STRING_SPLIT, is there a max number of args you recommend to not exceed for performance reasons?
There are even gotchas with just one row. Read this.
Right Said Fred: What are the top 3 DB communities in order?
That’s a weird question – can you rephrase it for the kind of thing you’re looking for out of a community? I’m not sure if you mean places where general database people hang out, or where SQL Server people hang out, or where questions get answered, or where presentations happen, or what.
SQL Crooner: Can we safely ignore the page life expectancy alerts from Idera SQL DM?
I cover that in detail in my Mastering Server Tuning class. I wish I could do justice to it quickly, but that’s why I have training classes – some topics require deeper discussions and details.
Jose: What is your opinion of the various commercial apps that re-write your queries a hundred different ways to find the optimal query syntax?
Really cool, but usually really expensive.
Nephthys: Does clustered index fragmentation matter for INSERT performance?
Run an experiment with your particular table’s structure, your nonclustered index structures, and your typical insert statement to find out.
CakeAndEatItToo: When running a query through the Actual Execution Plan, is it more important to be looking at time taken or cost%?
Neither. Time to head to my free How to Think Like the Engine class.
Alberto: Hi Brent, I read that default isolation level for SQL Azure DB is Read Committed Snapshot. In your experience, what should we pay attention to when migrating from an on-prem with Read Committed isolation ?
DBGeek: Like you, I use a Macbook Pro as my primary work computer. I use a virtual machine where I install VPN clients etc. But SSMS sometimes behaves really strangely on the Macbook’s screen resolution. What is your solution/workaround to handle high DPI settings?
In your hypervisor, change the Retina settings. Whatever you’ve got it set to, use the opposite one. Then, in Windows, don’t use the HighDPI settings at all.
Reshep: What are the top conventions used by your clients when creating different availability groups (business unit, region, etc)?
Business units and RPO/RTO.
Heryshaf: How do you know when insert contention is happening and what are the common ways of dealing with it?
Review your server’s top wait types. For common ways of dealing with it, check out my training classes.
Pietro: What naming convention do you recommend for clustered and non-clustered indexes? We currently use IX_TableName_Co1_Col2_Inc everywhere.
When you’re looking at an execution plan, why waste valuable pixels on IX? You know it’s an index. Why waste space on the table name? The table name’s already shown to the left of the index name. Just use the columns.
Ptahhotep: Do you have any kind of tab organization strategy in SSMS when many tabs are open?
Yes, save files and close them. The kinds of psychopaths who leave multiple SSMS tabs open are the same kinds of nut jobs who leave hundreds of browser tabs open instead of using bookmarks. They should be hunted down and eliminated before they harm others.
Tenenit: What data profiling queries do you like to run against client tables when identifying new index key candidates?
I don’t. The contents of the data don’t matter – the contents of the query do. For the mechanics of how that works, and why you should never select a table’s data to determine selectivity, check out my Fundamentals of Index Tuning class.
OMC: Is it possible to optimize a SQL table both for high INSERTS and SELECTS or must you pick one or the other?
That’s a great question, and it’s a sign that you’re ready for my Mastering Index Tuning class.
Chris May: How does the optimiser know which set of statistics to use for a given column if there are multiple statistics available? e.g. default system statistics, statistics as part of an index, manual created/partitioned statistics etc
I’ve heard someone say it’s the most recently updated one, but I have no idea if that’s true. I was this close to recording a video of me testing that hypothesis, but I ran out of time before heading down to Cabo for vacation.
Len: What are the pros / con’s of creating a clustered index on an identity int field vs a datetime field based upon GETDATE() ?
The datetime column will take up more space due to its data type size and the fact that SQL Server will have to add a tie-breaking uniqueifier behind the scenes. The clustering keys get added to all of the nonclustered indexes, too, so more space there as well.
Waldemar: What are the top areas of specialized consulting for SQL Server?
Watch this. I explain the different kinds of specialized consulting, what they do, and how to get into those careers. I know it’s kinda long, like 43 minutes, but if you don’t have 43 minutes to learn, well, we don’t have to worry about you getting a career as a specialist, do we? 😉
Horus: What is the killer feature Microsoft should add to SQL table partitioning that would lead to more wide scale adoption?
Why does it need to be adopted more widely? That feature is a pain in the butt. Instead, I wish they could build automatic sharding, automatically splitting data across multiple database servers for faster queries. That’s kind of a pipe dream for relational databases though.