EXECUTION PLAN

 What is execution plan and explain it? 

Execution plan graphically displays the data retrieval methods chosen by SQL Server. It represents the execution cost of specific statements and quires in SQL Server. This graphical approach is very useful for understanding the performance of the query. 

What is an execution plan? When would you use it? How would you view the execution plan?

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by SQL Server Query optimizer for a stored procedure or ad-hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure science the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called “Show Execution Plan” (located on the query drop-down menu). If this option is tuned on it will display query execution plan in separate window query is ran again.

Execution plan displays:

1. Physical operations

2. Logical operations

3. Actual Number rows

4. Estimated I/O cost

5. Estimated CPU cost

6. Number of Executions

7. Estimated Number of Executions

8. Estimated Operator cost

9. Estimated Subtree cost

10. Estimated Number of Rows

11. Estimated Row Size

12. Actual rebinds

13. Actual rewinds

14. Key lookup

15. Nested look up

16. Index seek

17. Index scan

TEMPDB ARCHITECTURE

 What is Stored in Tempdb?

Tempdb is used to store three different categories of temporary data:

– User Objects

– Internal Objects

– Version Stores

User Objects:

• Local and global temporary tables and indexes

• User-defined tables and indexes

• Table variables

• Tables returned in table-valued functions

Note: These lists are not designed to be all inclusive.

Internal Objects:

• Work tables for DBCC CHECKDB and DBCC CHECKTABLE.

• Work tables for hash operations, such as joins and aggregations.

• Work tables for processing static or keyset cursors.

• Work tables for processing Service Broker objects.

• Work files needed for many GROUP BY, ORDER BY, UNION, and SELECT DISTINCT operations.

• Work files for sorts that result from creating or rebuilding indexes (SORT_IN_TEMPDB).

• Storing temporary large objects (LOBs) as variables or parameters (if they won’t fit into memory).

Version Stores:

• The version store is a collection of pages used to store row level versioning of data.

• There are two types of version stores:

1. Common Version Store: Used when:

– Building the inserted and deleted tables in after triggers.

– When DML is executed against a database using snapshot transactions or read-committed row versioning isolation levels.

– When multiple active result sets (MARS) are used.

2. Online-Index-Build Version Store: Used for online index builds or rebuilds. EE edition only.

Tempdb doesn’t act as an other databases:

• Tempdb only uses simply recovery model.

• Manydb options not be able to change.

• Tempdb may not be dropped, attached or detached.

• Tempdb may not backed up, restore, can’t implement any HA options.

Types of Tempdb Problems:

• Generally, there are three major problems you run into with Tempdb:

1. Tempdb is experiencing an I/O bottleneck, hurting server performance.

2. Tempdb is experiencing DDL and/or allocation contention on various global allocation structures (metadata pages) as temporary objects are being created, populated, and dropped. E.G. Any space-changing operation (such as INSERT) acquires a latch on PFS, SGAM or GAM pages to update space allocation metadata. A large number of such operations can cause excessive waits while latches are acquired, creating a bottleneck, and hurting performance.

3. Tempdb has run out of space.

SOLUTION:

Use performance Monitor:

And also DMV’S are useful what is going on Tempdb

• sys.dm_db_file_space_usage: Returns one row for each data file in Tempdb showing space usage.

• sys.dm_db_task_space_usage: Returns one row for each active task and shows the space allocated and deallocated by the task.

• sys.dm_db_session_space_usage: Returns one row for each session, with cumulative values for space allocated and deallocated by the session.

Monitoring Tempdb Space:

Performance Counters:

• SQL Server: Database: Data File(s) Size (KB): tempdb

• SQL Server: Database: Log File(s) Used Size (KB): tempdb

• SQL Server: Transactions: Free Space in tempdb (KB)

DMV

• sys.dm_db_file_space_usage

Errors in tempdb running slow check in error logs:

• Check the SQL Server error log for these errors:

– 1101 or 1105: A session has to allocate more space in tempdb in order to continue

– 3959: The version store is full.

– 3967: The version store has been forced to shrink because tempdb is full.

– 3958 or 3966: A transaction is unable to find a required version record in tempdb.

Note: Be sure auto growth is turned on for tempdb, and ensure that you have enough available free disk space.

Operations that cannot be performed on the tempdb database:

> Adding filegroups.

> Backing up or restoring the database.

> Changing collation. The default collation is the server collation.

> Changing the database owner. Tempdb is owned by dbo.

> Creating a database snapshot.

> Dropping the database.

> Dropping the guest user from the database.

> Participating in database mirroring.

> Removing the primary filegroup, primary data file, or log file.

> Renaming the database or primary filegroup.

> Running DBCC CHECKALLOC.

> Running DBCC CHECKCATALOG.

> Setting the database to OFFLINE.

> Setting the database or primary filegroup to READ_ONLY.

DMV’S [DYNAMIC MANAGEMENT VIEWS]

 > This concept is introduced in SQL Server 2005 version.

> The main purpose we can monitor SQL Server without consuming hardware resources like DBCC queries.

> The DMV’S newly introduced in SQL Server 2005 gives the database administrator information about the current state of the SQL Server machine.

> These Values will help the administrator to diagnose problems and tune the server for optimal performance.

> The DMV’S in SQL Server are designed to give you a window into what’s going on inside SQL Server

> They can provide information on what’s currently happening inside the server as well as the objects it’s strong. They are designed to be used instead of system tables and various functions.

> DMV’S are stored in sys schema and they start with dm_in the name

To know list of DMV’S:

SELECT name, type, type_desc FROM sys.system_objects WHERE name LIKE 'dm_%' ORDER BY name

Output:

List of DMV'S

2005 version of Sql -89….. 2008 version of sql-176……2012 version of sql-900+

There are two types of dynamic management views and functions:

Server-scoped dynamic management views and functions: These require VIEW SERVER STATE permission on the server.

Database-scoped dynamic management views and functions: These require VIEW DATABASE STATE permission on the database.

There are multiple categories close to 17... In which these views and functions have been organized

We have 85 of these views and functions. To give a further split, 76 of these are views and 9 of them are functions... Below are the 4 types of DMV which can be used frequently.

1. SQL Server Related [Hardware Resources] DMV’S

2. Database Related DMV’S

3. Index Related DMV’S

4. Execution Related DMV’S

5. Replication Related DMV’S

6. Query notifications Related DMV’S

7. SQL Operating System Related DMV’S

8. I/O Related DMV’S

9. Transaction Related DMV’S

1. SQL Server related [Hardware Resources] DMV’S:

> This section contains the dynamic management views that are associated with the SQL Server Operating System (SQLOS). The SQLOS is responsible for managing operating system resources that are specific to SQL Server.

Locks:

Select * from Sys.dm_tran_locks:

Returns information about locks

Blockings:

Select * from sys.dm_os_waiting_tasks:

> Returns information about the wait queue of tasks that are waiting on some resource.

Sys.dm_os_wait_stats:

> Returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.

2. Database Related DMV’S:

Mirroring:

1. Sys.dm_db_mirroring_auto_page_repair:

1. Returns a row for every automatic page-repair attempt on any mirrored database on the server instance.

2. This view contains rows for the latest automatic page-repair attempts on a given mirrored database, with a maximum of 100 rows per database.

2. Sys.dm_db_mirroring_connections:

Returns a row for each connection established for database mirroring.

3. INDEX related DMV’S

Fragmentation: DMV's to find 2005 onwards

1. Select * from sys.dm_db_index_physical_stats:

To find Column to verify the fragmentation value:

Avg_fragementaion_in_pernt:

Missing Index:

Select * from sys.dm_db_missing_index_details:

Returns detailed information about missing indexes, excluding spatial indexes.

sys.dm_db_index_usage_stats:

Returns counts of different types of index operations and the time each type of operation was last performed in SQL Server.

4. Execution related DMV’S:

Sys.dm_exec_cached_plans:

> Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

Sys.dm_exec_connections:

> Returns information about the connections established to this instance of SQL Server and the details of each connection.

Sys.dm_exec_sessions:

> shows information about all active user connections and internal tasks. This information includes client version, client program name, client login time, login user, current session setting, and more.

Sys.dm_exec_cursors:

> Returns information about the cursors that are open in various databases.

5. Replication related DMV’S:

Sys.dm_repl_articles:

> Returns information about database objects published as articles in a replication topology.

Sys.dm_repl_tranhash:

> Returns information about transactions being replicated in a transactional publication.

Sys.dm_repl_schemas:

> Returns information about table columns published by replication.

Sys.dm_repl_traninfo:

> Returns information on each replicated or change data capture transaction.