Nice feature for next SQL server version

Wouldn't it be nice if SQL build statistics which automatically would help
the system create indexes at runtime?
It defenitely would help lazy programmers :)I would add some statistics to help us know whether indexes are used or not (so we can drop the
indexes not being used). Sounds like a case for sqlwish@.microsoft.com. :-)
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
news:%23$i%23RbCtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> Wouldn't it be nice if SQL build statistics which automatically would help
> the system create indexes at runtime?
> It defenitely would help lazy programmers :)
>|||I take it you're aware of the Index Tuning Wizard which semi-automates the
process of identifying useful indexes and creating them.
--
David Portas
--
Please reply only to the newsgroup
--
"Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
news:%23$i%23RbCtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> Wouldn't it be nice if SQL build statistics which automatically would help
> the system create indexes at runtime?
> It defenitely would help lazy programmers :)
>|||How often have you actually gotten something useful out of the Index
Tuning Wizard? I try it from time to time when I'm stumped and 99% of the
time it ends up either not being able to do anything with the query or
recommends a gigantic composite index that slows performance... Very
annoying.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:88WdnexFd47lWFmiRVn-sQ@.giganews.com...
> I take it you're aware of the Index Tuning Wizard which semi-automates the
> process of identifying useful indexes and creating them.
> --
> David Portas
> --
> Please reply only to the newsgroup|||> How often have you actually gotten something useful out of the Index
> Tuning Wizard?
Errr.. never. I've hardly ever used it. I expect it can give useful results
if supplied with a suitable Profiler trace but I expect most full-time
administrators and designers prefer to do this stuff for themselves based on
business requirements and their own research.
I don't think generating indexes at "runtime" could offer anything better.
Creating indexes without the opportunity for review by the
administrator/designer sounds pretty dangerous to me - if that's what the OP
was suggesting.
--
David Portas
--
Please reply only to the newsgroup
--|||Yeah! Something like a usage counter for each index would be great.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%23gTl40CtDHA.684@.TK2MSFTNGP09.phx.gbl...
> I would add some statistics to help us know whether indexes are used or
not (so we can drop the
> indexes not being used). Sounds like a case for sqlwish@.microsoft.com. :-)
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
> news:%23$i%23RbCtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> > Wouldn't it be nice if SQL build statistics which automatically would
help
> > the system create indexes at runtime?
> > It defenitely would help lazy programmers :)
> >
>|||"Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
news:%23$i%23RbCtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> Wouldn't it be nice if SQL build statistics which automatically would help
> the system create indexes at runtime?
I'd absolutely hate it. Well building a table of suggested indexes might be
nice, but making the change automagically would introduce more problems than
it was worth IMO.
> It defenitely would help lazy programmers :)
Niall Litchfield
Oracle DBA
Audit Commission UK|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:88WdnexFd47lWFmiRVn-sQ@.giganews.com...
> I take it you're aware of the Index Tuning Wizard which semi-automates the
> process of identifying useful indexes and creating them.
I do not agree quite.
I'd use it personally on projects, but the problem is I DO NOT manage the
SQL server, so I should instruct the DBADMIN to optimize or start an
optimization session. Of course, I include indexes at design but I might
forget some of them and more, I have some sort of SQL stored procedures,
which can sort and filter on ANY column. So if the -client- uses a lot of
queries of a specific type, SQL Server should decide at a certain defined
point, to add the index.
It would be unwise of me to add indexes for all possible combinations on
deployment.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
> "Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
> news:%23$i%23RbCtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> > Wouldn't it be nice if SQL build statistics which automatically would
help
> > the system create indexes at runtime?
> > It defenitely would help lazy programmers :)
> >
>|||"Niall Litchfield" <n-litchfield@.audit-commission.gov.uk> wrote in message
news:3fc5ee5a$0$9383$ed9e5944@.reading.news.pipex.net...
> "Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
> news:%23$i%23RbCtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> > Wouldn't it be nice if SQL build statistics which automatically would
help
> > the system create indexes at runtime?
> I'd absolutely hate it. Well building a table of suggested indexes might
be
> nice, but making the change automagically would introduce more problems
than
> it was worth IMO.
Allrighty,
in case we can define withhold parameters for auto-indexing and such it
would be no problem. The designer still has to have control over it but the
designer should not be too dependend on the dbadmin for helping or
co-operating on optimizing (and vice-versa)|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:tPGdne5sW-tVTVmiRVn-tA@.giganews.com...
> > How often have you actually gotten something useful out of the Index
> > Tuning Wizard?
> Errr.. never. I've hardly ever used it. I expect it can give useful
results
> if supplied with a suitable Profiler trace but I expect most full-time
> administrators and designers prefer to do this stuff for themselves based
on
> business requirements and their own research.
> I don't think generating indexes at "runtime" could offer anything better.
> Creating indexes without the opportunity for review by the
> administrator/designer sounds pretty dangerous to me - if that's what the
OP
> was suggesting.
Again, I do not suppose that SQL blindly will do that, I suggest a feature
that can be enabled per table, so if a table consists of 20 columns,
potentially, it could create 20 or more (if more columns are combined)
indexes SQL should decide at some points whether or not to create one.
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>|||"Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
news:eOIchwxvDHA.2180@.TK2MSFTNGP09.phx.gbl...
> "Niall Litchfield" <n-litchfield@.audit-commission.gov.uk> wrote in message
> news:3fc5ee5a$0$9383$ed9e5944@.reading.news.pipex.net...
> > "Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in
message
> > news:%23$i%23RbCtDHA.1884@.TK2MSFTNGP10.phx.gbl...
> > > Wouldn't it be nice if SQL build statistics which automatically would
> help
> > > the system create indexes at runtime?
> >
> > I'd absolutely hate it. Well building a table of suggested indexes might
> be
> > nice, but making the change automagically would introduce more problems
> than
> > it was worth IMO.
> Allrighty,
> in case we can define withhold parameters for auto-indexing and such it
> would be no problem. The designer still has to have control over it but
the
> designer should not be too dependend on the dbadmin for helping or
> co-operating on optimizing (and vice-versa)
You seem to be suggesting that the fewer times the dba and the system
designer have to speak the better. I'd have though the polar opposite were
true. design and admin are tasks that are inextricably linked.
Niall Litchfield
Oracle DBA
Audit Commission UK|||"Niall Litchfield" <n-litchfield@.audit-commission.gov.uk> wrote in message
news:3fd87eb4$0$9385$ed9e5944@.reading.news.pipex.net...
> "Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
> news:eOIchwxvDHA.2180@.TK2MSFTNGP09.phx.gbl...
> > in case we can define withhold parameters for auto-indexing and such it
> > would be no problem. The designer still has to have control over it but
> the
> > designer should not be too dependend on the dbadmin for helping or
> > co-operating on optimizing (and vice-versa)
> You seem to be suggesting that the fewer times the dba and the system
> designer have to speak the better. I'd have though the polar opposite were
> true. design and admin are tasks that are inextricably linked.
I absolutely do not say that there is a **one fits all* way of working. In
some cases you are right. In other cases, you don't want to have long talks
with the dbadmin that you even don't know, you give him a list of options
(db size, backup needs, etc) but you don't want to beg to get him or her
optimize *your app* if he has no budget for it and you have no priviliges to
start a tracelog.
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
>|||"Niall Litchfield" <n-litchfield@.audit-commission.gov.uk> wrote in message
news:3fd87eb4$0$9385$ed9e5944@.reading.news.pipex.net...
> "Egbert Nierop (MVP for IIS)" <egbert_nierop@.nospam.com> wrote in message
> news:eOIchwxvDHA.2180@.TK2MSFTNGP09.phx.gbl...
> You seem to be suggesting that the fewer times the dba and the system
> designer have to speak the better. I'd have though the polar opposite were
> true. design and admin are tasks that are inextricably linked.
ps; I'd like to stress again my original point. I have an app (CRM like)
where you *cannot* optimize everything on forehand! The app can only be
optimized in combination of user statistics, so if the user likes to search
on postalcode only or on his own favorite fields, SQL server should decide
for that environment, to add a new index. My app is that flexible that the
user can sort and search on any column. You don't want to add indexes for
any column do you? :)