Tracking connections on your SQL Instance

Many times throughout my career, I have needed to complete tasks on databases that there was little to no information about. I often refer to these little guys as mystery databases. Whether it be migrations, decommissions, troubleshooting application issues or any other issues, it can sometimes be hard to figure out who or what is using one of these mystery databases. Even speaking to people you think may know (or should) know the answers can come up fruitless. So how do you handle that?

Yes, you can run one of the various scripts (such as sp_who or sp_whoisactive) to see who\what is currently connected, but how confident can you be that is the complete list? What if an application only connects periodically? If you have little to no information on the database, can you know for sure? And yes, you could shut it down and see what breaks and who screams, but is this the most effective way?

I got tired of seeing blank stares and hearing “I don’t know” from application owners and developers so I created a process that lets me track connection history. This allowed me to answer these questions on my own or at least have more information available to me when talking to other teams. What I have found since we started running this process, is that is one of the most widely used tools that my team uses. Especially after this process has been running for months and have that data available to me.

The process is just a proc that pulls a distinct list from master.dbo.sysprocesses based on Instance name, database name, login, host name, and program name and loads this data into a table. Each time it runs I have a counter column that exists that is increased by 1 each time a distinct connection is found and a timestamp column that is updated with the most recent time it was found. I run this proc via a SQL Agent job on all of my servers on a 3-minute interval. The task is lightweight so you can run it as often as you like and the table should stay relatively small.

Let’s take a look at what you need to do to set this up. For the sake of this article, I am going to include it in the master database, however, I have a user created DBA database that is created on all my servers that I normally use. I use this for all the objects that I need created to manage a server.

1. Create a table that you plan to log the data into.

USE [master]
GO

CREATE TABLE [dbo].[SQLConnections](
	[SQLConnections_ID] [int] IDENTITY(1,1) NOT NULL,
	[Instance_Name] [nvarchar](50) NOT NULL,
	[Database_Name] [nvarchar](100) NULL,
	[loginame] [nvarchar](128) NULL,
	[hostname] [nvarchar](128) NULL,
	[program_name] [nvarchar](128) NULL,
	[LastLogin] [datetime] NULL,
	[ConnectionCount] [bigint] NULL,
 CONSTRAINT [PK_SQLConnections] PRIMARY KEY CLUSTERED 
(
[SQLConnections_ID] ASC
))

2. Create the proc

USE [master]
GO
CREATE PROC [dbo].[sp_LoadCurrentConnections]

AS

-- Create a Temp Table to hold the current connections

IF OBJECT_ID('tempdb..#SQLConnections') IS NOT NULL DROP TABLE #SQLConnections
CREATE TABLE #SQLConnections
(Instance_Name nvarchar(50),
Database_Name nvarchar(100),
loginame nvarchar(128),
hostname nvarchar(128),
program_name nvarchar(128),
LastLogin datetime,
ConnectionCount bigint)

-- Insert into DBAdb.dbo.SQLConnections

INSERT INTO #SQLConnections
(Instance_Name, Database_Name, loginame, hostname, program_name, LastLogin, ConnectionCount)
SELECT @@Servername as Instance_Name,
DB_NAME(dbid) as Database_Name,
loginame,
hostname,
program_name,
MAX(login_time) as LastLogin,
1 as [Count]
FROM sys.sysprocesses
GROUP BY DB_NAME(dbid), loginame, hostname, program_name

--- Update any existing data with latest login and add to count

UPDATE SC
SET SC.LastLogin = SCS.LastLogin,
SC.ConnectionCount = SC.ConnectionCount + 1
FROM #SQLConnections SCS
INNER JOIN master.dbo.SQLConnections SC ON SC.Instance_Name = SCS.Instance_Name
AND SC.Database_Name = SCS.Database_Name AND SC.loginame = SCS.loginame
AND SC.hostname = SCS.hostname AND SC.program_name = SCS.program_name

--- Insert any new rows into the table that do not exist

INSERT INTO master.dbo.SQLConnections
(Instance_Name, Database_Name, loginame, hostname, program_name, LastLogin, ConnectionCount)
Select SCS.Instance_Name, SCS.Database_Name, SCS.loginame, SCS.hostname, SCS.program_name, SCS.LastLogin, 1
FROM #SQLConnections SCS
LEFT JOIN master.dbo.SQLConnections SC ON SC.Instance_Name = SCS.Instance_Name
AND SC.Database_Name = SCS.Database_Name AND SC.loginame = SCS.loginame
AND SC.hostname = SCS.hostname AND SC.program_name = SCS.program_name
WHERE SC.Instance_Name is null

Note: You probably could use some of the other commonly used scripts that exist out there and just log the data to a table as well. This script is just what I use since its simple and accomplishes what I need for it to.

3. Create a SQL Agent job to execute the stored proc on the schedule of your choosing. (Note: I typically run it every 3 minutes)

After everything is setup and running

Once you have the process running, you have a historical record of what is connecting to your servers and an idea of how often it is connecting.

Here is an example output from a server that I manage (names have been updated to generic values) that will give an idea of the result set.

ConnectionScreenshot

From this table, you can see what each connection being made to your databases are and how often they are connecting. There are several things you can do with this information and you may find that it comes in handy a lot more then expected. You will find that anytime you need to see what connects to a database, this will be your go to place.

Getting the most from your SQL licensing

If you have a good understanding of SQL licensing (or read my previous blog post) then there are several things you can do to help optimize your environment to make sure you are getting the most from your licensing. In this article, I will also go through and talk about things you should be doing to make sure you are in compliance, but also things that you can do that present a significant cost savings to your organization.

I have worked with or for several different organizations throughout my career and been able to help them discover millions in licensing savings\compliance issues. The information below is the process that I have went through and checked with each of them to discover this.

Make sure you are selecting the correct licensing model.

I would encourage you to read Understanding the Basics of SQL licensing if you not have done so already or if you are unsure. In my experience, the most costly mistake that people make is licensing physical hosts over licensing VM’s. I have seen several cases where this amounts to millions of dollars cost difference. Licensing by host certainly has its place, but make sure you understand what that is. And if you are not sure what is the best, then take the time to do the math and see which way is the most cost effective.

Correct any issues with resource over-provisioning.

Take a look at the servers running in your environment and see if they are actually using all of the resources that are allocated to them. The most important thing to pay attention to is CPU since you are licensing based on cores. The second most important thing to look at is memory and that is because this alone could affect whether you need Enterprise or Standard. Then you still may want to look at other resource metrics that could have an impact on your CPU.

    • CPU – Check your average CPU utilization over time (especially during critical processing hours) and see what it is running at. Do not just compile an average number, but look at the CPU usage over a period of time so you can understand your spikes. My general recommendation is to try to keep average CPU somewhere around 30%. If its way under that then you may be over-provisioned and if you are way over that you could be experiencing performance issues as a result. What I am typically looking for here is obvious signs, for example, if you never extend beyond 5% utilization on a 8 core server you may be over-provisioned and paying for something you are not using.
        • Remember that there is a 2 licensing minimum for SQL so running 2 cores is the same as 4 cores from a licensing perspective.
    • Memory – While memory is not part of the licensing guidelines it can play a supporting role, because it can define what version you need. I have seen customers purchase Enterprise edition due to the memory resource constraints of Standard edition (remember the 64GB memory limits in SQL 2012?). I have also seen examples where this was not needed and the company effectively over-bought. When you look at this, do not just look at memory utilization which is typically high, but look at internal SQL memory counters such as Page Life Expectancy and Buffer cache Hit Ratio. You want to get an idea of how often SQL is able to read from memory vs disk and determine if it is acceptable for performance.

Make sure you are running the correct version of SQL.

Oftentimes companies will run Enterprise edition in situations where it is not needed. Make sure if you are running Enterprise edition you have a valid reason to do so.

NOTE: Microsoft does not have a supported downgrade path so if you need to downgrade you will to do a uninstall\reinstall

My rule of thumb is to run the lowest version of SQL that you can while still meeting business needs. Of course, Enterprise is nice and beneficial, but it is actually needed

Avoid running applications on the same server as the database.

If at all possible, the database should always be on an isolated server. You do not want to be paying expensive licensing costs only to be having some other application consuming those resources.

A good check in this situation is to look at memory and CPU consumed by SQL and by other things running and view what the difference is. If there is a wide margin you need to figure out what is going and see if it is something that can be fixed.

Look for opportunities to virtualize your physical database servers. 

There is a good chance that if you have a database server in your environment that the workload on that may have changed at some point for better or worse. Databases and applications are retired, new ones are brought on, processes get moved, or any sort of other thing that can have an impact on the server. With physical servers you are more limited in terms of your ability to grow or decrease resources (CPU\Memory). While you can do it, it takes provisioning or removing hardware. With a virtual server you typically have an easier path related to scalability (whether up or down). Not saying you should virtualize everything, but something to consider when looking at your environment.

Spend time performance tuning where you can.

This encompasses a lot of stuff from server tuning, query tuning, index tuning, application tuning, etc. If you can perform work to reduce the CPU load then you can reduce the number of cores and ultimately reduce licensing. This is a loaded topic that deserves blog posts of its own, but there are several great resources out there to assist with this.

My experience with most of the customers or companies I have worked with is that most of the performance tuning that goes on is reactive. Meaning someone is having a problem with something being slow or not performing up to standard. But I would argue that pro-active performance tuning is also very important and can come with significant cost savings. Its just something that I do not think there is enough prioritization on.

For example, I was working on a server once with a customer that was 24 cores and running Enterprise edition. I was able to tune over a short period of time to the point we were able to reduce the core count down to 12 (in half). Looking at retail numbers, this reduced the licensing cost on this server from 168K down to 84K, saving 84K in the process. While this may not always be the case, you probably have a lot of things that you could reduce a little, and a little can add up to a lot.

Look for consolidation opportunities, especially on servers that do not meet the minimum licensing core count.

Look at your environment and look for opportunities to consolidate databases. Not every database deserves or needs to be running on its own server.

There are a few things that you need to consider when look at consolidation opportunities.

    • You need make sure you are not required the database on its own server and there are different scenarios where this may be required.
    • Check to see which servers do not meet the the minimum licensing requirement. These are prime candidates for consolidations because these are costing you money that you are not getting any return on.
    • Look at resource utilization on your existing servers to see if they would be better combined.

I think this is a topic that can be expanded on greatly and I will plan to do so. Because there is a bit of art to how you handle this. Consolidations can trigger a conversation of cost vs ease of management. I do not recommend changes based on cost alone and think you should always factor in the level of difficulty to manage. More to come on this topic.

Define and execute on a decommission process.

If something is not being used try to get it removed as soon as you can to free up those licenses.

Oftentimes servers stop getting used and they sit out in the environment taking up licenses and you continue to pay for maintenance on those. I suggest performing audits occasionally to see if a server is still being used (if you are unsure). I will be creating a new blog post soon that details how you can do this.

Determine if SQL licensing was included in the purchase of an application.

If you are using 3rd party vendor products you may want to double-check to see if the SQL licenses are included with the product. Especially if they are supporting the product for you and you are just hosting it. You do not want to be paying for a license a 2nd time over. If you are unsure, check your contract or check with the vendor. I would warn against assuming as I have been surprised before.

Keep in mind that if the licenses are included then they usually have certain restrictions so please work with your Vendor or licensing manager to check this. Commonly, the only thing you are allowed to run on the server is there product, so running on a shared instance may void the license.

Wrap up:

SQL licensing is expensive and there is a lot that can be done to reduce these costs. I have worked with companies to save millions by going through the things listed above and you can too.