March Madness – SQL Azure – sys.dm_exec_sessions

Yesterday I showed you two ways to find out how changes to SQL Azure may impact your existing database. Today we shift gears a bit and go under the covers of your SQL Azure database to start looking at who is doing what to your database.

For those of you used to administering an on-premise instance of SQL Server you are aware of a plethora of ways to view the current sessions. When you move to SQL Azure you have two options, but both will require you to use the sys.dm_exec_sessions system view. The first option is to run the following code inside of SSMS against your SQL Azure database and examine the results:

SELECT *
FROM sys.dm_exec_sessions

When I did this it returned twelve rows, one of which was my own session. Running it again I had only six rows. At this point I am guessing that the other sessions are background processes, but that is just a guess (for now), and the total number of sessions will indeed fluctuate.

The second option is to run the same code, but to do it from the SQL Azure manage portal.

What’s that? You never heard of the SQL Azure manage portal? Neither did I until recently. I’m not even sure that is the correct name. But you get there from the Windows Azure portal by clicking on the link that says ‘Manage’:

From there you can execute a query, same as if you were in SSMS. Just click on the ‘New Query’ button, enter in your T-SQL, and then click on the ‘Run’ button. You should see a screen similar to this one:

This portal has a lot of interesting features. Think of it as a very scaled down version of SSMS that was designed specifically for the cloud. That means you don’t get something like Activity Monitor, but you do get the ability to run some scripts in order to pull back some details from the system views.

I like the portal, it gave me some very interesting information to digest. We will get back to the portal later this week, I am certain. Tomorrow we will keep digging around under the covers.