November 3, 2008 at 9:31 pm
Marios Philippopoulos (10/29/2008)
I'm having a performance problem, while migrating a large amount of data from one database to another within the same SQL instance.The main wait type is - by far - WRITELOG on the receiving database (the one accepting the data).
I have set my databases on SIMPLE recovery and moved the log file of the target db to a separate physical drive (RAID 5), in an effort to improve the performance (currently 12 hrs on an otherwise inactive server).
As an extra step I would also like to lessen the checkpoint frequency, to lessen the impact of this operation on the overall performance of my process.
Is is possible to configure the checkpoint frequency at the database level, and what is the config parameter?
Marios,
Just how much is "a large amount of data"?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2008 at 8:33 am
Jeff Moden (11/3/2008)
Marios Philippopoulos (10/29/2008)
I'm having a performance problem, while migrating a large amount of data from one database to another within the same SQL instance.The main wait type is - by far - WRITELOG on the receiving database (the one accepting the data).
I have set my databases on SIMPLE recovery and moved the log file of the target db to a separate physical drive (RAID 5), in an effort to improve the performance (currently 12 hrs on an otherwise inactive server).
As an extra step I would also like to lessen the checkpoint frequency, to lessen the impact of this operation on the overall performance of my process.
Is is possible to configure the checkpoint frequency at the database level, and what is the config parameter?
Marios,
Just how much is "a large amount of data"?
6 GB
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 4, 2008 at 7:20 pm
Then you should probably split it up... doing an update on something that large all at once will probably drive your code past the "tipping point" that every server seems to have. On my home machine, an update of a million rows on a certain table takes about 10 seconds. On 2 million rows, it takes about 20 seconds, as expected. On 3 million rows, it takes close to 2 HOURS... ie... "tipping point" was reached.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2008 at 9:10 pm
Jeff Moden (11/4/2008)
Then you should probably split it up... doing an update on something that large all at once will probably drive your code past the "tipping point" that every server seems to have. On my home machine, an update of a million rows on a certain table takes about 10 seconds. On 2 million rows, it takes about 20 seconds, as expected. On 3 million rows, it takes close to 2 HOURS... ie... "tipping point" was reached.
Pretty much every resource on a server has a utilization point beyond which you experience an exponential decay in that resource's performance. Jeff's example demonstrates how drastic that degredation can be.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 5, 2008 at 7:33 am
TheSQLGuru (11/4/2008)
Jeff Moden (11/4/2008)
Then you should probably split it up... doing an update on something that large all at once will probably drive your code past the "tipping point" that every server seems to have. On my home machine, an update of a million rows on a certain table takes about 10 seconds. On 2 million rows, it takes about 20 seconds, as expected. On 3 million rows, it takes close to 2 HOURS... ie... "tipping point" was reached.Pretty much every resource on a server has a utilization point beyond which you experience an exponential decay in that resource's performance. Jeff's example demonstrates how drastic that degredation can be.
Thanks guys, but it is not that simple. This is a complex data transfer involving many tables and complex business logic that I am not familiar with. The process is part of the release of a new app and is happening this Friday evening, so there is no time to change anything in the script. We are keeping the TEST-SAN LUN in place and will be keeping the LOG file of the target db there, since this shortens the completion time by half.
Good to know about this though for future reference.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 5, 2008 at 6:05 pm
Heh... good luck.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 6, 2008 at 3:54 am
Jeff Moden (11/5/2008)
Heh... good luck.
Thanks! I hate SANs, wish we could run SQL Server on desktops... 😉
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 6, 2008 at 8:16 am
Marios Philippopoulos (11/6/2008)
Jeff Moden (11/5/2008)
Heh... good luck.Thanks! I hate SANs, wish we could run SQL Server on desktops... 😉
It is unfair to simply hate SANs. You should hate improperly configured SANs - and perhaps the people that set them up. :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 6, 2008 at 8:39 am
How is SAN architecture in any way beneficial to SQL Server performance?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 6, 2008 at 8:52 am
Marios Philippopoulos (11/6/2008)
How is SAN architecture in any way beneficial to SQL Server performance?
1) Can you have 1024 drives direct attached effectively? You can do that with a 3Par SAN (think MySpace, which a year ago had 3 of those cabinets).
2) Front-side cache of tens or even hundreds of GB.
3) "Almost instant" backups/restores.
4) multipathing
5) redundancy along the entire IO path.
There are many other reasons - those are just the quickies I can think of in a minute or two and that relate directly to perf.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 6, 2008 at 8:58 am
TheSQLGuru (11/6/2008)
Marios Philippopoulos (11/6/2008)
How is SAN architecture in any way beneficial to SQL Server performance?1) Can you have 1024 drives direct attached effectively? You can do that with a 3Par SAN (think MySpace, which a year ago had 3 of those cabinets).
2) Front-side cache of tens or even hundreds of GB.
3) "Almost instant" backups/restores.
4) multipathing
5) redundancy along the entire IO path.
There are many other reasons - those are just the quickies I can think of in a minute or two and that relate directly to perf.
All these things you mention are great from an SA's perspective for managing and administering the enterprise, but I don't see how they actually help in database/application throughput and performance.
In other words, which of these items would give a SAN system the edge, as opposed to having an isolated system with the exact same specs of hardware/software?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 6, 2008 at 9:21 am
Marios Philippopoulos (11/6/2008)
TheSQLGuru (11/6/2008)
Marios Philippopoulos (11/6/2008)
How is SAN architecture in any way beneficial to SQL Server performance?1) Can you have 1024 drives direct attached effectively? You can do that with a 3Par SAN (think MySpace, which a year ago had 3 of those cabinets).
2) Front-side cache of tens or even hundreds of GB.
3) "Almost instant" backups/restores.
4) multipathing
5) redundancy along the entire IO path.
There are many other reasons - those are just the quickies I can think of in a minute or two and that relate directly to perf.
All these things you mention are great from an SA's perspective for managing and administering the enterprise, but I don't see how they actually help in database/application throughput and performance.
In other words, which of these items would give a SAN system the edge, as opposed to having an isolated system with the exact same specs of hardware/software?
If you can't see how having 1024 drives providing IO would be faster than a few tens (or even hundreds) of drives direct attached would be faster, well we got a big disconnect here! 😀 Same goes for multipathing, the removal of the massive overhead of backups and huge caches.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 6, 2008 at 9:24 am
Would all 1024 drives be 'seen' by the same SQL server instance and shared by all other SQL instances? How does that help with IO throughput? Isn't contention a problem in this scenario?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
November 6, 2008 at 9:30 am
SANs centralize the storage and often contain more drives than you can physically attach to one server. The PC architecture has limitations, and most vendors don't go beyond a certain number of drives. We outgrow a machine at one point that had 10 or 12 drives inside the chassis and another 10 or 12 in an external chassis. I think we were at the physical limit for attaching drives to that server at the time. Moving to a SAN enabled us, with the same "drive" layout, to have many more spindles underneath. We went from a series of R1 pairs (12-14 spindles) to having 30-40 spindles spinning underneath the drives.
November 6, 2008 at 9:38 am
Marios Philippopoulos (11/6/2008)
Would all 1024 drives be 'seen' by the same SQL server instance and shared by all other SQL instances? How does that help with IO throughput? Isn't contention a problem in this scenario?
You can create and expose LUNs in whatever manner is optimal for your environment.
We can go back and forth for hours or even days on this. Unfortunately I don't have that kind of time. If you can push the "I believe" button that SANs CAN provide incredible performance benefits that cannot be achieved by DAS - great. If not, either hire someone to come in and give you some mentoring or go back to "hating SANs". 😉
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply