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.


— 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


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.


3. Tick the Line numbers checkbox and click OK.



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



Hope this is helpful !!!!

SQL Server- Move TempDB to new location

Why and How to move TempDB to new location


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


exec sp_helpfile




This will provide you the current location of tempdb.

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

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




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.



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

use tempdb


exec sp_helpfile




Tempdb has been moved to a new location.


Hope this was helpful !!!!!

SQL Server – Uninstall /Remove SQL Server 2012 Failover Cluster Instance

Procedure to uninstall a SQL Server failover clustered instance

To update or remove a SQL Server failover cluster, you must be a local administrator with permission to login as a service on all nodes of the failover cluster.


To remove a node from an existing SQL Server failover cluster

  1. Insert the SQL Server installation media. From the root folder, double-click setup.exe. To install from a network share, navigate to the root folder on the share, and then double-click Setup.exe.
  2. The Installation Wizard launches the SQL Server Installation Center. To remove a node to an existing failover cluster instance, click Maintenance in the left-hand pane, and then select Remove node from a SQL Server failover cluster.
  3. The System Configuration Checker will run a discovery operation on your computer. To continue, Click OK. .
  4. After you click install on the Setup Support Files page, the System Configuration Checker verifies the system state of your computer before Setup continues. After the check is complete, click Next to continue.
  5. On the Cluster Node Configuration page, use the drop-down box to specify the name of the SQL Server failover cluster instance to be modified during this Setup operation. The node to be removed is listed in the Name of this node field.
  6. The Ready to Remove Node page displays a tree view of options that were specified during Setup. To continue, click Remove.
  7. During the remove operation, the Remove Node Progress page provides status.
  8. The Complete page provides a link to the summary log file for the remove node operation and other important notes. To complete the SQL Server remove node, click Close.



1 2 3 4 5 6 7



Repeat above steps on all nodes used for the SQL Server clustered instance. Make sure you uninstall the active node last.

Note:    Remove Passive Nodes First and Active node Last.

SQL Server – Error while enabling Windows feature: NetFx3


I was installing SQL Server 2012 on Windows Server 2012, and the installation failed with the following error.


Error while enabling Windows feature: NetFx3, Error Code: -2146498298, Please try enabling
Windows feature: NetFx3 from Windows management tools and then run setup again. For more
information on how to enable Windows features, see http://go.microsoft.com/fwlink/?linkid=227143




Since I didn’t enable .NET Framework 3.5 before installing SQL Server 2012 it failed.



Enable .NET Framework 3.5 and then install SQL Server 2012.

Below is the procedure to enable .NET Framework 3.5 in Windows Server 2012


Using GUI

1. Go to Server Manager -> Manage -> Add Roles and Features


2. Click Next



3. Click Next button, the wizard will shows the Installation Type section where we have to select the Role-based or feature-based installation option that it has used to configure this server by adding roles, role services and features.





4. Click Next button, it will show the Server Selection section. Click Select a server from the server pool and over the grid, select the server




5. Click Next



6. Select the .NET Framework 3.5 Feature Checkbox and click Next.



7. Warning message informs us that for one or more installation selections are missing source files on the destination server, so we have to specify an alternative source path. Click the Specify an alternative source patch




8. Provide the required source file path for the .Net Framework 3.5 which is in Windows Server 2012 ISO file. Extract or Mount this. and click OK



9. TheResults section will show the installation status. Installtion will complete.




Once .net framework 3.5 is enabled. We can install SQL Server 2012 on Windows Server 2012.



Using Command Line

dism /online /enable-feature /featurename:NetFx3 /source:d:\sources\sxs





Hope this was helpful !!!


SQL Server – Adding Node to SQL Server 2012 cluster

Adding Node to SQL Server 2012 cluster

This is part 2 of earlier post. Installing SQL Server 2012 Cluster




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


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



3. Make sure nothing has failed in Setup Support Rules. Then Click Next.



4. Enter the product Key and click Next



5. Accept License Terms and click Next.




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





7. Enter Service account credentials and click NEXT.



8. Click Next




9. Make sure nothing had failed in Add Node Rules check. Then click Next.




10. click Install.




12 13

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








SQL Server – Installing SQL Server 2012 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.





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




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 2012].


Steps to be performed on Node 1.

1. With the SQL Server installation binaries available, right click “Setup” and run as administrator:





2.  Click “Installation” in the left menu, then select “New SQL Server Failover Cluster Installation”




3. A check will run in order to find possible constraints when installing the SQL Server Setup support files. At the end of the check, click “OK”




4. Provide product key for your media. Then Next



5. Accept License Agreement (and the other check if you would like). Then Next


6.  Wait for Setup Support Rules check. Once done make sure no failed task. Evaluate warnings for your environment . Then click Next




7. click SQL Server Feature Installation and click Next.



8. Select Features to install and click NEXT. I have selected all Features.


9. Make sure feature Rule check complete with no Failed status. Then Next





10. Provide Network and Instance names. Then Next





11. Click Next



12. Click Next




13. Select Disk needed for the resource group . Then Click Next



14.  Enable IP4 and provide IP address. Then Next




15. Provide Service Accounts (and Collation if different than default – SQL_Latin1_General_CP1_Cl_AS). Then Next

16 17

16. Now we need to choose the authentication mode of our instance.The options are either “Windows Authentication”, which will take benefit from domain and local server logins, or “Mixed Mode”, which accepts Domain/Windows logins as well as logins created and managed by SQL Server.If you pick “Mixed Mode” a login called “sa”, member of the “Sysadmin” role will be enabled. For this reason we need to specify the password for this login.In the box bellow, we need to add all domain/Windows users that have access to the instance and will be part of the “Sysadmin” fixed server role. You need to use the three buttons to add/remove logins from this list:



17. Select data directories accordingly for Datafile, log file, tempdb files and backup files and click next.



18. If you have selected analysis features then on this screen add the administrator account and data directory for analysis service. Then click next







19. Select install only for reporting services and Click Next. You can configure reporting services later.





20. Check the error report and click NEXT.




21. Make sure no clusters rules failed. Then Next



22. Click Install.


25 26



23. Click Close to complete the installation.





Check the Failover Cluster Manger. And you would see SQL Service and other dependent resources running under it.





Adding Node to cluster will be covered in next post.



 Hope this was helpful !!!


Installing SQL Server 2008 R2 Cluster




SQL Server – How to configure MSDTC in Windows Server 2012 Cluster

Below are the steps to configure MSDTC in Windows Server 2012 Cluster:

1. Open Failover Cluster Manager and Right-click on Roles and pick the “Configure Role” option.


2. Choose the option “Distributed Transaction Coordinator (DTC)” from the list. Click  “Next”.



3. Provide Name and IP for new role selected (Distributed Transaction Coordinator)



4. Select the Disk to be used for MSDTC



5. Review the configuration and confirm by clicking next.



6. The installation will run and in the last step you will show summary report. Click “Finish”.



7. Now you will be able to see a DTC role in the Failover Cluster Manager.




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.



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.



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.




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:


Sample Output:



Recommended Operation based on Fragmentation Percent:

avg_fragmentation_in_percent value Corrective statement

* 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!!!!!!