SQLTeam.com | Weblogs | Forums

Can adding basic new Index create more problems?

sql2008r2

#1

Hi

I'm completely new to Indexing and would like to speed up some of my customers systems. My organisation made a mistake and upgraded a few of our customers from SQL 2000 to SQL 2008 before testing it fully in-house. The (new) servers are running slower than ever before as result. This is baffling the customers as you can imagine. Each customer has a two SQL user databases set up both about 1GB in size and each customer has about 30 users each.

At my organisation, there are not any currently resources to bring in a Developer or DBA to recommend what to do to speed up the new systems in place.

If I were to spend say four weeks learning as much as about SQL databases and Indexes, do you think it might be a good idea to add some indexes to see if this makes any difference. Also could I easily delete the Indexes to put the system back the way it was so no harm done? apart from it being a learning experience?

Kind Regards

James


#2

You can create some indexes to speed up things, but before you do that, I would recommend couple of other things:

a) Make sure that you are taking regular backups of your databases. This has nothing to do with performance, but is perhaps the most important job of a DBA.

b) Make sure that the indexes are properly maintained. i.e., rebuild/reorganize indexes if they are fragmented.

c) This step is optional, and some people don't like it, but use the sp_Blitz which you can find here If you choose to use it, read the documentation.

d) If all of the above does not give you any performance gains, then look at the slowest running queries and queries that are consuming most of the resources. Once you identify those, look at the query plans (or post representative queries here) and see if adding indexes will speed them up.

For each of the items that I listed above, if you search, you will find plenty of resources that will walk you through it. Before you try any of these on a production system, test it on a development system.


#3

Hi James K
Option B looks to be the safest to follow as someone with minimal experience of indexing. How can I check if an index is fragmented or needs rebuilding?

Kind Regards
James


#4

There are couple of options to determine what needs to be rebuilt and to do it.

One is to use the built-in Maintenance Plan wizard in SQL Server. From SQL Server Management Studio, if you go to YourServerName -> Management -> Maintenance Plans and right-click, you can launch a wizard that will walk you through it.

Another option is to use third-party scripts. There are well-tested and highly recommended third-party scripts. In particular Ola Hallengren's scripts and MInion scripts.

Regardless, read through some articles that explain how to do these manually (i.e., using T-SQL scripts). For example, this article.


#5

I agree with James, but I would rather do a index rebuild for all tables and update all stats if you can. Just to make sure that its not the current index or stats that affecting it. If after you have done that and performance is still bad, after that point I will consider adding or modifying indexes.

hope this helps.


#6

Hi Dennis
Definitely, if doing a reindex for all tables might be the safest way to try something out.

Is there a simple command I need to start with to reindex all or should I attempt to go through this first which Jame K also suggested :-
One is to use the built-in Maintenance Plan wizard in SQL Server.
From SQL Server Management Studio, if you go to YourServerName -> Management -> Maintenance Plans and right-click, you can launch a wizard that will walk you through it.

Cheers
Matt


#7

either way is fine, its always good to have a scheduled plan to maintain your index.

What I would do first will be rebuild all index once, and update all the stats (given your have the maintenance windows for it), then setup the schedule plan using ola hallengren;s script or any others

Here are a link with rebuilding all index here and you will just have to update stats (with fullscan if you can) after that. It might be a quick win.

Do remember to setup the schedule maintenance so that it doesn't come back.

Hope this helps


#8

If you really want to address the issues, you'll need to review the indexes themselves, starting with the clustered indexes. That's because the most critical factor in overall table performance is getting the best clustered index on every table.

Index reviews require analyzing missing index stats, index usage stats and, to a lesser extent, index operational stats. Learning that process allows you to directly address the critical problems, rather than a generic approach of rebuilding all indexes, etc.. Initially learning it would take 2-4 weeks or so. Since there are a lot of comprises involved in index selection, particularly with nonclus indexes, your judgment on indexing will continue to improve over several years.

Don't get me wrong, rebuilds have their place, but first you must make sure you have the right indexes. Consider, for example, when a critical index is missing (simply does not exist). Obviously rebuilding a bunch of other indexes, perhaps (almost) never used, won't add the index you really need for best overall performance.

I can provide a script to start with such analysis if you'd like.


#9

Hi Scott
I totally see where you are coming from. If you could provide a really basic script that maybe even I could understand then its something I'd be willing to try and post back the results possibly (minus any of the customers actual data/meta-data).
Much Appreciated
James


#10

Wat we can do is just look at one table, whichever one you consider the most critical overall for performance. That will reduce the complexity by reducing the sheer size of the output.

Otherwise, the script, and its results, aren't really "simple", they can't be. But it is a canned script, you can just run it as is, except that you'll need to adjust it to restrict it to only one table, but there's already a variable in the script for that. Check the bold parts of the script below, then run it and post the results.

Unfortunately this site's formatting is awful, since all sorts of characters seem to "cancel" pre-formatted posting, so I can't post the script with the proper indentation.

--USE [your_db_name] --make sure you are in the right db

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 @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

--!!NOTE: showing missing indexes can take some time; set to 0 if you don't want to wait!!
SET @list_missing_indexes = 1
SET @include_schema_in_table_names = 0
SET @table_name_pattern = 'put_your_table_name_here'
SET @order_by = 1
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
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


#11

Hi Scott
If I do run this scipt, how long do you think it will take and would you say I should get all the user to log out first?
Cheers
James


#12

Typically no more than 2 minutes. This can always be run live, the users can stay in. The second query is very fast and easy. It's just the first query -- missing index stats -- that can take a while because those system views are slower to join. Even then, it doesn't hurt anything that's running at all, it may just take it a couple of minutes to process. Or not, it just depends on how many missing indexes SQL thinks there are.