5

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.

Improve this question
5
  • In my experience, high CPU means index issues. How big is the script? Can you post part of it?
    – datagod
    Commented Sep 28, 2011 at 4:41
  • The script file has a size of 156 MB. Unfortunately, I cannot post it, since it would expose my client's data. Finally, the script ended after 5 1/2 hours with the following error message: Sqlcmd: Error: Syntax error at line 619987 in file 'SyncMammouthDev.sql'. However, this may be due to the fact that I generated the script on SQL Server Express 2008 RC and I was trying to run it on SQL Server 2005.
    – Jean-François Beauchamp
    Commented Sep 28, 2011 at 4:52
  • Can you elaborate on "index issues"?
    – Jean-François Beauchamp
    Commented Sep 28, 2011 at 4:54
  • 1
    Is it possible to break the script down into smaller parts? I assume if the script is as big as you say it is, you are doing some kind of data import. Can it be split into transactional blocks?
    – Miles D
    Commented Sep 28, 2011 at 7:20
  • @MilesD I guess it could, but it would be a lot of work, I found another solution. See below...
    – Jean-François Beauchamp
    Commented Sep 28, 2011 at 15:46

1 Answer 1

Reset to default
1

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?
Improve this answer
5
  • You were right. It is a script to load data in the database, and although there are a few 'GO' at the beginning of the script where triggers are being disabled and constraints are dropped, there is only one GO command for the big bunch of INSERT statements.
    – Jean-François Beauchamp
    Commented Sep 28, 2011 at 15:32
  • Glad to be of service. I'd still suggest that instead of doing a big SQL file (even with small batches), you'd get huge improvements by going the BCP import route.
    – Marian
    Commented Sep 28, 2011 at 15:35
  • Can BCP handle an SQL script? Because I don't think the tool I am using can generate something else than SQL scripts. Here how I solved my problem: The file was generated by xSQL Data Compare, and it would have been quite time consuming to edit it to add more GO statements. However, my copy of xSQL Data Compare is able to run the script on my local instance of SQL Server Express, so I backed up the remote database I was working on and restored it locally. I was then able to run the script with the tool on my local machine and to bring back that database to the remote server.
    – Jean-François Beauchamp
    Commented Sep 28, 2011 at 15:38
  • 1
    You can also use SSIS or the import / export wizard (which uses ssis) to transfer the data from your local copy to the remote copy. See if your tool has a batch size option as well. Furthermore, data compare might not be the best approach for this unless it's a one time deal.
    – Eric Humphrey - lotsahelp
    Commented Sep 28, 2011 at 16:01
  • @Jean-FrançoisBeauchamp: nope, BCP is a command line tool used to transfer tables' data in files and then back in tables. It handles binary files, not sql files. Instead of generating comparison scripts with a diff tool, you could build a more general script that takes a tables list, goes through the differences between each table in both dbs, builds staging tables for the differences, use Bcp to generate binary files.. than use those files in the second environment. This is a bit of a hassle at the beginning, to build the flow, but it will work much faster than generating huge SQL files.
    – Marian
    Commented Sep 29, 2011 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.