How to check Bulk Insert time

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!

Answer 1

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.

Answer 2

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!

Answer 3

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?

Answer 4

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!

Answer 5

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

 

Answer 6

User-1971168174 posted

Thanks!

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

Answer 7

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

Answer 8

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.

Answer 9

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