May 10, 2010 at 8:37 am
Robert Frasca (5/10/2010)
I tried adding the index on Entry_Sequence on the Patient_Info_Fields table but it didn't use it.
Index on (Entry_Sequence, Patient_Info_Field_ID)? Should have, the index looks covering, unless I missed something in the plan. No way SQL will use one just on Entry_Sequence.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 10, 2010 at 8:43 am
Try This as your view definition...
CREATE VIEW [PM].[vw_PatInfoData]
AS
SELECT
Patient_ID,
min(Case when Entry_Sequence= 1 then INFO1.Field_Value else Null end)AS Field1,
min(Case when Entry_Sequence= 2 then INFO1.Field_Value else Null end )AS Field2,
min(Case when Entry_Sequence= 3 then INFO1.Field_Value else Null end)AS Field3,
min(Case when Entry_Sequence= 4 then INFO1.Field_Value else Null end )AS Field4,
-- thru to
min(Case when Entry_Sequence= 24 then INFO1.Field_Value else Null end)AS Field24,
min(Case when Entry_Sequence= 25 then INFO1.Field_Value else Null end )AS Field25
FROM Patient_Info
group by Patient_ID
May 10, 2010 at 10:05 am
LOL! Doing three things at once and wasn't thinking.
INCLUDED Patient_Info_Field_ID and Field_Value and now it says it is using the index.
Unfortunately, it didn't really do anything performance wise as the query was working okay on my test box anyway. It went from 73 ms in duration to 58 but the reads and cpu remained virtually the same.
Unfortunately, I'm not sure that adding an index is an option since the problem is occurring on a client machine. I was hoping that someone might have some thoughts as to why it works fine on one database and slowly on another equivalent database on the same server.
"Beliefs" get in the way of learning.
May 10, 2010 at 12:32 pm
It's hard to say why you'd see to plans on the same machine, but I'd have to assume it probably had something to do with statistics or possibly ANSI settings on the database. That is a very big query.
Did you look at Dave's alternative?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 10, 2010 at 7:49 pm
I applied the covered index to the Patient_Info_Fields table as a test on the client's machine and it made the problem go away. I've recommended it as a low risk "hotfix" with possible positive performance implications for anything else that uses that obnoxious view. I've also asked for a backup of that client's database just to see if I can re-create the problem locally.
Thanks for everyone's help and I'll let you know what I learn.
BTW, this is one of the simpler queries in our application. There are queries that join 70 tables and views and some of those views also join 60 plus tables. I'm impressed as hell that they were able to get these queries to perform as well as they have, prior to my arrival, but now the volume of data in the client databases is reaching critical mass and what worked adequately, for example, with several tables of 100,000 rows is starting to stagger with several tables of a million plus rows. My next line of defense is a recommendation for Operational Data Store type structures to take the reporting load off the production databases until such time as some of this stuff can get re-written. I'm looking at transactional replication for that but that's a discussion for another thread.
"Beliefs" get in the way of learning.
May 11, 2010 at 5:42 am
Robert Frasca (5/10/2010)
I applied the covered index to the Patient_Info_Fields table as a test on the client's machine and it made the problem go away. I've recommended it as a low risk "hotfix" with possible positive performance implications for anything else that uses that obnoxious view. I've also asked for a backup of that client's database just to see if I can re-create the problem locally.Thanks for everyone's help and I'll let you know what I learn.
BTW, this is one of the simpler queries in our application. There are queries that join 70 tables and views and some of those views also join 60 plus tables. I'm impressed as hell that they were able to get these queries to perform as well as they have, prior to my arrival, but now the volume of data in the client databases is reaching critical mass and what worked adequately, for example, with several tables of 100,000 rows is starting to stagger with several tables of a million plus rows. My next line of defense is a recommendation for Operational Data Store type structures to take the reporting load off the production databases until such time as some of this stuff can get re-written. I'm looking at transactional replication for that but that's a discussion for another thread.
Something to keep an eye out for, we had a query that ran really, really fast, as it joined 86 tables. Unfortunately, while it ran really fast, it recompiled REALLY slowly. While recompiling of course, it's inaccessible to other calls. Because it's hitting 86 tables, the statistics were updating often enough that the table went into a recompile about once every 12-15 minutes. It recompiled for 3 minutes. So every quarter hour, our system would lock up for three minutes while all the access that needed that proc would hang... Not a good situation to be in. The fix was pretty simple, in concept. We rearchitected so that we were storing the data more efficiently and instead of hitting 86 tables, we were hitting less than 30.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 11, 2010 at 6:03 am
Maybe an indexed view could be a quick solution to those issues ?
Id still like to see how the view i gave effects things in this case.
May 11, 2010 at 7:36 am
Grant Fritchey (5/11/2010)
Robert Frasca (5/10/2010)
I applied the covered index to the Patient_Info_Fields table as a test on the client's machine and it made the problem go away. I've recommended it as a low risk "hotfix" with possible positive performance implications for anything else that uses that obnoxious view. I've also asked for a backup of that client's database just to see if I can re-create the problem locally.Thanks for everyone's help and I'll let you know what I learn.
BTW, this is one of the simpler queries in our application. There are queries that join 70 tables and views and some of those views also join 60 plus tables. I'm impressed as hell that they were able to get these queries to perform as well as they have, prior to my arrival, but now the volume of data in the client databases is reaching critical mass and what worked adequately, for example, with several tables of 100,000 rows is starting to stagger with several tables of a million plus rows. My next line of defense is a recommendation for Operational Data Store type structures to take the reporting load off the production databases until such time as some of this stuff can get re-written. I'm looking at transactional replication for that but that's a discussion for another thread.
Something to keep an eye out for, we had a query that ran really, really fast, as it joined 86 tables. Unfortunately, while it ran really fast, it recompiled REALLY slowly. While recompiling of course, it's inaccessible to other calls. Because it's hitting 86 tables, the statistics were updating often enough that the table went into a recompile about once every 12-15 minutes. It recompiled for 3 minutes. So every quarter hour, our system would lock up for three minutes while all the access that needed that proc would hang... Not a good situation to be in. The fix was pretty simple, in concept. We rearchitected so that we were storing the data more efficiently and instead of hitting 86 tables, we were hitting less than 30.
Hmmm. That may explain some other issues I'm seeing in our performance testing environment. Just out of curiosity, there's a Recompile counter in one of the SQL Server objects plugged into PerfMon, would you happen to know if that counts just the number of (re)compiles and, if so, is there a counter to look at for time spent recompiling? I don't remember seeing one but there are an awful lot of them so it's possible I missed it.
One of my complaints since I got here is that they (the development team) try to return tons of data with their queries. I was told they do that so they'll have it "in case they need it". That's why these queries JOIN so many tables. Another complaint is that they use VIEWS for implementing business rules instead of as a layer of abstraction. For example, instead of a Patient view to join demographic data, insurance data etc, they'll have a view of left-handed patients in their 30's, with three or more dependents, who've lost their insurance coverage in the last six months. A slight (very slight) exaggeration but you get the idea.
"Beliefs" get in the way of learning.
May 11, 2010 at 7:46 am
That counter is a cumulative count of recompiles. It's probably worth looking at in your situation. To see the affect of recompiles, I'd set up a server side trace and use the compile & recompile events to capture them. With that you can see the time spent there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 11, 2010 at 7:55 am
I'll do that the next time I run a trace. We capture all of the PerfMon stats for our performance tests so I have literally months of data to look at. I've looked at that counter a couple of times but didn't really spend any time on it.
Isn't there a WAIT type associated with recompiles?
"Beliefs" get in the way of learning.
May 11, 2010 at 8:05 am
Robert Frasca (5/11/2010)
I'll do that the next time I run a trace. We capture all of the PerfMon stats for our performance tests so I have literally months of data to look at. I've looked at that counter a couple of times but didn't really spend any time on it.Isn't there a WAIT type associated with recompiles?
Yeah, there is a wait type, but a couple of things that I hadn't thought of before. You can use the sys.dm_exec_query_stats to see how many times a proc was recompiled while it's in cache. You can also use sys.dm_exec_optimizer_info to see the cimpile times. For information on waits associated with recompiles, check here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply