January 21, 2011 at 1:30 pm
The following statement has been causing some odd behavior. I can not modify it because it is in a peice of vendor code and they won't rework it.
What they did though was purge some records from table b and afterwards it the query executed under a second...But here is where it gets odd. This week the users where testing the screen and day 1 it was slow ~5 minutes day 2 it was fast ~1 second and day 3 it's back slow.
I have have tried every trick in the book and I can't recreate the ~1 second response without modifying the query. If the query is searched on a value that is indexed in table c it returns with a sub second response.
Any thoughts?
Query
select distinct b.* , insnum = 0
from b inner join c on b.accid = c.accid and b.acctyp = c.acctyp
where 1=1 and ltrim(rtrim(c.street)) + coalesce(', ' + ltrim(rtrim(c.cityzip)), '') = '123 E MAIN ST, CLEVELAND OH 44105-1234'
order by b.acctyp, b.accid;
b has 41,027,932 records
c has 1,199,164 records
c has 11 records when filterd on the where condifion
b has 310 records when filterd on the accounts that match the join condition.
I didn't capture any information when it was success. Honestly because I was amazed when the delete job on table b resolved the issue.
This is in a test system and the only change each day was that we run a process that adds information to these two tables. Maintenance plans are in place to do reindexing (Reorg / Rebuild) based on need.
January 21, 2011 at 1:32 pm
Do you have other things running at the same time that may be conflicting?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 21, 2011 at 2:49 pm
No when I was doing my T-SQL Test I was the only one on the system and nothing else was running at the time.
I have rebuild index and update statistics and I can't recreate the less than 10 second time.
Wed it as about 5 minutes Thur is was under 10 seconds and Fri it is back to 5 minutes.
January 21, 2011 at 3:07 pm
Can you post the execution plan?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 21, 2011 at 6:55 pm
CirquedeSQLeil (1/21/2011)
Can you post the execution plan?
that's what I was going to say. can't tell what the query is doing exactly w/o it. you can also fire sql profiler or run a perfmon on your disk I/O (Read Queue Length).
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
January 24, 2011 at 8:01 am
Attached is the exectuion plan when it is performing poorly. I don't have any information at this time for when it is execution well.
I have modified the results to remove the [database].[schema] from the results. Other than that nothing has been modified.
If we get a successful 10 second run of the same query I will attach it also.
January 25, 2011 at 8:53 am
Not sure why you are seeing variations in execution times. Is the data changing significantly on a daily basis? Perhaps a cached execution plan works great one day, then the data changes drastically, and it's no longer good? How often are you rebuilding the indexes? If you get a sub-10 second time, then cleared the caches with DBCC FREEPROCCACHE, then run it again, is it still fast? (Note: try this on a test system. Clearing the cache could cause your system to run slower for a while as all SPs will need to be recompiled when they run.)
SQLSoldier just put up a blog post that might be related to your problem. It might be a parameter sniffing issue. Check out http://www.sqlsoldier.com/wp/sqlserver/misunderstoodandabusedupdatestatisticscommand.
I think one of your issues is that your where clause is not sargable - note the index scan in the execution plan, not an index seek. But an option to fix this might be to create a computed column on table c that is defined as ltrim(rtrim(c.street)) + coalesce(', ' + ltrim(rtrim(c.cityzip)), ''). Then put an index on that computed column. See if that helps.
January 25, 2011 at 11:13 am
shaun.stuart (1/25/2011)
I think one of your issues is that your where clause is not sargable - note the index scan in the execution plan, not an index seek. But an option to fix this might be to create a computed column on table c that is defined as ltrim(rtrim(c.street)) + coalesce(', ' + ltrim(rtrim(c.cityzip)), ''). Then put an index on that computed column. See if that helps.
I agree that the expression isn't sargable, but adding the computed column won't help unless the query is changed to point to that column... and the OP states that the vendor isn't willing to change anything.
Does this procedure have a bunch of parameters, and you're only showing the resulting query out of it by building the actual query dynamically?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 25, 2011 at 1:13 pm
WayneS (1/25/2011)
I agree that the expression isn't sargable, but adding the computed column won't help unless the query is changed to point to that column... and the OP states that the vendor isn't willing to change anything.
Does this procedure have a bunch of parameters, and you're only showing the resulting query out of it by building the actual query dynamically?
That column was the specified where clause, so SQL should be able to use an index built on it. No change to the query needed. It would require modifying the table, not the query. OP didn't say that was forbidden :-). Anyway, it might not help. I don't have much experience with computed columns.
It might be that this query might have been built out of parameters that we aren't seeing, which I why I included the link to SQL Soldier's post. I also find the "Where 1=1" part strange. SQL ignores it, as evidenced by the execution plan, but it's strange it would be there. It almost makes me think the query is built dynamically or something.
January 26, 2011 at 8:41 am
Thanks guys. Yes I agree with some modifications we should be able to improve the performance. Since I'm not able to recreate what was being called when it was giving a response less than 10 seconds we are a little in the dark about what actually was called.
This statement is being dynamically generated. When it is called with a value that has an index we are getting the less than 10 second response.
The vendor is running a reindexing job currently on the system so we can see where things go from there.
I know when I rebuilt the index for the clustered index on the tables I was able to get the time down to a little over 3 minutes. This morning it was back up to a little under 4 minutes when I ran the statement. That's before we started the reindexing job.
Thanks again.
January 26, 2011 at 8:54 am
I'm still not clear on what you mean when you say you "When it is called with a value that has an index..." Indexes are defined on columns, not values. If a column has an index, then all values in that column are indexed.
Given that the query is dynamically generated, I think it's highly likely you are experiencing a parametrization issue. Read the blog post from SQLSoldier that I posted above. This will likely be the cause of your problem. I would also confirm with your vendor that they are doing an index rebuild and not just an index defrag. (Or you can verify for yourself by running a trace and seeing if they are issuing a DBCC DBREINDEX or DBCC INDEXDEFRAG command.) A rebuild will sample 100% of the data in the index whereas a defrag will only sample some subset of the data. Therefore, a defrag may not help with a parametrization issue.
January 26, 2011 at 10:03 am
Assuming there is an index on street and cityzip fields on c, it would seem that an index seek and bookmark lookup would be the "really fast" plan. You could use a plan guide to force that particular query plan for this type of query. I think you said it is third-party, so you probably don't have access to modify the code but if you did you could also use index (and join - as in loop join) hints there. Please beware that forcing stuff CAN have unintended consequences for performance if query parameters are unexpected.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 26, 2011 at 10:04 am
It looks like you posted the estimated execution plan.
Would it be possible to post the actual execution plan?
Also, would you please post the actual execution of a query that performs better?
Based on an actual execution plan you could check if statistics are useful or way off.
A table scan of 41mill rows hash matched to a table scan with 36000 rows may take a while though...
January 26, 2011 at 2:19 pm
LutzM (1/26/2011)
It looks like you posted the estimated execution plan.Would it be possible to post the actual execution plan?
Also, would you please post the actual execution of a query that performs better?
Based on an actual execution plan you could check if statistics are useful or way off.
A table scan of 41mill rows hash matched to a table scan with 36000 rows may take a while though...
Will statistics matter with the functions wrapped around the columns like they are?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 26, 2011 at 2:28 pm
TheSQLGuru (1/26/2011)
LutzM (1/26/2011)
It looks like you posted the estimated execution plan.Would it be possible to post the actual execution plan?
Also, would you please post the actual execution of a query that performs better?
Based on an actual execution plan you could check if statistics are useful or way off.
A table scan of 41mill rows hash matched to a table scan with 36000 rows may take a while though...
Will statistics matter with the functions wrapped around the columns like they are?
Not for the WHERE part, I agree. But I'd expect it to be helpful to join the two tables:
from b inner join c on b.accid = c.accid and b.acctyp = c.acctyp
Aint gonna mess with a guru tho... 😉
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply