How to Use sp_BlitzIndex

00:07:22
https://www.youtube.com/watch?v=8Wo5M7kYO20

摘要

TLDRA función SP Blitz Index é unha ferramenta gratuíta que permite realizar unha comprobación de saúde nos índices das túas bases de datos. O vídeo explica como executar esta función, que tipos de parámetros utilizar e que tipo de resultados se poden esperar. Cando executas SP Blitz Index, recibe unha lista priorizada de problemas potenciais de rendemento nos índices, inclúe índices que faltan e índices que non se están a usar. A función tamén pode darte os comandos exactos para crear índices pero require que o usuario revise antes de aplicalos. Ademais, destacan varios modos para analizar os datos e a necesidade de coidar os avisos sobre índices innecesarios ou duplicados. Tamén se menciona o uso de SP Blitz Cache para axudar a entender que consultas beneficiaríanse deses índices.

心得

  • 🛠️ SP Blitz Index é unha ferramenta de análise de saúde de índices nas bases de datos.
  • 📊 Proporciona unha lista priorizada de problemas de rendemento sobre os índices.
  • 🔍 Axuda a identificar índices que faltan ou que non se usan.
  • 📄 Ofrece recomendacións detalladas sobre a creación de índices.
  • 🧠 Usa unha aproximación psicolóxica para nomear comportamentos inusuais de índices.
  • ⏱️ Pode analizar moitas bases de datos ao mesmo tempo pero require tempo.
  • 📈 Axuda para o axuste de rendemento dos índices en SQL Server.
  • 🔗 Inclúe ligazóns para máis información sobre os avisos detectados.
  • 👩‍💻 Precisa intervención manual para revisar e aplicar os índices recomendados.
  • 📙 Utiliza SP Blitz Cache para coñecer consultas que se beneficiarían dos índices.

时间轴

  • 00:00:00 - 00:07:22

    Está a preguntarse como están a funcionar os seus índices e se ten os índices axeitados deseñados para a súa base de datos ou se está desesperadamente a faltar algúns índices. Aquí é onde entra SP Blitz Index, un script gratuíto que lle proporciona un control de saúde nas súas bases de datos. Cando obtén SP Blitz Index só ten que executar para crear o procedemento almacenado, e pódese situar en calquera base de datos. Cambiando de ventá, móstrase como configurar algúns parámetros para SP Blitz Index, como por exemplo "get all databases" que analiza a saúde dos índices en todo o seu SQL Server. Se ten máis de 50 bases de datos, necesitará usar o parámetro "bring the pain" se desexa analizar máis de 50 porque levará moito tempo.

思维导图

Mind Map

常见问题

  • What is SP Blitz Index?

    SP Blitz Index is a script that provides a health check for databases by analyzing index usage and missing indexes.

  • How do I execute SP Blitz Index?

    You can execute it by creating the stored procedure in your database, typically in the master database, and then running it with desired parameters.

  • What does 'Bring The Pain' parameter do?

    It allows analysis of more than 50 databases at once, which can take a long time to process.

  • What information does SP Blitz Index provide?

    It provides a prioritized list of index performance issues, including missing indexes and unused indexes.

  • Can SP Blitz Index create indexes automatically?

    No, it provides recommendations, but you need to manually review and create the indexes.

  • How does SP Blitz Cache complement SP Blitz Index?

    SP Blitz Cache analyzes query plans and helps identify which queries would benefit from certain indexes.

  • What are some warnings provided by SP Blitz Index?

    Warnings include unused indexes, borderline duplicate indexes, and large tables with missing indexes.

  • Can SP Blitz Index help with index tuning?

    Yes, it provides details like index usage, missing indexes, and fields to help with index tuning.

  • What are some unique names used for index warnings in SP Blitz Index?

    Names like 'multiple personalities','hoarder', and 'workaholics' are used to describe unusual index patterns.

  • Where can I find more information on SP Blitz Index warnings?

    Each warning has a URL provided in the SP Blitz Index output with more details.

查看更多视频摘要

即时访问由人工智能支持的免费 YouTube 视频摘要！
字幕
en
自动滚动:
  • 00:00:00
    so you're wondering how your indexes are
  • 00:00:02
    doing like do you have the right indexes
  • 00:00:05
    designed for a database are you
  • 00:00:06
    desperately missing some indexes that's
  • 00:00:09
    where SP Blitz index comes in this
  • 00:00:12
    totally free script gives you a health
  • 00:00:14
    check across your databases now when I
  • 00:00:16
    get SP Blitz index I can just hit
  • 00:00:19
    execute and it's going to go create the
  • 00:00:20
    stored procedure I usually put my
  • 00:00:23
    utility stored procedures and master but
  • 00:00:25
    if you want to put it in another
  • 00:00:27
    database that's completely fine it'll
  • 00:00:29
    work in any database now I'm going to
  • 00:00:31
    switch Windows here and go over into
  • 00:00:33
    another window where I've already got
  • 00:00:34
    some parameters set up for SP Blitz
  • 00:00:36
    index the first one that I'm going to go
  • 00:00:38
    run it with is with get all databases
  • 00:00:41
    equals one this goes through and
  • 00:00:44
    analyzes the health of indexes across my
  • 00:00:47
    entire SQL Server all of my user
  • 00:00:50
    databases it's going to go check against
  • 00:00:52
    all of them if you've got more than 50
  • 00:00:55
    databases on your SQL Server we actually
  • 00:00:58
    stop there if you want to analyze more
  • 00:01:00
    than 50 you'll need to use the parameter
  • 00:01:03
    bring the pain equals one there's a
  • 00:01:06
    reason why it's named that it's going to
  • 00:01:08
    take a very long time it's not going to
  • 00:01:10
    block other people while it runs it's
  • 00:01:12
    just that it's going to take a long time
  • 00:01:14
    to analyze across hundreds of
  • 00:01:16
    databases so when I run SP Blitz index
  • 00:01:19
    here's what I get back I get a
  • 00:01:21
    prioritized list of things that are kind
  • 00:01:24
    of iffy in terms of performance on my
  • 00:01:27
    SQL server in here I've got my stack
  • 00:01:29
    overflow demo database I can see that
  • 00:01:32
    I've got a few really high value missing
  • 00:01:35
    indexes and I've got some indexes that
  • 00:01:37
    I'm not even
  • 00:01:39
    using now to explain each of these I
  • 00:01:42
    want to scroll across and look at the
  • 00:01:43
    details in the first three here these
  • 00:01:46
    are the indexes a SQL server has been
  • 00:01:48
    tracking to say I really wish I had
  • 00:01:51
    these
  • 00:01:53
    indexes sp sp Blitz index is just built
  • 00:01:55
    a top of the index usage DMVs and
  • 00:01:58
    missing index DMVs that are built into
  • 00:02:00
    SQL Server so it's limited in terms of
  • 00:02:03
    its capabilities it will tell you what
  • 00:02:06
    Fields it wants the index on but
  • 00:02:09
    remember with SQL servers missing index
  • 00:02:11
    DMVs this does not mean the order that
  • 00:02:14
    that index should be included in the
  • 00:02:17
    different fields may need to be moved
  • 00:02:18
    around into different order that's up to
  • 00:02:21
    you with your index tuning
  • 00:02:23
    capabilities as I continue scrolling
  • 00:02:25
    across here it'll tell you how many
  • 00:02:27
    times that index would have been used
  • 00:02:30
    how much faster it would have made the
  • 00:02:32
    queries involved with that and how
  • 00:02:35
    expensive those queries were that really
  • 00:02:38
    needed the index if you want to see
  • 00:02:41
    which queries they were your best bet is
  • 00:02:43
    to go use the SP Blitz cash stored
  • 00:02:46
    procedure that will go through and
  • 00:02:48
    analyze the queries in your plan cache
  • 00:02:50
    that's for another video we also tell
  • 00:02:53
    you how many indexes already exist on
  • 00:02:57
    that table so for example I can see here
  • 00:03:00
    yeah of course I need some indexes on
  • 00:03:03
    these tables I don't have any right now
  • 00:03:06
    and if I want to learn more about each
  • 00:03:08
    particular table that's getting
  • 00:03:09
    recommendations here see this more info
  • 00:03:13
    command I can just copy paste these
  • 00:03:16
    right out let's take one out by default
  • 00:03:19
    start a new window and then paste that
  • 00:03:21
    in execute it SP Blitz cach will then go
  • 00:03:26
    and give me a more thorough review just
  • 00:03:28
    about that one table for example here
  • 00:03:31
    are the indexes on that table this table
  • 00:03:35
    has one clustered index that also
  • 00:03:37
    happens to be the primary key we show
  • 00:03:39
    information about the fill Factor how
  • 00:03:41
    many seeks and scans it's had how large
  • 00:03:45
    it is whether or not it has partitioning
  • 00:03:47
    or compression and much
  • 00:03:50
    more if there are any missing indexes
  • 00:03:53
    those will show up in the next result
  • 00:03:55
    set and then the third result set has
  • 00:03:57
    the complete definition for the table
  • 00:04:00
    all of the fields on that table which I
  • 00:04:02
    find really useful when I'm doing index
  • 00:04:04
    tuning how big are the fields that I'm
  • 00:04:07
    getting ready to
  • 00:04:08
    index let's go back to SP Blitz index's
  • 00:04:11
    output and dig a little bit deeper we
  • 00:04:14
    also get the exact create indexes
  • 00:04:17
    command over here in the create tsql
  • 00:04:20
    column so I can just copy paste this in
  • 00:04:22
    and have an indexing party in my
  • 00:04:24
    database and everyone's invited well not
  • 00:04:28
    quite so fast see there's a trick here
  • 00:04:30
    when we paste in these these uh index
  • 00:04:33
    recommendations notice how there's some
  • 00:04:35
    little question marks in here these
  • 00:04:37
    don't compile as is you can't just
  • 00:04:40
    execute these and immediately create
  • 00:04:42
    indexes because I want you to kind of
  • 00:04:44
    understand what you're doing before you
  • 00:04:46
    go just create indexes willy-nilly
  • 00:04:47
    otherwise I know how you people are
  • 00:04:49
    you'll copy paste anything that you get
  • 00:04:51
    from the internet and immediately flood
  • 00:04:53
    your database with indexes so we make
  • 00:04:55
    you think there about whether you want
  • 00:04:57
    indexes created online or off and
  • 00:05:00
    whether or not you want sort in tempdb
  • 00:05:02
    on or off that's a performance question
  • 00:05:05
    best left for our index
  • 00:05:07
    training back over on SP Blitz index's
  • 00:05:09
    output we've also got alerts here that I
  • 00:05:12
    have some indexes that aren't being used
  • 00:05:15
    they're only slowing down my inserts
  • 00:05:18
    updates and deletes and if I scroll
  • 00:05:20
    across I can also see how big these
  • 00:05:22
    indexes are and how many rights I've
  • 00:05:25
    actually done to them but this is only a
  • 00:05:29
    hand full of warnings the reason why is
  • 00:05:32
    we only want to surface the most crucial
  • 00:05:34
    warnings on your largest objects if you
  • 00:05:38
    want to go into more details you want to
  • 00:05:41
    see things even on fairly small tables
  • 00:05:44
    what you can do is switch into a
  • 00:05:47
    specific database let's go like just the
  • 00:05:49
    stack Overflow database and then I'm
  • 00:05:52
    going to run it with a different set of
  • 00:05:54
    commands I'm going to run it with mode
  • 00:05:57
    equals
  • 00:05:58
    4 SP Blitz index has several different
  • 00:06:01
    modes but mode equals 4 will go down
  • 00:06:05
    even to more detail on our smaller
  • 00:06:08
    tables for example here you'll see it
  • 00:06:11
    returns more warnings multiple
  • 00:06:14
    personalities I've got borderline
  • 00:06:16
    duplicate indexes on one of my indexed
  • 00:06:20
    views now is probably a good time to
  • 00:06:22
    mention the cool names on these indexof
  • 00:06:25
    multiple personalities hoarder abnormaly
  • 00:06:29
    psychology Workaholics we kind of think
  • 00:06:32
    of this as a psychological test for your
  • 00:06:34
    indexes it's not that there's any kind
  • 00:06:37
    of bad psychology It's just sometimes
  • 00:06:39
    there are unusual behavioral patterns
  • 00:06:42
    like your e
  • 00:06:43
    kleptomaniac so we just want to alert
  • 00:06:45
    you about some of these unusual things
  • 00:06:48
    that are going on inside your
  • 00:06:50
    database if you want to learn more
  • 00:06:52
    details about any of the warnings that
  • 00:06:55
    your SQL Server is facing check out the
  • 00:06:58
    URL column where you can see whole pages
  • 00:07:01
    about each of the unusual psychological
  • 00:07:03
    behaviors that are going on inside your
  • 00:07:06
    database that's SP Blitz index a tool to
  • 00:07:09
    help you design the right indexes and
  • 00:07:12
    get rid of the junk indexes in your SQL
  • 00:07:14
    Server datab
  • 00:07:21
    base
标签
  • database
  • indexes
  • SQL Server
  • SP Blitz Index
  • health check
  • query analysis
  • performance tuning