SQL SERVER – Plan Caching and Schema Change – An Interesting Observation

Last week, I had published details regarding SQL SERVER – Plan Caching in SQL Server 2008 by Greg Low on this blog. Similar to any other white paper, I have read this paper very carefully and enjoyed reading it. One particular topic in the white paper that caught my attention is definition of schema change. I was well aware of this definition, but I have often found that users are not familiar with what exactly does a schema change mean.

Many people assume that a change in the table structure is schema change. In fact, creating or dropping index on any table also qualifies as schema change.

As per the white paper, “Schema change” is defined as follows:

  • Adding or dropping columns to a table or view.
  • Adding or dropping constraints, defaults, or rules to/from a table.
  • Adding an index to a table or an indexed view.
  • Dropping an index defined on a table or an indexed view (only if the index is used by the query plan in question).
  • Dropping a statistic (not creating or updating!) defined on a table will cause a correctness-related recompilation of any query plans that use that table. Such recompilations occur at that instant when the query plan in question begins execution. Updating a statistic (both manual and auto-update) will cause an optimality-related (data related) recompilation of any query plans that uses this statistic.

As per the above definition, I decided to experiment with schema change. In white paper, it was mentioned that schema change will recompile the execution batch. I decided to test this out. I created a small script (given below) and decided to test it.

As per the script, I created a large table and stored the procedure that was accessing that table. I created another object that was an index to the table; when SP was ran with index on table, it showed a plan different from that without the index on table. It was evident that the batch was recompiled when the index on the table was modified.

The script is available here to download.

Without Index execution plan:

With Index execution plan:

It is clear from the execution plan that it is different, and this proves the point that schema change recompiles the execution plan.

There is one more script provided in the white paper, which gets data from DMV related to cached plan. When I ran the following SP, it did not give me very interesting results.

SELECT cp.objtype AS PlanType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.refcounts AS ReferenceCounts,
cp.usecounts AS UseCounts,
st.TEXT AS SQLBatch,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st;


When I ran the above script, I found that the UseCount column did not reset when QueryPlan is changed. This was very surprising as I was expecting UseCount to reset on changing the query plan. I sent an email to Dr. Greg Low, who is author of this white paper. He was very kind to respond back within minutes with a proper answer, which is as follows:

You’ll notice in Profiler when you run the sproc after creating the index that it does in fact cause a recompile. However, in SQL Server 2008 (and 2005), these are statement level recompiles not proc level recompiles. I imagine that’s why they’re not reflected in the UseCount values ie: the plan itself isn’t recompiled and that’s what the counts are based on.

Well, this simple explanation clarified my doubts. Let me know what you think, and I strongly suggest you all to read the white paper written by Dr. Greg Low.

Reference : Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Introduction to SQL Server 2008 Profiler

Introduction

SQL Server Profiler is a powerful tool that is available with SQL Server since a long time; however, it has mostly been underutilized by DBAs. SQL Server Profiler can perform various significant functions such as tracing what is running under the SQL Server Engine’s hood, and finding out how queries are resolved internally and what scripts are running to accomplish any T-SQL command. The major functions this tool can perform have been listed below:

  • Creating trace
  • Watching trace
  • Storing trace
  • Replaying trace

Trace includes all the T-SQL scripts that run simultaneously on SQL Server. As trace contains all the T-SQL scripts running on SQL Server, it often tends to become considerably huge. Hence, it is always a good practice to capture only those data that are actually required for analysis.

Dilemma to use on Production Server

SQL Server Profiler has a lot of potential as a tool and offers several advantages. Unfortunately, developers have been keeping their hands off from this tool and usually give two major excuses.

  • Reason 1: Profiler adds too much overhead to production server.
  • Reason 2: Profiler accumulates plethora of data making it extremely intricate to analyze them afterwards.

It cannot be denied that both the above excuses are valid reasons and play a significant role in discouraging developers from using the profiler. When a profiler is run over a production server it adds additional load on the CPU. However, the real question is – How much load does the profiler add to CPU? It may be possible that the production server’s CPU is not used more than 50% at all, and running the profiler over that database will not degrade the performance.

If the profiler is configured appropriately to trace only relevant data for analysis, it will diminish the additional load that is introduced while it is running. If the profiler is run for a longer duration, then to capture the event of longer duration it is very important to collect selective data, otherwise the collected data can outgrow the space on the server.

Profiler Terminology

It is important to understand profiler terminology before digging into its details and finding how it works.

Event - An event is an action within an instance of SQL Server Database Engine. Some good examples of event are running any T-SQL or performing any operation in the SSMS that is related to database; running stored procedures, and creating jobs.

Event Class - An event class can be defined as a type of event that can be traced. Examples of event classes are SP:Starting and RPC:Completed.

Event Category – Group of events are called an event category. Examples of event category are Stored Procedure and Locks. In fact, there can be multiple event categories that can be selected for single trace.

Data Column - Data column is an attribute of an event class captured in the trace. Data column contains value of an event class.

Filter –Filters are used to create selectiveness in data that are collected in trace.

Trace - A trace captures data based on the selected event classes, data columns, and filters. A trace devoid of filter can be very hefty. Columns that are indispensable for the analysis should be selected only in trace. Trace can be saved in the database as well as in trace files.

Template - A template defines the default configuration for a trace. Templates can be saved, imported and exported between SQL Server instances. Templates from one SQL Server version cannot be imported to a different SQL Server version. SQL Server 2009 comes with 9 pre-created templates. The default template is a standard template that captures all SP and T-SQL batches. It additionally logs general database server activity.

Some very basic examples are presented below where data has been captured.

Starting the Profiler

SQL Server profiler is a component of client tools and can be installed independently from SQL Server Database Engine. It is mandatory for the user to have “system admin” rights to start the profiler. It can be started using the following methods.

1. Going to Start >> All Programs >> Microsoft SQL Server 2008 >> Performance Tools >> SQL Server Profiler

2. From SQL Server Management Studio >> Go to Tools >> SQL Server Profiler

3. For SQL Server 2008, type profiler in the command prompt.

4. For SQL Server 2005, type profiler90 in the command prompt.

Collecting Data

Once the profiler is started, connect it to any database. As discussed earlier, a profiler is not restricted to connecting only to the local database. With appropriate authentication and system admin role, it can connect to any database and capture the data.

Once the profiler is started click on Menu >> File >> New Trace.

This will display a login prompt very similar to SQL Server Database Engine. Connect with the appropriate username and password.

It will display the ‘Trace Properties’ screen. On this screen you can enter the trace name. You will notice that trace provider name, type and versions are pre-populated and cannot be altered. These are set based on which SQL Server instance you are connected with.

The ‘Trace Properties’ screen contains a drop-down for template selection, which is labeled with ‘Use the template’. As mentioned earlier, the default template selected is standard. We can see what events are included in the Standard template in the next tab, ‘Events Selection’.

The ‘Event selection’ screen contains a checkbox that can be configured as per the requirement of data to be collected. The same screen contains a ‘Column Filters’ button, which when clicked brings up another screen that can be used to further filter trace data.

There is another button named ‘Organize Columns’ on the same screen. This screen can be used to organize and group columns. Please note that columns cannot be grouped or ordered once the process of trace collection has begun.

The ‘Trace Properties’ screen contains a ‘Run’ button. When you click on this button, it runs the trace. The trace can be paused or stopped as required using the play menu on top.

This data can be saved in database and trace file or saved as XML. Trace file can be imported back in profiler, and profiler can re-run the whole scenario. Once the trace file is saved in database table, it can be queried by using T-SQL.

Summary

This article is a prologue to SQL Server Profiler. In the next article, I will talk about filtering and organizing columns. In addition, I will include storing trace file in the database table and querying table.

Please write to me at pinal@sqlauthority.com or contact me using form on my blog http://blog.sqlauthority.com/ if you have any doubt, big or small, regarding this article.

Reference : Pinal Dave (http://blog.SQLAuthority.com), DotNetSlackers

SQL SERVER – Introduction to SQL Server 2008 Profiler

Introduction

SQL Server Profiler is a powerful tool that is available with SQL Server since a long time; however, it has mostly been underutilized by DBAs. SQL Server Profiler can perform various significant functions such as tracing what is running under the SQL Server Engine’s hood, and finding out how queries are resolved internally and what scripts are running to accomplish any T-SQL command. The major functions this tool can perform have been listed below:

  • Creating trace
  • Watching trace
  • Storing trace
  • Replaying trace

Trace includes all the T-SQL scripts that run simultaneously on SQL Server. As trace contains all the T-SQL scripts running on SQL Server, it often tends to become considerably huge. Hence, it is always a good practice to capture only those data that are actually required for analysis.

Dilemma to use on Production Server

SQL Server Profiler has a lot of potential as a tool and offers several advantages. Unfortunately, developers have been keeping their hands off from this tool and usually give two major excuses.

  • Reason 1: Profiler adds too much overhead to production server.
  • Reason 2: Profiler accumulates plethora of data making it extremely intricate to analyze them afterwards.

Read Complete Article here.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Ahmedabad SQL Server User Group Meeting Review – March 21, 2009

We had fun session with Ahmedabad SQL Server Usre Group last week on March 21, 2009. It was short session but one interesting one. We discussed about how query profiler works and how to find most popular query from SQL Server instance. We had also prepared Trace Template as well query which can ran to identify longest running query along with popular query. I received nearly 10 questions after my session and lots of time was spent answering them. The whole session was very interactive.

I want to congratulate everybody who attended it, if you need my Profiler Template and Query to identify longest running query as well popular query, please let me know and I will send them to you.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Profiler – Adding Filters – Observation on CPU Load

Today I am blog about something which I found recently while working with SQL Server Profiler.

Profiler can be invoked just typing profiler in command prompt. I am using Windows Vista Ultimate 32 bit (License Version) and SQL Server 2008 Development (License Version). The reason I have put “License Version” because I encourage everybody to use only licensed software.

SQL Server Profiler gives feature where we can specify which column filter. Column filter can have value which can be validated with atucal data and based on it, it will store information in profiler stress.

I was always under impression that adding filter will reduce the load on CPU as profiler will have to do less amount of the work. I found that my assumption is wrong while doing testing with very large sample data and collecting very little information with column filter. In fact, column filter increase my CPU usage. After thinking it more I realize why this is happening. I am assuming that SQL Server Profiler has to collect all the necessary information in all the condition but in case of column filter, it will have to compare all the data with filter data, which can be the reason for higher CPU Load. For additional, testing I had removed few of the column filter and found that CPU load is reduced.This validates my theory that when there is filter, CPU has to work harder to filter the data. One good thing is that SQL Server does not store data which is removed, thus it reduces the storage space.

I am eager to know if SQLAuthority.com readers have ever faced situation like this. What is your opinion about it?

Reference : Pinal Dave (http://blog.SQLAuthority.com)