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)

About these ads

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)

MySQL – How to Format Date in MySQL with DATE_FORMAT()

MySQL supports formatting the datetime values into a different formats using DATE_FORMAT() function. This function accepts date/datetime values as a first parameter and returns into a specific format defined as a second parameter.

Let us explore this with the following examples which are self-explanatory

Define a DATETIME variable
SET @date:='2014-06-16 14:12:49';

-- Display datetime values in YYYY-mm-dd format
SELECT date_format(@date,'%Y-%m-%d') AS formatted_date;

The result is 2014-06-16

-- Display datetime values as Long Date format
SELECT date_format(@date,'%W, %M %d,%Y') AS formatted_date;

The result is Monday, June 16,2014

-- Display datetime values as Full date format
SELECT date_format(@date,'%W, %M %d,%Y %T') AS formatted_date;

The result is Monday, June 16,2014 14:12:49

-- Display datetime values in HH:MM:SS format
SELECT date_format(@date,'%T') AS formatted_date;

The result is 14:12:49

-- Display datetime values in Month Year format
SELECT date_format(@date,'%M %Y')  AS formatted_date;

The result is June 2014

-- Display datetime values in mm-dd-yyyy format
SELECT date_format(@date,'%m-%d-%Y')  AS formatted_date;

The result is 06-16-2014

-- Display datetime values in dd-mm-yyyy format
SELECT date_format(@date,'%M %Y')  AS formatted_date;

The result is 16-06-2014

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

SQL SERVER – Free intellisense add-in for SSMS

This article shows how to use ApexSQL Complete, a free SQL Server intellisense add-in. You can download ApexSQL Complete, and play along through the article.

ApexSQL Complete is a free SQL Server Management Studio and Visual Studio add-in that speeds up SQL coding. In this article, we will explain ApexSQL Complete through its features. To start using ApexSQL Complete, enable it from the ApexSQL menu under the main menu in SSMS:

Hint list – complete your SQL code

This is a key feature of ApexSQL Complete. It helps you find the wanted object or a SQL keyword, and complete the SQL statement faster instead of typing the entire object name or keyword. After you start typing, for example “SE”, SQL intellisense will appear with all keywords and database objects that contains “SE”, listed by importance:

You can uncheck the box for the appropriate objects, and prevent them to appear in the SQL intellisense from the add-in options, under the Hints tab. This will decrease the number of hints in the SQL intellisense list, and speed up the coding process:

Another useful property of the SQL intellisense in ApexSQL Complete is a multiple sections, which allow you to navigate over the database schema, to a table, and select the specific columns, checking the appropriate boxes. You can also select the columns and hit the Enter key to insert them:

If a SQL script you are using is large, at some point you will need to look at SQL code before you continue. ApexSQL Complete offers you to accomplish this without a break. Press and hold the CTRL key, and the SQL intellisense will become transparent, so you can see through, and review SQL code. Releasing the CTRL key will get you back to a previous state, and you can continue typing:

Tab navigation – monitors all opened SSMS tabs

This feature allows you to track opened and recently closed tabs, or to restore previously saved session after crashing. These operations can be managed from the add-in options, under the Tab navigation tab. Here you can set the period for keeping the tabs saved, and set the interval for auto-save.

This could be useful if, for any reason SSMS crashes. The Tab navigation feature saves your time, and gets you back to a point before crashing.

The Tab navigation pane consists of two parts, Opened tabs and Recently closed tabs. In the Opened tabs section, all opened tabs from one session will be shown, and you can easily switch from one to another. You can search the content of the opened and closed queries, and open the query that contains searched results. Double-clicking the query from a list of the Recently closed tabs tab opens it in a new query window. For the opened and closed tabs, if you select a query from the list, a complete content will be shown in the preview section to the right:

At any point, you can save your workspace or opened tabs, and restore to the saved state later, if SSMS crashes.

Code structure – view and find SQL code blocks

This feature provides a tree-like form of SQL code presented in a separate SSMS window on the left side of the query window. When you enable the Code structure feature from the add-in options, it allows you to see all the important parts of SQL code used in the query. SQL code blocks from the Code structure window can be expanded so you can navigate to a specific part of the block. Selecting any item from the Code structure window highlights a SQL code block in the query window:

This way you can move through SQL code in blocks and find the part you are looking for instead of scrolling down the query.

Executed queries – track executed queries

Using this feature allows you to track all executed queries in a defined period. To enable the feature, select the Log execute queries option under the Executed queries tab in the add-in options. You can set the folder for storing the executed queries. The queries are saved as an .xml files. You can also define the maximum number of lines in SQL code, which will be stored. The Default period option allows you to show the queries executed in a defined period.

When activated, the Executed queries form will show all the queries executed in a defined time range. If you select a query from the list, its content appears in the preview section. You can search through the queries, executed in a defined period. Double-clicking any of the executed queries from the list opens it in a new query window in SSMS, so you can additionally change SQL code.

Snippets library – insert often SQL statements

With this feature you can insert often used SQL statements, even a whole procedure, or blocks of SQL code. You can create a snippet from the ApexSQL Complete options, or from the SSMS query window:

1) To create a snippet from the ApexSQL Complete options, navigate to the add-in options, and click Add new snippet option, under the Snippets tab:

Here you can edit any of the predefined snippets from the library, export/import them to use it on another machine.

2) To create a snippet from the SSMS query window, type SQL code you want to be defined as a snippet, select it, and right click on it. From the context menu, choose the New snippet option:

This will open the Create a new snippet window, with the selected code already inserted in the Code section. You just need to define a name for the new snippet, and optionally a description:

To use already created snippet from the Snippet library, click the Insert snippet option from the context menu in the SSMS query window, and double click on a snippet from the list to use it in the query.

Navigate to object – locate an object in the Object Explorer

This allows you to locate the selected object in the Object Explorer pane. In the query window, select the object you want to locate, and right-click on it. From the context menu, choose the Navigate to object option, and the selected object will be located and highlighted in the Object Explorer pane to the left.

Test mode – execute queries without impact to the database

The Test mode feature allows you to execute a query in a test environment, without impact and consequences to the database. To use the Test mode feature, select the Test mode option from the toolbar, and highlight SQL code in the query window you want to execute.

The Test mode feature will add BEGIN TRANSACTION and the ROLLBACK TRANSACTION statements. After the execution, it rolls back the transactions at the beginning:

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

SQL SERVER – Beginning Contained Databases – Notes from the Field #037

[Note from Pinal]: This is a new episode of Notes from the Fields series. Login and Users are very confused concept. Just yesterday I wrote about a difference between Login and User. In Latest version of SQL Server now we can also have a User without Login. This concept is not easy to understand and needs a clear example.

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 related to Login and Users. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.


One of the downfalls with database mirroring is synchronizing database users with logins. If you were using SQL Server accounts instead of windows domain accounts it could be a tougher challenge to ensure that the unique identifiers (SID) match up. In SQL Server 2012 and up we can leverage contained databases to mitigate this problem with Availability Groups. Contained database users are stored inside the database instead of leveraging the instance logins. This allows the database itself to manage authentication and authorization for the login for the database.  Therefore, you don’t need to fixed orphan users when you failover.

Here is how you can enable and leverage Contained Databases.

Step One: Enable Contained Database on the instances that will host the database. In an Availability Group this would be all the replicas for the Availability Group.

This can be configured in SSMS or via T-SQL as shown below.

EXEC sp_CONFIGURE 'show advanced options',1
GO
RECONFIGURE
GO
EXEC sp_CONFIGURE 'contained database authentication',1
GO
RECONFIGURE
GO

Step Two: Enable Contained Database on the primary replica. This is also known as the read/write replica.

This can be done via SSMS or T-SQL

USE [master]
GO
ALTER DATABASE [AdventureWorks2012] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO

Step Three: Create a Contained User
In order to have a contained user were going to have to create a new user inside the database and create it without a login. This will make the user a contained user. You will also need to add the needed security for your account. For the purpose of this weekly tip we will skip this part. Creating the contain login also known as SQL user without login can be done with SSMS or T-SQL as shown below.

USE [AdventureWorks2012]
GO
CREATE USER [MyContainedUser] WITH PASSWORD=N'!LPPeople!', DEFAULT_SCHEMA=[dbo]
GO

Step Four: Test connectivity.

Finally, we can test connectivity. This will be done utilizing SSMS as shown below.  Make sure you change the default database to the database where the login is contained as shown below.


Now, make sure you select the Connection Properties tab and select the database where you created the contained login. In our example, this will be AdventureWorks2012.

 

When connection is successful with a contained database user you should see the login and the database right next to the instance in object explorer as shown below.

 

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 – Difference Between Login and User – SQL in Sixty Seconds #070

“What is the difference between Login and User in SQL Server?”

This is a very common question I often receive. Yesterday when I was browsing Facebook, I once again noticed this question once again asked in SQLBangalore group. My very good friends – Vinod Kumar and Balmukund Lakhani had already answered the question there. However, I every time, I read this question, I realize that not everyone knows the basics of these two concepts. If I have to explain the difference between them, it may take a long time, but I will try to explain it at a very basic level.

Basic Explanation:

SQL Server Login is for Authentication and SQL Server User is for Authorization. Authentication can decide if we have permissions to access the server or not and Authorization decides what are different operations we can do in a database. Login are created at the SQL Server instance level and User is created at SQL Server database level. We can have multiple user from different database connected to a single login to a server.

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 the subject of security. 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 – SSMS: Memory Consumption Report

The next in line in this series of reports is the “Memory Consumption” Report from SQL Server Management Studio. This is a goldmine of a report in my humble opinion and lesser respected. When I used to be consulted or land into performance tuning exercises for customers in the past, there is one question that gets repeated and echoed every now and then – “My SQL Server is eating away my RAM and it is not releasing it back even in non-peak hours”. I always am smiling when this question comes up. SQL Server or for that matter any database system is highly memory oriented processes. If they had taken for some reason, then they are not going to release it because they assume at a later point in time they will require it again. So instead of depending on the OS to allocate, they go with the assumption of grabbing and never releasing even when it is not required in the interim.

Now that brings to the point the fact what is my SQL Server using this memory for? Well, if you search the internet you will be amazed by the plethora of scripts and it is overwhelming how people have killed this subject to death. But this hidden gem inside SQL Server Management Studio is never talked about. So in this blog post, let me take a tour of what this report contains and how one should read the sections.

This report can be launched by going to Server Node in SQL Server Management Studio (SSMS) right click > Reports > Standard Reports > Memory Consumption.

The report has multiple sections which we would discuss one by one.

Memory Related Counters

These three values can give us a rough indication of memory pressure on SQL Server Instance. These three values are retrieved from SQL Server Memory counters.

SELECT  OBJECT_NAME
,counter_name
,CONVERT(VARCHAR(10),cntr_value) AS cntr_value
FROM sys.dm_os_performance_counters
WHERE ((OBJECT_NAME LIKE '%Manager%')
AND(
counter_name = 'Memory Grants Pending'
OR counter_name='Memory Grants Outstanding'
OR counter_name = 'Page life expectancy'))

As per perfmon counters help, “Memory Grants Outstanding” shows counter shows the current number of processes that have successfully acquired a workspace memory grantgrant, whereas “Memory Grants Pending” counter shows the current number of processes waiting for a workspace memory grant. Page life expectancy is defined as “Number of seconds a page will stay in the buffer pool without references

Top Memory Consuming Components

This section of the report shows various memory consumers (called clerks) in a pie chart based on the amount of memory consumed by each one of them. In most of the situations, SQLBUFFERPOOL would be the biggest consumer of the memory. This output is taken from sys. dm_os_memory_clerks DMV, which is one of the key DMV in monitoring SQL Server memory performance.  We can use sys.dm_os_memory_clerks to identify where exactly SQL’s memory is being consumed.

Buffer Pages Distribution (# Pages)

This particular section of the report shows the state of buffer pages. Behind the scenes it uses DBCC MEMORYSTATUS to get the distribution of buffer in various states. Buffer Distribution can be one of the following as: ‘Stolen’, ‘Free’, ‘Cached’, ‘Dirty’, ‘Kept’, ‘I/O’, ‘Latched’ or ‘Other’. Interestingly, if we run the DBCC MEMORYSTATUS, we may not see all these states. This is because memory status output format has been constantly changing SQL 2000 (KB 271624) and SQL 2005 (KB 907877).

Memory Changes Over Time (Last 7 Days)

This section of the report shows data from default trace. One of the event which is captured by default trace is “Server Memory Change” (Event id 81). Behind the scene, this section reads default trace, looks for event ID 81 and adds a filter (datediff(dd,StartTime,getdate()) < 7) to display last 7 days records. My laptop doesn’t have much load that why we don’t see any memory change. Another reason, as quoted in the text, of no data could be that default trace are disabled.

I am sure in your production or active development boxes these values are not going to be zero for sure.

Memory Usage By Components

At the bottom, there is a table which shows the memory for each component.  This is also taken from the same DMV, which is used in “Top Memory Consuming Components”. The graph earlier shows top 5% consumers by name and the rest would be shown as others. It’s important to note that in SQL 2014, it would always show MEMORYCLERK_XTP which is used by In-Memory OLTP engine (even if it’s not a top consumer).

Here is the little description of various columns:

Allocated Memory Amount of memory allocated to sqlservr.exe
Virtual Memory (Reserved) Memory reserved in Virtual Address Space (VAS)
Virtual Memory (Committed) Memory committed in Virtual Address Space. Once memory is committed in VAS, it would have physical storage (RAM or Pagefile)
AWE Memory Allocated Amount of memory locked in the physical memory and not paged out by the operating system
Shared Memory (Reserved) Amount of shared memory that is reserved
Shared Memory (Committed) Amount of shared memory that is committed

To understand reserve and committed, I always quote this. Imagine that you need to fly to Mumbai on a certain date and you book a flight ticket. This is called reservation. There’s nothing there yet, but nobody else can claim that seat either. If you release your reservation the place can be given to someone else. Committing is actually grabbing the physical seat on the day of travel.

Hope this gives you a fair idea about various pieces of memory consumers. As I mentioned before, this is one of those hidden gem reports that never gets seen. One can learn and know about a current running system and who are using SQL Server Memory from this report easily.

I would be curious to know if in any of your systems if there is any other component apart from BufferPool or SOSNode as the top memory consumers?

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