Systems Engineering and RDBMS

Archive for May, 2010

Citrix Receiver and iPad

Posted by decipherinfosys on May 31, 2010

A friend of mine asked yesterday how he can go about accessing his windows environment using his newly purchased iPad.  Or is it that he can use iPad only for personal use and not for professional use since his work environment is all predominantly windows based.  Luckily, their IT uses Citrix for their VDI (Virtual Desktop Interface).  So, all he really needed was the Citrix receiver for the iPad.  The citrix receiver is essentially a lightweight client that makes accessing virtual desktops and applications a simple point and click operation.

Here are some links demonstrating how to use the citrix receiver to access an application or your desktop and run your windows applications over there:

Couple of videos demonstrating the usage of Citrix Receiver (on iPad as well as iPhone) – here.

Harry Labana’s blog post – here.

So, if you have this kind of an infrastructure, you can use your iPad for not only personal use but also for professional work.  Have fun :-)

Posted in Technology | Leave a Comment »

Happy Memorial Day!

Posted by decipherinfosys on May 31, 2010

We wish all of our readers in the US a very Happy Memorial Day.  For those readers who are not familiar with this holiday, here is the wikipedia link:

http://en.wikipedia.org/wiki/Memorial_Day

Posted in General | Leave a Comment »

Pooled Connections

Posted by decipherinfosys on May 31, 2010

What is connection pooling and why is it necessary? A connection resource on the database side is essentially the database server process, the session and the associated memory.  In order to utilize the database resources effectively and to provide end user concurrency, applications use various resource pooling solutions.   In a typical web based application, connection pooling is typically done by a middle tier like an application server which gets x number of connections to a database and stores them in a pool (like an array).  Those are then marked as not being in use.  When a request comes in from the end user that goes through the application server, it would get a connection from the pool and use it as well as mark it as “in use”.  Once the response is given to the request and the session has been fulfilled, it goes back to the pool so that it can be used by another request.

The benefits of connection pooling are obvious – no connect/disconnect of the connections which is an overhead and using pooled connections, one can service the needs to many users.  In this post, we will look into pooled connections from a DBA’s perspective i.e. how a DBA can find out whether the connections hitting their instance are pooled or not.

As per MSDN, the memory usage per connection is roughly: (3 * network_packet_size + 94) KB.  So, for 4096 byte network packet size which is the default, we are talking about 106KB per connection.   So, you can see how it can add up pretty fast.  So, from a DB side, how can we track whether our applications are using pooled connections?  We can either trace the sessions (sql trace/profiler provides all the pertinent information) – using the same SQL that we had used in our Profiler basics post, we can change the filter condition to see which event classes and which event name and data columns & subclassnames are to be used:

select
v2.name as Event_Class
, v1.name as event_name
, v4.name as data_column
, case v4.is_filterable when 0 then 'No' else 'Yes' end as Is_Column_Filterable
, v5.subclass_name
, v5.subclass_value
from
sys.trace_events as v1
inner join sys.trace_categories as v2
on v1.category_id = v2.category_id
inner join sys.trace_event_bindings as v3
on v1.trace_event_id = v3.trace_event_id
inner join sys.trace_columns as v4
on v3.trace_column_id = v4.trace_column_id
inner join sys.trace_subclass_values as v5
on v3.trace_event_id = v5.trace_event_id
and v3.trace_column_id = v5.trace_column_id
where v5.subclass_name like '%pooled%'
order by v2.name, v1.name

Output:

Event_Class    event_name    data_column    Is_Column_Filterable    subclass_name    subclass_value
Security Audit    Audit Login    EventSubClass    Yes    Nonpooled    1
Security Audit    Audit Login    EventSubClass    Yes    Pooled    2
Security Audit    Audit Login Failed    EventSubClass    Yes    Nonpooled    1
Security Audit    Audit Login Failed    EventSubClass    Yes    Pooled    2
Security Audit    Audit Logout    EventSubClass    Yes    Nonpooled    1
Security Audit    Audit Logout    EventSubClass    Yes    Pooled    2

Using these, one can trace it out either by using SQL Trace or an active Profiler session.  However, a better solution would be to make use of the EVENTDATA() function along with a logon trigger since that would put less load on the server and also provide us an easy way to do trend analysis – we had discussed their usage once before when answering a question from a reader on how to prevent connections from certain IP addressess – you can read that post here.   As you know, in order to capture the XML data about LOGON events, we will make use of the EVENTDATA() function.  And as per BOL, the LOGON event returns this event data schema – and it has the IsPooled information that we are interested in:

<EVENT_INSTANCE>
<EventType>event_type</EventType>
<PostTime>post_time</PostTime>
<SPID>spid</SPID>
<ServerName>server_name</ServerName>
<LoginName>login_name</LoginName>
<LoginType>login_type</LoginType>
<SID>sid</SID>
<ClientHost>client_host</ClientHost>
<IsPooled>is_pooled</IsPooled>
</EVENT_INSTANCE>

Unlike the Audit logon trigger that we had created in that post, over here we would be interested in doing some trend analysis since we need to capture the data over a period of time to see what is going on and whether we have pooled connections or not.  So, let’s create a table for analysis:

CREATE TABLE dbo.AUDIT_POOLED_CONNECTION
(
AUDIT_POOLED_CONNECTION_ID        INT    IDENTITY    NOT NULL
,    EVENT_TYPE                        NVARCHAR(128)    NOT NULL
,    POST_TIME                        DATETIME        NOT NULL
,    SPID                            INT                NOT NULL
,    SERVER_NAME                        NVARCHAR(128)    NOT NULL
,    LOGIN_NAME                        NVARCHAR(128)    NOT NULL
,    CLIENT_HOST                        NVARCHAR(30)    NOT NULL
,    IS_POOLED                        BIT                NOT NULL
,    CREATE_DATE_TIME                DATETIME        NOT NULL DEFAULT GETDATE()
,    CREATE_USER_ID                    INT                NOT NULL DEFAULT 2 --ADMIN USER
,CONSTRAINT PK_AUDIT_POOLED_CONNECTION PRIMARY KEY (AUDIT_POOLED_CONNECTION_ID)
)
GO

And here is the simple logon trigger code to dump the data into this table:

create trigger AuditIsPooled_Demo
/* server means instance level*/
on all server
with execute as 'sa'
/* We specify the logon event at this stage
– Capture all the pertinent information for trend analysis
*/
for logon
as
begin
/*Insert the data that we are interested in for Pooled connections analysis*/

Insert into dbo.Audit_Pooled_Connection (Event_Type, Post_Time, SPID, Server_Name, Login_Name, Client_Host, Is_Pooled)
SELECT
EVENTDATA().value(‘(/EVENT_INSTANCE/EventType)[1]‘, ‘nvarchar(128)’) as Event_Type
, EVENTDATA().value(‘(/EVENT_INSTANCE/PostTime)[1]‘, ‘datetime’) as Post_Time
, EVENTDATA().value(‘(/EVENT_INSTANCE/SPID)[1]‘, ‘int’) as SPID
, EVENTDATA().value(‘(/EVENT_INSTANCE/ServerName)[1]‘, ‘nvarchar(128)’) as Server_Name
, EVENTDATA().value(‘(/EVENT_INSTANCE/LoginName)[1]‘, ‘nvarchar(128)’) as Login_Name
, EVENTDATA().value(‘(/EVENT_INSTANCE/ClientHost)[1]‘, ‘nvarchar(30)’) as Client_Host
, EVENTDATA().value(‘(/EVENT_INSTANCE/IsPooled)[1]‘, ‘bit’) as Is_Pooled

end
go

Now, upon several executions, we can see the output in that table (abridged):

AUDIT_POOLED_CONNECTION_ID    EVENT_TYPE    POST_TIME    SPID    SERVER_NAME    LOGIN_NAME    CLIENT_HOST    IS_POOLED    CREATE_DATE_TIME    CREATE_USER_ID
118    LOGON    2010-05-31 12:00:04.023    56    SVDB-03    sysdba    192.168.104.130    1    2010-05-31 12:00:04.037    2
119    LOGON    2010-05-31 12:00:04.037    58    SVDB-03    sysdba    192.168.104.130    0    2010-05-31 12:00:04.037    2

So, now we have seen how a DBA can monitor pooled connections from the instance side.  And for more understanding of the connection pools, their configuration and controlling connection pool fragmentation, look at the resources section below.

We will look into connection pooling in Oracle from a DBA perspective perspective in one of our future posts.  Oracle has also introduced DRCP (Database Resident Connection Pooling) in Oracle 11g which is also something we will look into that post.

Resources:

  1. Logon Triggers and EventData() function – here, here and here.
  2. MSDN article on connection pooling for the .Net Framework Data Provider for SQL Server – here.
  3. Another good article on ADO.Net connection pooling – here.
  4. Article at Pythian – excellent article discussing connection pooling – here.
  5. MSDN post on SQL Server connection Pooling (ADO.Net) – here.

Posted in .Net Development, SQL Server | Leave a Comment »

Getting the counts in hour intervals

Posted by decipherinfosys on May 24, 2010

A friend recently asked a question about how to split the counts for a given day into 4 hour intervals for an event that happens in their system.  The scenario was quite simple – they have meetings that happen every day and there is an activation event that is done before that meeting is made public to their end clients.  He wanted to build out a report showing the total count of the meetings that were activated on a given day and then their break up in 4 hour intervals to track down how the progress is made during the day by the operational staff.  A sub-report detailing the work metrics by team members was also done based on those time intervals to track the efficiency of the workers.

Here is the simple SQL to make it possible – this simply groups the data by the date and since the intervals are known, uses a simple logic to put the data in their respective buckets:

SELECT
CAST(CONVERT(VARCHAR(10), MEETING_ACTIVATION_DATE, 101) AS DATETIME) AS MEETING_ACTIVATION_DATE
,    COUNT(*) AS TOTAL_CNT
,    SUM(CASE WHEN cast(convert(varchar(2), meeting_activation_date,  114) as int) between 1 and 4 then 1 else null end) AS HOUR_1_4
,    SUM(CASE WHEN cast(convert(varchar(2), meeting_activation_date,  114) as int) between 5 and 8 then 1 else null end) AS HOUR_5_8
,    SUM(CASE WHEN cast(convert(varchar(2), meeting_activation_date,  114) as int) between 9 and 12 then 1 else null end) AS HOUR_9_12
,    SUM(CASE WHEN cast(convert(varchar(2), meeting_activation_date,  114) as int) between 13 and 16 then 1 else null end) AS HOUR_13_16
,    SUM(CASE WHEN cast(convert(varchar(2), meeting_activation_date,  114) as int) between 17 and 20 then 1 else null end) AS HOUR_17_20
,    SUM(CASE WHEN cast(convert(varchar(2), meeting_activation_date,  114) as int) between 21 and 24 then 1 else null end) AS HOUR_21_24
FROM dbo.MEETING_MASTER
WHERE MEETING_ACTIVATION_DATE IS NOT NULL
GROUP BY CONVERT(VARCHAR(10), MEETING_ACTIVATION_DATE, 101)
ORDER BY CAST(CONVERT(VARCHAR(10), MEETING_ACTIVATION_DATE, 101) AS DATETIME) DESC
GO

Same thing can be done in Oracle by using the TRUNC() function with date arithmetic or the INTERVAL/EXTRACT functionality.

Posted in SQL Server | Leave a Comment »

MSDN whitepapers on SQL Server 2008

Posted by decipherinfosys on May 24, 2010

In case you haven’t stumbled upon these whitepapers, here is the link to the MSDN whitepapers on SQL Server 2008:

http://msdn.microsoft.com/en-us/library/dd631807(SQL.10).aspx

There is a wealth of information in these whitepapers.   Happy Reading!  :-)

Posted in SQL Server | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 77 other followers