SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What is the performance bottleneck here?


What is the performance bottleneck here?

Author
Message
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6692 Visits: 7394
DISCLAIMER I did not write this code

Assume that #InstitutionChild is just a temp lookup table filed with about 95,000 int values
Ex: Create Table #InstitutionChild (ChildAID int)

Given the following code
SELECT
@DailyCnt = COUNT(tranid)
FROM
COREACQUIRE.dbo.AcqLogARTxnsAdd l WITH (NOLOCK)
LEFT OUTER JOIN COREACQUIRE.dbo.trans_in_acct t WITH (NOLOCK, INDEX (idx_tran_id_index))
ON l.tranid = t.tran_id_index
WHERE
t.atid = 39
AND l.artxntype IN ('91', '95')
AND l.ARTxnBusinessDate <= '1/1/2011'--@DateFrom
AND InstitutionId IN
(SELECT childaid FROM #InstitutionChild)
AND l.TransactionAmount <> 0
AND ISNULL(l.ExcludeFlag, '0') = '0'

When viewing the execution plan (image snippet attached - full plan has over 50 queries and I'm just interested in this portion of code) I see something I don't understand :
[COREACQUIRE].[dbo].[trans_in_acct].[ATID] as [t].[ATID]=(39) AND PROBE([Bitmap1015],[COREACQUIRE].[dbo].[trans_in_acct].[tran_id_index] as [t].[tran_id_index])

What on earth is this PROBE([Bitmap1015])?

I quickly "googled" it but really didn't come up with an explanation that made much sense to me. Can someone please shed some light?

This portion of the query is accounting for the majority of the reports execution time...and I'd like to get past this one w00t

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7347 Visits: 6431
I can't help you PROBE your BITMAP (sorry but I couldn't resist ;-)), but my first suggestion would be to remove the INDEX hint and see what that does to performance.

It could be the underlying data has changed since that hint was applied and SQL Server may be able to come up with a better plan if you remove the hint.

Make sure you take an accurate timing of the query with and without the HINT, and allow several runs to give SQL a chance to cache a new execution plan.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
SpringTownDBA
SpringTownDBA
Mr or Mrs. 500
Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)Mr or Mrs. 500 (580 reputation)

Group: General Forum Members
Points: 580 Visits: 1499
Your query is using bitmap filters.

a bitmap filter is a way for sql server to eliminate rows earlier in the execution pipeline. This is a powerful performance optimization.

Bitmap filters will always show up in two places in your query plan, once for the build, once for the probe.

The most common case will include a hash join. On the build side of the hash join, you will find the build for the bitmap filter (just like a hash join, the bitmap filter must consume all imput before the probe side can start).

consider this contrived and grossly oversimplified example:

table cats (cat_id int...)
table books (book_id, cat_id ...)

select books.* from books b inner join cats c
on b.cat_id = c.cat_id where c.cat_id in (1,33,51,79,91)



Sql server may decide to scan the cats table, and build a bitmap filter.
Through SQL Server magic, the bitmap filter realizes that all the
values of cat_id are odd. When Sql server does a scan on the books
table, it tests each cat_id against the filter. Any values of cat_id that
are even numbers can't possibly match when we get to the hash join,
so we eliminate those rows before they even make it to the query pipeline.

bitmap filters are a good thing, but typically only show up in plans with large row counts (DW queries, etc).
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6692 Visits: 7394
dwain.c (8/7/2012)
I can't help you PROBE your BITMAP (sorry but I couldn't resist ;-)), but my first suggestion would be to remove the INDEX hint and see what that does to performance.

It could be the underlying data has changed since that hint was applied and SQL Server may be able to come up with a better plan if you remove the hint.

Make sure you take an accurate timing of the query with and without the HINT, and allow several runs to give SQL a chance to cache a new execution plan.
LOL - yeah I chuckled when I just typed it ino the OP Smile

All jokes aside Wink when removing the index hint the optimizer automatically turns it into an index scan...so that's why I was trying to force it to use the index I had created specifically for it.

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6692 Visits: 7394
@SpringTown :: Thanks for the explanation, much appreciated!

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
SomewhereSomehow
SomewhereSomehow
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 469
It will be much more helpful, if you provide an Actual Execution Plan (in xml) for this slow query. If this is not a slow query by it self, but you think it is the slow part of more complex query, than it would be nice if you provide the actual query plan for all the complex query (including slow part), and a plan for it, without slow part (when you comment slow part, for example).


I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
Blog: http://somewheresomehow.ru
Twitter: @SomewereSomehow
mtassin
mtassin
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4828 Visits: 72518
MyDoggieJessie (8/7/2012)
[quote]
All jokes aside Wink when removing the index hint the optimizer automatically turns it into an index scan...so that's why I was trying to force it to use the index I had created specifically for it.


That's not always a bad thing... an Index Seek that involves Bookmark/RID lookups is generally worse than an index scan that doesn't.

Just as one example.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7347 Visits: 6431
mtassin (8/7/2012)
MyDoggieJessie (8/7/2012)
[quote]
All jokes aside Wink when removing the index hint the optimizer automatically turns it into an index scan...so that's why I was trying to force it to use the index I had created specifically for it.


That's not always a bad thing... an Index Seek that involves Bookmark/RID lookups is generally worse than an index scan that doesn't.

Just as one example.


This is kinda why I suggested he run a timing test with and without the INDEX hint.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
MyDoggieJessie
MyDoggieJessie
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6692 Visits: 7394
Tried those suggestions and really didn't see any performance gain...have a few other things to try but ended up getting too busy to look at it today!

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
dwain.c
dwain.c
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7347 Visits: 6431
I'll make two other suggestions then, but for the first I must ask a question:


AND l.ARTxnBusinessDate <= '1/1/2011'--@DateFrom



1. Are you actualy comparing against a hard-coded date or a parameter you passed in named @DateFrom? If the latter, try adding OPTION (RECOMPILE) to the query and time the result.


AND InstitutionId IN
(SELECT childaid FROM #InstitutionChild)



2. Can you make this an INNER JOIN #InstitutionChild ON InstitutionID = childaid without impacting the row count?


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search