Viewing 15 posts - 1,741 through 1,755 (of 7,619 total)
I still have to wonder on the appropriate use of NOLOCK. Even if the data does not change in a table, is NOLOCK that much better...
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".
April 7, 2021 at 2:15 pm
For these specific queries alone -- since we know nothing else about this table -- you should change the clustering key on the table shown.
CREATE TABLE dbo.CustTravelReqLocPlan (
...
CONSTRAINT [PK__CustTrav__401DC63546C0E5F6] PRIMARY...
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".
April 6, 2021 at 3:38 pm
It seems there must be some error occurring when the script it exec'd. Add some msgs to (1) verify if this is true and (2) if so, show the return...
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".
April 6, 2021 at 2:18 pm
(1) CROSS JOIN is not necessarily bad for performance. If the CJ'd table is single row, it's definitely fine.
(2) The %s are OK here, since there's no easier way to...
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".
April 5, 2021 at 2:25 pm
Yes, both can make a big difference in OLTP as well. The key is that it reduces I/O because fewer pages must be written to disk.
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".
April 5, 2021 at 2:20 pm
Vastly better to instead create a clustered index before you load the temp table.
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".
April 5, 2021 at 7:03 am
I still have to wonder on the appropriate use of NOLOCK. Even if the data does not change in a table, is NOLOCK that much better than a standard...
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".
April 4, 2021 at 7:45 pm
Very similar, but using an OUTER APPLY because you said you used a LEFT JOIN. OUTER APPLY is like a "LEFT APPLY" and CROSS APPLY is like an "INNER APPLY".
Don't...
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".
April 2, 2021 at 3:59 pm
Sorry, I just have to ask: why would logical file name(s_ need to be preserved? Are they hard-coded in some existing statements? That seems dangerous, what if files are added...
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".
April 1, 2021 at 2:19 pm
I would have thought you could just rename the logical names to end up with the same name.
Either: rename the original logical file name before you add the new file...
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".
April 1, 2021 at 1:33 pm
Hmm, maybe add another file to the filegroup in the new location.
Then remove the existing file from the filegroup; since the file's empty, you should be able to remove it.
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".
March 31, 2021 at 8:01 pm
Columnstore has its own quirks. For example, every time you SELECT data from the table you'll read roughly 1M rows. If your processing (almost) always does that anyway, such as...
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".
March 31, 2021 at 2:40 pm
I did give what I think is the reason for the recommendation: to be able to get back to the exact db image is something happens after removing TDE.
I get...
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".
March 30, 2021 at 8:28 pm
columnstore is an alternative to rowstore with page compression.
Typically you get even better compression with columnstore.
As to which is best, that depends on the specific situation with that table.
I'd say...
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".
March 30, 2021 at 8:26 pm
Do you really care that much? That is, isn't it easier just to take the log backup.
I think I know the reason: it would be the only way to get...
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".
March 30, 2021 at 7:45 pm
Viewing 15 posts - 1,741 through 1,755 (of 7,619 total)