INDEX ON TABLES
-
Hi,,
could anyone help me how to identify which tables in your database may need additional indexes?
in my databse there are 200 tables ech contails aroud 15 columns adn more than 1 million records in each table,now i need to increase the performance of the databse.
i have been through BOL but still struggling to solve the above quiers.
thanx
-
you add indexes to enhance performance based on the queries that hit the server;
for example, if a table is often searched by the column LastName, an index on that column might benefit you.
Since this is more of a general question on how to add indexes, I think what you want to do is use the "Database Engine Tuning Advisor" in SSMS(Tools>>Database Engine Tuning Advisor)
you'll need to create a profiler/trace on the database in question, let it run for a day for example, then save the results to a file.
then you can feed that file to the above function to get some suggestions. it will create index suggestions based on how the database was queried/used;
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanx Lowel,
i selected the database whic i want to perform inthe place master.
i was wondering do i need to create a new folder in the browse for a work load file?
-
satya.sakamuri (5/11/2010)
Thanx Lowel,i selected the database whic i want to perform inthe place master.
i was wondering do i need to create a new folder in the browse for a work load file?
it's up to you. folders are for organizing multiple files, right? the first thing you need is to start creating a workload file; starting a profiler session, letting it run, and then saving the results creates that file you need.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell, when i open the sql server profiler just iam able to see only the empty page,do i need to open any file here?
sorry for botherring you i am new to this topic.
thanx
-
It sounds like you've never used SQL Server Profiler. What you should first do is open Books Online and then search for 'SQL Server Profiler'. There is a lot of information there. Then, after you've done some research and you have specific questions I'm sure there are a lot of people who would be glad to help.
-
satya.sakamuri (5/11/2010)
Hi,,could anyone help me how to identify which tables in your database may need additional indexes?
in my databse there are 200 tables ech contails aroud 15 columns adn more than 1 million records in each table,now i need to increase the performance of the databse.
i have been through BOL but still struggling to solve the above quiers.
thanx
Bringing the subject back on track...I tried to point you to the tools, but I don't think they will help you if you do not understand how to address performance issues;
There's a lot of things to consider, some at the client side and some at the SQL Server side.
One key to performance is to examine execution plans for each query that is hitting your database. from that you can determine if an index would benefit, or if the query can be re-written to be more SARG-able;
You also have to look at things that make no sense....for example instead of a SEARCH, something that loads all the records for a user to select from....loading a million records when a search is better.
Sometimes things are done on the client side code when it would be faster on the SQL server.
indexing can help, but you need to identify the poor performing SQLs and change them as well.
Depending on the severity of the performance problems, You might want to consider bringing in a consutant, rather than looking for free advice.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I am really not a fan of the Database Tuning Advisor, but if you're going to use it, don't use it blindly. Consider it's suggestions very carefully and be sure that it's not suggesting just variations on the same indexes you already have. Test the indexes to be sure they do solve problems with performance.
Performance tuning is a big topic (people have been known to write books), so take your time and address stuff very carefully.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
The sys.dm_db_index_usage_stats view exposes statistics about the usage of indexes. I don't know for certain, but I suspect this is part of what the DTA uses behind the scenes to generate usage reports. I use the DTA on occasion, but I use this script more often because it's more convenient and quick. The index usage cache gets cleared whenever the server instance is shutdown or re-started, and the cache can also be manually flushed or aged, so it provides only a hint about what is or isn't used and to what extent. However, it is useful to run this both before and after a workload, and then compare the results.
/*
Using dm_db_index_usage_stats
*/
declare @i table
(
FlagID tinyint,
FlagDesc varchar(120),
ObjectName varchar(120),
IndexName varchar(120),
user_seeks int,
user_scans int,
user_lookups int,
user_updates int,
object_id int,
index_id int
)
--Unused indexes and tables
insert into @i
SELECT
0 as FlagID,
'Unused indexes and tables' as FlagDesc,
object_name(i.object_id) AS ObjectName
, i.name as IndexName
, user_seeks
, user_scans
, user_lookups
, user_updates
, o.object_id
, i.index_id
FROM sys.indexes i
INNER JOIN sys.objects o
ON o.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats s
ON i.object_id=s.object_id
AND i.index_id=s.index_id
AND database_id = DB_ID()
WHERE objectproperty(o.object_id,'IsUserTable') = 1
AND s.index_id IS NULL
and not exists (select 1 from @i as x where x.object_id = o.object_id and x.index_id = i.index_id)
ORDER BY objectname,i.index_id,indexname ASC
--Index usage. Least used appear first.
insert into @i
SELECT
1 as FlagID,
'Least Used indexes' as FlagDesc,
object_name(s.object_id) AS ObjectName
, i.name as IndexName
, user_seeks
, user_scans
, user_lookups
, user_updates
, s.object_id
, i.index_id
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ON i.object_id = s.object_id
AND i.index_id = s.index_id
WHERE database_id = db_id ()
AND objectproperty(s.object_id,'IsUserTable') = 1
and not exists (select 1 from @i as x where x.object_id = s.object_id and x.index_id = i.index_id)
ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC
--Index scan appear more than index seeks
insert into @i
SELECT
2 as FlagID,
'Frequent Index Scans' as FlagDesc,
object_name(s.object_id) AS ObjectName
, i.name as IndexName
, user_seeks
, user_scans
, user_lookups
, user_updates
, s.object_id
, i.index_id
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ON i.object_id = s.object_id
AND i.index_id = s.index_id
WHERE database_id = db_id ()
AND objectproperty(s.object_id,'IsUserTable') = 1
AND user_scans > user_seeks
and not exists (select 1 from @i as x where x.object_id = s.object_id and x.index_id = i.index_id)
ORDER BY user_scans DESC
--Index updated more than it is used
insert into @i
SELECT
3 as FlagID,
'Frequent Index Updates' as FlagDesc,
object_name(s.object_id) AS ObjectName
, i.name as IndexName
, user_seeks
, user_scans
, user_lookups
, user_updates
, s.object_id
, i.index_id
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ON i.object_id = s.object_id
AND i.index_id = s.index_id
WHERE database_id = db_id ()
AND objectproperty(s.object_id,'IsUserTable') = 1
AND user_updates > (user_scans + user_seeks)
AND s.index_id > 1
and not exists (select 1 from @i as x where x.object_id = s.object_id and x.index_id = i.index_id)
ORDER BY user_updates DESC
select * from @i
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
eric_russell 13013 (5/11/2010)
The sys.dm_db_index_usage_stats view exposes statistics about the usage of indexes. I don't know for certain, but I suspect this is part of what the DTA uses behind the scenes to generate usage reports.DTA precedes the missing index DMV and index usage DMV by a version or two.
DTA has its own logic, based solely (afaik) on the workload submitted to it. This is why a comprehensive workload is absolutely essential if it's going to produce even slightly useful results.
fwiw, I'm with Grant. Trust DTA about as far as you can throw the server. It's way too fond of too many recommendations (especially statistics) and too wide indexes. It's better than the missing index DMVs, which are prone to suggesting multiple very similar or even redundant indexes.
As for the index usage, it's flushed when the DB is closed. Shutdown, restore, detach, offline. There's no way I know of to manually flush it. Eric, you said you can. How?
There are two DMVs that can be manually cleared - sys.dm_os_latch_stats, sys.dm_os_wait_stats
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Lowell (5/11/2010)
Depending on the severity of the performance problems, You might want to consider bringing in a consutant, rather than looking for free advice.Seconded. Benefit is that a consultant can teach while tuning, so the problems get fixed and you learn how to do performance tuning.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (5/11/2010)
...As for the index usage, it's flushed when the DB is closed. Shutdown, restore, detach, offline. There's no way I know of to manually flush it. Eric, you said you can. How?
There are two DMVs that can be manually cleared - sys.dm_os_latch_stats, sys.dm_os_wait_stats
I admit, I now can't think of a way to flush the DMV statistics.
If one were to run the DMV script soon before and after a workload, it should present a pretty good view of index usage. There is no reason for the database to get taken offline, detached, etc. in the middle of the day without the DBA knowing it.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
-
eric_russell 13013 (5/11/2010)
GilaMonster (5/11/2010)
...As for the index usage, it's flushed when the DB is closed. Shutdown, restore, detach, offline. There's no way I know of to manually flush it. Eric, you said you can. How?
There are two DMVs that can be manually cleared - sys.dm_os_latch_stats, sys.dm_os_wait_stats
I admit, I now can't think of a way to flush the DMV statistics.
If one were to run the DMV script soon before and after a workload, it should present a pretty good view of index usage. There is no reason for the database to get taken offline, detached, etc. in the middle of the day without the DBA knowing it.
Whether the DBA knows it or not, that data can get changed by circumstances. It's best that you know that fact in order to gauge best whether or not the index usage is an accurate reflection of your system. Also, know which indexes are getting used doesn't help that much in determining which ones to create.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
-
Hi
/*
Using dm_db_index_usage_stats
*/
declare @i table
(
FlagID tinyint,
FlagDesc varchar(120),
ObjectName varchar(120),
IndexName varchar(120),
user_seeks int,
user_scans int,
user_lookups int,
user_updates int,
object_id int,
index_id int
)
--Unused indexes and tables
insert into @i
SELECT
0 as FlagID,
'Unused indexes and tables' as FlagDesc,
object_name(i.object_id) AS ObjectName
, i.name as IndexName
, user_seeks
, user_scans
, user_lookups
, user_updates
, o.object_id
, i.index_id
FROM sys.indexes i
INNER JOIN sys.objects o
ON o.object_id = i.object_id
LEFT JOIN sys.dm_db_index_usage_stats s
ON i.object_id=s.object_id
AND i.index_id=s.index_id
AND database_id = DB_ID()
WHERE objectproperty(o.object_id,'IsUserTable') = 1
AND s.index_id IS NULL
and not exists (select 1 from @i as x where x.object_id = o.object_id and x.index_id = i.index_id)
ORDER BY objectname,i.index_id,indexname ASC
--Index usage. Least used appear first.
insert into @i
SELECT
1 as FlagID,
'Least Used indexes' as FlagDesc,
object_name(s.object_id) AS ObjectName
, i.name as IndexName
, user_seeks
, user_scans
, user_lookups
, user_updates
, s.object_id
, i.index_id
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ON i.object_id = s.object_id
AND i.index_id = s.index_id
WHERE database_id = db_id ()
AND objectproperty(s.object_id,'IsUserTable') = 1
and not exists (select 1 from @i as x where x.object_id = s.object_id and x.index_id = i.index_id)
ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC
--Index scan appear more than index seeks
insert into @i
SELECT
2 as FlagID,
'Frequent Index Scans' as FlagDesc,
object_name(s.object_id) AS ObjectName
, i.name as IndexName
, user_seeks
, user_scans
, user_lookups
, user_updates
, s.object_id
, i.index_id
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ON i.object_id = s.object_id
AND i.index_id = s.index_id
WHERE database_id = db_id ()
AND objectproperty(s.object_id,'IsUserTable') = 1
AND user_scans > user_seeks
and not exists (select 1 from @i as x where x.object_id = s.object_id and x.index_id = i.index_id)
ORDER BY user_scans DESC
--Index updated more than it is used
insert into @i
SELECT
3 as FlagID,
'Frequent Index Updates' as FlagDesc,
object_name(s.object_id) AS ObjectName
, i.name as IndexName
, user_seeks
, user_scans
, user_lookups
, user_updates
, s.object_id
, i.index_id
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ON i.object_id = s.object_id
AND i.index_id = s.index_id
WHERE database_id = db_id ()
AND objectproperty(s.object_id,'IsUserTable') = 1
AND user_updates > (user_scans + user_seeks)
AND s.index_id > 1
and not exists (select 1 from @i as x where x.object_id = s.object_id and x.index_id = i.index_id)
ORDER BY user_updates DESC
select * from @i
if i run the above script inmy sql query with selecting perticular database ....will the data in tables effect?becaz those are production live tables in that database.
could you plese clear me this doubt before i run the DTA script.
-
:):)
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply