SQL SERVER – SafePeak – The Plug and Play Immediate Acceleration Solution

Introduction

Given how important performance is these days among SQL Server critical applications, I was excited to look into a new product by SafePeak Technologies (www.SafePeak.com) that aims to immediately resolve, in a plug-and-play way, the performance, scalability and peaks challenges of SQL Server applications on the Cloud, hosting servers and enterprise data centers.

Click to Enlarge

While most SQL Server shops ask me about performance monitoring and alert solutions, to allow them to identify and analyze performance bottlenecks, this unique product actually can resolve them and increase performance in dramatic ways.

SafePeak is a caching proxy that resides between the application and the database. It is unique in the way it requires neither code changes nor database changes. The only change required is routing the application through SafePeak instead of the database.

The benefits of such a solution are obvious: quick integration, immediate ROI, no complex projects and most importantly, minimal downtime for production systems.

As far as I know, this is the only true plug & play caching solution available on the market today and it can provide instant acceleration to any Cloud, hosted or business SQL Server application.

The concept

The idea behind SafePeak is that simple – repeatable queries should not be executed against the database, the result sets can be stored in RAM and can be returned immediately, in fractions of a millisecond, without the need to validate the syntax, calculate the execution plan, read IO blocks from the disk and perform data aggregation.

On transactional applications there are many such repeatable queries and SafePeak can reduce the overhead of executing them against the database over and over again.

How Does SafePeak work?

SafePeak’s solution to the performance problem is to store the result sets returned by the database in RAM, as is. When a recurring query or SP execution with the same parameters is passed through SafePeak, it will reply from RAM in a fraction of a millisecond instead of sending the query to the database, thus significantly reducing resources from the database server and the database storage.

In order to achieve this behavior, SafePeak reads and analyzes the TDS protocol (the protocol between the application and the SQL Server), making it transparent to any application working with SQL Server.

Data integrity, reliability and high availability

The main challenge with database caching is to understand in real time when data is no longer valid and should be evicted from the cache. Data can become obsolete for numerous reasons: DML, DDL, executing complex stored procedures, triggers etc.

SafePeak solved this complicated challenge with patent-pending algorithms with three parts: 1) Automatic analysis algorithm of the database schema and creation of a relations graph between all the objects in the database: tables, views, triggers, functions, stored procedures, etc.; 2) A self-adaptive algorithm that studies the application traffic, creates patterns of queries and understands their dependant objects; 3) Real-time analysis of the TDS and the T-SQL queries upon arrival of data modification event, and then flushing or refreshing in real-time the relevant cache items results.

The data on the SQL Server is always up-to-date and the cache items that remain in SafePeak memory are always up to date.

SafePeak’s team made sure to protect the application and database from potential glitches of software and hardware. Basically, in single-mode installation the worst case of any failure the traffic will be sent directly to the database server. In cluster installation, SafePeak second, the passive server takes over (Virtual IP) and becomes the active one.

Installation and configuration

SafePeak Installation is quick and simple, and is completed in few minutes. It requires a different server than the database with Windows 2008 64bit or Windows 2008R2 (VMware, Microsoft Hyper-V or any cloud virtual instance/server are supported as well).

Adding a database instance to SafePeak requires mixed authentication on the database but again, it is simple and takes few seconds. From this point SafePeak analyzes the schema and creates the relations graph, a procedure that takes between seconds to an hour and is a one-time operation. Once this is completed, you are good to go.

The application should be pointed (via connection string or etc\hosts file or a change of DNS record) to the SafePeak server instead of the database. If you have Jobs running on SQL Server using the agent then you may need to define an “alias” to the agent that will route all the jobs via SafePeak (but only if want SafePeak to be aware on any updates they may perform).

From this point SafePeak is in auto-pilot, learns the traffic patterns and does caching of whatever is possible.

There is fine tuning that can be done to improve performance in relation to stored procedures that perform dynamic SQL (inform SafePeak which objects are being accessed) and the same operation is required for CLR and encrypted objects. The application will continue to work without the configuration, but the effectiveness of SafePeak decreases. Basically within one hour the application will be accelerated.

User Interface

The nice, easy browser-based GUI allows both product management and also provides tools for performance bottlenecks identifications and analysis.

Click to Enlarge

The product provides a structured way to see performance information starting with the instance level all the way through the queries execution.

On each level the product shows the amount read and records the average response time, the cached response time.

Historical information can be viewed using the product reports.

Conclusion

Overall, I am very impressed with SafePeak as a novel product. SafePeak is a great acceleration solution for users who want immediate results for sites with performance, scalability and peak spikes challenges.

While there are specific environments that will clearly benefit from SafePeak, such as SQL Servers with high SELECTivity and read-intensive databases, it is designed for OLTP applications. Such environments that seem to get the most out of SafePeak’s acceleration are E-commerce, Cloud and Hosted apps, large SharePoint and CRM Dynamics implementations, ERP & CRM applications.

SafePeak can significantly increase response times, by reducing network roundtrip to the database, decreasing CPU resource usage, eliminating I/O and storage access

It is simple, plug-and-play, and easily integrates into the current architecture. If you have an important SQL Server website or enterprise application whose performance affects company sales and productivity – then it is definitely worth giving SafePeak a try.

However, while SafePeak works well with SQL Server OLTP apps, it does not play well with OLAP databases. You can still use SafePeak, but you will need to specially configure CLR objects with SafePeak.

You can download a free, fully functional trial here: www.SafePeak.com/download.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

20 thoughts on “SQL SERVER – SafePeak – The Plug and Play Immediate Acceleration Solution

  1. Answer 2 is correct.

    Option 1 will also work but will not give result in decimals so option 2 is the right answer.

    Thanks

    Sudhir Chawla
    (New Delhi, India)

  2. Testing SafePeak at the moment. There are several things to manage during installation process (Framework not correctly installed, IIS role not correctly installed) before I could use the tool.

    After that I noticed that evaluation on a minimal traffic test system is not very significant. So I have to run a test in hight traffic production enviroment.

    I will report my experience during the “tests” later.

  3. We started setting up a test environment for SafePeak but ended up abandoning the effort due to the way the engine handles dynamic SQL. There’s a lot of configuration overhead for any handling of dynamic SQL and a lot of wholesale dumping of tables from the cache. You also have to route all jobs, SSIS packages, etc through it — anything that modifies data. For a very specialized sort of environment, though, it looks promising. I’d like to have gotten further with it.

  4. So we tested SafePeak. After some problem during installation we encountered connection problems of SafePeak to SQL Server that were not configuration related.

    SafePeak itself is not flexible/dynamic enough for our needs (big-size online shop). You can not directly configure which sql statements to cache. Default is to cache all possible patterns and you have to disable caching for specified patterns or tables. In an database with dozens of tables and much more statements this is nearly impossible to handle.

    The opposite way to enable all patterns and/or tables to be cacheable and a more dynamic pr configurable way to define when cache expires and on what exact updates cache expires would be much more practicable for our needs.

    Unfortunatly we do no longer use SafePeak.

  5. We very much appreciate the feedback and points that have been raised. I want to provide some additional information that could be useful in understanding and using SafePeak more effectively.

    We have customers with very complex database schemas and large databases using applications like SharePoint 2007, Microsoft Dynamics CRM and NAV which have hundreds and even thousands of tables, views, procedures and queries.

    With many systems very little configuration is required (like Dynamics CRM and NAV), while applications that have many procedures with Dynamic SQL or CLR objects can take several hours to fine tune, requiring around 3-6 minutes of configuration per each active dynamic / CLR procedure.

    Here are a few tips, tools and techniques for how to better utilize SafePeak:

    1) Automatic vs Manual Cache Activation:

    You can choose to automatically (default) activate Cache to all “cacheable patterns” or to start with no patterns automatic activation and then activate them manually. This is done in the Settings -> SQL Instances Management -> Instance name -> Instance Configuration – > “Automatically apply patterns” checkbox.

    2) Working with objects, re-order screen, filtering by objects and searching Items:

    In the Cache Management module you can find the objects you are interested in by filtering, using search free text and re-ordering results. By default the most active objects are always shown on the top (tables on the tables screen, SQL patterns on the patterns screen etc.). You can re-order things differently by clicking any column name.

    In Tables Screen you can easily find and “check” the tables you are interested in, and then click “Show Patterns”. This will show you only SQL patterns that access those tables.

    You can also enter free text and click “search” and then see only objects that match your criteria.

    3) Managing cache pattern rules:

    For each SQL query, you have the ability to configure its cache timeout and schedule cache cleanup at certain hours.

    Cache Management -> Click on Instance name -> Click on Database name -> Click “Show Patterns” -> Click on a SQL pattern -> Caching Info / Objects Dependencies

    For each pattern the system automatically identified its “dependencies”: the tables and views it reads from. The cache item will be cleaned from memory on the arrival of an update command to one of those tables. This is how data reliability is achieved.

    You have the option to change this default behavior by removing one of the dependent objects. This allows you to have items in cache for a specifically defined time even if the data behind it is already obsolete.

    We are always looking for ways to improve the user experience and performance for our customers and welcome all to try SafePeak. Please send us questions, ideas and comments to info @ safepeak.com

  6. As a customers, we use SafePeak as a solution for our performance tool.
    We use it on our Microsoft CRM Dynamics 4 use by 1000 call center people.
    SafePeak give us 40%-70% better performance for SQL selects.
    The solution worth then other we test before.
    Very easy to implement and very easy to test if it do the job…. and it do!!!
    The team of SafePeak are very helpful and can help a DBA team to solve a lot of applications performance problems.
    Use it, my recommendation.

  7. Hi Dave,

    This is indeed interesting product. Would you please answer me following questions

    1) The cost of the product.
    2) Does this product only work with SQL Server or does it also help SharePoint?
    3) We have clustered environment, how does this product fit into it?

    Looking forward to answer of these queries and may be other have similar questions.

    • Hi David,

      I’m Alex, I’m from SafePeak Technologies.

      1) The price depends on your environment, number of databases, instances and if you need SafePeak single or cluster installation. Options for monthly subscription and a permanent license both are available.

      2) Sure. Any application that works with SQL Server. SharePoint too. here is a case study what SafePeak can do to your sharepoint:
      http://www.safepeak.com/Customers/Case-Studies/SharePoint-Case-Study

      3) Very easily – For each of your SQL Instances you create an instance on Safepeak (not real SQL Instance, but a instance that points to your SQL Server Instance). If your sql server is a cluster, you simply place your cluster virtual ip (VIP) to SafePeak. Then SafePeak points always to your active SQL Server.

      Please, ping me an email to alexs @ safepeak.com to get more details.

  8. Hi Alex,

    Thanks for your answer, I will pass this info to Sr. DBA – David M.

    Dave M,

    What about your IO performance and bottleneck?

    I sent you email regarding that replication error issue, which you were facing and I am awaiting your response on the same.

    Regards,
    Pinal Dave

  9. Hi Pinal,

    I hope you remember that we met in Mumbai during CTD.

    Here is my quick question to you –
    1) Am I correct that this application uses cacheing mechanism?
    2) If yes, what happens if underlying data changes? – I know the answer will be well – the product will adopt and clear cache for the same.
    3) If data is changing too much – this product will be continuous busy just maintaining itself and will be additional load on system. My perosonal opinion is that this product should be good for only read only workload – am I correct Sir?

    Btw, when are you coming back to Mumbai?

    HS

    • Hi Hardik,

      The SafePeak product is especially designed for real life applicaitons, not just read-only. For example, Microsoft CRM Dynamics, Microsoft SharePoint – all are good examples for applications that do lots of reads and lots of writes (but more read-intensive) that benefit SafePeak.

      The product uses a patent pending mechanism for caching results of queires in memory. The queries can be complicated reports, it can be stored procedures and it can be small but very frequently asked queries. Even client cursors can be cached too.

      SafePeak requires that all traffic to the database will go via SafePeak, so in real-time when an update arrives it learns it, and then cleans the cache from the effected cached results, based on understanding which tables will be updated.

  10. Pingback: SQLAuthority News – SafePeak Releases a Major Update: SafePeak version 2.1 for SQL Server Performance Acceleration « Journey to SQLAuthority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s