Performance Risk\Impact of Partitioning

  • I have three tables that are very large and have too many indexes.

    I can easily archive the tables without running into the risk of having to tune the Stored Procedures and embedded T-SQL in .NET Applications.

    I do not feel that Partitioning is needed when all that is required is to reduce the size of the tables to improve performance.

    The tables will be archived by year so there is not a lot of effort to just archive the tables.

    Any thoughts or recommendations would be greatly appreciated?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Make sure you have the best clustered index on every table. That is critical for best performance overall, and the wrong clustering key often leads to extra indexes. Particularly look for tables that are clustered on identity and have many nonclus indexes that all start with the same column(s), because those are often the most obvious fixes to do.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher (11/24/2015)


    Make sure you have the best clustered index on every table. That is critical for best performance overall, and the wrong clustering key often leads to extra indexes. Particularly look for tables that are clustered on identity and have many nonclus indexes that all start with the same column(s), because those are often the most obvious fixes to do.

    The indexes are a total mess.

    The table structures are bad. Not even close to 3rd Normal Form. :crazy:

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • You could always archive old data first, then adjust the indexes as needed. But be sure to capture all index stats (missing, usage and operation) now before you delete a bunch of rows from the tables.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • I read a lot of articles but I'm not

    sure what indexes need to be aligned or unaligned?

    What factors do you take into consideration?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • For us, most tables don't really need partitioned. Archiving and partitioned views handles most of them.

    When I do partition, I align indexes because I want to be able to do partition switches.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher (11/24/2015)


    For us, most tables don't really need partitioned. Archiving and partitioned views handles most of them.

    When I do partition, I align indexes because I want to be able to do partition switches.

    I do not think partitioning necessary in this situation.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Welsh Corgi (11/24/2015)


    I have three tables that are very large and have too many indexes.

    I can easily archive the tables without running into the risk of having to tune the Stored Procedures and embedded T-SQL in .NET Applications.

    I do not feel that Partitioning is needed when all that is required is to reduce the size of the tables to improve performance.

    The tables will be archived by year so there is not a lot of effort to just archive the tables.

    Any thoughts or recommendations would be greatly appreciated?

    Size of a table is not a problem.

    You do not need to reduce the size of a table to improve query performance.

    Archiving, as well as any other type of partitioning are not needed if the indexing for the table is defined correctly.

    See the example I posted here:

    http://www.sqlservercentral.com/Forums/FindPost1738471.aspx

    10 million of "inactive" records do not affect performance of the queries by a tiniest bit.

    I offered Scott to show how to achieve better performance using partitioning, but he's pretending he did not see that. He must be too ashamed to admit his ignorance. And too rude to apologise.

    I'm currently working on a table with 100mil+ records, and hundreds of new records must have been added to it while I was typing this post.

    But this whole bulk of those "historical" records does not bother anyone, because the clustered index is setup on Inserted_Datetime column, and all the queries request data relevant to a specific period of time. They take a "slice of data" from the table and do the rest on the processing on that slice only.

    Therefore a monthly report from a table with 8 years worth of data executes exactly the same time as it would on a table with the data for that month only.

    You may improve query performance instantly if you either:

    - change the clustered index to match the range selection criteria in the queries (see what columns is mainly used in ">", "<", "BETWEEN" conditions);

    - change the queries to use existing clustered index (re-setting a clustered index on a big table is not that instant 🙂 )

    Even if you have a clustered PK on an IDENTITY(1,1) column you can still build quite effective queries.

    Try to add to the query(ies) a derived table (or CTE) selecting MIN(ID) and MAX(ID) for the selected range of dates (or what s used to define a range of data) and select all the records BETWEEN MIN_ID AND MAX_ID.

    _____________
    Code for TallyGenerator

  • ScottPletcher (11/24/2015)


    For us, most tables don't really need partitioned. Archiving and partitioned views handles most of them.

    When I do partition, I align indexes because I want to be able to do partition switches.

    So I need to align all indexes?

    Please excuse my lack of experience.

    Thank you for your guidance.:-)

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Sergiy (11/24/2015)


    Welsh Corgi (11/24/2015)


    I have three tables that are very large and have too many indexes.

    I can easily archive the tables without running into the risk of having to tune the Stored Procedures and embedded T-SQL in .NET Applications.

    I do not feel that Partitioning is needed when all that is required is to reduce the size of the tables to improve performance.

    The tables will be archived by year so there is not a lot of effort to just archive the tables.

    Any thoughts or recommendations would be greatly appreciated?

    Size of a table is not a problem.

    You do not need to reduce the size of a table to improve query performance.

    Archiving, as well as any other type of partitioning are not needed if the indexing for the table is defined correctly.

    I offered Scott to show how to achieve better performance using partitioning, but he's pretending he did not see that. He must be too ashamed to admit his ignorance. And too rude to apologise.

    Lets be nice.

    I do not agree that the size of a table may have an impact on performance.

    That is my experience.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Welsh Corgi (11/24/2015)


    ScottPletcher (11/24/2015)


    For us, most tables don't really need partitioned. Archiving and partitioned views handles most of them.

    When I do partition, I align indexes because I want to be able to do partition switches.

    So I need to align all indexes?

    Please excuse my lack of experience.

    Thank you for your guidance.:-)

    Well, that would be the best option.

    But the world is not quite ideal. 🙂

    So, you probably would be better off aligning queries with existing indexing. Something similar to what I indicated in my previous post.

    It would be not perfect, but in most cases "close enough".

    The only situation which signifies a dead end (at least I don't know a way out without redesigning tables) is a clustered PK over GUID. That is a killer.

    _____________
    Code for TallyGenerator

  • Sergiy,

    There is always the 'It depends' caveat when looking at the this type of thing, and in a lot of cases the actual table design is the issue, especially if its very wide, and full of verbose data (VARCHAR fields).

    Sorting out the indexes is probably the first place to start see where if there are still issues with performance.

    Something as simple as an Index defrag/rebuild routine, if one doesnt exist already, can give a significant boost to the performance.

    After that its about looking at unused indexes and removing them, and you'd be surprised how many developers create indexes that are virutally the same thinking they perform better.

    The other possibility is that the infrastructure needs to be looked at, especially if its never switched servers.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • Sergiy (11/24/2015)


    Welsh Corgi (11/24/2015)


    I have three tables that are very large and have too many indexes.

    I can easily archive the tables without running into the risk of having to tune the Stored Procedures and embedded T-SQL in .NET Applications.

    I do not feel that Partitioning is needed when all that is required is to reduce the size of the tables to improve performance.

    The tables will be archived by year so there is not a lot of effort to just archive the tables.

    Any thoughts or recommendations would be greatly appreciated?

    Size of a table is not a problem.

    You do not need to reduce the size of a table to improve query performance.

    Archiving, as well as any other type of partitioning are not needed if the indexing for the table is defined correctly.

    See the example I posted here:

    http://www.sqlservercentral.com/Forums/FindPost1738471.aspx

    10 million of "inactive" records do not affect performance of the queries by a tiniest bit.

    I offered Scott to show how to achieve better performance using partitioning, but he's pretending he did not see that. He must be too ashamed to admit his ignorance. And too rude to apologise.

    I'm currently working on a table with 100mil+ records, and hundreds of new records must have been added to it while I was typing this post.

    But this whole bulk of those "historical" records does not bother anyone, because the clustered index is setup on Inserted_Datetime column, and all the queries request data relevant to a specific period of time. They take a "slice of data" from the table and do the rest on the processing on that slice only.

    Therefore a monthly report from a table with 8 years worth of data executes exactly the same time as it would on a table with the data for that month only.

    You may improve query performance instantly if you either:

    - change the clustered index to match the range selection criteria in the queries (see what columns is mainly used in ">", "<", "BETWEEN" conditions);

    - change the queries to use existing clustered index (re-setting a clustered index on a big table is not that instant 🙂 )

    Even if you have a clustered PK on an IDENTITY(1,1) column you can still build quite effective queries.

    Try to add to the query(ies) a derived table (or CTE) selecting MIN(ID) and MAX(ID) for the selected range of dates (or what s used to define a range of data) and select all the records BETWEEN MIN_ID AND MAX_ID.

    I still believe the best way to improve performance is to remove inactive rows from the main table/partition. If needed, a partitioned view can be used to see both active and deleted rows. Sergiy prefers to put the active flag in the clustering key which is insane and unnecessary overhead. A few sample, stand-alone queries don't demonstrate the true idiocy of that approach (note carefully what I said: that approach is idiotic, not the person proposing it). You realize the folly when multiple users start hitting it at the same time and the endless deadlocks start. Then you're forced into RCSI and its associated overhead, which on very busy tables can be huge. RCSI itself is useful, but it shouldn't be a forced fallback because of poor index design.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • The following is the structure of the table:

    CREATE TABLE [dbo].[xactControlPoint](

    [xactControlPointID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

    [xactControlPointTypeID] [int] NOT NULL,

    [call_id] [int] NOT NULL,

    [contr_id] [int] NULL,

    [xactTransactionIDValue] [varchar](50) NULL,

    [beginDate] [datetime] NOT NULL,

    [userName] [varchar](250) NULL,

    [notes] [varchar](2000) NULL,

    [reservationFlg] [bit] NULL,

    [rowUpdateDate] [datetime] NULL,

    [rowUpdateID] [int] NULL,

    [Target_Followup_Date] [datetime] NULL,

    CONSTRAINT [PK_xactControlPoint] PRIMARY KEY CLUSTERED

    (

    [xactControlPointID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FG1]

    ) ON [FG1]

    The following are the indexes:

    index_nameindex_descriptionindex_keys

    IDX__xactControlPoint__Callid_xactControlPointTypeIDnonclustered located on FG2call_id, xactControlPointTypeID

    IN_CallIDnonclustered located on FG2call_id

    IN_ContrIDnonclustered located on FG2contr_id

    IX_ControlPointTypeIDnonclustered located on FG1xactControlPointTypeID

    IX_xactControlPoint__K3_K4nonclustered located on FG2call_id, contr_id

    PK_xactControlPointclustered, unique, primary key located on FG1xactControlPointID

    Besides adding the begin date to the Primary Key, what other indexes should be created?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Welsh Corgi (11/25/2015)


    The following is the structure of the table:

    CREATE TABLE [dbo].[xactControlPoint](

    [xactControlPointID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

    [xactControlPointTypeID] [int] NOT NULL,

    [call_id] [int] NOT NULL,

    [contr_id] [int] NULL,

    [xactTransactionIDValue] [varchar](50) NULL,

    [beginDate] [datetime] NOT NULL,

    [userName] [varchar](250) NULL,

    [notes] [varchar](2000) NULL,

    [reservationFlg] [bit] NULL,

    [rowUpdateDate] [datetime] NULL,

    [rowUpdateID] [int] NULL,

    [Target_Followup_Date] [datetime] NULL,

    CONSTRAINT [PK_xactControlPoint] PRIMARY KEY CLUSTERED

    (

    [xactControlPointID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [FG1]

    ) ON [FG1]

    The following are the indexes:

    index_nameindex_descriptionindex_keys

    IDX__xactControlPoint__Callid_xactControlPointTypeIDnonclustered located on FG2call_id, xactControlPointTypeID

    IN_CallIDnonclustered located on FG2call_id

    IN_ContrIDnonclustered located on FG2contr_id

    IX_ControlPointTypeIDnonclustered located on FG1xactControlPointTypeID

    IX_xactControlPoint__K3_K4nonclustered located on FG2call_id, contr_id

    PK_xactControlPointclustered, unique, primary key located on FG1xactControlPointID

    Besides adding the begin date to the Primary Key, what other indexes should be created?

    On first glance, it looks as if the clustered key should start with call_id. The first step is always to determine the best clustering key, after which you can review what nonclus index(es) are needed.

    To make this determination, we need to review some additional stats as well. Can you run the following script and post the results? You'll need the DelimitedSplit8K function.

    --USE [your_db_name]

    SET DEADLOCK_PRIORITY -8 --make sure we are the victim if we unexpectedly block with anything

    IF OBJECT_ID('tempdb.dbo.#index_specs') IS NOT NULL

    DROP TABLE dbo.#index_specs

    IF OBJECT_ID('tempdb.dbo.#index_missing') IS NOT NULL

    DROP TABLE dbo.#index_missing

    IF OBJECT_ID('tempdb.dbo.#index_usage') IS NOT NULL

    DROP TABLE dbo.#index_usage

    CREATE TABLE dbo.#index_specs (

    object_id int NOT NULL,

    index_id int NOT NULL,

    min_compression int NULL,

    max_compression int NULL,

    drive char(1) NULL,

    alloc_mb decimal(9, 1) NOT NULL,

    used_mb decimal(9, 1) NOT NULL,

    rows bigint NULL,

    size_rank int NULL,

    approx_max_data_width bigint NULL,

    UNIQUE CLUSTERED ( object_id, index_id )

    ) --SELECT * FROM #index_specs

    DECLARE @list_missing_indexes bit

    DECLARE @list_missing_indexes_summary bit

    DECLARE @include_schema_in_table_names bit

    DECLARE @table_name_pattern sysname

    DECLARE @order_by smallint --1=table_name; 2=size; -2=size DESC;.

    DECLARE @format_counts smallint --1=with commas, no decimals; 2/3=with K=1000s,M=1000000s, with 2=0 dec. or 3=1 dec. places;.

    DECLARE @debug smallint

    SET @list_missing_indexes = 1

    SET @list_missing_indexes_summary = 0

    SET @include_schema_in_table_names = 0

    SET @table_name_pattern = '%'

    SET @order_by = -2

    SET @format_counts = 3

    SET @debug = 0

    --SELECT create_date FROM sys.databases /*WITH (NOLOCK)*/ WHERE name = N'tempdb'

    PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)

    DECLARE @is_compression_available bit

    DECLARE @sql varchar(8000)

    IF CAST(SERVERPROPERTY('ProductVersion') AS varchar(30)) LIKE '9%'

    OR CAST(SERVERPROPERTY('Edition') AS varchar(40)) NOT LIKE '%Enterprise%'

    SET @is_compression_available = 0

    ELSE

    SET @is_compression_available = 1

    SET @sql = '

    INSERT INTO #index_specs ( object_id, index_id, ' +

    CASE WHEN @is_compression_available = 0 THEN '' ELSE '

    min_compression, max_compression,' END + '

    drive, alloc_mb, used_mb, rows )

    SELECT

    part.object_id,

    part.index_id,' +

    CASE WHEN @is_compression_available = 0 THEN '' ELSE '

    ISNULL(MIN(part.data_compression), 0) AS min_compression,

    ISNULL(MAX(part.data_compression), 0) AS max_compression,' END + '

    MIN(LEFT([file].physical_name, 1)) AS drive,

    SUM(au.total_pages) / 128.0 AS alloc_mb,

    SUM(au.used_pages) / 128.0 AS used_mb,

    MAX(part.rows) AS rows

    FROM sys.partitions part

    INNER JOIN sys.allocation_units au ON

    au.container_id = part.partition_id

    OUTER APPLY (

    SELECT TOP (1) *

    FROM sys.database_files df

    WHERE

    df.data_space_id = au.data_space_id

    ) AS [file]

    GROUP BY

    part.object_id,

    part.index_id

    '

    IF @debug >= 1

    PRINT @sql

    EXEC(@sql)

    UPDATE [is]

    SET approx_max_data_width = index_cols.approx_max_data_width

    FROM #index_specs [is]

    INNER JOIN (

    SELECT index_col_ids.object_id, index_col_ids.index_id,

    SUM(CASE WHEN c.max_length = -1 THEN 16 ELSE c.max_length END) AS approx_max_data_width

    FROM (

    SELECT ic.object_id, ic.index_id, ic.column_id

    --,object_name(ic.object_id)

    FROM sys.index_columns ic

    WHERE

    ic.object_id > 100

    UNION

    SELECT i_nonclus.object_id, i_nonclus.index_id, ic_clus.column_id

    --,object_name(i_nonclus.object_id)

    FROM sys.indexes i_nonclus

    CROSS APPLY (

    SELECT ic_clus2.column_id

    --,object_name(ic_clus2.object_id),ic_clus2.key_ordinal

    FROM sys.index_columns ic_clus2

    WHERE

    ic_clus2.object_id = i_nonclus.object_id AND

    ic_clus2.index_id = 1 AND

    ic_clus2.key_ordinal > 0 --technically superfluous, since clus index can't have include'd cols anyway

    ) AS ic_clus

    WHERE

    i_nonclus.object_id > 100 AND

    i_nonclus.index_id > 1

    ) AS index_col_ids

    INNER JOIN sys.columns c ON c.object_id = index_col_ids.object_id AND c.column_id = index_col_ids.column_id

    GROUP BY index_col_ids.object_id, index_col_ids.index_id

    ) AS index_cols ON index_cols.object_id = [is].object_id AND index_cols.index_id = [is].index_id

    UPDATE ispec

    SET size_rank = ispec_ranking.size_rank

    FROM #index_specs ispec

    INNER JOIN (

    SELECT object_id, ROW_NUMBER() OVER(ORDER BY alloc_mb DESC, rows DESC, OBJECT_NAME(object_id)) AS size_rank

    FROM (

    SELECT object_id, SUM(alloc_mb) AS alloc_mb, MAX(rows) AS rows

    FROM #index_specs

    GROUP BY object_id

    ) AS ispec_allocs

    ) AS ispec_ranking ON

    ispec_ranking.object_id = ispec.object_id

    IF @list_missing_indexes = 1

    BEGIN

    SELECT

    IDENTITY(int, 1, 1) AS ident,

    DB_NAME(mid.database_id) AS Db_Name,

    CONVERT(varchar(10), GETDATE(), 120) AS capture_date,

    ispec.size_rank, ispec.alloc_mb,

    CASE WHEN @format_counts = 1 THEN REPLACE(CONVERT(varchar(20), CAST(dps.row_count AS money), 1), '.00', '')

    WHEN @format_counts = 2 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix

    WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS int) AS varchar(20)) + ca1.row_count_suffix

    WHEN @format_counts = 3 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix

    WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS decimal(14, 1)) AS varchar(20)) + ca1.row_count_suffix

    ELSE CAST(dps.row_count AS varchar(20)) END AS row_count,

    CASE WHEN @include_schema_in_table_names = 1 THEN OBJECT_SCHEMA_NAME(mid.object_id /*, mid.database_id*/) + '.'

    ELSE '' END + OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,

    mid.equality_columns, mid.inequality_columns, mid.included_columns,

    user_seeks, user_scans, cj1.max_days_active, unique_compiles,

    last_user_seek, last_user_scan, avg_total_user_cost, avg_user_impact,

    system_seeks, system_scans, last_system_seek, last_system_scan, avg_total_system_cost, avg_system_impact,

    mid.statement, mid.object_id, mid.index_handle

    INTO #index_missing

    FROM sys.dm_db_missing_index_details mid /*WITH (NOLOCK)*/

    CROSS JOIN (

    SELECT DATEDIFF(DAY, create_date, GETDATE()) AS max_days_active FROM sys.databases /*WITH (NOLOCK)*/ WHERE name = 'tempdb'

    ) AS cj1

    LEFT OUTER JOIN sys.dm_db_missing_index_groups mig /*WITH (NOLOCK)*/ ON

    mig.index_handle = mid.index_handle

    LEFT OUTER JOIN sys.dm_db_missing_index_group_stats migs /*WITH (NOLOCK)*/ ON

    migs.group_handle = mig.index_group_handle

    LEFT OUTER JOIN sys.dm_db_partition_stats dps /*WITH (NOLOCK)*/ ON

    dps.object_id = mid.object_id AND

    dps.index_id IN (0, 1)

    CROSS APPLY (

    SELECT CASE WHEN dps.row_count >= 1000000 THEN 'M' WHEN dps.row_count >= 1000 THEN 'K' ELSE '' END AS row_count_suffix

    ) AS ca1

    OUTER APPLY (

    SELECT ispec.alloc_mb, ispec.size_rank

    FROM dbo.#index_specs ispec

    WHERE

    ispec.object_id = mid.object_id AND

    ispec.index_id IN (0, 1)

    ) AS ispec

    --order by

    --DB_NAME, Table_Name, equality_columns

    WHERE

    1 = 1

    AND mid.database_id = DB_ID()

    AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern

    AND OBJECT_NAME(mid.object_id) NOT LIKE 'tmp%'

    ORDER BY

    --avg_total_user_cost * (user_seeks + user_scans) DESC,

    Db_Name,

    CASE WHEN @order_by IN (-2, 2) THEN ispec.size_rank * -SIGN(@order_by) ELSE 0 END,

    Table_Name,

    equality_columns, inequality_columns,

    user_seeks DESC

    SELECT *

    FROM #index_missing

    ORDER BY ident

    IF @list_missing_indexes_summary = 1

    BEGIN

    SELECT

    derived.Size_Rank, derived.alloc_mb,

    derived.Table_Name, derived.Equality_Column, derived.Equality#, derived.User_Seeks,

    ISNULL((SELECT SUM(user_seeks)

    FROM #index_missing im2

    CROSS APPLY DBA.dbo.DelimitedSplit8K (inequality_columns, ',') ds

    WHERE im2.Size_Rank = derived.Size_Rank AND

    LTRIM(RTRIM(ds.Item)) = derived.Equality_Column

    ), 0) AS Inequality_Seeks,

    derived.User_Scans, derived.Last_User_Seek, derived.Last_User_Scan,

    derived.Max_Days_Active, derived.Avg_Total_User_Cost, derived.Approx_Total_Cost

    FROM (

    SELECT

    Size_Rank, MAX(alloc_mb) AS alloc_mb, Table_Name, LTRIM(RTRIM(ds.Item)) AS Equality_Column,

    SUM(user_seeks) AS User_Seeks, SUM(user_scans) AS User_Scans,

    MAX(last_user_seek) AS Last_User_Seek, MAX(last_user_scan) AS Last_User_Scan,

    MIN(max_days_active) AS Max_Days_Active,

    MAX(avg_total_user_cost) AS Avg_Total_User_Cost,

    (SUM(user_seeks) + SUM(user_scans)) * MAX(avg_total_user_cost) AS Approx_Total_Cost,

    MAX(ds.ItemNumber) AS Equality#

    FROM #index_missing

    CROSS APPLY DBA.dbo.DelimitedSplit8K (equality_columns, ',') ds

    WHERE equality_columns IS NOT NULL

    GROUP BY size_rank, Table_Name, LTRIM(RTRIM(ds.Item))

    ) AS derived

    ORDER BY Size_Rank, Table_Name, Approx_Total_Cost DESC

    END --IF

    END --IF

    PRINT 'Index Usage Stats @ ' + CONVERT(varchar(30), GETDATE(), 120)

    -- list index usage stats (seeks, scans, etc.)

    SELECT

    IDENTITY(int, 1, 1) AS ident,

    DB_NAME() AS db_name,

    ispec.drive AS drv,

    ispec.size_rank, ispec.alloc_mb - ispec.used_mb AS unused_mb,

    --ispec.alloc_mb AS alloc_mb_raw,

    CAST(ispec.alloc_mb / 1000.0 AS decimal(9, 4)) AS alloc_gb,

    SUBSTRING('NY', CAST(i.is_primary_key AS int) + CAST(i.is_unique_constraint AS int) + 1, 1) +

    CASE WHEN i.is_unique = CAST(i.is_primary_key AS int) + CAST(i.is_unique_constraint AS int) THEN ''

    ELSE '.' + SUBSTRING('NY', CAST(i.is_unique AS int) + 1, 1) END AS [uniq?],

    REPLACE(i.name, ca1.table_name, '~') AS index_name,

    CASE WHEN @include_schema_in_table_names = 1 THEN OBJECT_SCHEMA_NAME(i.object_id /*, DB_ID()*/) + '.'

    ELSE '' END + OBJECT_NAME(i.object_id /*, i.database_id*/) AS Table_Name,

    --dps.row_count AS row_count_raw,

    CASE WHEN @format_counts = 1 THEN REPLACE(CONVERT(varchar(20), CAST(dps.row_count AS money), 1), '.00', '')

    WHEN @format_counts = 2 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix

    WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS int) AS varchar(20)) + ca1.row_count_suffix

    WHEN @format_counts = 3 THEN CAST(CAST(dps.row_count * 1.0 / CASE ca1.row_count_suffix

    WHEN 'M' THEN 1000000 WHEN 'K' THEN 1000 ELSE 1 END AS decimal(14, 1)) AS varchar(20)) + ca1.row_count_suffix

    ELSE CAST(dps.row_count AS varchar(20)) END AS row_count,

    i.index_id,

    ispec.approx_max_data_width AS [min_width],

    SUBSTRING(key_cols, 3, 8000) AS key_cols, SUBSTRING(nonkey_cols, 3, 8000) AS nonkey_cols,

    ius.user_seeks, ius.user_scans, --ius.user_seeks + ius.user_scans AS total_reads,

    ius.user_lookups, ius.user_updates,

    dios.leaf_delete_count + dios.leaf_insert_count + dios.leaf_update_count as leaf_mod_count,

    dios.range_scan_count, dios.singleton_lookup_count,

    DATEDIFF(DAY, STATS_DATE ( i.object_id , i.index_id ), GETDATE()) AS stats_days_old,

    dios.row_lock_count, dios.row_lock_wait_in_ms,

    dios.page_lock_count, dios.page_lock_wait_in_ms,

    ius.last_user_seek, ius.last_user_scan,

    ius.last_user_lookup, ius.last_user_update,

    fk.Reference_Count AS fk_ref_count,

    DATEDIFF(DAY, CASE

    WHEN o.create_date > cj1.sql_startup_date AND o.create_date > o.modify_date THEN o.create_date

    WHEN o.modify_date > cj1.sql_startup_date AND o.modify_date > o.create_date THEN o.modify_date

    ELSE cj1.sql_startup_date END, GETDATE()) AS max_days_active,

    i.fill_factor,

    ius2.row_num,

    CASE

    WHEN ispec.max_compression IS NULL THEN '(Not applicable)'

    WHEN ispec.max_compression = 2 THEN 'Page'

    WHEN ispec.max_compression = 1 THEN 'Row'

    WHEN ispec.max_compression = 0 THEN ''

    ELSE '(Unknown)' END AS max_compression,

    FILEGROUP_NAME(i.data_space_id) AS filegroup_name,

    ius.system_seeks, ius.system_scans, ius.system_lookups, ius.system_updates,

    ius.last_system_seek, ius.last_system_scan, ius.last_system_lookup, ius.last_system_update,

    GETDATE() AS capture_date

    INTO #index_usage

    FROM sys.indexes i /*WITH (NOLOCK)*/

    INNER JOIN sys.objects o /*WITH (NOLOCK)*/ ON

    o.object_id = i.object_id

    CROSS JOIN (

    SELECT create_date AS sql_startup_date FROM sys.databases /*WITH (NOLOCK)*/ WHERE name = 'tempdb'

    ) AS cj1

    OUTER APPLY (

    SELECT CASE WHEN EXISTS(SELECT 1 FROM #index_specs [is] WHERE [is].object_id = i.object_id AND [is].index_id = 1)

    THEN 1 ELSE 0 END AS has_clustered_index

    ) AS cj2

    LEFT OUTER JOIN dbo.#index_specs ispec ON

    ispec.object_id = i.object_id AND

    ispec.index_id = i.index_id

    OUTER APPLY (

    SELECT

    ', ' + COL_NAME(ic.object_id, ic.column_id)

    FROM sys.index_columns ic /*WITH (NOLOCK)*/

    WHERE

    ic.key_ordinal > 0 AND

    ic.object_id = i.object_id AND

    ic.index_id = i.index_id

    ORDER BY

    ic.key_ordinal

    FOR XML PATH('')

    ) AS key_cols (key_cols)

    OUTER APPLY (

    SELECT

    ', ' + COL_NAME(ic.object_id, ic.column_id)

    FROM sys.index_columns ic /*WITH (NOLOCK)*/

    WHERE

    ic.key_ordinal = 0 AND

    ic.object_id = i.object_id AND

    ic.index_id = i.index_id

    ORDER BY

    COL_NAME(ic.object_id, ic.column_id)

    FOR XML PATH('')

    ) AS nonkey_cols (nonkey_cols)

    LEFT OUTER JOIN sys.dm_db_partition_stats dps /*WITH (NOLOCK)*/ ON

    dps.object_id = i.object_id AND

    dps.index_id = i.index_id

    LEFT OUTER JOIN sys.dm_db_index_usage_stats ius /*WITH (NOLOCK)*/ ON

    ius.database_id = DB_ID() AND

    ius.object_id = i.object_id AND

    ius.index_id = i.index_id

    LEFT OUTER JOIN (

    SELECT

    database_id, object_id, MAX(user_scans) AS user_scans,

    ROW_NUMBER() OVER (ORDER BY MAX(user_scans) DESC) AS row_num --user_scans|user_seeks+user_scans

    FROM sys.dm_db_index_usage_stats /*WITH (NOLOCK)*/

    WHERE

    database_id = DB_ID()

    --AND index_id > 0

    GROUP BY

    database_id, object_id

    ) AS ius2 ON

    ius2.database_id = DB_ID() AND

    ius2.object_id = i.object_id

    LEFT OUTER JOIN (

    SELECT

    referenced_object_id, COUNT(*) AS Reference_Count

    FROM sys.foreign_keys /*WITH (NOLOCK)*/

    WHERE

    is_disabled = 0

    GROUP BY

    referenced_object_id

    ) AS fk ON

    fk.referenced_object_id = i.object_id

    LEFT OUTER JOIN (

    SELECT *

    FROM sys.dm_db_index_operational_stats ( DB_ID(), NULL, NULL, NULL )

    ) AS dios ON

    dios.object_id = i.object_id AND

    dios.index_id = i.index_id

    OUTER APPLY (

    SELECT OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name,

    CASE WHEN dps.row_count >= 1000000 THEN 'M' WHEN dps.row_count >= 1000 THEN 'K' ELSE '' END AS row_count_suffix

    ) AS ca1

    WHERE

    i.object_id > 100 AND

    i.is_hypothetical = 0 AND

    i.type IN (0, 1, 2) AND

    o.type NOT IN ( 'IF', 'IT', 'TF', 'TT' ) AND

    (

    o.name LIKE @table_name_pattern AND

    o.name NOT LIKE 'dtprop%' AND

    o.name NOT LIKE 'filestream[_]' AND

    o.name NOT LIKE 'MSpeer%' AND

    o.name NOT LIKE 'MSpub%' AND

    --o.name NOT LIKE 'queue[_]%' AND

    o.name NOT LIKE 'sys%'

    )

    ORDER BY

    --cj2.has_clustered_index, ispec.size_rank, --heaps first, by size

    db_name,

    --ius.user_seeks - ius.user_scans,

    CASE WHEN @order_by IN (-2, 2) THEN ispec.size_rank * -SIGN(@order_by) ELSE 0 END,

    --ius.user_scans DESC,

    --ius2.row_num, --user_scans&|user_seeks

    table_name,

    -- list clustered index first, if any, then other index(es)

    CASE WHEN i.index_id IN (0, 1) THEN 1 ELSE 2 END,

    key_cols

    SELECT *

    FROM #index_usage

    ORDER BY ident

    PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)

    SET DEADLOCK_PRIORITY NORMAL

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 15 posts - 1 through 15 (of 62 total)

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