Is it possible to automatically define indexes on user data? #51

Activity

1. billhowe commented on Apr 14, 2015

   

   billhowe

   on Apr 14, 2015

   More actions

   On Mon, Apr 13, 2015 at 8:35 AM, Patrick Michaud notifications@github.com
   wrote:

   It seems very likely that some user queries will be senselessly slow, if
   the columns they're selecting on aren't indexed. Is there a way, either in
   the upload process, or as queries are run, to automatically index user data?

   Yes. SQL Server includes Dynamic Management Views that tell you a lot of
   useful tuning information.

   One thing they can do is tell you about "missing indexes"

   You should be able to run this query (which I pulled from the web, not from
   memory....) to compute the costs associated with missing indexes. Then we
   can just build, say, the top k of these. We can trigger this effort every
   day, or just do it every time a view is created and modify the query to
   look specifically at the newly created view.

   SELECT TOP 10 [Total Cost] = ROUND(avg_total_user_cost *
   avg_user_impact * (user_seeks + user_scans),0) ,
   avg_user_impact , TableName = statement , [EqualityUsage] =
   equality_columns ,
   [InequalityUsage] = inequality_columns , [Include Cloumns] = included_columns
   FROM sys.dm_db_missing_index_groups g
   INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle =
   g.index_group_handle
   INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
   ORDER BY [Total Cost] DESC;

   More info:

   https://technet.microsoft.com/en-us/library/ms345524%28v=sql.105%29.aspx

   —

   Reply to this email directly or view it on GitHub
   #51.
2. shrjain commented on Apr 15, 2015

   

   shrjain

   on Apr 15, 2015

   Contributor

   More actions

   This would be really nice to have, but what are the storage and quota implications given an 'aggressive auto index creation'? (I am assuming ofcourse that these indexes start taking up non-trivial amount of storage.)
3. billhowe commented on Apr 15, 2015

   

   billhowe

   on Apr 15, 2015

   More actions

   It's a good point, but I'd be inclined to favor fast queries for early
   users and postpone quota/space issues until we think we may have them.

   On Wed, Apr 15, 2015 at 11:23 AM, shrjain notifications@github.com wrote:

   This would be really nice to have, but what are the storage and quota
   implications given an 'aggressive auto index creation'? (I am assuming
   ofcourse that these indexes start taking up non-trivial amount of storage.)

   —
   Reply to this email directly or view it on GitHub
   #51 (comment).

   Bill Howe
   Associate Director and Senior Data Science Fellow, UW eScience Institute
   Affiliate Faculty, Computer Science & Engineering
   University of Washington
   To acknowledge eScience: "Supported in part by the University of Washington
   eScience Institute"