locked
How to check Bulk Insert time RRS feed

  • Question

  • User-1971168174 posted

    Hi Support,

    I am doing bulk insert for 100k records and need to check how much time it takes to insert.

    Please let me know what is the best way to check how many mili seconds it took .

    Thanks!

    Thursday, May 14, 2020 3:33 PM

All replies

  • User475983607 posted

    I am doing bulk insert for 100k records and need to check how much time it takes to insert.

    Please let me know what is the best way to check how many mili seconds it took .

    Set a timer or get the current DateTime at the start and end of the bulk load.

    https://stackoverflow.com/questions/11675077/measure-the-time-it-takes-to-execute-a-t-sql-query

    Without source code it's difficult to provide a solution.

    Thursday, May 14, 2020 3:39 PM
  • User-1971168174 posted

    Thanks Mgebhard!

    Is there some query on sql level to check for each bulk load ..something like below..where we can make out time taken for all  bulk insert in a table.

    select *
    from sys.fn_dblog(NULL,NULL)
    where [Transaction ID] = '0000:0000045b' 
    and Operation = 'LOP_BEGIN_XACT';

    Thanks!

    Thursday, May 14, 2020 3:48 PM
  • User475983607 posted

    I'm not following your logic.  If want to take advantage of the transaction log then you need to persist the transaction so you can look it up later.  Why can't you save the start and end times of the bulk load?

    Thursday, May 14, 2020 4:08 PM
  • User-1971168174 posted

    Hi Mgebhard,

    Can you suggest ways to find out monitoring Sql db performance at regular interval? Lot of times users keep complaining that db is very slow..sometimes queries will run fast and sometimes very slow.

    One way i though is doing bulk insert  and bulk reading and check the time taken to see whats the duration at particular time.

    Thanks!

    Thursday, May 14, 2020 4:16 PM
  • User475983607 posted

    Can you suggest ways to find out monitoring Sql db performance at regular interval? Lot of times users keep complaining that db is very slow..sometimes queries will run fast and sometimes very slow.

    SSMS has SQL Server Profiler and Activity Monitor which you can use to figure out if there is a problem.

    https://docs.microsoft.com/en-us/sql/tools/sql-server-profiler/sql-server-profiler?view=sql-server-ver15

    https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/open-activity-monitor-sql-server-management-studio?view=sql-server-ver15

     

    Thursday, May 14, 2020 4:49 PM
  • User-1971168174 posted

    Thanks!

    Anything can be fetched through query so  that i can use it on dashboard? for monitoring database.

    Friday, May 15, 2020 2:57 AM
  • User288213138 posted

    Hi neerajkumarmodi,

    How to check Bulk Insert time

    As Mgebhard said, if you want to check Bulk Insert time, you only need to query the start time and end time.

    Anything can be fetched through query so  that i can use it on dashboard? for monitoring database.

    Can you explain in detail what function you want SQL Server Profiler and Activity Monitor to achieve?  and what is dashboard?

    Best regards,

    Sam

    Friday, May 15, 2020 6:19 AM
  • User-1971168174 posted

    Thanks Samwu!

    I am looking to query things which can help me finding why queries sometimes are running slow on user side.

    Monday, May 18, 2020 8:54 AM
  • User288213138 posted

    Hi neerajkumarmodi,

    I am looking to query things which can help me finding why queries sometimes are running slow on user side.

    There are many reasons that cause the query to suddenly slow down.

    With SQL Monitor, you can study the query history, get the "before" and "after" plans, diagnose the cause.

    More information about the use of SQL Monitor you can refer to this link:

    https://www.red-gate.com/hub/product-learning/sql-monitor/why-is-my-query-suddenly-slow

    Hope this can help you.

    Best regards,

    Sam

    Tuesday, May 19, 2020 2:25 AM