Published: Thursday, July 15, 1999
Just What is SQL Doing?
By Julian
Have you ever wondered what in the hell SQL server is doing at any given
moment? Wouldn't it be nice to be able to view the inner workings of SQL
at any point in time from the web? Well, it's quite possible.
The trick, is to query the sysprocesses table. This tip, from Julian
Sitkewich queries the sysprocesses table, showing us minute information on
connections, statuses and processes running or sleeping on the server!
All we have to do is create a simple ASP page and paste the following
code into it:
<%<![CDATA[
Dim conn
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DSN=pubs" 'Use you own connection string here!
'Create a recordset and query sysprocesses
Dim rs
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "SELECT * FROM master..sysprocesses WHERE hostname <> ''", conn
'We query where hostname is NOT empty, so that we get the listing of
'user processes, not system processes.
]]>%><![CDATA[
<HTML>
<BODY>
<TABLE border=1>
<TR>
]]><%<![CDATA[ For i = 0 to rs.Fields.Count - 1 ]]>%><![CDATA[
<TH>
<FONT SIZE=2>
]]><%=rs.Fields(i).Name<![CDATA[
</FONT>
</TH>
]]>
<% Next %>
<![CDATA[
</TR>]]>
<% Do While Not rs.EOF %><![CDATA[
<TR>
]]>
<% For i = 0 to rs.Fields.Count - 1 %><![CDATA[
<TD>
<FONT SIZE=2>
]]>
<=rs(i)%><![CDATA[</FONT>
</TD>
]]>
<% Next %><![CDATA[
</TR>
]]>
<% rs.MoveNext
Loop %><![CDATA[
</TABLE>
</BODY>
</HTML>
]]>
<%
'Always important, clean up time!!
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
%>
|
So, what does this code do? Well, it's pretty straight forward, I think.
It first creates a connection object and connects to the database of your
choice. Next, a recordset object is created and a query on sysprocesses
is performed. This recordset object is then displayed in a table. Not
much to it, really. Simple, yet powerful.
Be sure to try it out, and see SQL's current status! :)
Julian is a software developer who currently spending his time developing
a web-based project management software using Java. Most of his experience
is in the e-commerce/Internet development arena and prefers to develop
with Java, ASP, JSP, XML, Oracle, SQL Server, and UNIX (in no particular
order). Previous to his current endeavors, Julian worked for an online
learning company where he designed and implemented a large, distributed
online learning application.
