DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

Using DBCC FREEPROCCACHE to Clear SQL Server Procedure Cache

Posted on May 14, 2013 Written by Andy Hayes Leave a Comment

It is possible to clear out the entire SQL Server procedure cache using DBCC FREEPROCCACHE

The procedure cache is where SQL Server will cache execution plans after they have been compiled. The benefit of this caching is that there is no need for the execution plans to be compiled at run time. This compiling operation typically consumes resource and slows down the execution time of the query.

The obvious disadvantage of clearing out the plan cache is that all execution plans for your SQL Server instance are recompiled upon execution which may slow things down temporarily until the cache is re-populated. Great for development, give thought before executing in production.

You may be under pressure to quickly get performance back to normal but it’s better to laser target the offending queries if you have time and address accordingly. You can clear out the procedure cache for specific queries as we will see further down the post.

Running DBCC FREEPROCCACHE

DBCC FREEPROCCACHE;

…and you should be presented with the following informational message:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

This is optional – WITH NO_INFOMSGS and will simply suppress the informational message above.

DBCC FREEPROCCACHE WITH NO_INFOMSGS;

Upon executing, messages with be written to the SQL Server error log for each cache store in the plan cache.

Here is an example:

SQL Server has encountered 1 occurrence(s) of cachestore flush for the ‘Object Plans’ cachestore (part of plan cache) due to ‘DBCC FREEPROCCACHE’ or ‘DBCC FREESYSTEMCACHE’ operations.

Using DBCC FREEPROCCACHE to clear specific execution plans from the cache

You first need to pinpoint the identifier of the execution plan which you want to clear out of the cache. This is known as a “plan handle” and you can find the plan handles and the cached SQL by issuing a query against sys.dm_exec_cached_plans and sys.dm_exec_sql_text

SELECT cp.plan_handle, st.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE st.text LIKE N'{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de}ThePlanYouAreLookingFor{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de}'

So here I will clear a plan from the cache, firstly by running this query to get the plan inserted to the plan cache.

SELECT TOP 1 * FROM Person.Person;

Now I will attempt to find the plan_handle for the execution plan.

SELECT cp.plan_handle, st.text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE st.text LIKE '{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de}Person.Person{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de}'

dbcc freeproccache clear sql server plan cache

So I can see that I want to clear out the cache for the second row as displayed in the screenshot.

So add the plan_handle and say bye to that query plan. 🙂 (The plan handle has been deliberately shortened in the code below otherwise my CMS has trouble displaying it 🙂 )

DBCC FREEPROCCACHE (0x060006002FE61B1D40FDAFF501000000010000....);

Filed Under: All Articles, SQL Server Performance Tagged With: dbcc, performance, sql server

The OLEDB Wait Type and How to Reduce It

Posted on January 13, 2013 Written by Andy Hayes Leave a Comment

reduce oledb wait type

In this post, I look at the OLEDB wait type.

Books Online lists this as

“Occurs when SQL Server calls the SQL Server Native Client OLEDB Provider. This wait type is not used for synchronization. Instead, it indicates the duration of calls to the OLE DB provider.”

“OLEDB” stands for Object Linking Embedding Database. OLEDB is an API used by SQL Server when retrieving data from a remote server or datasource.

Linked server queries, remote procedure calls, BULK INSERT operations, full text search and queries to external data sources, for example Excel all contribute to OLEDB wait type.

The wait type occurs when the remote system or network connection is not fast enough and so the calling server has to wait for the results to be returned.

Guidance on what to check and ideas on how to reduce OLEDB wait type

If the stats for OLEDB wait type are high then there are some things you can do:

  1. Look at your data distribution – are the linked server calls absolutely necessary?
  2. If the data being referenced is static then consider copying the data to local database and query it there.
  3. Consider using replication to bring the data local.
  4. If the data source is some file such as Excel, can you reference it locally instead of over a network connection?
  5. Is it feasible to import the Excel file into the database?
  6. Tune the remote query if you can. If it is a server which you are able to administer or you can speak with the DBA of that system, then find ways to make that query run faster – check indexes, check statistics etc.
  7. Have your network admins check over the connection between the server and data source. Is it fast enough?
  8. OLEDB may be a symptom of another wait type. For example check RESOURCE_SEMAPHORE wait type – is this high?

Filed Under: All Articles, SQL Server Performance Tagged With: performance, sql server, wait types

Reducing SQL Server CXPACKET Wait Type

Posted on December 4, 2012 Written by Andy Hayes 10 Comments

cxpacket wait type parallelismIn my last post, I wrote about how SQL Server schedules tasks to be executed. It’s important to be  able to understand this when when trying to analyse wait types and statistics in SQL Server.

For this post, I will be looking at the CXPACKET wait type and what you can do to reduce it.

This is one wait type that can be misjudged.

I mentioned before that the wait types need translation – MSDN describes this as:

Occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem.

This is a parallel processing wait type where many threads are used to satisfy the query. Each thread deals with a subset of the data. The idea is that if there are more threads working on different parts of the data set, it can be more efficient.

Parallel execution can therefore be a highly effective way of dealing with large sets of data, for example in a data warehouse/reporting server. However, in OLTP systems, it could well have a negative impact on performance.

CXPACKET wait type occurs when there are threads taking longer to execute than the other threads in that query. The completed threads are left in waiting state until the last thread can complete.

I’ll go into more detail about these settings in the next section but by default your SQL Server is configured for parallelism as the “max degree for parallelism setting” is set at zero.

For a query then to qualify for parallel execution, its estimated (or cached) cost needs to exceed the “cost threshold for parallelism” setting.

So in an example scenario on a server with 8 cores, if a parallel query executes with “max degree of parallelism” set at zero, then up to 8 cores can be used in the query. As each core completes its work, it waits until all cores have completed.

The result is CXPACKET waiting and the potential to delay other SPIDs wanting a piece of CPU time to process their request.

Assessing causes of CXPACKET wait type is vitally important

You have to remember that CXPACKET waits are normal for multiprocessor servers because of parallel execution. So simply adjusting your server configuration in an effort to reduce this wait type could have an overall negative impact on performance.

Understanding whether the system is an OLTP, DSS or combination of both is critical.

It’s no good simply assuming that because you have CXPACKET waits, that it is automatically a problem. If CXPACKET is one of the top reported wait types on your SQL Server, the next thing to consider is, what are the causes and is this wait type normal for my workload?

There may be other contributing factors. Is CXPACKET a symptom of something else? For example, it could be as simple as a missing index or out of date statistics. Your SQL Server then tries to compensate by generating a query plan that uses parallelism in an effort to optimize the query, or rather, to make the scan operation go faster.

How to reduce CXPACKET wait type by changing settings

There are some settings which can affect how your queries use parallelism.

  1. Server level – “Max Degree of Parallelism”
  2. Server level – “Cost Threshold for Parallelism”
  3. Query hint – MAXDOP

Max Degree of Parallelism – specifies the number of CPU cores to be used in parallel query execution at the server level. The default value is 0 which means that SQL Server will use all available cores. Setting this to 1 turns off parallelism. Here is an example which instructs the SQL Server to use no more than 2 cores for parallel execution:

EXEC sp_configure 'max degree of parallelism',2
GO
RECONFIGURE WITH OVERRIDE
GO

Cost Threshold for Parallelism –  the estimated elapsed time in seconds before the server will consider parallelism in the query execution. Here is an example setting this value to the estimated elapsed time of 10 seconds:

EXEC sp_configure 'cost threshold for parallelism', 10
GO
RECONFIGURE WITH OVERRIDE
GO

MAXDOP – a query hint where the number of cores is specified for the query. Here is an example using MAXDOP for 2 cores:

SELECT Column1, Column
FROM SomeTable
WHERE (Column1 > @Value1 AND Column2 < @Value2)
OPTION (MAXDOP 2)

Note that you can also change max degree of parallelism and cost threshold for parallelism settings using Management Studio. These settings can be found under the Properties->Server Properties->Advanced section.

Summary and guidance points for CXPACKET wait type and parallelism

  • OLTP – parallelism can have a negative impact on performance.
  • OLAP/reporting benefits from parallelism.
  • Look at your plan cache, which of your queries use parallelism?
  • Check query plans – do you have missing indexes or out of date statistics?
  • What other waits are occurring that might be causing CXPACKET?
  • Consider using MAXDOP to fine tune specific queries.
  • Set max degree of parallelism and cost threshold for parallelism to best suit your server’s function.
  • Do not adjust max degree of parallelism without proper analysis of your servers workload/queries.

Filed Under: All Articles, SQL Server Performance Tagged With: performance, sql server, wait types

An Overview of SQL Server Task Scheduling

Posted on November 25, 2012 Written by Andy Hayes 1 Comment

In my previous post, I touched upon SQL Server wait types and in order to understand wait types in SQL Server, it’s important to know first how the server schedules tasks.

On each CPU core, only a single thread or “worker” can be running at any given time. Each CPU, be it logical or physical inside your SQL Server is assigned a scheduler and it is responsible for managing the work which is carried out by threads.

You can view the schedulers on your SQL Server by running this code:

SELECT * FROM sys.dm_os_schedulers;

Schedulers are responsible for managing user threads and internal operations. If you run that DMV, you can see these types of status in there:

  • VISIBLE ONLINE (DAC) – thread scheduler used to manage the DAC (Dedicated Administator Connection)
  • VISIBLE ONLINE – user thread schedulers
  • HIDDEN ONLINE – internal operation thread schedulers

So depending on how many CPUs you have in your SQL Server, you will see from this DMV that there can be quite a few schedulers available.

More CPUs = more schedulers = more work can get done 🙂

There are some other status’ which you might see:

  • HOT_ADDED – schedulers created in response to a CPU which was added whilst the server was online (hot added CPU)
  • VISIBLE OFFLINE – user schedulers which are mapping to CPUs which are offline in the affinity mask.
  • HIDDEN OFFLINE – schedulers assigned to internal operations mapped to CPUs which are offline in the affinity mask.

Thread status

If you’ve ever looked at your process list in SQL Server either by using sp_who2 or activity monitor, you may have noticed that there is a column in there called “Status” (activity monitor in Management Studio 2012 calls this “Task State”)

There are some status’ which help you to understand how the queries execute, they are RUNNING, SUSPENDED and RUNNABLE

RUNNING – the thread is actively running on the CPU.

SUSPENDED – thread is waiting for a resource to become available. Thread is on the “waiter list”.

RUNNABLE – resources are now available to the thread but as only one thread can execute on a CPU at any given time, the thread has to wait its turn. It goes to the bottom of the “RUNNABLE queue” and finally returns to a RUNNING state when it is its turn to run as controlled by the CPU scheduler.

It goes to the bottom of the “RUNNABLE queue”

Please note that this behaviour can change if using RESOURCE GOVERNOR (Enterprise Edition)

The size of the “RUNNABLE queue” for each scheduler can be seen by looking at the value for the “runnable_tasks_count” column from the sys.dm_os_schedulers DMV.

Threads can transition around the different states until their work is completed. When on the waiter list, a thread can wait for some time before it changes state. There is no set order in which they are then changed from SUSPENDED to RUNNABLE.

In order to view what each thread is waiting for, you can look at the sys.dm_os_waiting_tasks DMV:

SELECT * FROM sys.dm_os_waiting_tasks;

 Summary points

  • Each CPU (logical or physical) is assigned a scheduler.
  • Only one thread can be running on a CPU at any given moment.
  • Threads are either running on the CPU (RUNNING), on the waiter list (SUSPENDED) or in the runnable queue (RUNNABLE).
  • Use sys.dm_os_schedulers to view schedulers on your SQL Server.
  • Use sys.dm_os_waiting_tasks to view the tasks which are in the wait queue, waiting for resource to become available.

Filed Under: All Articles, SQL Server Performance Tagged With: performance, sql server, wait types

  • « Previous Page
  • 1
  • 2
  • 3
  • 4
  • Next Page »

Categories

  • All Articles (82)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (19)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • How to shrink tempdb
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to move tempdb

Recent Posts

  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial
  • How to Use SQL CASE for Conditional Logic in Your SQL Queries
  • Using ISNULL in SQL Server to Replace NULL Values

Search

Connect

  • Twitter
  • Facebook
  • Google+
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright © ‘2021’ DBA Diaries built on the Genesis Framework

This site uses cookies. We assume you are happy with cookies but click the link if you are not. Close