Book Image

Microsoft SQL Server 2012 Performance Tuning Cookbook

Book Image

Microsoft SQL Server 2012 Performance Tuning Cookbook

Overview of this book

As a DBA you must have encountered a slow running application on SQL Server, but there are various factors that could be affecting the performance. If you find yourself in this situation, don't wait, pick up this book and start working towards improving performance of your SQL Server 2012. SQL Server 2012 Performance Tuning Cookbook is divided into three major parts -- Performance Monitoring, Performance Tuning, and Performance Management--that are mandatory to deal with performance in any capacity. SQL Server 2012 Performance Tuning Cookbook offers a great way to manage performance with effective, concise, and practical recipes. You will learn how to diagnose performance issues, fix them, and take precaution to avoid common mistakes. Each recipe given in this book is an individual task that will address different performance aspects to take your SQL Server's Performance to a higher level.The first part of this book covers Monitoring with SQL Server Profiler, DTA, System statistical function, SPs with DBCC commands, Resource Monitor & Reliability, and Performance Monitor and Execution Plan. The second part of the book offers Execution Plan, Dynamic Management Views, and Dynamic Management Functions, SQL Server Cache and Stored Procedure Recompilations, Indexes, Important ways to write effective TSQL, Statistics, Table and Index Partitioning, Advanced Query tuning with Query Hints and Plan Guide, Dealing with Locking, Blocking and Deadlocking and Configuring SQL Server for optimization to boost performance.The third and final part gives you knowledge of performance management with help of Policy Based Management and Management with Resource Governor.
Table of Contents (28 chapters)
Microsoft SQL Server 2012 Performance Tuning Cookbook
Credits
About the Authors
Acknowledgement
Acknowledgement
About the Reviewers
www.PacktPub.com
Preface
Index

Filtering events


Running a trace which is configured to collect large number of events is not best practice. While collecting trace data, SQL Trace itself can introduce overhead and affect the performance of SQL Server if trace is configured to collect too much trace information. This also depends on whether the trace is server-side trace or client-side trace. If the trace is client-side using profiler, then the performance overhead can be greater.

Also, if large number of trace data is captured, the size of the trace file immediately grows very big and it becomes a difficult job for us to look for the right data in the trace. Therefore, any unnecessary or irrelevant trace data should not be collected.

This is the reason why we should consider limiting the resulting trace data and capturing only the events which are of our interest. For this, we should identify what trace data we need to look at and based upon that we should identify the filters that are applied to our trace.

Note

Collecting large amount of trace data can affect the performance of SQL Server. So, before creating a trace, we should identify the type of analysis we want to perform on trace information. A single trace should not be created for multiple types of analysis. For each analysis type, a separate trace should be created until and unless different types of analysis explicitly need to be combined into single trace for performing correlative analysis. For example, rather than creating a single trace that collects both scan events and lock events for index scan analysis and object locking analysis respectively, we should consider creating two separate traces; one for collecting only scan events and another for collecting lock events only.

Getting ready

In this recipe, we will see how to capture only those trace events that occurred for a specific database and from a specific SQL Server login.

Let's assume that sample database AdventureWorks2012 is our production database on our production server, which is hosting other databases also. One of the database users James complains that he faces some problems while running queries against database AdventureWorks2012. So, we want to trace his session only for database AdventureWorks2012. Because there are also other databases hosted on the same production server and many users are accessing AdventureWorks2012 database, we need to filter trace events based on session login name and database name in order to avoid any unwanted trace data from being collected.

To emulate this case practically, we need the following as prerequisites:

  • An instance of SQL Server 2012 Developer or Enterprise Evaluation edition

  • An SQL Server Login account with sysadmin rights

  • The sample AdventureWorks2012 database on the instance of SQL Server. For more details on how to install AdventureWorks2012 database, please refer the Introduction section of this book.

  • Two SQL Server logins named James and Peter with some permission on AdventureWorks2012 database.

How to do it...

We will be performing three main actions in this example. These are as follows:

  • Creating the required logins and users in the AdventureWorks2012 database (James and Peter )

  • Creating a trace by applying filters on the DatabaseName and SessionLoginName data columns

  • Executing sample queries from two separate connections belonging to James and Peter respectively and observing the trace data

Because two SQL Server logins named James and Peter with permissions on AdventureWorks2012 database are required, create them by performing the following steps:

  1. Open SQL Server Management Studio.

  2. Connect to the instance of SQL Server with login account having sysadmin rights.

  3. Execute the following T-SQL script to create the logins and their corresponding users in the AdventureWorks2012 database for James and Peter:

    --Creating Login and User in
    --AdventureWorks2012 database for James
    USE [master]
    GO
    CREATE LOGIN [James] WITH PASSWORD=N'JamesPass123'
    ,DEFAULT_DATABASE=[AdventureWorks2012]
    ,CHECK_EXPIRATION=OFF
    ,CHECK_POLICY=OFF
    GO
    USE [AdventureWorks2012]
    GO
    CREATE USER [James] FOR LOGIN [James]
    GO
    ALTER ROLE [db_owner] ADD MEMBER [James]
    GO
    --Creating Login and User in AdventureWorks2012 database for Peter
    USE [master]
    GO
    CREATE LOGIN [Peter] WITH PASSWORD=N'PeterPass123'
    ,DEFAULT_DATABASE=[AdventureWorks2012]
    ,CHECK_EXPIRATION=OFF ,CHECK_POLICY=OFF
    GO
    USE [AdventureWorks2012]
    GO
    CREATE USER [Peter] FOR LOGIN [Peter]
    GO
    ALTER ROLE [db_owner] ADD MEMBER [Peter]
    GO
    

    Note

    Notice the new command syntax in this script introduced in SQL Server 2012 for adding members to a role.

Now, we will create a trace and capture only events that occur for AdventureWorks2012 database from James' session only. To do this, follow these steps:

  1. Start SQL Server Profiler.

  2. Select New Trace... from the File menu. In the Connect to Server dialog box, provide connection details of SQL Server hosting the AdventureWorks2012 database and click on Connect.

  3. In the General tab of Trace Properties, enter FilteringEvents as the Trace name and select Blank template for the Use the template: drop-down menu as shown in following:

  4. In Events Selection tab, check the checkbox for event class SQL:BatchCompleted under the TSQL event category as shown in following screenshot:

  5. Click on Column Filters... button.

  6. In the Edit Filter dialog box, select DatabaseName from the list of available data columns on the left. Expand the Like option and enter string value AdventureWorks2012; then press the OK button as shown in the following screenshot:

  7. In the Edit Filter dialog box, select SessionLoginName from the list of available data columns on the left. Expand the Like option and enter string value James; then press the OK button as shown in following screenshot:

  8. Click on the Organize Columns... button in Events Selection tab of Trace Properties dialog box. Select TextData data column and then keep clicking on Up button repeatedly to move the column up the order in the list, until the column appears as the second item, at the top of the list underneath EventClass data column. Do this same exercise also for the data columns DatabaseName and SessionLoginName so that the final order of the data columns should look like as shown in following screenshot. Press OK in the Organize Columns dialog box:

  9. Click on the Run button to run the trace in the Trace Properties dialog box.

Now, we will open two instances of SQL Server Management Studio one by one that connect to SQL Server with the logins James and Peter respectively and run a few queries.

  1. Open the first instance of SSMS and connect to SQL Server with the login credentials of James. In the query window, type and execute the T-SQL statements as shown in following script:

    USE [AdventureWorks2012]
    GO
    SELECT * FROM [Sales].[Customer]
    GO
    USE [master]
    GO
    SELECT * FROM sys.databases
    GO
    
  2. Open a second instance of SSMS and connect to SQL Server with the login credentials of Peter. In the query window, type and execute the same T-SQL queries as shown in previous step.

  3. Switch to SQL Server Profiler window that is running the trace. Examine the trace data as shown in following screenshot:

How it works...

In this recipe, we first created two SQL Server logins and their corresponding users in AdventureWorks2012 database to demonstrate how to apply a trace filter based on a specific SQL Server login, so that the events belonging to SQL Server logins other than the one for which the filter condition on SessionLoginName is satisfied are not captured. We executed a T-SQL script to create logins and users for James and Peter. For a login/user, the script first creates an SQL Server login account by the executing T-SQL statement—CREATE LOGIN. It then creates a user in the AdventureWorks2012 database for that login and adds the user to the db_owner database role by executing the T-SQL commands CREATE USER and ALTER ROLE respectively.

After creating logins and users, we started a new trace in SQL Server Profiler. We selected a Blank trace template and chose SQL:BatchCompleted event class as the only event that will be captured. Then we specified filters on DatabaseName and SessionLoginName data columns so that only the events which are occurred against AdventureWorks2012 database by user James are captured. We also organized the data columns in the Organize Columns dialog box, so that we can have better view of data columns we are interested in when trace data is displayed in SQL Server Profiler; we do not have to scroll much across the right side to see the values of TextData, DatabaseName, and SessionLoginName.

Tip

Use of DatabaseID

We can alternatively use DatabaseID data column instead of DatabaseName to specify a filter on a particular database. For this, we must know system assigned ID value for a specific database. This value can be retrieved by either calling DB_ID('AdventureWorks2012') metadata function or querying sys.databases catalog view.

After starting the trace, we opened two instances of SSMS out of which one instance connects with the login James and another one connects with the login Peter. In both the instances of SSMS, we run a few sample queries against the AdventureWorks2012 and master database.

We can see the resulting trace data as shown in final screenshot. Notice that events belonging to login Peter and the events occurred for master database were not captured.

There's more...

In a real world scenario, you may need to put filters on columns that are frequently used in trace filters to narrow down the data that you have to look at for troubleshooting. The following section lists some of data columns that are commonly used in trace filters:

  • ApplicationName: A filter can be specified on this data column so that only trace events raised by a particular client application are captured

  • DatabaseID: A filter can be specified on this data column so that only trace events raised for a specific database are captured

  • DatabaseName: A filter can be specified on this data column so that only trace events raised for a specific database are captured

  • HostName: A filter can be specified on this data column so that only trace events raised from a specific host or client machine are captured

  • LoginName: A filter can be specified on this data column so that only trace events raised by a specific login are captured

  • ObjectID: A filter can be specified on this data column so that only trace events raised for a specific object are captured

  • ObjectName: A filter can be specified on this data column so that only trace events raised for a specific object are captured

  • SessionLoginName: A filter can be specified on this data column so that only trace events raised by a specific login are captured

  • SPID: A filter can be specified on this data column so that only trace events raised from a specific session connection are captured

Tip

LoginName and SessionLoginName may look identical at first. However, there is a small difference between them.

By using EXECUTE AS syntax in SQL Server, we can execute T-SQL statements in the same session under different security context other than the security context of the login who actually initiates the session/connection. For example, James can login to SQL Server and run a query under security context of Peter by using EXECUTE AS command. In this case, data column SessionLoginName returns James, while LoginName data column returns Peter. In other cases, where SQL Statements are not executed under different security context, data columns SessionLoginName and LoginName return the same value.