Viewing 15 posts - 916 through 930 (of 7,613 total)
CREATE UNIQUE INDEX IX_ID2 ON #IndexTEST(ID2) INCLUDE (ID);creates unique constraint, exactly as you requested.
Second statement is not needed at all.
Technically that's just a unique index,...
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 11, 2022 at 3:18 pm
The indexes will come along. Since they are part of the db, they get restored with 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".
April 8, 2022 at 9:17 pm
For more specific tuning, yes, we must see the query plan and the DDL for the table(s) involved, including all indexes.
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 8, 2022 at 5:51 pm
Cost Threshold for Parallelism = 5
Max Degree of Parallelism = 12
Are these values correct?
Definitely not for the first one. Bump that up to at least 40:
EXEC sys.sp_configure...
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 8, 2022 at 5:50 pm
Note the "<" in the comparison; that is required. This handles due_date correctly whether it is a datetime data type or a date.
;WITH cte_get_max_due_date 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".
April 8, 2022 at 5:44 pm
--Removed, double 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".
April 8, 2022 at 5:26 pm
Cost Threshold for Parallelism = 5
Max Degree of Parallelism = 12
Are these values correct?
Definitely not for the first one. Bump that up to at least 40:
EXEC sys.sp_configure 'cost threshold...
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 8, 2022 at 5:25 pm
I have extensive experience in health care as well. Experience directly in that field is in fact very useful.
Obviously industry terms may be different and have different connotations in each...
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 8, 2022 at 5:18 pm
Look at table msdb.dbo.restorehistory.
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, 2022 at 11:23 pm
You can clean up the existing code some, but the condition:
Prop_TYPE NOT LIKE '%Rond%'
is still going to require a table scan or a covering index scan (if a covering index...
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, 2022 at 11:21 pm
I don't believe SQL will use an existing index for a constraint, even if the index perfectly matches the constraint to be added.
In your situation, the index and the constraint...
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, 2022 at 8:25 pm
(1) Did you set on the "optimize for ad-hoc workloads" SQL Server setting?
(2) What is the "threshold for parallelism setting"? In particular, did you leave it at the default of...
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, 2022 at 5:35 pm
I just wanted to add in that I am not a big fan of the "SELECT * INTO" approach to table creation. It recreates the table, but only 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".
April 5, 2022 at 5:22 pm
Typically I/O causes the most issues.
Therefore, another first quick thing to do is to look at the queries with the TOP (10) Avg I/O. You can find these from SSMS.
Right-click...
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, 2022 at 6:07 pm
Sorry, I wasn't being clear before about how to use transactions here.
If there are no BEGIN TRAN / COMMIT TRAN, then every modification (DELETE / INSERT / UPDATE) will be...
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, 2022 at 3:26 pm
Viewing 15 posts - 916 through 930 (of 7,613 total)