Entries for the 'Procedure Cache' Category

This Post Needs More Stored Procedures

New in SQL Server 2008 is a server setting called “Optimize for adhoc workloads”. I was happy to see this. You should be too especially if you have ever had arm wrestle an app that causes a bloated proc cache on an x86 box. Ugghh… Adam blogged on it here, Bob blogged on it here and here is the documentation.

I was wondering how this setting would play with the forced parameterization database setting. It looks like forced parameterization trumps the new server wide setting. If my simple tests below are right, it could present an interesting problem. What if your app generates a lot of totally unique adhoc queries that have no benefit from parameterization and you have queries that would benefit from parameterization? Well, the answer is still the same. Write stored procedures. :)

In the real world, sometimes you get a pig and all you can do is put lipstick on it. If you have to choose, “Optimize for Adhoc” would most benefit memory\IO bound servers while “forced parameterization” could help both the CPU and memory usage. However, that is a very general statement. Other things should be considered so it will depend.

--So what happens when you turn on "Optimize for adhoc" and forced parameterization 
exec sp_configure  'show advanced options', 1
reconfigure with override
go
exec sp_configure 'optimize', 1
reconfigure with override 
alter database master set parameterization forced
dbcc freeproccache 

--Let's run a query will not generate a trivial plan.
select name, object_id, create_date
from sys.all_objects
where object_id = 3 and create_date = '2008-07-09 16:19:59.943'
go

--The CacheObjType is a compiled plan and 57344 bytes
select p.cacheobjtype, p.size_in_bytes ,  s.*
from sys.dm_exec_query_stats s
join sys.dm_exec_cached_plans p
on s.plan_handle = p.plan_handle
cross apply sys.dm_exec_sql_text(sql_handle)
where text like '%select name , object_id , create_date from%'

--Now, let’s turn off forced parameterization

alter database master set parameterization simple
dbcc freeproccache

select name, object_id, create_date
from sys.all_objects
where object_id = 3 and create_date = '2008-07-09 16:19:59.943'
go 

--The CacheObjType is a Compiled Plan Stub and 320 bytes
select p.cacheobjtype, p.size_in_bytes ,  s.*
from sys.dm_exec_query_stats s
join sys.dm_exec_cached_plans p
on s.plan_handle = p.plan_handle
cross apply sys.dm_exec_sql_text(sql_handle)
where text like '%select name , object_id , create_date from%'

email it! |   |   | 

Permalink     4 Comments

The Perils of SQL Profiler

I posted the first Captain VarChar(MAX) comic last Friday. It takes a swing at profiler. A lot of you are like “Duh!”. I have just done too many dba interviews where the candidate’s default answer is profiler. The interview usually gets tough or really short at that point. Just say no to SQL profiler.

I really have to stretch my imagination to come up with a situation where using SQL Profiler sounds like a good idea. For example, you quickly want to capture “ordered” events when repro’ing a problem in your *test* environment AND you filter it by your SPID. Any other time, you are wasting lots of time, cause performances overhead and probably missing what you are looking for while digging for the needle in the haystack. Also contrary to popular belief, running the GUI on another machine DOES NOT help much. For the rest of the post when I refer to a trace, I am referring to one captured with a server side trace unless I say “profiler” or “GUI”

I will admit that I have brought a production app to its knees with profiler. Picture this. You get a call while you are running profiler that the app is having issues. At first you think, “Hey, I have a trace running so at least I will be able to pinpoint the problem.” Then you see TRACE_WRITE wait types. Your stomach knots up because you realize that you just caused a production outage. Don’t be that guy(or gal).

SQL 2005+ just gives you too much info through the DMV’s. 90%+ of the time you can use the DMV’s instead of Profiler. The other 10% of the time, use a server side trace not the Profiler GUI. I have never seen a server side trace bring down an app. Even unfiltered traces on highly transactional systems where a gigabyte+ is collected every minute. I can see it causing an issue but the server would have to be pretty max’ed out to begin with.

Lastly, a trace only reports CPU, Reads, Duration etc when the query completes. Query completion for a misbehaving query could take 10x to 100x longer than normal. This makes traces unusable in a lot of cases when troubleshooting.

Let’s look at some scenario’s:

Troubleshooting a performance issue that is currently happening

A SQL trace in this situation is almost never needed on SQL 2005+. I troubleshoot like this:

1) See if\what hardware subsystem is pegged out with about 5 or 6 perfmon counters.

a) Verify with DMV data or wait stats if needed.

2) Identify the problematic queries with the sys.dm_exec_% DMV's

3) Fix.

Here is the query I use. In a nutshell, it grabs a snapshot, waits a second and does a diff so I can see what is currently slamming the server. Otherwise, the cumulative CPU and IO columns might throw me off. I also grab the query plan which is easy here but very expensive in a trace and hard to correlate with the SQL completed event.

select r.cpu_time 

, r.logical_reads

, r.session_id 

into #temp

from sys.dm_exec_sessions as s 

inner join sys.dm_exec_requests as r 

on s.session_id =r.session_id --and s.last_request_start_time=r.start_time

where is_user_process = 1 

waitfor delay '00:00:01'

select 

substring(h.text, (r.statement_start_offset/2)+1 , 
((case r.statement_end_offset when -1 then datalength(h.text)  

else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as text

, r.cpu_time-t.cpu_time as CPUDiff 

, r.logical_reads-t.logical_reads as ReadDiff

, p.query_plan

, r.wait_type

, r.wait_time

, r.last_wait_type

, r.wait_resource

, r.command

, r.database_id

, r.blocking_session_id

, r.granted_query_memory

, r.session_id

, r.reads

, r.writes

, r.row_count

, s.[host_name]

, s.program_name

, s.login_name

from sys.dm_exec_sessions as s 

inner join sys.dm_exec_requests as r 

on s.session_id =r.session_id and s.last_request_start_time=r.start_time

full outer join #temp as t on t.session_id=s.session_id

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h

cross apply sys.dm_exec_query_plan(r.plan_handle) p

order by 3 desc

drop table #temp

Proactive performance tuning

Now this starts to get in to fuzzy territory. Technically, a SQL trace is more thorough than doing your analysis off of the procedure cache. However, I would say benefits outweigh the losses in most but not all cases.

Procedure Cache Pro’s

· No need to capture a trace

· No need to load a trace

· Aggregation already exists so the data can be smaller assuming that the application has good plan reuse.

· Easy access to XML Showplan

Procedure Cache Con’s

· Queries can be flushed until next execution for many reasons

· Some queries are not cached. These are usually queries that you don’t care about.

To replace SQL trace analysis with a DMV query, use something like this:

select total_worker_time/execution_count as AvgCPU  
, total_elapsed_time/execution_count as AvgDuration  
, (total_logical_reads+total_physical_reads)/execution_count as AvgReads
, execution_count   
, substring(st.text, (qs.statement_start_offset/2)+1 , 

((case qs.statement_end_offset when -1 then datalength(st.text) 

else qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as txt  
, query_planfrom sys.dm_exec_query_stats as qscross apply sys.dm_exec_sql_text(qs.sql_handle) as stcross apply sys.dm_exec_query_plan (qs.plan_handle) as qp
--where – filter as needed.
--group by – Consider striping literals and grouping by the SQL text. 

--See Inside SQL Server 2005 for more info.
--Order by – Sort as needed.

Uncommon cases

There are some less common uses of SQL traces like the blocked process report, deadlock report, recompile tracing and many others. Make sure you review all of your options and don’t forget about the WMI Provider for Server Events. Use your best judgment and avoid the GUI like the plague.

Conclusion

To summarize very briefly: Never use the SQL Profiler GUI, know your tools, and pick the best one for the job.

email it! |   |   | 

Permalink     8 Comments

TechEd! Let's do this.

I am pumped up about TechEd. Bill Gates's last technical keynote. He has to have a bomb to drop... The launch of Windows\VS\SQL Server 2008. All of the speakers are reaching deep into their bag of tricks and bringing new hardcore sessions. The universal party. The blogger's lounge. Twitter, FriendFeed, MSDN, and Technet are all buzzing with posts.�

I found out a couple of days ago that a speaker would not be able to make it due to a family emergency. I offered to present his session on DMV's so I will be presenting these sessions:

Using Dynamic Management Views to Improve Your Development

Dynamic Management Views were added to SQL Server 2005 and have been enhanced in SQL Server 2008. While they provide great functionality and usefulness, it appears they are not widely understood or implemented as yet. This session provides an overview of the Dynamic Management Objects available (both Views and Functions) and describes those considered the most useful. It shows how they can be utilized in monitoring, troubleshooting, and ongoing management of SQL Server systems and in gaining an understanding of SQL Server’s operation. The session also illustrates how they can be used to improve the database development experience and how they can be used in custom reports within SQL Server Management Studio.

Database Platform
300
BRK
Jason Massie

�

Windows Management Instrumentation (WMI) for the Command Line DBA

This is a demo filled session on how a command line DBA can unleash the power of WMI. WMI allows the DBA access to information that was previously hard to get or unavailable like the application event logs, OS perfmon counters, and OS events. This session covers WQL queries from SSIS, accessing OS performance counters from SQL, automated reactions to WMI events, WMI from Windows PowerShell and much more.

Database Platform
400
BRK
Jason Massie

�

Troubleshooting Query Plans Gone Wrong

Have you ever flushed the procedure cache to resolve a performance problem? Have you ever had to use a join or index hint when you should not have to? Have you ever updated statistics to fix a performance problem and wondered why it worked? In this session, we get deep into how the query optimizer decides how to execute a SQL statement. We look at common problems that cause the optimizer to choose the wrong access paths like underestimation. We go over a detailed demo on parameter sniffing, problems with local variables and statistics. We also look at solutions to these problems in several areas including good design at the application, schema, and query levels, proactive maintenance and reactive fixes. This session applies to Microsoft SQL Server 2000 through 2008 but we pay special attention to new features in SQL Server 2008 like plan freezing, new hints, filter indexes, and statistics among others.

Database Platform
400
BRK
Jason Massie

I'll be there Monday through Saturday. Hope to see you there!

email it! |   |   | 

Permalink     2 Comments

RE: Filtered indexes not compatible plan reuse

Before the original post, I had submitted a connect item for something similar.

It turns out that filtered indexes may not be used when auto-parameterization(or for stored proc) occurs without a recompile hint. This is sort of understandable but I can think of workarounds and I suspect additional logic will be added in the future as mentioned below. Here is the feedback from a connect item I submitted.

"

Thanks for your feedback. The query in question, select lastname from Person.Contact where ContactID <=(100-20) is auto-parameterized by SQL Server into the following form, using the standard auto-parameterization rules: select lastname from Person.Contact where ContactID <=(@1-@2) These rules are designed to minimize compile time cost for simple queries like this, possibly at the expense of additional optimizations such as using a filtered index. Admittedly, it is a difficult tradeoff. In a future release, we'll consider extending the design to make a better decision in a cost-based way. For now, this behavior is by design.

"

alt head: You can't have your cake and eat it too.

email it! |   |   | 

Permalink     1 Comments

Filtered indexes not compatible plan reuse??

So I was messing around with filtered indexes tonight. Yes, beta software is my idea of a wild Saturday night. Hey, I played some Smash Bros first. ;) I was trying to figure out what happens if a plan is created using a covering but filtered index and try to reuse it with a parameter that is covered vertically but not horizontally. Let's look at this example: 

create database dbtest01;

--Force parameterization

alter database dbtest01 set parameterization forced;

use dbtest01

--create test table 1

create table t1

(id int identity(1,1),

c1 int,

c2 int)

--insert dummy data

declare @ctr int = 2000000

while @ctr > 0

begin

insert into t1(c1, c2) values (@ctr, @ctr)

select @ctr=@ctr-1

end

--create clustered indexes

create clustered index CI on t1(id)

--Let create a covering indexes that is pretty extreme.

create index ix01 on t1(c1) where c1 = 1990001

--Test queries

--ctrl + m

dbcc freeproccache

set statistics io on

--Does a CI scan.

select c1 from t1 

where c1 = 1990001

--Nope, index hints returns an error

--Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

select c1 from t1  with (index=ix01)

where c1 = 1990001

--Ahh, recompile works. Now it uses the filtered index

select c1 from t1 

where c1 = 1990001

option(recompile)

--What about simple parameterization and a stored proc?