June 4, 2009 at 7:04 am
Chris Morris (6/4/2009)
Lynn Pettis (5/28/2009)
Okay! Any one in Germany near Frankfurt? Maybe has a teen age (around 18 year old) son or daughter (preferably)?And yes I am serious. I have a teenage daughter who paid for her own trip to Germany and is currently not having a very good time like she had hoped.
Lynn, if you can get her to London - flights from Frankfurt to London City used to be quite cheap - I'll send my 18-year old daughter down from Manchester. My flat in Leyton is empty at the mo, they could share and explore London together.
Well, if she had been spending more than just 1 week I'd do that, but she left Frankfurt Tuesday evening and is now safely home us. Thank you for the offer. She goes into the military shortly, and if she finds herself stationed in Europe I'll see if she want's to give that a shot.
Thanks for the offer.
June 4, 2009 at 7:40 am
Jeff Moden (6/4/2009)
Here's the reason why I use Index(0) instead of Index(1). From BOL:
Cheers Jeff - it was your motivation in including the hint I was after really (though the subsequent IAM discussion was excellent).
/P
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 4, 2009 at 8:30 am
I've only been here a short time now, but some of the questions I see are just painful to read.
From the answers I've seen, there are a lot of very professional people willing to help.
I think what gets me the most, on this board and others, is posters that don't care/don't know but just want a answer/fix. That really goes against all the good I see in computing.
June 4, 2009 at 8:41 am
Here we go again with truncating and shrinking logs, especially when people don't understand what it is they are doing.
June 4, 2009 at 8:50 am
Lynn Pettis (6/4/2009)
Here we go again with truncating and shrinking logs, especially when people don't understand what it is they are doing.
hmmmm ...... if you were shrinking it to 40 GB why do you now want to restore it to 100 GB? Was there a light bulb that suddenly appeared above your head?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 4, 2009 at 8:54 am
Lynn Pettis (6/4/2009)
Here we go again with truncating and shrinking logs, especially when people don't understand what it is they are doing.
Do you understand what he's done? Cause I'm not sure I do
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
June 4, 2009 at 8:57 am
GilaMonster (6/4/2009)
Lynn Pettis (6/4/2009)
Here we go again with truncating and shrinking logs, especially when people don't understand what it is they are doing.Do you understand what he's done? Cause I'm not sure I do
I guessing that in trying to shrink it down to 40 GB he had a typo in the command and shrank it down to 4 MB instead of 40 GB.
It's just a guess.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 4, 2009 at 9:07 am
Alvin Ramard (6/4/2009)
GilaMonster (6/4/2009)
Lynn Pettis (6/4/2009)
Here we go again with truncating and shrinking logs, especially when people don't understand what it is they are doing.Do you understand what he's done? Cause I'm not sure I do
I guessing that in trying to shrink it down to 40 GB he had a typo in the command and shrank it down to 4 MB instead of 40 GB.
It's just a guess.
Hmmm, guess it may be but logical it seems from what OP posted.
June 4, 2009 at 10:04 am
Lynn Pettis (6/4/2009)
Any one else have an idea regarding this one?Apparently the full backups work but can't run a transaction log backup. I'm at a loss at this point.
Lynn, I'm no backup expert, but could it be soemthing like the backup done by the application, if I understand the OP correctly, is not a "native" SQL backup but the transaction log backup is?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 4, 2009 at 10:13 am
Alvin Ramard (6/4/2009)
Lynn Pettis (6/4/2009)
Any one else have an idea regarding this one?Apparently the full backups work but can't run a transaction log backup. I'm at a loss at this point.
Lynn, I'm no backup expert, but could it be soemthing like the backup done by the application, if I understand the OP correctly, is not a "native" SQL backup but the transaction log backup is?
Don't think so as the OP indicated that the backup was being recorded in the table msdb.dbo.backupset.
I think David Webb may have hit on a possibility, however. He just asked the OP if the application may be doing an explicit truncate on the log after the backup. If that is the case that would explain why a transaction log backup can't be run. That was something I hadn't thought of asking about.
June 4, 2009 at 10:27 am
When are companies going to learn that they need to hire qualified DBAs???
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 4, 2009 at 10:29 am
Lynn Pettis (6/4/2009)
Alvin Ramard (6/4/2009)
Lynn Pettis (6/4/2009)
Any one else have an idea regarding this one?Apparently the full backups work but can't run a transaction log backup. I'm at a loss at this point.
Lynn, I'm no backup expert, but could it be soemthing like the backup done by the application, if I understand the OP correctly, is not a "native" SQL backup but the transaction log backup is?
Don't think so as the OP indicated that the backup was being recorded in the table msdb.dbo.backupset.
I think David Webb may have hit on a possibility, however. He just asked the OP if the application may be doing an explicit truncate on the log after the backup. If that is the case that would explain why a transaction log backup can't be run. That was something I hadn't thought of asking about.
Yep, looks like he was right.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
June 4, 2009 at 10:34 am
Jeff Moden (6/3/2009)
The following code creates a million row multiple account checkbook, does the "Fire Hose" cursor running total thing 5 times and follows that with the "Pseudo Cursor" method 5 times.
OK, that's a pretty good case. I'll have to revisit my own example and try to figure out why I'm not seeing such an extreme difference. By the way, when I run your example on different servers, the perceived difference changes dramatically; on one brand new server, with loads of RAM and CPU power, the cursor is only twice as slow. On my desktop machine, the UPDATE runs in 12 seconds and I killed the cursor after 10 minutes.
I was able to improve the cursor in your example by doing a few things, like making sure to include the Amount column in the clustered index on CheckBook, and creating the index on the temp table first, rather than afterward (the data is inserted in that order anyway, so fragmentation shouldn't be an issue). I expected that wrapping the whole thing in a transaction would further speed it up, but I was surprised to see that it made no real difference.
With regard to the UPDATE, something you might want to play with instead of relying on the hints is using a CTE and forcing the data to be ordered. This will not actually cause a sort to occur unless you don't have a supporting index. And I did test to make sure that if you do not have a supporting index, a sort WILL in fact occur. This should give you a better guarantee of ordered processing than the INDEX and TABLOCKX hints.
;WITH p
AS
(
SELECT TOP(2147483647)
*
FROM #pcWork
ORDER BY
AccountNumber,
TransactionDate,
CheckBookId
)
UPDATE p
SET @PrevBalance = AccountBalance = CASE
WHEN AccountNumber = @PrevAccount
THEN @PrevBalance + Amount
ELSE Amount
END,
@PrevAccount = AccountNumber
OPTION (MAXDOP 1)
--
Adam Machanic
whoisactive
June 4, 2009 at 11:04 am
Viewing 15 posts - 5,326 through 5,340 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply