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.

SQL Server Database Performance

by Dawn Richcreek 15. March 2019 07:42

How do I get the most performance from my SQL Server?

SQL Server applications are typically the most I/O intensive applications for any enterprise and thus are prone to suffer performance degradation. Anything a database administrator can do to reduce the amount of I/O necessary to complete a task will increase the server’s performance of the application.

Excess and noisy I/O has typically been found to be the root cause of numerous SQL performance problems such as:SQL Server

  • -SQL query timeouts
  • -SQL crashes
  • -SQL latency
  • -Slow data transfers
  • -Slow or sluggish SQL based-applications
  • -Reports taking too long
  • -Back office batch jobs bleeding over into production hours
  • -User complaints; users having to wait for data

 

Some of the most common actions DBAs often resort to are:

  • -Tuning queries to minimize the amount of data returned.
  • -Adding extra spindles or flash for performance
  • -Increased RAM
  • -Index maintenance to improve read and/or write performance.

 

Most performance degradation is a software problem that can be solved by software

None of these actions will prevent hardware bottlenecks that occur due to the FACT that 30-40% of performance is being robbed by small, fractured, random I/O being generated due to the Windows operating system (that is, any Windows operating system, including Windows 10 or Windows Server 2019).

 

Two Server I/O Inefficiencies



IO StreamAs the storage layer has been logically separated from the compute layer and more systems are being virtualized, Windows handles I/O logically rather than physically which means it breaks down reads and writes to their lowest common denominator, creating tiny, fractured, random I/O that creates a “noisy” environment that becomes even worse in a virtual environment due to the “I/O blender effect”.



IO StreamThis is what a healthy I/O stream SHOULD look like in order to get optimum performance from your hardware infrastructure. With a nice healthy relationship between I/O and data, you get clean contiguous writes and reads with every I/O operation.

 

 

 

 

 

Return Optimum Performance – Solve the Root Cause, Instantly

 

Condusiv®’s patented solutions address root cause performance issues at the point of origin where I/O is created by ensuring large, clean contiguous writes from Windows to eliminate the “death by a thousand cuts” scenario of many small writes and reads that chew up performance. Condusiv solutions electrify performance of windows servers even further with the addition of DRAM caching – using idle, unused DRAM to serve hot reads without creating an issue of memory contention or resource starvation. Condusiv’s “Set It and Forget It” software optimizes both writes and reads to solve your toughest application performance challenges Video: Condusiv I/O Reduction Software Overview.

 

Lab Test Results with V-locity I/O reduction software installed

labtest 

 

Best Practice Tips to Boost SQL Performance with V-locity

 

 

By following the best practices outlined here, users can achieve a 2X or faster boost in MS-SQL performance with Condusiv’s V-locity® I/O reduction software.

-Provision an additional 4-16GB of memory to the SQL Server if you have additional memory to give 

-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 

-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 highly inefficient with its memory usage

-Download and install the software – condusiv.com/try. No SQL code changes needed. No reboot required. Note - Allow 24 hours for algorithms to adjust.

-After a few days in production, pull up the dashboard and look for a 50% reduction in I/O traffic to storage

Note – if offloading less than 50% of I/O traffic, consider adding more memory for the software to leverage and watch the benefit rise on read heavy apps.

RecentComments

Comment RSS

Month List

Calendar

<<  November 2019  >>
MoTuWeThFrSaSu
28293031123
45678910
11121314151617
18192021222324
2526272829301
2345678

View posts in large calendar