June 22, 2021 at 6:02 pm
To process this data I setup Sql Agent jobs and basically setup one for each oven\wireline. It's processing the queue of records fairly fast but I do see a WAIT condition of "SOS_SCHEDULER_YIELD"... I have a 6 logical processor box with 40gig of memory, would there be anything performance wise I could tweak from an engine level?
Thanks for replies and suggestions much appreciated.
Please post a new question - specific to this issue.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 22, 2021 at 8:22 pm
Proper clustering would make the archiving process very fast and easy, since all rows would be contiguous by Quality_Date.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 23, 2021 at 12:03 am
I'm going to re-cluster in TEST to see what effect it has on processing...
Thanks.,
June 23, 2021 at 7:59 pm
As I started down the path of re-clustering the PK.
UNIQUEly CLUSTERED on ( Quality_Date, Quality_ID ).
Quality_Date is a computed column and set to NULL which can't be part of the PK.
Any thoughts ..
Thanks.
June 23, 2021 at 8:10 pm
As I started down the path of re-clustering the PK.
UNIQUEly CLUSTERED on ( Quality_Date, Quality_ID ).
Quality_Date is a computed column and set to NULL which can't be part of the PK.
Any thoughts ..
Thanks.
Is that something you can change? Cannot imagine why you would be creating a row in this table without a specified datetime. Are there NULL values in the Quality_Datetime column?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 23, 2021 at 10:20 pm
Doesn't need to be a PK, just the clustering index. The Quality_ID by itself would typically be the (nonclustered) PK.
Create the _test table from scratch. Add the UNIQUE CLUSTERED index and the PK:
CREATE UNIQUE CLUSTERED INDEX Quality_Test__CL
ON dbo.Quality_Test ( Quality_Date, Quality_ID )
WITH ( DATA_COMPRESSION = ROW /*or PAGE, if you prefer*/, FILLFACTOR = 98, SORT_IN_TEMPDB = ON) ON [PRIMARY] /*or your preferred filegroup name here*/
/* yes, technically NONCLUSTERED is not needed when the clus index is built first, but I prefer to explicitly specify it */
ALTER TABLE dbo.Quality_Test ADD
CONSTRAINT Quality_Test__PK PRIMARY KEY NONCLUSTERED ( Quality_ID )
WITH ( DATA_COMPRESSION = ROW, FILLFACTOR = 99, SORT_IN_TEMPDB = ON ) ON [PRIMARY] /*or your preferred filegroup name here*/
Then load the table in batches in clus key (which is believe is Quality_ID) order from the main table. Batch size will depend on the total row width and any trans log constraints, but you'd want to do at least 100,000 rows at a time, or 1M if doable. And be sure to specify "INSERT INTO dbo.Quality_Test WITH (TABLOCK)" to help the chances of getting minimal logging, if possible (which will depend on your version of SQL and other factors).
Btw, SQL should automatically PERSIST the computed column when you use it in an index, you shouldn't have to explicitly specify it as PERSISTED yourself, although of course that wouldn't hurt.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 23, 2021 at 10:26 pm
It's derived from the Quality_Datetime that's already defined in the Table. When records are Inserted that's when Quality_Date
is populated.. I can't change how that works.
Thanks.
June 24, 2021 at 11:58 am
I'll try that today Scott... Thanks.
June 24, 2021 at 4:54 pm
Okay so I keep the same Table and followed these steps on my DEV Box...
Dropped FK's
Deleted old PK
Added new suggested Indexes
Re-Applied Fk's
Testing
Thx.
Viewing 9 posts - 31 through 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply