March 13, 2010 at 9:42 pm
SQL: SQL 2005 enterprise edition 64 bit install; sp3
OS: Windows 2003 Enterprise 64 bit; sp2
I had 32 gig and we increased to 64 gig. I did not enable awe since this is a 64 bit setup. I increased the max server memory to take 54 gig of the 64 gig...min is set to 4 gig. When it had 32 gig, I had the max server memory set to 26 gig.
After the addition of the memory and readjusting the max server memory to 54 gig, a query went from taking 6 secs to 1 min and 45 seconds. I set the max server memory back to 26 gig and now the query runs in 6 seconds.
I am at a loss as to why this is happening...can anyone shed any light. I have read tons of material on memory settings for sql...but I must have missed something. Any help or insight would be much appreciated.
I have set the lock pages in memory account to the service account that runs sql.
March 13, 2010 at 10:04 pm
First thing here is theoretically max and min memory setting should not effect if it is 64 bit, i have no idea why changing it to 26 GB performing better.
March 14, 2010 at 6:52 am
jwa082276 (3/13/2010)
...a query went from taking 6 secs to 1 min and 45 seconds. I set the max server memory back to 26 gig and now the query runs in 6 seconds.
It is probably not related to the memory change. Have you checked the server error log?
The thing to do here is to compare the actual execution plans. There should be an obvious difference between the two runs. If you are able to post those two .sqlplan files, I would be happy to take a look.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 14, 2010 at 7:37 am
GTR (3/13/2010)
First thing here is theoretically max and min memory setting should not effect if it is 64 bit
Why do you say that? My experience of 64-bit SQL Server is that, if anything, it is even more important to set these correctly than on 32-bit SQL Server.
http://technet.microsoft.com/en-us/library/ms187499.aspx
If locked pages in memory privilege is granted (either on 32-bit for AWE support or on 64-bit by itself), we recommend also setting max server memory.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 14, 2010 at 10:28 am
Ok I compared the 2 execution plans....
one execution plan from when the max server memory was 26 gig and one from when the max server memory was set to 54 gig....there is definitley a difference in one of the query execution plans....
I have attached it....
Question: why would sql give it different execution plans just b/c I changed the max server memory.
Also to add, I checked the event logs on the server...all clean..app, system and security...
SHould I set locked pages in memory to the service account that is running sql since this is 64 bit?
March 14, 2010 at 4:38 pm
Off-hand it looks like the extra memory has encouraged it to believe that a 15gb hash in the middle of your plan would be a better idea than several smaller sorts and hashes. But apparently it is not better...
Can't tell you much more without the actually query and some table definitions...
[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 14, 2010 at 5:57 pm
But why would it do that with the extra memory? Is there a way around this?
Its almost as if I shouldnt have added the additional memory.......
Here is the proc:
CREATE PROCEDURE [dbo].[GetContractNumberFromFilter]
-- Add the parameters for the stored procedure here
@ProgramID Varchar(25) = '',
@ProdOfficeCode Varchar(4) = '' ,
@CedantID int = 0,
@UWCode Varchar(4) = '',
@LegalCode Varchar(4) = '',
@BrokerCode Varchar(3) = '',
@ProdTypeID int = 0,
@ContTypeID int =0,
@StatusID int =0,
@IRISPolicyNum Varchar(25) = '',
@SubmissionID Varchar(25) = NULL,
@EffectiveDateFrom varchar(10) = NULL,
@EffectiveDateTo varchar(10) = NULL,
@IncDate VARCHAR(2) = NULL ,--contains <, > <= conditions
@BrokerReference Varchar(12) = '',
@TreatyTitle Varchar(100) = NULL,
@ProfitCenterID int = 0,
@DocTypID int =0,
@IsPureTerror bit =0,
@SQLUNMODELLED bit= 0,
@MaxRecords int = 100000,
@DOCUMENTSEARCH bit =0,
@SQLMODELLED bit = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
--@EffectiveDateFrom DateTime = NULL,
--@EffectiveDateTo DateTime = NULL,
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @SQL_UNMODELLED varchar(100)
Declare @Where varchar(8000)
Declare @Join varchar (8000)
DECLARE @MaxRec VARCHAR(50)
IF @DocumentSearch = 1
BEGIN
SET @SQLUNMODELLED = 0
SET @SQLMODELLED = 0
END
Select @Where = ''
Select @Join = ''
Select @SQL_UNMODELLED = ' Not Exists (Select C.ProgramID From tbl_Contract C Where C.ProgramID = P.ProgramID) '
If (@IsPureTerror = 1)
Begin
Select @Where = [dbo].[BuildCondition] (@Where,' dbo.IsPureTerror(C.PerilCode) > 0 ')
End
IF Len(@ProgramID) > 0
Begin
IF RIGHT(@ProgramID,1)='*'
Begin
Select @Where = [dbo].[BuildCondition] (@Where, 'P.ProgramID LIKE ''' + REPLACE(@ProgramID,'*','%') +'''' )
End
Else
Begin
Select @Where = [dbo].[BuildCondition] (@Where, 'P.ProgramID = ''' + @ProgramID + '''' )
End
End
IF Len(@IRISPolicyNum) > 0
Begin
IF RIGHT(@IRISPolicyNum,1)='*'
Begin
Select @Where = [dbo].[BuildCondition] (@Where, 'C.IRISPolicyNum LIKE ''' + REPLACE(@IRISPolicyNum,'*','%') +'''' )
End
Else
Begin
Select @Where = [dbo].[BuildCondition] (@Where, 'C.IRISPolicyNum = ''' + @IRISPolicyNum + '''' )
End
End
IF Len(@ProdOfficeCode) > 0
Begin
Select @Where = [dbo].[BuildCondition] (@Where,'P.ProdOfficeCode = ' + '''' + @ProdOfficeCode + '''')
End
IF @CedantID > 0
Begin
--Select @Where = [dbo].[BuildCondition] (@Where, @Where + 'P.CedantID = ' + + CONVERT(varchar, @CedantID) )
Select @Where = [dbo].[BuildCondition] (@Where, 'P.CedantID = ' + CONVERT(varchar, @CedantID) )
End
IF Len(@UWCode) > 0
Begin
Select @Where = [dbo].[BuildCondition] (@Where,'P.UWCode = ' + '''' + @UWCode + '''')
End
-- IF Len(@LegalCode) > 0
-- Begin
-- Select @Where = [dbo].[BuildCondition] (@Where,'P.UWCode = ' + '''' + @LegalCode + '''')
-- End
IF Len(@BrokerCode) > 0
Begin
Select @Where = [dbo].[BuildCondition] (@Where,'P.BrokerCode = ' + '''' + @BrokerCode + '''')
End
IF @ProdTypeID > 0
Begin
Select @Where = [dbo].[BuildCondition] (@Where, 'P.ProdTypeID = ' + CONVERT(varchar, @ProdTypeID) )
End
IF @ContTypeID > 0
Begin
Select @Where = [dbo].[BuildCondition] (@Where, 'P.ContTypeID = ' + CONVERT(varchar, @ContTypeID) )
End
IF @StatusID > 0
Begin
Select @Where = [dbo].[BuildCondition] (@Where, 'C.StatusID = ' + CONVERT(varchar, @StatusID) )
End
/*IF @IRISPolicyNum > 0
Begin
Select @Where = [dbo].[BuildCondition] (@Where, 'C.IRISPolicyNum = ' + CONVERT(varchar, @IRISPolicyNum) )
End*/
IF Len(@SubmissionID) > 0
Begin
Select @Where = [dbo].[BuildCondition] (@Where,'C.SubmissionID LIKE ''' + REPLACE(@SubmissionID,'*','%') + '''')
End
IF (ISNULL(@EffectiveDateFrom,'') <> '')
BEGIN
IF (ISNULL(@EffectiveDateTo,'') <> '')
BEGIN
--Between
IF Len(@Where) > 0
Begin
Select @Where = @Where + ' AND ' + '(P.EffectiveDate BETWEEN ' + '''' + CONVERT(varchar,@EffectiveDateFrom) + ''''
+ ' AND ' + '''' + @EffectiveDateTo + '''' + ')'
End
Else
Begin
Select @Where = '(P.EffectiveDate BETWEEN ' + '''' + CONVERT(varchar,@EffectiveDateFrom) + ''''
+ ' AND ' + '''' + CONVERT(varchar,@EffectiveDateTo) + '''' + ')'
END
END
ELSE
Begin
Select @Where = [dbo].[BuildCondition] (@Where,'P.EffectiveDate ' + @IncDate + '''' + CONVERT(varchar,@EffectiveDateFrom) + '''')
End
END
IF Len(@BrokerReference) > 0
Begin
Select @Join = @Join + ' INNER JOIN BrokerInfo AS B ON P.ProgramID = B.ProgramID '
Select @Where = [dbo].[BuildCondition] (@Where,'B.BrokerReference LIKE ' + '''' + @BrokerReference + '''')
End
IF Len(@TreatyTitle) > 0
Begin
Select @Where = [dbo].[BuildCondition] (@Where,'C.TreatyTitle LIKE ' + '''' + @TreatyTitle + '''')
End
IF @ProfitCenterID > 0
Begin
--Select @Where = 'P.ProgramID = ' + CONVERT(varchar, @ProgramID)
Select @Where = [dbo].[BuildCondition] (@Where, 'C.ProfitCenterID = ' + CONVERT(varchar, @ProfitCenterID) )
End
IF @DocTypID > 0
Begin
--Select @Where = 'P.ProgramID = ' + CONVERT(varchar, @ProgramID)
Select @Join = @Join + ' INNER JOIN DMSDocument AS D ON D.ContractID = C.ContractID '
Select @Where = [dbo].[BuildCondition] (@Where, 'D.DocumentTypeID = ' + CONVERT(varchar, @DocTypID) )
End
IF Len(@LegalCode) > 0
Begin
IF @DocTypId = 0
BEGIN
Select @Join = @Join + ' INNER JOIN DMSDocument AS D ON D.ContractID = C.ContractID '
END
--Common for both cases - DocTypeId = 0 and docTypeID > 0
Select @Join = @Join + ' INNER JOIN DMSLegalAnalystReview AS LAR ON LAR.DocId = D.ID '
Select @Where = [dbo].[BuildCondition] (@Where,'LAR.LegalAnalystCode = ' + '''' + @LegalCode + '''')
End
If @SQLUNMODELLED = 1
BEGIN
Select @WHERE = [dbo].[BuildCondition] (@Where,@SQL_UNMODELLED)
END
IF Len(@Where) > 0
Begin
Select @Where = ' Where ' + @Where
End
SELECT @MaxRec = CONVERT(VARCHAR, @MaxRecords)
IF @DOCUMENTSEARCH = 1
BEGIN
/*when the search is document search, we need to get Programid, contractid from RWS and then use that
combination to get the appropriate records from axis_dms database*/
CREATE TABLE #TEMPDMS
(ProgramId INT NULL,
ContractId INT NULL,
TreatyTitle VARCHAR(100) NULL,
ProfitCenter VARCHAR(50) NULL,
LineOfBusiness VARCHAR(50) NULL,
)
EXEC ('INSERT INTO #TEMPDMS (ProgramId, ContractId, TreatyTitle, ProfitCenter, LineOfBusiness) Select TOP ' + @MaxRec + ' P.ProgramID, C.ContractID
,C.TreatyTitle, PC.ProfitCenter, PT.Description FROM tbl_Program AS P INNER JOIN
tbl_Contract AS C ON P.ProgramID = C.ProgramID LEFT JOIN tbl_ProfitCenter AS PC ON C.ProfitCenterID = PC.ProfitCenterID LEFT JOIN tbl_ProdType PT ON P.ProdTypeId = PT.ProdTypeId ' + @Join + @Where + ' ORDER BY P.ProgramId DESC' )
EXEC [GetDMSDocumentInformationForSearch]
DROP TABLE #TEMPDMS
END
Else
BEGIN
--SELECT @MaxRec = CONVERT(VARCHAR, @MaxRecords)
IF @SQLMODELLED =1
Exec('Select TOP ' + @MaxRec + ' * FROM vw_Program P WHERE P.ProgramID IN (Select P.ProgramID FROM vw_Program P INNER JOIN
tbl_Contract AS C ON P.ProgramID = C.ProgramID' + @Join + @Where + ') ORDER BY P.ProgramId DESC' )
ELSE
Exec('Select TOP ' + @MaxRec + ' * FROM vw_Program P WHERE P.ProgramID IN (Select P.ProgramID FROM vw_Program P LEFT JOIN
tbl_Contract AS C ON P.ProgramID = C.ProgramID' + @Join + @Where + ') ORDER BY P.ProgramId DESC' )
END
END
March 14, 2010 at 9:52 pm
jwa082276 (3/14/2010)
Should I set locked pages in memory to the service account that is running sql since this is 64 bit?
Absolutely yes. More details:
I am going to take a look at the execution plans now. Note to Barry: the optimizer never considers how much Buffer Pool is available when constructing a query plan. It does save the minimum amount of memory required, and a 'desired' amount however. If less than half the desired memory grant is available at run-time, the request will wait in the memory grant scheduler queue. The maximum wait time is 25 times the estimated cost of the plan, in seconds. Error 8645 is raised if this time-out is reached.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 14, 2010 at 10:03 pm
Difficult to say anything definitive from that estimated execution plan. Actual plans are much more useful since they contain run-time information, such as the distribution of rows between threads, actual number of rows flowing between each operator, and so on.
The estimated 15GB size referenced by Barry occurs in both plans before the aggregate. It just happens to be a hash aggregate in one plan, and a stream aggregate in the other.
Post actual execution plans, and we will be able to say more.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 14, 2010 at 10:08 pm
The posted procedure is interesting in itself, but does not match the execution plans. We need the code for GetDMSDocumentInformationForSearch, not the procedure that calls it.
By the way, you really need to read Erland Sommarskog's guide to writing dynamic search conditions:
http://www.sommarskog.se/dyn-search.html
In fact, I would suggest you read and understand that article fully before doing anything else today. 😉
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 15, 2010 at 7:02 am
Thx..I will read the guide today...I didnt write this SP....got handed to me...so I am pressed with finding out whey now it performs bad as we added the additional memory...
March 15, 2010 at 7:22 am
jwa082276 (3/15/2010)
I didnt write this SP....got handed to me
Good luck with it then 😉
The article should give you some ideas for improvements.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 15, 2010 at 8:43 am
You might be able to force the plan to be the same as the lower-memory one by using a plan guide. Or use hints to achieve the same result.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 15, 2010 at 8:56 am
TheSQLGuru (3/15/2010)
You might be able to force the plan to be the same as the lower-memory one by using a plan guide. Or use hints to achieve the same result.
Absolutely. Given an actual plan, we might even be able to go further and recommend some more useful indexes too. I am a firm believer in helping the optimizer to produce a good plan on its own before restoring to hints - and plan guides are just unwieldy. Of course it would be nice to see the code for the procedure in question too 🙂
If you are thinking about a RECOMPILE hint, I would agree with that too - potentially, anyway. There is a lot that could be done.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 15, 2010 at 10:02 am
Paul White (3/14/2010)
GTR (3/13/2010)
First thing here is theoretically max and min memory setting should not effect if it is 64 bitWhy do you say that? My experience of 64-bit SQL Server is that, if anything, it is even more important to set these correctly than on 32-bit SQL Server.
http://technet.microsoft.com/en-us/library/ms187499.aspx
If locked pages in memory privilege is granted (either on 32-bit for AWE support or on 64-bit by itself), we recommend also setting max server memory.
We had memory issues while we were on 32 bit SQL 2005, then Microsoft first suggestion was to change Max Min memory settings as that didn't fix, Microsoft next suggestion was to upgrade to 64 bit SQL after upgrade, we are not seeing memory pressure now, Believe Max and Min is not necessary if you have SQL 64 bit, unless you want spend more time on this.
http://www.dell.com/downloads/global/power/ps3q06-20060270-Sankaran.pdf
AWE is not necessary on 64-bit versions of SQL Server 2005
because virtual address space is not limited to 4 GB
http://msdn.microsoft.com/en-us/library/cc966540.aspx
Address Windowing Extensions (AWE) is an API that allows a 32-bit application to manipulate physical memory beyond the inherent 32-bit address limit. AWE mechanism technically is not necessary on 64-bit platform. It is, however, present there.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply