July 17, 2014 at 11:41 am
Just a little information about the DB:
DB size = 10GB
Number of tables = 1476
This simple query tell me how many indexes I have in the DB.
SELECT
so.name AS TableName
, si.name AS IndexName
, si.type_desc AS IndexType
FROM
sys.indexes si
JOIN sys.objects so ON si.[object_id] = so.[object_id]
WHERE
so.type = 'U' --Only get indexes for User Created Tables
AND si.name IS NOT NULL
ORDER BY
so.name, si.type
Total Number of Indexes = 2342
This query gives me a list of unused indexes.
SELECT
DB_NAME() AS [DatabaseName]
, SCHEMA_NAME([sObj].[schema_id]) AS [SchemaName]
, [sObj].[name] AS [ObjectName]
, CASE
WHEN [sObj].[type] = 'U' THEN 'Table'
WHEN [sObj].[type] = 'V' THEN 'View'
END AS [ObjectType]
, [sIdx].[index_id] AS [IndexID]
, ISNULL([sIdx].[name], 'N/A') AS [IndexName]
, CASE
WHEN [sIdx].[type] = 0 THEN 'Heap'
WHEN [sIdx].[type] = 1 THEN 'Clustered'
WHEN [sIdx].[type] = 2 THEN 'Nonclustered'
WHEN [sIdx].[type] = 3 THEN 'XML'
WHEN [sIdx].[type] = 4 THEN 'Spatial'
WHEN [sIdx].[type] = 5 THEN 'Reserved for future use'
WHEN [sIdx].[type] = 6 THEN 'Nonclustered columnstore index'
END AS [IndexType]
FROM
[sys].[indexes] AS [sIdx]
INNER JOIN [sys].[objects] AS [sObj]
ON [sIdx].[object_id] = [sObj].[object_id]
WHERE
NOT EXISTS (
SELECT *
FROM [sys].[dm_db_index_usage_stats] AS [sdmfIUS]
WHERE
[sIdx].[object_id] = [sdmfIUS].[object_id]
AND [sIdx].[index_id] = [sdmfIUS].[index_id]
AND [sdmfIUS].[database_id] = DB_ID()
)
AND [sObj].[type] IN ('U','V') -- Look in Tables & Views
AND [sObj].[is_ms_shipped] = 0x0 -- Exclude System Generated Objects
AND [sIdx].[is_disabled] = 0x0 -- Exclude Disabled Indexes
Total number of unused indexes = 1829
Question:
1. Is this normal?
2. About the unused index. Does it tell me the list of indexes which are never used or is it a list of unused indexes which are not used recently.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
July 17, 2014 at 11:54 am
1400 tables, 2300 indexes. That's about 1.5 indexes per table. Actually kind of a low number, IMHO.
Does this seem normal? Define normal. What works fine for you and your environment may work terribly for me and my environment.
Since the DB is only 10 GB, you may not see a need for more indexes. At this size, unless you have a server with 512 mb of RAM, everything should be fast!
As for the unused indexes, these statistics are from the last time the server was re-booted. So, if you re-booted yesterday, then these numbers may be misleading.
The question I have is where did these indexes come from? The developers? A DBA? How much analysis was completed before indexes were put in place?
And, what exactly are you trying to determine? Are you seeing performance issues? Are you simply trying to learn more about your server and databases?
Download Brent Ozar's Blitz Index.
http://www.brentozar.com/blitzindex/
This is a good starting point when attempting to learn and analyze indexes in a database.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 17, 2014 at 12:03 pm
Since the DB is only 10 GB, you may not see a need for more indexes. At this size, unless you have a server with 512 mb of RAM, everything should be fast!
Server has 32GB of RAM and 8GB is assigned to this DB which to me is a lot.
As for the unused indexes, these statistics are from the last time the server was re-booted. So, if you re-booted yesterday, then these numbers may be misleading.
Got it. It was rebooted 5 days ago.
The question I have is where did these indexes come from? The developers? A DBA? How much analysis was completed before indexes were put in place?
Don't know.
And, what exactly are you trying to determine? Are you seeing performance issues? Are you simply trying to learn more about your server and databases?
I am just trying to learn about the environment. I am not seeing any performance issues other than some queries are running, generating 1 or 2 columns, going through 13000+ logical reads so I am assuming this is bad.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
July 17, 2014 at 12:10 pm
New Born DBA (7/17/2014)
Since the DB is only 10 GB, you may not see a need for more indexes. At this size, unless you have a server with 512 mb of RAM, everything should be fast!
Server has 32GB of RAM and 8GB is assigned to this DB which to me is a lot.
Uh, when you say "8GB is assigned to this DB", how are you determining that? Do you mean this INSTANCE?
As for the unused indexes, these statistics are from the last time the server was re-booted. So, if you re-booted yesterday, then these numbers may be misleading.
Got it. It was rebooted 5 days ago.
I think your query that checks for unused indexes may be faulty.
This is the one I use, I'm sure that credit is due for this, but I cannot remember where this came from.
SELECT '[' + DB_NAME() + '].[' + SU.[name] + '].[' + O.[name] + ']' [statement],
I.[name] [index_name],
IUS.[user_seeks] + IUS.[user_scans] + IUS.[user_lookups] [user_reads],
IUS.[user_updates] [user_writes],
SUM(SP.rows) [total_rows]
FROM sys.dm_db_index_usage_stats IUS
INNER JOIN sys.indexes I ON IUS.[object_id] = I.[object_id]
AND I.[index_id] = IUS.[index_id]
INNER JOIN sys.partitions SP ON IUS.[object_id] = SP.[object_id]
AND SP.[index_id] = IUS.[index_id]
INNER JOIN sys.objects O ON IUS.[object_id] = O.[object_id]
INNER JOIN sys.sysusers SU ON O.[schema_id] = SU.[UID]
WHERE IUS.[database_id] = DB_ID() -- current database only
AND OBJECTPROPERTY(IUS.[object_id], 'IsUserTable') = 1
AND IUS.[index_id] > 0
GROUP BY SU.[name], O.[name], I.[name], IUS.[user_seeks] + IUS.[user_scans] + IUS.[user_lookups], IUS.[user_updates]
HAVING IUS.[user_seeks] + IUS.[user_scans] + IUS.[user_lookups] = 0
ORDER BY IUS.[user_updates] DESC, SU.[name], O.[name], I.[name]
And, what exactly are you trying to determine? Are you seeing performance issues? Are you simply trying to learn more about your server and databases?
I am just trying to learn about the environment. I am not seeing any performance issues other than some queries are running, generating 1 or 2 columns, going through 13000+ logical reads so I am assuming this is bad.
Probably not a good assumption. 13000+ may be normal for your system.
The answer is, "It depends".
What is the query doing? Can you post the execution plan?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 17, 2014 at 12:45 pm
Michael L John (7/17/2014)
Uh, when you say "8GB is assigned to this DB", how are you determining that? Do you mean this INSTANCE?
Sorry my bad. 8GB is assigned to the instance and there is only 1 DB.
Michael L John (7/17/2014)
I think your query that checks for unused indexes may be faulty.
This is the one I use, I'm sure that credit is due for this, but I cannot remember where this came from.
SELECT '[' + DB_NAME() + '].[' + SU.[name] + '].[' + O.[name] + ']' [statement],
I.[name] [index_name],
IUS.[user_seeks] + IUS.[user_scans] + IUS.[user_lookups] [user_reads],
IUS.[user_updates] [user_writes],
SUM(SP.rows) [total_rows]
FROM sys.dm_db_index_usage_stats IUS
INNER JOIN sys.indexes I ON IUS.[object_id] = I.[object_id]
AND I.[index_id] = IUS.[index_id]
INNER JOIN sys.partitions SP ON IUS.[object_id] = SP.[object_id]
AND SP.[index_id] = IUS.[index_id]
INNER JOIN sys.objects O ON IUS.[object_id] = O.[object_id]
INNER JOIN sys.sysusers SU ON O.[schema_id] = SU.[UID]
WHERE IUS.[database_id] = DB_ID() -- current database only
AND OBJECTPROPERTY(IUS.[object_id], 'IsUserTable') = 1
AND IUS.[index_id] > 0
GROUP BY SU.[name], O.[name], I.[name], IUS.[user_seeks] + IUS.[user_scans] + IUS.[user_lookups], IUS.[user_updates]
HAVING IUS.[user_seeks] + IUS.[user_scans] + IUS.[user_lookups] = 0
ORDER BY IUS.[user_updates] DESC, SU.[name], O.[name], I.[name]
Gave me 113 rows.
Michael L John (7/17/2014)
Probably not a good assumption. 13000+ may be normal for your system.
The answer is, "It depends".
What is the query doing? Can you post the execution plan?
See the attachment please.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
July 17, 2014 at 2:08 pm
The execution plan tells it all. This query is performing a table scan. In order to get one row, the query optimizer needs to look at all of the rows.
In this case, you probably NEED an new index.
It's recommending a new index with a single field. This may be a good index for the entire system, it may only be a good index for this query.
You need to dig into your system, and perform some analysis of the index usage.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 17, 2014 at 2:19 pm
Michael L John (7/17/2014)
The execution plan tells it all. This query is performing a table scan. In order to get one row, the query optimizer needs to look at all of the rows.In this case, you probably NEED an new index.
It's recommending a new index with a single field. This may be a good index for the entire system, it may only be a good index for this query.
You need to dig into your system, and perform some analysis of the index usage.
I created a nonclustered index and this is what I got.
Please see the attachments;
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
July 17, 2014 at 2:44 pm
There's no "right" number of indexes per table. It depends entirely on usage.
You need to consider at least SQL's index usage stats and missing index stats. [There are some other things you can look at, esp. for large (2M+row) tables, but we can skip any of that for now.]
After reviewing the code marked in bold, run the queries below to see missing index and index usage stats.
USE [<your_db_name>]
SET DEADLOCK_PRIORITY LOW --probably irrelevant, but just in case
DECLARE @list_missing_indexes bit
DECLARE @table_name_pattern sysname
--NOTE: showing missing indexes can take some time; set to 0 if you don't want to wait.
SET @list_missing_indexes = 1
SET @table_name_pattern = '%' --'%'=all tables.
--SET @table_name_pattern = '%'
PRINT 'Started @ ' + CONVERT(varchar(30), GETDATE(), 120)
IF @list_missing_indexes = 1
BEGIN
SELECT
GETDATE() AS capture_date,
DB_NAME(mid.database_id) AS Db_Name,
dps.row_count,
OBJECT_NAME(mid.object_id /*, mid.database_id*/) AS Table_Name,
mid.equality_columns, mid.inequality_columns, mid.included_columns,
user_seeks, user_scans, ca1.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
FROM sys.dm_db_missing_index_details mid WITH (NOLOCK)
CROSS APPLY (
SELECT DATEDIFF(DAY, create_date, GETDATE()) AS max_days_active FROM sys.databases WHERE name = 'tempdb'
) AS ca1
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)
--order by
--DB_NAME, Table_Name, equality_columns
WHERE
1 = 1
AND mid.database_id = DB_ID() --only current db
AND OBJECT_NAME(mid.object_id) LIKE @table_name_pattern
--AND mid.object_id IN (OBJECT_ID('<table_name_1>'), OBJECT_ID('<table_name_2>'))
ORDER BY
--avg_total_user_cost * (user_seeks + user_scans) DESC,
Db_Name, Table_Name, equality_columns, inequality_columns
END --IF
-- list index usage stats (seeks, scans, etc.)
SELECT
ius2.row_num, DB_NAME() AS db_name,
CASE WHEN i.name LIKE ca2.table_name + '%'
THEN '~' + SUBSTRING(i.name, LEN(ca2.table_name) + 1 +
CASE WHEN SUBSTRING(i.name, LEN(ca2.table_name) + 1, 1) = '_' THEN
CASE WHEN SUBSTRING(i.name, LEN(ca2.table_name) + 2, 1) = '_' THEN 2 ELSE 1 END
ELSE 0 END, 200)
ELSE i.name END AS index_name,
CASE WHEN i.is_unique = 0 THEN 'N' ELSE 'Y' END + '.' +
CASE WHEN i.is_primary_key = 0 AND i.is_unique_constraint = 0 THEN 'N' ELSE 'Y' END AS [uniq?],
ca2.table_name,
i.index_id, --ius.user_seeks + ius.user_scans AS total_reads,
dps.row_count,
SUBSTRING(key_cols, 3, 8000) AS key_cols, SUBSTRING(nonkey_cols, 3, 8000) AS nonkey_cols,
ius.user_seeks, ius.user_scans, ius.user_lookups, ius.user_updates,
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 > ca1.sql_startup_date THEN o.create_date
ELSE ca1.sql_startup_date END, GETDATE()) AS max_days_active,
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
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 WHERE name = 'tempdb'
) AS ca1
CROSS APPLY (
SELECT OBJECT_NAME(i.object_id/*, DB_ID()*/) AS table_name
) AS ca2
OUTER APPLY (
SELECT
', ' + COL_NAME(object_id, ic.column_id)
FROM sys.index_columns ic
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(object_id, ic.column_id)
FROM sys.index_columns ic
WHERE
ic.key_ordinal = 0 AND
ic.object_id = i.object_id AND
ic.index_id = i.index_id
ORDER BY
COL_NAME(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
WHERE
is_disabled = 0
GROUP BY
referenced_object_id
) AS fk ON
fk.referenced_object_id = i.object_id
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%'
)
--AND OBJECT_NAME(i.object_id /*, DB_ID()*/) IN ('tbl1', 'tbl2', 'tbl3')
ORDER BY
--row_count DESC,
--ius.user_scans DESC,
--ius2.row_num, --user_scans&|user_seeks
-- list clustered index first, if any, then other index(es)
db_name, table_name,
CASE WHEN i.index_id IN (0, 1) THEN 1 ELSE 2 END,
key_cols
PRINT 'Ended @ ' + CONVERT(varchar(30), GETDATE(), 120)
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply