SQL Server Performance

Alert Notification ...URGENT Help Required

Discussion in 'T-SQL Performance Tuning for Developers' started by bzeebee, Sep 16, 2003.

  1. bzeebee New Member

    I want to create an alert for the following condition -
    if the number of rows in a table Exceeds 2M it should send me an email...
    How can i do this...Please advice...

    Thanks,
    B
  2. gaurav_bindlish New Member

    Create a trigger on the table for checking the number of rows. As soon as it crosses 2M, generate a mail using XP_SENDMAIL.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  3. bambola New Member

    You could set a job that runs a count(*) on the table and if arrive to a certain range, send the alert. Schedule it to run as often as needed.
    If you absoluely need the number to be 2M, then I guess trigger is your only option. Though I would not reccomand it as it will fire with each insert. A job even if run often would be a better choice. Make sure you use NOLOCK hint in the SELECT COUNT(*) not to cause lock problems.
    You can create the job from EM (management/sql server agent/jobs).

    Bambola.
  4. gaurav_bindlish New Member

    Well it will impact the performance of insert queries. So try this query for getting the number of rows...
    SELECT rows FROM SYSINDEXES
    WHERE ID = OBJECT_ID(Table_Name)
    AND INDID < 2
    I am not sure if I put the correct column names....

    Also note this may not give you the current no. of rows. So if you want to be dead sure about that use sp_spaceused with updateusage set to true.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  5. bzeebee New Member

    Gaurav
    I tried this
    SELECT rows FROM SYSINDEXES
    WHERE ID = OBJECT_ID(emp_tag)
    AND INDID < 2

    Server: Msg 207, Level 16, State 3, Line 1
    Invalid column name 'emp_tag'

    I tried using Bambola's suggestion but count(*) takes 5-10 mins to come back with the result...

    Please advice
    -B
  6. Luis Martin Moderator

    Sysindexe.rows give you information about number of rows in table and indexes.

    Luis
  7. bzeebee New Member

    emp_tag is the table name ...did I do something wrong.....

    SELECT rows FROM SYSINDEXES
    WHERE ID = OBJECT_ID(emp_tag)
    AND INDID < 2


    I got this error

    Server: Msg 207, Level 16, State 3, Line 1
    Invalid column name 'emp_tag'



  8. bambola New Member

    You are missing the single quotes.

    SELECT rows FROM SYSINDEXES
    WHERE ID = OBJECT_ID('emp_tag')
    AND INDID < 2

    Note that this will give an approximate number as it depends on sp_updatestats. If you can live with it it's a faster choice. I'd still would go with a job though and not with a trigger.

    Bambola.
  9. bzeebee New Member

    Well I tried it with the quotes

    SELECT rows FROM SYSINDEXES
    WHERE ID = OBJECT_ID('emp_tag')
    AND INDID < 2

    and it did not return any rows ....



    So you are suggesting that I put the following in a job

    Select count(*) from emp_tag WITH (NOLOCK)
    if count(*) > 2M then
    use xp_sendmail to email


    run this as a job on a schedule and get the job done?




  10. bambola New Member

    try to update statistics and see if it changes.

    Bambola.
  11. Luis Martin Moderator

    In order to perfomance I suggest the following:

    select si.rows, so.name as TableName, si.id from sysobjects as so
    join sysindexes as si
    on so.id = si.id
    where so.name = 'emp_tag' and si.indid = 0

    Luis Martin
  12. bzeebee New Member

    it gives me an error

    UPDATE STATISTICS emp_tag

    Server: Msg 2706, Level 11, State 6, Line 1
    Table 'emp_tag' does not exist.


    Should I create statistics....on the table

    IF YES , Is this the syntax.....
    CREATE STATISTICS emp_tag
    ON DB..emp_tag
    WITH FULLSCAN, NORECOMPUTE
  13. Luis Martin Moderator

    About error: be sure you are working with rigth database.

    Luis Martin
  14. bambola New Member

    I was thinking about the same thing, Luis. bzeebee - If you are using QA run use database_name to make sure you are using the correct database, or select db_name() to find out where you are <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br /><br />Bambola.
  15. bzeebee New Member

    Its definitely the right db
    dbserver - ARGONE
    dbname - DBONE



    use DBONE
    UPDATE STATISTICS emp_tag

    Server: Msg 2706, Level 11, State 6, Line 2
    Table 'emp_tag' does not exist.


  16. bambola New Member

    So table does not exist on the database...
    can you see it in sysobjects? maybe you did not spell the name correctly...

    Bambola.
  17. bzeebee New Member


    select * from sysobjects where name = 'emp_tag'

    I get one row for this


  18. bambola New Member

    Any chance we are talking about a view?
    what does this return

    select xtype from sysobjects where name = 'emp_tag'

    Bambola.
  19. bzeebee New Member

    xtype = 'U'
    its definitely a table
  20. Luis Martin Moderator

    Try to run a maintenance job to test the table and database.
    I can't understand what is goin on.

    Luis Martin
  21. bzeebee New Member

    works fine.... job ran successfully ...

    queries run fine....

    update stats does not work though......

    i just need to get an email when the count of rows in the table exceeds 2million
  22. Luis Martin Moderator

    Ok. That was the first problem, I write, few post ago, scrip to get the numbers of rows in emp_tag table. After that we go to the statistics problem.

    So, run a job with script and XP_sendmail and see what is goin on.

    Luis Martin
  23. bambola New Member

    What about the owner of the table? maybe it's not dbo....

    Bambola.
  24. bzeebee New Member

    update stats worked when I user user.table_name
    thanks bambola ..I guess i overlooked that one..

    luis I used this query you gave:-

    select si.rows, so.name as TableName, si.id from sysobjects as so
    join sysindexes as si
    on so.id = si.id
    where so.name = 'user.emp_tab' and si.indid = 0


    It does not return any rows inspite of me having done the update stats
  25. bambola New Member

    Try this

    select si.rows, so.name as TableName, si.id
    from sysobjects as so
    join sysindexes as si
    on so.id = si.id
    where so.name = 'syslogd'
    and si.indid < 2
    and so.type = 'u'
    Bambola.

  26. gaurav_bindlish New Member

    Why do we need this join? Why can't we get the information from sysindexes only? Use this and post what is the result?
    SELECT rows FROM SYSINDEXES
    WHERE ID = OBJECT_ID('user.emp_tag')
    AND INDID < 2
    I am assuming user is the owner of the object.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  27. bzeebee New Member

    gaurav,
    the query returned the number of rows..

    so now I need to use xp_sendmail to email me right?

    thanks
  28. gaurav_bindlish New Member

  29. bzeebee New Member

    Thanks Gaurav, Bambola and Luis for your valuable time to help me solve this issue.....

    -B

Share This Page