Troubleshooting Database Slowness and Blocking with sp_who, sp_who2 and sp_WhoIsActive

When users are reporting slowness in applications then as a database administrator what is your first step, you will try to find some blocking from current active processes. Most of the DBA’s first step is to fire the query sp_who or sp_who2 and check the details.

Today we will discuss how we can work with sp_who, sp_who2 and overview on sp_WhoIsActive as well. SPIDs from 1 to 50 are reserved for system processes and normally do not create issues and above SPID 50 are user processes which normally creates problem and you have to troubleshoot and resolve these issues.

sp_who

You can use sp_who system stored procedure whenever you want to check the details for blocking, active users, sessions or processes. This stored procedure is officially provided by Microsoft and very useful for day to day activities. If you add a parameter ‘Active’ with sp_who you will get current active users, sessions, processes and other details as shown above.

You can use and execute sp_who as:

sp_who

sp_who ‘active’

sp_who ‘loginname’     –put your login name from sessions

sp_who 57                –where 57 is spid

sp_who ‘active’

As stated above you will get current active users, sessions, processes details with this procedures.

sp_who2

Though this system stored procedure is undocumented by Microsoft but it is very useful and widely used by DBAs and database developers across the globe. sp_who2 helps in finding all sessions which are available and established in database instance. It helps in finding blocking, high CPU, high disk IO as shown in figure above.

You can find the blocking SPID in BlkBy column and kill the same after analysing it with DBCC inputbuffer(spid).

You can use and execute sp_who2 as:

sp_who2

sp_who2 ‘active’

sp_who2 ‘loginname’     –put your login name from sessions

sp_who2 57          –where 57 is spid

sp_who2 ‘active’

sp_who2 ‘active’ is an extension to sp_who and similar to sp_who2 in output, it is very helpful in analysing current active sessions details and has more columns and useful details as compared to sp_who. It also helps in analysing parallel processes running with same SPIDs.

sp_WhoIsActive

sp_WhoIsActive is an external stored procedure provided by Adam Machanic from blog and is very useful and more advanced to sp_who and sp_who2. It gives you details about total duration for blocking, sql query involved in blocked by and blocking, query execution plan, xml file, blocking session id and wait time and wait type etc.

Though it takes much time for execution as compared to sp_who and sp_who2 but results provided by it is quite interesting and saves your lots of time for troubleshooting.

By adding extra parameters you will find execution plan details and xml details as given below.

@get_additional_info = 1

@get_plans =1

@get_locks = 1

You can use this procedure as:

sp_WhoIActive

sp_WhoIActive @get_additional_info = 1, @get_plans =1             –To get extra columns

Once you identified your blocking spid, high cpu time or high disk IO then you can kill the query with KILL command as:

Activity Monitor in SQL Server 2012

Activity Monitor is an inbuilt monitoring tool which comes with SQL Server Management Studio. This monitoring tool provides real-time information about processes, resource waits, data file I/O and recent expensive queries etc. If you are a DBA or have to monitor database instance but do not have any third party tool, in that case it is very useful inbuilt tool which can be helpful to monitor the health of system and instance. Kindly note that to access activity monitor you need VIEW SERVER STATE permission.

In earlier version of SQL Server like SQL Server 2005 and SQL Server 2000, this monitoring tool was available at different location in instance and in SQL Server 2012, you can find it by right click on server name or instance name and select activity monitor as shown below.

Once activity monitor window will open, you will find different categories in it like.

1. Overview
2. Processes
3. Resource Waits
4. Data File I/O
5. Recent Expensive Queries

1. Overview
In overview section, we can monitor graphical information about processor time, waiting tasks, database I/O details, batch request per second.

2. Processes
In processes section, you will find information about processes, login details, database, application details, currently running SQL commands, blocking details etc. You can right click on process and kill the blocking queries or you can trace the processes with SQL Server Profiler directly.

3. Resource Waits
Resource wait section provides detail about processes which are waiting for resources, and different wait time information.

4. Data File I/O
Data file I/O provides detail about database and physical database files currently in use and read write information.

5. Recent Expensive Queries
Most expensive recent queries detail and resources utilized by those queries are available in recent expensive queries section. You can find the execution plan and view the query by right click on the available query.

CLICK HERE to watch live practical.

Reference: Manzoor Siddiqui [www.SQLServerLog.com]

How to Set Backup Compression in SQL Server

Microsoft has introduced backup compression feature from SQL Server 2008. This feature is very useful to save the time, reduces device I/O and  storage cost and it is also a good backup strategy option. You can configure backup compression at instance level so that if you perform any database backup, that database will be compressed by default or you can also set backup compression at individual database level. Here I am listing both the options.

Backup Compression at Database Level

You can set backup compression by T-SQL or by SQL Server Management Studio (SSMS).

Set Backup Compression with T-SQL

BACKUP DATABASE [BooksInfo] TO DISK = 'C:\Backup\BooksInfo.bak' 
WITH COMPRESSION
GO

This backup will compress your data upto 60%-80% depending upon type of data stored in database, like if you have mostly text data then you will get more compressed backup file or if you have images or videos then you will get less compressed backup file.

Set Backup Compression with SSMS

You can also set backup compression by SQL Server Management Studio from below path.

Right click on Database –> go to Tasks –> Back Up… –> backup database window will pop up –> go to Options page –> In compression section set backup compression to Compress backup –> click OK.

Once you have set the backup compression, now you can take the database backups in compressed file format.

Backup Compression at Instance Level

You also have option to set backup compression at instance level so that whenever you will backup any database it will be automatically in the form of compression for all your databases. You can set backup compression at instance level by T-SQL as follows.

EXEC sys.sp_configure N'backup compression default', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

It will enable Compress Backup option at instance level. If you want to set it by SSMS then you can follow below path and set the same.

Right click on database instance –> go to Properties –> it will pop-up Server Properties –> go toDatabase Settings page –> tick mark Compress backup option –> click OK. (refer below figure)

Now, if you will take any database backup, it will be in compressed format by default.

But if you want to take database backup in normal format i.e. without compression and your instance level setting is enabled, then you can use WITH NO_COMPRESSION as shown below.

SQL Server: How to create a linked server pointing to a server different from its name

Background 

The purpose of this article is to provide a step-by-step method of creating a linked server that point to a server different from its name. Configuring a linked server in this manner is very handy when we need to execute SQL scripts containing linked server names in our different environments. 

Suppose there are servers ‘A’ and ‘B’. You want to make server ‘A’ as linked server on your machine. Once completed the configuration of linked server for server ‘A’. It should be points to itself (Server ‘A’) only. But instead of pointing the same server (server ‘A’), you can also point it to some other server (server ‘B’). If you point the Linked server ‘A’ to server B‘, then Linked server ‘A’ will extract all the data from server ‘B’.

Where to use?

This technique is very useful while working in different environments (dev, test, pre-production) with scripts having linked server names added at different places.

Let’s assume that our scripts have linked server name referring to a production server mentioned at many different places. We may not be able to test the script with respect to the production server. Also every time changing the code (e.g.renaming the linked server for testing) is not good. In this scenario, we can use our technique to create a linked server (may be our production server) that points to our development server but with a name same as the production server. Thus, we will be able to use the same script in different environments without changing the linked server names.

What is a Linked Server?                         

Linked Server is a mechanism in SQL Server by which we can add other SQL Server to a Group on a different SQL Server instance and query both the SQL Server DBs using T-SQL Statements. A linked server definition specifies an OLE DB provider and an OLE DB data source. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data.

Linked Server Configuration

Approach 1 – Using Script to create Linked Server

/****** Object:  LinkedServer ******/

--New Linked Server with Windows Authentication

EXEC master.dbo.sp_addlinkedserver

--Provide a Name for Linked server by which we want to create it       
@server = N'BIMSQL01', 
@srvproduct=N'SQL Native Client', 
@provider=N'SQLNCLI',
/*Name of the server that we want to point. Please note that the name of the linked server and the actual server it points to are different.*/
@datasrc=N'BIMSQL02', 

@provstr=N'Provider=SQLOLEDB.1; /*complete connection string for a server that we want to point*/

  Integrated Security=SSPI;

  Persist Security Info=False;
  Initial Catalog=ITSDB;
  Data Source=BIMSQL02'                          

--If you have SQL login, provide credentials else make it as NULL.

EXEC master.dbo.sp_addlinkedsrvlogin

       @rmtsrvname=N'BIMSQL01',   --Name for Linked server

       @useself=N'False',

       @locallogin=NULL,

       @rmtuser=N'sa',

       @rmtpassword='Q!W@E#R$'

 GO

Approach 2 – Using Linked Server Wizard

Follow the steps below for configuring the linked server that points to a server different from its name:

1)      For creating the linked server, go to Start, All Programs, Microsoft SQL Server 2008, SQL Server Management studio. It will open connection window. Check the server name & connect it.

2)      In Management studio, go to Server Objects, Linked Servers. Right click on the Linked server folder and click onNew Linked Server…  

3)      It will open the Linked server configuration window.

4)      For configuring the linked server, fill the  following required  information about the linked server:

Ø  Linked server: Provide the name with which you are going to refer the linked server. For Ex: BIMSQL01

Ø  Server Type: Other data source

Ø  Provider: SQL Native Client

Ø  Product: SQL Native Client

Ø  Data Source: The data source is usually the name of the database server. So, here you need to give the name of the actual server (BIMSQL02) on which you want to query data remotely. Please note that the name of the linked server and the actual server it points to are different.

Ø  Provider string:  Here you need to provide the complete connection string for the server (BIMSQL02). The format of connection string is shown below:

Provider=SQLOLEDB.1; Integrated Security=SSPI; Persist Security Info=False; Initial Catalog=ITSDB; Data Source=BIMSQL02

Ø  Catalog:  It is optional field. You can mention the database name here.

Ø  Go to Security tab

Here you need to choose the security context in order to make connection with the linked server. For this document, we are using windows authentication. For windows authentication, you can choose connection will “Be made using the logins current security context”.

 For SQL Server authentication, we need to choose “Be made using this security context” and provide the required SQL login and password.

Ø  After giving the complete information, click Ok. It will create the Linked server named ‘BIMSQL01’ that will actually extract the data from BIMSQL02 server.

Ø  For checking the Linked server, go to Server objects, Linked server, Expand the linked server folder. It enlists the entire linked servers. Verify that ‘BIMSQL01’ should be in the list.

Ø  For testing the linked server connection, right click on the linked server (BIMSQL01) and click Test Connection.

Ø  Test the data retrieval using T-SQL:

 SELECT * FROM BIMSQL01.ITSDB.dbo.Region

 It will extract the data from BIMSQL02 server instead of BIMSQL01 server.

Conclusion 

By using the above steps, we can create a linked server that point to a server different from its name.

----------------------------------End of Document----------------------------------------

Understanding about LINKED SERVER In SQL

Linked Server in nothing but combining two different Server database together and perform a single query or joining table objects from different database of different server.

For Example:

You have Server-A with Database-A and Server-B with Database-B in different geographical locations. Now you want to make a SQL statement by JOINING Table-A from Server-A / Database-A and Table-B from Server-B/Database-B.  to do this you need to configure linked server configurations.

Configuring Linked Server.

Syntax

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]

     [ , [ @provider= ] 'provider_name' ]

     [ , [ @datasrc= ] 'data_source' ]

     [ , [ @location= ] 'location' ]

     [ , [ @provstr= ] 'provider_string' ]

     [ , [ @catalog= ] 'catalog' ]

Suppose You are currently logged in at SERVER-A

USE master

GO

EXEC sp_addlinkedserver  

   @server=N'S1_instance1', -- Linked Server Name

   @srvproduct=N'',

   @provider=N'SQLNCLI',    -- Provider Name

   @datasrc=N'Server-B';    -- SQL Server Instance Name

Provider Details:

SQL Server	Microsoft SQL Server Native Client OLE DB Provider	SQLNCLI
Oracle	Microsoft OLE DB Provider for Oracle	MSDAORA

It can connect any server supporting Microsoft OLEDB Provider.

Linked Server Login

 sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname' 

     [ , [ @useself = ] 'TRUE' | 'FALSE' | NULL ] 

     [ , [ @locallogin = ] 'locallogin' ] 

     [ , [ @rmtuser = ] 'rmtuser' ] 

     [ , [ @rmtpassword = ] 'rmtpassword' ] 

Creates or updates a mapping between a login on the local instance of SQL Server and a security account on a remote server

EXEC  sp_addlinkedsrvlogin

       @rmtsrvname = 'Server-B',

       @useself = 'TRUE',

       @locallogin = 'Domain\Mary',

       @rmtuser =  'mysqllog',

       @rmtpassword = 'rmtpassword' 

  

To Drop linked Server Login

sp_droplinkedsrvlogin [ @rmtsrvname= ] 'rmtsrvname' , 

                      [ @locallogin= ] 'locallogin'

 Execute SQL

SELECT a.Roll, a.StudentName

  FROM Server-B.Dtabase-B.dbo.Table-B

Database Migration from SQL Server 2000 to 2008/2008 R2/2012

1.    Background:

The purpose of this article is to lay out the structure for database migration from SQL 2000/2005 to SQL 2008/R2/2012. This article describes migration process in a simpler way for database only which includes pre and post migration steps also.

2.    What is Upgrade?

Upgrade is an automated process in which the upgrade tool, called Setup, moves an old instance of SQL Server to a new instance while maintaining the data and metadata of the old instance. At the end of the upgrade, the old instance is no longer available and the new instance has the same name as the old instance.

3.    What is Migration?

Migration is a manual process in which the DBA installs a new instance of SQL Server and copies the metadata and data from an old instance of SQL Server to the new instance. Migration provides access to two instances of the system, letting you verify and compare the two systems. During migration, both the old and new systems remain online until migration to the new instance is complete. At the end of the migration, all applications are directed to access the new instance and the old instance is manually removed.

4.    High Level Architecture:

5.    Before Migration

5.1 What to Migrate

This step includes surveying environment to determine SQL Server components that will be migrating and then the best technical option(s) can be determined. Here we are planning to migrate SQL Server database.

  5.2 Upgrade prerequisites

1) Verify that SQL Server 2008/R2/2012 environment meets hardware and software requirements. 

2)  Ensure that SQL Server 2008/R2/2012 environment is capable of supporting future needs from a user and transaction perspective. (using SQL Server 2008 Upgrade Advisor to determine potential issues)

3)   Set up an environment to test the migration process as well as front-end applications and dependent (upstream and downstream) systems' functionality.

   5.3 SQL Server 2012 Upgrade Advisor

Perhaps the most important tool of the several tools typically used for upgrade planning is Upgrade Advisor. Upgrade Advisor smoothes the transition to SQL Server 2012 by predicting issues in your legacy instances of SQL Server 2000 and SQL Server 2005. It analyzes objects and code within legacy instances and produces reports detailing upgrade issues, if there are any, organized by SQL Server component. The resulting reports show detected issues and provide guidance about how to fix the issues or work around them. The reports are stored on disk, and you can review them by using Upgrade Advisor or export them to Microsoft Excel for further analysis.

In addition to analyzing data and database objects, Upgrade Advisor can analyze Transact-SQL scripts and SQL Server Profiler/SQL Trace traces. Upgrade Advisor examines SQL code for syntax that is no longer valid in SQL Server 2008/R2/2012. It generates a report listing the code in question, together with links to where you can find more information to help resolve the questionable code.

5.4 Requirements for running Upgrade Advisor 2012 are as follows:

1)    Windows Vista SP1, or SP2, Windows 7 and Windows Server 2008 R2.

2)  Windows Installer beginning with version 4.5. You can install Windows Installer from the Windows Installer Web site.

3) Microsoft .NET Framework 4. .NET Framework 4 is available on the SQL Server 2012 product media, and from the .NET Framework 4 download page.

4)  SQL Server 2000 Decision Support Objects (DSO) if analyzing SSAS (you can use SQL Server 2000 Setup to install DSO)

5)   SQL Server 2000 client components if analyzing DTS (you can use SQL Server 2000 Setup to install the SQL Server 2000 client components)

6)  Pentium III-compatible processor or a later version, with a processor speed of at least 500 MHz

7)  Available Disk Space should be 15MB.

Whether you choose an in-place upgrade or a side-by-side upgrade, run Upgrade Advisor on your legacy systems. You can run Upgrade Advisor from a local or remote server, and you can execute it from the Command Prompt window by using a configuration file name as an input parameter.

Note: You can run the SQL Server 2008/2012 Upgrade Advisor only against instances of SQL Server 2000 and SQL Server 2005. You cannot run it against instances of SQL Server 2008/2012 or on SQL Server 7.0.

Upgrade Advisor is a separate download. The most recent downloadable version is available as part of the below link:

http://www.microsoft.com/en-us/download/details.aspx?id=29065

5.  Key SQL Server 2012 migration considerations

Here are additional technical considerations that are keys to the migration:

1) Execute DBCC CHECKDB to validate that the previous environment (SQL Server 2000 or 2005) is free of corruption prior to the upgrade. 

2) Issue final backups and ensure a rollback plan is in place to continue business operations in case an unforeseen problem is encountered. 

3) Develop enterprise standards for SQL Server 2005 configurations, operations (security, maintenance, etc.) and development. 

4)   Archive unneeded data and perform data cleansing for the migrated data. 

5) Ensure all code is operating accurately prior to migration so that migration is the blame for broken code. 

6) Perform database maintenance such as UPDATE STATISTICS, DBCC CHECKDB and index rebuilds post upgrade. 

7) Validate that the SQL Server 2000/2005 configurations and database compatibility modes are accurate post upgrade.

6. What are the reasons for Migration to SQL Server 2008/R2/2012?

There are several reasons to migrate to SQL Server 2008/R2/2012 of which are involves server level improvements that are not performance related. SQL Server 2008/2012 offers certain reliability and availability improvements, such as the following:

1)     SQL Server 2008/2012's database mirroring is more robust and easier to configure and manage. 

2)    Policy-based management can be a tremendous feature for improving the consistency of SQL Server's configuration and ongoing management, especially with compliance or other restrictive requirements. 

3)     A new data auditing feature that provides more granular auditing of SQL Server activity. 

4)    Transparent Data Encryption (TDE) lets you turn on encryption without requiring massive application-level changes and can help meet specific security and compliance requirements. 

5)   The data collector helps make performance management and tuning easier and more centralized. 

6)   The Resource Governor gives administrators central control over CPU and memory allocation, which is useful for servers that host multiple databases and experience significant resource contention between those applications. 

7)    If you manage multiple servers, Server Group management enables you to run T-SQL queries against multiple servers simultaneously from a central management server. This makes server farm management easier and more consistent.

8)      To avail all the new features of the SQL Server 2008/2012.

7. Different ways of Database Migration (SQL Server 2000/2005 to SQL Server 2012)

 There are several ways to migrate the databases from SQL Server 2000/2005 to SQL Server 2008/2012:

        i. Copy database Wizard:  The Copy Database Wizard lets you move or copy databases and their objects easily from one server to another or one instance to another instance, with no server downtime. Using this wizard, you can do the following:

1)     Pick a source and destination server.

2)     Select databases to move or copy.

3)     Specify the file location for the databases.

4)     Create logins on the destination server.

5)     Copy additional supporting objects, jobs, user-defined stored procedures, and error messages.

6)     Schedule when to move or copy the databases.

In addition to copying databases, you can copy associated metadata, for example, logins and objects from the masterdatabase that are required by a copied database

       ii. Database Backup and Restore: You can take the backup the database from the previous version and restore it into the new version of SQL Server (SQL Server 2008).

            iii. Detaching and Attaching: To move a database using detach and attach, you should make the following steps:

1)    Detach the database.

2)    Move the database file(s) to the desired location on another server or disk.

3)    Attach the database specifying the new location of the moved file(s):  After detaching, the database will be removed from SQL Server but will be intact within the data and transaction log files that compose the database. You can use these data and transaction log files to attach the database to any instance of SQL Server, including the server from which the database was detached. After attaching, the database will be available in exactly the same state it was in when it was detached.

6. Post Migration testing:

After migration, we need to compare the previous version’s database to migrate database.

Followings are the things that we need to check:

      1)    Check the total no. of tables, stored procedures, views and their names in the migrated database. It should be same as previous versioned database.

       2)     Check the row counts of the tables