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 ?

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): - `client_id` - indicates the id of the `chat_clients` table - `duration` - the duration of the chat (120 seconds) - `system_type` - stores the name of the chat (Zopim, Chat2Desc, ...) - `created_at` - creation date Table `chat_clients` (information about users who were in the chat): - `is_agent` - 0 | 1: 1 => my user, 0 => not my - `user_id` - is the user id. Contains either id from the users table or empty. - `assigned_data` - those initials under which users were in the chat - `bean_module` - it does not matter (information about my user) - `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: - `text` - the text of the message. - `client_id` - indicates the id of the chat_clients table - `chat_id` - indicates the id of the table chats - `file_id` - indicates the id of the chat_files

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.

I am about to setup a SQL Server 2014 on a Windows Server 2012. Starting from best performance on the drive I formatted the drive with 64k allocation unit size. Now I am stuck using Diskpart to set the perfect offset. As far as I understand I have 63 hidden sectors and to align it with my formatting I can use an offset of 1024k , did I get the right? Anyway when I list my partition afterwards I get this result: Using command line `wmic partition get BlockSize, StartingOffset, Name, Index` I get a starting offset of `135266304` Some websites/blogs recommend 32K other do recommend 1K for 2012, whom can I trust? Currently I have issues understanding it correctly. May the force (you) enlighten me?

We are trying to evaluate the potential performance improvements that could be gained by switching an existing applications .mdf files to a RAID10 LUN vs. the existing RAID5 LUN they are living on right now. The transaction log already lives on a RAID10 LUN. So the question is, if we move data files to RAID10 would the end user see performance improvements in the GUI? or would it be just faster writes to the data files when a checkpoint occurs and the changes get written to the data file but no improvement would be seen at the end user level?

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 some tables which benefit greatly from CLUSTER ON/CLUSTER USING in Postgres SQL: ``` # CLUSTER table USING index_name; # ANALYZE VERBOSE table; # CLUSTER VERBOSE; ``` A maintenance task periodically runs CLUSTER VERBOSE to keep things fresh. But is there a test I can run to see how fragmented the table is, prior to running CLUSTER VERBOSE? Maybe something like: ``` # CLUSTER ANALYZE table 40000 records. 4000 observed clusters, 5000 potential clusters (20% fragmentation) ``` Note that I use CLUSTER so data accessed at the same time is "defragmented" into a small number of disk blocks. For example I have thousands of attributes that go with each page. a `CLUSTER page_attribute USING page_id;` puts all the attributes next to each other, greatly reducing disk load.

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?

Assuming constant memory (32gb) and CPU (4), 2 x disk arrays, I have the following disks - 2 x 150 (10k) - 6 x 150 (15k) They are all local disks. My requirements - My DB is 350gb and set to default 10% growth - My OS & SQL Server are Server 2k8R2 (C: drive OS + page + applications = 55Gb) - Log requirements are about 70gb and set to default 10% growth and is routinely truncated - My TempDb is about 12gb currently and set to default 10% growth My problem is that I’m trying to understand where to best put the TempDB and OS and the Log. My experience is limited in optimal configuration of these two This is not an online transactional system. It has heavy data write (new data + indexes rebuild/reorg) then heavy data read (I'm estimating at about 50/50) processing for about 13 hours, and then just quiet. My understanding is that the TEMPDB is heavily used during normal processing compared to the log. My idea is the following - 2 x 150g (15k) Raid 1 = 150g for OS + TempDB - 2 x 150g (10k) Raid 1 = 150g for LOG (note slower disks here) - 4 x 150g (15k) Raid 5 = 150g for data Does this sound like a good idea? I could then swap the Log + TempDB if needed. Am I breaking a cardinal rules like never put TempDB on OS disk due to paging concerns, or perhaps never put log on slower disk than data? Edit: We also have a SSAS on the system and the end users access only the Cube. The 50% read above is based on the time it takes to process SSAS database.

My question targets Postgres, but answers might just be good enough coming from any database background. Are my assumptions correct: - Disks have a fixed block size? - RAID controller can have a differnt block size? Does one RAID block get split onto multiple real disk blocks? - The filesystem also has an independant block size which again gets split onto the RAID block size? - Postgres works with fixed 8k blocks. How does the mapping to the filesystem block size happen here? Are Postgres 8k blocks batched together by the filesystem? When setting up a system is it best to have all blocks at 8k? Or do the settings not real matter? I was also wondering if some "wrong" block size settings could endanger data integrity in case of a crash? Maybe if a Postgres 8k block has to be split onto multiple disk blocks? Or does nothing get batched together, and therefore I loose disk space with every mismatch between defined block sizes?