Sql-server – sqlcmd to run large SQL file causes high CPU usage

sql-server-2005sqlcmd

I am using sqlcmd.exe to run a large SQL script, and the server CPU usage has been at 100% for the past 5 hours. The SQL script file has a size of 156 MB. I am running the script on SQL Server 2005. This does not look normal to me.

Using SQL Server Management Studio to run the script is not an option, since it won't run very large files.

Note: I know the SQL script is good, since I was able to run it on my local machine with another tool (xSQL data). Unfortunately, I don't have access to that tool on the server.

Best Answer

If the sql script is 156 MB I suppose that you're doing data load using a single statement for each row.

Is that everything in one big batch (all statements one after the other without GO)? If yes, then SQL Server will try to parse that file all at once... on a file that big you're killing the CPU.

Suggestions:

can you split that file in more than one batch? so the parsing will be done batch after batch..
can you load the data from bcp files instead of sql file?

Related Solutions

Sql-server – How to find the detail cpu high process name which occupying most resource in the SQL Server database

You can look at sys.dm_exec_query_stats to find queries which are causing the highest total_worker_time.

SELECT      TOP 20
            ST.text,
            QS.*
FROM        sys.dm_exec_query_stats          QS
CROSS APPLY sys.dm_exec_sql_text(sql_handle) ST
ORDER BY    total_worker_time

Sql-server – high ‘total_worker_time’ for stored proc using OPENQUERY in SQL Server 2005

In sys.dm_exec_query_stats total_worker_time is:

Total amount of CPU time, in microseconds, that was consumed by executions of this plan since it was compiled.

So it is the total CPU time for all executions of the cached plan. The execution_count column gives the number of times the complete plan has executed, and there is also information about the lowest and highest values recorded. The figures from the DMV are in microseconds, whereas STATISTICS TIME output shows milliseconds. Finally, the MAXDOP hint only applies to the portion of the query that runs locally.

I noticed you are running SQL Server 2005 SP2. There were all sorts of timing issues with that (now unsupported) release, for some detail, see:

SQL Server 2005 SP2 will introduce new messages to the error log related to timing activities.

Consider moving to 2005 SP4.

Best Answer

Related Solutions

Sql-server – How to find the detail cpu high process name which occupying most resource in the SQL Server database

Sql-server – high ‘total_worker_time’ for stored proc using OPENQUERY in SQL Server 2005

Related Question