Viewing 15 posts - 1,156 through 1,170 (of 7,619 total)
But when Jonathon added both columns the WHERE clause, it might have taken care of it.
c.CustomerNumber = t.CustomerNumber AND c.Customer = t.Customer
Jonathon, your second query brought the spool back. ...
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".
November 5, 2021 at 4:29 pm
Agreed, the NOLOCK was already on my radar.
Removing NOLOCK would only slow down the process, never speed it up. I'm not saying you should or shouldn't use it here...
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".
November 5, 2021 at 3:16 pm
I don't understand what you are trying to do or your data. But I think the part:
Bank = (Select Bank From Customer Where Customer=t.Customer)is redundant.
I'm not...
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".
November 5, 2021 at 3:14 pm
What indexes do you have on that table?
Looks like an index on ( MessageType, NewValue, KeyValue, RecordDate ) INCLUDE ( CreateDate, OldValue )
might be useful for this query.
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".
November 5, 2021 at 2:54 pm
You're presuming an awful lot. Why would you want "at least 2 files per filegroup" for a single table?
To spread the I/O out, the typical reason for multiple files...
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".
November 4, 2021 at 6:27 pm
I still have to think about this. We have hundreds of dbs. Say anywhere from 3 to 24 large tables in each db, a separate fg for each. Presumably you'd...
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".
November 4, 2021 at 4:12 pm
{Thousands of recompiles a day} vs {thousands of extra table scans a day} is an easy choice. I'd have to see actual damage from the recompiles to recommend...
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".
November 4, 2021 at 3:49 pm
Of course that forces the entire table to be rewritten every time. In a typical rebuild, often large parts of the table do not need rewritten because they...
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".
November 4, 2021 at 2:58 pm
I'll have to think about that method some more, don't have time right now.
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".
November 4, 2021 at 2:47 pm
{Thousands of recompiles a day} vs {thousands of extra table scans a day} is an easy choice. I'd have to see actual damage from the recompiles to recommend differently. Partly...
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".
November 4, 2021 at 2:45 pm
So OP does in their next post.
OK. I might still stick with recompile on this. It's returning only a single int column and the controlling columns (in the WHERE clause)...
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".
November 3, 2021 at 9:49 pm
Just one more thing... The example above spans 29 months. Is there a good way to separate it out to place three columns for each month within the date...
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".
November 3, 2021 at 8:16 pm
So how do you know this will be run "thousands of times a day"? I wouldn't expect it to called anywhere even close to that many times.
I did not add...
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".
November 3, 2021 at 8:06 pm
Which version of SQL Server? If you're in a recent enough version, you can use function TRY_CAST to see if the value is a valid int or not.
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".
November 3, 2021 at 8:02 pm
exec sp_executesql @sql = @sql, N'@date datetime', @date
Are you sure you need a specific datetime and not a range value such as the current day?
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".
November 3, 2021 at 8:01 pm
Viewing 15 posts - 1,156 through 1,170 (of 7,619 total)