Folder SQL Server Database
Last updated 02 March 2021.
SQL Server Database - 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.
Before you start a query from the sql server area, it is important that you first select the database you want to process.
Open the "options" section.
Then select the desired database from the dropdown box.
All queries for databases are now queried for this database. You can change the data at any time.
Contains the following informational sections:
In the SQL database section there are now some queries that you can filter on special table names. When you start the query and a dialog box with the "SpecifiedTable" parameters appears, you can enter the table name or leave the parameter value empty. If you confirm with okay and have inserted a table name, the SQL query will be executed specifically on this table. If you leave the parameter value blank, the SQL query will be executed for the entire database.
The filtering applies to the following SQL queries:
- SQL Server Database -> Automatic Statistics Updating
- SQL Server Database -> Buffer Usage
- SQL Server Database -> Last time table was accessed
- SQL Server Database -> Objekt modify
|Automatic Statistics Updating||Statistics for query optimization are objects that contain statistical information about the distribution of values in one or more columns of a table or indexed view. The Query Optimizer uses these statistics to estimate the cardinality, or number of rows, in the query result. These cardinality estimates enable the Query Optimizer to create a high-quality query plan. For example, depending on your predicates, the Query Optimizer could use cardinality estimates to choose the index seek operator instead of the more resource-intensive index scan operator, and in doing so improve query performance.||2.0|
|Query Execution Counts||Returns aggregate performance statistics for cached query plans in SQL Server. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.||2.0|
If you’re dealing with lots of application generated, dynamic or ad hoc T-SQL queries, then attempting to determine tuning opportunities, missing indexes, incorrect structures, etc., becomes much more difficult because you don’t have a single place to go to see what’s happening. Each ad hoc query looks different… or do they. Introduced in SQL Server 2008 and available in the standard Dynamic Management Objects (DMO), we have a mechanism to identify ad hoc queries that are similar in structure through the query hash.
Query hash values are available in the following DMOs: sys.dm_exec_requests and sys.dm_exec_query_stats. Those two cover pretty much everything you need, what’s executing right now, what has recently executed (well, what is still in cache that was recently executed, if a query isn’t in cache, you won’t see it). The query hash value itself is nothing other than the output from a hash mechanism. A hash is a formula that outputs a value based on input. For the same, or similar, input, you get the same value. There’s also a query_plan_hash value that’s a hash of the execution plan.
|Recent Full Backups||Retrieve SQL Server database backup history.||2.0|
Determine SQL Server memory use by database and object.
For many people, the way that SQL Server uses memory can be a bit of an enigma. A large percentage of the memory your SQL Server instance utilizes is consumed by buffer pool (essentially, data). Without a lot of digging, it can be hard to tell which of your databases consume the most buffer pool memory, and even more so, which objects within those databases. This information can be quite useful, for example, if you are considering an application change to split your database across multiple servers, or trying to identify databases that are candidates for consolidation.
|IO Stats By File||
SQL Server is usually a high I/O activity process and in most cases the database is larger than the amount of memory installed on a computer and therefore SQL Server has to pull data from disk to satisfy queries. In addition, since the data in databases is constantly changing these changes need to be written to disk. Another process that can consume a lot of I/O is the TempDB database. The TempDB database is a temporary working area for SQL Server to do such things as sorting and grouping. The TempDB database also resides on disk and therefore depending on how many temporary objects are created this database could be busier than your user databases.
Since I/O is such an important part of SQL Server performance you need to make sure your disk subsystem is not the bottleneck. In the old days this was much easier to do, since most servers had local attached storage. These days most SQL Servers use SAN or NAS storage or to further complicate things more and more SQL Servers are running in a virtualized environment.
|Last Update Object||If a user wants to finds out when was the last table updated he can query dynamic management view (dmv) – sys.dm_db_index_usage_stats and easily figure out when was the table updated last. Let us comprehend this example by creating a table and updating it. We can use dmv to determine when it was updated last.||2.0|
|Information Object||Which of the tables don't have primary keys?||2.0|
Basics of Statistics
SQL Server Query Optimizer uses statistics to estimate the distribution of values in one or more columns of a table or index views, and the number of rows (called cardinality) to create a high-quality query execution plan. Often statistics are created on a single column but it’s not uncommon to create statistics on multiple columns.
Each statistics object contains a histogram displaying the distribution of values of the column (or of the first column in the case of multi-column statistics). Multi-column statistics also contains a correlation of values among the columns (called densities), which are derived from the number of distinct rows or the column values.
There are different ways you can view the details of the statistics objects. For example, as shown in the query below, you can use the DBCC SHOW_STATISTICS command. DBCC SHOW_STATISTICS shows the header, histogram, and density vector based on data stored in the statistics object.
|Database Size History||Trending Database Growth From Backups.||2.0|
|Available Space in MB||no further description.||2.0|
|Check Statistics||no further description.||2.0|
|Find Queries using the index in Query Store 2016||no further description.||2.0|
|Find tables with similar table structure||
Find the tables whose name is not the same as the current table.
Get the count of matching column names.
The ones with the highest column name match percent are most likely related tables.
|Last time table was accessed||no further description.||2.0|
|Objekt modify||no further description.||2.0|
|Parameter Stored Procedure||no further description.||2.0|
|Search for Queries in the Execution Plan Cache||no further description.||2.0|
|Search the Execution Plan Cache for Index Hints||no further description.||2.0|
|Search the Exec Plan Cache for Index Hints 2016||no further description.||2.0|