Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dstj
Starting Member
2 Posts |
 Posted - 2010-08-03 : 14:40:32
|
Hi,My MSSQL 2008 server is on another machine and I have a batch script that needs to do stuff on both machines (backup db, update/alter tables, migrate data, copy some files around...). At some point, is does this:"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\sqlcmd.exe" -i D:\path\updateDB.sql -S <db server ip> -d <database> -b -X -U script_deploy -P <password> When I run that line locally on the DB server, it runs in about 1 minute. When running it from the remote server, it runs in about 14-15 minutes! Any ideas on what could make it run so slow?The 'script_deploy' user is 'db_owner' (I know it's probably too permissive, but I'm trying to get it working here. ;) )Last thing that may or may not be useful, I noticed that the output for the script ran from sqlcmd is much shorter than the output received when ran from Management Studio. It's missing many many "(1 rows affected)", but I can confirm that the script ran OK.Any help is appreciated,Thanks,Dominic. |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2010-08-17 : 05:17:01
|
| right off the bat, it may be due to network traffic, the way your infrastructure is designed, firewalls in between, anti-virus on sql server...moving files around, does that mean you copy files from the remote to the sql server or is it moving files within sql server?--------------------keeping it simple... |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-17 : 06:35:51
|
| "It's missing many many "(1 rows affected)", but I can confirm that the script ran OK."They are usually just "clutter" in output. Although you may want to know if one fails (in which case I suggest puttingIF @@ROWCOUNT <> 1 PRINT 'Action XXX failed' immediately after the SQL command.You can turn the "(1 rows affected) off/on using SET NOCOUNT ON/OFF [slightly confusing double negative!] or (from memory ...) a SWITCH in the command line for sqlcmdAnother reason for slow running on remote server is maybe it a) either has more data to process or b) the indexes are fragmented, or the statistics not updated recently, or both. If you restore a recent backup onto your machine and the script runs locally more quickly then that isn't the problem - a RESTORE will give you the indexes in identical fragmented-shape as the original DB |
|
|
|
dstj
Starting Member
2 Posts |
Posted - 2010-08-17 : 10:48:03
|
| Hi, thanks for your replies.As it turns out, setting "SET NOCOUNT ON" did the trick. It was only those ±1000 "(1 rows affected)" output that caused the query to run extremely slow! I never would have though it would have such an impact over a 100 Mbps network !Dominic. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-17 : 11:13:50
|
| Ah, good, glad you got it sorted. Each one is a separate resultset, so requires a round-trip in the conversation. Maybe there is a little latency in your 100 Mbps network ... it would soon show up on that many "Here's your data" ... "And thank you Sir!" handshakes! |
|
|
|
|
|
|
|
|
sqlcmd script on remote server take 15x longer!?