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 123»»»

SQL performance issues after increasing memory from 32 to 64 gig Expand / Collapse
Author
Message
Posted Saturday, March 13, 2010 9:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:17 PM
Points: 16, Visits: 276
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.
Post #882480
Posted Saturday, March 13, 2010 10:04 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 3:22 PM
Points: 381, Visits: 359
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.

EnjoY!
Post #882486
Posted Sunday, March 14, 2010 6:52 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:21 PM
Points: 9,927, Visits: 11,193
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #882563
Posted Sunday, March 14, 2010 7:37 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:21 PM
Points: 9,927, Visits: 11,193
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #882567
Posted Sunday, March 14, 2010 10:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:17 PM
Points: 16, Visits: 276
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?


  Post Attachments 
max_server_memory_26_executionplan23.sqlplan (34 views, 149.50 KB)
max_server_memory_54_executionplan.sqlplan23.sqlplan (17 views, 129.39 KB)
Post #882586
Posted Sunday, March 14, 2010 4:38 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 1:19 PM
Points: 9,294, Visits: 9,491
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...



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #882633
Posted Sunday, March 14, 2010 5:57 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 12, 2013 1:17 PM
Points: 16, Visits: 276
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









Post #882649
Posted Sunday, March 14, 2010 9:52 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:21 PM
Points: 9,927, Visits: 11,193
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:

http://blogs.msdn.com/psssql/archive/2009/09/11/fun-with-locked-pages-awe-task-manager-and-the-working-set.aspx

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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #882686
Posted Sunday, March 14, 2010 10:03 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:21 PM
Points: 9,927, Visits: 11,193
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #882688
Posted Sunday, March 14, 2010 10:08 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Monday, November 17, 2014 7:21 PM
Points: 9,927, Visits: 11,193
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #882693
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse