• DBA,  Performance

    Performance Tuning Steps Query by looking an execution plan and effectiveness of the index Part 2

    How to read execution plan to improve poorly performing queries Execution plan is optimizer’s calculation of how the query will be executed by creating the best low-cost query plan (if it is already in the cache, it will reuse).  SQL server is cost based optimizer.  Meaning, the optimizer decides based on CPU, I/O and statistics etc and creates a plan and returns the results. Up to date statistics is very important one to create a more accurate plan. We can view this in GUI and text both will be helpful, when the scenario come in.   Types:  Estimated and Actual execution plan – both will be helpful. Estimated plan: It…

  • AlwaysON

    None of the IP addresses configured for the availability group listener can be hosted by the server Either configure a public cluster network 19456

    Requirement: Remove a node from one of availability alwayson group and added it on another group. It was a multi-subnet and the node is a DR node on a different subnet. You have to remove the IP from the group in the windows cluster go to run — cluadmin.msc — click roles — click the group –> click on server name expand it — remove the IP of the node it could be offline. Once deleted add it on the other group by the listener or from the cluster. Background: It is a three node cluster two in same data center and third node is in DR data center. TITLE:…

  • DBA,  Performance

    Performance Tuning Steps a Query by looking an execution plan and effectiveness of the index creation Part-1

    The index is very important and powerful object in the database. It is an optional and secondary object for the table. It will speed up the data return time by navigating the B-tree structure, like a book’s index. In General, index tuning is important one, putting a right index will speed up the code.  Please read this post for finding symptoms, where you have issue at server or database or query level.  Most of the time, the query will be the issue. Once, you have long running query start tuning it. In this post, I am going to share the usage and effectiveness of index from low level to high…

  • DBA

    Microsoft SQL Server Migration plan and steps

    Migration is nothing but moving a databases from one instance to another instance. It could be a single database or all the databases. DBA will get this many times, for testing or development server new build or removing legacy/old servers In general, this will be a combination of migration and upgrade of newer version ex: existing 2012 from old server to new server 2016. Some will have two instances in same server and migrate for dev and test purpose. Types: Best backup/restore, for VLDB very large databases, you can plan SAN replication or VM level and mount point/ VMDK movements with detach attach database etc. Supported Version and Edition Upgrades,…

  • DBA

    Microsoft SQL Server upgrade plan and steps

    SQL server upgrade is going lower version of SQL to higher version – the latest version. It can be a minor upgrade i.e. service pack patch apply or major upgrade from one version to another ex: 2016 to 2017. Types: We can perform this in the existing system that is called In-place upgrade. This requires more downtime and better roll back/fall back plan. Since, if anything happens take more time to restore previous state, but quickest one, just taking a backup of everything -VM snapshot. We can install new latest SQL version and copy everything to the new server that is called side by side upgrade. This is something like…