Viewing 15 posts - 841 through 855 (of 7,613 total)
Focus first on logical I/Os, as that's usually the culprit one way or another.
If you can run the code at will, then add this statement at the start of the...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 8, 2022 at 5:25 pm
What is the data of "yourcolumn" (the original column)?
If it's already datetime, and you just want to strip the time off, then do this instead:
DATEADD(DAY, DATEDIFF(DAY, 0, yourcolumn), 0)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 8, 2022 at 5:19 pm
Just remember that you'll end up with a rather huge transaction log file on Step 3 and it will take 2-3 times longer if you don't achieve "Minimal Logging".
Also...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 8, 2022 at 4:49 pm
so maybe I need to rethink how I have structured my tables. I don't have a binary win_loss in the game data which you will see. I put...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 8, 2022 at 4:48 pm
This might give you some helpful ideas. It also contains minimum sample data to show you how data can be posted. I used a Season table to determine the Season...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 8, 2022 at 3:23 pm
If you're willing to get rid of the PIVOT in favor of a cross-tab approach (which IMHO is preferable anyway), then try this:
SELECT
[Type],...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 7, 2022 at 5:54 pm
Getting carried away with the semicolons, and attempting to use two WITHs for one SELECT:
:WITH cte AS (
SELECT [cntSRVLDAPLOADAVERAGE]
FROM...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 6, 2022 at 9:31 pm
ignore, duplicate post
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 6, 2022 at 2:13 pm
Apologies on the mistake about COMPRESS.
I know you think that 2016 is magic after the first insert. The documentation says that new pages will be minimally logged. ...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 6, 2022 at 2:13 pm
Apologies on the mistake about COMPRESS.
I know you think that 2016 is magic after the first insert. The documentation says that new pages will be minimally logged. I'll...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 6, 2022 at 2:02 pm
Apologies on the mistake about COMPRESS.
I know you think that 2016 is magic after the first insert. The documentation says that new pages will be minimally logged. I'll have...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 6, 2022 at 1:57 pm
Using a loop to break things up into sections will cause the every section except the first to be FULLY LOGGED, which takes as much as 3 times longer...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 5, 2022 at 7:13 pm
Ooh, I accidentally left off one critical pre-step:
(0) Pre-allocate enough log space to handle the INSERTs. How much total log space you'll need depends on whether minimal logging is available...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 5, 2022 at 6:49 pm
If none of your tables have indexes, you should start there, and fast!
First, review the missing index stats to see which column(s) should be used in the clustered index. That...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 4, 2022 at 7:11 am
You could also consider using a brand new filegroup, with at least two files, for the new/replacement table, which will reduce any interference with other filegroups. Then again, you're stuck...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 3, 2022 at 9:03 pm
Viewing 15 posts - 841 through 855 (of 7,613 total)