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.


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)

About these ads

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.


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:


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)

SQL SERVER – Monitor Database via a Heatmap, Alarms List and Realtime Diagnostics for FREE

Note: This review is based on the performance monitoring and tuning free product Spotlight on SQL Server Freemium.

If you think of a DBA’s life, it has one very simple goal – their server should never go down and all the queries should always perform excellently. However, just like any other life goals o it is not possible to achieve that easily. I have often seen many DBA’s continuously watching their monitor to make sure that their servers are running fine. Another habit of most  DBAs is to continuously check their mobile phone for alerts. Nowadays we get so many alerts it is  getting harder to keep watch on the most important alerts for the health of our server. The habit of looking at the phone and computer monitor is so rooted into a DBA’s mind that they keep on looking at their phone at home to catch a suspicious alert.

Earlier this year, when I attended SQL PASS 2013, I  stopped by the Dell Software booth to see what they have  new in the SQL Server world. I  noticed Spotlight on SQL Server Freemium running on their monitor. When I inquired about the price, I was happy, in fact I was very happy as it was totally FREE! After returning home, I got much too busy with my day job, but  I recently I got some time and I downloaded the Spotlight on SQL Server Freemium FREE tool. The installation was pretty straight forward and easy. It took me less than 10 seconds for me to install the tool, just make sure that your SSMS is closed when you install  Spotlight on SQL Server Freemium, otherwise it will show you a warning to turn off SSMS.

Once I installed the plug-in, it was very easy to use it as it becomes an integral part of the SQL Server Management Studio, the interface is a very user friendly.

There are three distinct options in the Spotlight on SQL Server Freemium tool bar menu. Once you click on Monitoring it will give  three options. 1) Heatmap 2) Alarms 3) Connections. Let’s look at  them very quickly over here.

1) Heatmap

If our server is down, we want to know right away, but if everything is smooth we do not want to keep on getting reminders about that. For that reason Heatmap is a very essential part of  Spotlight on SQL Server Freemium. It gives an ‘at-a-glance’ picture of the state of all the servers DBAs have in their environment. Colors communicate all the information about what is going on with your server. The heatmap takes this a step further by displaying each server as a tile and then aggregating all of the statuses of a server and assigning a size to that tile. It also displays alarms for the connection when touched.

2) Alarms

Alarms  is just an alternate way to view Heatmaps. They display alarms on each server ordered by severity. You can configure and sort alarms the way you prefer. Once an alarm rings an experienced user can do either of  two actions: a) Acknowledge the alarm and solve the issue  b) Snoozing it to be reminded in the future.

3) Connections

This particular area displays various connections to diagnose a server as well as the server which you are monitoring. You can make various adjustments in your server connection in this section.

System Health Check

One of the biggest features of Spotlight on SQL Server Freemium is health check and providing a prioritized list of the key health system issues. Users can pinpoint various issues with the help of this list and resolve SQL Server issues. There are major five categories this tool checks:  Security, Disaster Recovery, Index Optimization, Memory and SQL Best Practice.

In future blog posts we will cover each of these topics in depth. Meanwhile, I strongly suggest you download Spotlight on SQL Server Freemium and makes sure your servers are healthy. Additionally, visit www.SpotlightEssentials.com, the one-stop shop for all things Spotlight.

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