Post reply

A good way to ascertain the usage of a set of views in a DB

  • March 17, 2011 at 10:17 am

    #389073

    Hi All,

    I have a bit of a newbie question for you

    I have a database upon which someone has run the DTA and created a large number of Views on the database and I was looking for a quick way to capture how often if at all those views were being used so that I could make a judgement on if i should delete them or not

    Any help or Advice would be great

    Thanks

    Simon

  • March 17, 2011 at 11:06 am

    #1300083

    The only idea I have right now is to check used / unused indexes scripts based on the clustered index. That should give a fair-ish assessment.

    The real question is was the application retrofitted to use those views. If it was not then maybe you have a clearer shot at removing them.

  • March 17, 2011 at 11:09 am

    #1300086

    Hi

    AFAIK the App was not modified to use the new Views

    I did think about using Index Usage DMV queries to see if they were being used or not

    I will check out index usage DMV queries to see if they are being used

    Cheers for the quick reply

    S

  • March 17, 2011 at 11:15 am

    #1300091

    Normal views or indexed views? What edition of SQL?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 17, 2011 at 11:18 am

    #1300095

    Hi

    Sql 2005 Ent Edition 32 bit latest SP

    Normal Views

  • March 17, 2011 at 11:21 am

    #1300098

    simonjday (3/17/2011)

    Hi

    Sql 2005 Ent Edition 32 bit latest SP

    Normal Views

    What's the point of creating normal views to tune the db????

    More to the point, you won't have ANY gain if you delete those.

    The only possible gain here is to stop updating useless code. In that regard the question to ask is how long will it take to figure out what I can delete vs how long it takes to maintain them anyways. Do the math and decide from that.

  • March 17, 2011 at 11:22 am

    #1300099

    Normal views won't appear in any of the index-related DMVs as they are just saved select statements. If they are normal views and the app has not been changed to use them, you can drop them all safely. Double check that the app really doesn't use them though.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 17, 2011 at 11:29 am

    #1300105

    I agree that it don't make any sense to have them to tune the db

    This is a DB that we have a performance issue with and when i have come to look at the DB it appears that a previous individual has had fun with the DTA as all the views have the _dta prefix and the default DTA naming conventions which helps me no end it trying to work why the heck they were created in the first place 🙂

    There are about 1600 of them and they seem to serve no purpose what so ever

    I will have to setup some tracing in the app to check that they are not being used

    Thanks to all for the input and confirming what i initially thought

    Cheers

    S

  • March 17, 2011 at 11:29 am

    #1300106

    Ninja's_RGR'us (3/17/2011)

    simonjday (3/17/2011)

    Hi

    Sql 2005 Ent Edition 32 bit latest SP

    Normal Views

    What's the point of creating normal views to tune the db????

    DTA shouldn't create normal views. IT can create indexed views, if the option is selected, but not normal ones.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 17, 2011 at 11:36 am

    #1300114

    simonjday (3/17/2011)

    I agree that it don't make any sense to have them to tune the db

    This is a DB that we have a performance issue with and when i have come to look at the DB it appears that a previous individual has had fun with the DTA as all the views have the _dta prefix and the default DTA naming conventions which helps me no end it trying to work why the heck they were created in the first place 🙂

    There are about 1600 of them and they seem to serve no purpose what so ever

    I will have to setup some tracing in the app to check that they are not being used

    Thanks to all for the input and confirming what i initially thought

    Cheers

    S

    I know this is counterintuitive, but trying to clean that mess out is actually a waste of time, unless proven otherwise.

    The best case scenario is that you clear items from the catalog after wasting hours if not days making sure you're not bugging the applicationSSSS that use your system.

    One likely scenario is you screw something up is that part(s) of the system will stop working. This is where you can waste days and weeks trying to undo that damage. Where I've been bitten is some rarely used end of month / year / whatever processes that were fired manually once in a while.

    So again if you're trying to solve a performance problem, I'd stay away from refactoring that part of the system, it won't get you anywhere.

  • March 17, 2011 at 11:38 am

    #1300115

    Thanks for the advice

    I will have to look into the tracing from the App side of things

  • March 17, 2011 at 11:40 am

    #1300121

    Check out this amazing article by Gail, that's what I do to solve performance issues. Usually takes less than 1 day of work over 1 week on most systems...

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

  • March 17, 2011 at 11:47 am

    #1300127

    Cheers

    That looks like a very useful article i will give it a go

    Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply