SQL Server – Some commonly used DMV/DMFs by most of the DBAs

There are more than 150 DMV/DMFs spread across 20 some categories in 2012, and no one remember all these by heart.  Below are some commonly used DMV/DMFs by most the DBAs.

DMV /DMFs

— Execution related DMV and DMF

— Current Connections (User Processes only)
SELECT * FROM sys.dm_exec_connections

— Active Sessions
SELECT * FROM sys.dm_exec_sessions

— Currently executing requests
SELECT * FROM sys.dm_exec_requests

— Very Important: Query Statistics
SELECT * FROM sys.dm_exec_query_stats

— Cached Plans
SELECT * FROM sys.dm_exec_cached_plans

— Very Important: Query Optimizer details
SELECT * FROM sys.dm_exec_query_optimizer_info

— Very Important: Query Plan, This is DMF
SELECT TOP 1 * FROM sys.dm_exec_query_stats T1
CROSS APPLY sys.dm_exec_query_plan (T1.plan_handle )

— Very Important: SQL Statement, this is DMF
SELECT TOP 1 * FROM sys.dm_exec_query_stats T1
CROSS APPLY sys.dm_exec_SQL_text (T1.SQL_handle )

— Index related Dynamic Management Objects
— Index Usability
SELECT * FROM sys.dm_db_index_usage_stats

— Missing Indexes
SELECT * FROM sys.dm_db_missing_index_details

— Physical Stats, this is DMF, requires DBID, ObjectID, IndexID, PartitionID and Mode
SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID(‘TESTDB‘), OBJECT_ID(‘EMPLOYEE‘), NULL, NULL , NULL) WHERE avg_fragmentation_in_percent >30

— SQL Server Operating system related Dynamic Management Objects

— Wait Stats
SELECT * FROM sys.dm_os_wait_stats

— Scheduler
SELECT * FROM sys.dm_os_schedulers

— Waiting/ Suspended Tasks
SELECT * FROM sys.dm_os_waiting_tasks WHERE Session_ID > 50

— Performance Counters
SELECT * FROM sys.dm_os_performance_counters

— Cluster Nodes Details
SELECT * FROM sys.dm_os_cluster_nodes

 

 

SQL Server – How to display line number in Query Editor- SSMS

Displaying line numbers in Query Editor is very useful, when you are working with large queries/module. As it is really annoying to find line number when a syntax error occurs [line numbers in Query Editor is not enabled].

How to enable or disable the display of line numbers in SSMS Query Editor

1. Open SQL Server Management Studio. Go to Tools > Options

1

2. In the Options dialog box on the left side panel expand the Text Editor option and expand Transact-SQL. Next select General as shown in the snippet below.

2.1

3. Tick the Line numbers checkbox and click OK.

2

 

4. Now, when a query window is opened Line Numbers will be displayed as below.

3

 

Hope this is helpful !!!!

SQL Server- Move TempDB to new location

Why and How to move TempDB to new location

Why:

There are mainly two reasons why you would like to move TempDB to a new location:

1. TempDB has grown big and existing drive doesn’t have enough disk space.

2. Moving TempDB to a new disk which is faster to improve the performance.

 

How:  Follow the steps to move TempDB to new location

First of all, check out the current location of TempDB files by executing the below query.

use tempdb

go

exec sp_helpfile

 

1

 

This will provide you the current location of tempdb.

now to move the tempdb to new location, execute the below command

 
USE master
GO
ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = ‘C:\Tempdb\tempdb.mdf‘)
GO
ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = ‘C:\Tempdb\templog.ldf‘)
GO

 

2

 

I am moving tempdb from (C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\) to (C:\Tempdb\) as i have only one drive in my machine. You can change it accordingly.

To make changes come in effect, restart SQL Service.

 

3

After restarting the sql services, check the location of tempdb now by running the below query.

use tempdb

go

exec sp_helpfile

4

 

 

Tempdb has been moved to a new location.

 

Hope this was helpful !!!!!

Rebuild or Reorganize

In OLTP database applications, Indexes are subjected to data modification, which makes indexes fragmented (gaps in data pages).

Gaps in data pages can reduce the number of rows that can be stored in SQL Server’s data cache, leading to increased disk I/O. Logical fragmentation can cause extra disk activity, as the disk subsystem has to work harder to find the data on disk and move it to the data cache. The only way to remove wasted space and logical fragmentation is to rebuild or reorganize the indexes on a regular basis.

 

Rebuild: physically drop and rebuilds indexes from scratch. This removes index fragmentation and updates statistics at the same time. If you use this option, you do not want to run the Reorganize Index or the Update Statistics task, as doing so would be redundant.

Command: ALTER INDEX REBUILD WITH (ONLINE = ON)*

 

Reorganize Index is more of a gentle reshuffling of the leaf-level pages of an index, such that the physical ordering matches the logical ordering, and wasted space is minimized.

Command: ALTER INDEX REORGANIZE 

 

Difference between Rebuild and Reorganize:

 

Reorganize Index Rebuild Index
Performs a less thorough index defragmentation than Rebuild Index Virtually all wasted free space and logical fragmentation is removed.
If an index does not have any fragmentation, then it is not reorganized, saving resources. All indexes are rebuilt from scratch, whether they need it or not.
Does not require long blocking locks. Requires potentially long blocking locks that prevent users from accessing the indexes being rebuilt.
An online task that allows users to access the database during the task. A task that should be performed offline, though with the Enterprise Edition of SQL Server, you can use the online version of rebuilding an index.
Generally takes longer to run than the Rebuild Index Task. Generally runs faster than the Reorganize Index Task.
Uses less disk space than the Rebuild Index Task. Uses more disk space than the Reorganize Index Task.
Uses less space in the transaction log than the Rebuild Index Task. More space is required in the transaction log than the Reorganize Index task.
Index and column statistics must be updated separately. This adds to the administration hassle. Index and column statistics are automatically updated as part of this step, using the FULLSCAN option.

 

Vs

 

Tracking Index Fragmentation

By using the system function sys.dm_db_index_physical_stats, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases.

 

Sample Query to check Fragmentation in a Database:

1

Sample Output:

2

 

Recommended Operation based on Fragmentation Percent:

avg_fragmentation_in_percent value Corrective statement
> 5% and < = 30% ALTER INDEX REORGANIZE
> 30% ALTER INDEX REBUILD WITH (ONLINE = ON)*

* Rebuilding an index can be executed online or offline. Reorganizing an index is always executed online.

If the fragmenation % is between 5% and 30%, then Index Reorganize is sufficient. However, If it exceeds 30%, then index Rebuild is recommended.

 

Index Maintenance frequency and Time

It is difficult to give general advise on how frequently index maintenance (Rebuild/Reorganise/Update Statistics) should be performed, as it depends on the nature of data, indexes and query which used them. However few considerations/recommendations are as below.

 

* If your indexes fragment rapidly and you have maintenance window every night, then Rebuild indexes every night. There is no harm in doing so.

* If it can’t be run nightly, then at a minimum, perform this activity weekly.

* If you don’t have maintenance window long enough weekly, then consider alternatives :

— Rebuild Indexes Online (only available in Enterprise Edition)

— Reorganize indexes and then update indexes

 

 

 –Hope this was helpful!!!!!!

 

Installing SQL Server 2008 R2 Cluster

Microsoft Cluster

A Microsoft cluster is a high-availability system designed to keep the database up as much as possible. It does this by resuming processing on the secondary server in the event of a failure on the principal server. Since the data resides on a shared disk, a Microsoft cluster will not survive a data corruption.  This technology is high availability, not disaster recovery. All data resides in the same data center and on the same disk. A cluster can be used with other technologies, such as database mirroring.

Below is the SQL Cluster Architechture:

Architechture

 

 

 

In order to Install SQL Server Cluster, we need to have Windows cluster ready as a base with some other pre-requisites as well. [ Installing Windows Cluster is out of scope of this article.]

1- Windows Cluster

2- DTC

3- SQL

Below IPs needs to be reserved well in advance to save yourself some time. IP Addresses and Hostnames can be of your choice.

Prerequisites

 

We then need to setup 2 Network Interfaces to be used in production and the other as a heartbeat.

Once the setup is ready to install SQL Server Cluster. Please follow the below steps to configure 2 Node Active/Passive Cluster[SQL Server 2008 R2].

 

Installation:

Steps to be performed on Node 1.

  1. Run setup.exe from the installation media to launch SQL Server Installation Center. Click on the Installationlink on the left-hand side1
  2. Click the New SQL Server failover cluster installation link. This will run the SQL Server 2008 R2 Setup wizard2

 

3. In the Setup Support Rules dialog box, validate that the checks return successful results and click Next.

3

4. In the Product Key dialog box, enter the product key and click Next.

4

5. In the License Terms dialog box, click the I accept the license terms check box and click Next.

5

6. In the Setup Support Rules dialog box, click Install. Validate that the checks return successful results. If the checks returned a few warnings, make sure you fix them before proceeding with the installation.

8

7. Select the features to install and click Next.

9

 

8. Configure the Network Name ( this is going to be the DB cluster name ) and instances

10

 

9. In the Disk Space Requirements dialog box, check that you have enough space on your local disks to install the SQL Server binaries and click Next.

11

10. Choose your Cluster Resource Group ( or leave to default )

12

 

11. In the Cluster Disk Selection dialog box, select the available disk groups that are on the cluster for SQL Server to use.

13

 

12. In the Cluster Network Configuration dialog box, enter the IP address and subnet mask.

14

 

13. In the Cluster Security Policy dialog box, accept the default value of Use service SIDs (recommended).

15

14. In the Server Configuration dialog box, enter the credentials that you will use for your SQL Server service accounts in the Service Accounts tab. Configure the Service accounts Users and Password as per best practices.

16

 

15.  Select the appropriate Authentication Mode. If you want to add the currently logged on user to be a part of the SQL Server administrators group, click the Add Current User button.

17

16. On the Data Directories tab, enter the path where your system and user database files will be created.

18

17. Install, But do not configure is the only option available as we haven’t installed the pre-requisites for Reporting Service.

19

 

18. Make Sure everything passes on the Cluster installation Rules

21

 

19. Review your install Summary and Click Install

22

 

20. Now wait for the installation to finish

23

 

21. In the Complete dialog box, click Close. Cluster Installation on Node 1 is finished.

24

 

 

Now, start installation on Node 2.

1. Run setup.exe and Click the Add node to a SQL Server failover cluster link

25

2.  Install Support rules and Make Sure all passes.  Few steps are similar to Node 1 Installation

26

27

28

 

30

 

 

31

 

3. You will see that setup has detected the current Single node cluster from above, click Next

 

32

 

4. Enter the same credentials and click Next.

 

33

 

5. Make sure everything is green.

 

 

35

 

6. In the Ready to Update dialog box, verify that all configurations are correct and click Install.

 

36

 

7. In the Complete dialog box, click Close. This concludes installing the passive node of a SQL Server 2008 R2 Failover Cluster

 

39

 

Verifying The Cluster

 

FailoverCluster

 

 

Hope this was helpful.