Some people say that the overhead for having nullable column is 1 bit per row, some that it is 1 byte per row Which one is true ? I assume is 1 bit per row (so 8 rows make 1 byte), am I right ?

The null bitmap is stored at the row level so 1 byte is needed for each 8 columns of every row. The overhead is incurred for each row regardless of whether the column is defined as nullable or not (since SQL 2000).

For NULL values in varchar, there is still 2 byte overhead per row - is it actually true ?

Column offset is stored for NULL variable-length columns except for the last one(s) so NULL values in variable length may incur a 2-byte overhead depending on the position.

Most important question: I have a table with ~250 million rows of data, total size is ~115 GB I've added 7 columns using below code, checking the size of the table using sp_spaceused after each command

This is where it gets interesting. SQL Server Enterprise Edition (or Developer Edition) includes optimizations such that some schema modifications are only meta-data operations. If you run the same test on Standard or Express edition, space will double.

See https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-record/ . Although old, most still applies to current SQL Server version, including Azure SQL Database.

If rows which cluster together get updated together, that could result in fewer full-page-images, so it would be expected to decrease WAL size. But moving checkpoints further apart would also do that, and wouldn't generate the massive spike in WAL that the CLUSTER operation itself would do.

You can use pg_waldump to inspect some WAL files. It doesn't have a mode to generate per-object stats, so you would have to use something like Perl to parse and summarize. The records only indicate objects by their relfilenodes, so you would also need to do something to convert them back to names.

But you should probably tackle the problem more directly. What are the problems you are having? Raw size of WAL? Transmission over network? Random reads needed to fetch the pages which need to get updated during replay?

As with all denormalization decisions, it boils down to two things:

1. How important is it to optimize the query performance? If the query with the JOIN provides satisfactory performance, then use that, because the database will enforce its own data integrity to prevent update anomalies. However, if the difference is performance is great enough that you definitely cannot satisfy your performance objective if you use the JOIN solution, then you must use the denormalization.

2. Do you have a robust plan for accounting for the data anomalies? You correctly identified that you could have a challenge if category names change, because you need to remember to update them in multiple rows. How confident are you that you can write the code perfectly to do that? Alternatively, do you have a plan for detecting and correcting anomalies that occur if updates fail to update the category name in all necessary places? Do the updates and the correction code account for concurrent updates?

I understand that index rebuild requires schema stability (Sch-S) lock which means it will block any query that tries to do the schema modification (Sch-M).

Does an offline index rebuild of clustered or non-clustered index also block user's SELECT/UPDATE/INSERT/DELETE queries?

The database server cannot read one of its own files, which should never happen. You have to fix that condition.

Probably causes for that are:

• You are running a virus scanner on the data directory that locks the file. Don't do that.

• You changed ownership or permissions on that file. You should never modify anything in the data directory.

The procedure tries to ensure that the backup pieces available can be used to restore the database.
I don't know if there's any equivalent for PostgreSQL.

However, the only way to be absolutely certain that a database can be recovered is ... to restore [a copy of] that database.

Just because a procedure says that everything is OK is no substitute for proper Disaster Recovery Rehearsals wherein you actually restore the database to

• Definitively prove that the database can be restored, and
• Remind you how to do the restore because, hopefully, it's not something that you'll be doing on a regular basis, otherwise.

Sql server recommendation is to decide max dop based on number of processors in a single Numa node.

Given a system with 4 Numa nodes, each having 6 processors, the max dop recommended hence would be 6.

Why is max dop related the number of NUMA nodes?

In MySQL,

Linear read-ahead is a technique that predicts what pages might be needed soon based on pages in the buffer pool being accessed sequentially... if you set innodb_read_ahead_threshold to 48, InnoDB triggers a linear read-ahead request only when 48 pages in the current extent have been accessed sequentially.

In the default setting, an extent contains 64 pages. So the prefetch mechanism will "asynchronously read-ahead the entire following extent" (i.e., 64 - 48 = 16 pages). The assumption here behind linear read-ahead is that the remaining 16 pages are very likely to be read after 48 sequential page reads.

Qestion

Without prefetching, even though the remaining 16 pages are going to be read next, MySQL can just read them sequentially. This should have the same performance as using prefetching. So what are the benefits of linear prefetching? Only when the remaining 16 pages may get randomly accessed can make it meaningful?

Thanks!

There is no dual-password feature in MySQL 5.x.

At my last job, we used MySQL 5.x, and authentication was done using SSL certificates instead of passwords. The authentication was satisfied if the certificate had an ISSUER property indicating it was created by the certificate authority owned by the company we worked for. But the certificate itself had an expiration date, so we had to update it periodically and redeploy the certificate to the client application.

As long as the same ISSUER was used for the certificate, either the old or new certificate would authenticate a user. So you could generate a new certificate and deploy it with the application anytime you want (before the old one expired). There was no downtime required.

This solution works for your needs, but it would require you set up a certificate authority (CA) for your company so you can generate secure certificates.

See https://dev.mysql.com/doc/refman/5.7/en/create-user.html#create-user-tls for reference on using SSL/TLS options.

My storage admin says that our SAN has 1 storage pool (set of physical RAID drives) that is shared by various servers and end-user vms.

I want to install a new SQL server instance on a volume created from this SAN. I was thinking that it will be nice to have a new dedicated storage pool only for the sql server. However, my storage admin has shared the below information (from Dell documentation) and is insisting upon creating the new volume for the sql server from the existing storage pool.

In general, it is recommended to use fewer storage pools within Dell EMC Unity arrays because this reduces complexity and increases flexibility. Dell EMC recommends using a single virtual disk pool when implementing SQL Server. This provides better performance by leveraging the aggregate I/O bandwidth of all disks to service I/O requests from SQL Server. A single drive pool is also easier to manage, allowing an administrator to quickly and easily adapt the storage system to satisfy the ever-changing workloads that are common in SQL Server environments. Before creating multiple storage pools to separate workloads, understand the various Dell EMC Unity features that are available for managing and throttling specific workloads.

Does anyone have experience with such a situation?