Home Forums SQL Server 2005 SQL Server 2005 Performance Tuning Tasty Performance scenario to feast on, looking for general feedback and theories only RE: Tasty Performance scenario to feast on, looking for general feedback and theories only

  • January 22, 2013 at 11:02 am

    #1579754

    M_E_K (1/22/2013)

    Hi opc.three,

    Thanks for the feedback so far. Really appreciate your time.

    Actual execution plan of a currently running query is what I'm looking to get, I'll take a look at profiler, SST options.

    In one of your responses, you said: "I'm guessing that it would be a bad idea",

    Did you mean to say: "wouldn't be a bad idea"?

    No, that it would be bad idea. With that many data changes in the table, without auto-stats enabled stats will become stale in a hurry. This means that the execution plan generated when the table had no or very few rows could be used when the table had 100K or millions of rows, and that could make for some very bad performance.

    Answers to your follow up questions on FF:

    - It is not clustered

    - The GUIDs are generated/managed by the application. Here are some examples:

    b3d4a540-0c4e-1000-8029-a571dc530000

    b3d4a541-0c4e-1000-8029-a571dc530000

    b3d4a542-0c4e-1000-8029-a571dc530000

    b3d4a543-0c4e-1000-8029-a571dc530000

    b3d4a544-0c4e-1000-8029-a571dc530000

    b3d4a545-0c4e-1000-8029-a571dc530000

    b3d4a546-0c4e-1000-8029-a571dc530000

    Here are some examples of the QUALIFIED IDs:

    1-12TVTL1

    1-1CDYQZQ

    1-HDPSS4

    Here is DDL for table and index:

    CREATE TABLE [dbo].[M_C_CONTACT] (

    [GUID] [nvarchar](50) NOT NULL,

    [QUALIFIED_ID] [nvarchar](30) NOT NULL

    ) ON [PRIMARY]

    CREATE UNIQUE NONCLUSTERED INDEX [M_C_CONTACT_U1] ON [dbo].[M_C_CONTACT]

    (

    [GUID] ASC,

    [QUALIFIED_ID] ASC

    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]

    I would consider changing the data type of the GUID column to UNIQUEIDENTIFER, which is 16 bytes wide. You'll save yourself 86 bytes per row (84 for data + the 2-byte overhead in-built for NVARCHAR).

    As for your indexes, I would also try changing M_C_CONTACT_U1 to clustered and see how you do with fragmentation. Re: the GUIDs and how they might affect the FILLFACTOR, are they generated in sequence or are they effectively random?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato