Viewing 15 posts - 991 through 1,005 (of 7,619 total)
You can explicitly set DB_CHAINING on (ALTER DATABASE ... SET DB_CHAINING ...) for selected dbs if you prefer. You might want to consider that in this specific case.
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".
February 17, 2022 at 9:29 pm
You provided no sample data, so no way to test. But something like this should help. If table "accession_2" does not contain duplicate "accession_number"s, you should be able to remove...
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".
February 16, 2022 at 10:54 pm
So you mean to move data to a new filegroup just that one table?
No, all the (very) large tables. Until the original filegroup is small.
Because it's LOB data, isn't it's...
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".
February 16, 2022 at 7:26 pm
SQL could have to rewrite roughly 3.4TB of data (the used space) to shrink that file -- that will take a long time.
Are you using data compression on the tables? ...
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".
February 16, 2022 at 6:28 pm
(1) force large value types of out row (as above). Note that for an existing table, you need to run an UPDATE to move the actual column values off page. ...
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".
February 15, 2022 at 4:13 pm
For efficiency, create a clustering key on the table on ( header1, header2 ). Since SQL Server so strongly prefers unique indexes, if those values aren't unique by themselves, 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".
February 15, 2022 at 5:13 am
Would help greatly to see the DDL for the tables, including all indexes.
The query could also be rewritten to help performance, but without sample data there's no way to test...
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".
February 14, 2022 at 8:24 pm
Ouch.
Based on my past experience, you'll continue to have trouble until you move all non-SQL-Server apps to different boxes. Not just CPU could be an issue, you could easily run...
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".
February 14, 2022 at 8:02 pm
My guess would be that the log file needed to grow dynamically, and that is very slow. That partly would depend on the specific UPDATEs that were done and how...
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".
February 14, 2022 at 7:27 pm
You didn't provide any sample data so I can't test this at all:
SELECT TP.Location,
TE.Name,
...
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".
February 11, 2022 at 2:57 pm
Your second table, CTR_PanelMembers, is defined as a heap.
Instead, it should have a clustered index on:
( CTRseqNo, RecordID )
I don't see anything wrong with the FK definition in the second...
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".
February 10, 2022 at 2:17 pm
I assumed that the only value after 'Receiver' was the id. If more string can follow, the code below will need changed. I was not able to test the code...
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".
February 9, 2022 at 4:42 pm
No, adding a COMMIT will not speed it up, the associated explicit BEGIN TRAN will likely slow it down slightly. SQL will (auto)commit after the statement finishes anyway.
Be sure you...
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".
February 8, 2022 at 10:52 pm
You have an explicit transaction called from within BEGIN TRY/END TRY yet the ROLLBACK (which presumably corresponds to the transaction) is in the CATCH block. Checking XACT_STATE in 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".
February 7, 2022 at 4:43 pm
You need to step back and do proper data modeling on all your entities. That is the most important step in proper design.
Then, as you convert the entities to physical...
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".
February 7, 2022 at 4:41 pm
Viewing 15 posts - 991 through 1,005 (of 7,619 total)