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.

STORED PROCEDURES IN SQL SERVER

 > A stored procedure is a group of Sql statements that has been created and stored in the database. Stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedure will reduce network traffic and increase the performance. If we modify stored procedure all the clients will get the updated stored procedure

In SQL we are having different types of stored procedures are there

a) System Stored Procedures

b) User Defined Stored procedures

c) Extended Stored Procedures

System Stored Procedures:

System stored procedures are stored in the master database and these are starts with a sp_ prefix. These procedures can be used to perform variety of tasks to support Sql server functions for external application calls in the system tables and use to perform many administrative and informational activities.

Ex: sp_helptext [StoredProcedure_Name]

User Defined Stored Procedures:

User Defined stored procedures are usually stored in a user database and are typically designed to complete the tasks in the user database. While coding these procedures don’t use sp_ prefix because if we use the sp_ prefix first it will check master database then it comes to user defined database.

Stored procedure are modules or routines that encapsulate code for reuse. A stored procedures can take input parameters, return tabular or scalar results and messages to the client.

Extended Stored Procedures:

Extended stored procedures are the procedures that call functions from DLL files that an instance of Microsoft SQL Server can dynamically load and run. Now a day’s extended stored procedures are depreciated for that reason it would be better to avoid using of Extended Stored procedures.

Why use Stored Procedures?

> Rewriting inline SQL statements as Stored Procedures

> Compilation and storing of the query execution plan

> Enabling of conditional and procedural logic

> Centralized repository for DML and DDL code enabling code reuse

> Protection from SQL Injection attacks

> Enabling of strict security model

> Readability

Sample of creating Stored Procedure

USE AdventureWorks2008R2;

GO

CREATE PROCEDURE dbo.sp_who

AS

SELECT FirstName, LastName FROM Person.Person;

GO

EXEC sp_who;

EXEC dbo.sp_who;

GO

DROP PROCEDURE dbo.sp_who;

GO

Advantages of using stored procedures

a) Stored procedure allows modular programming.

You can create the procedure once, store it in the database, and call it any number of times in your program.

b) Stored Procedure allows faster execution.

If the operation requires a large amount of SQL code is performed repetitively, stored procedures can be faster. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times.

c) Stored Procedure can reduce network traffic.

An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.

d) Stored procedures provide better security to your data

Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly.

DBCC (DATABASE CONSOLE COMMANDS) COMMANDS

 > DBCC commands are used to check the consistency of the database or database objects. While executing DBCC commands the DB engine creates a database snapshot and then runs the checks against this snapshot. After the DBCC command is completed, this snapshot is dropped.

> It’s consuming hardware resources. The DBCC commands are most useful for performance and troubleshooting exercises.

DBCC commands broadly falls into four categories:

 Maintenance

 Informational

 Validation

 Miscellaneous

MAINTENANCE COMMANDS:

Perform the maintenance tasks on a database, index, or filegroup.

CLEANTABLE:

> Reclaims space from dropped variable-length columns in tables or indexed views.

DBCC CLEANTABLE (‘Database name’, ‘Table name’, size)

DBREINDEX:

> Rebuilds one or more indexes for a table in the specified database.

DBCC DBREINDEX (‘Table name’, ‘Index name’, Fill factor)

DROPCLEANBUFFERS:

> Removes all clean buffers from the buffer pool.

DBCC DROPCLEANBUFFERS

FREEPROCCACHE:

> Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool.

DBCC FREEPROCCACHE

INDEXDEFRAG:

> Defragments indexes of the specified table or view.

DBCC INDEXDEFRAG (‘Database name’,’Table name’,’index name, partition number’)

SHRINK DATABASE:

> Shrinks the size of the data and log files in the specified database.

DBCC SHRINKDATABASE (‘Database name’, target percentage)

SHRINKFILE:

> Shrinks the size of the specified data or log file for the current database, or empties a file by moving the data from the specified file to other files in the same filegroup, allowing the file to be removed from the database. You can shrink a file to a size that is less than the size specified when it was created. This resets the minimum file size to the new value.

DBCC SHRINKFILE (‘File name’, target percentage)

INFORMATIONAL COMMANDS:

Performs tasks that gather and display various types of information.

CONCURRENCYVIOLATION:

> Is maintained for backward compatibility. It runs but returns no data.

DBCC CONCURRENCYVIOLAION

UPDATEUSAGE:

> Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.

DBCC UPDATEUSAGE (‘Database name’)

INPUTBUFFER:

> Displays the last statement sent from a client to an instance of Microsoft SQL Server 2005.

DBCC INPUTBUFFER (session id)

OPENTRAN:

> Displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions.

DBCC OPENTRAN (‘database name’)

OUTPUTBUFFER:

> Returns the current output buffer in hexadecimal and ASCII format for the specified session_id.

DBCC OUTPUTBUFFER (session id)

PROCCACHE:

> Displays information in a table format about the procedure cache.

DBCC PROCCACHE

SHOW_STATISTICS:

> Displays the current distribution statistics for the specified target on the specified table.

DBCC SHOW_STATISTICS (table or index view name’, target)

SHOWCONTIG:

> Displays fragmentation information for the data and indexes of the specified table or view.

DBCC SHOWCONTIG (‘table name’)

SQLPERF:

> Provides the transaction log space usage statistics for all databases. It can also be used to reset wait and latch statistics.

DBCC SQLPERF (LOGSPACE)

TRACESTATUS:

> Display the status of trace flags.

DBCC TRACESTATUS (Trace number)

USEROPTIONS:

> Returns the SET options active (set) for the current connection.

DBCC USEROPTIONS

VALIDATION COMMANDS:

> Performs validation operations on a database, table, index, catalog, filegroup, or allocation of database pages.

CHECKALLOC:

> Checks the consistency of disk space allocation structures for a specified database.

DBCC CHECKALLOC (‘Database name’)

CHECKCATALOG:

> Checks for catalog consistency within the specified database.

DBCC CHECKCATALOG (‘Database name’)

CHECKCONSTRAINTS:

> Checks the integrity of a specified constraint or all constraints on a specified table in the current database.

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

CHECKDB:

> Checks the logical and physical integrity of all the objects in the specified database.

DBCC CHECKDB (‘Database name)

CHECKFILEGROUP:

> Checks the allocation and structural integrity of all tables and indexed views in the specified filegroup of the current database.