Entries for the 'query optimizer' Category

Why do I have to use a MERGE\HASH JOIN Hint?

I have run into this several times lately so I thought I would whip out a quick post.

The scenario

A query runs much faster with a hash join hint than a nested loop but that is what the optimizer is choosing.

High level background

Merge and hash are good when you are working with lots of rows because it can leverage sequential IO. If you do a nested loop join with lookups on 100-1000+ rows, the random IOs can be 10-1000% more expensive than a sequential scan.

Why?

So why does the optimizer choose a nested loop? I have seen it is because the optimizer had to make a best guess at how make rows were being returned. It most cases, the optimizer guesses 1 row will be returned so a nested loop is the best choice as far as the optimizer can tell.

Identifying the problem 

Identifying the problem is pretty easy. All you need to do is look at the query plan. It can be text, xml or graphical. Look actual and estimated rows returned. If the actual rows are 11teen,000,000 but the estimated rows is 1 then this may be the problem

Causes

I most often see this when large table variables, local variables or modified stored procedure parameters are involved. There can be other causes. See my favorite white paper.

Fix?

Easy. Don’t use table variables and local variables. If you have to modify sp parameters, pass them to a nested stored proc. Make sure stats are up to date. If the WHERE cause is complex, try putting the logic in a persisted computed column or indexed view. Another option is to use a covering index. Of course, the last resort is the JOIN hint.

Need more?

Want to get real deep on stuff like this? Check out Craig Freedman’s blog or his Inside SQL Server 2005 chapters.

email it! |   |   | 

Permalink     4 Comments

5 Quick Tips for the Query using the “Wrong” Index

I quote “wrong” because with the info the optimizer has, it thinks it has the right index. :)

1. Look for cardinality problems. You can do this by looking at the actual rows versus estimated rows in the query plan. If they are off, chances are you are not following the best practices listed here.

2. Conversions in the WHERE clause can cause issues. Be sure to look at query plan for implicit conversions

3. Complicated logic in the WHERE clause can also cause problems. Consider indexed persisted computed columns or indexed views.

4. Constraints give the optimizer more info when choosing a plan with the “right” index. It can take a PK or unique constraint into consideration but it does not know about similar logic in a trigger.

5. Large datasets are being returned but the index is not covering. An index with just the columns in the WHERE clause is ok if just a few rows are returned but it does not take much for an index scan to be more efficient.

#1 is the most important one because the link gives 5 or 7 more reason why the “wrong” index is chosen.

P.S. If all else fails, update stats and if that does not work, make the index covering.

email it! |   |   | 

Permalink     1 Comments

NTSSUG June Presentation

Here are the slides and scripts from my June presentation at the North Texas SQL Server Users Group. The presentation was on the Data Collector in SQL Server 2008 but it was short so I also touched on filtered indexes, query hashes and troubleshooting. Enjoy and let me know if you have any questions.

Also Peter Debetta is presenting TONIGHT at 6:30 on the following topics:

"We'll be discussing the various ways you can secure your SQL Server data via encryption, including the new Transparent Data Encryption feature of SQL Server 2008."

 Stop by.

email it! |   |   | 

Permalink     0 Comments

CXPACKET, MAXDOP and your OLTP system

So you are experiencing CXPACKET wait types? If you run a google search, you will quickly find out you are experiencing the dreaded SQL Server parallelism problem and you must reduce MAXDOP to 1. While that is possible, I say most likely not. I say that, most of the time, SQL is doing the best it can with what it has to work with. That is the query, the data and the schema.

Microsoft has come a long way since version <= 7.0 when it comes to parallelism. If your OLTP queries follow best practices and are well indexed, they probably will never generate a parallel plan. This is because they are fast and access a small amount of rows. If they are missing indexes or SQL overestimates cardinality, SQL might decide to do scans, sorts, hashes, spools etc. These iterators, among others, can go parallel to reduce execution time at the cost of system resources. These iterators are not bad and they do have their place. It just isn’t on OLTP type of queries most of the time.

So dropping the MAXDOP on an OLTP system to 1 probably won’t hurt much because most of the time there is an IO bottleneck. If the CXPACKET wait types are a symptoms of poor indexing and row count estimation, it won’t help either. Whole books have been written on indexing, query tuning and there is a nice whitepaper on stats best practices to avoid estimation problems so I am not going to go into that. I will give you some pointers on quickly identifying whether the CXPACKET is a symptom or the cause.

So your boss and boss's boss run over to your desk yelling about the customer complaints of slowness with SQL. You run this query or something similar:

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

You notice that you have several rows with CXPACKET wait types. With this query, you click the xml show plan link and:
1.    search the xml for missing indexes.
2.    Save as a .sqlplan and reopen in SSMS
3.    Compare estimated and actual rows in the iterators on the right side of the plan. Poor estimates may bubble to the left as well.
4.    If there are no missing indexes, estimates and actual are fairly close, reducing MAXDOP may help if it is not a huge report or query.

5. If there are missing index or bad estimations, fix it! :)

Of course, these are not rules set in stone. Just a style in the art of database administration. Just don't blame it on a "parallelism bug" because it is a poor musician that blames his instrument.

One thing to note, if you do turn down MAXDOP server-wide, turn in on at the query level(enterprise edition) on your index operation because they are optimized for it.
Alter index all on tblBlah rebuild with (maxdop=32);

email it! |   |   | 

Permalink     3 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?

alter database dbtest01 set parameterization simple;

create proc proc1

@p1 int

as

select c1 from t1 

where c1 = @p1

--It does the CI scan

exec proc1 1990001