SQL SERVER – Andy Defines Basic RDBMS: Isolation in Processes – Notes from the Field #038

[Note from Pinal]: This is a new episode of Notes from the Field series. Every time I give an introductory note, however, this time there is no need of intro note. This note is from Andy and as we all know he is amazing person when we have to understand the fundamentals. He has written this blog post with such an interesting way that you must read it to understand the very basic fundamental of Isolation.


When I think about SQL Server isolation in processes, it reminds me of eggs. Our family raises chickens for their eggs. Fresh eggs are very tasty, but there’s always the risk of a bad egg.

In the image above, I am preparing to scramble five eggs. I have cracked and opened five eggs (you can tell by the number of eggshells), but only four egg yolks are shown. “Why are there only four yolks, Andy?” I’m glad you asked.

My process for opening the eggs involves first dropping the contents of the egg into the mug, examining the contents, then – if satisfactory – adding them to the bowl for mixing. You don’t have to do this to make five scrambled eggs; you can crack the eggs right over the bowl.

But what happens when you open a bad egg? You risk ruining the entire batch of mostly good eggs. If you crack the eggs over the bowl, you have to pick five good eggs in a row to get a batch that’s ready to scramble. You may get lucky and it may only require five eggs. But the risk of a bad egg is ever present. You could get one good egg, followed by a bad egg. Now you have to throw both the good and bad egg out and begin again. The next time you may get three good eggs and then one bad egg. Now you’ve wasted three good eggs.

Isolating the eggs is a good practice. If I first empty the egg into a mug, I have constrained the process so that I only risk one egg at a time. In so doing, I have successfully mitigated risk to the least possible unit. I will only throw out the bad eggs without risking contamination of the good eggs in the bowl.

Isolation is generically defined as, “the process of separating somebody or something from others, or the fact of being alone and separated from others.”

In database terms, isolation is one of the four defining properties (i.e., atomic, consistent, isolated, durable—remember these by using the acronym ACID) of a Relational Database Management System (RDBMS). Similar to isolating bad eggs from the good, RDBMS isolation keeps individual database transactions from intermingling with each other during execution. It’s not that other transactions are bad, we just want to keep them separated so that data from one transaction doesn’t corrupt data from another transaction.

Are isolated transactions completely unaffected by each other? No, unlike the example of completely isolating a bad egg from the mix of good eggs, RDBMS isolation doesn’t prevent one transaction from influencing or impeding another transaction. An example of influence is resource contention; an example of impedance is locking. Isolation simply guarantees the results of the transaction will not be affected by concurrently executing transactions.

You can learn more about these properties from this awesome post by my friend, Pinal Dave: SQL Server – ACID (Atomicity, Consistency, Isolation, Durability).

If you want to get started with SSIS with the help of experts, read more over at Fix Your SQL Server.

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

About these ads

SQL Authority News – Webinar: SQL Server 2014 In-Memory OLTP and SafePeak In-Memory Caching

In the past couple of months many of us heard about the new SQL Server 2014 and its In-Memory technologies, such as In-Memory OLTP for high-transactional application and the ColumnStore INDEX’s for BI apps. This comes in addition to various in-memory technologies outside of the SQL Server world, such as Oracle, IBM or few NewSQL startups.

There are plenty of benefits for in-memory solutions:

  • Offloading expensive repeated database requests
  • Acceleration of Database speed and application performance
  • Scalability of applications
  • Reducing database resources and licensing costs

The level of interest in performance in-memory technologies is clearly on a rise, so we’ll try to provide you with interesting news, investigation summaries and interesting webcasts, especially when it comes to the SQL Server databases, applications and users.

Choosing the right solution for you can be based on many factors:

  • Application stage: a Greenfield new application, a Live production homegrown application or Commercial off-the-shelf application
  • Application characteristics: Operational vs. Analytical BI application
  • Effort level and time available to resolve a challenge: the level of effort, resources and costs you want to apply in setup, development, testing and deployment

Personally, I love webinars as they give an opportunity to learn very quickly one or two technologies; many times combining a view from 10,000 feet down to practical 100 foot level; learn various technologies aspects; either how to get started or help defining a realistic plan.

My friends at SafePeak, the software vendor of In-Memory Dynamic Caching for performance acceleration of SQL Server applications, are running next week (Wednesday 30th) a webinar under the title: SQL Server 2014 In-Memory OLTP and SafePeak In-Memory Caching.

The webinar plans to review the SQL Server 2014 In-Memory OLTP, how it works, various scenarios where it fits more and less, key benefits, key limitations (and how to overcome some of them), things to consider when planning to use it. Additionally the webinar will review SafePeak’s Automated In-Memory Caching for SQL Server applications, it’s internals or how it works, where it fits more and where it fits less, and run a technologies comparison of both SQL 2014 In-Memory OLTP and SafePeak Caching – and probably (as usually happens) show a live demo of both technologies to get a better grip of the content.

Should be interesting…

You can register the webinar here.

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

SQL SERVER – Finding Object Dependencies in SSMS – SQL in Sixty Seconds #071

While we are doing development, we create and drop objects. We build new things and we need to understand the relationships between database objects when we are doing various activities in SQL Server. Well, it is indeed very hard to know all, the relationship between various objects in SQL Server. However, with the help of SQL Server 2014 Management Studio, you can for sure do the same task very easily.

You have to go the object of which you want to see properties of and right click over it.

Now click over the option “View Dependencies”. It will bring up a screen listing various dependencies.

I hope this is clear enough. If not, I strongly suggest you watch following quick video where I explain this concept in extremely simple words.

Action Item

Here are the blog posts I have previously written on SSMS. You can read it over here:

You can subscribe to my YouTube Channel for frequent updates.

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

SQL SERVER – Free Entry to SQLPASS 2014 is Possible

The other day, I received an email from Dell and I was extremely delighted to read it. It contained details how one can go for FREE to SQLPASS.

What is SQLPASS?

The three-day event will be marked by a lot of learningsharing, and networking, which will help me increase both my knowledge and contacts. PASS Summit provides me a golden opportunity to build my network as well as to identify and meet potential customers or employees. If I am a consultant or vendor who is looking for better career opportunities, PASS Summit is the perfect platform to meet and show my skills to my new potential customers and employers. Further, breakfasts, lunches, and evening receptions, which are included with registration, are meant to provide more and more networking opportunities.

At PASS Summit, I gain not only new ideas, but also inspire myself from top professionals and experts. Learning new things about SQL Server, interacting with different kinds of professionals, and sharing issues and solutions will definitely improve my understanding and turn me into a better SQL Server professional who can leverage and optimize SQL Server to improve business.

I am once again going to SQLPASS. You can join me as well for FREE.

How to Get FREE Entry?

Dell is running a contest. The contest will begin July 17 and run until August 18. To enter, all you have to do is download a free 30-day trial of Spotlight on SQL Server Enterprise or LiteSpeed for SQL Server from this download page. The cost of SQLPASS regular entry is USD 1800 but you can get in for free if you take part in the contest.

About Products

Spotlight on SQL Server Enterprise delivers simplicity without compromise by providing unmatched monitoring, diagnosis and optimization of your SQL Server environments, ensuring peak performance around the clock. With intuitive overviews of enterprise health and performance, automated alerts and actions, an integrated tuning module and mobile device support, Spotlight makes it easier than ever to obtain the data you need to ensure the health of your SQL Server databases.

-  Ensure high-speed, storage-efficient backup and restore for SQL Server databases with up to 85 percent savings in backup size, storage costs, and restore times. LiteSpeed for SQL Server makes it possible, with minimal effort and risk. Delivering a wide variety of recovery options, LiteSpeed ensures the right SQL Server data is restored and available as quickly as possible.

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

SQL SERVER – SSMS: Activity – All Blocking Transactions

Working out of India has its own challenges and I enjoy here despite these challenges thrown at me. One of the biggest advantage I have working with Pluralsight is, I can still get my job done by working-from-home occasionally. And this is one of the perks I wish most of the companies give their employees. You might be thinking why I am doing this, well the obvious answer to this question relies on the fact how the previous day went. If it rained heavily, which is does in Bengaluru in July, then the chances are that roads would have a build-up of traffic the next day morning. Taking traffic away from your life is never so easy, but with technology improvements like Maps on the phone, I still manage to get an alternate route to reach my destination. This is what makes life interesting and the exploration into new places always fun.

I just wish SQL Server had some way of achieving the same. Blocking and Locking are fundamental to keeping databases in sync and consistent. This blog is all about Blocking Transactions report from the instance level.

To access the report, get to Server node -> Reports -> Standard Reports -> Activity – All Blocked Transactions.

From this node, if there are no apparent blocking happening in the system at the point this report was run, we will be presented with a “Blank” output as shown below.

The ideal situation for us to be in this state, even for a transitional system, but this will never be the case in reality. For a highly transactional systems which try to modify / insert data in same table, SQL Server will respect the order in which the request came and will not allow incompatible locks to exist at the same time. So this behaviour creates a queue automatically and this is what we call as Blocking.

This brings us to the next output, where we are having multiple transactions running. To show some data in report from my non-production-workload system, I have simulated a blocking scenario using two statements. In such a scenario you can see there are two regions to look at: the Session ID of 52, 53 and 54. From the hierarchy, we know that 52 is blocking both 53 and 54. We can also know there are 2 “#Directly Blocked Transactions” in the system currently from the top row for SPID 52. If there are additional transactions trying to insert or delete, then this will show the complete chain of tractions currently blocked.

We also get to see the type of statement that is waiting in this blocking scenario. In the diagram below we see the two statements involved are – INSERT and DELETE.

Various DMVs which have been used to get this information are sys.dm_tran_locks, sys.dm_tran_active_transactions, sys.dm_tran_session_transactions, sys.dm_tran_database_transactions and sys.dm_exec_requests. Along with above, report also uses DMF sys.dm_exec_sql_text to convert the SQL handle to more meaningful text.
If that was not enough then we can also head to the Activity Monitor and expand the Processes tab to get similar information. It is evident that the head of blocking is 52 whereas 53 and 54 are waiting on 52. It is completely up to us to decide what we need to do. We can Kill process 52 and the other transactions will go through.

As a small note, the Task States can give us vital information of what is happening in the system. Some of the states are worth mentioning:

Sleeping This shows the SPID is waiting for a command or nothing is currently executing.
Running SPID is currently running.
Suspended SPID is waiting for locks or a latch.
Rollback Connection is in rollback state of a transaction.

You can use the state information to take an informed decision of killing a process if required.

At this moment, yet another blog post that is worth a mention is Blocked Process Threshold post. This option makes sure there is a profiler event raised when a request is blocked beyond a predefined period of time. So do take a look at that too if you are interested in that behaviour.

The reports series is catching up and the learnings are multi-fold for me personally. Subsequent posts I will get into the other reports and give you my learnings.

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

SQL SERVER – Activity Reports – Dormant Sessions

With schools starting for my daughter, I rarely get involved in her daily routine activity. But there is one thing that I don’t try to miss – the parents-teachers meet. Sometimes it is not about the report card on how my daughter faired against the rest in the class but it is more than that. I am curious to understand how she behaves in the class, how she makes friends in class, how her extra-curricular activities are, is she disciplined overall etc. Those are key attributes and traits I am looking at getting as feedback from the teachers in that hour of interactions.

In the same lines, there are tons of other parameters one needs to be aware off with working with SQL Server. A lot of times DBA’s when they are doing maintenance or monitoring of servers, they need help around who is currently accessing the server, what are the inactive sessions, what are the connections with the most resources, all the active sessions on the server and more. This blog will answer these questions. Here are the reports we would talk about:

  1. Activity – All Cursors
  2. Activity – Top Cursors
  3. Activity – All Sessions
  4. Activity – Top Sessions
  5. Activity – Dormant Sessions
  6. Activity – Top Connections

Activity – All Cursors

This report shows information about the cursors used in SQL Server. They are looping construct in T-SQL world. I have probably heard many times, from different sources, as a best practice; avoid using TSQL cursors. In my opinion, there could be situation where cursors might out-perform as compared to other looping constructs. For example, a cursor would be a good candidate for row-by-row processing that can’t be performed by set based operations. We get flexibility via cursor as it provides a subset of data and that allows manipulation of the data in different ways. Having said that, do perform your own performance tests before using the same – these recommendations have to be used with a pinch of salt rather than as written on stone.

The heart of this report is DMV sys.dm_exec_cursors which has a lot of information available about the cursors that are open in various databases. The reports also uses below DMVs.

sys.dm_exec_sessions To get login name
sys.dm_exec_sql_text To get text of the statement via sql_handle

For seeing the sample data into the report, we can run below query

DECLARE cur CURSOR
FOR SELECT
name FROM sys.objects
DECLARE @temp SYSNAME
OPEN
cur
FETCH NEXT FROM cur INTO @temp
WHILE @@fetch_status >= 0
BEGIN
FETCH
NEXT FROM cur INTO @temp
WAITFOR delay '00:00:01'
END
CLOSE
cur
DEALLOCATE cur

All the values shown are explained in documentation of sys.dm_exec_cursors.

Activity – Top Cursors

This report is same as earlier report and only difference is that we can see them categorized as below.

  1. Top 10 Oldest Cursors – This shows the oldest cursor the on SQL.  (Order by creation_time)
  2. Top 10 Dormant Cursors – shows Cursor sitting idle since last query (open or fetch) (Order by worker_time)
  3. Top 10 IO Intensive Cursors – Shows cursors that are consuming the most IO resources. (Order by reads + writes)
  4. Top 10 CPU Intensive Cursors – Shows cursors that are consuming the most CPU resources. (Order by dormant_duration)

All four sections run exactly same query with different order by clause (which I mentioned in definition) by DMV sys.dm_exec_cursors.

Activity – All Sessions

As the name says – this report shows the details of all sessions, connections, requests and the statements currently active in the server.

This report provides details on all active user sessions on the Instance organized by Login. Since I have started two different login “SlowIO” and “sa”, we are seeing the report shows two groups (highlighted). We can drill down to each group till statement level.  Under the hood it uses sys.dm_exec_sessions,

sys.dm_exec_connections and sys.dm_exec_requests DMVs.

Activity – Top Sessions

SELECT TOP 10 s.session_id,
s.login_time,
s.HOST_NAME,
s.program_name,
s.cpu_time             AS cpu_time,
s.memory_usage * 8     AS memory_usage,
s.total_scheduled_time AS total_scheduled_time,
s.total_elapsed_time   AS total_elapsed_time,
s.last_request_end_time,
s.reads,
s.writes,
COUNT(c.connection_id) AS conn_count
FROM   sys.dm_exec_sessions s
LEFT OUTER JOIN sys.dm_exec_connections c
ON ( s.session_id = c.session_id)
LEFT OUTER JOIN sys.dm_exec_requests r
ON ( r.session_id = c.session_id)
WHERE  ( s.is_user_process = 1)
GROUP  BY s.session_id,
s.login_time,
s.HOST_NAME,
s.cpu_time,
s.memory_usage,
s.total_scheduled_time,
s.total_elapsed_time,
s.last_request_end_time,
s.reads,
s.writes,
s.program_name

Here are the various order by clauses added in each section. You can do it yourself as well.

  1. Top Oldest Sessions (order by s.login_time asc)
  2. Top CPU Consuming Sessions (order by s.cpu_time desc)
  3. Top Memory Consuming Sessions (order by s.memory_usage desc)
  4. Top Sessions By # Reads (order by s.reads  desc)
  5. Top Sessions By # Writes (order by s.writes desc)

Activity – Dormant Sessions

This is an interesting report and shows dormant sessions in SQL Server. Dormant session is a session which has connected earlier, ran some query and sitting idle. This report provides details on Sessions that have been inactive for more than an hour. Behind the scene, the report uses sys.dm_exec_sessions and puts filter on datediff(mi, last_request_end_time, @d1) >= 60 to get dormant sessions.

As shown above, there are three sections in the report. In the first section (1), we can see number of All Sessions, number of Dormant Sessions which are there from more than 1 hour and number of users with Dormant Sessions. This might be different from number of sessions, because single login might have more than one session open at a point in time. The second section (2) shows the Top 10 Dormant Sessions. All of the columns are self-explanatory. Third section (3) shows top 10 dormant sessions by user name. This would be useful in development servers where we use user name to find who is connected.

Activity – Top Connections

This is last Activity report in the list. Earlier reports are based on sessions and this report is based on connections. Since this report is similar, I would not explain much.

Here is the base query used by report

SELECT TOP 10 ( Row_number()
OVER(
ORDER BY c.connect_time) )%2             AS l1,
CONVERT(CHAR(100), c.connection_id)            AS connection_id,
c.session_id,
c.connect_time,
c.num_reads,
c.num_writes,
c.last_read,
c.last_write,
c.client_net_address,
c.client_tcp_port,
(
SELECT COUNT(*)
FROM   sys.dm_exec_requests r
WHERE  ( r.connection_id = c.connection_id)) AS request_count,
s.login_time,
s.HOST_NAME,
s.program_name,
s.login_name,
s.is_user_process
FROM   sys.dm_exec_connections c
LEFT OUTER JOIN sys.dm_exec_sessions s
ON ( s.session_id = c.session_id)

There are three sections. They show similar information but with different order by clauses.

  • 10 Oldest Connections – order by c.connect_time
  • Top Ten Connections By # Reads – order by c.num_reads desc
  • Top Ten Connections By # Writes – order by c.num_writes desc

Well, that was quite a few reports in one go today. I am sure you will play with them and do let me know if you find anything interesting or used these reports in any interesting ways.

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

Developer’s Life – Every Developer is a Wonder Woman

We have focused a lot lately on this “superhero series.”  I love fantasy books and movies, and I feel like there is a lot to be learned from them.  As I am writing this series, though, I have noticed that every super hero I write about is a man.  So today, I would like to talk about the major female super hero – Wonder Woman.

Developers are mostly men.  It is a male-dominated field.  This is changing slowly, but I don’t think that means we should over-look Wonder Woman.  She had a lot of great powers that remind me of developers, and that we can all learn from.

So how are developers like Wonder Woman?

 

Well, read on my list of reasons.

Developer’s Read Ahead

Wonder Woman had telepathy.  She could hear what other people were thinking.  How many of us could use that power in our daily lives?  Developers often work with co-workers or clients who are not tech-savvy, and have a hard time expressing their needs and desires.  We might not be able to develop telepathy, but we can certainly try to see things from their point of view and try to understand their needs.

Truth

Wonder Woman’s main super-tool is the Lasso of Truth.  Once caught in it, super villains are forced to tell the truth.  It would be nice to have the Lasso of Truth in real life, but we also must consider ourselves bound by it, too.  Lying to get out of a difficult job is never good.  Lasso yourself and promise to be honest, always.

Super Speed

Wonder Woman had super speed.  Developers can also impress with their ability to finish tasks in an unbelievable amount of time.  It is never good to sacrifice quality for speed, but this is certainly a skill that reminds me of many developers I know!

Eagle Eyes

Wonder Woman had eagle eyes.  She could zoom in on details from far away and catch evil-doers.  Developers may not have binocular-like vision, but they often catch mistakes that untrained eyes would never see.

Developer Heals

Wonder Woman had amazing healing powers.  She was not a frail woman – she could recover from injury at lightning speed.  Developers do not often have physical jobs, so I am thinking about their abilities to recover after a particularly difficult project, and be so excited about solving the problem they are full of energy for the next one.

Wonder Woman was, well, a woman.  It is tough being the only woman/minority/tech guru in the room.  But no matter your reason for being the “odd man (or woman) out,” remember that your unique perspective brings something very important to your company.  Never underestimate yourself, and don’t let other’s forget your worth, either.

Wonder Woman has not had the same kind of popularity as many other super heroes, which I think is a shame because we can still learn a lot from her.  What are some of the powers you wish you had from Wonder Woman, or any other super hero?

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