July 29, 2011 at 1:27 pm
This sql extract (from an update statement) causes our tempdb database to grow to 500GB (as well as takes hours to grow that large)
...
inner join <table2>
on AcYear = cast(Year(LDate) as char(4))
. . .
By changing this join to
. . .
inner join <table2>
on AcYear= Year(LDate)
tempdb hardly grows at all and the process completes in 19 seconds.
Any ideas how to troubleshhot this much appreciated.
Barkingdog
July 29, 2011 at 1:32 pm
Optimize the query and u should be fine. Do not use cast, trimming, substring, order if u dnt need to.
July 29, 2011 at 1:36 pm
Is there a particular reason you need it as a char(4)? If there's a reason providing DDL and the execution plan will help us figure out the best way to make it happen.
July 29, 2011 at 1:38 pm
There's not much to discuss here. Converting to char takes hours while int takes seconds. The server has already answered your question.
The difference is that one uses an index seek while the other scans. The estimates are possibly much more accurate too on the int version (assuming there's a difference).
July 29, 2011 at 1:54 pm
Barkingdog (7/29/2011)
This sql extract (from an update statement) causes our tempdb database to grow to 500GB (as well as takes hours to grow that large)...
inner join <table2>
on AcYear = cast(Year(LDate) as char(4))
. . .
By changing this join to
. . .
inner join <table2>
on AcYear= Year(LDate)
tempdb hardly grows at all and the process completes in 19 seconds.
Any ideas how to troubleshhot this much appreciated.
Let me take the "how to troubleshoot" part of it.
First thing to do is to check execution plan of both version of the query - I bet they are different and something ugly lurs on the first version of it.
In this particular case checking exec plans will be more likely enough - for future reference, next step would be to trace queries and check for wait events which would be superfluos in the particular case.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 29, 2011 at 2:00 pm
Ninja's_RGR'us (7/29/2011)
There's not much to discuss here. Converting to char takes hours while int takes seconds. The server has already answered your question.The difference is that one uses an index seek while the other scans. The estimates are possibly much more accurate too on the int version (assuming there's a difference).
I'm guessing this is where it's going to end up. I just wanted to give the poster a chance to explain in case there's something we aren't aware of.
July 30, 2011 at 7:58 pm
If it takes that amount of space and there more than one statement in a sp then issue a backup statement in between to backup the log provided you got the Database in full mode. Hopefully you have then you can backup the log each x minutes during this hugh sp. 500 GIG would pull us down.!
July 30, 2011 at 10:22 pm
TRACEY-320982 (7/30/2011)
If it takes that amount of space and there more than one statement in a sp then issue a backup statement in between to backup the log provided you got the Database in full mode. Hopefully you have then you can backup the log each x minutes during this hugh sp. 500 GIG would pull us down.!
Ummm... nope... two reasons. The first reason is that this has nothing to do with the log. It's TempDB that's blowing out. Take a look at the original post.
The second reason is that there's no good reason why any query should cause 500 GIG of growth in any file unless it's importing or updating 500 GIG of data. The problem is bad code and it simply needs to be fixed. If the problem actually were in the Log file, then splitting the load on the log file with backups "each x minutes" would be like putting a bandaid on a stab wound... it covers up the problem but you're still bleeding badly. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
July 30, 2011 at 10:23 pm
TRACEY-320982 (7/30/2011)
If it takes that amount of space and there more than one statement in a sp then issue a backup statement in between to backup the log provided you got the Database in full mode. Hopefully you have then you can backup the log each x minutes during this hugh sp. 500 GIG would pull us down.!
err. NO.
Tempdb is the one growing not the logs. This is because of a bad plan and most likely worst estimates in the bad plan.
Your statement works in the case of massive delete where you could do it in smaller batches, this is not the case in that particular problem.
July 31, 2011 at 3:20 pm
Neither int nor char convertions inside the ON sentence will use an index. They are non sargable. Difference can be due to exogenous factors that maybe affect the execution plan. Pls, can you send us the execution plan of both queries (SET SHOWPLAN_TEXT ON), otherwise we only can speculate about the cause.
July 31, 2011 at 8:26 pm
Interesting thoughts from everyone. Unfortunately the developer has modified the query so the original problem no longer exists. In other words I can't provide the needed execution plan. Sorry about that. I guess that concludes this thread.
Barkingdog
July 31, 2011 at 8:43 pm
Barkingdog (7/31/2011)
Interesting thoughts from everyone. Unfortunately the developer has modified the query so the original problem no longer exists. In other words I can't provide the needed execution plan. Sorry about that. I guess that concludes this thread.Barkingdog
Actually, that sounds quite fortuitous ... I love it when problems just vanish.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply