DBCC SHOW_STATISTICS('schema.Table','IndexName')
- Metadata about the statistics including date and time of the last update of the statistics, number of rows in the table, number of rows sampled, number of steps in the histogram, index density, average key length, whether the index contains string summary statistics, filtered predicate for statistics if applicable, and number of rows before applying the filter.
- Index densities for the combination of columns in the index.
- A histogram of up to 200 sample values in the first key column in the indexv
STATS_DATE
SELECT name AS ObjectName,
STATS_DATE(object_id, stats_id) AS UpdateDate
FROM sys.stats
WHERE object_id = OBJECT_ID('Sales.Customers');
If you use SQL Server Profiler to capture a deadlock graph, you must configure the trace
before deadlocks occur.
Events:
- Deadlock graph
- Lock:Deadlock
- Lock:Deadlock Chain
- sys.sp_xtp_control_proc_exec_stats Use this system stored procedure to enable statistics collection for your SQL Server instance at the procedure level.
- sys.sp_xtp_control_query_exec_stats Use this system stored procedure to enable statistics collection at the query level for selected natively compiled stored procedures.
sys.dm_exec_* Connections, sessions, requests, and query execution
sys.dm_os_* Information for the operating system on which SQL Server runs
sys.dm_tran_* Details about transactions
sys.dm_io_* IO processes
sys.dm_db_* Database-scoped information
-
sys.dm_os_memory_cache_counters View the current state of the cache.
-
sys.dm_os_sys_memory View resource usage information for the server, including total physical and available memory and high or low memory state.
-
sys.dm_os_memory_clerks View usage information by memory clerk processes that manage memory for SQL Server.
-
sys.dm_tran_locks Use this DMV to view all current locks, the lock resources, lock mode, and other related information.The sys.dm_tran_locks DMV provides you with information about existing locks and locks that have been requested but not yet granted in addition to details about the resource for which the lock is requested. You can use this DMV only to view information at the current point in time. It does not provide access to historical information about locks.
-
sys.dm_os_waiting_tasks Use this DMV to see which tasks are waiting for a resource. Whenever a user asks you why a query is taking longer to run than usual, a review of this DMV should be one of your standard troubleshooting steps
-
sys.dm_os_wait_stats Use this DMV to see how often processes are waiting while locks are taken.The sys.dm_os_wait_stats DMV is an aggregate view of all waits that occur when a requested resource is not available, a worker thread is idle typically due to background tasks, or an external event must complete first. Potential issues:
- CPU pressure Compare the signal wait time to the total wait time to determine the relative percentage of time that a thread has to wait for its turn to run on the CPU. When this value is relatively high, it can be an indicator that the CPU is overwhelmed by queries that require tuning or your server needs more CPU. You can confirm whether the issue is related to CPU by checking the runnable_tasks_count column in the sys.dm_os_schedulers DMV to see if there is a high number of tasks in the runnable queue. You might also see a higher occurrence of the SOS_SCHEDULER_YIELD wait type if the CPU is under pressure. In addition, you can monitor CPU-related performance counters as described in Skill 4.4.
- IO issues If tasks are waiting for the IO subsystem, you will see waits that contain IO in the name. In particular, monitor the trend in average wait time which is calculated by dividing wait_time_ms by waiting_tasks_count. If it starts trending upward, investigate IO using performance counters. Two wait types that will appear frequently in this DMV when IO issues exist are ASYNC_IO_COMPLETION and IO_COMPLETION. Check physical disk performance counters to confirm this diagnosis, which we describe in the next section, “Troubleshoot and analyze storage, IO, and cache issues.” Consider adding indexes to reduce IO contention. You might also see PAGEIOLATCH waits when a thread is waiting for latches to release after writing a data page in memory to disk or WRITELOG waits when the log management system is waiting to flush to disk. These wait types can indicate either an IO subsystem problem or a memory problem. To narrow down the possibilities, you need to check IO statistics by using sys.dm_io_virtual_file_stats and by reviewing IO-related performance counters.
- Memory pressure The PAGEIOLATCH wait might also indicate memory pressure instead of an IO subsystem problem. It appears when SQL Server does not have enough free memory available for the buffer pool. Check the Page Life Expectancy performance counter to see if it is dropping as compared to a baseline value to confirm whether memory is the reason for this wait type. If you see an increase in CXPACKET waits in conjunction with PAGEIOLATCH waits, the culprit could be a query plan using large table or index scans. Another indicator of a memory pressure issue is the RESOURCE_SEMAPHORE wait. It occurs when a query requests more memory than is currently available. You can check the sys.dm_exec_query_memory_grants DMV and combine it with sys.dm_exec_sql_text and sys.dm_exec_sql_plan DMVs to find the memoryintensive queries and review their query plans.
-
sys.dm_db_index_usage_stats Use this DMV to review the use of indexes to resolve queries. Counts in this DMV are reset when the server restarts or when an index is dropped and recreated.
SELECT OBJECT_NAME(ixu.object_id, DB_ID('WideWorldImporters')) AS
[object_name] ,
ix.[name] AS index_name ,
ixu.user_seeks + ixu.user_scans + ixu.user_lookups AS
user_reads,
ixu.user_updates AS user_writes
FROM sys.dm_db_index_usage_stats ixu
INNER JOIN WideWorldImporters.sys.indexes ix ON
ixu.[object_id] = ix.[object_id] AND
ixu.index_id = ix.index_id
WHERE ixu.database_id = DB_ID('WideWorldImporters')
ORDER BY user_reads DESC;
- sys.dm_db_index_physical_stats Use this dynamic management function (DMF) to check the overall status of indexes in a database.
DECLARE @db_id SMALLINT, @object_id INT;
SET @db_id = DB_ID(N'WideWorldImporters');
SET @object_id = OBJECT_ID(N'WideWorldImporters.Sales.Orders');
SELECT ixs.index_id AS idx_id,
ix.name AS ObjectName,
index_type_desc,
page_count,
avg_page_space_used_in_percent AS AvgPageSpacePct,
fragment_count AS frag_ct,
avg_fragmentation_in_percent AS AvgFragPct
FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'Detailed') ixs
INNER JOIN sys.indexes ix ON
ixs.index_id = ix.index_id AND
ixs.object_id = ix.object_id
ORDER BY avg_fragmentation_in_percent DESC;
- sys.dm_db_missing_index_details Use this DMV to identify the columns used for equality and inequality predicates.
- sys.dm_db_missing_index_groups Use this DMV as an intermediary between sys.dm_db_index_details and sys.dm_db_missing_group_stats.
- sys.dm_db_missing_index_group_stats Use this DMV to retrieve metrics on a group of missing indexes.
- SET SHOWPLAN_XML ON statement instructs SQL Server to generate the estimated plan without executing the query.
- SET SHOWPLAN_TEXT ON Returns a single column containing a hierarchical tree that describes the operations and includes the physical operator and optionally the logical operator.
- SET SHOWPLAN_ALL ON Returns the same information as SET SHOWPLAN_TEXT except the information is spread across a set of columns in which you can more easily see property values for each operator.
Using Extended Events is a lightweight approach to capturing query plans. There are two Extended Events that you can use to review query plans:
- query_pre_execution_showplan This Extended Event captures the estimated query plan for a query. An estimated query plan is prepared without executing the query.
- query_post_execution_showplan This Extended Event captures the actual query plan for a query. An actual query plan is the estimated query plan that includes runtime information. For this reason, it is not available until after the query executes.
Although SQL Trace is designated as a deprecated feature and will be removed from a future release of SQL Server, it remains an available option in SQL Server 2016. You can define server-side traces by using system stored procedures and then run these traces on demand or on a scheduled basis. As an alternative, you can use SQL Server Profiler as a client-side option. The overhead of running server-side traces is much less than the overhead of using SQL Server Profiler, but the overhead is still significant. Therefore, take care when using SQL Trace in a production environment regardless of the approach you take and disable tracing as soon as possible.
- sp_trace_create This procedure creates a new trace and defines a file into which SQL Server stores trace data. It returns a trace ID that you reference in the other procedures to manage the trace.
- sp_trace_setevent This procedure must be called once for each data column of the events to capture in the trace. That means you must call this procedure many times for any single trace. When you call this procedure, you pass in the following arguments, the trace identifier captured as output when you create the trace, the event identifier, the column identifier, and the status of ON (1) or OFF (0).
- sp_trace_setfilter This procedure must be called once for each filter on an event data column.
- sp_trace_setstatus This procedure starts, stops, or removes a trace. It must be stopped and removed before you can open the related trace file.
You can use SQL Server Profiler instead of manually creating the many stored procedures to define a trace when you need to capture a query plan. On the File menu, click New Trace, and then connect to the server on which you want to run the trace. In the Trace Properties dialog box, click the Events Selection tab and then select the Show All Events checkbox. Expand the Performance node, and select one or more of the following checkboxes:
- Showplan XML This event is raised when SQL Server selects an estimated query plan.
- Showplan XML For Query Compile This event is raised when SQL Server compiles a query and produces an estimated query plan which it adds to the query plan cache. Generally, this event is raised only once for a query unless the query requires recompilation.
- Showplan XML Statistics Profile This event is raised after SQL Server executes a query and has generated an actual query plan.
- Data Flush Interval (Minutes) The frequency in minutes at which SQL Server writes data collected by the query store to disk.
- Statistics Collection Interval The granularity of time for which SQL Server aggregates runtime execution statistics for the query store. You can choose one of the following intervals: 1 minute, 5 minutes, 10 minutes, 15 minutes, 30 minutes, 1 hour, or 1 day. If you capture data at a high frequency, bear in mind that the query store requires more space to store more finely grained data.
- Max Size (MB) The maximum amount of space allocated to the query store. The default value is 100 MB per database. If your database is active, this value might not be large enough to store query plans and related information.
- Query Store Capture Mode The specification of the types of queries for which
SQL Server captures data for the query store. You can choose one of the following
options:
- None The query store stops collecting data for new queries, but continues capturing data for existing queries.
- All The query store captures data for all queries.
- Auto The query store captures data for relevant queries. It ignores infrequent queries and queries with insignificant compile and execution duration.
- Size Based Cleanup Mode The specification of whether the cleanup process activates when the query store data approaches its maximum size (Auto) or never runs (OFF).
- Stale Query Threshold (Days) The number of days that SQL Server keeps data in the query store.
ALTER DATABASE <databasename>
SET QUERY_STORE = ON
(
OPERATION_MODE = READ_WRITE ,
CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 30 ),
DATA_FLUSH_INTERVAL_SECONDS = 3000,
MAX_STORAGE_SIZE_MB = 500,
INTERVAL_LENGTH_MINUTES = 50
);
The query store captures information about query plans and runtime execution statistics until the maximum space allocation is reached. You can review this data in the following DMVs:
- sys.query_store_plan Query plan information, such as Showplan XML, the number of compilations, the date and time of the initial and last compilations, the last execution date and time, and the average and most recent duration of compilation, among other details. The query plan available in this DMV is the estimated plan only.
- sys.query_store_query Aggregated runtime execution statistics for a query, including CPU binding, memory, optimization, and compilation statistics. This information is stored at the statement level and not at the batch level which is different from the behavior of sys.dm_exec_query_stats.
- sys.query_store_query_text The text of the executed query.
- sys.query_store_runtime_stats Runtime execution statistics for a query, such as first and last execution date and time, the number of executions, statistics (average, last, minimum, maximum, and standard deviation) for query duration, CPU time, logical IO reads and writes, physical IO reads and writes, CLR time, DOP, maximum used memory, and row counts.
- sys.query_store_runtime_stats_interval The start and end times defining the intervals during which SQL Server collects runtime execution statistics for the query store.
- sp_query_store_flush_db Flush the portion of the query store currently in memory to disk. This stored procedure takes no arguments.
- sp_query_store_force_plan Force SQL Server to use a specified query plan for a specified query. You provide identifiers for the query and plan as arguments for this stored procedure.
- sp_query_store_remove_plan Remove a specified query plan from the query store.
- p_query_store_remove_query Remove a specified query from the query store, in addition to the query plans and runtime execution statistics related to it.
- sp_query_store_reset_exec_stats Reset the runtime execution statistics for a specified plan.
- sp_query_store_unforce_plan Keep a specified query plan in the query store, but no longer force SQL Server to use it for a specified query.
You must use the elastic database client library to implement standard sharding patterns in a SQL Database by calling its features in your elastic scale application. You use it to perform operations across the all shards as a unit or to perform operations on individual shards, as shown in Figure 4-39. The elastic database client library provides the following features:
- Shard map management You first register each database as a shard, and then define a shard map manager that directs connection requests to the correct shard by using a sharding key or a key range. A sharding key is data such as a customer ID number that the database engine uses to keep related transactions in one database.
- Data-dependent routing Rather than define a connection in your application, you can use this feature to automatically assign a connection to the correct shard.
- Multishard querying The database engine uses this feature to process queries in parallel across separate shards and then combine the results into a single result set.
- Shard elasticity This feature monitors resource consumption for the current workload and dynamically allocates more resource as necessary and shrinks the database to its normal state when those resources are no longer required.
- Express This edition is a free version of SQL Server with limited features that you
can use for small applications and Web sites. The maximum database size supported
by this edition is 10 GB. It uses up to 1 GB memory and to the lesser of 1 physical
processor or 4 cores. There are three types of SQL Server 2016 Express from which
to choose:
- LocalDB You use LocalDB for a simple application with a local embedded database that runs in single-user mode.
- Express You use Express when your application requires a small database only and does not require any other components packaged with SQL Server in the Standard edition. You can install this edition on a server and then enable remote connections to support multiple users.
- Express with Advanced Services This edition includes the database engine as well as Full Text Search and Reporting Services.
- Web This edition is scalable up to 64 GB of memory and the lesser of 4 physical processors or 16 cores with a maximum database size of 524 PB. It includes the database engine, but without support for availability groups and other highavailability features. It also does not include many of the advanced security and replication features available in Standard or Enterprise edition, nor does it include the business intelligence components such as Analysis Services and Reporting Services, among others. Web edition is intended for use only by Web hosters and third-party software service providers.
- Standard This edition scales to 128 GB of memory and the lesser of 4 physical processors or 24 cores. The maximum database size with Standard edition is 524 PB. This edition includes core database and business intelligence functionality and includes basic high-availability and disaster recovery features, new security features such as row-level security and dynamic data masking, and access to non-relational data sources by using JSON and PolyBase.
- Enterprise This edition includes all features available in the SQL Server platform and provides the highest scalability, greatest number of security features, and the most advanced business intelligence and analytics features. Like Standard edition, Enterprise edition supports a database size up to 524 PB, but its only limits on memory and processor sizes are the maximums set by your operating system. To support higher availability, this edition supports up to 8 secondary replicas, with up to two synchronous secondary replicas, in an availability group, online page and file restore, online indexing, fast recovery, mirrored backups, and the ability to hot add memory and CPU. For greater performance, Enterprise edition supports in-memory OLTP, table and index partitioning, data compression, Resource Governor, parallelism for partitioned tables, multiple file stream containers, and delayed durability, among other features. Enterprise edition includes many security features not found in Standard edition. In particular, Always Encrypted protects data at rest and in motion. Additional security features exclusive to Enterprise edition include more finely-grained auditing, transparent data encryption, and extensible key management. Features supporting data warehouse operations found only in Enterprise edition include change data capture, star join query optimizations, and parallel query processing on partitioned indexes and tables.
- Developer This edition is for developers that create, test, and demonstrate applications using any of the data platform components available in Enterprise edition. However, the Developer edition cannot be used in a production environment.
- Evaluation This edition is a free trial version of SQL Server 2016. You can use this for up to 180 days that you can use to explore all of the features available in Enterprise Edition before making a purchasing decision.
- Basic This service tier has a maximum database size of 2 GB and performance level of 5 DTUs. You use this option when you need a small database for an application or website with relatively few concurrent requests. The benchmark transaction rate is 16,600 transactions per hour.
- Standard This service tier has a maximum database size of 250 GB and performance levels ranging from 10 to 100 DTUs. You can use this option when a database needs to support multiple applications and multiple concurrent requests for workgroup and web applications. With 50 DTUs, the benchmark transaction rate is 2,570 transactions per minute.
- Premium This service tier has a maximum database size of 1 TB and performance levels ranging from 125 to 4,000 DTUs. You use this option for enterprise-level database requirements. With 1,000 DTUs, the benchmark transaction rate is 735 transactions per second.