SQL Server Performance


Discussion in 'Contribute Your Performance and Clustering Tips' started by Stas, Jan 17, 2006.

  1. Stas New Member

    Sometimes we need receive alert on client side about some event on SQL server without request. For instance, data is changed and we want notify all client applications. It is possible to realize this option with extended stored procedure contained in dynamic-link library, that send broadcast packets through sockets via UDP. The creation of an extended stored procedure was made in C++ Builder 6, with use of ODS (Open Data Service) API for MS SQL Server 2000. To take into consideration that static library Opends60.lib with this Borland IDE, which realize all services of ODS API, has outdated version and support only MS SQL 7. You may get import library file from my page or create it self-dependent with Implib utility. Also, to be noticed that UDP not ensure the message delivery, but don#%92t require a connection, as TCP. This is main reason why UDP was choosen.<br /><br /> The simple example of using alerts is generation event from user audit table#%92s trigger after adding new record. The structure of “EVENT” table consist of unique record identifier, user login and message. The extended stored procedure "xp_event" may has following input parameters: &lt;host name&gt;, &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />ort number&gt;,&lt;message text&gt;, &lt;user name&gt;, &lt;record identifier&gt;. You may use broadcast address in host name argument. For instance, (net-directed broadcast ) or (limited broadcast address). Also you may use the local network computer#%92s name. Notice, that if your net has subnets, then router didn#%92t admit any broadcast packet without addition settings. The number of UDP port is optional, but you should avoid system ports that are using by OS. By default, the client listen 3338 port.<br /><br /> The TSQLAlerter component (<a href='http://www.messageapi.narod.ru/' target='_blank' title='http://www.messageapi.narod.ru/'<a target="_blank" href=http://www.messageapi.narod.ru/>http://www.messageapi.narod.ru/</a></a>) has two methods: Start and Stop, which creates new process for port listening and stop it correspondingly, i.e. the client has UDP server roles. The event OnGetMessage comes in the moment of notification delivery, and the pointer to TLabel visual component allows display a getting message at the form. The structure are using for sending broadcast messages has the following view:<br /><pre id="code"><font face="courier" size="2" id="code">typedef struct TDATASEND // The structure for sending alerts<br /><br />{<br /> char message[1024]; <br /> char login[1024];<br /> long id;<br />} TDATASEND;</font id="code"></pre id="code"> The thread gets an alert and synchronize properties Message, RecordId and Login of TSQLAlerter class object in AddMessage() method. The example of registration procedure and realization of delivery notification you may look in script <a href='http://www.messageapi.narod.ru/TSQLAlerter_Script.html' target='_blank' title='http://www.messageapi.narod.ru/TSQLAlerter_Script.html'<a target="_blank" href=http://www.messageapi.narod.ru/TSQLAlerter_Script.html>http://www.messageapi.narod.ru/TSQLAlerter_Script.html</a></a>.
  2. satya Moderator

    Useful procedure and script to generate alers without stressing SQL server, but have you tested this on a large scale environment. Running C++ process on top of stressed SQL server will have negative effects, as we have had so.

    (I will move to scripts forum for relevancy)

    Satya SKJ
    Contributing Editor & Forums Moderator
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. Stas New Member

    satya, I try test my solution with help of this simple script:

    declare @count int
    set @count = 0
    while @count < 1000
    insert into EVENTS VALUES('Stas','Test Message')
    set @count = @count +1

    You can see profiler log at picture http://messageapi.narod.ru/test.jpg:

    First record - script execution with 'xp_event' extended stored procedure.
    Second record - drop 'events_trg' trigger.
    Last record - script execution without 'xp_event' extended stored procedure.

Share This Page