March 8, 2010 at 9:37 am
For months we have experienced intermittent problems with a function somewhere in the DB going “out of whack” by performing very poorly the next production day after our job that updates stats and reruns indexes completes. Most tables/functions work fine (and even other functions/procs that work against the same base tables as the poor performing function), but we’ll see one function drag about 30% of the time after updating the stats.
The issue will reoccur on the same function multiple times. Eventually we’ll (out of desperation) re-code the function and the poor performance has, thus far, gone away. However, give it a few weeks and a different function will pop onto the radar consistently.
To aggravate the symptoms, new connections to the database do not experience the problem. The production application connects to the DB using a SQL account and utilizes connection pooling, so the account remains active. However, if I connect via SSMS using the production account’s credentials (or my own) and use the function, it works fine.
The workaround is to force a recompile of the function. This leads me to think that the query plan from before the stats run is still cached for the application connection. I don’t understand why that would happen.
So, I don’t understand why the one of the 200 or so functions seems to not recompile with the stats update for a single connection.
Thoughts anyone?
March 8, 2010 at 9:42 am
Ben have you ruled out parameter sniffing?
if your procedures have default values for some of the parameters, no matter whether you updated statistics or not, parameter sniffing could occur, where a "bad" execution plan is created, because the compiler assuming the "defaults" are the correct/best values for finding values, whent hey are actually being used as placeholders;
when real world values get passed in, the procs execution plan is way off, and results in bad performance.
there's lots to read about it hear on SSC, as well as the work around to avoid it in the first place(replacing parameters with local variables inside the proc)
Lowell
March 8, 2010 at 9:44 am
A stats update invalidates all plans that use those statistics. An index rebuild does much the same. That means the plans will be recompiled on the next execution.
It does sound like some form of parameter sniffing where what's an optimal plan for some executions is not an optimal plan for all executions. Can you post the function and, if possible, a good and bad execution plan for it?
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
March 8, 2010 at 11:39 am
The code is (currently):
create view dbo.vwSystemDateTime
as
select getdate () SystemDateTime
go
CREATE FUNCTION [dbo].[BedIsAssigned]
(
@BedID int
)
RETURNS bit
AS
BEGIN
DECLARE @Result bit
IF EXISTS(SELECT Organization_Bed_Assignment_ID
FROM Cimor.dbo.Organization_Bed_Assignment
WHERE Organization_Bed_ID = @BedID
AND (Release_Date_Time IS NULL OR Release_Date_Time>(select SystemDateTime from vwSystemDateTime))
)
BEGIN
SET @Result = 1
END
ELSE
BEGIN
SET @Result = 0
END
RETURN @Result
END
Quick disclaimer, I'd love to tune this but am presenting it to you w/warts in case the wart is the problem)
Ben
March 8, 2010 at 11:41 am
We do not have before/after query plans at this time.
March 8, 2010 at 12:26 pm
Could you post table definitions and some sample data (not much) I just want to see how it runs.
In the meantime...
Scalar User-defined functions, especially data-accessing user defined functions, are in general performance nightmares. SQL won't inline them, it'll run the function once for each row of the outer query and, to make matters worse, won't show you the cost or IOs in the execution plan/Statistics IO
http://sqlinthewild.co.za/index.php/2009/04/29/functions-io-statistics-and-the-execution-plan/
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
March 8, 2010 at 12:38 pm
It may sound crazy, but I think that your function would work better as an in-line TVF.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 8, 2010 at 12:59 pm
It very likely will.
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
March 8, 2010 at 1:22 pm
It’s not an easy feat to post sample data. The type of data involved is regulated and there are serious privacy issues involved, so I could only post data after a lot of review by management.
Following is the schema of the Organization_Bed_Assignment table which holds 70k of records
CREATE TABLE [dbo].[Organization_Bed_Assignment](
[Organization_Bed_Assignment_ID] [int] NOT NULL,
[Organization_Bed_ID] [int] NOT NULL,
[Episode_of_Care_ID] [int] NOT NULL,
[Assignment_Date_Time] [datetime] NOT NULL,
[Release_Date_Time] [datetime] NULL,
[Assignment_Processing_Indicator] [bit] NOT NULL,
[Comment] [varchar](1024) NULL,
[Last_Processed_Period] [char](8) NULL,
[Boarding_Indicator] [bit] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[CreatedBy] [varchar](32) NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[ModifiedBy] [varchar](32) NOT NULL,
CONSTRAINT [PK__Organization_Bed__77AB884F] PRIMARY KEY CLUSTERED
(
[Organization_Bed_Assignment_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Organization_Bed_Assignment] WITH NOCHECK ADD CONSTRAINT [FK_Episode_of_care__Organization_Bed_Assignment] FOREIGN KEY([Episode_of_Care_ID])
REFERENCES [dbo].[Episode_of_care] ([Episode_of_care_ID])
GO
ALTER TABLE [dbo].[Organization_Bed_Assignment] CHECK CONSTRAINT [FK_Episode_of_care__Organization_Bed_Assignment]
GO
ALTER TABLE [dbo].[Organization_Bed_Assignment] WITH NOCHECK ADD CONSTRAINT [FK_Organization_Bed__Organization_Bed_Assignment] FOREIGN KEY([Organization_Bed_ID])
REFERENCES [dbo].[Organization_Bed] ([Organization_Bed_ID])
GO
ALTER TABLE [dbo].[Organization_Bed_Assignment] CHECK CONSTRAINT [FK_Organization_Bed__Organization_Bed_Assignment]
GO
ALTER TABLE [dbo].[Organization_Bed_Assignment] ADD CONSTRAINT [DF__Organizat__Assig__789FAC88] DEFAULT (convert(char(8),getdate(),112)) FOR [Assignment_Date_Time]
GO
ALTER TABLE [dbo].[Organization_Bed_Assignment] ADD CONSTRAINT [DF_Organization_Bed_Assignment_Assignment_Processing_Indicator] DEFAULT (0) FOR [Assignment_Processing_Indicator]
GO
ALTER TABLE [dbo].[Organization_Bed_Assignment] ADD CONSTRAINT [DF_Organization_Bed_Assignment_Boarding_Indicator] DEFAULT (0) FOR [Boarding_Indicator]
GO
ALTER TABLE [dbo].[Organization_Bed_Assignment] ADD CONSTRAINT [DF__Organizat__Creat__7993D0C1] DEFAULT (getdate()) FOR [CreatedDate]
GO
ALTER TABLE [dbo].[Organization_Bed_Assignment] ADD CONSTRAINT [DF__Organizat__Creat__7A87F4FA] DEFAULT (convert(varchar(32),user_name())) FOR [CreatedBy]
GO
ALTER TABLE [dbo].[Organization_Bed_Assignment] ADD CONSTRAINT [DF__Organizat__Modif__7B7C1933] DEFAULT (getdate()) FOR [ModifiedDate]
GO
ALTER TABLE [dbo].[Organization_Bed_Assignment] ADD CONSTRAINT [DF__Organizat__Modif__7C703D6C] DEFAULT (convert(varchar(32),user_name())) FOR [ModifiedBy]
GO
A sample of a procedure that calls the function is:
SELECT DISTINCT
a.Organization_Bed_IDAS OrganizationBedID,
a.Bed_NameAS BedName,
c.Organization_Link_IDAS OrganizationID,
OD.Organization_NameAS SiteUnitName,
c.Organization_Ward_IDAS OrganizationWardID,
c.Organization_Ward_NameAS WardName,
b.Organization_Room_IDAS OrganizationRoomID,
b.Organization_Room_NameAS RoomName,
d1.Organization_Bed_Assignment_ID AS OrganizationBedAssignmentID,
f.Consumer_Data_IDAS ConsumerID,
dbo.GetConsumerLastFirstMIName(f.Consumer_Data_ID) AS ConsumerName,
dbo.BedIsAssigned(a.Organization_Bed_ID) AS IsAssigned,
d1.Assignment_Date_TimeAS AssignmentDateTime,
d1.Release_Date_TimeAS ReleaseDateTime,
CASE
WHENISNULL(a.To_Date, '99999999') > @CurrentDateISO --BED
AND ISNULL(b.To_Date, '99999999') > @CurrentDateISO --ROOM
AND ISNULL(c.To_Date, '99999999') > @CurrentDateISO --WARD
THEN i.CD_Organization_Bed_Status_Value
ELSE 'Closed'
ENDAS CurrentBedStatus
FROM Organization_Bed a
INNER JOIN Organization_Room b
ON a.Organization_Room_ID = b.Organization_Room_ID
INNER JOIN Organization_Ward c
ON b.Organization_Ward_ID = c.Organization_Ward_ID
LEFT JOIN dbo.Organization_Bed_Assignment d
ON d.Organization_Bed_ID = a.Organization_Bed_ID
AND Assignment_Date_Time = (SELECT MAX(Assignment_Date_Time) FROM dbo.Organization_Bed_Assignment
WHERE Organization_Bed_ID = a.Organization_Bed_ID)
LEFT JOIN Organization_Bed_Assignment d1
ON d1.Organization_Bed_Assignment_ID = (CASE WHEN dbo.BedIsAssigned(a.Organization_Bed_ID) = 0 THEN NULL ELSE d.Organization_Bed_Assignment_ID END)
INNER JOIN dbo.Organization_Link AS OL ON
OL.Organization_Link_ID = c.Organization_Link_ID
INNER JOIN dbo.Organization_Data AS OD ON
OD.Organization_Data_ID = OL.Organization_Data_ID
LEFT JOIN Episode_of_Care f
ON d1.Episode_of_Care_ID = f.Episode_of_Care_ID
LEFT JOIN CD_Gender h
ON b.CD_Gender_ID = h.CD_Gender_ID
LEFT JOIN CD_Organization_Bed_Status i
ON CD_Organization_Bed_Status_ID = dbo.GetOrganizationBedStatusID(a.Organization_Bed_ID)
LEFT JOIN dbo.Consumer_MV AS cmv ON f.consumer_Data_ID = cmv.consumer_Data_ID
WHERE
c.Organization_Link_ID IN (SELECT DISTINCT x FROM #t)
AND
(
(@ShowUnassignedOnly = 0)
OR
dbo.BedIsAssigned(a.Organization_Bed_ID) = 0
)
AND(@SiteUnitID IS NULLOR c.Organization_Link_ID = @SiteUnitID)
AND(@WardID IS NULLOR c.Organization_Ward_ID = @WardID)
AND(@LastName IS NULLOR cmv.Last_Name LIKE @LastName OR @Last_NYSIIS = cmv.Last_Name_NYSIIS)
AND(@FirstName IS NULLOR cmv.First_Name LIKE @FirstName OR @First_NYSIIS = First_Name_NYSIIS)
AND(@MiddleName IS NULL OR cmv.Middle_Name LIKE @MiddleName)
AND(@AssignmentRangeFromDate IS NULL OR @AssignmentRangeFromDate >= a.From_Date)
AND(@AssignmentRangeToDate IS NULL OR ISNULL(a.To_Date, '99999999') >= @AssignmentRangeToDate)
ORDER BY OD.Organization_Name, c.Organization_Ward_Name,
b.Organization_Room_Name, a.Bed_Name
Yes, it’s ugly code. Welcome to my current world. Yes, they are calling the function in the SELECT, JOIN, and WHERE clauses. Yes, this could definitely stand to be re-written. Bear in mind that this does perform reasonably well most of the time, just not directly after a stats run, and we have isolated the function call itself as the bottleneck.
Also remember that this is not the first function to exhibit this type of behavior. In fact, it’s about the 5th. The functions come from different areas of the system that work with vastly different data (this is about bed assignments, others have been in app’s security (does the user have rights to a menu option), invoicing (calculating the total line amount on an invoice), etc. The functions have varying approaches, different coders/styles, and differing input parameters and return types.
The common thread is that they run fine most of the time until a stats update sweep. Then they drag until they are recompiled.
I agree with you about the functions being a performance bottle-neck and have even blogged to that point. I am part of the “clean-up” team that is tuning a system that was put up live without any performance testing and are now chipping away at issues to improve the system (sound familiar?) However, since this function (and the others) perform well except for directly after a stats update, I don’t think the coding style is relevant to the issue. But then I am at a loss, so you could certainly overturn that assertion with a well formed argument?.
As to the comments about a TVF working better, could someone expand on that? I am not familiar with TVF having significant performance implications.
Thanks again,
Ben
March 8, 2010 at 1:39 pm
Oh my....
I'll be blunt. I am surprise that ever performs acceptably. The 'catch-all' query form that you have there does not perform well. It is seriously prone to very, very bad execution plans and will almost never run well.
A stats update will force the plans out of cache and, depending what the next call to this proc is, could result in a really, really sub-optimal plan generated just because the parameters are different.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
At this point I would suggest a near-complete rewrite of this query, probably with dynamic SQL (the post above has an example of how), and possibly with a table-valued function replacing the scalar function.
Basically, the way it is written you are going to have different execution plans generated and cached depending how the query is called the first time and it's unlikely any of them will be entirely optimal for all possible calls.
I understand the problem of real data, is there any chance of getting some garbage test data even? If not, I'll see if the data generator I have will come up with something reasonable for testing. How many rows (aprox) are in these tables?
Can you post the defintions of the following table too please
dbo.Consumer_MV,
Organization_Ward
and the definition of dbo.GetConsumerLastFirstMIName() and dbo.GetOrganizationBedStatusID(a.Organization_Bed_ID)
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
March 8, 2010 at 2:12 pm
I agree with you on the catch-all nature of the system design. I am spending a lot of my time rewriting these to Dynamic-SQL and educating the users as the ramifications of this approach.
The dev and test data are too similar to production to post (another initiative I am working on) so I think creating garbage test data is going to be the only option unless I take on 3 hours of scrubbing and 4 hours of approval meetings to release data.
Consumer_MV has 1,040k records.
Organization_Ward has 460 records.
…and here are the defs:
CREATE TABLE [dbo].[Consumer_MV](
[Consumer_Data_ID] [int] NOT NULL,
[Person_Data_ID] [int] NOT NULL,
[First_Name] [varchar](64) NOT NULL,
[Middle_Name] [varchar](64) NULL,
[Last_Name] [varchar](64) NOT NULL,
[Name_Prefix] [varchar](64) NULL,
[Name_Suffix] [varchar](64) NULL,
[First_Name_NYSIIS] [varchar](256) NULL,
[Last_Name_NYSIIS] [varchar](256) NULL,
[DCN] [varchar](256) NULL,
[SSN] [varchar](256) NULL,
[Birth_Date] [char](8) NULL,
[Gender] [varchar](64) NULL,
[Primary_City] [varchar](40) NULL,
[Primary_State] [varchar](64) NULL,
[Primary_ZipCode] [varchar](10) NULL,
[Primary_County] [varchar](32) NULL,
[Primary_Country] [varchar](64) NULL,
[ModifiedDate] [datetime] NULL,
[Primary_Street1] [varchar](80) NULL,
[Primary_Street2] [varchar](80) NULL,
[Primary_County_FIPS] [char](3) NULL,
CONSTRAINT [PK__Consumer_MV__2920B817] PRIMARY KEY CLUSTERED
(
[Consumer_Data_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Consumer_MV] ADD CONSTRAINT [DF_Consumer_MV_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate]
GO
CREATE TABLE [dbo].[Organization_Ward](
[Organization_Ward_ID] [int] NOT NULL,
[Organization_Ward_Name] [varchar](64) NULL,
[From_Date] [char](8) NOT NULL,
[To_Date] [char](8) NULL,
[Organization_Link_ID] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[CreatedBy] [varchar](32) NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[ModifiedBy] [varchar](32) NOT NULL,
CONSTRAINT [PK__Organization_War__758E35B3] PRIMARY KEY CLUSTERED
(
[Organization_Ward_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Organization_Ward] WITH CHECK ADD CONSTRAINT [FK_Organization_Link__Organization_Ward] FOREIGN KEY([Organization_Link_ID])
REFERENCES [dbo].[Organization_Link] ([Organization_Link_ID])
GO
ALTER TABLE [dbo].[Organization_Ward] CHECK CONSTRAINT [FK_Organization_Link__Organization_Ward]
GO
ALTER TABLE [dbo].[Organization_Ward] WITH CHECK ADD CONSTRAINT [CK_Organization_Ward_Date_Range_Check] CHECK (([dbo].[CheckDateRange]([From_Date], [To_Date]) = 1))
GO
ALTER TABLE [dbo].[Organization_Ward] CHECK CONSTRAINT [CK_Organization_Ward_Date_Range_Check]
GO
ALTER TABLE [dbo].[Organization_Ward] WITH CHECK ADD CONSTRAINT [Organization_Ward_From_Date_ISO_Date_Check] CHECK (([dbo].[ValidISODate]([From_Date]) = 1))
GO
ALTER TABLE [dbo].[Organization_Ward] CHECK CONSTRAINT [Organization_Ward_From_Date_ISO_Date_Check]
GO
ALTER TABLE [dbo].[Organization_Ward] WITH CHECK ADD CONSTRAINT [Organization_Ward_To_Date_ISO_Date_Check] CHECK (([dbo].[ValidISODate]([To_Date]) = 1))
GO
ALTER TABLE [dbo].[Organization_Ward] CHECK CONSTRAINT [Organization_Ward_To_Date_ISO_Date_Check]
GO
ALTER TABLE [dbo].[Organization_Ward] ADD DEFAULT (convert(char(8),getdate(),112)) FOR [From_Date]
GO
ALTER TABLE [dbo].[Organization_Ward] ADD DEFAULT (getdate()) FOR [CreatedDate]
GO
ALTER TABLE [dbo].[Organization_Ward] ADD DEFAULT (convert(varchar(32),user_name())) FOR [CreatedBy]
GO
ALTER TABLE [dbo].[Organization_Ward] ADD DEFAULT (getdate()) FOR [ModifiedDate]
GO
ALTER TABLE [dbo].[Organization_Ward] ADD DEFAULT (convert(varchar(32),user_name())) FOR [ModifiedBy]
GO
CREATE FUNCTION dbo.GetConsumerLastFirstMIName
(
@ConsumerDataID int
)
RETURNS varchar (256)
AS
BEGIN
DECLARE @ConsumerName varchar(256)
SET @ConsumerName=''
SELECT@ConsumerName = Last_Name + ', '
+ First_Name + ' '
+ ISNULL(Middle_Name, '')
+ ISNULL(' ' + Name_Suffix, '')
FROMdbo.Consumer_MV with (nolock)
WHEREConsumer_Data_ID = @ConsumerDataID
RETURN @ConsumerName
END
GO
CREATE FUNCTION dbo.GetOrganizationBedStatusID
(
@OrganizationBedID INT
)
RETURNS INT
AS
BEGIN
DECLARE @StatusID INT
DECLARE @testtable TABLE
(
BedID INT PRIMARY KEY ,
MaxFromDate CHAR(8)
)
INSERT INTO @testtable
( BedID ,
MaxFromDate
)
( SELECT Organization_Bed_id ,
MAX(FROM_DATE)
FROM Organization_Bed_Status_History
WHERE Organization_Bed_ID = @OrganizationBedID
GROUP BY Organization_Bed_id
)
SELECT @StatusID = a.CD_Organization_Bed_Status_ID
FROM Organization_Bed_Status_History a
WHERE a.Organization_Bed_ID = @OrganizationBedID
AND a.From_Date = ( SELECT MaxFromDate
FROM @testtable
WHERE BedID = a.Organization_Bed_ID
)
RETURN @StatusID
END
GO
CREATE TABLE [dbo].[Organization_Bed_Status_History](
[Organization_Bed_Status_History_ID] [int] NOT NULL,
[Organization_Bed_ID] [int] NOT NULL,
[CD_Organization_Bed_Status_ID] [int] NOT NULL,
[From_Date] [char](8) NOT NULL,
[To_Date] [char](8) NULL,
[CreatedDate] [datetime] NOT NULL,
[CreatedBy] [varchar](32) NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[ModifiedBy] [varchar](32) NOT NULL,
CONSTRAINT [PK_Organization_Bed_Status_History] PRIMARY KEY CLUSTERED
(
[Organization_Bed_Status_History_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Organization_Bed_Status_History] WITH CHECK ADD CONSTRAINT [FK_CD_Organization_Bed_status__Organization_Bed_Status_History] FOREIGN KEY([CD_Organization_Bed_Status_ID])
REFERENCES [dbo].[CD_Organization_Bed_status] ([CD_Organization_Bed_status_ID])
GO
ALTER TABLE [dbo].[Organization_Bed_Status_History] CHECK CONSTRAINT [FK_CD_Organization_Bed_status__Organization_Bed_Status_History]
GO
ALTER TABLE [dbo].[Organization_Bed_Status_History] WITH NOCHECK ADD CONSTRAINT [FK_Organization_Bed__Organization_Bed_Status_History] FOREIGN KEY([Organization_Bed_ID])
REFERENCES [dbo].[Organization_Bed] ([Organization_Bed_ID])
GO
ALTER TABLE [dbo].[Organization_Bed_Status_History] CHECK CONSTRAINT [FK_Organization_Bed__Organization_Bed_Status_History]
GO
ALTER TABLE [dbo].[Organization_Bed_Status_History] WITH CHECK ADD CONSTRAINT [CK_Organization_Bed_Status_History_Date_Range_Check] CHECK (([dbo].[CheckDateRange]([From_Date], [To_Date]) = 1))
GO
ALTER TABLE [dbo].[Organization_Bed_Status_History] CHECK CONSTRAINT [CK_Organization_Bed_Status_History_Date_Range_Check]
GO
ALTER TABLE [dbo].[Organization_Bed_Status_History] WITH CHECK ADD CONSTRAINT [CK_Organization_Bed_Status_History_From_Date_ISO_Date_Check] CHECK (([dbo].[ValidISODate]([From_Date]) = 1))
GO
ALTER TABLE [dbo].[Organization_Bed_Status_History] CHECK CONSTRAINT [CK_Organization_Bed_Status_History_From_Date_ISO_Date_Check]
GO
ALTER TABLE [dbo].[Organization_Bed_Status_History] WITH CHECK ADD CONSTRAINT [CK_Organization_Bed_Status_History_To_Date_ISO_Date_Check] CHECK (([dbo].[ValidISODate]([To_Date]) = 1))
GO
ALTER TABLE [dbo].[Organization_Bed_Status_History] CHECK CONSTRAINT [CK_Organization_Bed_Status_History_To_Date_ISO_Date_Check]
GO
ALTER TABLE [dbo].[Organization_Bed_Status_History] ADD CONSTRAINT [DF__Organizat__Creat__2E0AC65C] DEFAULT (getdate()) FOR [CreatedDate]
GO
ALTER TABLE [dbo].[Organization_Bed_Status_History] ADD CONSTRAINT [DF__Organizat__Creat__2EFEEA95] DEFAULT (convert(varchar(32),user_name())) FOR [CreatedBy]
GO
ALTER TABLE [dbo].[Organization_Bed_Status_History] ADD CONSTRAINT [DF__Organizat__Modif__2FF30ECE] DEFAULT (getdate()) FOR [ModifiedDate]
GO
ALTER TABLE [dbo].[Organization_Bed_Status_History] ADD CONSTRAINT [DF__Organizat__Modif__30E73307] DEFAULT (convert(varchar(32),user_name())) FOR [ModifiedBy]
GO
I am intrigued by the notion of the first run setting the plan and subsequent runs using that plan. It would explain quite a few things as to the intermitant nature and even why sometimes we attempt to recompile and it seems to not improve, but if we persevere and recompile additional times we eventually get it working faster. I can see it all being in the timing of the first call.
March 8, 2010 at 2:26 pm
Ben Vegiard (3/8/2010)
The dev and test data are too similar to production to post (another initiative I am working on) so I think creating garbage test data is going to be the only option unless I take on 3 hours of scrubbing and 4 hours of approval meetings to release data.Consumer_MV has 1,040k records.
Organization_Ward has 460 records.
…and here are the defs:
I'll try and look at this later this week. No promises, I have real work to do as well and that obviously takes precedence.
I am intrigued by the notion of the first run setting the plan and subsequent runs using that plan. It would explain quite a few things as to the intermitant nature and even why sometimes we attempt to recompile and it seems to not improve, but if we persevere and recompile additional times we eventually get it working faster. I can see it all being in the timing of the first call.
SQL caches its execution plans (plan cache) and reuses then from that point on until something forces it to invalidate the cached plan. The plan will be invalidated when any underlying object changes, when statistics change (automatic update or manual), an explicit recompile is requested or something clears the entire procedure cache.
When a plan is first compiled it is optimised based on the parameters passed on that first call. This is why parameter sniffing is a problem. SQL optimises the query for one set of parameters and will reuse it. If the generated execution plan would not be optimal for a different set of parameters then executions with those parameters are far slower than they should.
I would guess that there are some parameter sets where the generated plan is acceptable for most calls and other sets where the generated plan is only optimal for a very small number of calls.
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
March 8, 2010 at 2:38 pm
I'm not sure if the rewritten versions of the attached functions will return the same as yours currently used, but it might be worth a try...
Especially dbo.GetOrganizationBedStatusID could be simplified a lot if I'm not overlooking something obvious... (I tend to do without test data though...)
Regarding the parameter sniffing: I'd prefer to leave it with much more experienced folks already involved... 😉
CREATE FUNCTION dbo.GetConsumerLastFirstMIName
(@ConsumerDataID INT )
RETURNS TABLE
AS
RETURN(
SELECTConsumer_Data_ID,
Last_Name + ', '
+ First_Name + ' '
+ ISNULL(Middle_Name, '')
+ ISNULL(' ' + Name_Suffix, '') AS FullName
FROMdbo.Consumer_MV WITH (NOLOCK)
WHEREConsumer_Data_ID = @ConsumerDataID
)
GO
CREATE FUNCTION dbo.GetOrganizationBedStatusID
(
@OrganizationBedID INT
)
RETURNS TABLE
AS
RETURN
(
WITH cte AS
(
SELECT MAX(FROM_DATE) AS MDate,
@OrganizationBedID AS BedId
FROM Organization_Bed_Status_History
WHERE Organization_Bed_ID = @OrganizationBedID
)
SELECT a.CD_Organization_Bed_Status_ID AS StatusId,
@OrganizationBedID AS BedId
FROM Organization_Bed_Status_History a
INNER JOIN cte
ON a.Organization_Bed_ID = cte.BedId
AND a.From_Date = cte.MDate
)
GO
March 8, 2010 at 2:44 pm
Don't go any further out of your way. I have enough of an understanding to test this using the real thing. As well, this answer fits well enough that I am confident it's a strong possibility. If it doesn't pan out I may come back with results to see if there are further ideas.
Thank you very much for the time and insight you have put into this!
Ben
March 8, 2010 at 2:47 pm
Here is a rewrite of one of the functions you posted. Use it in the FROM clause using CROSS APPLY. I'd show you, but I didn't see a query using this particular function.
CREATE FUNCTION dbo.GetConsumerLastFirstMIName(
@ConsumerDataID int
)
RETURNS TABLE
AS
RETURN (
SELECT
Last_Name + ', ' + First_Name + ' ' + ISNULL(Middle_Name, '') + ISNULL(' ' + Name_Suffix, '') as ConsumerLastFirstMIName
FROM
dbo.Consumer_MV
WHERE
Consumer_Data_ID = @ConsumerDataID
)
You can check out this blog post to see a comparision between scalar functions, in-line TVF, and Hardcoded functions:
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply