[Video] Office Hours with a Special Guest: Count Distinct
I was busy, so I asked a friend to fill in for me and answer your top-voted questions from https://pollgab.com/room/brento. He did a pretty good job:
Here’s what we covered:
- 00:00 Start
- 00:52 Confused: Who uses differential backups, really? I don’t get the point.
- 02:20 Chris: Are third-party tools a necessity or a luxury for managing SQL Server?
- 03:26 SwissDBA: Statistics can only store 8kb of data, but often it would useful it it could store more info about that table. Can we make stats bigger and would this be a good idea to do?
- 05:29 Stockburn: Hi Brent, any advice on running sp_blitzindex against a 1TB db with over 60000 tables and over 130000 Indexes. I have tried but it never finishes. Old Microsoft Navision environment. As always love what you do, cheers!
- 07:06 Pat: Which team should the DBA be in: devs, sysadmins, devops, or something else?
- 08:06 Indara: Is query store beneficial / necessary if you have third party SQL monitoring software?
- 08:52 Tobin: Does Microsoft ever watch office hours / read your blog? Any resulting changes?
- 10:52 DBANoob: Is it possible to perform transactional replication from one HA listener to another HA listener? Before you make fun and ask why, just know we are aware of how crazy this may sound and I don’t have enough characters to explain why we need to do this. Appreciate any input on this!
- 11:29 TheyBlameMe: Online index creation on a big table in primary DB causing transaction log to max out at 100% due to slow transfer over wire to an AlwaysOn ReadOnly replica in a different geo-location. How can this be better managed, mitigated? No hickups primary, online index duration secondary
- 12:33 End Try Begin Cry: We need to test our response to checkdb finding corruption. Is there a way to intentionally corrupt a database in various ways?
- 13:22 FloydianDB: How can I convince our head of the company that adding columns in a certain position in the table is a bad idea. He won’t listen and I’m tired of doing create/drop table statement and re-adding the data back in.
3 Comments. Leave new
AG to AG transactional replication is possible. It is even possible with a highly available distributor. It takes how terrible standalone replication is normally and magnifies it several orders.
“Stockburn: Hi Brent, any advice on running sp_blitzindex against a 1TB db with over 60000 tables and over 130000 Indexes. I have tried but it never finishes. Old Microsoft Navision environment.”
Navision has many tables per company.
Can they delete companies not in use? I have seen users with multiple test companies which Navision allows you to copy inside the same database.
or archive them to a different database and reconnect the companies to that archive database.
I have gone through that before, mainly a lot of duplicate companies from failed company setup attempts – the problem is that if you have a bunch of inactive companies. In our case it was the constant confusion of new accountants and developers not knowing which companies to use.
Usually most of the indexes in NAV are managed by the application. Some of them are mandatory keys the application has to have, and some of them may be custom keys someone over time had used to speed something up that may no longer be needed, but you can’t do anything about them, the ERP administrator would have to remove them in NAV.
For Brent’s point, for the indexes the ERP system created, you can’t do anything about them as a DBA. Missing indexes can be figured out with blitzCache and if you are worried about other index problems as you create new ones outside of NAV, you can run blitz index on individual tables.