Post reply

Number of Insert and\or update in a table.

  • August 23, 2010 at 7:21 pm

    #141552

    Hi Expert,

    Is there anyway we can find number of insert and\or update happening on DB table during a day.

    Thanks in advance,

  • August 23, 2010 at 11:09 pm

    #1210786

    Which edition u r using??

  • August 23, 2010 at 11:22 pm

    #1210790

    An insert/update trigger should work.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 24, 2010 at 5:15 pm

    #1211375

    Hi Rateesh,

    I am on SQL 2005 Ent edition with SP3.

    Hi Gail,

    I am not sure weather trigger is good idea or not as my aim is to find number of updates on a table so that I can make sure index I am going to create on table is not going to create any negative impact on performance.

  • August 24, 2010 at 11:38 pm

    #1211458

    First of all, what kind of and why you want to create index to track update/insert operation ?

    and to answer your query , there is an dmv might help you select last_user_update from sys.dm_db_index_usage_stats

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • August 24, 2010 at 11:39 pm

    #1211459

    mdp11 (8/24/2010)

    so that I can make sure index I am going to create on table is not going to create any negative impact on performance.

    Number of updates is not going to tell you that. Create the index in a dev/test environment and test it out, see what improvement it gives, what (if any) negative impact.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 25, 2010 at 12:28 am

    #1211469

    Thanks Bhuvnesh and Gila,

    I know that but here my idea is to make sure the table doesn't have frequent insert \update before I create an Index. If table data is updated more frequently then that may create negative impact of creating index.

    Anyway as per gail advise I will try new index on test machine first before I go to prod.

    Thanks,

  • August 26, 2010 at 6:50 am

    #1212253

    I'm with the guy/gal that recommended using the index usage stats DMV, at least for inserts. And if you have a column that is "always" updated you can get the updates count as well.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • August 26, 2010 at 9:37 am

    #1212383

    TheSQLGuru (8/26/2010)

    I'm with the guy/gal that recommended using the index usage stats DMV, at least for inserts. And if you have a column that is "always" updated you can get the updates count as well.

    GUY ....i just updated my AVATOR pic 🙂

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • August 26, 2010 at 10:13 am

    #1212400

    Hi MDP,

    You can do that using Performance Dashboard Reports. Just note the number of inserts at the start of the Day and then subtract this value from the value that you get at the end of the Day.

    Performance Dashboard Reports using DMVSand and the values obtained are cummulative and since the SQL Server Restart.

    http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

    http://www.mssqltips.com/tip.asp?tip=1553

    http://www.sql-server-performance.com/articles/per/bm_performance_dashboard_2005_p1.aspx

    Thank You,

    Best Regards,

    SQLBuddy

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply