Not all of the questions y’all post at https://pollgab.com/room/brento require long, thought-out answers. Some are just one-line specials, like these:
F’legend: Hi Brent, in reply to a question talking about database restores for 1TB+ you also touched on scrubbing or synthetic data as a way to populate dev environments. Are there any resources/tools you would suggest for these approaches?
Read this and the comments on it.
On Another Beach Somewhere: I am using multiple instances of SQL on the same VM. Brent says that’s a bad idea… lets pretend I have a good reason. How does Min/Max Memory come into play? Can I set both instances to the same “Max” and let them compete for memory or do I need to allocate a portion to each?
Congratulations. You just discovered one of many reasons why I told you it was a bad idea. Just wait til you discover CPU contention.
Steph: Hello Brent, is there a reason why SQL Server doesn’t seem to have shortcuts to compute stats on columns with unique constraint ? Why scan all rows when the density is always 1/count(*) and histograms seem useless ? This confuses me. Thanks.
Because some queries will do range scans, as in, show me all IDs between 1000 and 2000. SQL Server needs to know how many IDs exist in that range – some IDs may have been deleted. The only way to find out is to read the data.
Darin Webber: Hello Brent!! I need to change a datatype of 2 columns on a very large table.What would be the best approach for his operation? Creating 2 new columns and copying the data and then dropping the old ones, or there is another better approach? Will dropping the columns cause downtime?
Tayyip: Is there an ideal index usage stats age band for when it’s safe to make decisions re: dropping rarely used NC indexes?
For me, if an index hasn’t been used in 32 days, it needs to go.
Don’t Blame Anthony: What is your opinion of using Pure FlashBlade to speed up SQL backups and restores?
I haven’t used it myself.
Daniel: Can you please please break down steps and the process of consuming data from an API (JSON format) using SSIS and then uploading that consumed data into a SQL Server database? Thanks!
I don’t use SSIS.
Bad DBA Taylor: We built a devops repo to deploy SQL permissions for service accounts, but due to the high volume (15k/batches/s) the release pipeline gets deadlocked on a very simple CREATE USER statement causes a massive SCH_M lock chain. Our security cache is 12gb. Could this be the problem?
If you think it is, try to flush that cache first as part of your deployment scripts. (I have a feeling it isn’t, though.)
RoJo: I want to add AG offsite. Can I use 2016 on-site and 2017 or 2019 offsite?
I would not do that, no. Failover would be one-way – you couldn’t fail back.
TeeJay: We recently had to do an unplanned over-the-top SQL upgrade from 2014 to 2017 in our prod environment (yes, bad, I know). I now have a bunch of fires to put out. Are there any obvious first places to look for new performance problems (like the cardinality estimator was for 2014)?
Indeed there are! Check out my Mastering Server Tuning class.
Tayyip: What are the pros/cons of working as the sole SQL DBA vs working on a team of SQL DBA’s?
You can learn more, more quickly, by working with others. You also have to put up with them.
Alex: Hi Brent, You look like someone that is in pretty good shape. How do you balance your Work, Health & Mind in what can be a sedentary job. Especially because of Covid alot of people are not having to go into the office any more?
Seshat: What are your thoughts on using SQL startup option -E for index rebuild / read ahead performance across multiple data files?
Never heard of it.
Sekhmet: What is your experience / opinion of using NVDIMM / PMEM as a high speed storage for SQL Server?
Don’t Blame Anthony: How do you deal with undetected write errors in SQL server?
Detect them by running DBCC CHECKDB as frequently as practical – even overnight if your business windows allow it.
Anatoli: What interesting tools do you know of (besides SSMS / Sentry Plan Explorer) that will parse SQL query plans?
Quest Spotlight Tuning Pack is another one.
DBAInProgress: Hello Brent. What different screencast softwares have you used to overlay yourself onto your screen with audio?
Piotr: What are your favorite DMV’s in Azure SQL DB?
I don’t have a separate set there – I try to use common ground between both Azure and SQL Server.
Fyodor: Do you have any guidelines for maximum number of columns to allow for the include columns in a NC index?
Yes, and we discuss them in my Mastering Index Tuning class.
Mahesh: When Sql is migrated to cloud will there be a change to RTO and RPO .
Yes.
DanishDBA: Hi Brent. My friend is going to implement logging requirements from customers. They want to see who changed a column value, date and old/new value. Is System-versioned Temporal Tables the recommended solution when performance is the main concern? What are your thoughts on this?
If performance is the main concern, only log the specific columns you care about. For example, you probably wouldn’t want to monitor high-velocity columns like QuantityInStock. When you only care about specific columns and specific sources, triggers are likely to be faster, and generate less transaction log activity.
Skrillex: How do you verify that it’s safe to upgrade DB compat mode to latest version? 2014 to 2019 in this case.
Urquan: What percent of your clients license SQL by the server / core vs Microsoft Assurance?
I don’t track that. I don’t sell licensing. I don’t even ask clients how they’re licensing it.
Guðmundur: For query perf testing, can you hint DB compat level at the query level vs changing for entire DB?
Piotr: What is your opinion of external tables in Azure SQL DB? Do they perform any better than linked servers in on-prem SQL Server?
Never used ’em.
Elijah: Are there workflow orchestrators that you recommend as an alternative for SSIS? SSIS seems to have pain-points around metadata getting out of date, difficulty to review as code, and being difficult to debug for large workflows. However, nothing else seems to work with SQL Agent.
I don’t work with SSIS, so I’d post that on #SQLHelp.
Anatoli: What are the ideal display columns / column order when running sp_whoisactive to triage a server performance fire?
The defaults. Learn to use the defaults so that when you parachute into a server you’ve never seen before, you can work quickly.
Chenjesu: Do you see any interesting SQL alerting mechanisms at your clients other than email?
No.
Bryan Flores: Hello Brend, do you recommend sql server replication in 2019 or 2022, but do you recommend instead of replication
Come on, Ryan, that’s like asking if I recommend hammers. What’s the problem you’re trying to solve? Don’t ask if you should use a tool – describe the problem you’re facing, and then we can pick the right tool, Mr. Flowers.
Yehat: How do SQL DB snapshots compare with SAN snapshots as a potential rollback mechanism for a failed application deployment / update?
Most of the clients I work with have some kind of high availability and disaster recovery mechanism like log shipping, AGs, or SAN replication. You can’t roll back a database snapshot without affecting everything downstream. SAN snapshots have similar problems, but if you’re also using SAN replication for DR, the syncing back & forth is way easier.
The Swedish Chef: How do you project costs for hypothetical Azure SQL Managed instance against hypothetical SQL in Azure VM? Need to figure out who wins a bakeoff and migrate from on-prem.
Start by exactly quantifying your per-server costs to build, manage, and troubleshoot Always On Availability Groups for a single cluster. If you can’t do that, a true cost comparison is hopeless. (Hint: it’s hopeless.)
Anatoli: What is your opinion of the TSQL MERGE statement? Do you have any performance tips when using it?
Sajawal: Please don’t say NO. How can we read an execution on Azure Synapse Analytics? Please help us in that world as well.
I’ve never used Synapse Analytics.
Yitzhak: Do SSMS live query stats ever get you over the performance finish line?
Yes, but read this.
Vilhelms: When do you like to use sp_blitzwho vs when do you like to use sp_whoisactive?
I answer that one in this module of my How I Use the First Responder Kit class.
Guðmundur: Which SQL locking model is easier for developers to understand and code? Optimistic or pessimistic locking?
Optimistic.
SQL_RAGE: How does referencing a column by an alias versus the column name influence SQL’s decision on which execution plan to use? Seeing SQL use a bad plan for a query using the aliased column column name in an ORDER BY, but uses a good plan when using the column alias.
Odds are, it’s unrelated to the alias, and related to optimization timeouts. I explain those in my Fundamentals of Query Tuning class.
Ramil: Hi Brent! App controls a lot of things like amount of database files-only one, indexes can be created only via app, partitioning is not allowed. How can I scale up horizontally in this case? because DB is growing 250gb per month. OLTP DB, currently 1.8 TB, in AlwaysOn AG.
I think you fundamentally misunderstand scaling terms. Scaling up vertically refers to adding hardware. Scaling out horizontally refers to adding more servers. In either case, this is pretty far beyond a free Office Hours question – feel free to click Consulting at the top of the screen before you make a multi-million-dollar licensing and hardware architecture plan.
Fyodor: Would you consider blogging a list of your top 10 most watched Ozar You Tube videos?
No, because people who aren’t smart enough to go to my YouTube channel, click Videos, and click Sort By aren’t smart enough to hold down database jobs anyway.
8 Comments. Leave new
Ouch. God bless you.
Mr. Flowers make my day!! Thanks Brent!!
I love how you mispelled Bryan since he called you Brend. I didn’t notice It at first glance, but then It killed me ?. Reaching new levels of roasting ?
@The Swedish Chef: You can use the Total Cost of Ownership (TCO) Calculator (https://azure.microsoft.com/en-us/pricing/tco/calculator/). This will help you forecast your costs in the Cloud.
@Bad DBA Taylor (the guy with 15k batches/s and schema locks because of a create user):
You should redo your security.
– create database roles and grant the permissions to it
– create Active Directory groups where you put in the users, who need to access the database(s)
– create a login in an user for those AD-Groups and add it to the database role
– do not grant permissions direct to the user for the AD-Group, because you may need some special users (as service accouts) or want several AD-Groups have partwise similar permissions (e.g. you could have a group for basic access, for advanced and (almost) full access; this way you would grant the basic permissions only to the basic role and put the advanced / full AD group user into the basic group too)
When you do so, you should never need to run a CREATE USER statement again, have a much more straight forward security concept and no problems when a new employee is hired (just put im into the correct AD-groups and he has all the permissions he needs)
How do you handle row level security with that solution?
I did not test it (we aren’t using row level security), but IS_ROLEMEMBER(‘db_my_own_role’) should work in a security policy too.
And USER_NAME() / SUSER_NAME() still returns you the name of the domain account (e.g. my_domain\thfranz), so you could compare it to e.g. the “owner” of a record
@Yehat, based on my personal experience, I would not trust native SQL Server database snapshots as a rollback option for a deployment in production. If the snapshot file runs out of space, get corrupted or hit the NTFS file fragments limit, your snapshot will become suspect and unusable, you’ll be in serious trouble if the snapshot was your only way to rollback.