November 24, 2015 at 1:56 pm
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/
November 24, 2015 at 2:01 pm
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) "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".
November 24, 2015 at 2:43 pm
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/
November 24, 2015 at 3:24 pm
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) "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".
November 24, 2015 at 4:20 pm
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/
November 24, 2015 at 4:40 pm
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) "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".
November 24, 2015 at 6:46 pm
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/
November 24, 2015 at 6:55 pm
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
November 24, 2015 at 9:30 pm
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/
November 24, 2015 at 9:39 pm
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/
November 24, 2015 at 10:35 pm
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
November 25, 2015 at 5:13 am
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
November 25, 2015 at 9:11 am
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) "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".
November 25, 2015 at 9:25 am
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/
November 25, 2015 at 9:40 am
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) "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".
Viewing 15 posts - 1 through 15 (of 62 total)
You must be logged in to reply to this topic. Login to reply