suggestions on making more efficient and faster

  • Bruin wrote:

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • I'm going to re-cluster in TEST to see what effect it has on processing...

    Thanks.,

  • 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.

  • Bruin wrote:

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • 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.

  • I'll try that today Scott... Thanks.

  • 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 39 (of 39 total)

You must be logged in to reply to this topic. Login to reply