June 17, 2009 at 1:01 am
I tried to export data from SQL Server 2008 database to excel spreadsheet (by using a query on source part) by using Import/Export wizard found in tasks/import export wizard in our production database, at the same time, some claims came about getting timeout from other apps, does import/export wizard puts exclusive lock on source table? Or should i search for other reasons?
June 17, 2009 at 7:56 am
Assuming the source tables are not being updated, no, it should only be putting shared locks on them for reading. It might cause excessive I/O, memory, or CPU use while it exports the data, depending on the size & number of the tables. Maybe that's the cause of the timeouts.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 17, 2009 at 8:29 am
Thanks then i have to focus on server performance then, for your knowledge is there a performance difference between exporting data and running the same query on SSMS?
Thanks,
June 17, 2009 at 8:38 am
In a perfect world, no. In reality, yeah. But specifically what would be the difference? I'm not sure. I haven't found the export wizard to create the most efficient ETL processes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 17, 2009 at 8:58 am
What can I use instead of Import/Export wizard? Mostly excel outputs are wanted from me,
a query result
June 17, 2009 at 9:24 am
Import/Export uses SSIS. You can go to SSIS directly. It's more work, but you can make a more efficient DTL.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply