Monthly Archives: October 2015

Find and Remove Duplicate Records

— by Ginger Keys

Having duplicate records in a database is an age-old problem that almost every organization has to deal with. Duplicates can appear because of careless data input, merging records from old systems into new systems, uploading leads from purchased lists, and multiple other reasons.

Identifying these duplicate records can also be tricky. You might have multiple people with the same first and last name. You might have one person with multiple addresses, emails, or other identifying characteristics. In most cases business rules, not repetitive values, will determine what constitutes duplicate data. Knowing your data is the key to determining whether your records are duplicates or not.

It can be a painstaking process, but we will go over some basic steps to help find and remove duplicate records in your database.

Create Duplicates

First let’s take a look at a table and purposefully insert duplicate records into it. I have selected rows out of a table that contains customer information.

Blog_20151029_1

Now I will insert these rows of data into my Customers table to create duplicate rows:

Blog_20151029_2

Find Duplicates

Duplicate records in your table will most likely not be in sequential order, as shown in our example above. So in order to find duplicates in your table run this query, substituting the names of your database, table, and relevant columns. You will need to determine which field(s) in your table will constitute duplicate records. Again, knowing your data and business rules for your organization will determine whether you have duplicate records in your database.

USE YourDatabase

GO

SELECT column1, column2, COUNT(column2) AS Duplicates

FROM YourTable

GROUP BY column1, column2

HAVING COUNT(column2) > 1

In our example below, the results below assume that contactname is the column we are using to determine duplicate records in our table.

 Blog_20151029_3

Delete Duplicates

Now that we see there are indeed duplicate records in our table, we can delete duplicate rows with this script (again, you will substitute your database, table, and column names):

SET NOCOUNT ON

SET ROWCOUNT 1

WHILE 1 = 1

BEGIN

DELETE  

FROM Customers

WHERE    contactname IN

(SELECT  contactname

FROM    Customers

   GROUP BY contactname

HAVING  COUNT(*) > 1)

IF @@Rowcount = 0

BREAK ;

END

SET ROWCOUNT 0

To check the results, we run the select statement again to make sure the duplicates are gone:

Blog_20151029_4

Conclusion

SQL Server has methods for preventing duplicate records in a database, such as enforcing entity integrity through the use of primary key constraints, unique key constraints, and triggers. However duplicates can occasionally occur because of database design error, or repetitive data that somehow gets past these quality control methods. The techniques described above, in addition to your familiarity with your data, will help you to find and delete duplicate records in your databases.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRX@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!

Contained Databases – Pros and Cons

–by Ginger Keys

Beginning with SQL Server 2012 and beyond, Microsoft has developed a new feature in all editions of SQL Server that allows us to create databases independent of the instance hosting that database. In this article I will cover some of the features, pros and cons, and how to create a contained database.

What is it?

A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. Contained databases have no dependencies on server-level metadata and settings.   And currently, there are only two options available:

NONE – no boundaries defined or visible – SQL versions 2012 and later have default set to NONE

PARTIAL – can define boundaries between databases and server. Contained databases can allow some features to cross the database boundary. SQL server can determine when containment boundary has been crossed.

Boundaries can be defined with PARTIAL contained databases. Boundaries are the borders that define where the metadata and settings for a database ends…and where the metadata and settings for the server begin. Boundaries separate settings, capabilities, features, and data. A contained element exists entirely within the database boundary. Uncontained elements cross the database boundary.

To identify these elements, you can query the sys.dm_db_uncontained_entities DMV which can help you identify uncontained entities that might prevent portability (what parts of your database won’t be portable (contained)). By determining the containment status of the elements of your database, you can determine what objects or features you need to replace or alter to support containment.

Why Use a Contained Database?

The main purpose of a contained database is to enable isolation and portability. To allow for a seamless move of your database from one server to another. To create a database that stores user information and other metadata inside the database, instead of an instance-level login which is stored in the master database. So there is no need to create logins on the instance level.

What are the Benefits?

  1. Authentication – normally logins are managed at the server level (as logins) and at the database level (as users), so they are defined in two locations.  The mapping that connects logins to users gets lost when a database is moved to a different server, which results in orphaned users.  When moving a database, the logins must be created on the new server, and must match exactly…otherwise this mapping will be broken.Orphaned users can also cause HA/DR to be compromised in this situation, because of trying to manage logins and users separately. Logins have to be synchronized against secondary or failover servers to avoid orphaned users.

    With contained databases, you won’t run into the issue of orphaned users. Contained database users do not have to authenticate with the instance. They don’t have to have a corresponding server login. The contained database user has no dependency on the server. They will connect directly to the database.

    For applications to connect, they have to specify the database in the application connection string, and include the parameter called ‘initial catalog’ in order to connect directly to the database.

  2. Use with AlwaysOn – in case of failover, contained database users will be able to connect to the secondary without creating logins on the instance hosting the secondary.
  3. Development – because developers don’t always know on which instance their database will be deployed, creating a contained database can limit the environmental effects on the database, and can lessen the work and difficulties for the developer.
  4. Administration – database owners maintain the database settings in the contained database, instead of master database, so it avoids users having to be given sysadmin permissions.
  5. Collation – temp objects are created with the collation of contained database in the case where the server collation is different from database.  There is no need to COLLATE anymore. There may be some problems if a session accesses both contained and non-contained databases. For information about collation when crossing between contained and uncontained databases, click here https://msdn.microsoft.com/en-us/library/ff929080%28v=sql.110%29.aspx 

What are the Issues or Limitations?

  1. Features you can’t use :
    • Replication
    • Change Data Capture
    • Change Tracking
    • Numbered Procedures
  2. Connection Strings – must specify the contained database in the connection string – you cannot rely on the login’s default database to establish the connection.
  3. Cross-database queries – applications are not able to cross-database query.  Even if the same user/password has been created in both databases.
  4. Passwords – creating a contained database user on a system that has a password policy is difficult, if the user has been created previously without a password on another system that has no password policy.  CREATE USER doesn’t support bypassing a server’s password policy
  5. Collation – If your code uses explicit collate or DATABASE_DEFAULT, you might need to change your code to use CATALOG_DEFAULT instead.  Also, you can’t use:
    • Schema-bound objects dependent on built-in functions with collation changes
    • Binding changes resulting from collation changes
    • Non-contained databases use the tempdb database’s collation settings by default, whereas contained databases use their own collation settings by default when interacting with the tempdb database. It’s important to note that although contained databases can interact with the tempdb database to create temporary tables, the use of explicitly named constraints defined against those temporary tables isn’t allowed once databases become contained.

The items in the list above are not necessary problems, but issues you should be aware of before creating and using a contained database.

How to Create a Contained Database

–To create or enable a contained database, you must first enable this feature on the SQL instance:

SP_CONFIGURE ‘contained database authentication’, 1

GO

RECONFIGURE

GO

–Create your database:

USE master

Go

CREATE DATABASE ContainDB

GO

–Change database type to contained:

USE master

GO

ALTER DATABASE ContainDB

SET CONTAINMENT = PARTIAL

You can do this either through TSQL, or you can open the Properties of the database, go to the Options page, and change the Containment Type to ‘Partial’.

Blog_20151023_1

–create a contained database user

CREATE USER [CDB_User]

WITH PASSWORD=‘password’,

DEFAULT_SCHEMA=[dbo]

GO

–give the newly created user permissions to access the database:

EXEC sp_addrolemember ‘db_owner’, ‘CDB_User’

GO

If you prefer to grant memberships in roles other than db_owner, substitute the appropriate role(s) as shown:

EXEC sp_addrolemember ‘db_datareader’, ‘CDB_User’

GO

EXEC sp_addrolemember ‘db_datawriter’, ‘CDB_User’

GO

Log into the Contained Database

In order to log into the contained database with the login created above, open SSMS and change authentication to SQL Server Authentication. Type in the Login and Password, and click the Options>> button:

Blog_20151023_2

Type in the name of your contained database in the ‘Connect to database’ box, and hit Connect:

Blog_20151023_3

You will then be connected to only the contained database. You should not be able to see any of the other databases or instance features.

Blog_20151023_4

Conclusion

Contained databases make it easier to move databases between systems. Some complications related to non-contained databases can be resolved by using a partially contained database. They are fully portable, which makes it easier for a DBA to move databases between instances without having to deal with issues like orphaned users.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!

Using Common Table Expressions to Improve Query Performance

-by Jeffry Schwartz

Queries with multiple join clauses create particularly difficult issues when they perform poorly. Join ordering is extremely important because if a join creates a huge interim result set at the beginning of the query execution, SQL Server must continue to handle this result set until it can be trimmed down with filtering or additional join clauses. If this does not occur until the end of query execution, the query will usually perform poorly. However, if the number of records from a large table can be limited early in the execution or the join to the large table is added at the end of the query, the query will usually perform better. Therefore, when large tables are involved the ordering and the types of joins can dramatically affect the performance of a query. When multiple large tables are involved, it is often quite difficult to determine which one caused the poor performance.

One way to influence SQL Server’s ordering of joins is to use Common Table Expressions (CTEs), which were introduced in SQL Server 2005. Using CTEs also has the benefit of allowing a developer to break up a very complex query into its component parts, which can simplify the logic as well as assessment of exactly which joins dramatically affect performance. In addition, since the query is broken up into component parts, development, maintenance, and readability can be simplified greatly. A CTE is defined to be a “temporary named result set, derived from a simple query and defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement.” “CTEs can be thought of as alternatives to derived tables (subquery), views, and inline user-defined functions.” Source: http://en.wikipedia.org/wiki/Common_table_expressions. The following query provides an illustration of a CTE in its most basic form:

with Top30000 as ( — just check the first 30,000 entries

select top 30000 Duration

from <table> with (nolock)

where Duration is not null

)

select min(Duration) as [Min Duration]

from Top30000

Clearly, this query could be written in one statement and the query plan would undoubtedly be the same regardless of whether it used a CTE. One key thing to remember about CTEs is that they only exist within the scope of the query. For example, any subsequent T-SQL statements will not have access to the Top30000 entity, so it is NOT a temporary table. A slightly more sophisticated example is shown below:

use AdventureWorks2008R2;

with ProductReviewID as (

SELECT p.ProductID, p.Name, pr.ProductReviewID, p.ListPrice

FROM Production.Product p

LEFT OUTER JOIN Production.ProductReview pr ON p.ProductID = pr.ProductID

WHERE p.ProductID = 718

)

SELECT DISTINCT p.ProductID, p.Name, p.ListPrice, sd.UnitPrice AS [Selling Price]

FROM Sales.SalesOrderDetail AS sd

INNER JOIN ProductReviewID AS p ON sd.ProductID = p.ProductID

Again, this query could be constructed without using a CTE. In this particular case, I selected two sample join queries from BOL and combined them into a single query using a CTE. Although the filter is hard-wired in this example, it is illustrative of filtering the result set early in the query to reduce the number of records being used by the last part of the query. Note also that the last portion of the query had to join to the CTE in order for the CTE to be visible. A subquery can also accomplish this.

Most CTE examples illustrate recursion or utilize the following layout that is similar to the one that used AdventureWorks above:

CTE1

CTE2 select from CTE1

Select from CTE2 joining other tables

However, this layout is also possible:

CTE1

CTE2

Select from CTE2 joining CTE1 joining other tables

As is this form:

CTE1

CTE2

CTE3, which selects from CTE2 joining CTE1

Select from CTE3 joining other tables

One method that I have used extensively to develop efficient queries is to build complex queries gradually using the last layout, i.e., select from tables and gradually introduce joins while maintaining excellent performance. It is critical that representative data be used during this process, i.e., the table sizes used for testing should be representative of what the production database contains. For example, most of the large tables in the AdventureWorks database contain approximately 20,000 records. With table sizes this small, almost any query design works well. However, if the tables contain 200,000 or 2,000,000 records the behavior would be quite different. In these situations, joins can provide substantial filtering or actually expand the result set. The advantage of the gradual addition approach is that the developer can identify immediately those joins that significantly degrade performance and then attempt to utilize other joins until only the most troublesome joins remain. Although a very efficient form of the query can often be devised using only CTEs, sometimes, interim results must be stored in a temporary table, which is then joined with the large troublesome table.

In summary, although using CTEs will not force SQL Server to perform joins in the order specfied in the T-SQL code, it can often influence SQL Server to adhere more closely to a desired join ordering sequence, especially if filtering clauses are used early in the CTE sequence. Adding joins against large table at the end of a query can also achieve a similar result. Regardless, using a gradual approach to building queries with CTEs enables developers to understand exactly what the effect of every join is and therefore, create the most efficient query possible.

This is the beginning of a series on CTE’s and performance. Stay tuned for more CTE posts to come. I’ll show some examples of how to take an existing query with joins and convert it to a CTE with performance improvement.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRX@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!

AlwaysOn Availability Groups – Enable or Disable Jobs on Failover

— By Lori Brown  @SQLSupahStah

After reading up on AlwaysOn Monitoring and Alerting (https://blog.sqlrx.com/2015/08/27/alwayson-monitoring-and-alerting/), I was asked to come up with a way to automatically enable jobs that may only need to run on the primary replica of an availability group. Requirements were that on failover of an AlwaysOn Availability Group some jobs should be enabled on the primary replica and some jobs should be disabled on the secondary replica. After some thinking and research on the sys.dm_hadr_availability_group_states dynamic management view, I came up with a solution that uses a job category, a failover alert setup for error 1480 that is notated in the AlwaysOn Monitoring and Alerting post and some code in a job that is only run if the failover alert fires.

The sys.dm_hadr_availability_group_states dmv gives information on the state of availability groups that are running on the instance including the name of the instance that is the primary. https://msdn.microsoft.com/en-us/library/ff878537.aspx. Here is my query:

SELECT s.primary_replica

FROM sys.dm_hadr_availability_group_states s

JOIN sys.availability_groups ag ON ag.group_id = s.group_id

WHERE ag.name = ‘AGroup_Dev’

Blog_20151008_1

Since I can get at information showing if an instance is primary or secondary, all I have to do is find the jobs that should be enabled. The best way I could think of to do this is to assign a specific job category to jobs that need some type of action taken on them and query for jobs with the correct category on them.

SELECT j.name

FROM msdb.dbo.sysjobs j

WHERE EXISTS (SELECT c.category_id

FROM msdb.dbo.syscategories c

WHERE j.category_id = c.category_id

AND c.name = ‘SomeCategory’)

AND j.enabled = 0  — disabled but should be enabled on failover

ORDER BY j.name

Blog_20151008_2

All I would need to do is add some logic that would roll through (yes, sadly that means a cursor) each job returned by the query above and run sp_update_job to enable or disable it.

FETCH NEXT FROM agjobs INTO @jobname

— Enable AG jobs if primary

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQLString = ‘EXEC msdb.dbo.sp_update_job @job_name = ”’+@jobname+”’ , @enabled = 0′

EXEC sp_executesql @SQLString

— Get the next record

FETCH NEXT FROM agjobs INTO @jobname

END

— Close and deallocate the cursor

CLOSE agjobs

DEALLOCATE agjobs

So, now let’s put it all together and fill in the rest of the code…..

In our development server we are using the VM’s and instances that were used in the AlwaysOn Monitoring and Alerting post and the AGroup_Dev availability group that was set up. There are 3 instances: AlwaysOn1 is set as the primary, AlwaysOn2 is a secondary in synchronous mode, and AlwaysOn3 is a secondary in asynchronous mode. On all 3 instances we have the jobs AG Database Health and AG Replica Health. These jobs should only be enabled to run if the instance is the primary replica for AGroup_Dev.

First, create a new job category that will be assigned to any jobs that must have a specific action applied to it if a failover occurs. In my example below, I have created the job category AG1 on the AlwaysOn1 instance.

USE [msdb]

GO

— Create AG job category

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name = N’AG1′ AND category_class = 1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N’JOB’, @type = N’LOCAL’, @name = N’AG1′

END

GO

I also created job categories (AG2 & AG3) on AlwaysOn2 and AlwaysOn3 respectively. I did this so that I could keep track of what instance the category applies to. Once my job category is created, I then assigned it to the AG Database Health and AG Replica Health jobs.

Blog_20151008_3

Next, make sure that Error 1480 is set to generate an alert.

EXEC msdb.dbo.sp_add_alert

@name = N’AG Role Change (failover)’,

@message_id = 1480,

@severity = 0,

@enabled = 1,

@delay_between_responses = 0,

@include_event_description_in = 1;

GO

Now we can make the job that needs to query the dmv and enable or disable jobs depending on the value in the primary_replica column. I created the AlwaysOn Job Management job on each instance using the code below and simply changed the places where the job category is used to have the correct category name for the instance it is on.

— create job to run if alert 1480 fires

/****** Object:  Job [AlwaysOn Job Management]    ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’AlwaysOn Job Management’,

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N’No description available.’,

@category_name=N'[Uncategorized (Local)]’,

@owner_login_name=N’sa’,

@job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Enable or Disable]    ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Enable or Disable’,

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0,

@subsystem=N’TSQL’,

@command=N’DECLARE @primreplica VARCHAR(100)

DECLARE @AGName VARCHAR(100)

DECLARE @jobname VARCHAR(128)

DECLARE @SQLString NVARCHAR(2000)         — Dynamic SQL string

SET @AGName = ”AGroup_Dev”  — must know ag name

SELECT @primreplica = s.primary_replica

FROM sys.dm_hadr_availability_group_states s

JOIN sys.availability_groups ag ON ag.group_id = s.group_id

WHERE ag.name = @AGName

IF UPPER(@primreplica) =  UPPER(@@SERVERNAME)

BEGIN

— PRIMARY

— Find all disabled AG jobs.

DECLARE agjobs CURSOR FOR

SELECT j.name

FROM msdb.dbo.sysjobs j

WHERE EXISTS (SELECT c.category_id

FROM msdb.dbo.syscategories c

WHERE j.category_id = c.category_id

AND name = ”AG1”)

AND j.enabled = 0  — disabled but should be enabled on failover

ORDER BY j.name

— Open the cursor

OPEN agjobs

FETCH NEXT FROM agjobs INTO @jobname

— Enable AG jobs if primary

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQLString = ”EXEC msdb.dbo.sp_update_job @job_name = ”””+@jobname+””” , @enabled = 1”

EXEC sp_executesql @SQLString

–PRINT @SQLString

— Get the next record

FETCH NEXT FROM agjobs INTO @jobname

END

— Close and deallocate the cursor

CLOSE agjobs

DEALLOCATE agjobs

END

ELSE

BEGIN

— SECONDARY

— Find all enabled AG jobs.

DECLARE agjobs CURSOR FOR

SELECT j.name

FROM msdb.dbo.sysjobs j

WHERE EXISTS (SELECT c.category_id

FROM msdb.dbo.syscategories c

WHERE j.category_id = c.category_id

AND name = ”AG1”)

AND j.enabled = 1  — enabled but should be disabled on failover

ORDER BY j.name

— Open the cursor

OPEN agjobs

FETCH NEXT FROM agjobs INTO @jobname

— Disable all AG1 jobs if secondary

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQLString = ”EXEC msdb.dbo.sp_update_job @job_name = ”””+@jobname+””” , @enabled = 0”

EXEC sp_executesql @SQLString

–PRINT @SQLString

— Get the next record

FETCH NEXT FROM agjobs INTO @jobname

END

— Close and deallocate the cursor

CLOSE agjobs

DEALLOCATE agjobs

END’,

@database_name=N’master’,

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

I don’t have a schedule set up for the AlwaysOn Job Management job because I only want this to run if a failover alert is sent. All that is left to do is to make the 1480 failover alert run the AlwaysOn Job Management job.

— Make the alert run the Job management job

EXEC msdb.dbo.sp_update_alert @name=N’AG Role Change (failover)’,

@job_name=N’AlwaysOn Job Management’

GO

Blog_20151008_4

I did this for all instances that are in the AlwaysOn group AGroup_Dev, so now we need to test by failing over to each node and making sure that the jobs with the special category assigned are enabled or disabled.

Here are AlwaysOn1 and AlwaysOn2 before failover:

Blog_20151008_5

And here is how they look after failover:

Blog_20151008_6

As you can see the AG Database Health and AG Replica Health jobs are now disabled on AlwaysOn1 and are enabled on AlwaysOn2.

Of course the job code can be changed to accommodate more than 1 availability group and more than 1 job category. Be creative and have fun with it! Hope this helps anyone who needs this type of functionality for their AlwaysOn replicas.

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRX@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!

Tip of the Month: Buffer Pool Extension

— By Lori Brown  @SQLSupahStah

Available in SQL Server 2014 Enterprise Edition and Standard Edition x64 only

Buffer Pool Extension is a way to use solid state disks (SSD) as nonvolatile RAM (NvRAM) instead of memory. It reduces latency. It stores clean or committed pages only so there is no risk of data loss and will give faster access to data that would have otherwise be slow because it would have to be read from disk. Buffer Pool Extension can provide performance gains for read-heavy OLTP workloads.

Turn on Buffer Pool Extension in SQL with an ALTER SERVER statement. FILENAME is the path to the bpe file that will be located on the SSD disk and SIZE is the size of the bpe file in GB.

ALTER SERVER CONFIGURATION

 SET BUFFER POOL EXTENSION ON

(FILENAME = ‘X:\BPESSD\EXTENDMEMORY.BPE’, SIZE = 50 GB)

Troubleshooting SQL Server CPU

–by Ginger Keys

Recently, a client of mine contacted me in a panic because CPU was running at 99-100% over an extended period of time, which was affecting everything on the machine.

We’ve all seen it at one time or another. And there are several reasons this could be happening. It could be SQL consuming most of the CPU, or it could be something outside of SQL. As a DBA, you don’t want to spend a lot of time troubleshooting high CPU usage if SQL isn’t the culprit. If the issue is caused by processes outside of SQL, you can take a high level look around, but should probably defer that to your Sys Admin or your IT team. If it is SQL that is causing the issues though, we need to dig in deeper.

This article discusses basic CPU troubleshooting. We will take a very ‘high level’ approach to a topic that can potentially get very in-depth and complex. The goal is not only to provide immediate relief but also to help prevent future stress to your CPU caused by SQL.

Is it SQL or System Processes causing high CPU Usage

The first step is to determine if it is SQL or not causing CPU pressure. There are several ways you can make this determination.

1. Task Manager/Resource Monitor

The quick way is to open Task Manager and/or Resource Monitor, and look to see if sqlservr.exe is the culprit. Be cautious when looking at task manager to troubleshoot, as this tool records data once per second. So task manager will record spikes in performance (which is normal), instead of averaging it out over a longer time span (which is a better indicator of stress).

Blog_20151001_1

Blog_20151001_2

2. Perfmon Counters  

You can also determine if SQL is the culprit for high CPU usage by using Performance Monitor (Perfmon). Unlike task manager which records data once/second, you can adjust Perfmon to poll the data every 3-5 seconds to get a better indication of CPU stress. There are four counters you will want to look at:

    • Processor: % Privileged Time (_Total) – measures the amount of time your computer is executing I/O operations and other System services (aka Kernel mode).  If this metric is high (>20%) it’s possible that something like system backups are running, your driver is bad, or large files are being copied into or out of the system.   Either way, it’s time to get your Sys Admin involved.
    • Processor: % User Time (_Total) – measures amount of time your computer is executing user applications, including SQL.  If user time is consuming most of the time, we need to look at what is running on SQL to see what is causing the high CPU usage.
    • Process: % Privileged Time (sqlservr.exe instance) – measures the percentage of elapsed time that the process threads spent executing code in privileged (Kernel/System) mode
    • Process: % User Time (sqlservr.exe instance) – measures the percentage of elapsed time that the process threads spent executing code in user (application/SQL) mode.

Blog_20151001_3

3. Query DMVs

Another method to find out what is causing high CPU usage is to execute the following DMV:

–Get CPU Utilization % from SQL, NonSQL (System), and Idle

DECLARE @ms_ticks_now BIGINT

SELECT @ms_ticks_now = ms_ticks

FROM sys.dm_os_sys_info;

SELECT TOP 10 record_id

,dateadd(ms, – 1 * (@ms_ticks_now – [timestamp]), GetDate()) AS EventTime

,SQL_CPU_Use

,100 – SystemIdle – SQL_CPU_Use AS NonSQL_CPU_Use

,SystemIdle

FROM

(

SELECT record.value(‘(./Record/@id)[1]’, ‘int’) AS record_id

,record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]’, ‘int’) AS SystemIdle

,record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]’, ‘int’) AS SQL_CPU_Use

,TIMESTAMP

FROM

(

SELECT TIMESTAMP

,convert(XML, record) AS Record

FROM sys.dm_os_ring_buffers

WHERE ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’

AND record LIKE ‘%<SystemHealth>%’

) AS x

) AS y

ORDER BY record_id DESC

(from: http://sqlblog.com/blogs/ben_nevarez/archive/2009/07/26/getting-cpu-utilization-data-from-sql-server.aspx)

The query results break down CPU usage as SQL usage, NonSQL (or System) usage, and Idle. If the SQL_CPU_Use has predominantly higher percentages than NonSQL or Idle, then we know that SQL is causing the pressure on CPU.

Blog_20151001_4

                                                                                                                                                                                

System Process causing high CPU usage

If after your preliminary investigating, you discover that SQL is not causing CPU pressure, but rather it is a system issue, the problem could be any number of things. It’s good to have a general understanding of these other issues, but as a SQL DBA, you may not be able to spend a lot of time troubleshooting these. Some system, or non-SQL issues that could cause CPU stress are:

1. Architecture

2. Virtual Environment

  • Host CPU settings
  • Host processor usage
  • vCPUs allocated among guests
  • # guests
  • # other machines on host, and how busy are they
  • Resource governor settings

3. CPU Power Settings

  • Should be set to high performance on SQL server.  However, this has had inconsistent results, and may or may not make a significant difference.

4. Hardware & Software Interrupts – these should be looked at over a period of time, (at least 5 seconds per sample over 30 minutes or more), and should also be interpreted in relation to a baseline of what’s normal for your system

  • Processor: % Privileged Time (Kernel Time + DPC + Interrupts) over 15-20% consistently is bad.  High Kernel time could indicate windows backups are happening or result from a bad driver
  • Processor: % Interrupt Time – over 5-10% consistently is a problem.  This shows the percentage of time processor spent servicing hardware interrupts.  A high rate could indicate disk or network adapter issues.
  • Processor: % DPC Time – over 5-10% consistently is a problem.  This shows percentage of time processor handles deferred procedure calls.  These are interrupts with a lower priority, and have been deferred to some kind of queue.  Could be software or hardware causing this, and might be necessary to redistribute interrupts or upgrade to faster processor.

5. Antivirus Scans – temporarily disable anti-virus software to rule this out, or locate the antivirus process in task manager/resource monitor.

6. SvcHost.exe – this is a generic process name, and includes individual services that Windows uses to perform various tasks.  There can be multiple occurrences of this process running on your computer at the same time.  

                                                                                                                                                                                

SQL causing high CPU usage

If you determine that it is definitely SQL causing the CPU stress, you first need to find the problem and correct or stop it. After you provide some immediate relief to your system, you then need to troubleshoot the issue so that you can make necessary adjustments and prevent it from happening in the future.

1. sys.dm_exec_requests

First you want to find what in SQL is monopolizing the CPU.  To do this, look at the currently executing queries along with the CPU usage.  Run this script to identify the queries using the most CPU time:

SELECT sessions.session_id

,requests.status

,requests.blocking_session_id

,requests.wait_type

,requests.wait_time / (1000.0) ‘wait_time(sec)’

,requests.cpu_time

,requests.total_elapsed_time / (1000.0) ‘total_elapsed_time(Sec)’

,Substring(sqltext.TEXT, (requests.statement_start_offset / 2) + 1,

((CASE requests.statement_end_offset

WHEN – 1 THEN Datalength(sqltext.TEXT)

ELSE requests.statement_end_offset

END – requests.statement_start_offset  ) / 2

) + 1) AS statement_text

,requests.command

,sessions.login_name

,sessions.host_name

,sessions.program_name

FROM sys.dm_exec_sessions AS sessions

INNER JOIN sys.dm_exec_requests AS requests ON requests.session_id = sessions.session_id

CROSS APPLY sys.dm_exec_sql_text(requests.sql_handle) AS sqltext

WHERE requests.session_id != @@SPID

ORDER BY requests.cpu_time DESC

 

When you identify the query consuming the most CPU time, you should be able to observe the TSQL command that SQL is processing, the user issuing the command, and determine whether it can be safely terminated or not.

2. sys.dm_os_wait_stats

This provides historical (from the time that the SQL instance started), cumulative data and records waits related to the workload of your server.  We can use this DMV to investigate the most prevalent wait types over a period of time, and specifically see CPU waits (signal time) which might indicate CPU pressure.  Since this data is cumulative, current waits might not be evident and adjacent samples should be differenced to determine actual resource and signal wait times.  It is best to track these statistics over time and establish a baseline on which to compare and look for anomalies.

3. Activity Monitor

Using Activity Monitor in SSMS displays both DMVs above in an easy to read report, but can create a performance hit to run it…maybe not a good idea if your CPU is already under extreme stress.

Blog_20151001_5

4. Stored Proc / Query

    After you have taken care of the query that was causing the CPU pressure, you can run the following to get recent CPU utilization by query.  This will show the most expensive queries that have already completed and their execution plans, so that you can check for ways to prevent them from beating up your CPU in the future.

— Get CPU utilization by query

SELECT TOP 10 st.text

,st.dbid

,st.objectid

,qs.total_worker_time

,qs.last_worker_time

,qp.query_plan

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

ORDER BY qs.total_worker_time DESC

(from: http://sqlskills.com/blogs/glenn)

Some of the steps you might need to take in order to improve performance for these queries are:

  • Check execution plan and tune query.  For a great explanation click here https://blog.sqlrx.com/2015/03/16/introduction-to-sql-server-execution-plans/
  • Update statistics on tables called – statistics are what SQL uses to decide how to execute your query in the most efficient way.
  • Identify high I/O queries
  • Create/rebuild indexes – be sure not to create indexes without thoroughly testing first!

5. Database

    To find out if one or more of your databases is predominantly causing CPU pressure, the following query will help determine which database(s) has used the most CPU resources on your instance:

— Get CPU utilization by database

WITH DB_CPU_Stats

AS

(SELECT DatabaseID, DB_Name(DatabaseID) AS [Database Name], SUM(total_worker_time) AS [CPU_Time_Ms]

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID]

FROM sys.dm_exec_plan_attributes(qs.plan_handle)

WHERE attribute = N’dbid’) AS F_DB

GROUP BY DatabaseID)

SELECT ROW_NUMBER() OVER(ORDER BY [CPU_Time_Ms] DESC) AS [CPU Rank],

[Database Name], [CPU_Time_Ms] AS [CPU Time (ms)],

CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPU Percent]

FROM DB_CPU_Stats

WHERE DatabaseID <> 32767 — ResourceDB

ORDER BY [CPU Rank] OPTION (RECOMPILE);

(from: http://sqlskills.com/blogs/glenn)

This information should be gathered over a period of time, to determine if it is a regular occurrence. If you see that a database is consistently using most of the CPU resources, you can

  • Update statistics – to ensure SQL is executing queries in the most efficient way
  • Create/rebuild indexes – to help eliminate fragmentation and forwarded records, as these cause  SQL to work harder to find records

6. Excessive compilations/recompilations

When a query is submitted to SQL, it has to create or reuse a query plan. Creating a query plan can be very CPU intensive, so reusing them is easier on the CPU. Query plans may have to be recompiled if the object schema changes, or if the statistics change on the underlying table or database.

You can view these metrics through Perfmon using these counters:

  • SQLServer:SQL Statistics: SQL Compilations/sec
  • SQLServer:SQL Statistics: SQL Re-Compilations/sec

The ideal value for Re-compilations per second is zero.  And the ideal value for Compilations is less than 25% of SQLServer:SQL Statistics: Batch Requests/sec.

Blog_20151001_6

If your compilations or recompilations are at a higher than optimal number, consider the following:

  • Optimize for ad-hoc
  • Enable parameterization – Query plan reuse is critical for optimal performance on highly transactional systems and it is most often achieved by parameterizing your queries or stored procedures.
  • Avoid statistics based recompilations – by using KEEP PLAN hint or KEEPFIXED PLAN hint

7. SQL Server Configuration

    Making sure your SQL server is configured for optimal performance and best practices can make a huge difference in performance.  Some of the things you can look at are:
    • Max Degree of Parallelism – if you are seeing a lot of CXPACKET wait types, you may need to adjust your MAXDOP.
    • Affinity Mask – this may or may not make a big difference
    • Priority Boost (don’t enable) –this too, may or may not make a big difference
    • Max worker threads – adjusting this could help, but is beyond the scope of this article.
    • File organization on underlying disks – choice of RAID array, and the organization and placement of file types make a big difference in the performance of your server

                                                                                                                                                                                

Conclusion

Troubleshooting CPU issues can be a very painstaking process as there are multiple combinations of issues that can be causing stress. The methods outlined above provide a basic approach to troubleshooting CPU pressure. It could be hardware, software, system, SQL, web, network, and a host of other problems, as well as a complicated combination of these! As a DBA, your main concern is to find and correct any SQL related issues causing this pressure on the CPU.

If you would like assistance configuring, maintaining, or monitoring your SQL Server and instances please contact us at SQLRx@sqlrx.com. We would be happy to answer any question you have, and would be interested to hear about your SQL experiences as well!

                                                                                                                                                                                

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!