December 16, 2014 at 10:26 am
Jeff Moden (12/16/2014)
Gosh. I can't believe that the Hierarchies On Steroids #1 article made it to the "Bacon" list on the Tribal Awards. I wouldn't mind winning that one. The prize is a jar of "Bacon Jam" and I like bacon even more than pork chops. 😛
Just chipped in a slice:-D
😎
December 16, 2014 at 10:29 am
Eirikur Eiriksson (12/16/2014)
Jeff Moden (12/16/2014)
Steve Jones - SSC Editor (12/15/2014)
Jeff Moden (12/14/2014)
In the category of learning something new, I just did some testing on the differences between ONLINE and OFFLINE index rebuilds because of a post I saw on LinkedIn. I knew that ONLINE was going to be slower than OFFLINE but I'm totally shocked at the how much of a difference there is on multiple fronts. I was really skeptical of claims like "OFFLINE is 5 times faster" and "OFFLINE also does a better job of defragging", etc, etc, and, so, did a bunch of testing and found out it was all true. I'm truly amazed.I'm also humbled by the fact that I didn't know this before.
Writeup?
I'm not surprised. Given that online essentially starts a transaction, flips two pages, commits, it should be a lot slower.
Writeup? I was thinking the same thing. It would have to be a "what I found" type of article rather than an "SME" type of article because I didn't know a thing about it before I did the testing.
Just my 2 Cents, by experience and if there are sufficient resources (which are needed by the ONLINE anyway), creating a new index and then drop the existing one beats ONLINE, just like The Miami Sharks, Any Given Sunday
😎
Edit: Typo
Wouldn't you also need to rename the index at the end? SQL would need to create a new query plan if there's a differently named index, no?
EDIT to add: Also, that means you have to have enough space for the additional index. While that sounds trivial, lots of places run on the knife's edge when it comes to space.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 16, 2014 at 10:49 am
Stefan Krzywicki (12/16/2014)
Eirikur Eiriksson (12/16/2014)
Jeff Moden (12/16/2014)
Steve Jones - SSC Editor (12/15/2014)
Jeff Moden (12/14/2014)
In the category of learning something new, I just did some testing on the differences between ONLINE and OFFLINE index rebuilds because of a post I saw on LinkedIn. I knew that ONLINE was going to be slower than OFFLINE but I'm totally shocked at the how much of a difference there is on multiple fronts. I was really skeptical of claims like "OFFLINE is 5 times faster" and "OFFLINE also does a better job of defragging", etc, etc, and, so, did a bunch of testing and found out it was all true. I'm truly amazed.I'm also humbled by the fact that I didn't know this before.
Writeup?
I'm not surprised. Given that online essentially starts a transaction, flips two pages, commits, it should be a lot slower.
Writeup? I was thinking the same thing. It would have to be a "what I found" type of article rather than an "SME" type of article because I didn't know a thing about it before I did the testing.
Just my 2 Cents, by experience and if there are sufficient resources (which are needed by the ONLINE anyway), creating a new index and then drop the existing one beats ONLINE, just like The Miami Sharks, Any Given Sunday
😎
Edit: Typo
Wouldn't you also need to rename the index at the end? SQL would need to create a new query plan if there's a differently named index, no?
EDIT to add: Also, that means you have to have enough space for the additional index. While that sounds trivial, lots of places run on the knife's edge when it comes to space.
In fact having a new plan/plans is the desired effect if the old ones are based on outdated statistics/fragmented index. As for the resources, ONLINE will need approximately the same.
😎
December 16, 2014 at 10:53 am
Eirikur Eiriksson (12/16/2014)
Stefan Krzywicki (12/16/2014)
Eirikur Eiriksson (12/16/2014)
Jeff Moden (12/16/2014)
Steve Jones - SSC Editor (12/15/2014)
Jeff Moden (12/14/2014)
In the category of learning something new, I just did some testing on the differences between ONLINE and OFFLINE index rebuilds because of a post I saw on LinkedIn. I knew that ONLINE was going to be slower than OFFLINE but I'm totally shocked at the how much of a difference there is on multiple fronts. I was really skeptical of claims like "OFFLINE is 5 times faster" and "OFFLINE also does a better job of defragging", etc, etc, and, so, did a bunch of testing and found out it was all true. I'm truly amazed.I'm also humbled by the fact that I didn't know this before.
Writeup?
I'm not surprised. Given that online essentially starts a transaction, flips two pages, commits, it should be a lot slower.
Writeup? I was thinking the same thing. It would have to be a "what I found" type of article rather than an "SME" type of article because I didn't know a thing about it before I did the testing.
Just my 2 Cents, by experience and if there are sufficient resources (which are needed by the ONLINE anyway), creating a new index and then drop the existing one beats ONLINE, just like The Miami Sharks, Any Given Sunday
😎
Edit: Typo
Wouldn't you also need to rename the index at the end? SQL would need to create a new query plan if there's a differently named index, no?
EDIT to add: Also, that means you have to have enough space for the additional index. While that sounds trivial, lots of places run on the knife's edge when it comes to space.
In fact having a new plan/plans is the desired effect if the old ones are based on outdated statistics/fragmented index. As for the resources, ONLINE will need approximately the same.
😎
Sometimes, sure, but if the data that comes in is fairly regular, maybe not.
Rebuilding an index ONLINE takes the same amount of space as a second index?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 16, 2014 at 11:34 am
Stefan Krzywicki (12/16/2014)
Eirikur Eiriksson (12/16/2014)
Stefan Krzywicki (12/16/2014)
Eirikur Eiriksson (12/16/2014)
Jeff Moden (12/16/2014)
Steve Jones - SSC Editor (12/15/2014)
Jeff Moden (12/14/2014)
In the category of learning something new, I just did some testing on the differences between ONLINE and OFFLINE index rebuilds because of a post I saw on LinkedIn. I knew that ONLINE was going to be slower than OFFLINE but I'm totally shocked at the how much of a difference there is on multiple fronts. I was really skeptical of claims like "OFFLINE is 5 times faster" and "OFFLINE also does a better job of defragging", etc, etc, and, so, did a bunch of testing and found out it was all true. I'm truly amazed.I'm also humbled by the fact that I didn't know this before.
Writeup?
I'm not surprised. Given that online essentially starts a transaction, flips two pages, commits, it should be a lot slower.
Writeup? I was thinking the same thing. It would have to be a "what I found" type of article rather than an "SME" type of article because I didn't know a thing about it before I did the testing.
Just my 2 Cents, by experience and if there are sufficient resources (which are needed by the ONLINE anyway), creating a new index and then drop the existing one beats ONLINE, just like The Miami Sharks, Any Given Sunday
😎
Edit: Typo
Wouldn't you also need to rename the index at the end? SQL would need to create a new query plan if there's a differently named index, no?
EDIT to add: Also, that means you have to have enough space for the additional index. While that sounds trivial, lots of places run on the knife's edge when it comes to space.
In fact having a new plan/plans is the desired effect if the old ones are based on outdated statistics/fragmented index. As for the resources, ONLINE will need approximately the same.
😎
Sometimes, sure, but if the data that comes in is fairly regular, maybe not.
Rebuilding an index ONLINE takes the same amount of space as a second index?
Rebuilding an index drops an recreates the index
You also can do CREATE INDEX WITH DROP_EXISTING to keep the same index name.
Statistics are redone with any index rebuild or create so any plans will be re-compiled whether you have the same index name or not.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 16, 2014 at 12:05 pm
Jack Corbett (12/16/2014)
Stefan Krzywicki (12/16/2014)
Eirikur Eiriksson (12/16/2014)
Stefan Krzywicki (12/16/2014)
Eirikur Eiriksson (12/16/2014)
Jeff Moden (12/16/2014)
Steve Jones - SSC Editor (12/15/2014)
Jeff Moden (12/14/2014)
In the category of learning something new, I just did some testing on the differences between ONLINE and OFFLINE index rebuilds because of a post I saw on LinkedIn. I knew that ONLINE was going to be slower than OFFLINE but I'm totally shocked at the how much of a difference there is on multiple fronts. I was really skeptical of claims like "OFFLINE is 5 times faster" and "OFFLINE also does a better job of defragging", etc, etc, and, so, did a bunch of testing and found out it was all true. I'm truly amazed.I'm also humbled by the fact that I didn't know this before.
Writeup?
I'm not surprised. Given that online essentially starts a transaction, flips two pages, commits, it should be a lot slower.
Writeup? I was thinking the same thing. It would have to be a "what I found" type of article rather than an "SME" type of article because I didn't know a thing about it before I did the testing.
Just my 2 Cents, by experience and if there are sufficient resources (which are needed by the ONLINE anyway), creating a new index and then drop the existing one beats ONLINE, just like The Miami Sharks, Any Given Sunday
😎
Edit: Typo
Wouldn't you also need to rename the index at the end? SQL would need to create a new query plan if there's a differently named index, no?
EDIT to add: Also, that means you have to have enough space for the additional index. While that sounds trivial, lots of places run on the knife's edge when it comes to space.
In fact having a new plan/plans is the desired effect if the old ones are based on outdated statistics/fragmented index. As for the resources, ONLINE will need approximately the same.
😎
Sometimes, sure, but if the data that comes in is fairly regular, maybe not.
Rebuilding an index ONLINE takes the same amount of space as a second index?
Rebuilding an index drops an recreates the index
You also can do CREATE INDEX WITH DROP_EXISTING to keep the same index name.
Statistics are redone with any index rebuild or create so any plans will be re-compiled whether you have the same index name or not.
Does reorg?
Thanks, that's good to know.
If rebuilding drops and recreates an index, why is creating and dropping it faster? Why isn't REBUILD just automatically CREATE INDEX WITH DROP_EXISTING?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 16, 2014 at 12:13 pm
WHOO! #HappySnoopyDance
I just spent 2 & 1/2 days reworking a package and its supporting tables / stored procedures. Previous time of execution between 8 - 12 hours.
Current execution time ... < 5 minutes.
"Celebration Time, come on!"
December 16, 2014 at 12:43 pm
Brandie Tarvin (12/16/2014)
WHOO! #HappySnoopyDanceI just spent 2 & 1/2 days reworking a package and its supporting tables / stored procedures. Previous time of execution between 8 - 12 hours.
Current execution time ... < 5 minutes.
"Celebration Time, come on!"
Good work, Brandie. I find that doing stuff like that has a tremendous amount of job satisfaction that comes with it.
December 16, 2014 at 2:29 pm
Brandie Tarvin (12/16/2014)
WHOO! #HappySnoopyDanceI just spent 2 & 1/2 days reworking a package and its supporting tables / stored procedures. Previous time of execution between 8 - 12 hours.
Current execution time ... < 5 minutes.
"Celebration Time, come on!"
Great work!
December 16, 2014 at 3:31 pm
Brandie Tarvin (12/16/2014)
WHOO! #HappySnoopyDanceI just spent 2 & 1/2 days reworking a package and its supporting tables / stored procedures. Previous time of execution between 8 - 12 hours.
Current execution time ... < 5 minutes.
"Celebration Time, come on!"
Excellent!
"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
December 16, 2014 at 5:22 pm
Brandie Tarvin (12/16/2014)
WHOO! #HappySnoopyDanceI just spent 2 & 1/2 days reworking a package and its supporting tables / stored procedures. Previous time of execution between 8 - 12 hours.
Current execution time ... < 5 minutes.
"Celebration Time, come on!"
YEEEEEEE-HAAAAAA!!!!! WOOP! WOOP! WOOP! YOU GO GIRL! YOU GO GIRL! BOOM SHAKALAKA! BOOM SHAKALAKA!
--Jeff Moden
Change is inevitable... Change for the better is not.
December 16, 2014 at 5:38 pm
Stefan Krzywicki (12/16/2014)
Jack Corbett (12/16/2014)
Stefan Krzywicki (12/16/2014)
Eirikur Eiriksson (12/16/2014)
Stefan Krzywicki (12/16/2014)
Eirikur Eiriksson (12/16/2014)
Jeff Moden (12/16/2014)
Steve Jones - SSC Editor (12/15/2014)
Jeff Moden (12/14/2014)
In the category of learning something new, I just did some testing on the differences between ONLINE and OFFLINE index rebuilds because of a post I saw on LinkedIn. I knew that ONLINE was going to be slower than OFFLINE but I'm totally shocked at the how much of a difference there is on multiple fronts. I was really skeptical of claims like "OFFLINE is 5 times faster" and "OFFLINE also does a better job of defragging", etc, etc, and, so, did a bunch of testing and found out it was all true. I'm truly amazed.I'm also humbled by the fact that I didn't know this before.
Writeup?
I'm not surprised. Given that online essentially starts a transaction, flips two pages, commits, it should be a lot slower.
Writeup? I was thinking the same thing. It would have to be a "what I found" type of article rather than an "SME" type of article because I didn't know a thing about it before I did the testing.
Just my 2 Cents, by experience and if there are sufficient resources (which are needed by the ONLINE anyway), creating a new index and then drop the existing one beats ONLINE, just like The Miami Sharks, Any Given Sunday
😎
Edit: Typo
Wouldn't you also need to rename the index at the end? SQL would need to create a new query plan if there's a differently named index, no?
EDIT to add: Also, that means you have to have enough space for the additional index. While that sounds trivial, lots of places run on the knife's edge when it comes to space.
In fact having a new plan/plans is the desired effect if the old ones are based on outdated statistics/fragmented index. As for the resources, ONLINE will need approximately the same.
😎
Sometimes, sure, but if the data that comes in is fairly regular, maybe not.
Rebuilding an index ONLINE takes the same amount of space as a second index?
Rebuilding an index drops an recreates the index
You also can do CREATE INDEX WITH DROP_EXISTING to keep the same index name.
Statistics are redone with any index rebuild or create so any plans will be re-compiled whether you have the same index name or not.
Does reorg?
Thanks, that's good to know.
If rebuilding drops and recreates an index, why is creating and dropping it faster? Why isn't REBUILD just automatically CREATE INDEX WITH DROP_EXISTING?
To be clear, for any index over 128 Extents (that's just 8MB, folks), the new index of a REBUILD (not including a DROP_EXISTING) will be built BEFORE the old one is dropped. REORGANIZE does an "in-place" reshuffle.
Don't forget that if you manually drop a clustered index and then rebuild it, ALL of the non-clustered indexes will be rebuilt... twice. The DROP_EXISTING will only cause the NCI's to be rebuilt once.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 16, 2014 at 11:58 pm
Brandie Tarvin (12/16/2014)
WHOO! #HappySnoopyDanceI just spent 2 & 1/2 days reworking a package and its supporting tables / stored procedures. Previous time of execution between 8 - 12 hours.
Current execution time ... < 5 minutes.
"Celebration Time, come on!"
Congrats!
Reminds me of that time I rebuild a crappy SSIS package that took a few hours (if it didn't crash) to an almost fully T-SQL solution (yes Jeff, you read that right) that ran under a minute. Discovered faulty business logic in the process and corrected the test query the business used to test the results. I love moments like these 😀
"Your results are incorrect."
"No no, your test query is incorrect. That's not how you do joins." 😎
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 17, 2014 at 1:31 am
Steve Jones - SSC Editor (12/15/2014)
Given that online essentially starts a transaction, flips two pages, commits, it should be a lot slower.
That's REORGANIZE.
Online rebuild rebuilds the index entirely, creating a new copy of the index, but plays some tricks with the allocation units and write operations to allow the old index to be accessible and to merge any changes that occur during the rebuild into the new index. I'd also expect it to be lower and more resource intensive because it's doing more work than an offline rebuild.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 17, 2014 at 4:42 am
Brandie Tarvin (12/16/2014)
WHOO! #HappySnoopyDanceI just spent 2 & 1/2 days reworking a package and its supporting tables / stored procedures. Previous time of execution between 8 - 12 hours.
Current execution time ... < 5 minutes.
"Celebration Time, come on!"
Sigh. So I presented coworker with my changes and asked for a peer review. Partially to make sure I didn't miss anything, partially to give coworker a learning opportunity since coworker was the one who designed the package. There were 7 files / queries involved and lots of tables. The response I got when I told coworker was: "You redesigned all the queries? But only the one was giving us problems!"
Me, I'm looking at the package and seeing opportunities for cleaning up extra variables, reducing the number of containers in half by moving File System Tasks from one container to another and deleting the excess, stripping out code and variables that were built in the initial development and not used. Also, I'm seeing 7 procs where excrutiating RBAR WHILE loops are slowing down processing. Granted, 6 of those procs were not having issues because they are usually working on files that are pretty small. But that doesn't mean they won't cause a problem in the future. After all, the one problem we did have wasn't a problem when this process was first put into place.
And being fair, this package was the first major ETL process designed by my coworker. Coworker had a huge learning curve not only for SSIS but the 7+ files that were being sent from a different system. Using the code I got from the forums here, he redesigned one file into a separate package that worked effectively. The other package (with the other files) were left alone.
But still... To my mind, since I'm already fixing one part of the package, why shouldn't I proactively fix the rest of it to make sure the other 6 procs and ETL paths do NOT become a problem down the road?
Or am I just being too efficient?
Viewing 15 posts - 46,606 through 46,620 (of 66,749 total)
You must be logged in to reply to this topic. Login to reply