Hot answers tagged

7

Last couple of weeks we've been working on getting to the root cause of what could likely be the cause of the occurrence of these I/O issues and slowdown of the checkpoints. Sounds good. Have you collected and cut up the minifilter and storport tracing, yet? If so, what did it show? At first glance it looks to be clearly an I/O subsystem error and the ...


6

It's a good idea to keep them separated, even if the drives are currently backed by the same storage device. It buys a lot of future flexibility, at a slight expense in management overhead for both the DBAs and the Windows/storage admins. If you want to change the physical storage that backs either of the drives in the future, you won't have to go through ...


5

In your question you say "one LUN assigned to a SQL Server VM" - what you want is one LUN assigned to the hypervisor machine that can have multiple virtual machine disks for the SQL Server stored on it. This allows the VM to use a much larger number of threads to access the disk. In order to get the best queue depth out of VMware, specifically, ...


5

O_DSYNC is faster than O_DIRECT because it performs writes without verification. O_DIRECT is paranoid but more data consistent I wrote about this before : Clarification on MySQL innodb_flush_method variable


5

You mentioned you're checking wait stats and "every other metric." I assume you are seeing high PAGELATCH and WRITELOG waits? Just to double check, have you reviewed sys.dm_io_virtual_file_stats? That's where I would start when getting these 15 second I/O messages. Use Erin Stellato's excellent article "What Virtual Filestats Do, and Do Not, Tell You ...


4

My understanding is following: Historically InnoDB didn't scale well with large buffer pool. So - with a lot or RAM - OS disk cache might impact performance a lot -> this is why O_DIRECT performed bad for those environments (because O_DIRECT gets no help from OS disk cache). MySQL 5.6 has those issues addressed and in recent versions you may set buffer pool ...


4

in relation to your comment, I suspect a Ops related issue here instead of a SQL Server engine issue. These SAN devices usually work on the block layer and some manage transaction log/data file sync better than others, as well as other areas. You can show the ops team that no, SQL Server doesn't randomly corrupt data like this. You can restore backups ...


4

You can't depend on the system databases being cached in memory, more likely they won't be because of their less frequent access relative to the user databases. I think you'll end up in a quasi-functioning state, where your user databases will still be accessible but certain features of the server instance that rely on master and msdb will throw some weird ...


4

From documentation The Caveats section of the Availability group database level health detection failover option doc has some info that might improve our guesses on the question: It is important to note that the Database Level Heath Detection option currently does not cause SQL Server to monitor disk uptime and SQL Server does not directly monitor database ...


3

So you ran diskspd tool with the following parameters: Discussing Parameters - b8K : 8 kB block size (default: 64K) - d60 : 60 seconds duration (default: 10s) - o32 : 32 outstanding I/O reqeusts per-target per-thread (default: 2 ) - t8 : 8 threads per target - h : disable ...


3

If you are able to segregate drives so they use dedicated communication channels to the drive (D: has it's own channel, E: has a separate channel, etc.), then I believe you may be able to achieve some gains. If the different drives all go through the same channel to the storage then I don't think it will have an impact. Whether or not you can segregate the ...


3

The guidelines for different volumes were more of general best practices when you're trying to squeeze your performance/cost ratio. Outside of that, putting them in different volumes not only helps segregate workload which could cause performance to be affected by SSD functions such as TRIM or how it deletes data for example, but also helps you with ...


3

A SAN isn't something to be nervous about. Yes, there a bunch of capabilities (software) that come with it which enable features like replication and auto-tiering, but those are above and beyond the basic performance. At the end of the day they're still a bunch of disks behind controllers designed to optimise availability and performance. There are a number ...


3

Depends on what kind of offline. I had it get itself into a state where it had no idea what transactions were committed because the failure mode it was seeing was writes to the dbs were failing at block levels. It spammed the log nicely, but couldn't recover until I manually bounced it as it would believe the in-memory copy was correct after hitting the IO ...


3

> is this the best approach for performance? That is at the same time too broad a question (i.e. will be very opinion based) and to narrow (other people looking for advice are unlikely to have that same config) > Please remember downtime is acceptable for some servers at a time in the case of a drive failure because this is not production workloads Are ...


3

When on a SAN, you do not write to 1 specific disk but to a set of disks (managed at the SAN level). When you use physical disk, you want to split IO to prevent from IO bottleneck causing performance issue (P.s. Splitting data and log is an old habbit and may not be the best way to split your IO... But you probably want to make sure tempdb is on a different ...


3

The focus seems to be on performance. However there are other things you would like to be concerned about. For example: disk failure: if on the same disk, then you lose both data and logs, otherwise if the the logs are safe you may be able to restore the db disk full: if on the same disk, if logs run too big and fill up the disk, then the write to the data ...


3

does the Microsoft SQL Server supports the SSD-Trim-Command to deallocate space in some way? That's really not in the purview of the database, which is an application on top of the operating system. Windows has support for TRIM in the most common form which is via NTFS (as the most common filesystem) of which SQL Server sits on top of (technically there is ...


2

The 42MB unit (Hitachi calls this a page too, how convinient). is the basic allocation unit at which space is allocated to a LUN. So with Dynamic provisioning, a LUN will always grow in 42MB steps. These, Hitachi 42MB pages are taken from different Array groups with in a pool. So whenever more space is needed for the LUN a new 42MB Hitachi page is ...


2

That all depends on which configuration you're seeking. As @EricHiggins noted, a traditional cluster will have shared resources, including disk, network name(s) and IP(s), and the service itself. SQL Server is a cluster-aware application and will interact with the Windows cluster service. When a failure (or manual failover) is detected by the cluster service,...


2

To answer your questions in order: You can check for the physical fragmentation of the disk by going to the drive, right clicking on it and selecting tools, then defragment. Or close to that, depending on which version of the OS you are using your specific steps may be different. But you will only be able to see the fragmentation on a volume level, not ...


2

I am not a SAN admin, but I have a very nice one in my team that takes the time to do his job proprely, and share his knowledge with us when we ask. The way our SAN is configured makes it so there is a very large amount of cache between the SQL servers and the physical disks. In this situation, having multiple filegroups, or even splitting the DATA and LOG ...


2

There are a lots of questions I'd like to ask to give you a more definitive answer (How many servers do you have? What kind/speed of disks are you running locally? What do your current IO loads look like? etc), but at the end of the day, a great many organizations are using SAN's to store database files. SAN technology is something I would recommend ...


2

This is a database recovery question first and foremost, then performance. DB Recovery: By keeping the LDF and the MDF file in the same physical array, then you lose any chance of performing a 'tail of log' backup. This means your data is gone from either: The last transaction log taken and copied off the failed array. A mirrored pair/availability ...


2

Method 4 Depending on the size (and contents) of the data store, if you connect your new SAN to your VMWare infrastructure, you can storage vMotion in addition to moving the guest itself. If you have large databases in the Availability Group, I'd recommend a modification of Tony Hinkle's answer under Method 1. Remove secondary replica (call it "B") from ...


2

Oracle logical block size is orthogonal to the storage logical block size (and the file system block size, for that matter). At a minimum your Oracle block size should be a multiple of the disk block size to avoid reading unnecessary data. If your tablespaces have different block sizes, the smallest of them (block sizes) would need to meet this constraint.


2

keep in mind the following: generally SAN in sthe best option for clustered environments. In non clustered Environment you can opt for locl disks. Moving from SAN/Local Disk to SMB Windows share can hide some potential problems. Using SMB network connectivity becomes a very very important piece of your architecture. Probalby you will have to use dedicated ...


2

IOPS and Latency are two independent numbers. 5k IOPS seems decent, but the Latency of 44ms suggest (to me) that it is practically useless (when compared to a single disk's 5ms latency). When a single SATA drive can have a transfer rate of 100 MB/s, your 45 MB/s aggregated transfer rate suggests your SAN is overloaded. High end SANs will incorporate SSD ...


2

In general, the type of storage and current size of database isn't particularly relevant to your auto-grow settings. Ensuring that instant file initialization is enabled is far more important. If instant file initialization is not enabled, then everything (more or less) comes to a halt while it is initializing the new part of the file. With SSDs that time ...


1

Frequently Asked Questions about Storage Replica specifically mentions using it for a SQL Server FCI. Business continuity and database recovery - SQL Server also mentions it specifically. I've also found other non-Microsoft articles (this one, in particular) mentioning it as well. This, along with the fact that SQL Server doesn't know or care about the ...


Only top voted, non community-wiki answers of a minimum length are eligible