none
More RAM or Dual core processor required? RRS feed

  • Question

  • We are running server 2003 r2 x64 with sql 2008 enterprise with 12gb RAM and 1 dual core processor (singe port machine)

    We are running a really heavy query which is using 11.7gb pagefile. We need to run several of these queries but performance suffers very badly when we execute more than one. CPU usage is fairly static at less then 10% when only one query running but shoots to 75% + when we execute a second/ third one (pagefile usage stays at 11.7gb)

    We are aware that we need to improve performance, the question is do we get more RAM or switch to a quad core processor?
    Saturday, August 14, 2010 10:59 AM

All replies

  • Hi Robert,

    Since you are sat on 64 bit as you are probably aware you are already making use of the 12Gb of RAM. To what extent have you tried to optimise your queries? Assuming you already have appropriate data types, primary, foreign keys and clustered views. Have you had a look at the following URLs; - Statistics Used by the Query Optimizer in Microsoft SQL Server 2008 and DBCC SHOW_STATISTICS (Transact-SQL)?

    I personally can't say whether you should upgrade your RAM or processor or both without some knowledge of your business critical queries and the statistics columns generate within these queries.

    I hope this helps,

    Kind Regards,

    Kieran.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Wood PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
    Saturday, August 14, 2010 1:48 PM
  • The answer is RAM.

    But first, the answer is change your SQL settings to a maximum RAM of maybe 10gb.  When a SQL Server system is paging, your performance has gone down the toilet.   SQL works much better when it knows its own limits (hat tip to Dirty Harry).

    What about your disk structures?  Do you even have the log on a separate device from the data?  What about tempdb?  Are you using RAID storage or SANS at all?  All these things matter bigtime, when you start running multiple heavy queries.  Though, it is often the case that throwing lots of RAM at the problem is a quick fix.  But lots of RAM may be 128gb or more.  They build those server-class machines for a reason!

    How big is your database?

    Josh

     

    Saturday, August 14, 2010 3:32 PM
  • Thanks for the feedback Robert if your queries are fully optimised then it's back to upgrading the parts that you can kick as you stated in your initial question. After reading JRStern's response it looks like he is better placed to offer you this hardware upgrade guidance than me.
    If you have found any of my posts helpful then please vote them as helpful. Kieran Wood PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
    Saturday, August 14, 2010 5:37 PM
  • I would say :

    check perfmon SQL Server instance >> memory manager >> Target and total server memory and see how much it is .

    next see the waittime and waittype in the sysprocesses for that SPID .

    Post the putput here .

    Also check the max server memory of SQL Sevrer .

    even though SQL is paging out , how much RAM is showong as available in task manager at that time .

    if its less that or about 256 MB , i think you should add RAM

    I also think that you should add CPU as well (use perfmon to see if any requests are waiting on CPU)

    Check error logs if its showing any signs of stress .

    Regards

    Abhay

    Saturday, August 14, 2010 5:38 PM
  • Robert,

    You need to look at the WAIT STATISTICS to accurately figure out the bottleneck.

    http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/performance_tuning_waits_queues.mspx

    http://download.microsoft.com/download/D/B/D/DBDE7972-1EB9-470A-BA18-58849DB3EB3B/TShootPerfProbs2008.docx

    How are you checking the deduping? Can you share the code at a high level with a sample data? Deduping queries could be highly optimized using HASHBYTES and making few schema/index changes. Take a look at HASHBYTES and see if that will work in your scenario.

    SQL Server is memory hungry and if you can feed more RAM then you will definitely see the benefit in general as you get to keep more data pages in memory and less roundtrips to disk and less work for lazywriter as well.

    I am surprised that you are running SQL Server 2008 Ent edition on a crappy hardware. Multi-core boxes aren't that expensive these days and would strongly suggest to go on a newer hardware if you can.


    http://SankarReddy.com/
    Saturday, August 14, 2010 5:40 PM
    Moderator
  •  

    Thanks for your response Abhay, here are the stats you requested.

    What are your thoughts?

    Error logs are fine.

    Physical Memory

    Available:  112264 (KB)

     Perfmon results

    Target Server Memory (KB)                                                                                                                                                                                                                                         11280448
    Total Server Memory (KB)                                                                                                                                                                                                                                          11280448

    Sakar,

    Thanks for the links, I will read through.

    My current understanding is that a QUAD core processor will give us double the SQLOS schedulers so would help performance but my question is would say another 8GB of RAM improve performance more?

     

    Saturday, August 14, 2010 6:00 PM
  • Hi Robert,

    So from current output what we are seeing is that SQL Server is configured to use 10 GB of memory (as suggested by another geek above and you did that) and it is using whole 10 GB of memory.

             Total Server Memory (KB)   11280448/1024/1024=10.75 GB  (This is configured value)

             Target Server Memory (KB) 11280448/1024/1024=10.75 GB  (This is currenty being used)

    Now Physical Memory as we can see from above output is 112264/1024=109 MB (which is less). As you have left 2 GB memory for Operating System as well and out of that only 109 MB is free that means some other components on this machine is also using memory which we need to figure out (using Perfmon). But as a whole yes it seems that adding more memory and adding CPU will help you a bit.

    Apart from this on SQL side we can:

    Run SQL Server Performance Dash Board during the time when we run these queries and see what results we are getting.

    http://sqlblogcasts.com/blogs/thepremiers/archive/2008/06/20/sql-server-2008-performance-studio.aspx

    Run queries using Database Tunning Advisor and check if some indexes are missing

     

    Regards

    Gursethi

     

    Saturday, August 14, 2010 6:29 PM
  • Hi Gursethi,

    I posed the below question and did not get a reply yet so have not taken any action. Could you answer?

    So should I change to?

    Min: 1024

    Max: 10240

    Can I change server memory options while the query is running, or should I wait till it finishes?

    It is currently set at

    Min: 0

    Max: 2147483647

    Saturday, August 14, 2010 6:40 PM
  • So should I change to:

    Min: 1024

    Max: 10240

    Can I change server memory options while the query is running, or should I wait till it finishes?

    It is currently set at

    Min: 0

    Max: 2147483647

    Which could be the problem?

    That is definitely a large part of your problem.

    Actually, I'm rather fuzzy on when you can change it.  I'm sure you can change it anytime, but  you may have to bounce the service, or the server, before it takes effect.

    Now, just setting the proper limits won't solve all the world's problems, but one thing at a time!  Let's see what that does for you first.

    As for whether more processors will help, that gets into additional issues, parallelism, contention between queries, yada yada.  Workgroups and such:

    http://msdn.microsoft.com/en-us/library/bb933866.aspx

    How much of your 116gb do these queries look at?  That's just about how much RAM you could add and have it help.  Of course maybe better indexing or other schema changes would help.

    Actually, after you've fixed the memory limits, the next question is whether your query can be tuned by recoding (and new fields, new indexes), before you go buying any new hardware.  I was just deduping a 3gb table in about 3 seconds as long as it had an index on the dup field and a unique identity key.

    Josh

     

    Saturday, August 14, 2010 6:49 PM
  • SQL is using 10.75 GB
    Total RAM is 12GB
    Available RAM is 109 MB


    Collect this data for 5 mins in perfmon log during the load
    ---------------------------------------------------------------
    -> SQL Server working set and private bytes from perfmon (under process)
    -> for CPU I have already asked you to caprure if there is anything waiting (use perfmon)
    -> processor >> privileged time >> user time >> processor time.
    -> perfmon >> <instance> Plan cache .Give the full output from this for 5 mins
    -> perfmon >> <instance> mEMORY MANAGER .Give the Memory grants pending .5 mins
    -> perfmon >> <instance> Buffer manager .Give page life expectancy
    -> perfmon >> <instance> Wait statistics .Give all the output from it .

    Further give us these details
    -----------------------------
    -> the waittime and waittype of the query 60 times in loop.
    -> for now need not to change the max and nim server memory and let SQL dynamically use it.Otherwise how will you know how much SQL needs .
    -> check if you see this message "A significant part of SQL Server memory has been paged out"
    -> DBCC memorystatus output when the load is good.
    -> dbcc sqlperf(umsstats) and dbcc sqlperf(waittats) when the load is good.

    -> To find out HOW much SQL Server is paging please follw this link in my blog and show us the output .
    http://ms-abhay.blogspot.com/2008/02/understanding-memory-issues-through.html

    To know how your SQL memory is being used by SQL please follow this blog entry and send the output to me :
    http://ms-abhay.blogspot.com/2009/04/how-to-find-how-cachebpool-is-being.html

    Send all the data zipped @ abhay_c@hotmail.com or upload it on some fileshare website (many are there for free)

    Sorry for this , but I need this output for more analysis .

    Regards

    Abhay

    Sunday, August 15, 2010 5:51 AM
  • please take a dump for 2 mins for :

    dbcc sqlperf(umsstats) and dbcc sqlperf(waittats) when the load is good.

    regards

    Abhay

    Sunday, August 15, 2010 5:56 AM
  • 1 )

    if you have a non-clustered index on component id your sub-tree cost will be around 60% less than when you do not have it .
    (I have tested it in adventureworks)

    Adventurework test
    select distinct componentid into tmp_File from Production.BillOfMaterials


    2)

    If you have a non-clustered index on DM_FileURN and PURN your sub-tree cost would be 75% less.

    Adventurework test
    select distinct componentid,perassemblyqty into tmp_File from Production.BillOfMaterials

    3)
    since you have distinct values in tmp_FilelistforPCount table , create a clustered index on this table .But test it since it might take a lot of time to do clustered index insert.

    4) are the columns a.PURN = b.PURN sorted (this will avoide nested loop join and will go for merge join)

    5) could you please share the execution plan for

    set @sql = 'update a ' +'set '+@col+ ' = ''Y''' +' from DM_AllData_Master_Ranked_Counts_P a, DM_AllData_Master_Ranked b '+' where a.PURN = b.PURN and ' +'b.DM_FileURN = '''+@col+''''
    exec(@sql)


    and

    delete from tmp_FilelistforPCount where dm_fileurn = @col

    Sunday, August 15, 2010 10:01 AM
  • Here is a sample query we are running currently, to match records back to the files they can be found in.

    Please feel free to critique..

    ...

     

    The current query above looks at about 40gb of the db

    Goodness.  I don't see any deduping going on, other than the distinct.  And I am unable to imagine the purpose of that loop, but that shouldn't be the slow part.  Are you certain the code you showed is correct?

    One quick thing, you can probably cut the load by a bunch just by reversing the order of those first two selects, with a little change, right?

    select
    distinct DM_FileURN, PURN -- , AURN, EURN
    into DM_AllData_Master_Ranked_Counts_P
    from DM_AllData_Master_Ranked 
    
    select
    distinct DM_FileURN
    into tmp_FilelistforPCount
    from DM_AllData_Master_Ranked_Counts_P
    -------^^^^^^^^^
    
    
    

    Josh

     

    Sunday, August 15, 2010 2:39 PM
  • Thanks Josh. Yes, you are correct, that was not a deduping query running. It was the current one, which is also running slowly.
    
    Here is one of the dedupe queries...
    
    drop procedure DM_Phone_Dedupe_2
    go
    create procedure DM_Phone_Dedupe_2
    
    as
    
    begin
    
    declare @ProcessName varchar(100),
    @Progress varchar(200)
    
    set nocount on
    
    set @ProcessName = 'DM_Phone_Dedupe_2'
    
    set @Progress = 'Started...'
    exec DM_Report_Progress @ProcessName, @Progress
    
    
    /****************************************************************************************************************/
    declare @DM_TableCnt1 int,
    @DM_PhoneNumber varchar(7999),
    @SQL varchar(7999),
    @DM_PhoneURN varchar(10)
    
    set @Progress = 'Running 1st loop...'
    exec DM_Report_Progress @ProcessName, @Progress
    
    
    select @DM_TableCnt1 = COUNT(distinct DM_PhoneURN) from DM_Phone_Dedupe_Working_tmp (nolock)where isnumeric(ISNULL(DM_PhoneNumber,'')) = 1 
    --set @DM_TableCnt1 = 5
    while @DM_TableCnt1 <> 0
    	begin
    
    		select top 1 @DM_PhoneNumber = DM_PhoneNumber from DM_Phone_Dedupe_Working_tmp (nolock) where isnumeric(ISNULL(DM_PhoneNumber,'')) = 1 -- <> '' AND DM_PhoneNumber <> '0' -- is not null 
    		select top 1 @DM_PhoneURN = DM_PhoneURN from DM_Phone_Dedupe_Working_2 (nolock) where DM_PhoneNumber = @DM_PhoneNumber 
    
    --select 'no ' + @DM_PhoneNumber
    --select 'urn '+ @DM_PhoneURN
    
    set @Progress = 'Updating Daytime Phone...'
    exec DM_Report_Progress @ProcessName, @Progress
    
    set @SQL = 'insert into DM_AllData_Master_Ranked_Tel (DM_PhoneURN, DM_FileURN, DM_TableSRN, DM_PhoneNumber) ' +
    		'	select '''+@DM_PhoneURN+ ''',a.DM_FileURN, a.DM_TableSRN,'''+@DM_PhoneNumber+''''+
    			' from DM_AllData_Master_Ranked (nolock) a left join DM_AllData_Master_Ranked_Tel b '+
    			' on a.DM_FileURN = b.DM_FileURN and a.DM_TableSRN = b.DM_TableSRN ' +
    			'where a.DM_FileURN <> ''LL_124'' and b.DM_TableSRN is null and (a.DaytimePhone = ''' +@DM_PhoneNumber+ '''' +
    ' or a.EveningPhone = ''' +@DM_PhoneNumber+ '''' +
    ' or a.MobileNo = ''' +@DM_PhoneNumber+ '''' +
    'or a.Telephone = ''' +@DM_PhoneNumber+ '''' +
    'or a.Telephone2 = ''' +@DM_PhoneNumber+ '''' +
    'or a.Telephone3 = ''' +@DM_PhoneNumber+ '''' + ')'
    exec(@SQL)			
    --print @SQL
    
    set @Progress = 'Deleting from DM_Phone_Dedupe_Working_tmp...'
    exec DM_Report_Progress @ProcessName, @Progress
    
    		set @SQL = 'delete from DM_Phone_Dedupe_Working_tmp where DM_PhoneNumber = '''+@DM_PhoneNumber+''''
    		exec (@SQL) 
    
    --		select @DM_TableCnt1 = COUNT(distinct DM_PhoneURN) from DM_Phone_Dedupe_Working_tmp (nolock) where isnumeric(ISNULL(DM_PhoneNumber,'')) = 1 
    
    set @DM_TableCnt1 = @DM_TableCnt1 - 1		
    	end
    
    /****************************************************************************************************************/
    /*
    set @Progress = 'update blank phone urn...'
    exec DM_Report_Progress @ProcessName, @Progress
    
    -- need to give blank phone number a urn
    update a
    set a.DM_PhoneURN = b.DM_PhoneURN
    from tmp_Phone_Dedupe_Stage1 a (nolock), DM_Phone_Dedupe_Working_tmp b (nolock)
    where replace(a.DaytimePhone, ' ', '') = b.DM_PhoneNumber and
    a.DM_PhoneURN is null
    
    update a
    set a.DM_PhoneURN = b.DM_PhoneURN
    from tmp_Phone_Dedupe_Stage1 a (nolock), DM_Phone_Dedupe_Working_tmp b (nolock)
    where replace(a.EveningPhone, ' ', '') = b.DM_PhoneNumber and
    a.DM_PhoneURN is null
    
    update a
    set a.DM_PhoneURN = b.DM_PhoneURN
    from tmp_Phone_Dedupe_Stage1 a (nolock), DM_Phone_Dedupe_Working_tmp b (nolock)
    where replace(a.MobileNo, ' ', '') = b.DM_PhoneNumber and
    a.DM_PhoneURN is null
    
    update a
    set a.DM_PhoneURN = b.DM_PhoneURN
    from tmp_Phone_Dedupe_Stage1 a (nolock), DM_Phone_Dedupe_Working_tmp b (nolock)
    where replace(a.Telephone, ' ', '') = b.DM_PhoneNumber and
    a.DM_PhoneURN is null
    
    update a
    set a.DM_PhoneURN = b.DM_PhoneURN
    from tmp_Phone_Dedupe_Stage1 a (nolock), DM_Phone_Dedupe_Working_tmp b (nolock)
    where replace(a.Telephone2, ' ', '') = b.DM_PhoneNumber and
    a.DM_PhoneURN is null
    
    update a
    set a.DM_PhoneURN = b.DM_PhoneURN
    from tmp_Phone_Dedupe_Stage1 a (nolock), DM_Phone_Dedupe_Working_tmp b (nolock)
    where replace(a.Telephone3, ' ', '') = b.DM_PhoneNumber and
    a.DM_PhoneURN is null
    */
    /****************************************************************************************************************/
    /*
    set nocount on
    
    set @Progress = 'Running loop...'
    exec DM_Report_Progress @ProcessName, @Progress
    
    declare @DM_TableCnt int,
    @TableName varchar(200),
    @TableName2 varchar(200)
    
    --select @DM_TableCnt = COUNT(distinct DM_TableName) from tmp_Address_Dedupe_Stage1 
    select @DM_TableCnt = COUNT(distinct TableName) from DM_TableLog where Ph_Processed is null
    --select top 1 @TableName = TableName from tmp_DM_TableLog where Processed is null order by TableName
    
    while @DM_TableCnt <> 0
    	begin
    		print @DM_TableCnt
    		select top 1 @TableName = DM_TableName from tmp_Phone_Dedupe_Stage1 order by DM_TableName 
    
    		set @Progress = 'Processing ' +@TableName
    		exec DM_Report_Progress @ProcessName, @Progress
    		
    		set @Progress = 'Updating DM_AllData_Master_Ranked...' 
    		exec DM_Report_Progress @ProcessName, @Progress
    
    		set @SQL = 'update a
    					set a.DM_PhoneURN = b.DM_PhoneURN
    					from DM_AllData_Master_Ranked a, tmp_Phone_Dedupe_Stage1 b
    					where a.DM_TableName = b.DM_TableName and
    					a.DM_TableSRN = b.DM_TableSRN and
    					a.DM_PhoneURN is null and 
    					a.DM_TableName = '''+@TableName+''' '
    		exec (@SQL) 
    
    		set @Progress = 'Deleting from tmp_Phone_Dedupe_Stage1 ' 
    		exec DM_Report_Progress @ProcessName, @Progress
    
    		set @SQL = 'delete from tmp_Phone_Dedupe_Stage1 where DM_TableName = ''' +@TableName+''''
    		exec (@SQL) 
    
    		set @TableName2 = replace(replace(@TableName, '[',''),']','')
    
    		set @Progress = 'Updating DM_TableLog...' 
    		exec DM_Report_Progress @ProcessName, @Progress
    
    		set @SQL = 'update a
    					set a.Ph_Processed = ''Y'' 
    					from DM_TableLog a
    					where a.TableName = '''+@TableName2+''' '
    		exec (@SQL) 
    
    		set @DM_TableCnt = @DM_TableCnt - 1
    		
    	end
    
    */
    set nocount off
    
    /***************************************************************************************************************************************/
    
    set @Progress = 'Finished.' 
    exec DM_Report_Progress @ProcessName, @Progress
    
    end
    
    
    Sunday, August 15, 2010 3:23 PM
  • Well, ... how to say this ... you might look up in some SQL textbooks how to go about eliminating duplicates all in one statement.  Glancing at your code, I would think this should be possible.  I don't even see a need for dynamic SQL even as written, though that shouldn't make a big difference.  It's a bit like you're going out of your way to write the least efficient possible code.  But a thirty dollar book should help you more than fifty grand more in hardware.

    Josh

     

    Sunday, August 15, 2010 3:45 PM
  •  

    The query is that bad?

    Could you give me some pointers as to where it is wrong and how it can be improved?

    Sunday, August 15, 2010 5:42 PM
  • The query is that bad?

    Could you give me some pointers as to where it is wrong and how it can be improved?

    Truthfully ... yes, it's that bad.

    This is a pretty basic SQL skill.

    The first hint is the loop - if you're even average competent in SQL, you should see pretty nearly zero use for loops, unless you have to call sequential external APIs.

    De-duping is another.

    Google is your friend.  Or even, Bing!

    As you can see, there is a lot written about it.

    http://social.msdn.microsoft.com/Search/en-US?query=delete%20duplicate%20records&ac=8

    let's see if someone will just show us a basic version ...

    http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=DuplicateRows

    (be sure to read the comments for refinements)

    ohmigoodness, look at this one!  if you lack a primary key or other uniquifier, delete all with the key and put one back!

    http://support.microsoft.com/kb/139444

    (I'd prefer to add a uniquifier, if at all possible)

     

    here's a rather clever if not necessarily terribly efficient method, hey I didn't even know this would work:

    http://social.msdn.microsoft.com/forums/en-us/transactsql/thread/A967B575-F348-4DBC-AFEB-EB42852FF502

    Or maybe it is efficient?  Maybe I'll try it on Monday!

    Good luck, and congratulations on your new skill! (you see, I learned something myself just looking around about this!)

    Josh

    ps - good code will probably run 1000x faster than your existing code. 

    pps - that's unless you have a *lot* of dups, like 50% or more of your data, in which case good code might only run 10x or 100x faster.

     

    Sunday, August 15, 2010 6:30 PM
  • Hi,

    I agree query optimisation is core more often than not.

    I recommend Microsoft SQL Server 2008 T-SQL Fundamentals (PRO-Developer) , also I recommend creating or reviewing the Management -> Maintenance Plans after optimising the T-SQL; - http://technet.microsoft.com/en-us/library/ms189036(SQL.100).aspx

    In general when focusing on speeding up databases I think it's best to focus on what you can't kick first (software) within a reasonable time frame, then when these options are exhausted focus on what you can kick (hardware).

    I hope this helps,

    Kind Regards,

    Kieran.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Wood PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
    Monday, August 16, 2010 6:05 PM