I Distinctly Detect a Percolating Process

I’m back! Miss me? 🙂 I apologize for not posting for the last couple of weeks.

Ever have one of those days / weeks / months / years / lives when you feel like you’re trying to take a sip of water out of that fire hose called “life” and you get a bit more than you bargained for? Yeah, life’s been like that lately. I am really hoping things will calm down now at least for the next few weeks. It’s just been one thing after another all at once or so it seems. Anyway, I really want to get back to posting on my blog regularly and improving upon my role as VP of Marketing for our local neighborhood friendly SQL Server user grouphmm… I wonder if SQL bacon swag would help increase membership…

Anyhow, back to life with SQL Server. For the last month or so, I’ve been posting in pieces about the script I use to check my SQL servers. My last post involved monitoring logins. This week is all about processes. As in what is connecting to my server and why do I care?

I don’t know about anyone else, but I like to know what should be connecting to the servers and what is connecting. Ever since my fun filled week with a SQL injection attack at my last job, I became pretty paranoid regarding who and what is connecting to the servers. So I try to keep tabs on the programs connecting in case I spot something odd. It takes one to know one, right? In addition, for the majority of our servers at my current job we generally don’t allow anyone who is not a DBA to connect to the databases using Management Studio or Query Analyzer (for SQL 2000 and prior). We do have some exceptions, and I keep that in mind while checking the servers. I also like to check if something is being blocked and if there are multiple processes with the same spid (server process id a.k.a. session id in SQL 2005+).

Below is one of the queries I currently have in my script. I will then show you what I have added to my script since attending Tim Ford’s (Blog | Twitter) DMO session on SQL Cruise.

SQL Server 2000:
--Check processes...
select blocked
,hostname
,loginame
,spid
,program_name
,cmd
,dbid
,login_time
,cpu
,physical_io
,memusage
from master..sysprocesses
where datediff(d,login_time,getdate())>1
order by login_time;

For SQL Server 2000 systems, I queried the sysprocesses system table. You can also run sp_who2. I used the system table because at the time it was easier to query against, and I wanted to see connections for the last day. This just gives me a feel for how many new connections there are. Here is the script for SQL Server 2005+:

SQL Server 2005:
--Check processes...
select requests.blocking_session_id
,sessions.host_name
,sessions.login_name
,connections.session_id
,sessions.program_name
,connections.client_net_address
,connections.client_tcp_port
,connections.local_net_address
,connections.local_tcp_port
,sessions.cpu_time
,sessions.memory_usage
,requests.status
,requests.command
,requests.wait_type
,requests.last_wait_type
,requests.database_id
,requests.user_id
from master.sys.dm_exec_connections connections
inner join master.sys.dm_exec_sessions sessions
on (connections.session_id = sessions.session_id)
left join master.sys.dm_exec_requests requests
on (connections.session_id = requests.session_id)
where datediff(d, connections.connect_time,getdate())>1
order by connections.connect_time;

This one is a bit more involved. It uses three system views: dm_exec_connections, dm_exec_sessions, and dm_exec_requests. These views map to the sysprocesses table. You can probably guess what kind of information each view contains.

The dm_exec_connections view shows detailed information regarding each connection to the SQL Server instance. The dm_exec_sessions view shows information on both the connections to SQL Server and the processes internal to SQL Server such as background tasks that are running. The dm_exec_requests view basically shows information about what is executing in SQL Server at that moment. I’ve included it so I know if something is being blocked. For more information on these DMVs, see Books Online (BOL).

I don’t know if any of you caught it, but I noticed a “funny” thing while writing this post. No, it’s not the author of this blog… or is it? When I first wrote this script a few years ago, I didn’t really know what I was doing. Now I noticed that I really should have put the sessions DMV first and then joined it to the connections DMV. Since the sessions DMV returns more than just connection information, I probably should put it first to include the internal processes in the results. Below is the new query which will now include information on the internal processes as well. I included “distinct” in the select, switched around the sessions and connections DMVs, and changed the where and order by clauses to use the login_time column from the sessions DMV.

select distinct requests.blocking_session_id
,[sessions].host_name
,[sessions].login_name
,[sessions].login_time
,connections.session_id
,[sessions].program_name
,connections.client_net_address
,connections.client_tcp_port
,connections.local_net_address
,connections.local_tcp_port
,[sessions].cpu_time
,[sessions].memory_usage
,requests.status
,requests.command
,requests.wait_type
,requests.last_wait_type
,requests.database_id
,requests.user_id
from master.sys.dm_exec_sessions [sessions]
left join master.sys.dm_exec_connections connections
on ([sessions].session_id = connections.session_id)
left join master.sys.dm_exec_requests requests
on ([sessions].session_id = requests.session_id)
where datediff(d, [sessions].login_time,getdate())>1
order by [sessions].login_time;

What basically ties these views together is the session id. In SQL Server 2000 and prior, this is the spid. It basically identifies an individual process or session.

To clarify the difference between the sessions and connections DMVs, run the following queries on a test/development system. The sessions DMV will return records where the session_id is both less than 50 (system processes) and greater than or equal to 50. The connections DMV will return only records where the session_id is greater than 50 (non-system processes).

select 'sessions'
,[processes].spid
,[processes].loginame
,[processes].login_time
,[processes].hostname
,[processes].program_name
,[sessions].session_id
,[sessions].login_name
,[sessions].login_time
,[sessions].host_name
,[sessions].program_name
from master.sys.sysprocesses [processes]
inner join master.sys.dm_exec_sessions [sessions]
on [processes].spid = [sessions].session_id;

select 'connection'
,[processes].spid
,[processes].loginame
,[processes].login_time
,[processes].hostname
,[processes].program_name
,connections.session_id
,connections.connect_time
,connections.num_reads
,connections.num_writes
,connections.client_net_address
from master.sys.sysprocesses [processes]
inner join master.sys.dm_exec_connections connections
on [processes].spid=connections.session_id;

Check out the spids and session_ids below.

Sessions vs Connections

Using variations on these queries, you can check a whole bunch of different things such as what distinct processes and connections are there, who is connecting to the server using Management Studio, if any processes are being blocked, and so on. I have a few different queries for these things but in the interest of brevity (Ha! Me? Brief? Double-ha!) I won’t post them here. I’ll include it in the revised version of my script which will be posted at the conclusion of my little series here. Yes, there is a method to my madness. By the way, there is a whole slew of things you query on with these DMVs. BOL has more info on it.

Now for the new stuff I added. When I first wrote this script, it was mainly for SQL Server 2000 database servers. When we added in SQL Server 2005, I just rewrote my 2000 scripts using the new 2005 system views. I didn’t stop to think of all the cool things you can use the “new” system views for. New to me, anyway. Insert cool things I learned while on SQL Cruise. Note: These queries were NOT taken verbatim from Tim. I just took what I learned and applied it to my script. Contrary to popular belief or what you may have heard from my grumpy cat, I am not that stupid. However, if I inadvertently did something stupid like that, I will personally flog myself… repeatedly… or confine myself to a room with nothing but an application written by a monkey using Access 2.0…

One super cool thing I learned was “cross apply”. I’ve heard about it and have seen it used. However, I didn’t really know what it did. As I understand it, “Cross Apply” basically allows you to call a table-valued function and apply it to the results from your query. For example, let’s say you want to know who is connected to the server instance and you want to know what they’re doing. Here is one way of doing this in SQL Server 2005+.

Here is a query to see who is connected to the database server:

select [sessions].host_name
,[sessions].login_name
,[sessions].login_time
,connections.session_id
,[connections].most_recent_sql_handle
,[sessions].program_name
,connections.client_net_address
,connections.client_tcp_port
,connections.local_net_address
,connections.local_tcp_port
from master.sys.dm_exec_sessions [sessions]
inner join master.sys.dm_exec_connections connections
on ([sessions].session_id = connections.session_id)
order by [sessions].login_time;

When you run it, the value returned for “most_recent_sql_handle” looks like a bunch of gobble-de-gook. At least that’s how it looks to me.

most_recent_sql_handle

It’s actually a varbinary and contains the command or request that was last executed from this connection. Now, if you’re anything like me and really want to know what that request was, you can find out! What? You can? Please tell me more, oh great princess of the SQL! You just need to use this handy dandy function called sys.dm_exec_sql_text(). You can actually run it by itself specifying a sql handle, like this:

select text from sys.dm_exec_sql_text(0x020000009FB257015CD6602EDDA19A13E2CECD08F24E0A99);

The results I get from the one above are here:

dm_exec_sql_text

Pretty nifty, huh? So now, you can use the Cross Apply and include it in the query like this:

select session_id
,connect_time
,most_recent_sql_handle
,sql_text.[text]
from sys.dm_exec_connections
cross apply sys.dm_exec_sql_text(most_recent_sql_handle) sql_text;

And the results are… drum roll, please!

Cross Apply Example

Voila! And that’s all there is to it! I was amazed it was that simple. Now, this wasn’t the only thing I learned. There’s plenty more including troubleshooting and performance tips, but I can’t write this post forever. I doubt you all can stay awake that long. 🙂

On a bit of a side note, when I need to troubleshoot performance issues for our SQL Server 2005+ systems and I get stuck on what DMVs to use, I also stop at Glenn Alan Berry’s blog for help. It’s another fantastic source for all-things DMVs, and I can usually find what I need. I would most definitely recommend it. He’s also on Twitter.

Some of you may have been wondering what’s been going on at work these days. Quite a bit, actually. We all have our projects we’ve been working on. Our poor boss had to work pretty much all Labor Day weekend doing backups, restores, and moving indexes for our financial system upgrade. We were her backups in case things went belly up or she was too tired to continue. Thankfully for everyone (including my bank account), it went fairly well considering there were a few bumps along the way. Whew! We didn’t even find her curled up under her desk in a ball clutching a blanket or rocking back and forth come Tuesday morning! In our eyes, that means it went pretty well.

As for everything else, from speculation as to why someone would leave a broom in our area (regardless of what you’ve heard, no, I did not fly in on it) to my DBA buddy blowing her lid multiple times (in the microwave) to one of our power users informing us he will “call Pennsylvania right away and get an air drop from the Hershey’s factory” for helping him solve a database connection issue, I really can’t complain about anything at work.

We’ve been pretty busy lately anyway which is usually a good thing. My DBA buddy believes it’s fitting that I’m the Princess considering I’ve been working on these things called “taxing entities”. Yes, I have my kingdom and my minions love me. Well, not really… no minions yet but I’m working on it! Speaking of minions, it’s time to start looking for another assistant. SQL Bunny refuses to come back no matter how much I claim to now provide bunny-fits (e.g. benefits).

On a positive note, I’m considering resuming my mind control experiments. So far it’s going better than I had anticipated. We can now finish each other’s thoughts. Hey! What? Oh no, you didn’t! Oh yes I did! Hey look! Squirrel! We’ve been wearing the same color clothing for several days in a row. Who says DBAs don’t have style?! Although, now we need to keep at least one extra SQL Sentry t-shirt in the office. Both our boss and I wore our SQL Sentry t-shirts to work on Thursday without consulting each other first! Is that cool or what? Of course, my DBA buddy had to be different and not wear hers. Tsk! Tsk! I shall have to work on that.

Speaking of my DBA buddy, she had the gall to attempt switching the mind control experiment on me but was met with little success, thankfully. Apparently, she’s been trying to get me to bake more dark chocolate *yum!* brownies for several weekends in a row by repeatedly sending me thoughts of dark chocolate brownies. I haven’t made any yet. Ha! Nice try! 😛

So, what’s next for your beloved SQL Princess? There are some really great events coming up which I am very excited about. First, SQL Sentry is coming to Colorado! Yay! We didn’t scare them away! Woo hoo! They will be presenting at the SQL Server user groups for Colorado Springs, Denver, and Boulder!

Secondly, SQL Saturday #52 is coming up on September 25th in Denver! Yay! Not only that, but your beloved SQL Princess is respectfully donating her time to this special event. Yes, you read that correctly. I will be there! Woo hoo! So you had better register now if you haven’t already! I will have to warn you that I just might bring my camera too. So don’t behave! Err… I suppose that should be *cough* you had better behave yourselves! Yeah, that’s it! 😉

Updated: To view the prior post in this series, please see “Peek-a-Who? Ha! I See You!”  Next in the series is “Growing, Growing, Gone!”

Advertisements

4 thoughts on “I Distinctly Detect a Percolating Process

  1. Tim Ford’s DMV training on SQLCruise inspired a blog post I hope to send into the wild soon. So it’s true, we were paying attention and not just looking out the window at the pretty blue ocean 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s