The MidnightDBAs release Minion Reindex

The MidnightDBA team is announcing the release of a new, free index maintenance solution for SQL Server: Minion Reindex.

Database professionals need a reindex solution that is fully automated out of the box, intuitive to use, simple to customize, deeply configurable, and easy to report from. Existing index maintenance solutions fall short in ease of deployment, configurability, reporting, and more. The new Minion Reindex by MidnightDBA was designed by database administrators, for database administrators, to solve every one of these headaches.

Download Minion Reindex

Minion Reindex is a powerful and elegant tool that we are proud to have in our toolbox.  It makes our jobs easier, makes our clients’ maintenance operations more streamlined, and overall just makes the world a shinier, happier place to live in. 

Download Minion Reindex now for free at www.MidnightSQL.com/Minion.  And, join us at our Minion Reindex webinar on Monday October 27 at 12:00pm CT.  Check www.MidnightSQL.com/Minion for webinar information and links to documentation and feature tutorials.  

Features

We’re excited about a LOT of the features in Minion Reindex. Why is this particular solution worth your notice? Well…

·         Super easy install and removal, of course.

·         Easy MASS installation. One little Powershell script, and you can install Minion Reindex on hundreds of instances just as easily as on one.

·         Extreme granular configuration. Configure extensive settings at the default, database, and/or table levels with ease. And I mean every setting you can think of, from Fill Factor to sort in TempDB.

·         Order index operations by database and by table. This feature is very powerful; there are 2 levels of ordering at the database level, and another 2 levels for table ordering.

·         Flexible include and exclude – Reindex only the databases you want, using specific database names, and/or LIKE expressions. There’s even an advanced option to use regular expressions (regex) for excludes.

·         Live insight – Minion Reindex lets you see what’s happening while the routine is still running. See what Minion Reindex is doing every step of the way, and how much further it has to go.

·         Maximized maintenance window – Spend the whole maintenance window on index maintenance, not on gathering fragmentation stats. (Oh yeah, we let you schedule the frag stats gathering separately, if that’s what you want.)

·         Extensive, useful logging – Use the Minion Reindex log for estimating the end of the current reindexing run, troubleshooting, planning, and reporting.

·         Built in manual runs – Choose to only print reindex statements, and run them individually as needed.

·         Integrated help –Get help on any Minion Reindex object without leaving Management Studio.

SQL Server Update Statistics

Updating the statistics of all the tables regularly in your SQL Server Database is an integral part of Database Maintenance. This can be achieved in many ways. We can do this through a script or create a sql job scheduled to run at a certain time.

SQL Server also has a database option called "Auto Update Statistics" which by default is True. This feature follows a basic algorithm as to when the statistics of a table is to be updated . One drawback of this setting is if certain set of conditions are satisfied for a table then it starts updating the stats at that time. This could be in the middle of the day at peak hours and hence could create perforrmance issues in production , so it is sometimes safe to disable this and do the update statistics through a script or a sql job for a very large database. One of the easiest and fundamental steps involved in sql query optimization is updating the statistics of all tables involved in the query. Updated stats help the SQL query optimizer to generate optimized query plans for queries. The SQL Server maintains information like number of data pages used by each table, the total row count of each table and the number of DML operations which affected the keys since the last statistics upgrade. Besides this SQL Server also maintains index statistics To determine whether an index has it's stats updated we can run the following command: DBCC SHOW_STATISTICS (table_name , index_name) To Update the Stats on a table the following command can be run : UPDATE STATISTICS <table name> WITH FULLSCAN The above command will scan all the rows of the table to get the updated statistics.     UPDATE STATISTICS <table name> WITH SAMPLE 50 PERCENT    The above script can be run for very large tables in case we want to specify a percentage of the total no of rows. We should also update statistics for all tables in a database when we are upgrading from any version to a higher SQL Server version

The Page File

The page file is a disk file that the computer uses to increase the amount of physical storage for virtual memory. In other words, when the memory in use by all of the existing processes exceeds the amount of available RAM, the Windows operating system takes pages of one or more virtual address spaces and moves them to the page file that resides on physical disk. This frees up RAM for other uses. These “paged out” pages are stored in one or more page files that are located in the root of a disk partition. There can be one such page file on each partition.

                                    On a server running SQL Server, the objective is to try to keep SQL Server running using just the available physical memory. SQL Server itself goes to great lengths to ensure that it doesn’t over-allocate memory, and tries to remain within the limits of the physical memory available.

Given this basic objective of SQL Server, in most cases there is limited need for a page file. However, a frequently asked question is “Is there a recommended size for the page file?” The answer to this question, of course, is “it depends.” It depends on the amount of RAM installed and what virtual memory will be required above and beyond SQL Server. A general guideline is to configure a page file total of 1.5 to 2 times the amount of RAM installed in the server.

However, in large systems with a large amount of RAM (more than 128GB), this may not be

possible due to the lack of system drive space. Some good guidelines to adhere to in these cases are outlined in the following:

·         Configure an 8GB page file on the system drive.

·         Make sure that the Windows operating system startup parameters are configured to capture

A kernel dump in the event of a failure. Please see this article from Microsoft Support on how to configure this setting: http://support.microsoft.com/kb/307973.

·         Optional: Configure multiple page fi les (on disk volumes other than the system volume) that will be available for the OS to utilize if a larger page file is desired. It is recommended to use one additional page file of 200GB for each 256GB of RAM installed in the server.

In some cases, SQL Server and the OS might not cooperate well on sharing the available memory,

and you may start to see system warnings about low virtual memory. If this occurs, you will ideally add more RAM to the server, reconfigure SQL to use less memory, or increase the size of the page file. It may be better to reconfigure SQL Server to remain within the available physical memory than it is to increase the size of the page file. Reducing paging always results in better performance. If paging occurs, for best performance, the page file should be on fast disks that have minimal disk usage activity, and the disks should be periodically defragmented to ensure that the page file is contiguous on the disks, reducing the disk head movement and increasing performance. The metric in the Windows System Monitor to measure page file usage is Paging file: %Usage, which should be less than 70 percent.