SQL Server Performance

Welcome to the New Brain Storm Section

Discussion in 'What is the Best Way to Identify Unused Indexes?' started by bradmcgehee, Aug 4, 2005.

  1. bradmcgehee New Member

    This is the first of a new feature to the SQL-Server-Performance.Com forum. Here, we will focus on "big issues" of interest to all SQL Server DBAs and developers. Each topic will focus on a single issue. Our goal is to work together to try and figure out the best way to solve these "big problems".

    As we get new "big issues," they will be added as new major topics.

    Please only use this area for a discussion of the relevant "big issues".

    This idea for this is from Luis Martin, who wrote an article on our first "big issue". If you would like to check out his article, you can at:

    http://www.sql-server-performance.com/lm_index_elimination_english.asp

    Frank Kalis, Satya S K Jayanty, and Luis Martin (actually, I drafted them all) have volunteered to be moderators of this new forum.

    If you have any comments or feedback about Brain Storm, please post them here:

    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=9567



    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  2. satya Moderator

    As referred by Luis in the article Index Tuning wizard will help to findout the best used indexes. In the performance perspective better indexes are the easiest fix to implement with no application changes required. Effective indexing makes the huge difference in performance.

    But it is not the end of road to fine tune the performance, it isn't easy to figure out what the best indexes will be. It still takes knowledge of lots of performance details and experience to formulate effective improvements.

    At the same time one should aware that many of the new indexes will reduce the duration of common queries with few improvements in overall system performance. Overall the result will be better from clustered index choice that can reduce CPU utilization dramatically.

    I believe there is no golden rule to drop any index as they were created to fetch better results. I would like to hear from Luis with regard to the testing he was performing to findout an ideal way to get the information.


    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. Luis Martin Moderator

    The method I write works fine to me, but take long time and hard work.

    How about to find indexes actually in cache using some sp and scheduled a job said each 5 or 10 minuts?. I think after 1 or 2 weeks, we can find witch indexes are frequently used and witch non.

    Just a tougth.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  4. merrillaldrich New Member

    I am in the midst of index tuning a database at my company, and I've found Luis' technique to be quite helpful. My task ironically seems to be mainly the *removal* of indexes created over time that are redundant, and take resources to maintain without providing benefit. I am specifically looking for indexes that are not used.

    One suggestion: I was nervous about Luis' recommendation to trace only queries that took longer than a specific time, so I used different criteria for my profiler trace --

    a. We have three client applications, so I did a separate trace for the traffic from each application, running each trace at a different time, to keep the load on the server minimal. I did this by lifting the name of the application from sysprocesses, then using that in the profiler criteria.

    b. We access the db via load-balanced web servers, so I was able to trace queries from only 1 out of 6 of those, by specifying the hostname.

    c. Instead of minimum time for a query, I set the profiler to record only statements with reads > 0, which filters out junk like "set transaction isolation level" and so on, while retaining even fast queries. My thought was that if very fast queries are using indexes, I want that to be part of my analysis.

    Also, I am running the tuner on a complete copy of the database, so it will not tamper with the production DB.

    Lastly, I wrote a series of queries that find and report indexes covering duplicate columns (submitted as an article to this site, which Brad is reviewing).

    One open question I have for everyone: I am researching the issue of covering indexes vs. separate per-column indexes, and I have not found a solid answer. Since SQL Server can now join indexes to speed a search, I am trying to determine for certain whether the structure:

    Index mytable on lastname, firstname, id, etc.

    is better or worse than

    Index mytable on lastname
    Index mytable on firstname
    Index mytable on id
    etc.

    or exactly when one or the other structure would be more useful. Any thoughts?
  5. ghemant Moderator

    Hi all,
    if we set profiler to trace indexid scan for some weekdays and then compare the scanned indexid (with this we can get indexname ) with all indexs to find unused index ! how ideal is this !? [?] [?]






    hsGoswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami
  6. Luis Martin Moderator

    Profiler don´t give a good way to trace indexed and, as far I research, 2005 does not improve that.
    This other reason why I don´t like to use profiler (as I suggest in article) because with 2005 output is totally different.

    This problem (non active indexes) is no easy. Example of this is: there is no 3rd tool witch help with that.

    One of this tools, Spotlight, show what indexes are in cache. That´s why I suppose is a good begining.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  7. bradmcgehee New Member

  8. Luis Martin Moderator

    I'm working with the following idea:

    I scheduled a job with some code to find what indexes are in buffer cache. This job run every 5 minuts.

    After a week I have a txt file, DTS to table (90000 records), with this kind of information:

    Database Table index %OfCache ObjectSizeKB %OfObject
    -------- ----- ----- -------- ------------ ---------

    With that table and other with all indexes in database, I can find those indexes where never were in Buffer Cache.

    Can I presume those indexes are candidate to remove?

    What do you think about the idea in general?





    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  9. Adriaan New Member

    That should cover the "everyday" usage. But what about tables and indexes that are accessed less regularly, like for bi-weekly or monthly reports?

    I assume indexes for temp tables won't get cached, but what happens to those for global temp tables?
  10. Luis Martin Moderator

    "That should cover the "everyday" usage. But what about tables and indexes that are accessed less regularly, like for bi-weekly or monthly reports?"

    A week was an example, no problem to wait 1 month to collect all information.

    "I assume indexes for temp tables won't get cached, but what happens to those for global temp tables?"

    I don't know.





    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  11. Luis Martin Moderator

    Well, 3 thing cross my mind.

    1) I have a very bad day.
    2) This topic was a mistake.
    3) This forum was a mistake.

    What do you think?




    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  12. bradmcgehee New Member

    I this is a good idea, both in forum and in topic. I think the problem may be that this a difficult topic with no easy answers.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  13. Luis Martin Moderator

    May be you are right.<br /><br />But, at least, I would like to discuss ideas.<br /><br />In post before I write one idea about it. Any member could said:<br /><br />1) Yes, go on because......[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />2) No, does not work because.....[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />3) You are totally crazy because.....[<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br />4) I don't understand because you English is frightful.[:I]<br /><br />But no one (except Adriaan) said something.[<img src='/community/emoticons/emotion-6.gif' alt=':(' />!]<br /><br />I hope that nobody takes offense[<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important<br />Bertrand Russell<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
  14. mmarovic Active Member

    Ok Luis, I don't want to dissapoint you, but this question needs more time then usual question. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />I'm definitly going to share my experience, so I'll come back to this issue when I have enough time to formulate my approach.
  15. merrillaldrich New Member

    Me too. I just don't have any answer ready yet <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  16. ghemant Moderator

    Hi,

    quote:Originally posted by LuisMartin

    I'm working with the following idea:

    I scheduled a job with some code to find what indexes are in buffer cache. This job run every 5 minuts.

    After a week I have a txt file, DTS to table (90000 records), with this kind of information:

    Database Table index %OfCache ObjectSizeKB %OfObject
    -------- ----- ----- -------- ------------ ---------

    With that table and other with all indexes in database, I can find those indexes where never were in Buffer Cache.

    Can I presume those indexes are candidate to remove?

    What do you think about the idea in general?





    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.
    Dear Sir, what if it happens to some financial institute or organization where they need some reports to be generated monthly and they are very very important to them and they are using that reports less regular / monthly / bio monthly , for e.g. monthly pay / monthly interest paid to customers / present or absentism of an employee !?
    so this type of tables and its index are visible on less regular / once or twice in month .

    In this scenario what should be observed with this one ?! [?]



    Regards

    Hemantgiri S. Goswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami
  17. Luis Martin Moderator

    Hemantgiri,

    I see no problem with that. I said one week just like example. Could be 2 month, or 3. Today we have nothing, so we can wait 3 month to have something.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  18. ghemant Moderator

    Dear Sir,<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />Database Table index %OfCache ObjectSizeKB %OfObject<br />-------- ----- ----- -------- ------------ ---------<br /><br />With that table and other with all indexes in database, I can find those indexes where never were in Buffer Cache.<br /><br />Can I presume those indexes are candidate to remove?<br /><br />What do you think about the idea in general?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />i find its a good idea because 2 / 3 months observervation is enough to find out those indexs that are less regular in use [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Hemantgiri S. Goswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami<br />
  19. Luis Martin Moderator

    I was reading the following article by Tom Pullen.

    http://www.sql-server-performance.com/tp_analyzing_sql_server_2000_data_caching.asp

    and made some changes, (Review article first)

    CREATE PROCEDURE prMemusageRecord
    AS

    CREATE TABLE #memusagerecord
    (
    dbid int,
    objectid int,
    indexid int,
    Buffers int,
    Dirty int,
    )

    INSERT INTO #memusagerecord EXEC('DBCC MEMUSAGE (IDS, ' + '200' + ')')

    INSERT INTO MemusageRecord
    (dbid, objectid, indexid, Buffers, Dirty)
    SELECT dbid, objectid, indexid, Buffers, Dirty
    FROM #memusagerecord

    DROP TABLE #memusagerecord
    GO

    I change DBCC to get 200 rows. (Could be a variable)

    Next change:

    CREATE PROCEDURE prCheckRecentCache @dbname SYSNAME

    AS


    DECLARE @sql VARCHAR(8000)


    SELECT @sql =
    'SELECT so.name as ''Object'', x.name,
    CASE m.indexid
    WHEN 0 THEN ''Data''
    WHEN 1 THEN ''Clustered Index''
    ELSE ''Nonclustered Index ID: ''+ CONVERT(VARCHAR, m.indexid)
    END AS ''Cache Data Type'',
    CONVERT(DECIMAL(9,2),8*m.Buffers/1024.00) AS ''MB Cached'', m.InsertDate, db_name(dbid) AS ''Database''
    FROM
    AdminLuisNueva..MemusageRecord m
    JOIN ' + @dbname + '..sysobjects so
    ON m.objectid = so.id
    JOIN ' + @dbname + '..sysindexes x
    on so.id = x.id
    WHERE x.indid = m.indexid
    AND m.dbid=db_id('''+@dbname+''')
    ORDER BY ''MB cached'' DESC '

    EXECUTE (@sql)
    GO

    In this case I change original to store Indexes Name in Cache.


    I scheduled Tom Job, to run every 5 minuts and create a new job with the following step:

    Exec prCheckRecentCache 'DatabaseName'
    Delete MemusageRecord

    Output to txt file.(append)
    I Schedule this job to run each 10 minuts.

    After 1 month, I import that txt into a table and compare with all indexes in database to find out witch ones were never in Cache.

    Tom use Pubs database, I use AdminLuisNueva.

    Any comments, code change,etc., will appreciate.





    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  20. Luis Martin Moderator

    Well, I wrote and article about it.
    It work for me.

    Is in spanish, but you can read all code without problem.

    http://www.sql-server-performance.com/articles_spanish.asp

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  21. dmaddhali New Member

    Hi Luis,
    I haved surfed the net to get as much as info regarding the unused indexes.
    your information seems to be great. I am SQL SERVER 2005 Beginner.I tried to understand your article "http://www.sql-server-performance.com/articles_spanish.asp".
    Can you please give a code to
    1)monitor the cache information regarding indexes(clustered/nonclustered) in server not database
    2)store that information in a table .
    3)compare this informatino with all the indexes in the SERVER
    4)delete all the indexes whose usage percentage is less than 20%.

    Please help me out in this as I am a beginner in SQL SERVER 2005.I also want to know that variable which needs to be changed to set DATABASE/SERVER information , usage percentage information , etc..


    Thanks in advance .
  22. Luis Martin Moderator

    1) create table:<br /><br />CREATE TABLE [dbo].[MemusageRecord] (<br />[dbid] [int] NOT NULL ,<br />[objectid] [int] NOT NULL ,<br />[indexid] [int] NOT NULL ,<br />[Buffers] [int] NOT NULL ,<br />[Dirty] [int] NOT NULL ,<br />[InsertDate] [datetime] NOT NULL<br />) ON [PRIMARY]<br />GO<br />CREATE CLUSTERED INDEX [mur001] ON [dbo].[memusagerecord]([InsertDate]) ON [PRIMARY]<br />GO<br /><br />ALTER TABLE [dbo].[memusagerecord] ADD<br />CONSTRAINT [DF_getdate_insertdate] DEFAULT (getdate()) FOR [InsertDate]<br /><br />2) Create procedure to load table:<br /><br />CREATE PROCEDURE prMemusageRecord<br />AS<br /><br />CREATE TABLE #memusagerecord<br />(<br />dbid int,<br />objectid int,<br />indexid int,<br />Buffers int,<br />Dirty int,<br />)<br /><br />INSERT INTO #memusagerecord EXEC('DBCC MEMUSAGE (IDS, ' + '200' + ')')<br /><br />INSERT INTO MemusageRecord<br />(dbid, objectid, indexid, Buffers, Dirty)<br />SELECT dbid, objectid, indexid, Buffers, Dirty<br />FROM #memusagerecord<br /><br />DROP TABLE #memusagerecord<br />GO<br /><br />3) Create procedure to get objects created step 2)<br /><br />CREATE PROCEDURE prCheckRecentCache @dbname SYSNAME<br /><br />AS<br /><br />DECLARE @sql VARCHAR(8000)<br /><br />SELECT @sql =<br />'SELECT so.name as ''Object'', x.name,<br />CASE m.indexid<br />WHEN 0 THEN ''Data''<br />WHEN 1 THEN ''Clustered Index''<br />ELSE ''Nonclustered Index ID: ''+ CONVERT(VARCHAR, m.indexid)<br />END AS ''Cache Data Type'',<br />CONVERT(DECIMAL(9,2),8*m.Buffers/1024.00) AS ''MB Cached'', m.InsertDate, db_name(dbid) AS ''Database''<br />FROM<br />AdminLuisNueva..MemusageRecord m<br />JOIN ' + @dbname + '..sysobjects so<br />ON m.objectid = so.id<br />JOIN ' + @dbname + '..sysindexes x<br />on so.id = x.id<br />WHERE x.indid = m.indexid<br />AND m.dbid=db_id('''+@dbname+''')<br />ORDER BY ''MB cached'' DESC '<br /><br />EXECUTE (@sql)<br />GO<br /><br />@dbname is your database.<br /><br />4) Now you have to create a job to run, said, each 5 minuts and store objects.<br />The main step is:<br /><br />prMemusageRecord <br /><br />5) Create new job to run each 15 minuts with output in csv format. The main step should contein:<br /><br />Use AdminCache<br />Exec prCheckRecentCache ‘Base de Datos#%92<br />Delete MemusageRecord<br /><br />6) Create a new DTS Job, to import csv output into one table.<br /><br />I suggest follow the instructions in article. (Step 7)<br /><br />7) Create one table with all indexes in database.<br /><br />Use Adminluisnueva<br />if exists (select * from dbo.sysobjects where id = object_id(N'[IndicesJHR]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)<br />drop table [IndicesJHR]<br />GO<br /><br />Create Table IndicesJHR (Filas int, Tabla varchar(50), IndicesActuales varchar(50))<br />Use JHR<br />go<br />insert into AdminLuisNueva.dbo.IndicesJHR<br />select si.rows as 'filas', SO.Name as Tabla, SI.name as 'Indices'<br />from sysobjects as SO<br /> join sysindexes as SI<br /> on SO.Id = SI.id<br /><br /><img src='/community/emoticons/emotion-11.gif' alt='8)' /> Compare all indexes in database with indexes in cache:<br /><br />select * from IndicesJHR where IndicesActuales not in ( select Indice from indexcacheJHR)order by Indicesactuales<br /><br /><br />The steps 1 to 8 cover your 1 to 3 questions.<br /><br />About 4), the procedure don't work with %. The idea is to run at least for one month to get all indexes in cache.<br />After a month step <img src='/community/emoticons/emotion-11.gif' alt='8)' /> tell you witch indexes never was used.<br /><br />Hop that help.<br /><br /><br /><br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important<br />Bertrand Russell<br /></font id="size1"><br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br /><br /><br />
  23. Hartmut5 New Member

    When I saw this topic, the first thing that popped into my mind was the Scans event classes in Profiler. I'm surprised no one responded to ghemant's suggestion (neither affirmatively nor negatively).

    This event class won't pinpoint specific queries, but it will tell you which indexes are being used, and you can then easily find out which ones are not being used. I haven't tried this out yet to see just how much data is produced, but here is how I would set up the trace:

    Events:
    Scans --> Scan: Stopped

    Data Columns:
    EventClass
    DatabaseID
    ObjectID
    IndexID
    Mode (optional)
    ApplicationName
    LoginName (optional)
    ClientProcessID (optional)
    SPID (optional)
    StartTime (optional)

    Filters:
    ApplicationName Not like SQL Profiler
    DatabaseID Greater than or equal 5 (or Equals 5 if you only want data for 1 db)
    ObjectID Greater than or equal 100 (same as checking "Exclude system IDs")

    I'm guessing this can produce huge amounts of data, especially when a lot of queries do loop joins. However, if your equipment can handle the load, then using this method will give you the data you want with very little hands-on time. If the data is stored in a database table, then you can set up a job that runs periodically which selects the unique DatabaseID, ObjectID, and IndexID values (along with a count(*) if you want), pumps the results into a summary table, and then truncates the profiler dump table.

    Once all the data is collected, it's just a matter of iterating through all the db_id's and selecting the user indexes from sysindexes that are not contained in the summarized data.

    Of course, once you know which ones are used, documentation becomes your friend. Regardless of how you go about the process of finding unused indexes, there's no use in going back over the same indexes the next go-around. It's just a matter of keeping up with the auditing of any new indexes as they come along... (and we all know how that goes...)


    -Hartmut5
  24. merrillaldrich New Member

    Does that profiler event trace also capture other types of access than scans? I.E. index seek, etc.?

    Eschew obfuscation, whilst doggedly pursuing the reduction of complexification.
  25. Luis Martin Moderator

    What I try is to find some method without profiler.
    I have 5 clients, so is imposible to me to run profiler 7x24.
    What I wrote allow me to each month test indexes used and no used.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  26. Banthor New Member

    There was a process of creating a trace on objects, writing to a database using a stored procedure that fired asynchonosly. This sproc was very light wieght and did not interfere with performace. It was then queried in a right join against the sysobjects table and objects that were not used would be come apparent.

    This worked as self auditing system against each database and would provide usage on indexes as well as views, tables, triggers, functions, etc ....

    Search the SQL Server Magazine archives for the last year. It was a cover story

    Randy Piktin
    Technology Consultant
  27. Luis Martin Moderator

    Could you post the link?

    Thanks.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  28. merrillaldrich New Member

  29. Luis Martin Moderator

    Nop I did not, but I was reading something similar for 2005.

    Thanks for sharing.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
    Leonardo Da Vinci

    Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte


    All postings are provided “AS IS” with no warranties for accuracy.



  30. FrankKalis Moderator

    In SQL Server 2005 the sys.dm_db_index_usage_stats DMV records statistics since the last restart of the server. So, if you don't restart your server quite often and an index is not in there, it is likely not to be used by the system.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Share This Page