{"id":2111,"date":"2015-11-16T00:00:00","date_gmt":"2015-11-16T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/doing-a-sql-server-healthcheck-via-powershell\/"},"modified":"2021-08-24T13:39:35","modified_gmt":"2021-08-24T13:39:35","slug":"doing-a-sql-server-healthcheck-via-powershell","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/doing-a-sql-server-healthcheck-via-powershell\/","title":{"rendered":"Doing a SQL Server Healthcheck via PowerShell"},"content":{"rendered":"
\n

Introduction<\/h1>\n

This article describes a way of checking the health of any number of SQL Server instances and servers. It quickly collates server performance statistics and highlights potential problems such as missing indexes, untrusted foreign keys and database options that aren’t set to the default. Whether you are an accidental or deliberate DBA, this tool will either save you paying a consultant to run a health check for you or it will save you writing and executing all of the necessary scripts and formatting the report. This tool is open-source, on GitHub<\/a> and is free for use. It.is rather different from SQLBlitz in that it is PowerShell-driven and does not attempt to be prescriptive. It is also designed to be easy for the user to customize. <\/p>\n

To get started, all you need to do is to:<\/p>\n

    \n
  1. Download and extract HealthCheck.zip<\/code> to the desired location (it is in a link at the bottom of the article).<\/li>\n
  2. Open DBList.txt and replace server and database names as required. \t\tAlternatively, execute the PowerShell script passing the name of the \t\tserver and the database as parameters and skip the third step.
     For \t\texample: .\/HealthCheck.ps1 Server1\\InstanceA DB1<\/code><\/li>\n
  3. Execute HealthCheck.bat.<\/li>\n<\/ol>\n

    \t The report can be found in the\\HealthCheck\\Reports<\/code> directory. <\/p>\n

    The Report<\/h2>\n

    The report contains one worksheet for each of the T-SQL scripts. I have included what I think is most important when evaluating a SQL Server from a performance perspective.<\/p>\n

    \"2307-img180.jpg\"<\/p>\n

    \n
    CacheHitRatios<\/dt>\n
    shows the hit ratios and sizes for each plan cache store.  The closer the hit ratios are to 100%, the better. Lower values could indicate memory pressure or plan cache bloat.<\/dd>\n
    ColumnTypesVarchar255<\/dt>\n
    lists tables that have more than one column defined as varchar(255). These tables should be evaluated to see whether other data types would be more appropriate.<\/dd>\n
    CPU_Pressure<\/dt>\n
    Calculates signal waits and resource waits as a percentage of the overall wait time. As a guideline, signal waits should usually be below 20%. If you see values higher than this, try tuning the queries highlighted by the MissingIndexesProcsByCPU <\/code>report.<\/dd>\n
    CursorsWithoutCloseDeallocate<\/dt>\n
    lists objects with cursors that have not been closed or deallocated. Static cursors use the most memory and could therefore have the greatest impact if not closed correctly.<\/dd>\n
    DatabaseOptions<\/dt>\n
    returns database options that are enabled as well assome other information such as compatibility leveland recovery model.<\/dd>\n
    DataTypes_Deprecated<\/dt>\n
    lists tables with columns that use deprecated types text, ntext and image. These should be replaced by the new types (n)varchar(max) <\/code>and \tvarbinary(max) <\/code>to avoid your code breaking after upgrading SQL Server.<\/dd>\n
    FillFactor<\/dt>\n
    lists indexes with fill factors of less than 90. You may want to review these and decided if space is being wasted.                <\/dd>\n
    ForeignKeysUntrusted<\/dt>\n
    lists untrusted foreign keys.These constraints will not be considered by the query optimizer. See http:\/\/sqlblog.com\/blogs\/hugo_kornelis\/archive\/2007\/03\/29\/can-you-trust-your-constraints.aspx<\/a> for more details.<\/dd>\n
    HeapsNoIndexes<\/dt>\n
    lists tables that have no indexes. It is usually a good idea to have at least a clustered index on a table.<\/dd>\n
    HeapsWithNCIndex<\/dt>\n
    lists tables that have a non-clustered index but do not have a clustered index. There are exceptions where this might be acceptable. See the following article for more information: https:\/\/msdn.microsoft.com\/en-GB\/library\/hh213609.aspx<\/a><\/dd>\n
    HypotheticalIndexes<\/dt>\n
    lists hypothetical indexes. They are usually created by the Database Tuning Advisor but can also be created using T-SQL. It is best to ensure these are removed to avoid clutter.  Also, there have been bugs related to these such as the following: http:\/\/blogs.msdn.com\/b\/sqlserverfaq\/archive\/2009\/06\/25\/exception-when-selecting-from-sysindexes-in-sql-server-2005.aspx<\/a><\/dd>\n
    IndexesIgnoreDupKey<\/dt>\n
    lists tables with non-clustered indexes that have theignore_dup_key \t<\/code>option enabled. This option harms performance of inserts and updates as describedin Craig Freedman’s article on MSDN:http:\/\/blogs.msdn.com\/b\/craigfr\/archive\/2008\/01\/30\/maintaining-unique-indexes-with-ignore-dup-key.aspx<\/a><\/dd>\n
    IndexesUnusedSizes<\/dt>\n
    lists tables that have unused indexes. Before removing indexes, remember that the index usage stats DMV is cleared upon a server restart.<\/dd>\n
    IndexKeysOver900Bytes<\/dt>\n
    lists indexes that have a maximum key width over 900 bytes which could result in error 1946 during an insert or update operation.<\/dd>\n
    IndexKeyWidth<\/dt>\n
    lists tables with wide cluster keys. These should be reviewed as cluster keys are also present in all non-clustered indexes and can therefore consume a lot of space, possibly unnecessarily.<\/dd>\n
    InMemoryTables (2014 only) <\/dt>\n
    lists In-Memory OLTP tables along with their memory usage.<\/dd>\n
    IO_Stats<\/dt>\n
    returns IO stall statistics for all databases with the target database listed first.<\/dd>\n
    LatchStats<\/dt>\n
    returns latch wait statistics. The latch stats DMV is also cleared upon a server restart.<\/dd>\n
    MemoryClerks<\/dt>\n
    lists top ten memory clerks and total memory usage.<\/dd>\n
    MissingIndexes­<\/dt>\n
    lists missing indexes for the target database with an estimated impact above 70 and index advantage above1,000.<\/dd>\n
    MissingIndexesForeignKeys<\/dt>\n
    lists unindexed columns referenced by foreign keys.  Deletes on the referenced tables will result in scans on the parent tables. Evaluate whether indexes on the parent tables are required.<\/dd>\n
    MissingIndexesProcsByCPU (2008-2014 only) <\/dt>\n
    returns missing index information for the ten stored procedures that consume most CPU resource. Filtered on indexes with an impact above 70.<\/dd>\n
    MissingIndexesProcsByReads (2008-2014 only) <\/dt>\n
    as above but for the highest consumers of I\/O.<\/dd>\n
    PlanCacheByType<\/dt>\n
    returns plan cache size broken down by type. Look out for a bloated ad hoc cache store, which is a common problem.<\/dd>\n
    ServerAudits<\/dt>\n
    lists active server audits. Watch out for any synchronous server audits, with a queue delay of 0. These can really hurt performance.<\/dd>\n
    ServerConfig<\/dt>\n
    returns non-default server configurations. Look out for a non-default fill factor. I prefer to leave this setting as the default of zero and assign each index its own fill factor, if required. Check that the “max degree of parallelism” has been set according to the official guidelines from Microsoft https:\/\/support.microsoft.com\/en-gb\/kb\/2806535<\/a>. A common oversight is to leave the maximum server memory as the default 2 TB. This is one setting where you will definitely want to have a non-default configuration.<\/dd>\n
    ServerInformation.ps1<\/dt>\n
    PowerShell script that returns information on the target server’s CPUs, BIOS, and memory, as well as SQL Server-specific details.<\/dd>\n
    ServerTraces<\/dt>\n
    lists active traces on the server. Beware of traces where is_rowset = 1 as this means somebody is running SQL Profiler against your server, which has a big overhead.<\/dd>\n
    sp_xml_removedocument<\/dt>\n
    lists objects that prepare XML documents but do not remove them. Microsoft recommends alwaysincluding this to avoid running out of memory.<\/dd>\n
    StatisticsDuplicates<\/dt>\n
    lists duplicate statistics with their sizes.This report uses the key_ordinal column to identify the first column of an index statistic due to the following bug\\unexpected behavior with the stats_column_id: https:\/\/connect.microsoft.com\/SQLServer\/Feedback\/Details\/1163126<\/a><\/dd>\n
    TablesAnsiNulls<\/dt>\n
    lists tables created with ANSI_NULLS<\/code> off. This setting is deprecated and should no longer be used. Also, various features in SQL Server will generate an error when this setting is off, such as indexed views and filtered indexes.<\/dd>\n
    TablesManyIndexes<\/dt>\n
    lists tables with more than five indexesand an index count greater than half the column count. Having five indexes or more isn’t necessarily bad, it’s just a starting point for finding tables that possibly have an unnecessarily large amount of indexes. They can often be consolidated into fewer indexes, saving space and improving write latencies. <\/dd>\n
    TablesOver10GB<\/dt>\n
    lists tables larger than 10 GB. Good candidates for data compression, assuming you have SQL Server 2008 or a later version and the Enterprise edition.<\/dd>\n
    TablesWithMoreIndexesThanColumns<\/dt>\n
    lists tables that have more indexes than columns. Not usually a very good sign!<\/dd>\n
    UniqueIdentifierClusterKey<\/dt>\n
    lists tables that have a unique identifier as the first column of the clustered index. This is rarely a good idea, although there are exceptions to this such as latch contention on increasing keys. This MSDN article explains in more detail. http:\/\/blogs.msdn.com\/b\/sqlserverfaq\/archive\/2010\/05\/27\/monotonically-increasing-clustered-index-keys-can-cause-latch-contention.aspx<\/a><\/dd>\n
    WaitStats<\/dt>\n
    Returns server wait statistics. These give a good indication as to where you should focus your performance tuning efforts.<\/dd>\n<\/dl>\n

    I deliberately didn’t include missing indexes resulting from ad hoc queries in the report due to the slow performance of the sys.dm_exec_query_stats <\/code>DMV on servers with a lot of memory. If you find that the DMV returns results quickly on your servers, feel free to add a script to report on this.<\/p>\n

    I also decided not to includeindex fragmentation statistics for the same reason.
    The performance of the sys.dm_db_index_physical_stats<\/code> DMV can be really slow with large tables. If you do want to include this information, there are plenty of ready-made scripts out there on the various SQL Server forums.<\/p>\n

    The scripts for the stored procedures missing index reports …
    (MissingIndexesProcsByCPU.sql <\/code>and MissingIndexesProcsByReads.sql<\/code>)
    …are not available for SQL Server 2005 instances as the sys.dm_exec_procedure_stats <\/code>DMV was only introduced in SQL  Server 2008.<\/p>\n

    How Does It Work?<\/h2>\n

    The batch file reads a list of SQL Server instance and database names from a text file and passes them as parameter values to a PowerShell script, which it executes in a loop, once for each database (see the “Permissions Required” section below for security issues). The PowerShell script is intended to do a health check on a single instance, putting the result in an excel file of the same name as the instance. It does this health check by executing a set of diagnostic T-SQL and PowerShell scripts, exporting metadata to CSV files.  Finally, a VBA Excel macro is executed from PowerShell to merge the CSV files into an Excel spreadsheet and then formats the report. I could have implemented this part in PowerShell too, but as I had written a formatting macro a few years ago, I decided to reuse it.<\/p>\n

    Software Required<\/h2>\n

    The following software is required to run the health check:<\/p>\n