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)

About these ads

SQL SERVER – Download SQL Server 2014 Developer Edition at USD 59.59

Since the release of the SQL Server 2014 earlier this year, lots of people have been asking if people can download the full version of SQL Server 2014. SQL Server 2014 full version is not available for FREE. You have to either get standard version or enterprise version. You can download SQL Server 2014 trial version and use it till it expire.

If you want SQL Server 2014 for development purpose or for test system, you do not have to buy standard or enterprise version. You can just download it the development version. The development version costs USD 59.95 and it is available on DVD and immediate download.

Here is the link to the product.

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

SQL Authority News – Microsoft Whitepaper – SQL Server 2014 and Windows Azure Blob Storage Service: Better Together

Microsoft has released SQL Server 2014 earlier this month and now have released very long but interesting white paper on Windows Azure Blog Storage Services. SQL Server Data Files in Windows Azure feature is available in all editions of SQL Server 2014. This feature is enabled by default and at free of cost. This feature makes SQL Server 2014 hybrid cloud database as it provides native support for database files stored as Windows Azure Blobs. If you notice this is a big leap forward where data stored locally as well as on a cloud are accessed by SQL Server 2014 at the same time. You can also host SQL Server Instance in Azure cloud and it can also use the Windows Azure Blobs just like on-premises SQL Server.

Let me give you another example, if you have a situation that you have stored your database files in the cloud as well as backup on Windows Azure Cloud. Now if you want to restore your backup you do not have to bring that back to on-premises before restoring it. You can just restore your data directly from Windows Azure Storage to your SQL Server 2014 instance in a Virtual Machine. It will remove the necessity to do lots of data movement between cloud and on-premises servers.

This white paper discusses exactly the same feature very much in detailed. This white paper has over 116 pages and the size of the word file is 2.5 MB. However, after a long time I have found a Whitepaper which explains concepts from the beginning and walks users step by step with the examples. The appendix file to this Whitepaper contains all the necessary code as well as very interesting reference material. I think I am going to spend my entire weekend reading and learning from this Whitepaper.

Image included in this blog post is courtesy to the same white paper and it explains the story of entire white paper in just one image. Brilliant.

Download the white paper SQL Server 2014 and Windows Azure Blob Storage Service: Better Together.

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

Developers – Drive by Daniel Pink – Book Review

I have been an avid reader of books. I read pretty much one or two books every week. I believe reading helps me a lot in my day job as well as well writing blog post. I am an evangelist and my primary responsibility is to spread the love for the technology. When I read more, I learn more and that effectively leads me to communicate better with my audience. Here is one of the recent book which I read and I loved it. This book is about what motivates us and what is the best job for us. I have read this book a second time and I think finally I have digested the meaning of this book. I love every bit of it and if you do not have it, you should get it.

I am going to give 10 Drive physical books to 10 people who register for my newsletter over here.

If you want to own the book, you can get it from Amazon |Flipkart.

Here are essential few quotes from the book.

The joy of the task was its own reward.

When money is used as an external reward for some activity, the subjects lose intrinsic interest for the activity.

Rewards can deliver a short-term boost – just as a  jolt of caffeine can keep you cranking for a few more hours. But the effects wears off – and, worse, can reduce a person’s longer term motivation to continue the project.

Enjoyment-based intrinsic motivation, namely how creative a person feels when working on the project, is the strongest and most pervasive driver.

Intrinsic motivation is of great importance for all economic activities. It is inconceivable that people are motivated solely or even mainly by external incentives.

Intrinsic motivation is conductive to creativity; controlling extrinsic motivation is detrimental to creativity.

It is those who are least motivated to pursue extrinsic rewards who eventually receive them.

Greatness and nearsightedness are incompatible. Meaningful achievement depends on lifting one’s sights and pushing toward the horizon.

Any extrinsic reward should be unexpected and offered only after the task is complete.

Type Intrisic behavior emerges when people have autonomy over the four T’s: their task, their time, their technique, and their team.

If you want to work with more type Intresic’s the strategy is to become one yourself. Automony, it turns out, can be contagious.

There is complexity, autonomy, and a relationship between effort and reward in doing creative work, and that’s worth more to most of us than money.

Carrots & sticks are so last century. Drive says for 21st century work, we need to upgrade to autonomy, mastery & purpose.

This new approach has three essential elements: (1) Autonomy—the desire to direct our own lives; (2) Mastery—the urge to get better and better at something that matters; and (3) Purpose—the yearning to do what we do in the service of something larger than ourselves.

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

SQL SERVER – Idera SQL XEvent Profiler – a Free Tool for XEvent Monitoring

Download Extended Event Profile FREE

We often resist the change, we do not like change and we keep on using the same old technology which we have been using it to resolve the new challenges which we face in our daily life. The Extended Event is such a technology as well. It was added to SQL Server in SQL 2008 and expanded upon greatly in SQL 2012 is a powerful mechanism for monitoring activity in SQL Server. It has huge implications for tools like SQL Diagnostic Manager, which switched over to XEvent technology for wait monitoring, and for users of all stripes who need to see what is happening on their servers.

Though the product was well thought of it was yet not quite ready for average users due to many reasons. It offered a reduced set of events and could only be manipulated through TSQL. In SQL 2012 the number of events went up dramatically and Microsoft released an XEvent GUI as part of Management Studio. However, lots of users think that the XEvent tool is a bit overkill for a lot of purposes. Therefore, a lot of users continue to use SQL Server Profiler, which uses trace technology. It’s easy to see why – it’s familiar, it’s easy to use, and it takes only a few clicks to get a diagnostic session started.

Team Idera has put together a new free tool called SQL XE Event Profiler, which offers the simplicity of Profiler but uses the powerful and lightweight XEvent technology on the back end. The idea was to provide the simple 2 click behavior that users were looking for, along with the familiar SQL Profiler templates that users are already accustomed to using. The application has the familiar start, stop, and pause buttons, the ability to turn auto-scroll on and off, and the ability to clear the grid. While we were at it, we added some nice grouping functionality to the event grid, along with text searching and export.

I am downloading this tool this weekend and going to play with this tool and see how I can use it on my various servers. I encourage all of you to do the same.  Here is the link to the original blog.

Download Extended Event Profile FREE

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

SQL SERVER – Tools for Proactive DBAs – Central Management Server – Notes from the Field #024

[Note from Pinal]: This is a new episode of Notes from the Fields series. AlwaysOn is a very complex subject and not everyone knows many things about this. The matter of the fact is there is very little information available on this subject online and not everyone knows everything about this. This is why when a very common question related to AlwaysOn comes, people get confused.

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very common issue DBAs and Developer faces in their career and is related to AlwaysOn Availability Group. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.

After completing several AlwaysOn Availability Group implementations there are two questions that come up frequently. When did my availability group failover? Where is my read-write replica? The answer to the first one is provided here [http://johnsterrett.com/2014/03/18/where-is-my-availability-group/ ]. Today, were going to look at the answer to the second question.

Where is my read-write replica?

The following script, when executed on an availability group replica returns the availability group name, current role state, and database name. This will let you know if this instance is hosting the PRIMARY “read/write” replica.

AvailabilityGroup = ag.name,
AvailabilityGroupRole = ars.role_desc,
FROM sys.databases db
INNER JOIN sys.availability_databases_cluster adc ON db.group_database_id = adc.group_database_id
INNER JOIN sys.availability_groups ag ON adc.group_id = ag.group_id
INNER JOIN sys.dm_hadr_availability_replica_states ars ON ag.group_id = ars.group_id
AND db.replica_id = ars.replica_id
NULL AS AvailabilityGroup, NULL AS AvailabilityGroupRole, name
FROM sys.databases

How do I check all replicas?

Now you know how to check if an instance of SQL Server participating in an AlwaysOn Availability Group is the “Read/Write” PRIMARY role. Next, we can utilize Central Management Server to run the same script across your other instances participating as replicas in your AlwaysOn Availability Group configuration. If you have never used Central Management Server this step-by-step guide can get you going in 10 minutes.

For this week, I have an windows failover cluster with two nodes “SQL2012DR” and “SQL2012PROD1 aka localhost,1433”). Each node hosts its own default instance of SQL Server. There are two separate AlwaysOn Availability Groups. Using Central Management Server, I can rerun the query above and quickly see where my read/write replica is.

Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

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

SQL SERVER – Life of a SQL Query – Query States

This is the guest blog post written by James Davies, Sales Engineer, Confio Software, a SolarWinds company. It is written based on the Database Performance Analyzer. I encourage you to download it for free from here.

From the moment a query request is made until the query response is delivered, what really happens to your query in SQL Server? Let’s find out, and talk about why that matters to you.

Put simply, queries in SQL Server can exist in one of three states while executing inside the database engine. These states are defined as:

  • RUNNING–meaning that the query is actively running on the CPU
  • RUNNABLE–meaning that the query is ready to run but CPU resources are not yet available. It is waiting in the Runnable Queue for a CPU to open up
  • SUSPENDED–meaning that the query is waiting for a third party resource to become available (for example,, disk I/O, blocking and so on)

In an overly simplified way, it’s a bit like the checkout line at the grocery store, in which each shopper has just one item. The cashier is the CPU and the item the checker is actively scanning is like a query that is running. The shoppers waiting in line, each with their own item, are like runnable queries, just waiting to be scanned and checked out. Let’s say that when the checker scans your item, the price doesn’t come up, and a price check is called. The checker asks you to step aside, and wait for the price check to complete. When the price comes back, the checker asks you to step back in line, all the way at the end of the line. That’s like a suspended query (and that’s not something you want to happen to your query!). If there are four checkout lines running, then that would be a bit like having four CPU cores, each with their own set of queries in various states of running, runnable and suspended.

So then what is a wait type? In SQL Server wait types are simply a more detailed explanation of the suspension state. As DBAs, we don’t like to see queries in the suspended state, and want to find out just what is causing the query not to process.

When you look at wait statistics, you want to see queries spending the majority of their time in a running or runnable state. You can also use wait statistics to discover why a query is in a suspended state. If you can answer that question, you can fix the problem so that the query is mostly in a non-suspended state. And this is why understanding your query wait statistics is key to maximizing query performance.

You can look at wait statistics using SQL Server Management Studio (SSMS) or third-party tools such as SolarWinds Database Performance Analyzer (DPA). DPA provides simple, fast and visual identification of query wait statistics correlated with system resources, including historical and trend information, so that you can easily pinpoint root cause of query performance issues.

Database Performance Analyzer (formerly Confio Ignite) Shows Top Waits and Which Resources Are Being Waited On

Whichever tools you use, understanding query states and wait statistics is the foundation to a good proactive database performance practice.

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