September 20, 2022 at 4:06 am
Hello everyone!
I am in dire need of some assistance or direction. I am by no means a expert at any of this stuff. Unfortunately for me the company I currently work for does not have a DBA. We are experiencing some issues with our warehouse management software getting a lot of execution timeout errors and running slow. I reached out to their support and they just asked me to rebuild the indexes for the database.
I've rebuilt the indexes for the database being used for this application but employees are still complaining. And the WMS support staff is being unresponsive now. I don't know what else to do to check on what may be causing these issues. I know I am probably in over my head (hey sometimes that is how we learn new things ) but I am hoping someone can give me some guidance as to what to look for in order to troubleshoot this.
Not sure if this will help but this is the error we keep running into. Also a screen shot from the activity monitor on the DB, not sure how much that would help.
Any help or links or anything anyone could provide to help me find some answers would be greatly appreciated.
September 20, 2022 at 1:15 pm
So, you're going for a swim in the deep end of the pool.
Let's start with what you shouldn't do. Don't rebuild the indexes. Probably, they hit issues in the past, someone did a teeny, tiny, amount of research, saw a blog or a forum post where that helped, so they rebuilt the indexes. Probably, and I'm confident here, that resulted in a statistics rebuild. That statistics rebuild caused recompiles on the procedures and a new plan was generated that worked better for performance. However, what they saw was, index rebuild fixes performance, which simply isn't true.
What to do instead.
First, enable a mechanism to capture query metrics. I recommend either Query Store (the simplest) or Extended Events ( the most detailed) as a mechanism for capturing query metrics. With that in place, identify the queries that a) run the longest, b) use the most resources and c) are executed most frequently. You want to look at all three, because only using one of these measures won't give you a complete picture. With that in hand, identify queries that make at least two of these lists. Then, get the execution plans for those queries. Understand how the optimizer is satisfying the queries based on the behavior of the execution plan. That will then guide you to where you need to adjust indexes, structures, or code.
Yeah, I know, giant task. As I said, you're in the deep end of the pool and there's really not a simple "flip this switch and you'll get better performance" answer. Sorry.
For LOTS more detail on this, I recommend the two books linked in my signature. One you can get for free, the other costs money.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 20, 2022 at 2:34 pm
Hello everyone!
I am in dire need of some assistance or direction. I am by no means a expert at any of this stuff. Unfortunately for me the company I currently work for does not have a DBA. We are experiencing some issues with our warehouse management software getting a lot of execution timeout errors and running slow. I reached out to their support and they just asked me to rebuild the indexes for the database.
I've rebuilt the indexes for the database being used for this application but employees are still complaining. And the WMS support staff is being unresponsive now. I don't know what else to do to check on what may be causing these issues. I know I am probably in over my head (hey sometimes that is how we learn new things ) but I am hoping someone can give me some guidance as to what to look for in order to troubleshoot this.
Not sure if this will help but this is the error we keep running into. Also a screen shot from the activity monitor on the DB, not sure how much that would help.
Any help or links or anything anyone could provide to help me find some answers would be greatly appreciated.
First of all, who is "WMS"? Second of all, you didn't post either the error you were talking about or any screen shot.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 20, 2022 at 3:14 pm
igaytwanb wrote:Hello everyone!
I am in dire need of some assistance or direction. I am by no means a expert at any of this stuff. Unfortunately for me the company I currently work for does not have a DBA. We are experiencing some issues with our warehouse management software getting a lot of execution timeout errors and running slow. I reached out to their support and they just asked me to rebuild the indexes for the database.
I've rebuilt the indexes for the database being used for this application but employees are still complaining. And the WMS support staff is being unresponsive now. I don't know what else to do to check on what may be causing these issues. I know I am probably in over my head (hey sometimes that is how we learn new things ) but I am hoping someone can give me some guidance as to what to look for in order to troubleshoot this.
Not sure if this will help but this is the error we keep running into. Also a screen shot from the activity monitor on the DB, not sure how much that would help.
Any help or links or anything anyone could provide to help me find some answers would be greatly appreciated.
First of all, who is "WMS"? Second of all, you didn't post either the error you were talking about or any screen shot.
I assumed "WMS" would be "Warehouse Management Software", based on the rest of the post. Perhaps they can't identity the specific software any more than that?!
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".
September 20, 2022 at 3:34 pm
Jeff Moden wrote:igaytwanb wrote:Hello everyone!
I am in dire need of some assistance or direction. I am by no means a expert at any of this stuff. Unfortunately for me the company I currently work for does not have a DBA. We are experiencing some issues with our warehouse management software getting a lot of execution timeout errors and running slow. I reached out to their support and they just asked me to rebuild the indexes for the database.
I've rebuilt the indexes for the database being used for this application but employees are still complaining. And the WMS support staff is being unresponsive now. I don't know what else to do to check on what may be causing these issues. I know I am probably in over my head (hey sometimes that is how we learn new things ) but I am hoping someone can give me some guidance as to what to look for in order to troubleshoot this.
Not sure if this will help but this is the error we keep running into. Also a screen shot from the activity monitor on the DB, not sure how much that would help.
Any help or links or anything anyone could provide to help me find some answers would be greatly appreciated.
First of all, who is "WMS"? Second of all, you didn't post either the error you were talking about or any screen shot.
I assumed "WMS" would be "Warehouse Management Software", based on the rest of the post. Perhaps they can't identity the specific software any more than that? Or perhaps that's the actual name of the software?
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".
September 20, 2022 at 8:42 pm
Would some of these free tools be a starting point for someone unfamiliar troubleshooting ?
https://www.idera.com/productssolutions/freetools/
Other companies may have similar tools, and most will also offer free trials of paid products that might be useful.
September 20, 2022 at 8:46 pm
With so little claimed knowledge on your part, I'm going to direct you to a tool that has help many both with and without experience. It's not a magic fix... it is a bit of magic that will help you find what needs to be fixed. It's Brent Ozar's "First Responder" kit. Here's a link with an hour long flick as to how to start and links as to where to get the stuff. A lot of it is all what people would have you take a look at "from the ground up". sp_Blitz will also give you a wealth of knowledge about the health of your system AND provide you links about where to get more information about each problem
Here's the link I'm talking about...
https://www.brentozar.com/responder/
--Jeff Moden
Change is inevitable... Change for the better is not.
September 21, 2022 at 5:38 am
Would some of these free tools be a starting point for someone unfamiliar troubleshooting ?
https://www.idera.com/productssolutions/freetools/
Other companies may have similar tools, and most will also offer free trials of paid products that might be useful.
Let me be 100% clear, that company is a competitor to the company I work for.
Now, on with the answer.
No. There's nothing there that's going to give anyone the information and guidance they need to start the process of troubleshooting query performance. Problem is, this is a rather tough space, requiring more than a little bit of knowledge to make it work well. The full-blown monitoring solution from Idera could help (although, I'd argue Redgate's would help more, but, that's me), but not those point tools.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 29, 2022 at 9:03 pm
SQL already captures the vast bulk of the stats you need, thus you don't generally have to set up separate runs, etc. to capture activity.
If you're willing to run the script below and post both results files -- as shreadsheets or CSV -- I can take a look and offer some index changes that will be useful.
/*capture changed system settings so that they can be reset to their original values at the end of this script*/DECLARE @deadlock_priority smallint
DECLARE @transaction_isolation_level smallint
SELECT @deadlock_priority = deadlock_priority, @transaction_isolation_level = transaction_isolation_level
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID
SET DEADLOCK_PRIORITY -8; /*"tell" SQL that if this task somehow gets into a deadlock, cancel THIS task, NOT any other one*/SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--**********************************************************************************************************************
SET NOCOUNT ON;
DECLARE @filegroup_name nvarchar(128)
DECLARE @include_system_tables bit
DECLARE @list_filegroup_and_drive_summary bit
DECLARE @list_missing_indexes bit
DECLARE @list_missing_indexes_summary bit
DECLARE @list_total_size bit
DECLARE @max_compression int
DECLARE @order_by smallint /* -2=size DESC; 1=table_name ASC; 2=size ASC; */DECLARE @table_name_pattern sysname
DECLARE @table_name_exclude_pattern sysname = '#%'
SET @list_missing_indexes = 1 --NOTE: can take some time, set to 0 if you don't want to wait.
SET @list_missing_indexes_summary = 0 /*not available unless you uncomment the code for it which requires DelimitedSplit8K function*/SET @order_by = -2 /* -2=size DESC; 1=table_name ASC; 2=size ASC; */SET @list_total_size = 1
SET @filegroup_name = '%'
SET @table_name_pattern = '%'
IF @include_system_tables IS NULL
SET @include_system_tables = CASE WHEN DB_NAME() IN ('master', 'msdb', 'tempdb') THEN 1 ELSE 0 END
SET @list_filegroup_and_drive_summary = 0
DECLARE @debug smallint
DECLARE @format_counts smallint --1=',0'; 2/3=with K=1000s,M=1000000s, with 0/1 dec. places;.
DECLARE @include_schema_in_table_names bit
DECLARE @sql_startup_date datetime
DECLARE @total decimal(19, 3)
SELECT @sql_startup_date = create_date
FROM sys.databases WITH (NOLOCK)
WHERE name = 'tempdb'
SET @include_schema_in_table_names = 1
SET @format_counts = 3
SET @debug = 0
IF OBJECT_ID('tempdb.dbo.#index_missing') IS NOT NULL
DROP TABLE dbo.#index_missing
IF OBJECT_ID('tempdb.dbo.#index_operational_stats') IS NOT NULL
DROP TABLE dbo.#index_operational_stats
IF OBJECT_ID('tempdb.dbo.#index_specs') IS NOT NULL
DROP TABLE dbo.#index_specs
IF OBJECT_ID('tempdb.dbo.#index_usage') IS NOT NULL
DROP TABLE dbo.#index_usage
SELECT *
INTO #index_operational_stats
FROM sys.dm_db_index_operational_stats ( DB_ID(), NULL, NULL, NULL )
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,
alloc_gb AS CAST(alloc_mb / 1024.0 AS decimal(9, 3)),
used_mb decimal(9, 1) NOT NULL,
used_gb AS CAST(used_mb / 1024.0 AS decimal(9, 3)),
rows bigint NULL,
table_mb decimal(9, 1) NULL,
table_gb AS CAST(table_mb / 1024.0 AS decimal(9, 3)),
size_rank int NULL,
approx_max_data_width bigint NULL,
max_days_active int,
UNIQUE CLUSTERED ( object_id, index_id )
) --SELECT * FROM #index_specs
--**********************************************************************************************************************
PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)
DECLARE @is_compression_available bit
DECLARE @sql varchar(max)
IF (CAST(SERVERPROPERTY('Edition') AS varchar(40)) NOT LIKE '%Developer%' AND
CAST(SERVERPROPERTY('Edition') AS varchar(40)) NOT LIKE '%Enterprise%')
AND (CAST(SERVERPROPERTY('ProductVersion') AS varchar(30)) NOT LIKE '1[456789]%.%')
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 + '
alloc_mb, used_mb, rows )
SELECT
base_size.object_id,
base_size.index_id, ' +
CASE WHEN @is_compression_available = 0 THEN '' ELSE '
base_size.min_compression,
base_size.max_compression,' END + '
(base_size.total_pages + ISNULL(internal_size.total_pages, 0)) / 128.0 AS alloc_mb,
(base_size.used_pages + ISNULL(internal_size.used_pages, 0)) / 128.0 AS used_mb,
base_size.row_count AS rows
FROM (
SELECT
dps.object_id,
dps.index_id, ' +
CASE WHEN @is_compression_available = 0 THEN '' ELSE '
MIN(p.data_compression) AS min_compression,
MAX(p.data_compression) AS max_compression,' END + '
SUM(dps.reserved_page_count) AS total_pages,
SUM(dps.used_page_count) AS used_pages,
SUM(CASE WHEN dps.index_id IN (0, 1) THEN dps.row_count ELSE 0 END) AS row_count
FROM sys.dm_db_partition_stats dps ' +
CASE WHEN @is_compression_available = 0 THEN '' ELSE '
INNER JOIN sys.partitions p WITH (NOLOCK) ON
p.partition_id = dps.partition_id ' END + '
--WHERE dps.object_id > 100
WHERE OBJECT_NAME(dps.object_id) LIKE ''' + @table_name_pattern + ''' AND
OBJECT_NAME(dps.object_id) NOT LIKE ''' + @table_name_exclude_pattern + '''
GROUP BY
dps.object_id,
dps.index_id
) AS base_size
LEFT OUTER JOIN (
SELECT
it.parent_id,
SUM(dps.reserved_page_count) AS total_pages,
SUM(dps.used_page_count) AS used_pages
FROM sys.internal_tables it WITH (NOLOCK)
INNER JOIN sys.dm_db_partition_stats dps WITH (NOLOCK) ON
dps.object_id = it.parent_id
WHERE it.internal_type IN ( ''202'', ''204'', ''211'', ''212'', ''213'', ''214'', ''215'', ''216'' )
GROUP BY
it.parent_id
) AS internal_size ON base_size.index_id IN (0, 1) AND internal_size.parent_id = base_size.object_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 table_mb = ispec_ranking.table_mb,
size_rank = ispec_ranking.size_rank
FROM #index_specs ispec
INNER JOIN (
SELECT *, ROW_NUMBER() OVER(ORDER BY table_mb DESC, rows DESC, OBJECT_NAME(object_id)) AS size_rank
FROM (
SELECT object_id, SUM(alloc_mb) AS table_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.table_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,
LEN(mid.included_columns) - LEN(REPLACE(mid.included_columns, ',', '')) + 1 AS incl_col_count,
mid.included_columns,
user_seeks, user_scans, NULL AS max_days_active, /*cj1.max_days_active,*/ unique_compiles,
last_user_seek, last_user_scan,
CAST(avg_total_user_cost AS decimal(9, 3)) AS avg_total_user_cost,
CAST(avg_user_impact AS decimal(9, 3)) AS [avg_user_impact%],
system_seeks, system_scans, last_system_seek, last_system_scan,
CAST(avg_total_system_cost AS decimal(9, 3)) AS avg_total_system_cost,
CAST(avg_system_impact AS decimal(9, 3)) AS [avg_system_impact%],
mid.statement, mid.object_id, mid.index_handle
INTO #index_missing
FROM sys.dm_db_missing_index_details mid WITH (NOLOCK)
INNER JOIN sys.indexes i ON i.object_id = mid.object_id AND i.index_id IN (0, 1) AND i.data_space_id <= 32767
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)
OUTER 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.table_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
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.table_mb,
derived.Table_Name, derived.Equality_Column, derived.Equality#, derived.User_Seeks,
ISNULL((SELECT SUM(user_seeks)
FROM #index_missing im2
OUTER APPLY 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(table_mb) AS table_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 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.size_rank, ispec.alloc_mb - ispec.used_mb AS unused_mb,
CASE WHEN i.data_space_id <= 32767 THEN FILEGROUP_NAME(i.data_space_id) ELSE '{CS}' END AS main_fg_name,
CAST(NULL AS int) AS filler,
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,
key_cols AS key_cols, nonkey_cols AS nonkey_cols,
LEN(nonkey_cols) - LEN(REPLACE(nonkey_cols, ',', '')) + 1 AS nonkey_count,
CAST(NULL AS varchar(100)) AS filler2,
ius.user_seeks, ius.user_scans, --ius.user_seeks + ius.user_scans AS total_reads,
ius.user_lookups, ius.user_updates,
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,
ispec.alloc_gb AS index_gb, ispec.table_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, oa1.table_name, '~') AS index_name,
i.index_id,
ispec.approx_max_data_width AS [data_width],
CAST(CAST(ispec.used_mb AS float) * 1024.0 * 1024.0 / NULLIF(dps.row_count, 0) AS int) AS cmptd_row_size,
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,
i.fill_factor,
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,
DATEDIFF(DAY, CASE
WHEN o.create_date > @sql_startup_date AND @sql_startup_date > o.modify_date THEN o.create_date
WHEN o.create_date > @sql_startup_date AND o.modify_date > @sql_startup_date THEN o.modify_date
ELSE @sql_startup_date END, GETDATE()) AS max_days_active,
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,
ius2.row_num,
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
INNER JOIN dbo.#index_specs ispec ON
ispec.object_id = i.object_id AND
ispec.index_id = i.index_id
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
OUTER APPLY (
SELECT STUFF((
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('')
), 1, 2, '')
) AS key_cols (key_cols)
OUTER APPLY (
SELECT STUFF((
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('')
), 1, 2, '')
) 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 #index_operational_stats 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
) AS oa1
OUTER 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
WHERE
--i.object_id > 100 AND
(i.is_disabled = 0 OR @order_by IN (-1, 1)) AND
i.is_hypothetical = 0 AND
i.data_space_id <= 32767 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 'tmp[_]%' AND
--o.name NOT LIKE 'queue[_]%' AND
--(DB_NAME() IN ('master', 'msdb') OR o.name NOT LIKE 'sys%')
(@include_system_tables = 1 OR o.name NOT LIKE 'sys%')
--AND o.name NOT LIKE 'tmp%'
) AND
(@filegroup_name IS NULL OR CASE WHEN i.data_space_id <= 32767 THEN FILEGROUP_NAME(i.data_space_id) ELSE '{CS}' END LIKE @filegroup_name) AND
(@max_compression IS NULL OR ispec.max_compression <= @max_compression)
ORDER BY
--cj2.has_clustered_index, ispec.size_rank, --heaps first, by size
db_name,
--i.index_id,
--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
OPTION (MAXDOP 3, RECOMPILE)
SELECT *
FROM #index_usage
ORDER BY ident
IF @list_total_size > 1
SELECT SUM(index_gb) AS Total_Size_GB
FROM #index_usage
IF @list_filegroup_and_drive_summary = 1
SELECT
LEFT(df.physical_name, 1) AS drive,
FILEGROUP_NAME(au_totals.data_space_id) AS filegroup_name,
au_totals.total_mb AS total_fg_mb,
au_totals.used_mb AS used_fg_mb,
au_totals.total_mb - au_totals.used_mb AS free_fg_mb,
CAST(df.size / 128.0 AS decimal(9, 3)) AS file_size_mb
FROM (
SELECT
au.data_space_id,
CAST(SUM(au.total_pages) / 128.0 AS decimal(9, 3)) AS total_mb,
CAST(SUM(au.used_pages) / 128.0 AS decimal(9, 3)) AS used_mb
FROM sys.allocation_units au
INNER JOIN sys.filegroups fg ON
fg.data_space_id = au.data_space_id
GROUP BY au.data_space_id WITH ROLLUP
) AS au_totals
INNER JOIN sys.database_files df ON
df.data_space_id = au_totals.data_space_id
ORDER BY filegroup_name, drive
PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)
--**********************************************************************************************************************
/*reset settings to their original values*/SET DEADLOCK_PRIORITY @deadlock_priority
IF @transaction_isolation_level = 1
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
ELSE
IF @transaction_isolation_level = 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
ELSE
IF @transaction_isolation_level = 3
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
ELSE
IF @transaction_isolation_level = 4
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
ELSE
IF @transaction_isolation_level = 5
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
/*end of script*/
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".
September 29, 2022 at 9:08 pm
Btw, as noted by some others, I too, fortunately, have never seen any automated tool that can really properly analyze index usage and make really good suggestions for changes. You simply must have some experience and expertise to do this properly.
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".
September 30, 2022 at 5:21 am
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply