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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply