SQL 2016 AOAG. 3 node cluster. Primary and 1 secondary on-prem configured with synchronous availability mode. 1 node in Azure using asynchronous availability mode. We are using AOAGs to move data to the Azure node. The data and log drives on the Azure node were configured with 64k block size per best practices. Older on-prem nodes disk and log drives were configured with 512 blocks. Soon after the Azure node was added to the AOAGs we started seeing multiple entries in the Azure SQL Error log like... "There have been ###### misaligned log IOs which required falling back to synchronous IO. The current IO is on file F:\mssql\Log\xxxxxxxxxxxxx_log.ldf. " I now understand this is a result of the physical sector mismatch between the on-prem and Azure nodes (512 vs 64k)... We then followed the recommendations of enabling trace flag 1800 and expected those log entries to diminish. However, they have not. log_send_queue_size and redo_queue_size values on the Azure node are very low. Since the Azure node will eventually become the primary when applications are moved to Azure... Should we keep the 64k block size, or re-format and start over with AG synchronization ?

I have different folders on different machines and I need one logical name (alias) for them both, sure one on each server. I need to create one logical directory in several servers with the same name, but their paths in actuality are different. So I can create a logical backup device with a path, which is like this c:/myfolder/backup.bak and all the backups initiated to this device will be appended in the same file. But I need to create it for the c:/myfolder directory (aka folder) without specifying the file backup.bak. so it will reference to a folder and not to a file, so I can create different backups in the folder with different names and separate files.

In PostgreSQL there is alignment padding of various datatypes which can increase (or decrease) the size of records (as stored on disk) in varying ways (see these links, 1 , 2 , 3 ): I searched for "Oracle alignement padding" (and also for SQL Server, MySQL and FirebirdSQL) and various combinations of these words and came up with one link of interest for Oracle (C struct related). For the other systems I found nothing of interest (mostly stuff like LPAD()/RPAD() or other presentation related material). I did find this by Eric Raymond - generic C struct material again. http://www.catb.org/esr/structure-packing/ which is also about C structs Note: **"should generalize to any compiled language with C-like structures."** (end of section 1. - Who should read this.) For a shorter read on the same topic, see here . Providing a "behind the scenes" remedy has been discussed (logical vs. physical order) on the PostgreSQL lists but has gone nowhere - see the discussions here and also this answer . So, as I understand it, this alignment issue is *_fundamental_* to C/C++ (and all compiled languages with structs - esr) and to the best of my knowledge, the kernels of all the systems I searched for are written in various combinations of C/C++. However, only PostgreSQL appears to be discussing the issue but not other systems. My question is that I'm wondering have all the other mainstream systems have implemented some sort of column rearrangement behind the scenes? I'd be interested in hearing how other systems have mitigated any problems caused by this if they haven't done some sort of column rearranging? EDIT: On searching further, I have found these two posts (1 , 2 ) about marshalling of C structs (which, if I've understood correctly) are the way that records move from disk to memory and back - the similarity between a C struct and a record structure being reasonably obvious. So, and this is again AIUI, you can either store C structs (or records) in a sort of way that directly maps to the processor memory alignment requirements (and "wastes" space) **OR** the struct can be marshalled (or serialised - v. similar) as a bit (or byte) stream and stored that way (less "waste" but more processor intensive). **But** this poses a further problem! The developers of PostgreSQL are very adept at C programming and would know about these techniques - so why would they not have done what (**apparently** AFAICS) other systems have done and removed this overhead?

We are currently using MBR partition type with our SQL Server in production, but as our data volume increases, we are facing issues with our data drives getting full. We can't extend them because they are MBR, which means we have to either stop SQL Server and move some files or add a new file to the database. We are considering switching to GPT partition type, but we are not sure if there are any potential problems that we need to be aware of before making the switch. Has anyone else used GPT partition type with SQL Server in production? If so, have you experienced any issues? We would appreciate any insights or experiences you can share with us.

Is it possible to safely change the drive letter for a volume which holds only system databases? What precautions should be taken and how should it be done? I know I can just go to computer management > storage and change drive letter but can have negative consequences on SQL Server operation?

I have a three nodes SQL Server Alwayson cluster on Azure VM. I need to expand the data disk on all three nodes. The disk is a stripe disk build during VM configuration. Based on this: https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/storage-configuration?tabs=windows2016#existing-vms I should exted the disk following instruction. But in my environment Azure prevent me doing this as the storage is part of an HA solution. How can I expand the disk without dismounting the HA solution?

I have a 2 TB database and the Data Drive has filled up. My network team cannot expand the drive nor provide another drive so that the secondary data file can be pointed towards that. My question is can the secondary drive be pointed towards a network share .

Hello I would like to know which is the correct way ... or the most recommended way to present the disks to ASM, for example: I have 3 disks mapped to a server (rhel7) orasys, orafra, oradata, with dm-multipath I configure the multipath and I put aliases (orasys, orafra and oradata respectively). The doubt I have from there, I have read in the guides, that the disks are formatted, leaving for examples: orasys1, orafra1, oradata1 and these are the ones that are presented to ASM as /dev/mapper/* and with the respective permissions ... but when I do it that way it gives an error finishing the installation of the grid because it cannot create the diskgroup, however if I do not format them, that is, I present them directly as orasys, orafra and oradata I have no problem. The database is oracle 19c and I don't use asmlib but udev rules. Thanks.

We have recently migrated our database mirroring secondary to new hardware. The log drive (and other SQL Server drives) on the secondary has been formatted to 64KB block size, whereas the primary server is on the default 4KB. As a result of this we are getting the following entries in the log of the secondary almost constantly - There have been N misaligned log IOs which required falling back to synchronous IO. The current IO is on file L:\SQLSERVER\Logs\MyDatabase_log.ldf. this article suggests a fix is to turn on trace flag 1800 on the server > with 512-byte sector size as per Microsoft's recommendation I have run the following command on both servers: fsutil fsinfo ntfsinfo L:\ and can see for the primary: Bytes Per Sector : 512 Bytes Per Physical Sector : 512 Bytes Per Cluster : 4096 Bytes Per FileRecord Segment : 1024 and the secondary: Bytes Per Sector : 512 Bytes Per Physical Sector : 4096 Bytes Per Cluster : 65536 Bytes Per FileRecord Segment : 1024 Therefore it seems traceflag 1800 should be enabled on the primary. With this in mind, I ran DBCC TRACEON (1800, -1); on the primary and verfied it was on with DBCC TRACESTATUS I then checked the error log on the secondary and can see the messages regarding Async IO still persist. Does this traceflag need setting as a startup parameter (with subsequent reboot) to take effect? Obviously I would do this anyway so it persists reboots but just ran the DBCC TRACEON first so it is active The documentation doesn't reference that a reboot is required. Obviously the way to find out is to test but due to this being a production server, I would need a maintenance window to that which isn't available to me at the moment Eventually, the primary server will be migrated to new hardware and I will ensure the disks are formatted in the same manner but for the tie being I was hoping this traceflag would provide a quick fix. If I cannot get this to work, I suppose the intermediate option would be to reformat the log drive on the existing primary to 64KB block size

Help please solve the following situation: There are two kinds of API where message history is stored, it's **Zopim ** and **Chat2Desc** (**import into Postman **). While these two but can then others appear. And my database with the users table: Table users id , email, phone, ... In **Zopim**, users are identified via **email**, and in **Chat2Desc** through the **phone**. For me, these two fields are important, whatever the chat was and how many would not be. That is, if I receive an email or a user's phone in messages, I make a request to my database (table users) to identify my user. And in principle, even the structure of chat rooms is not important.I'll somehow choose them.And here's how to properly save them, so much so that I had one structure for everyone. And that's what I came up with (Something I don't like, especially the chat_clients table): **Explanation:** **Table chats (Data for chat):** 1. client_id - indicates the id of the chat_clients table 2. duration - the duration of the chat (120 seconds) 3. system_type - stores the name of the chat (**Zopim, Chat2Desc**, ...) 4. created_at - creation date **Table chat_clients (information about users who were in the chat):** 1. is_agent - 0 | 1: 1 => my user, 0 => not my 2. user_id - is the user id. Contains either id from the users table or empty. 3. assigned_data - those initials under which users were in the chat 4. bean_module - it does not matter (information about my user) 5. unique_col - There will either be a email (from Zopim) or a phone (from Chat2Desc, Or I think to store the id of the users table). Will guarantee the uniqueness of the values. The users_id + unique_col bunch is unique (UNIQUE KEY user_id_unique_col_UQ (user_id, unique_col)) **Table chat_messages:** 1. text - the text of the message. 2. client_id - indicates the id of the chat_clients table 3. chat_id - indicates the id of the table chats 4. file_id - indicates the id of the chat_files table 5. transport - the value will be for Chat2Desc (**Viber, WhatsApp,** ...), for Zopim, so it's not empty, Zopim Table chat_files Information about the transferred files in the chat. Analogical tables may be not to store additional information. In the future I'm going to select the history of correspondence for each user. **Q:** How can I improve the structure of tables to get more flexibility ? Thank you in advance.

The article SQL Server tempdb Best Practices Increase Performance suggests that I should split tempdb into a number of files equal to the number of cores. So for 4 cores you get 4 files. > By having the larger number of files, you can increase the number of > physical I/O operations that SQL Server can push to the disk at any > one time. The more I/O that SQL Server can push down to the disk > level, the faster the database will run. With standard databases, SQL > Server can cache a large amount of the data that it needs into memory. > Because of the high-write nature of the tempdb, the data needs to be > written to the disk before it can be cached back up into memory. Though it sounds good in theory, is it really that good as a general optimisation? Is it something that may only apply for specific systems where IO is very high?

I want to separate MySQL database from the OS (Ubuntu) disk by moving the entire database to an exclusive HDD (M.2), and update the database folder in my.cnf. *My question is:* when the MySQL folder is on a separate disk, can I safely format the OS disk and install a fresh Ubuntu (and MySQL)? Is it OK to install a fresh MySQL and simply point to the existing MySQL folder on a different disk in the my.cnf of freshly installed MySQL? OR the best practice is to restore the databases from a dump after a fresh install, though the database is still available in the machine?

I am new to thinking in terms of databases. I have read about how B-trees are stored on disk but I haven't seen an actual implementation or read about how it works for B-trees. Wondering how one might store Skip Lists on disk. There are many descriptions for storing it with pointers in memory, but I haven't seen anything on disk storage. Wondering what it might look like at a high level (if it's just one big file or many small files, if there are different types of files, etc.).

Postgres delete doesn't actually remove the record from the table file. Once the delete is committed and the last transaction is closed, no postgres process can see the record any more, the deleted record is now liable to be vacuumed. Does vacuum (or some other system) overwrite the deleted data or is some of it left sitting in the table file? Obviously vacuum full and cluster will both re-write the files omitting the deleted record, but I'm particularly interested if there are any details about what ordinary vacuum will or, will not, do to fields stored in the heap and TOAST files.

Recently I have rebuild indexes uisng Ola Hallengreen script which were 99% fragmented. After rebuilt i have noticed the physical reads have reduced a lot. Does this has anything to do with Index Rebuilt ?

[This SO answer](https://stackoverflow.com/questions/16953638/how-is-mysql-data-stored-on-disk/16953724#16953724) outlines how MySQL organizes its database into files, but doesn't describe the structure of the files themselves. I am wondering, at the physical level, how relational database management systems store their records in files. What the structure of the file is roughly, where the columns/values are (if it's just a CSV), and how it is traversed.

In learning about how data in a database is saved to disk, I came across the slide below. Earlier in the presentation they mention a _block size_ is roughly 512 to 8,192 bytes. I haven't worked with files much before, I basically think about programming in terms of main memory access. But the presentation says to transfer 1 block into main memory it takes about 1ms. This is opposed to nanosecond-scale main-memory modifications on individual records. So the slide below says that to modify a single record (a single column of a record even), you must save an _entire block_ back to disk. I assume this is for every single change you make. So for every network request that comes into the database calling UPDATE, that is 1 block read and 1 block write (assuming it's for one record in one table). That is ~2ms per update. If you have [100k requests per second](https://www.quora.com/How-many-requests-per-second-do-Twitter-servers-have-to-handle) , that's 100 requests per millisecond. Assuming each request did 1 record update, that means you probably have 50 databases handling the request load. I know my math and assumptions are probably wrong. Which is why I am asking this question: 1. If you really must read and write an entire block per modification. It seems like you could read just the slice you wanted instead. 2. How a database optimizes updates so you can fit more into that ~2ms window per update. I can imagine if you batch update a few records, they all are in different blocks, so that means even slower time. --- [](http://www.cs.sfu.ca/CourseCentral/454/johnwill/cmpt454_02disks.pdf)

The size of the our PostgreSQL data directory is 100 GByte. Up to now we use traditional hard disk drives and hardware RAID 10. Which hard disk drives and RAID setup would give us maximum performance? The budget is at maximum 5000$. The load is like in most average databases. Maybe 1/5 writing (update or inserts) 4/5 reading (select).

I use my home machine as my personal database for my hobby analytics project. This machine has an SSD for the main drive where MySQL stores its data. It also has a secondary hard drive that is larger and not an SSD. I am about to create a table that I fear might be too large to fit on my SSD; is it possible for me to instead store that one table on the larger (but slower) drive? If it matters, I'm generally using MyISAM tables but could be persuaded to use InnoDB if that would help.

I'm configuring a Microsoft SQL Server installation which will use a SAN for storage. For SAN storage I have: - A small number of SSD disks - A larger number of 10K disks My SAN supports storage tiering. I'm on the fence about how to use the SSD disks. Here are the options that come to mind: - *Option 1:* Create tiered storage with the SSDs and 10K disks and use auto tiering to move highly accessed data to the SSDs. Then break that storage pool up into several different drives. - *Option 2:* Place key (I/O intensive) data on the SSDs and throw everything else on the 10K disks. **Questions** I don't have enough SSDs so that everything can reside on SSDs. So what's most important performance wise for SSD storage? tempdbs? LDF files? MDF files? My database is more write intensive than read intensive (if that makes a difference). Secondarily, are there any best practices I should follow when setting up the drives? Should I have separate drives for each tempdb? LDF files? MDF files? What about the system databases like master, model etc., should those go on their own dedicated drives or maybe on the C: drive? **Server disk activiity** By the way, here is a screenshot of our existing database server disk activity. It seems the tempdbs are the most read/write intensive followed by our MDF file and surprisingly the LDF file seems to have the least I/O. Any advice would be appreciated.