Condusiv Technologies Blog

Condusiv Technologies Blog

Blogging @Condusiv

The Condusiv blog shares insight into the issues surrounding system and application performance—and how I/O optimization software is breaking new ground in solving those issues.

I/Os Are Not Created Equal – Random I/O versus Sequential I/O

by Gary Quan 13. February 2020 10:36

To demonstrate the performance difference of I/O patterns, put yourself in a Veterinarian’s office where all the data is still stored on paper in file cabinets. For a single animal (billing, payments, medication, visits, procedures…), it is all stored in different folders and placed in different cabinets according to specific categories, like Billing and Payments.  To get all the data for that one animal, you may have to retrieve 10 different folders from 10 different cabinets. Wouldn’t it be easier if all that data was in a single file so you can retrieve it in one single step? This is basically the difference between Random I/O and Sequential I/O.

 

Accessing data randomly is much slower and less efficient than accessing it sequentially.  Simply, it is faster to write/read the same data with a single sequential I/O rather than multiple, say 25, smaller random I/Os. For one, the operating system must process all those extra I/Os rather than just the single one, a substantial overhead.  Then the storage device also has to process all those multiple I/Os too. With Hard Disk Drives (HDDs), the penalty is worse because the extra disk head movement to gather the data from all those random I/Os is very time-consuming. With Solid State Drives(SSDs), there is not the penalty of the disk head movement, just the penalty of the storage device having to process the multiple I/Os rather than a single one. In fact, storage manufacturers usually provide two benchmarks for their devices – Random I/O and Sequential I/O performance. You will notice that the Sequential I/Os always outperform the Random I/Os. This is true for both HDDs and SSDs.

 

Sequential I/O always outperforms Random I/O on hard disk drives or SSDs.

 

So, enforcing Sequential I/Os to occur will get you optimal I/O performance, both at the Operating System level (less I/Os to process) and at the Storage level. Unfortunately, the Windows File system tends to cause Random I/Os to occur when data is written out, then subsequentially when that same data is read back in. The reason for this is when files are created or extended, the Windows File System does not know how large those creations/extensions are going to be, so it does not know what to look for in finding the best logical allocation to place that data so it can be written in one logical location (one I/O). It may just find the next available allocation which may not be large enough, so it has to find another allocation (another I/O) and keep doing so until all the data is written out.  The IntelliWrite® technology in Diskeeper® and V-locity® solves this by providing intelligence back to the File System so it can find the best allocation which helps enforce Sequential I/Os to occur rather than Random I/Os and enforcing optimal I/O performance.

Tags:

IntelliWrite | Performance | SSD, Solid State, Flash

6 Best Practices to Improve SQL Query Performance

by Bruce Boyers 31. January 2020 07:11

 

MS SQL Server is the world’s leading RDMS and has plentiful benefits and features that empower its efficient operation. As with any such robust platform, however—especially one which has matured as SQL Server has—there have been best practices evolved that allow for its best performance.

For any company utilizing it, Microsoft SQL Server is central to a company’s management and storage of information. In business, time is money, so any company that relies on information to function (and in this digital age that would be pretty much all of them) needs access to that information as rapidly as possible. In that information is often obtained from databases through queries, optimizing SQL query performance is vital.

As 7 out of 10 Condusiv customers come to us because they were experiencing SQL performance issues and user complaints, we have amassed a great deal of experience on this topic and would like to share. We have done extensive work in eliminating I/O inefficiencies and streamlining I/O for optimum performance. It is especially important in SQL to reduce the number of random and “noisy” I/Os as they can be quite problematic. We will cover this as well as some additional best practices. Some of these practices may be more time-consuming, and may even require a SQL consultant, and some are easy to solve.

SQL Server, and frankly all relational databases, are all high I/O utilization systems. They’re going to do a lot of workload against the storage array. Understanding their I/O patterns are important, and more important in virtual environments. — Joey D’Antoni, Senior Architect and SQL Server MVP

Here are 6 best practices for the improvement of SQL query performance.

1. Tune queries

A great feature of the SQL language is that it is fairly easy to learn and to use in creating commands. Not all database functions are efficient, however. Two queries, while they might appear similar, could vary when it comes to execution time. The difference could be the way they are structured; this is a very involved subject and open to some debate. It’s best to engage a SQL consultant or expert and allow them to assist you with structuring your queries.

Aside from the query structure, there are some great guidelines to follow in defining business requirements before beginning.

   Identify relevant stakeholders

   Focus on business outcomes

   Ask the right questions to develop good query requirements

   Create very specific requirements and confirm them with stakeholders

2. Add memory

Adding memory will nearly always assist in SQL Server query performance, as SQL Server uses memory in several ways. These include:

   the buffer cache

   plan cache, where query plans are stored for re-use

   buffer pool, in which are stored recently written-to pages

   sorting and matching data, which all takes place in memory

Some queries require lots of memory for joins, sorts, and other operations. All of these operations require memory, and the more data you aggregate and query, the more memory each query may require.

[Tips for current Condusiv V-locity® users: (1) Provision an additional 4-16GB of memory to the SQL Server if you have additional memory to give. (2) Cap MS-SQL memory usage, leaving the additional memory for the OS and our software. Note - Condusiv software will leverage whatever is unused by the OS (3) If no additional memory to add, cap SQL memory usage leaving 8GB for the OS and our software Note – This may not achieve 2X gains but will likely boost performance 30-50% as SQL is not always efficient with its memory usage]

3. Perform index maintenance

Indexes are a key resource to SQL Server database performance gain. The downside, however, is that database indexes degrade over time.

Part of this performance degradation comes about through something that many system administrators will be familiar with: fragmentation. Fragmentation on a storage drive means data stored non-contiguously, so that the system has to search through thousands of fragments, meaning extra I/Os, to retrieve data. It is a similar situation with a database index.

There are two types of database index fragmentation:

   Internal fragmentation, which occurs when more than one data page is created, neither of which is full. Performance is affected because SQL Server must cache two full pages including empty yet allocated space.

   External fragmentation, which means pages that are out of order.

When an index is created, all pages are sequential, and rows are sequential across the pages. But as data is manipulated and added, pages are split, new pages are added, and tables become fragmented. This ultimately results in index fragmentation.

There are numerous measures to take in restoring an index so that all data is sequential again. One is to rebuild the index, which will result in a brand-new SQL index. Another is to reorganize the index, which will fix the physical order and compact pages.

There are other measures you can take as well, such as finding and removing unused indexes, detecting and creating missing indexes, and rebuilding or reorganizing indexes weekly.

It is recommended you do not perform such measures unless you are a DBA and/or have a thorough understanding of SQL Server.

4. Add extra spindles or flash drives

Like the increase of memory, increasing storage capacity can be beneficial.

Adding an SSD, the most expensive option, can provide the most benefit as there are no moving parts. The less expensive option is to add spindles. Both of these options can help with decreasing latency times, but it does not get rid of the extra I/Os occurring due to fragmentation. It is not really solving the root cause of I/O inefficiencies.

5. Optimize the I/O subsystem 

Optimizing the I/O subsystem is highly important in optimizing SQL Server performance. When configuring a new server, or when adding or modifying the disk configuration of an existing system, determining the capacity of the I/O subsystem before deploying SQL Server is good practice.

There are three primary metrics that are most important when it comes to measuring I/O subsystem performance:

   Latency, which is the time it takes an I/O to complete.

   I/O operations per second, which is directly related to latency.

   Sequential throughput, which is the rate at which you can transfer data.

You can utilize an I/O stress tool to validate performance and ensure that the system is tuned optimally for SQL Server before deployment. This will help identify hardware or I/O configuration-related issues. One such tool is Microsoft DiskSpd, which provides the functionality needed to generate a wide variety of disk request patterns. These can be very helpful in the diagnosis and analysis of I/O performance issues.

You can download DiskSpd.exe here.

Another tool is Condusiv’s I/O Assessment Tool for identifying which systems suffer I/O issues and which systems do not. It identifies and ranks systems with the most I/O issues and displays what those issues are across 11 different key performance metrics by identifying performance deviations when workload is the heaviest.

You can download Condusiv’s I/O Assessment Tool here

6. Use V-locity I/O reduction software

Reduce the number of I/Os that you are doing. Because remember, the fastest read from disk you can do is one you don’t do at all. So, if you don’t have to do a read, that’s all the better. —Joey D’Antoni, Senior Architect and SQL Server MVP

Reducing and streamlining small, random, fractured I/O will speed up slow SQL queries, reports and missed SLAs. V-locity makes this easy to solve. 

Many companies have utilized virtualization to greatly increase server efficiency for SQL Server. While increasing efficiency, at the same time virtualization on Windows systems has a downside. Virtualization itself adds complexity to the data path by mixing and randomizing I/O streams—something known as the “I/O blender effect.” On top of that, when Windows is abstracted from the physical layer, it additionally utilizes very small random read and writes which are less efficient that larger contiguous reads and writes. SQL Server performance is penalized not once, but twice. The net effect is I/O characteristics more fractured and random than they need to be.

The result is that typically systems process workloads about 50 percent slower than they should be, simply because a great deal more I/O is required.

While hardware can help performance problems as covered above, it is only a temporary fix as the cause of Windows I/O inefficiencies is not being addressed. Many sites have discovered that V-locity I/O reduction software, employed on any Windows server (virtual or physical) is a quicker and far more cost-effective solution. V-locity replaces tiny writes with large, clean, contiguous writes so that more payload is delivered with every I/O operation. I/O to storage is further reduced by establishing a tier 0 caching strategy which automatically serves hot reads from idle, otherwise unused memory. The software adjusts itself, moment to moment, to only use unused memory.

The use of V-locity can improve performance by 50 percent or more. Many sites see twice as much improvement or more, depending on the amount of DRAM available. Condusiv Technologies, developer of V-locity, actually provides a money-back guarantee that V-locity will solve the toughest application performance challenges on I/O intensive systems such as SQL Server.

You can download a free 30-day trial of V-locity I/O reduction software here. 


Tags:

SQL Server | SSD, Solid State, Flash | V-Locity

Top 10 Webinar Questions – Our Experts Get Technical

by Marissa Newman 7. January 2020 12:58

As we enter the new year and reflect on the 25 live webinars that we held in 2019, we are thrilled with the level of interaction and thought we’d take a look back at some of the great questions asked during the lively Q&A sessions. Here are the top questions and the responses that our technical experts gave.

 

Q. We run a Windows VM on a Microsoft Azure, is your product still applicable?

A. Yes. Whether the Windows system is a physical system or a virtual system, it still runs into the I/O tax and the I/O blender effect.  Both which will degrade the system performance.  Whether the system is on premise or in the cloud, V-locity® can optimize and improve performance.

 

Q. If a server is dedicated to running multiple SQL jobs for different applications, would you recommend installing V-locity?

A. Yes, we would definitely recommend using V-locity. However, the software is not specific to SQL instances, as it looks to improve the I/O performance on any system. SQL just happens to be a sweet spot because of how I/O intensive it is.

 

Q. Will V-locity/Diskeeper® help with the performance of my backup jobs?

A. We have a lot of customers that buy the software to increase their backup performance because their backup windows are going past the time they have allotted to do the backup. We’ve had some great success stories of customers that have reduced their backup windows by putting our software on their system.

 

Q. Does the software work in physical environments?

A. Yes, although we are showing how the software provides benefits in a virtual environment, the same performance gains can be had on physical systems. That same I/O tax and blender effect that degrade performance on virtual systems can also happen on physical systems. The I/O tax occurs on any Windows systems when nice, sequential I/O is broken up into less efficient smaller, random I/O, which can also apply to physical workstation environments. The Blender Effect that we see when all of those small, random I/Os from multiple VMs have to get sorted by the Hypervisor and can occur on physical environments too. For example, when multiple physical systems are read/writing to different LUNs on the same SAN.

 

Q. What about the safety of this caching? If the system crashes, how safe is my data?

A. The software uses read-only caching, as data integrity is our #1 priority when we develop these products. With read-only caching, the data that’s in our cache is already in your storage. So, if the system unexpectedly goes down (i.e. Power outage), it’s okay because that data in cache is already on your storage and completely safe.

 

Q. How does your read cache differ from SQL that has its own data cache?

A. SQL is not too smart or efficient with how it uses your valuable available memory. It tries to load up all of its databases as much as it can to the available memory that is there, even though some of the databases or parts of those database aren’t even being accessed. Most of the time, your databases are much larger than the amount of memory you have so it can never fit everything. Our software is smarter in that it can determine the best blocks of data to optimize in order to get the best performance gains. Additionally, the software will also be caching other noisy I/Os from the system that can improve performance on the SQL server.

 

Q. In a Virtual environment, does the software get installed on the Host or the VMs?

A. The software gets installed on the actual VMs that are running Windows, because that’s where the I/Os are getting created by the applications and the best place to start optimizing. Now, that doesn’t necessarily mean that it has to get installed on all of the VMs on a host. You can put it just on the VMs that are getting hit the most with I/O activity, but we’ve seen the best performance gains if it gets installed on all of the VMs on that host because if you only optimize one VM, you still have the other VMs causing performance degradation issues on that same network. By putting the software on all of them, you’ll get optimal performance all around.

 

Q. Is your product needed if I have SSDs as my storage back-end?

A. Our patented I/O reduction solutions are very relevant in an SSD environment. By reducing random write I/Os to back end SSD’s, we also help mitigate and reduce write amplification issues. We keep SSDs running at “like new” performance levels. And, although SSDs are much faster than HDDs, the DRAM used in the product’s intelligent caching feature is 10x-15x faster than SSDs. We have many published customer use cases showing the benefits of our products on SSD based systems. Many of our customers have experienced 50, 100, even 300% performance gains in an all flash/SSD environment!

 

Q. Do we need to increase our RAM capacity to utilize your software?

A. That is one of the unique Set-It-and-Forget-It features of this product. The software will just use the available memory that’s not being used at the time and will give it back if the system or user applications need it. If there’s no available memory on the system, you just won’t be able to take advantage of the caching. So, if there’s not enough available RAM, we do recommend adding some to take advantage of the caching, but of course you’re always going to get the advantage of all the other technology if you can’t add RAM. Best practice is to reserve 4-8GB at a minimum.

 

Q. What teams can benefit most from the software? The SQL Server Team/Network Team/Applications Development Team?

A. The software can really benefit everyone. SQL Servers are usually very I/O intensive, so performance can be improved because we’re reducing I/O in the environment, but any system/applications (like File Server or Exchange Server) that are I/O intensive will benefit. The whole throughput and network team can benefit from it because it decreases the meta traffic that has to go through the network to storage, so it increases bandwidth for others. Because the software also improves and reduces I/O across all Microsoft applications, it really can benefit everyone in the environment.

 

There you have it – our top 10 questions asked during our informative webinars! Have more questions? Check out our FAQs, ask us in the comments below or send an email to info@condusiv.com.

Tags:

Application Performance | SSD, Solid State, Flash

How To Get The Most Out Of Your Flash Storage Or Move To Cloud

by Rick Cadruvi, Chief Architect 2. January 2020 10:41

You just went out and upgraded your storage to all-flash.  Or, maybe you have moved your systems to the cloud where you can choose the SLA to get the performance you want.  We can provide you with a secret weapon that will make you continue to look like a hero and get the real performance you made these choices for.


Let’s start with why you made those choices to start with.  Why did you make the change?  Why not just upgrade the aging storage to a new-gen HDD or hybrid storage subsystem?  After all, if you’re like most of us, you’re still experiencing explosive growth in data and HDDs continue to be more cost-effective for whatever data requirements you’re going to need in the future.

 

If you went to all-flash, perhaps it was the decreasing cost that made it more approachable from a budgetary point of view and the obvious gain in speed made it easy to justify.

 

If it was a move to the cloud, there may have been many reasons including:

   •  Not having to maintain the infrastructure anymore

   •  More flexibility to quickly add additional resources as needed

   •  Ability to pay for the SLA you need to match application needs to end user performance

Good choices.  So, what can Diskeeper® and V-locity® do to help make these even better choices to provide the expected performance results at peak times when needed most?

 

Let’s start with a brief conversation about I/O bottlenecks.

 

If you have an All-Flash Array, you still have a network connection between your system and your storage array.  If you have local flash storage, system memory is still faster, but your data size requirements make it a limited resource. 

 

If you’re on the cloud, you’re still competing for resources.  And, at peak times, you’ll have slows due to resource contention.  Plus, you will experience issues because of File System and Operating System overhead. 

 

File fragmentation creates significant increases in the number of I/Os that have to be requested for your applications to process the data they need to.  Free Space fragmentation adds overhead to allocating file space and makes file fragmentation far more likely.

 

Then there is all the I/Os that Windows creates that are not directly related to your application’s data access.  And then you have utilities to deal with anti-malware, data recovery, etc....  And trust me, there are LOTs of those.

 

At Condusiv, we’ve watched the dramatic changes in storage and data for a long time.  The one constant we have seen is that your needs will always accelerate past the current generation of technologies you use.  We also handle the issues that aren’t handled by the next generation of hardware.  Let’s take just a minute and talk about that.

 

What about all the I/O overhead created in the background by Windows or your anti-malware and other system utility software packages?  What about the I/Os that your application doesn’t bother to optimize because it isn’t the primary data being accessed?  Those I/Os account for a LOT of I/O bandwidth.  We refer to those as “noisy” I/Os.  They are necessary, but not the data your application is actually trying to process.  And, what about all the I/Os to the storage subsystem from other compute nodes?  We refer to that problem as the I/O Blender Effect.

 

 

Our RAM caching technologies are highly optimized to use a small amount of RAM resources to eliminate the maximum amount of I/O overhead.  It does it dynamically so that when you need RAM the most, we will free it up for your needs.  Then, when RAM is available, we will use it to remove the I/Os causing the most overhead.  A small amount of free RAM will go a long way towards reducing the I/O overhead problem.  That’s because our caching algorithms look at how to eliminate the most I/O overhead effectively.  We don’t use LIFO or FIFO algorithms hoping to eliminate I/Os.  Our algorithm uses empirical data, in real-time to guarantee maximum I/O overhead elimination while using minimal resources.

 

Defragmenting all your files that are fragmented is not reasonable due to data explosion.  Plus, you didn’t spend your money to have our software use it to make it look pretty.  We knew this long before you ever did.  As a result, we created technologies to prevent fragmentation in the first place.  And, we created technologies to empirically locate just those files that are causing extra overhead due to fragmentation so we can address those files only and therefore get the most bang for the buck in terms of I/O density.

 

Between our caching and file optimization technologies, we will make sure you keep getting the performance you hoped for when you need it the most.  And, of course, you will continue to be the superstar to the end users and your boss.  I call that a Win-Win. 😊

 

Finally, we continue to look in our crystal ball for the next set of I/O performance issues that will be coming up that others aren’t thinking before they appear in the first place.  You can rest assured we will have solutions for those problems long before you ever experience them.

 

##

 

Additional and related resources:

 

Windows is still Windows Whether in the Cloud, on Hyperconverged or All-flash

Why Faster Storage May NOT Fix It

How to make NVMe storage even faster

Trial Downloads

 

Tags:

Causes and Solutions for Latency

by Kim Amezcua 19. December 2019 04:14

Sometimes the slowdown of a Windows server occurs because the device or its operating system is outdated. Other times, the slowdown is due to physical constraints on the retrieval, processing, or transmitting of data. There are other causes as we will cover. In any case, the delay between when a command is made and a response is received is referred to as "latency."

Latency is a measure of time. For example, the latency of a command might be 0.02 seconds. To humans, this seems extraordinarily fast. However, computer processors can execute billions of instructions per second. This means that latency of a few millionths of a second can cause visible delays in the operation of a computer or server.

To figure out how to improve latency, you must identify the source of any latency issues. There are many possible sources of latency and, for each one, there are high latency fixes. Here are two possible causes of latency as well as a brief explanation for how to improve latency. In this case, I/O latency where the computer process is waiting for the I/O to complete, so it can process the data of that I/O, is a   waste of your computer processing power.

Data Fragments

Logical data fragments occur when files are written, deleted, and rewritten to a hard drive or solid-state drive.

When files are deleted from a drive, the files actually still exist on the drive. However, the logical address on the Windows operating file system for those files is freed up for use. This means that "deleted" files remain on the logical drive until another file is written over it by reusing the address. (This also explains why it is possible to recover lost files). 

When an address is re-used, the likelihood that the new file is exactly the same length as the "deleted" file is remote. As a result, little chunks or fragments of data remaining from the "deleted" file remain on the logical drive. As a logical drive fills up, new files are sometimes broken up to fit into the available segments. At its worst, a logical fragmented drive contains both old fragments left over from deleted files (free space fragments) and new fragments that were intentionally created (data file fragments).

Logical data fragments can be a significant source of latency in a computer or server. Storing to, and retrieving from, a fragmented logical drive introduces additional steps in searching for and reassembling files around the fragments. For example, rather than reading a file in one or two I/Os, fragmentation can require hundreds, even thousands of I/Os to read or write that same data.

One way for how to improve latency from these logical data fragments is to defragment the logical drive by collecting data fragments and making them contiguous. The main disadvantages of defragmenting are that it must be repeated periodically because the logical drive will inevitably fragment again and also defragmenting SSDs can cause them to wear out prematurely.

A better method for how to improve latency from disk fragments is to prevent the logical disk from becoming fragmented. Diskeeper® 18 manages writes so that large, contiguous segments are kept together from the very start, thereby preventing the fragments from developing in the first place.

Limited Resources

No matter how "fast" the components of a computer are, they are still finite and tasks must be scheduled and performed in order. Certain tasks must be put off while more urgent tasks are executed. Although the latency in scheduling is often so short that it is unnoticeable, there will be times when limited resources cause enough of a delay that it hampers the computer or server.

For example, two specifications that are commonly used to define the speed of a computer are processor clock speed and instructions per cycle. Although these numbers climb steadily as technology advances, there will always be situations where the processor has too many tasks to execute and must delay some of them to get them all done.

Similarly, data buses and RAM have a particular speed. This speed limits the frequency with which data can be moved to the processor. These kinds of Input/output performance delays can reduce a system’s capacity by more than 50%.

One way to address latency is a method used by Diskeeper® 18. In this method, idle available DRAM is used to cache hot reads. By caching, it eliminates having to travel all the way to the storage infrastructure to read the data; and remember that DRAM can be 10x-15x faster than SSDs and even many factors more than HDDs. This allows faster retrieval of data; in fact, Windows systems can run faster than when new.

Reducing latency is mostly a matter of identifying the source of latencies and addressing them. By being proactive and preventing fragmentation before it happens and by caching hot reads using idle & available DRAM, Diskeeper® 18 makes Windows computers faster and more reliable.

 

RecentComments

Comment RSS

Month List

Calendar

<<  February 2020  >>
MoTuWeThFrSaSu
272829303112
3456789
10111213141516
17181920212223
2425262728291
2345678

View posts in large calendar