Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

What is the performance bottleneck here? Expand / Collapse
Author
Message
Posted Monday, August 6, 2012 9:23 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 5:46 AM
Points: 3,998, Visits: 7,172
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


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1340982
Posted Tuesday, August 7, 2012 12:15 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:53 AM
Points: 3,422, Visits: 5,368
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!
Post #1341024
Posted Tuesday, August 7, 2012 12:36 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:26 PM
Points: 316, Visits: 1,497
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).











Post #1341034
Posted Tuesday, August 7, 2012 5:58 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 5:46 AM
Points: 3,998, Visits: 7,172
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 :)

All jokes aside ;) 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"
Post #1341177
Posted Tuesday, August 7, 2012 6:00 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 5:46 AM
Points: 3,998, Visits: 7,172
@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"
Post #1341179
Posted Tuesday, August 7, 2012 8:49 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 14, 2014 9:53 PM
Points: 75, Visits: 444
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
Post #1341325
Posted Tuesday, August 7, 2012 1:17 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
MyDoggieJessie (8/7/2012)
[quote]
All jokes aside ;) 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
Post #1341497
Posted Tuesday, August 7, 2012 6:15 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:53 AM
Points: 3,422, Visits: 5,368
mtassin (8/7/2012)
MyDoggieJessie (8/7/2012)
[quote]
All jokes aside ;) 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!
Post #1341632
Posted Tuesday, August 7, 2012 6:47 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 5:46 AM
Points: 3,998, Visits: 7,172
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"
Post #1341637
Posted Tuesday, August 7, 2012 6:59 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 4:53 AM
Points: 3,422, Visits: 5,368
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!
Post #1341639
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse