SQL Server Performance Forum – Threads Archive

Insert is running slow for 8 million records

Hi Folks, Insert on my server is running slow 50 record persec and get hanged after insering about 2 million records,I have tried the same insert in my desktop it is pretty fast approx 200 records per sec. Here is the config of server<img src=’/community/emoticons/emotion-2.gif’ alt=’:d’ />ual core processor ,Task manager is showing 8 cpu,4GB RAM,SQL Standard edn ,page file(virtual memory )is enabled,disk RAID 1+0 max deg parallism is enabled to 2,AWE enabled,no application is there or connected to this server DB:No non clustered index in that table,db in simple recovery my personal desktop<img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />4,1GB RAM,SATA disk 60 GB,Standard edn ,page file(virtual memory )is enabled Any ideas??

Hover,
What about performance counters on server?
– disk i/o

Does the table have any insert triggers? FK constraints? If you carefully prepare the data, and the database is not in use by other users, you might temporarily disable the triggers and drop FK constraints to speed up insertion, then re-enable triggers and constraints after insertion.

THis table dont have any trigger and FK constraint…neither any indexing Disk I/O is 1023 Here is the list of imp counters and latest values Physical Disk: _Total Avg disk queue length 0.029 Current disk queue length 0 Disk read/sec 0.000 Disk writes/sec 8.054 Network Interface HP NC373i Multifunction Gigabit Server Adapter Bytes sent/sec28444.989 Bytes total/sec 30621.820 Processor _Total %Processor time23.049 Interrupts/sec 578.629 Sql server:Buffer Manager Buffer cache hit ratio: 99.86 SQL server<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />QL Statistics Batch Requests/sec 0.000 SQL Server<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />atabases_Total Transactions/Sec 7.041

Try running sys.dm_os_wait_stats and sys.dm_exec_sessions to capture the sessions.
http://blogs.msdn.com/sqlcat/archive/2005/11/30/498415.aspx fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

disk i/o looks ok,
Logfiles and datafiles on same disk ?
Is there somthing else running on same machine?
What about indexes? Dropping them and recreate after load might speed things up?
What about initial db filesize big enough?

Log files and data files are seperated on different disks
No idexes are there
No othere application are running on this machine
Data file is Initially 900 MB,Autogrowth is set to 1 MB

avoid the autogrow !
If you need the autogrow, grow with a reasonable size (e.g. 100Mb)
each grow operations needs time and physical IO !! (SLOW)

Rather set it to a size that is 30% more than current available size.
http://sqlserver-qa.net/blogs/tools…autogrowth-setting-is-it-for-good-or-bad.aspx fyi. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Hover Log files and data files are seperated on different disks No idexes are there No othere application are running on this machine Data file is Initially 900 MB,Autogrowth is set to 1 MB <hr height="1" noshade id="quote"></blockquote id="quote"> May be it is auto growing for each 1-10 rows insert [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Manually increase the size, if you don’t want to stop the load process… or Stop the load process then manually increase the size to considerable amount based on your data load and start the load and make sure you have enough space in log too… MohammedU. Moderator SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>