Adding memory slow down the query result

Question

Adding memory slow down the query result

balbirsinghsodhi

SSCrazy Eights

Points: 9210
More actions
March 21, 2008 at 5:16 pm

#124463

Adding more memory on machine will slow down the Update Statistics, amazing...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Explain, please.

[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]

Mohan Kumar SSCertifiable Points: 5365 More actions · Answer 2

While explaining please make sure you mention sql server version too.

[font="Verdana"]--www.sqlvillage.com[/size][/font]

sqlagentman SSCarpal Tunnel Points: 4484 More actions · Answer 3

Also provide us with O/S and SP settings as well as 64 v. 32 bit and SQL Edition and SP. Also confirm your memory settings and whether the SQL service has local policy set to Lock Pages In Memory and depending upon SQL/OS settings, whether AWE is enabled. Also, is this a dedicated SQL server? If not, what else is running on it?

Lastly, what was the initial amount of RAM and how much more did you add?

- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford

sadowy Valued Member Points: 58 More actions · Answer 4

Looks like this topic has been dead for a while, but the subject seems to match what I am seeing.

We thought that the hardware of our existing sql server may have been a bottleneck in our new implementation of our web site w/ sql server. Current server:

1 - 3 GHz Quad core xeon processor

3 Gig memory

SQL Server 2008

Windows Server 2003 R2 Standard 32 Bit

Stand alone server with no other apps running on it.

As a test case, I created a new virtual server for our beta site to test on:

4 processors

12G memory

SQL Server 2008

Windows Server 2008 Enterprise 64 Bit

This is the only vm running on this host. The host is dual CPU quad core w/ 24G memory.

We noticed that many text based querries ran faster, but a spatial query that took 1 sec on our production machine ended up taking 3 mins on our test machine. I noticed that the execution plan was different between the production and test sql servers. I tried a reboot of the test sql server to see if a fresh memory state would improve the response, but no difference after reboot.

On a hunch, I lowered the memory to both 4G and 8G on the test machine, and lo and behold, the spatial query returned the results in ~1 sec like the production machine. The execution plan also mimicked the production machine after the memory change.

Does it make any sense that an SQL server would have better performance with less memory?

Yes, the indexes on both servers are identical, and yes, both spatial tables have spatial indexes.

Here is the query that we were running:

DECLARE @MyBuffer geography;

DECLARE @MyNewRegion geography;

SELECT @MyBuffer = geography::STGeomFromText('POLYGON ((-118.14624895188734 34.201738209991156, -118.1463976609366 34.201785843130949, -118.14653982671638 34.2014781014831, -118.14664690335094 34.201246298484328, -118.14650157055577 34.201199870627896, -118.14639300457549 34.20143123564, -118.14624895188734 34.201738209991156))', 4326);

(SELECT bm.APN as description, bm.geom.STAsText() AS Poly, 'TEST' as MyCounty, bm.ID

FROM [MY_Basemap] bm

WHERE (GEOM.STIntersects(@MyBuffer.STBuffer(152.3999951232)) = 1))

ORDER BY MyCounty, description

Thanks!

Nick

Megistal SSCrazy Eights Points: 8787 More actions · Answer 5

The best advice I could say is check the query plan for both and compare.

That will tell you what has happened.

In one scenario you might have a join task sooner in the task hierarchy which filter a larger table later in the query plan and in the other plan that task join is done later for X reasons, than the larger table get scanned completely instead of being seek.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

This is unusual, but not unheard of. In order to determine the specific reasons though, we would need to compare the actual execution plans for both query executions.

[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]