Folder SQL Server Check
Last updated 11 November 2020.
SQL Server Check - Performance troubleshooting and profiling for Dynamics NAV | BC and SQL Server
Checks SQL Server in scope for some of most common skewed Best Practices and performance issues.
Valid from SQL Server 2012 onwards. By default all databases in the SQL Server instance are eligible for the several database specific checks, and you may use the optional parameter to narrow these checks to specific databases.
Only a system administrator/local host administrator is able to perform all checks or functions.
This code and information are provided "AS IS" without warranty of any kind, either expressed or implied. Furthermore, the author or Olav Treffurt IT Consulting shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.
Contains the following informational sections:
|Uptime||The time since the instance last started||2.0|
|OS Version and Architecture||no further description.||2.0|
|Disk space||no further description.||2.0|
|HA Information||no further description.||2.0|
|Linked servers info||no further description.||2.0|
|Instance info||no further description.||2.0|
|Buffer Pool Extension info||no further description.||2.0|
|Resource Governor info||no further description.||2.0|
|Database Information||no further description.||2.0|
|Database file autogrows last 72h||no further description.||2.0|
|SQL Server Configuration||
A script to use to check SQL Server configuration options.
Focus on these settings:
-- backup checksum default (should be 1)
-- backup compression default (should be 1 in most cases)
-- clr enabled (only enable if it is needed)
-- cost threshold for parallelism (depends on your workload)
-- lightweight pooling (should be zero)
-- max degree of parallelism (depends on your workload and hardware)
-- max server memory (MB) (set to an appropriate value, not the default)
-- optimize for ad hoc workloads (should be 1)
-- priority boost (should be zero)
-- remote admin connections (should be 1)
|Feature usage||no further description.||2.0|
|Backups since last Full Information||no further description.||2.0|
|System Configuration||no further description.||2.0|
|Clean up temp objekts||When you have finished, ensure that you run the last block to clear up the temporary tables that have been created.||2.0|
Processor | Memory | Pagelife | IO
|Number of available Processors||Number of available Processors for this instance vs. MaxDOP setting subsection.||2.0|
|Historical CPU Utilization SQL vs Other Processes||
Get CPU Utilization History for last 256 minutes (in one minute intervals).
Look at the trend over the entire period. Also look at high sustained 'Other Process' CPU Utilization values.
|Additional Processor information||no further description.||2.0|
|Server Memory||Mind that it may take some time in servers with large caches.||2.0|
|SQL Server Buffer Detail||
A useful metric that is easy to get is a measure of buffer cache usage by database on the server.
This query returns, in order from most pages in memory to fewest, the amount of memory consumed by each database in the buffer cache.
|LPIM||no further description.||2.0|
|Pagefile||no further description.||2.0|
|SQL Server Signal Waits||Higher the Signal wait stats are not good for the system. Very high value indicates CPU pressure. In my experience, when systems are running smooth and without any glitch the Signal wait stat is lower than 20%. Again, this number can be debated (and it is from my experience and is not documented anywhere). In other words, lower is better and higher is not good for the system.||2.0|
|Memory Usage Status||You need to monitor SQL Server memory usage regularly to ensure enough memory is available for mitigating drastic performance problems and to ensure SQL Server is running as fast as possible. With V8 Search tool you can use to track down the root cause of SQL Server memory bottlenecks, identify how much memory is available, and how SQL Server is currently using the memory.||2.0|
|MAX DOP Recommendation||
What is MAXDOP and why it is important?
When SQL Server runs on a computer with more than one processor or CPU, it detects the best degree of parallelism, that is the number of processors employed to run a single statement, for each query that has a parallel execution plan. You can use the max degree of parallelism option to limit the number of processors to use for parallel plan execution and to prevent run-away queries from impacting SQL Server performance by using all available CPUs.
The Microsoft SQL Server max degree of parallelism (MAXDOP) configuration option controls the number of processors that are used for the execution of a query in a parallel plan. This option determines the number of threads that are used for the query plan operators that perform the work in parallel. Depending on whether SQL Server is set up on a symmetric multiprocessing (SMP) computer, a non-uniform memory access (NUMA) computer, or hyperthreading-enabled processors, you have to configure the max degree of parallelism option appropriately. This article discusses the general guidelines that you can use to configure the max degree of parallelism option for SQL Server when you use the sp_configure system stored procedure.
The OPTION (MAXDOP) Transact-SQL query hints can override the max degree of parallelism option in the sp_configure value if the query specifies this option. In SQL Server 2008 and later versions, if the MAXDOP value exceeds the value that was configured by using the Resource Governor, the database engine uses the Resource Governor MAXDOP value. All semantic rules that are used with the max degree of parallelism option are applicable when you use the MAXDOP query hint. The other two options that can override or affect the MAXDOP setting are as follows:
Parallel index operations
The max degree of parallelism configuration option does not limit the number of processors that SQL Server uses. To configure the number of processors that SQL Server uses, use the affinity mask configuration option.
The MAXDOP setting applies to each operator of the plan (e.g. Clustered Index Scan). Each step will use one CPU or the number of CPUs that is specified by MAXDOP and never anything in between. If multiple operators of a plan execute concurrently in parallel, the total number of threads that are used by the query may exceed the MAXDOP setting specified.
SQL Server 2016 (13.x) and higher
Starting with SQL Server 2016 (13.x), during service startup if the Database Engine detects more than eight physical cores per NUMA node or socket at startup, soft-NUMA nodes are created automatically by default. The Database Engine takes care of placing logical processors from the same physical core into different soft-NUMA nodes. The recommendations in the table below are aimed at keeping all the worker threads of a parallel query within the same soft-NUMA node.
|Memory Information||no further description.||2.0|
|Pending disk I/O Requests||Indicate that your I/O has insufficient service capabilities (HBA queue depths, reduced throughput, etc).||2.0|
|Check for HP Logical Processor issue||For more information, see the MSDN documentation||2.0|
Server | Service Accounts | Instance
|SQL Server ErrorLog||The SQL Server error log contains user-defined events and certain system events you can use for troubleshooting.||2.0|
|Service Accounts Status||no further description.||2.0|
|Recommended build check||no further description.||2.0|
|Global trace flags||no further description.||2.0|
|Errorlog based checks||Because it is a string based search, add other search conditions as deemed fit.||2.0|
Database | TempDB
|DBs with collation <> master subsection||no further description.||2.0|
|DBs with skewed compatibility level||no further description.||2.0|
|User DBs with non-default options||no further description.||2.0|
|DBs with Sparse||no further description. For more information, see the MSDN documentation||2.0|
|DBs Autogrow in percentage||no further description.||2.0|
|Backups and Database files in same volume||Data files and Logs / tempDB and user Databases / Backups and Database files in same volume (Mountpoint aware).||2.0|
|Get IO Disk Usage Percentage By Drive Letter||
Get I/O utilization by database.
Helps determine which database is using the most I/O resources on the instance.
|Get IO Disk Latency Percentage By IO Stalls By DB||Drive level latency information.||2.0|
|Get IO Disk Latencies Read/Write||Viewing Aggregate Information. Shows you the drive-level latency for reads and writes, in milliseconds. Latency above 20-25ms is usually a problem.||2.0|
|SQL Server Databases||View a List of Databases on an Instance of SQL Server.||2.0|
|I/O Usage By Database||It helps the DBA to understand where the I/O distribution is across databases at both the data and log file level.||2.0|
|Using DBCC LOGINFO||This command provides transaction log space usage statistics for all databases.||2.0|
|DBCC CHECK DB Latest Date||Expose last known good DBCC CHECKDB information – A new database option has been added to programmatically return the date of the last successful DBCC CHECKDB run. Users can now query DATABASEPROPERTYEX([database], ‘lastgoodcheckdbtime’) to obtain a single value representing the date/time of the last successful DBCC CHECKDB run on the specified database.||2.0|
|DBCC SQL Perf||Usage of DBCC SQLPERF for Transaction Log Management.
Transaction log is one of the main aspect of all databases. In some circumstances, the transaction logs get large and not know how much space is utilized by it, become a major issue. It effects the performance of SQL Server because of which user faces problem while working on it.
|Databases Quick Object Report||no further description.||2.0|
|Database Cache Size||
Insight into the SQL Server buffer cache
When we talk about memory usage in SQL Server, we are often referring to the buffer cache. This is an important part of SQL Server’s architecture, and is responsible for the ability to query frequently accessed data extremely fast. Knowing how the buffer cache works will allow us to properly allocate memory in SQL Server, gauge accurately how databases are accessing data, and ensure that there are not inefficiencies in our code that cause excessive data to be cached.
What is in the buffer cache?
Hard disks are slow; memory is fast. This is a fact of nature for anyone that works with computers. Even SSDs are slow when compared to high-performance memory. The way in which software deals with this problem is to write data from slow storage into fast memory. Once loaded, your favorite apps can perform very fast and only need to go back to disk when new data is needed. This fact of life in computing is also an important part of SQL Server architecture.
Whenever data is written to or read from a SQL Server database, it will be copied into memory by the buffer manager. The buffer cache (also known as the buffer pool) will use as much memory as is allocated to it in order to hold as many pages of data as possible. When the buffer cache fills up, older and less used data will be purged in order to make room for newer data.
Data is stored in 8k pages within the buffer cache and can be referred to as “clean” or “dirty” pages. A dirty page is one that has been changed since last being written to disk and is the result of a write operation against that index or table data. Clean pages are those that have not changed, and the data within them still matches what is on disk. Checkpoints are automatically issued in the background by SQL Server that will write dirty pages to disk in order to create a known good restore point in the event of a crash or other unfortunate server situation.
|User Table Heap DB Count||
Heap tables are tables without clustered index. A table in SQL Server can have a Clustered Index, in which case it is called a Clustered Table, and without a Clustered Index it is called a Heap Table.
In a heap table, the data is not sorted in any way; it is just a stack of unordered, unstructured data records. When you access a heap table using a SELECT statement, SQL Server uses a table scan operator in the execution plan if no suitable non-clustered index is defined. No table-seek operator is available. This is very important. A heap table does not scale with the amount of data you have. The more data you have, the longer the process takes. A table scan is always an O(n) operation (more on big-o notation) - it does not scale as your table grows larger and larger.
|Find out who created the Temporary Table||no further description.||2.0|
|TempDB Stats after Session||no further description.||2.0|
One of the new features introduced by Microsoft in SQL Server 2017 is the new DMF sys.dm_db_log_info. It helps to take a look at the structure of the transaction log file without using the undocumented DBCC LOGINFO.
As we all know, Transaction Log Files are internally divided into small virtual log files called VLF/VLFs. We can’t directly configure the size or number of VLFs which is actually affected by the number of activities. The number one reason is when we add a log file or the size of the log file get increased.
Having a large number of log files(a thousand or more) can affect the backup performance, restore, and database startups.
|Database Size Growth as a list||no further description.||2.0|
|How old your Backups||no further description.||2.0|
|Workload Analysis||Problematic SQL queries can severely reduce the performance of SQL Server and the business-critical applications that it supports. Consequently, it is essential to have access to detailed, real-time information on the performance of entire instances and databases, along with diagnostic tools to quickly locate and resolve specific performance issues.||2.0|
|Search Data for String||no further description.||2.0|
Performance | Monitoring
|Hints usage*||For more information, see the MSDN documentation.||2.0|
|Plan use ratio*||For more information, see the MSDN documentation.||2.0|
|Perf counters, Waits, Latches and Spinlocks||
Duration must be between 10s and 255s (4m 15s), with a default of 90s.
|Get Pending Memory Grants With Queries||Returns information about all queries that have requested and are waiting for a memory grant or have been given a memory grant. Queries that do not require a memory grant will not appear in this view.||2.0|
|Wait Stats||One of the most under-utilized performance troubleshooting methodologies in the SQL Server world is one called “waits and queues” (also known simply as “wait stats”). The basic premise is that SQL Server is permanently tracking why execution threads have to wait. You can ask SQL Server for this information and then use the results to narrow down where to start digging to unearth the cause of performance issues. The “waits” are what SQL Server tracks. The “queues” are the resources that the threads are waiting for. There are a myriad of waits in the system and they all indicate different resources being waited for. For example, a PAGEIOLATCH_EX wait means a thread is waiting for a data page to be read into the buffer pool from disk. A LCK_M_X wait means a thread is waiting to be granted an exclusive lock on something.||2.0|
|Wait Types Stats||Cumulative wait stats are not as useful on an idle instance that is not under load or performance pressure.||2.0|
|Session Diver IP Monitor||
Tracking the Number of Active Sessions on a Database.
Who's Using Your Databases and What Are They Doing?
How many users are on your databases? Well, there could be just one user (login) doing one heck of a lot, using many sessions. This is especially true if you have a shared connection to the application under one User ID so that there are many people or processes using one login name.
Ideally, this shouldn't happen; each user should be using their unique Windows login, thereby enabling you to audit what is going on properly. The better your security, the more users you are likely to have in proportion to the sessions because it means that users are not sharing logins.
Sadly, we don't always live in an ideal world, especially in IT. We, therefore, need to track SPIDs and logins.Tehe list shows a quick query to tell you how many sessions are being used by each login name on each database on a server.
|Top Logical Reads Queries||
Long running queries have been found in the plan cache. These may be ETL, reports, or other queries that should run long. Or it could be that someone is searching for all users whose names are LIKE '%%'. Either way, these queries bear investigating.
In some cases, this is the total clock time that the query took to execute and in others this is the total CPU time that the query took to execute. Queries with a high max_elapsed_time just take a lot of time to run – they could be slow single threaded queries. Queries with a high max_worker_time (CPU time) may be highly parallel queries.
|Total Logical Reads||Logical read indicates total number of data pages needed to be accessed from data cache to process query. It is very possible that logical read will access same data pages many times, so count of logical read value may be higher than actual number of pages in a table. Usually the best way to reduce logical read is to apply correct index or to rewrite the query.||2.0|
|Wait Info Summary||Another view - see "Causing Waits"||2.0|
|Wait Info||Another view - see "Causing Waits"||2.0|
|Top 20 Blocked Queries||SQL Server is able to service requests from a large number of concurrent users. When SQL Server is servicing requests from many clients, there is a strong possibility that conflicts will occur because different processes request access to the same resources at the same time. A conflict in which one process is waiting for another to release a resource is called a block. Although in SQL Server a blocked process usually resolves itself when the first process releases the resource but there are times when a process holds a transaction lock and doesn't release it. In this tip, we will learn different techniques to troubleshoot and resolve blocks in SQL Server.||2.0|
|Top Worker Time Queries||This Query is very basic and very simple, there are many different versions are available online. This basic script does do the job which I expect to do – find out the most expensive queries in SQL Server Box.||2.0|
|Top 20 Query Response Time For Individual Queries||Another view - no further description.||2.0|
|Execution Cache Single Use Plans Explore||no further description.||2.0|
|Find Long Running Queries||Identifying long running SQL queries can be a good starting point when doing a performance analysis. To find which SQL queries performed slower than expected, open the Result Viewer.||2.0|
|Top Avg Elapsed Time Queries||Another view - no further description.||2.0|
In SQL Server, internal latch architecture protects memory during SQL operations. It ensures the consistency of memory structures with read and write operation on pages. Rudimentarily, it has two classes, buffer latches, and non-buffer latches which perform lightweight synchronization in the SQL Engine.
The latch ensures memory consistency while Locks ensures logical transaction consistency. When multiple users or applications access the same data at the same time, locking prevents them from making simultaneous changes to the data. Locks are managed internally by the Microsoft SQL Server Compact Database Engine. While a user performs DML operations, locks are automatically acquired and released on resources. The latch ensures memory consistency on memory structures including indexes and data pages. Generally, SQL server uses buffer pool and IO latches to deal with synchronizing primitive manner in-memory structures. When there is a multiple thread concurrency load on the server then a latch result from an attempt to acquire an incompatible memory structure and in doing so, a latch contention issue can arise.
|Session Diver IP Monitor||no further description.||2.0|
|Blocking Live||This Query gives you a view of current connections on SQL Server. You can use Query to view information about the current processes and locks held on SQL Server resources.||2.0|
In SQL Server, transactions are the essential components to trace the database logs. The SQL Server executes all its Statements through the transaction logs. After executing the statement the transaction log contains a specific information about the committed transactions. In this article, we will know how to check active transactions in SQL Server with some T-SQL commands.
What are the Active Transactions in SQL Server?
An active transaction in SQL Server is a transaction which is currently being executed on the Server. Checking active transactions in SQL Server are helpful to verify the effectiveness of the database. The number of active transactions tells, there are certain transactions that are never completing. No other transaction can change the data which is held by the active transaction throughout an active transaction. While we are changing the data, we hold a unique lock on that data. Conversely, we are unable to read that data held by another transaction because another transaction holds a special lock on the data which stops you from analyzing it. Maintaining a low level of active transactions helps in maintaining the request for data accessibility. There are two different categories of methods with which you can explore and view SQL Server transaction logs.
|Log Buffer||This wait type is when a thread is waiting for a free log buffer in the log cache, so the current log block that is in the process of ending (because it reached the maximum 60KB size or because a transaction wrote an LOP_COMMIT_XACT log record) can be copied into the log cache and then subsequently written to disk.||2.0|
The SQL Server query optimizer uses distribution statistics when creating a query plan. The statistics provide information about the distribution of column values across participating rows, helping the optimizer better estimate the number of rows, or cardinality, of the query results.
The statistics provide the optimizer with clues on the best ways to process the query and consequently maximize performance. For example, the statistics might lead the optimizer to choose an index seek over an index scan, a potentially more resource-intensive operation. Without statics, it would be difficult for the optimizer to determine the best processing strategy in advance.
|Plan Statement Version 2||
What is a Query Plan?
A query plan is a set of steps that the database management system executes in order to complete the query. The reason we have query plans is that the SQL you write may declare your intentions, but it does not tell SQL the exact logic flow to use. The query optimizer determines that. The result of that is the query plan.
In SQL Server a query plan is called an execution plan.
|DBCC Opentran||DBCC OPENTRAN can be used to identify active transactions that may be preventing the protocol shortening. DBCC OPENTRAN displays information about the oldest active transaction and the oldest distributed and non-distributed replicated transactions (if any) in the transaction log of the specified database. Results are displayed only if there is an active transaction in the log or if the database contains replication information. If there are no active transactions in the log, an information message is displayed.||2.0|
|SQL Monitor Version 2||no further description.||2.0|
|SQL Handle Short||
With this query, the text of the respective SQLHandle is displayed.
The SQLHandle is a varbinary data type, which is suitable for binary data of variable length. The sql_handle is a token that references a stored procedure or a batch.
|SQL Handle Long||no further description.||2.0|
|Optimizer Info Martix||
The SQL Server Query Optimizer
To understand how to write SQL code for SQL Server that performs well, it is important to appreciate how the query optimizer works.
The SQL Server Query Optimizer is a cost-based optimizer. It analyzes a number of candidate execution plans for a given query, estimates the cost of each of these plans and selects the plan with the lowest cost of the choices considered. Indeed, given that the Query Optimizer cannot consider every possible plan for every query, it actually has to do a cost-based balancing act, considering both the cost of finding potential plans and the costs of plans themselves.
Therefore, it is the SQL Server component that has the biggest impact on the performance of your databases. After all, selecting the right (or wrong) execution plan could mean the difference between a query execution time of milliseconds, and one of minutes or even hours. Naturally, a better understanding of how the Query Optimizer works can help both database administrators and developers to write better queries and to provide the Query Optimizer with the information it needs to produce efficient execution plans.
|Most CPU IO Monitor||
Repetitively high CPU utilization – high CPU utilization can be seen in Windows Task Manager. This is an obvious one when you see that the CPU is pegged that’s a good sign that something is going on
I/O increase – CPU issues can be manifested from I/O and memory problems. So, I/O should also be checked when troubleshooting
High recompiles – many recompiles can add some cycles to the CPU
Expensive queries – even more so than the previous one
|Transaction||no further description.||2.0|
|Current Running Transaction Monitor||no further description.||2.0|
|Check SP (Top 10)||no further description.||2.0|
|Buffer Object per Database||no further description.||2.0|
|Misleading SQL Server Performance Counters||no further description.||2.0|
|Long Running Query SQL 2016||no further description.||2.0|
|Query Plan Version 2||no further description.||2.0|
Security | Maintenance | Naming Convention
|SQL Server Jobs||
Tells you the account being used for the SQL Server Service and the SQL Agent Service.
Shows the process_id, when they were last started, and their current status.
|DBCC CHECKDB||Direct Catalog Updates and Data Purity subsection.||2.0|
|SQL Agent TSQL Job Step Binary||SQL Server Agent allows you to use tokens in Transact-SQL job step scripts. Using tokens when you write your job steps gives you the same flexibility that variables provide when you write software programs. After you insert a token in a job step script, SQL Server Agent replaces the token at run time, before the job step is executed by the Transact-SQL subsystem.||2.0|
|Database Level Privileges||no further description.||2.0|
|System health error checks||no further description.||2.0|
|SQL Agent alerts for severe errors||no further description.||2.0|
|Password check||no further description.||2.0|
|SQL Server Agent Jobs||no further description.||2.0|
Index | Statistics
|Check Old Statistics||no further description.||2.0|
|Missing Indexes||Outputs only potentially most relevant, based in scoring method - use at you own discretion). These include drops for Duplicate, Redundant, Hypothetical and Rarely Used indexes, as well as creation statements for FK and Missing Indexe.