SQL SERVER – Faster Application Performance with In-Memory OLTP: Microsoft SQL Server 2014 and SafePeak Dynamic Caching

Today, I’d like to examine how in-memory OLTP engines to vastly improve performance and scalability of SQL Server databases and to accelerate applications.  A very large part of this article focuses specifically on SQL Server 2014 and its new In-Memory OLTP capabilities.

So, let’s dive in.

Achieving fast application response times, high performance and database scalability is critical. Typically, we may have used two approaches looking for performance gains:

  1. Basic application and database read-write code tuning and redesign
  2. Deploying high-spec hardware servers and storage

Both techniques have limitations.  Code re-writes and database redesign cban be lengthy processes, and can introduce bugs into otherwise stable systems.  Bigger hardware and specialized storage technologies can be expensive. And upgrading to larger hardware can also cause increased software licensing costs.

In-Memory to the rescue

In-memory solutions are becoming increasingly popular and software development professionals have started to embed different kinds of In-Memory solutions.  Caching prevents the cached queries from hitting the database at all, thus eliminating the locks, blocks and spinlocks and drastically reducing CPU, Memory and I/O load. The benefit is that storing, accessing and maintaining data IN-MEMORY (instead of on disks) greatly improves application performance and helps Dev and IT teams meet SLA goals.

There are a few different flavors of in-memory technology. Let’s take a look.

Data-Grids (IMDG) and Distributed Caching Solutions

For developers building new applications, a popular approach is to use API/code based Data-Grids (IMDG) and Distributed Caching solutions. Such solutions enable complex parallel processing, event processing and generally help to build highly scalable, high performance applications (such as large social networks’ websites and real-time stock-trading platforms).  But major code development effort is required. Developers are required to not only put (and get) data into In-Memory Cache but also to mimic database transactional logic.  Maintaining this code can get complicated.

For most production databases and applications such major code changes are not a feasible option.

In-Memory Dynamic Caching

In-memory dynamic caching is another approach that is very well suited to accelerating live in-production applications and databases.  In-memory dynamic caching does not require additional code in applications, or modifications to databases.  This key advantage over Data Grids and Distributed Caching solutions (and over the SQL Server 2014 In-Memory OLTP engine, as we will see) makes it much easier technology to deploy. Because there is no requirement to touch app-code, it also means this approach will accelerate 3rd-party applications that use SQL Server, like SharePoint or Dynamics.

With in-memory dynamic caching, all queries and stored procedures are analyzed and managed automatically: their logic and dependent objects (like tables, views, triggers, procedures, etc.) are identified and analyzed.  All read-based queries and procedures leverage the in-memory cache. Write commands are monitored and cache invalidation is applied in real-time.

Because in-memory dynamic caching is the easiest solution to deploy currently, I’ll take a closer look at the leading vendor in this category later in this article. But let’s first take a hard look at in-memory OLTP from SQL Server 2014.

Introduction to SQL Server 2014 In-Memory OLTP

Microsoft’s newly released SQL Server 2014 features the In-Memory OLTP engine (some even define this as a revolution).

I want to examine SQL Server 2014 In-Memory OLTP from different angles: how to start using it, provide directions for migration planning, review closely many of its limitations, discuss SQL 2014 In-Memory OLTP applicability and see where the SQL Server In-Memory OLTP can be an alternative to in-memory dynamic caching, and where it is complimentary.

SQL Server 2014’s biggest feature is definitely its In-Memory transaction processing, or in-memory OLTP, which Microsoft claims make database operations much faster. In-memory database technology for SQL Server has long been in the works under the code name “Hekaton”.

Microsoft built its new In-Memory OLTP query processing engine from scratch, using a new lock-free and latch-free design. This is a key differentiator from alternatives such as pinning tables with DBCC PINTABLE or from putting databases on SSDs. While “DBCC PINTABLE” places a table in SQL Server’s buffer pool and the SSDs provide higher I/O, relational locks and latches still exist.

SQL Server 2014 In-Memory OLTP engine uses a new optimistic multi-version concurrency control mechanism. When a row in a shared buffer is modified, the In-Memory OLTP engine makes an entirely new version of that row and timestamps it. This process is very fast because it’s done entirely in memory. The engine then analyzes and validates any updated rows before committing them. This design is faster and more scalable than the traditional locking mechanism used by SQL Server’s relational database engine because there are no locks or other wait-states that prevent the processor from running at full speed.

In order to start using the SQL 2014 In-Memory OLTP, a database must have certain tables (actually, the file groups used to store tables) declared as memory-optimized. The resulting table can be used as a conventional database table or as a substitute for a temporary table.

Additional speed improvements are made from the gains realized by keeping a table in memory. Reads and writes to an in-memory table only lock on individual rows, and even then, row-level locks are handled in such a way that they don’t cause concurrency issues. Stored procedures can also run in memory for more of a boost by having them compiled to native code.

Excellent performance for DML write commands (Inserts, Update, Delete)

In classic disk tables there is a latch contention: concurrent transactions are attempting to write to the same data page; latches are used to ensure only one transaction at a time can write to a page.

The In-Memory OLTP engine is latch-free; there is no concept of pages. Thus, concurrent transactions do not block each other’s inserts, thus enabling SQL Server to fully utilize the CPU.

Microsoft released benchmarks show the following performance difference for INSERT intensive tables (run on a server with 2 CPUs, with 24 logical cores):

Disk-based tables Memory-optimized tables Performance difference
10,000,000 order inserts using 100 threads, with 100,000 inserts each 1:31:11 0:01:49 50X

Read (SELECT) commands can gain significant benefit as well. The new engine allows creating “Native” pre-compiled Stored Procedures can access data of memory-optimized tables and perform data changes.  With the use of In-memory tables there are significantly less (but not zero) locks, blocks, spinlocks and of course the I/O scans.

Quick start guide to SQL Server 2014 In-Memory

Step #1: Add MEMORY_OPTIMIZED_DATA filegroup to enable in-memory OLTP for your Database:

IF NOT EXISTS (SELECT * FROM sys.data_spaces WHERE TYPE='FX')
ALTER DATABASE CURRENT ADD FILEGROUP [AdventureWorks2012_mod] CONTAINS MEMORY_OPTIMIZED_DATA
GO
IF NOT EXISTS (SELECT * FROM sys.data_spaces ds JOIN sys.database_files df ON ds.data_space_id=df.data_space_id WHERE ds.TYPE='FX')
ALTER DATABASE CURRENT ADD FILE (name='AdventureWorks2012_mod', filename='$(checkpoint_files_location)AdventureWorks2012_mod') TO FILEGROUP [AdventureWorks2012_mod]

Step #2: For memory-optimized tables, automatically map all lower isolation levels (including READ COMMITTED) to SNAPSHOT:
ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON

Step #3: Create your specific Table(s) to be Memory_Optimized:

To use In-Memory OLTP, you define a heavily accessed table as memory optimized. You have to define the primary key settings advanced settings when you create the table (can’t be altered later).

Notice the HASH, BUCKET_COUNT and MEMORY_OTIMIZED settings:
CREATE TABLE [Sales].[SalesOrderDetail_inmem](
[SalesOrderID] UNIQUEIDENTIFIER NOT NULL INDEXIX_SalesOrderID HASH WITH (BUCKET_COUNT=1000000),
[SalesOrderDetailID] [int] NOT NULL,
[OrderDate] [datetime2] NOT NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL INDEXIX_ProductID HASH WITH (BUCKET_COUNT=10000000),
/*
...
...
*/
INDEX IX_OrderDate (OrderDate ASC),
CONSTRAINT [imPK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY NONCLUSTERED HASH
(
[SalesOrderID],
[SalesOrderDetailID]
) WITH (BUCKET_COUNT=10000000)

) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA)

Step #3 – Update statistics for memory-optimized tables:
UPDATE STATISTICS Sales.SalesOrderDetail_inmem
WITH FULLSCAN, NORECOMPUTE

Step #4 – CREATE stored procedures to be “NATIVE_COMPILATION”:

Stored procedures that only reference Memory_Optimized tables can be natively compiled into machine code for further performance improvements.
CREATE PROCEDURE Sales.usp_InsertSalesOrder_inmem
@SalesOrderID UNIQUEIDENTIFIER OUTPUT,
@DueDate datetime2 NOT NULL,
@OnlineOrderFlag bit NOT NULL,
@Comment NVARCHAR(128) = NULL,
@SalesOrderDetails Sales.SalesOrderDetailType_inmem readonly
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN
ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
/*
Your procedure code
Your procedure code
Your procedure code
*/
END

Migrating to SQL Server 2014 In-Memory OLTP

Migration to In-Memory OLTP has to be performed in a development environment and carefully tested. Your High-Availability design, Databases design, Tables schemas and data, stored procedures, business logic in the database and even application code – all may require many syntax changes to use In-Memory OLTP.

This is not a “click and migrate” process. It requires development cycles, application and database design and code changes.

The right way to use the In-Memory OLTP engine is:

  1. Plan your production database architecture. The In-Memory OLTP is very different and has many limitations in terms of H/A, Mirroring, Replications available functionalities;
  2. Plan carefully your new database (and possibly application) design;
  3. Migrate several specific tables and procedures that are good benefit candidates;
  4. Develop or change your business-logic to fit the new design;
  5. Test and evaluate;
  6. Deploy

To evaluate whether the In-Memory OLTP can improve your database performance, you can use Microsoft new AMR tool (Analysis, Migrate and Report). For helping with actual migration you can use the Memory Optimization Advisor for tables and the Native Compilation Advisor to help porting a stored procedure to a natively compiled stored procedure.

The AMR tool helps identifying the tables and stored procedures that would benefit by moving them into memory and also help performing the actual migration of those database objects. The AMR tool is installed when you select the “Complete” option of “Management Tools”, and is later accessed through SQL Server Management Studio (SSMS) in Reports  –>> Management Data Warehouseà Transaction performance reports tool:

The AMR tool provides reports which tables and procedures can benefit the most from In-Memory OLTP and provide a hint how complex will be the conversion. The reports show either recommendations based on usage, contention and performance. Here is example (graphics may change in the GA release):

After you identify a table that you would like to port to use In-Memory OLTP, you can use the Memory-Optimization Advisor to help you migrate the disk-based database table to In-Memory OLTP. In SSMS Object Explorer, right click the table you want to convert, and select Memory-Optimization Advisor.

Limitations of SQL Server 2014 In-Memory OLTP

The In-Memory addition to SQL Server 2014 does not just expand the main features of the SQL Server, but rather it is a completely new engine. Just like any new software, the SQL Server In-Memory engine has limitations. Many things have to be planned and defined differently.

The below list is not full, but rather represents many major non-supported features for your consideration:

Server and Database limitations

  • REPLICATION is not supported – Officially not supported, although in-memory tables can be defined as Subscribers (but not Publishers)
  • MIRRORING is not supported
  • DATABASE SNAPSHOT is not supported
  • 250GB limit per server – Total data in all loaded in-memory tables cannot exceed 250GB
  • MARS is not supported – Multiple Active Result Sets (MARS) is not supported with natively compiled stored procedures, so your application can’t use MARS connection to talk with the database
  • Change Data Capture (CDC) is not supported
  • DTC (distributed transactions) are not supported
  • RTO (Recovery Time Objective) of your High Availability – the Starting and Recovery time is slower – For every database object (table, stored procedure) SQL Server has to compile and link the corresponding DLL file (that is loaded afterwards into the process space of sqlservr.exe), and this also takes some time. The compilation and linking is also performed when you are restarting your SQL Server, or when you perform a cluster failover.

SCHEMA, KEYS, INDEXes, TRIGGERS  limitations:

  • FIXED Schema – You have to design your in-memory tables with knowledge of your data size. Indexes, statistics, and blocks cannot be changed / applied later.
  • ALTER TABLE for existing disk table is not supported – You can’t alter existing tables to become memory-optimized. You have to create new tables that are memory-optimized.
  • ALTER TABLE for in-memory table is not supported – You cannot add another column to a memory-optimized table in your production.
  • Row size limited to 8060 bytes
  • FOREIGN KEY’s and CHECK CONSTRAINTS are not supported
  • Datatypes:
    • Datetimeoffset, Geography, Hierarchyid, Image, Ntext, Text, Sql_variant, Varchar(max), Xml, User data types (UDTs) – not supported
    • CHAR and VARCHAR – Should be replaced to n(var)char
  • Various not supported TABLE and COLUMN definitions: IDENTITY, ON (file group or partition), Data type [name], Computed Columns, FILESTREAM, SPARSE, ROWGUIDCOL, UNIQUE
  • INDEX limitations: no COLUMNSTORE, CLUSTERED INDEX, no LOB datatypes
  • DDL TRIGGERS and Event Notifications (Server and Database level) – Have to be removed before creating or dropping memory-optimized tables and/or natively compiled procedures
  • LOGON TRIGGERS do not affect memory-optimized tables
  • DML TRIGGERS cannot be defined on memory-optimized tables – You can explicitly use stored procedures to insert, update, or delete data to simulate the effect of DML triggers.

T-SQL non supported operators:

  • Classic: OR, NOT, IN, LIKE, BETWEEN, CONTAINS, PERCENT, DISTINCT, NEXT VALUE FOR
  • UNION’s are  not supported
  • MIN, MAX – limited to non strings
  • LEFT / RIGHT / FULL OUTER JOIN – Outer joins return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions – not supported (only Inner Joins are supported).
  • FOR XML, FOR BROWSE
  • Dynamic SQL (EXECUTE, EXEC) not supported
  • CURSORs are not supported
  • Sub-Queries are not supported

Transactions, Cross Database queries and

  • BEGIN, COMIT, ROLLBACK are not supported – “Atomic Blocs” are an alternative
  • Cross Database queries and transactions are limited – You cannot access another database from the same transaction or the same query that also accesses a memory-optimized table. You can create Table Variables, create two transactions: 1) insert the data from the remote table into the variable; 2) Insert the data into the local memory-optimized table from the variable.
  • MERGE – A useful feature with performing insert, update, or delete operations on a target table based on the results of a join with a source table.

Functions, Views and Stored Procedures

  • User-defined functions cannot be used in natively compiled stored procedures
  • No In-Memory (“Native”) Functions
  • VIEWs – Views cannot be accessed from natively compiled stored procedures.
  • Disk-based tables cannot be accessed from natively compiled stored procedures. 

T-SQL window functions are not supported at all. Examples:

  • ROW_NUMBER()
  • RANK()
  • OVER (PARTITION BY) or OVER (PARTITION BY)

First conclusions about SQL Server 2014 In-Memory OLTP

Microsoft took a huge step with the SQL Server 2014 In-Memory OLTP, allowing developers to create high-speed and more scalable applications and databases. Embedding the new In-Memory OLTP engine inside SQL Server allows combining both classic disk-based tables and procedures together with the In-Memory tables and natively compiled stored procedures. This gives you amazing tools to create high speed enterprise and web-scale applications.

But the In-Memory OLTP has many limitations. It lacks support for many classically used T-SQL operators and functionalities, and many complications exist to migrate existing apps. The new In-Memory engine is not automatic to deploy, nor plug-and-play friendly. It’s designed to be carefully defined, developed and tested. Migration of existing applications to benefit In-Memory OLTP is possible but only in cases where you are able to develop and maintain the needed changes in the database and the application.

The SQL Server 2014 In-Memory OLTP is a great framework that is really focused on building new applications or new application-parts that will benefit from the In-Memory engine. For new apps it is also not a silver bullet solution to fit any problem, challenge or need, but rather a mission-specific tool.

In-Memory Dynamic Caching: An SQL Server 2014 In-Memory OLTP alternative or complimentary?

We have reviewed briefly the main in-memory data caching alternatives (data-grids, distributed caching, and dynamic caching).  Of these, dynamic caching can be the easiest to deploy. Our examination of SQL Server 2014 In-Memory OLTP shows it to be a very interesting solution but one that has various limitations making it tough to use for existing production applications.  So the obvious question is this: how do Dynamic Caching and the new SQL Server In-Memory OLTP engine compare?  Are they complimentary? What’s best to use today?

So we can ground the conversation in reality, let’s compare the leader in dynamic caching for SQL Server, SafePeak, with SQL Server 2014 In-Memory OLTP.

SafePeak In-Memory Dynamic Caching

SafePeak is a query-level automated caching middleware for SQL Server based operational applications. SafePeak’s software solution combines three ideas:

  1. Result-based data caching for repetitive queries and stored procedures;
  2. In-memory databases that automatically keep transaction data integrity; and
  3. A solution that can fit immediately to production applications (including closed third party apps) with no code changes and minimal efforts for deployment.

SafePeak can accelerate the performance of applications and databases by In-Memory Caching of results from repetitive SQL queries and SQL stored procedures, while maintaining 100% ACID compliance and data integrity. SafePeak answers queries in microseconds (<0.001sec). SafePeak result-based dynamic caching prevents cached queries from hitting the database at all, thus eliminating the locks, blocks and spinlocks and drastically reducing usage and of I/O, CPU and Memory.

The SafePeak solution is software only. It doesn’t require any specialized hardware. And it doesn’t require any changes to apps or databases to deploy (unlike data grids).   It’s based on “smart auto-learning”, where all queries and stored procedures are analyzed automatically. It identifies and analyzes logic and dependent objects (like tables, views, triggers, procedures, etc.) for you.  And as applications evolve, so too does cached data.

Critically, it has advanced data integrity and eviction processes.  SafePeak will determine if an incoming query is an update, insert, alter or any other request that may cause a change in the database. In this case, SafePeak dissects the request and decides which tables in the database may be affected by its execution. It then looks at the query results stored in the Cache Manager and evicts all results that have any connection to the affected database tables.

Besides the auto-pilot mechanism, SafePeak’s management dashboard allows you to tune and refine caching effectiveness.

For example, with a good understanding of the application, a user can configure caching rules for certain SQL queries, stored procedures or even tables and views to have reduced cache invalidation sensitivity to table updates, in order to keep the data in cache. Consider a use-case like “Get details of order #123” command, where the result does not change with entrance of new orders.

Another example is a “cache warm-up” mechanism, in which you create a list of queries that are scheduled to enter SafePeak’s Cache.

SafePeak and SQL Server 2014 In-Memory OLTP

Now that we’ve looked at SafePeak’s software for dynamic caching, the following chart lets us quickly compare it to SQL Server 2014 In-Memory OLTP.

Attribute SQL Server 2014In-Memory OLTP SafePeakIn-Memory Dynamic Caching
Data In-Memory All table data Hot Data only
Type of data in memory Raw tables data Result-sets(of Queries and Procedures)
RAM requirements High ** All table data is loaded to memory Medium *
* Only Hot and Computed data is in-memory
Middleware No Yes
Response time from memory Micro to milliseconds Microseconds
Fast speed for “first query call” Yes No *
* Possible via cache warm-up
Read:Write ratio affects the Read efficiency performance No Yes
READ queries acceleration High High
WRITE queries acceleration High Low
No Locks on tables Yes No* Locks are reduced but only due to less load on the server
SQL Server Support 2014 only 2005, 2008, 2012(2014 to be supported during 2014)
Full T-SQL and Schema support No, has many limitations Yes
Replication, Mirroring support No Yes
Database / Application Migration requirements Complex None
Time to migrate and deploy for existing production application Long Short
Fits 3rd party applications (SharePoint, Dynamics…) No Yes

The initial comparison shows that SafePeak In-Memory Dynamic Caching is a better fit for general cases of production and custom applications (including OLTP and Reporting applications), especially where there are significantly more reads than writes.  The SafePeak technology also accelerates 3rd-party applications like Microsoft SharePoint, Microsoft Dynamics, or BMC Remedy for example.

SafePeak focuses on caching Reads, but has also a limited in-direct acceleration effect on Writes.

As a by-product, SafePeak also can improve the scalability of SLQ Server databases, thanks to two situations:

  1. Reads no longer tax database I/O, CPU or memory resources – which is especially helpful to databases that are serving “chatty” applications;
  2. Databases have freed-up resources now available to serve more users and more transaction workloads.

The SQL In-Memory OLTP engine is focused on loading specific tables into memory, creating specific queries and procedures calls for them and getting great performance on those tables. However many changes may be required to the database and the application. Performance and scalability improvements for very specific tasks, like Writes or Reads that are always-in-memory, can be much higher than SafePeak, but these improvements come with time and development costs. Also, SQL Server In-Memory OLTP engine does not answer all complex load and performance challenges.

The best answer probably is that both SafePeak Caching and the Microsoft In-Memory OLTP can be either chosen for different tasks or complement each other:

  • Where SafePeak “virtualizes” and accelerates the hot-data from all most-accessed tables, views and procedures, and
  • Where the SQL Server In-Memory OLTP boosts performance of a few very intensive write-and-read tables.

This has been a long article, but I hope it helps you better understand how In-Memory Data Cache solutions can address your SQL Server database performance and scalability.

If you’re curious to evaluate SafePeak software you can download free software from their website (www.safepeak.com/download).  It comes with technical training and free assistance support, which I always recommended taking advantage of.

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

About these ads

SQL SERVER – Optimizing Three Important Resources – CPU, Memory and Disk

For any DBA there are three most important resources – CPU, Memory and Disk. Here is a real life story just happened recently which discusses CPU, Memory & Disk along with a new manager and frustrated employee. Well, thanks to Dell Software’s Spotlight on SQL Server Enteprise,  the story has a happy ending.  Trust me, you will enjoy this mini story, so continue reading.

Beginning of the Story

Recently, I was traveling to Delhi to speak at a popular annual conference. Due to a lenghy commute, I decided to arrive one day early to catch up with an old friend. My friend and I had plans to have dinner together as we had not seen each other for quite a while. However, when I called up my friend at 4 PM, he said he will be not able to join me and encouraged me to go to dinner alone. Trust me – I was here early to catch up with my friend, so dinner was not the priority. I immediately asked the usual question WHY? He replied that he has a new boss that is giving him a hard time with the database server.

After listening to his situation for about 10 minutes, I asked him if I can join him in his office to help him understand what is going on with his servers. If I can be any help and resolve the problem, maybe we can still catch up for the dinner, if not, well it’s still fun to work with a new database server and solve its problem. I arrived shortly at my friend’s office and he guided me to his cubicle.

My friend was recently moved to a new team in his organization where he was responsible of managing a critical server, but this server was suffering from a slow performance  issue. To make the matter worse, his team had a new manager who had just joined two days prior. In this world, there are many great managers (I have been rather fortunate), but there are always  people with “room for improvement”. His manager was only two days into his role, but due to this tense situation, he got aggravated at my friend and asked him to solve a problem before the end of the day. Additionally, he asked him to prepare the details related three important resources – CPU, Memory and Disk (with a presentation to match).

The Resources – CPU, Memory and Disk

For any system, there are three major resources that need to be accounted for – CPU, Memory and Disk . It is extremely crucial to know how each of these resources are used properly and optimally in any system. If due to any reason, any one of the resources is over used or under used, it can create a negative effect on the performance of the entire server. Here my friend was challenged to solve this problem very quickly –  as well as was asked to create reports on the subject. Well, this was indeed not easy for him to do in such a short period of the time.

Spotlight on SQL Server to the Rescue

Honestly, I knew this was not going great. I asked my friend that there is no way he can do everything in such a short period of the time. He needs to now depend on third party tools which are built for performance analysis and tuning. Sometimes it makes sense to leave the expert’s work to experts. There is simple no need to re-engineer the entire wheel. I suggested he download Dell Software’s Spotlight on SQL Server Enterprise and install it on his production server. The installation is pretty straight forward and right after it was installed, it brought up following screen. There are four sections in the Home Page sections.

  1. Sessions (Active sessions)
  2. SQL processes (CPU usages)
  3. SQL Memory (Buffer Cache)
  4. Disk Storage (Log File and Indexes)

When you look at each of the sections you can find various information very clearly and easily. It is absolutely impossible to explain each section in words, hence, I have created this 150 seconds video which explains how each of the sections works. Trust me, it is very easy to use tool.

Finally – Dinner Time

Well, my friend immediately installed Spotlight on SQL Server Enterprise on his server and was able to discover various issues in no time. In our case we had issues with blocked processes as well as slow disk. With the help of the following two areas on the home screen, we were able to quickly resolve the problem on his production server. Once the problem was solved, his manager was presented with various screenshots and graphs from the tool itself  – he was rather delighted to see all the tabular information,  as well as in a graphical way. Around 8 PM we left the office for dinner.

Today

If you wonder what is the status of my friend’s organization today, here is the quick note.

Action Item for my friend – He needs to pay for our dinner next time.

Action Item for you – Download Dell Spotlight on SQL Server Enterprise today and let me know what you think?

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

SQLAuthority News – Microsoft Whitepaper – Optimizing Your Query Plans with the SQL Server 2014 Cardinality Estimator

I have been reading this Whitepaper from a couple of days and I am yet not done reading it completely, but I think it is one of the best white papers I have read in the recent time. First of all, it is written by my friend and SQL Expert Joe Sack. If you know Joe, you know that he is blessed with the skill to make a complex subject very easy. There are very few experts can do this. Kudos to Joe for amazing white paper. This white paper is 43 pages long and over 10,000 words, but trust me, every single word is worth it. I am planning to complete this whitepaper this weekend.

Every new version of SQL Server gets new performance enhancement features. In SQL Server 2014 SQL Server Query Optimizer cardinality estimation process got a major overhaul. A new algorithm for the cardinality estimation now has improved accuracy, consistency and suitability of key areas within the cardinality estimation process. In simple words, it is going to improve performance of queries executed on SQL Server.

Here is the paragraph from the Whitepaper which describes what actually this white paper is going to include:

The SQL Server query optimization process seeks the most efficient processing strategy for executing queries across a wide variety of workloads. Achieving predictable query performance across online transaction processing (OLTP), relational data warehousing, and hybrid database schemas is inherently difficult. While many workloads will benefit from the new cardinality estimator changes, in some cases, workload performance may degrade without a specific tuning effort. In this paper, we will discuss the fundamentals of the SQL Server 2014 cardinality estimator changes. We will provide details on activating and deactivating the new cardinality estimator. We will also provide troubleshooting guidance for scenarios where query performance degrades as a direct result of cardinality estimate issues.

I personally believe there are so many new enhancements in SQL Server that sometimes, I feel foreign to this entire subject. I feel that SQL Server has moved ahead and I am still working with version n-1. However, when I come across white paper like this, I regain my own confidence as now I know I can depend on this white paper to learn what are the new features available. Later on I can go and implement this feature on production server and master the basics as well as advanced concepts.

Download the white paper from here. 

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

SQL SERVER – Presenting 4 Technology Sessions at Great Indian Developer 2014 – Contest

The Great Indian Developer Conference (GIDS) is one of the most popular annual event held in Bangalore. This year GIDS is scheduled on April 22, 25. I will be presenting total four sessions at this event and each session is very different from each other.

Event Location: J. N. Tata Auditorium
National Science Symposium Complex (NSSC)
Sir C.V.Raman Avenue, Bangalore, India

Event Date: April 22-25, 2014 (Tuesday-Friday)

Website: http://www.developermarch.com/developersummit/ (You can find registration information over here).

I will be presenting at total 4 sessions during this event.

Indexes, the Unsung Hero

April 22, 2014 – Time:13:35-14:35 | 60 mins | GIDS.Net | Conference

Slow Running Queries are the most common problem that developers face while working with SQL Server. While it is easy to blame SQL Server for unsatisfactory performance, the issue often persists with the way queries have been written, and how Indexes has been set up. The session will focus on the ways of identifying problems that slow down SQL Server, and Indexing tricks to fix them. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session.
Indexes are the most crucial objects of the database. They are the first stop for any DBA and Developer when it is about performance tuning. There is a good side as well evil side to indexes. To master the art of performance tuning one has to understand the fundamentals of indexes and the best practices associated with the same. We will cover various aspects of Indexing such as Duplicate Index, Redundant Index, Missing Index as well as best practices around Indexes.

SQL Server Performance Troubleshooting: Ancient Problems and Modern Solutions

April 22, 2014 – Time:15:55-16:55 | 60 mins | GIDS.Net | Conference

Many believe Performance Tuning and Troubleshooting is an art which has been lost in time. However, truth is that art has evolved with time and there are more tools and techniques to overcome ancient troublesome scenarios. There are three major resources that when bottlenecked creates performance problems: CPU, IO, and Memory. In this session we will focus on High CPU scenarios detection and their resolutions. If time permits we will cover other performance related tips and tricks.
At the end of this session, attendees will have a clear idea as well as action items regarding what to do when facing any of the above resource intensive scenarios. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session. To master the art of performance tuning one has to understand the fundamentals of performance, tuning and the best practices associated with the same. We will discuss about performance tuning in this session with the help of Demos.

MySQL Performance Tuning – Unexplored Territory

April 25, 2014 – Time:10:45-11:30 |45 mins | GIDS.Data | Conference

Performance is one of the most essential aspects of any application. Everyone wants their server to perform optimally and at the best efficiency. However, not many people talk about MySQL and Performance Tuning as it is an extremely unexplored territory. In this session, we will talk about how we can tune MySQL Performance. We will also try and cover other performance related tips and tricks. At the end of this session, attendees will not only have a clear idea, but also carry home action items regarding what to do when facing any of the above resource intensive scenarios. Developers will walk out with scripts and knowledge that can be applied to their servers, immediately post the session. To master the art of performance tuning one has to understand the fundamentals of performance, tuning and the best practices associated with the same. You will also witness some impressive performance tuning demos in this session.

Hidden Secrets and Gems of SQL Server We Bet You Never Knew

April 25, 2014 – Time:15:00-17:45 | 150 mins | GIDS.Tutorials | Conference

It really amazes us every time when someone says SQL Server is an easy tool to handle and work with. Microsoft has done an amazing work in making working with complex relational database a breeze for developers and administrators alike. Though it looks like child’s play for some, the realities are far away from this notion. The basics and fundamentals though are simple and uniform across databases, the behavior and understanding the nuts and bolts of SQL Server is something we need to master over a period of time.

With a collective experience of more than 30+ years amongst the speakers on databases, we will try to take a unique tour of various aspects of SQL Server and bring to you life lessons learnt from working with SQL Server. We will share some of the trade secrets of performance, configuration, new features, tuning, behaviors, T-SQL practices, common pitfalls, productivity tips on tools and more.

This is a highly demo filled session for practical use if you are a SQL Server developer or an Administrator. The speakers will be able to stump you and give you answers on almost everything inside the Relational database called SQL Server.

Surprise Gift

If you attend my session, I will be asking one question at the end of the each of my sessions. If you get it right, you can win something surprise gift from me.

Must Attend Sessions

Besides above four sessions of mine, I will be attending the following sessions while I am at GIDS. Read the blog post of Vinod Kumar for additional information about his session.

April 22, 2014 – Time:10:35-11:35
SQL Server Management Studio – Tips and Tricks ~ Vinod Kumar

April 22, 2014 – Time:11:45-12:45
Architecting SQL Server HA and DR Solutions on Windows Azure  ~ Vinod Kumar

April 22, 2014 – Time:14:45-15:45
Understanding Windows Better Using SysInternals ~ Vinod Kumar

April 25, 2014 – Time:14:05-14:50
Introduction to Microsoft Power BI ~ Vinod Kumar

April 25, 2014 – Time:14:05-14:50
Lazy Commit Like NoSQL with SQL Server ~ Balmukund Lakhani

April 25, 2014 – Time:15:00-17:45
Hidden Secrets and Gems of SQL Server We Bet You Never Knew ~ Balmukund Lakhani, Pinal Dave, Vinod Kumar

NOTE: If you are not going to attend this event, sign up for the newsletter over here: http://bit.ly/sqllearn. I will be sharing my code, demo, slides and all the relevant information in the newsletter.

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

SQL SERVER – SafePeak SQL Server Acceleration Software Gets an Upgrade

SafePeak, the SQL Server application acceleration software, gets a new version upgrade and launches on Amazon Cloud Marketplace.

SafePeak’s idea in a nutshell is simple.

SafePeak is a query-level automated caching middleware for SQL Server-based operational applications. SafePeak accelerates apps by caching SQL queries while keeping a 100% ACID compliance and data integrity. SafePeak answers queries in microseconds (<0.001sec), consistently and reliably. The solution requires no code-changes to your apps, is based on smart auto-learning, is auto-adaptive and contains safety mechanisms. The company reports that its newest version (2.1.123) is more automated, performs a deeper analysis and is easier to use – resulting in even better performance improvements.

Caching for SQL? Who does that?

Today’s database servers are very busy keeping up with demands from applications and web servers. Data volumes continue to grow and workloads are increasingly complex.  All this adds up to more strain on a database. When application performance suffers, IT managers, Dev managers and Architects hear about it and need to find solutions.

A widely used approach to enhance database performance is called caching. Caching is usually applied in one of three ways. Software-level caching can be applied at either the application-level (like Memcached) or at the data-management level (as with in-memory databases or data grids). The third approach is hardware-level caching and involves bigger servers (adding CPU, RAM) and faster IOPS storage (using I/O caching like Fusion-IO, or not-so-cheap Flash storage).

Many applications’ managers don’t have resources for a lengthy do-it-yourself caching project. DIY caching can be technically challenging, and it can require significant changes to applications, which are simply impossible to make for commercial 3rd-party apps.

Pressed for time, many people may be forced into “throwing more hardware” at the problem.  It’s a classic approach, although expensive and sometimes one that comes with a different set of complications (downtimes, upgrades, etc.).

SafePeak – Easier, Better, Faster

SafePeak offers an easier and better way to accelerate SQL Server performance. SafePeak’s software solution gives all of the benefits of caching, but without any app re-programming. It works for your custom apps and 3rd-party apps. And its app acceleration encompasses all of an app’s queries and procedures traffic at the same time.

SafePeak is a modern SQL Server caching alternative: a smart, automated and comprehensive data caching layer – kind of a combination of Memcached caching and an In-Memory database, but enhanced with machine learning intelligence.

Comparing alternatives, SafePeak’s best advantage is probably its time-to-deployment. It’s fast to get up and running.  Deployed mostly on virtual machines, it takes only a few hours of initial configurations and almost no downtime (connection-string IP change).  And that’s it.  Custom-built and 3rd party apps using SQL Server, like SharePoint or CRM Dynamics, all experience dramatic performance improvements.

If your SQL Server-based apps are starting to slow down, SafePeak may be a simple solution.  SafePeak offers a trial (www.safepeak.com/download) with technical training & assistance support (recommended, and they’re super nice guys, too!).

Life is good!

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

SQL SERVER – Monitoring Your Server at a Glance – A Open Conversation with DBAs

A few days ago I was visiting a large Database Shop and I happened to meet quite a few DBAs there. After my session was over we all went to the company’s cafe for a quick coffee. While having a coffee in the relaxed environment, I shared details about my recent vacation with my family. After sharing a quick story of my vacation, I asked the DBAs around me about their own vacations. To my surprise many of the DBA’s have not taken a holiday for quite a some time. Upon asking why they have not taken holiday the answer was simple (and as expected) – due to the job.

Excuse for No Vacation

Here are a few reasons I received from them why they do not take days off from their job.

“I do not want to leave, I do not know the guy who will manage the server after me. He may not know where to look when things go wrong.”

“Forget about days off, almost every other night I have to wake up to fix something on my server, I do not remember when I slept 6 hours without waking up to look at the server.”

“I am new in the job and I have lots to learn. Honestly, when something goes wrong, I have no clue where to look so I take lots of time to figure things out.”

There were quite a few responses in the same line; I realized that DBAs do want to take a break from their work and enjoy time with their friends and family, however, due to some reasons (insecurity, possessiveness, inexperience or just pure love for the job) they are not able to take breaks.

A Key Question and Honest Answer

Here is the key question I asked them back -

What one thing you think you need in your job, to be able to take a much-needed vacation without any stress or worry?”

I got a very interesting answer to my question-

“We need a single screen for all of our systems, which can help us know what a exactly going on on our server at all times.”

A very interesting answer – what every DBA wants is a tool, feature, or a utility that can tell them what exactly is going on in their server every single time, consistently and accurately. I think this is a fair request. I am a DBA and Developer and I know there are challenges in our job. We are often so familiar with our application that it is not possible for other DBAs to know what is going on in our system. When something goes wrong, they all have to run to us as we have the solution from our experience. Sometime I am worried to leave my server alone as I think some developer will deploy a code that will make the server behave slowly or sluggishly.

Honestly, this is not a healthy environment. You need to have transparent system where you know what is going on in your server with a single glance and if you want more information, you should be able to get it by double clicking it. Sometimes there is no out-of-the-box solution for the product itself. We need to depend on third party solutions or experts who know what they are doing.

Here is what I do

As soon we were done with coffee, I immediately opened my laptop and displayed Spotlight on SQL Server Enterprise. When I was done displaying various features of the tool, I could see a ray of hope in the eyes of the DBAs. I could sense that they did not know about this product or they have not seen anything like this. Here is the home screen of the Spotlight on SQL Server Enterprise.

You can see in the above image there are many sections for various important monitoring areas of SQL Server.

Sessions: This section demonstrates the various details about your sessions like how many sessions are open, how many different computers are connected as well as how many active sessions there are. The one which attracted my attention was details about the response time. This shows the full round-trip response time for of a query representative of general workload.

SQL Processes: This section demonstrates how many total, system, and user processes are. We can also see blocked processes there as well. The best part is that we can right click on any area of this home screen and see further details. I right clicked on User Processes and selected Session Details.

It immediately demonstrated the display of various user sessions and the script which was running in the session.

SQL Memory: This section demonstrates a very vital information about memory – like how big is my buffer or how much of my cache is being used. I also love the section of procedure cache, because we do not often track something this important when we are talking about SQL and memory. Here is the image of my computer’s Cache Size by Object Type which displays various object types and size taken from my memory cache.

Disk Storage: This section talks about IO activities, keeping in mind database workload. We can see details about various data files, filegroups, database count, log files but my favorite section is MAX IO/Wait which is at the bottom of the section. We can see various Wait types and upon clicking details we can see Diagnose I/O Pressure as well.

There are plenty of things to discuss about this tool. There is animated movement of packets, batches, logical reads, checkpoints etc, it just gives us everything we want to know at a glance. If we want to see any further details we can just right click and see more details about that particular object or activity.

Here is the best part – if there is anything where we need to focus or get alerted, Spotlight on SQL Server Enterprise will turn that to red and will send email alerts.

Moreover, Spotlight on SQL Server Enterprise makes it even easier for DBAs to go on vacation… and still keep an eye on the servers! By using Spotlight Mobile which, by the way is FREE for Spotlight users,  you can monitor the health of your SQL Server environment via the Heatmap, view an alarms list, get alarm details and have the ability to snooze, or acknowledge alarms anywhere, anytime and on any device!

Well, I will stop here today, as this is what I demonstrated in the cafe to my DBA friends. After watching demos, they had promised me that they will download the trial and give this product a shot and see if this can earn them a vacation.

I would like to know your opinion about this project. You can download the fully functional 30-day free version and install it on your machine.

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

Big Data – Real-Time Analytics Performance with ClustrixDB

Note: The product used in comparison is ClustrixDB. It is available to download for FREE.

NewSQL databases provide scale-out of NoSQL without giving up on SQL or ACID transactions. While most NewSQL databases focus only on transactions, ClustrixDB also provides fast real-time analytics that are becoming increasing important to many businesses. ClustrixDB does this by bringing Massively Parallel Processing (MPP) used in data warehouses, to the primary database.

So, I decided to get a workload and try it out to see what kind of performance improvements one can get, if any. Since, joins and aggregates are the workhorses of real-time analytics processing, they are a good place to start.

Configuration

I built a simple dataset with two tables USERS (100K rows), USER_ADDRESSES (200K rows) and BIDS (10M rows) so this dataset has 2GB of data (mysqldump). For platform I used AWS and got ClustrixDB from AWS Marketplace. For comparison, I decided to use MySQL 5.6 since the exact same data and queries can be run on both databases. For both databases, the instance types are m1.xlarge.

MySQL does not scale beyond a single server and is usually deployed with master and two read slaves. Since ClustrixDB provides horizontal scale-out within one cluster, rather than master-slave (with multiple copies of data), the equivalent configuration is 3 servers. ClustrixDB horizontal scaling allows all nodes to participate in all query types. For measuring performance single MySQL is enough because performance for one query will be the same – whether we use the master or read slave.

For ClustrixDB, I also tried out 6 servers to see if analytics get faster as you add servers.

Here is the resulting table:

Results

We see that some queries get significantly faster, however one query showed no performance improvement. The count query on users is only counting 100K rows so it is likely not enough work. The count query on the bids table (counting 10M rows) shows speedup with 3 nodes, but with 6 nodes we don’t get as much improvement. This is still a very simple query. The queries with aggregates and joins get significantly faster (23x and 8.79x) on 3 nodes. These queries also get nearly twice as fast as you go from 3-node ClustrixDB to 6-node ClustrixDB, this is because of MPP in ClustrixDB.

Overall, we see that for more complex analytical queries ClustrixDB gets significant advantage. This means reports will get much faster with ClustrixDB. For some other queries, there is not enough work or being distributed does not offer that much advantage and here the performance is about the same. For real-time analytics requirements, ClustrixDB seems like a good solution.

Note: The product used in comparison is ClustrixDB. It is available to download for FREE.

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