SQL Server – Query / Monitor Index Fragmentation

I've been DBA on my first job outside of college for 3 months and have significant difficulty figuring out how index fragmentation on 2 of our production servers (2 separate clusters, but none) can be accurately queried and reported, which is not for this specific question important … just pretend I have 1 server).

We already use Ola Hallengren's scripts for index maintenance, but I need to query all our databases on our server and retrieve the% fragmentation along with the table name, index name, and database name.

Here is my current query that does not seem to be correct:

SELECT s.(name) +'.'+t.(name)  AS table_name  
 ,i.NAME AS index_name  
 ,dbd.name AS (database_name)  
 ,index_type_desc  
 ,ROUND(avg_fragmentation_in_percent,2) AS avg_fragmentation_in_percent  
 ,page_count  
 FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'LIMITED') ips  
INNER JOIN sys.tables t on t.(object_id) = ips.(object_id)  
INNER JOIN sys.schemas s on t.(schema_id) = s.(schema_id)  
INNER JOIN sys.indexes i ON (ips.object_id = i.object_id) AND (ips.index_id =   i.index_id)  
INNER JOIN sys.databases AS dbd on dbd.database_id = ips.database_id  
WHERE i.type IN (1,2) -- Include ONLY Clustered & Non-Clustered Indexes  
AND s.name <> 'Audit' -- Exclude Audit Schemas  
AND ips.page_count > 100 --Exclude small page counts  
AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'   
AND ips.index_level = 0 -- Current level of the index. 0 for index leaf  
levels... Greater than 0 for nonleaf index levels.

Even if I remove the WHERE clauses, my data still seems to be wrong. One of my records / tuples shows a table in a database with a 1700-page index, but if I manually go to that index, does it have only 1? I worked for hours on it and can not seem to figure out what's going on. I have read the entire documentation for this DMV.

seo – Pantheon Dev Site included in Google Index (with injected robots.txt)

We have a bizarre scenario that I would like to have opinions about. A page on thisdomain.com is created based on Pantheon using the URL thisdomain.pantheon.io staging / dev *.

Pantheon's development platform adds a robots.txt file to avoid indexing the developer site in Google: User-agent: * Disallow: /

Experienced SEOs know that this is not enough to keep something away from Google. At some point during development, a writer inadvertently linked to a page on the Dev site from the production site, causing Google to index the domain domain.domain.pantheon.io.

Result: thisdomain.pantheon.io is now stuck in the index and even relocates the production site to Google # 23 for its own brand query. SEO guy is sad SEO.

We are verified in GSC ** for developers and production.

A normal advice would be:

Add & nbsp; & nbsp; noindex & # 39; directives, pick and wait
Retrieve and wait for the password to page (403,).
Temporarily redirect page to production (301,) retrieve and wait.

Of course, none of this works unless Gooblebot can see this 403/301/404 / etc. Answers remains the page in the index. With Pantheons "injected" robots.txt we are SOL.

Do you have any idea how we could force this out of the index?

* It's worth pointing out to non-Pantheon people that there's no way to change "thisdomian" in the staging URL to something else. We have no control over the robots.txt file and can not remove it.

** If your idea is a URL removal tool: By removing URLs, we can quickly hide from the site thisdomain.pantheon.io. However, this would only temporarily hide our efforts, and I have recommended this for the time being. The removal program does not work at 401.

Mongodb index is not used on $ or query

I have created a collection first

and created index
db.first.createIndex({a:1, b:1, c:1, d:1, e:1, f:1});

and inserted data

db.first.insert({a:1, b:2, c:3, d:4, e:5, f:6});
db.first.insert({a:1, b:6});

For questions like

db.first.find({f: 6, a:1, c:3}).sort({b: -1}).explain();

Index can be used

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "myproject.first",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : (
                {
                    "a" : {
                        "$eq" : 1
                    }
                },
                {
                    "c" : {
                        "$eq" : 3
                    }
                },
                {
                    "f" : {
                        "$eq" : 6
                    }
                }
            )
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "a" : 1,
                    "b" : 1,
                    "c" : 1,
                    "d" : 1,
                    "e" : 1,
                    "f" : 1
                },
                "indexName" : "a_1_b_1_c_1_d_1_e_1_f_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "a" : ( ),
                    "b" : ( ),
                    "c" : ( ),
                    "d" : ( ),
                    "e" : ( ),
                    "f" : ( )
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "backward",
                "indexBounds" : {
                    "a" : (
                        "(1.0, 1.0)"
                    ),
                    "b" : (
                        "(MaxKey, MinKey)"
                    ),
                    "c" : (
                        "(3.0, 3.0)"
                    ),
                    "d" : (
                        "(MaxKey, MinKey)"
                    ),
                    "e" : (
                        "(MaxKey, MinKey)"
                    ),
                    "f" : (
                        "(6.0, 6.0)"
                    )
                }
            }
        },
        "rejectedPlans" : (
            {
                "stage" : "SORT",
                "sortPattern" : {
                    "b" : -1
                },
                "inputStage" : {
                    "stage" : "SORT_KEY_GENERATOR",
                    "inputStage" : {
                        "stage" : "FETCH",
                        "filter" : {
                            "$and" : (
                                {
                                    "c" : {
                                        "$eq" : 3
                                    }
                                },
                                {
                                    "f" : {
                                        "$eq" : 6
                                    }
                                }
                            )
                        },
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "a" : 1
                            },
                            "indexName" : "a_1",
                            "isMultiKey" : false,
                            "multiKeyPaths" : {
                                "a" : ( )
                            },
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 2,
                            "direction" : "forward",
                            "indexBounds" : {
                                "a" : (
                                    "(1.0, 1.0)"
                                )
                            }
                        }
                    }
                }
            }
        )
    },
    "serverInfo" : {
        "host" : "Manishs-MacBook-Pro.local",
        "port" : 27017,
        "version" : "3.6.4",
        "gitVersion" : "d0181a711f7e7f39e60b5aeb1dc7097bf6ae5856"
    },
    "ok" : 1
}

but when i use or query

db.first.find({ $or: ({f: 6}, {a:1})}).explain();

Index is not used, columns are scanned instead

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "myproject.first",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$or" : (
                {
                    "a" : {
                        "$eq" : 1
                    }
                },
                {
                    "f" : {
                        "$eq" : 6
                    }
                }
            )
        },
        "winningPlan" : {
            "stage" : "SUBPLAN",
            "inputStage" : {
                "stage" : "COLLSCAN",
                "filter" : {
                    "$or" : (
                        {
                            "a" : {
                                "$eq" : 1
                            }
                        },
                        {
                            "f" : {
                                "$eq" : 6
                            }
                        }
                    )
                },
                "direction" : "forward"
            }
        },
        "rejectedPlans" : ( )
    },
    "serverInfo" : {
        "host" : "Manishs-MacBook-Pro.local",
        "port" : 27017,
        "version" : "3.6.4",
        "gitVersion" : "d0181a711f7e7f39e60b5aeb1dc7097bf6ae5856"
    },
    "ok" : 1
}

Please let me know if I'm doing something wrong.

python – Pythonic substring Anagram Index

I am working to improve my Python skills and have a daily code problem. For the problem:

Given a word W and a string S, you will find all the starting indices in S that are anagrams of W.

For example, s = "abxaba" and w = "ab" (0, 3, 4),
s = "cbaebabacd", w = "abc" should return (0, 6)

Is there a more efficient / pythonic solution than the code I wrote? Storing the queue as a list and putting the queue per loop twice seems inefficient.

def anagram_indices(w, s):

  w = set(list(w))

  queue = ()
  index = ()

  for x, i in enumerate(list(s)):
    queue.append(i)

    while(w.issubset(set(queue(1:)))):
      queue.pop(0)

    if w == set(queue):
      index.append(x-len(w)+1)

  return(index)

word = "ab"
string = "abxaba"

print(anagram_indices(word, string))

Visibility and Index of Products – Magento Stack Exchange

In Magento 2, one of the products on the front was not visible. If the command-line indexer is running: The product is re-indexed. Why do I need to run indexer: reindex? In the system -> index management, settings are already "update on schedule", cron is running and in colmn updated data is changed. The product should be displayed automatically?

But … in the database in the indexer_state table, there are different data, and that data changed when I ran indexer: reindex. So what is this table, why does it have different data than in admin (System -> Index Management)?

Thanks for any help

SQL Server 2012 – What are the implications of over-provisioning varchar types for the size of the index store?

Takes over the deployment varchar Columns (as with using varchar(100) to store values ​​from 1 to 10 characters varchar(10)) have an effect on the size of the index memory and if so, why?

I remember reading a Q / A about it here, but can not find it with any of the terms that come to my mind.

The Google search index lists the IP address of my old server instead of my domain name

This question already has an answer here:

My two-year-old website was indexed and the search result was listed as usual for domain names in Google search results for a long time. However, server IPs have recently been changed, which may not be the cause, but Google now lists some of my pages under my old IP address 12.34.56.789/contents/my-page- in the search results, as well as many of my pages under their domain like all of these millennia by default.

How do I fix this when Google ruins my mark by indexing dead links?

And how can I not be afraid of it in the future? Domain names exist so we can change the IPs.

SQL Server – Slow query in production, wrong execution plan or wrong index?

I just fixed a production issue by deleting and rebuilding an index.
I suspect that deleting the index also cleared execution plans that used it, and one of them happens to be bad.

Arguments for a bad execution plan:

  • Before I deleted the index, I looked up the last update date for the statistics in the given table and they were up to date.
  • My database administrator has set up the solution for the index and statistics management of Hallengren
  • The slow query was a SELECT statement that was executed by sp_executesql with date parameters. Execute the same select statement without sp_executesql was fast, but did not use the same execution plan.

Arguments against a bad execution plan:

  • Before we dropped the index, we got really wild and ran the forbidden dbcc freeproccache to delete a bad plan, but this has not resolved or changed the performance issue.

Note:

  • The slow query randomly uses a table indexed by date. However,
    There are big differences in the number of records for each date. in the
    In other words, a specific date ranges from a few records to more than 100,000
    and it's pretty coincidental.

  • The database runs under compatibility level 140 (SQL Server)
    2017)

Was the cause of the problem a bad plan or an outdated index?
If it's a bad plan, why? dbcc freeproccache Did not it work out?