.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Can anyone explain how SQL Server performance scales with CPUs/Cores, memory, disk drives, etc?

Posted By:      Posted Date: September 10, 2010    Points: 0   Category :Sql Server
I have experienced the situation where our application running on an expensive system with two 4 core CPUs ran slower than on a cheap machine with one dual core CPU. Both machines had similar memory capacity. In each case, the same query was being run by only one user. It would be nice to find a way to increase performance, but if adding cores doesn't help, what can a poor developer do? Customers get annoyed when they spend a truckload of money on a new server and nothing changes... Another potential problem is when customers have an expensive SAN system Sometimes the performance is much slower then on a system with conventional storage. Can anyone explain the relationship between SQL Server perfomance and hardware ?

View Complete Post

More Related Resource Links

SQL Server 2008 R2 on Windows 2008R2 Ent - Drives+Raid Configuration + W2008 Disk Initialization


Hi, I just want to make sure that we setup this server the best way possible as we are moving from a previous box where

performance was affected mainly because of partitioning and SQL memory optimizations.


The Hardware:

Dell PowerEdge R710 

  • Intel® Xeon® X5660, 2.8Ghz, 12M Cache,Turbo, HT, 1333MHz
  • 64GB Memory (8x8GB), 1333MHz Dual Ranked RDIMMs
  • PERC H700 Controller
  • 8 x 300GB 10K RPM Serial-Attach SCSI 6Gbps 2.5in Hotplug Hard Drive
  • Virtual Volume 1: 2 Drives = Raid 1 - for the OS
  • Virtual Volume 2: 2 Drives = Raid 1 - for the ldf's
  • Virtual Volume 3: 4 Drives = Raid 10 - for the mdf's

Windows 2008R2 64bits is installed on Volume 1.

- I would like to know what suggestions you might have for the Initialization of the other 2 volumes (MBR or GPT).

- Any other settings like partition align = 64

- Memory Settings at the OS level.

- Memory Settings on SQL.

- Any other recommendations.

SQL Server Performance

Improving .NET Application Performance and Scalability
SQL: Scale Up vs. Scale Out
Stored Procedures
Execution Plans
Deployment Considerations

file upload in chunks or not buffering in memory before writing to disk?


What are the options for handling file uploads to reduce the memory footprint?  Is there a way to upload in chunks?  Is there a way to stream upload directly to disk instead of loading entire file in server memory?


SQL Server: Uncover Hidden Data to Optimize Application Performance


SQL Server 2005 gathers a lot of useful data that you can use to identify areas where database performance can be improved. Here's what you need to improve performance.

Ian Stirk

MSDN Magazine January 2008

IIS 6.0: New Features Improve Your Web Server's Performance, Reliability, and Scalability


As the Web evolves, so does the role that Internet servers play. The Internet has seen the growth of e-commerce, B2B business, collaboration, streaming and other new media, and these new applications require new features to meet increasingly complex needs. Microsoft Internet Information Services (IIS) has many of the features today's mature Web sites need. This article outlines the features in the upcoming version 6.0 and discusses how they promote better scalability, reliability, and performance. Features such as Remote administration, caching, and metabase improvements, as well as custom isolation and security enhancements, make IIS 6.0 the Web server of the future.

George Shepherd

MSDN Magazine March 2002

ATL Server and Visual Studio .NET: Developing High-Performance Web Applications Gets Easier


When developing high-performance applications for the Web, developers often must choose between performance and ease of development. With ATL Server, new with Visual Studio .NET, developers get the best of both worlds. ATL Server uses a tag replacement engine written in C++, provides a simple programming model, and promotes enhanced performance and easy debugging. This article presents an overview of the ATL Server architecture, then creates a basic ATL Server project. It then goes on to explain processing SRF files, HTTP streams, forms, cookies, and header files. Managing session state is also discussed, along with file uploads and performance monitoring.

Shaun McAravey and Ben Hickman

MSDN Magazine October 2000

Designing and Developing High-Performance SharePoint Server 2007 Publishing Sites

Learn about the Web Content Management features in SharePoint Server 2007 that enable organizations to deploy Internet-facing publishing sites, and considerations for developers and designers when designing and building high-performance publishing sites.

SQL Server 2005 Performance Dashboard Reports user context

HI, We are planning on installing the Dashboard reports. But we'd like to know if the Dashboard is run under admin user or the login user who runs the Dashboard reports? Thanks! Kong

WmiPrvSE.exe causes a huge memory leak in SQL server 2008 R2, why?

My OS is windows server 2008 r2 data center 64 bit and my SQL server is server 2008 R2. The problem is when I open sql server, the WmiPrvSE.exe process consumes 20 - 60kbs every second. Several hours later, it eats up at least 200mbs memorry and this process seems like it will never stop. Can any experts give me some ideas to fit this mess? PS: My mainboard is Asus striker II formular, cpu is intel quad-core. Also, all my software are updated to latest version.

SQL server memory / speed configuration

Let me preface this by saying I'm aware of SQL server memory architecture, i.e it takes as much as it can get it's hand's on ...I've read through all the post's on here and they have been very helpful ... However if someone could take a look at these these configuration values ... I'm trying to get a new server dialed in, I'm still having some user end speed issues, and application hangup's that seem sql related ... I'm by no means a dba so if someone who's knowledgeable could point me in the right direction I would appreciate it ... I feel like something is not configured properly ... The box has Microsoft SQL Server 2008 (RTM) 64-bit on Windows NT 6.0 <X64> Service Pack 2 Dell Power Edge R10 16 Logical CPU, 8 Hyperthread 12gig of memory (Right now SQL has 10/12 gig allocated in the task manager ( I know this is not accurate ) I'm adding another 12 gig tomorrow ... So the machine will be running 24 gig) There's two 9 gig databases running on the machine with several 4-6 million row tables .... Server is SQL Dedicated Configuration Values   Page Life Expectancy 18,038 under moderate load, tho I've seen it as low as 1,200 under heavy load .... Buffer Pool Usage CACHESTORE_SQLCP 2778616 OBJECTSTORE_LOCK_MANAGER 38816 MEMORYCLERK_SOSNODE 20936 CACHESTORE_OBJCP 18992 CACHESTORE_PHDR 16472 USERSTORE_SCHEMAMGR 12184 USERSTORE_DBMETADATA 8264 USE

SQL Server Management Studio - Invalid access to memory location

In SQL Server Management Studio (for SQL Server 2008), when I try to open a new query window, or create a new database, I get a popup error box saying the following: "Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (mscorlib)" This just started happening in the last few days, and I have no idea why.  I even uninstalled SQL Server 2008, and then installed SQL Server 2008 R2, and continued to get the exact same error, in the exact same scenarios. Please let me know if you have seen this before, and what I might try to correct it. Thanks, Robert  

MDX query using linked server - performance question

Hi, I'm having a strange performance behavior. When I’m running a mdx query on management studio, it runs for 10 minutes (its mainly calc members on a large cube) When I run the same query via linked server and openquery, it runs for 20 minutes. Is the SQL from some reason does a parsing or running the query twice? The server is sql2008 UP1 on 64bit. and the "allow in process" is checked. its the default in sql2008 Thanks in advance, Yoav  

explain me how many functions are their in sql server with examples

explain me how many functions are their in sql server with examples ?

Max Server Memory Alteration..

I am running SQL Server 2005 Enterprise on Win Server 2003 SP1 32 bit with 16 GB RAM. I have both /3BG and /PAE enabled. AWE is enabled for SQL Server. I have set the Max Server Memory to 12 GB. When I check the perfmon counters, The Available MBytes is about 2.3 GB. This means that I am not using this 2.3 GB of my 16 GB memory. What I am thinking is that instead of keeping the /3GB switch, I will remove it and make the Max Server Memory to 13 GB. Will this be euqivalent to my prevoius configuration? Will my SQL Server get the same RAM as before? Anyways, what's the advice to achieve optimum utilization of RAM?

best practice SQL Server 2008R2 setup on 3 drives

Hi, I have read best practice lists for setting up SQL Server, but I don't have enough available drives to meet the optimal setup. I have 3 drives and below is how I plan on using my 3 drives: C (30GB) - Windows 2008 OS and SQL Program Files E (120GB) - SQL Master/Model/TEMPDB/MDF Files F: (60GB) - SQL LOG Files   Should I configure the above different?

SQL Server - Performance Collection

Hello: i have question in regards to collection sets when done in file format CSV for more than 4-5 days. usally when done for more than 3-4 days & Perfmon CSV file open in Excel is very misleading when viewed in graphical format. for e.g. when i see in graph format for processor time done for 4-5 days it shows like 90% usage but when doing AVERAGE for same counter show like 10-15%. is there better way to display same info in a correct graphical format. I am not interested in 3rd party tool & i know they can provide more accurate information. i am looking for defaults that comes with Microsoft. Any help is appreciated. Thanks  

SQL Server not releasing memory back!

Hi Guys,   I have done enough research on this topic and understood that SQL Server loves to cache as much memory as it can get hold of , but did not find a straight forward answer to my problem.   To explain things in detail, We are currently using SQL Server 2005 (64 Bit) on Windows 2003 (64 Bit) with 32 GB of RAM. This morning when I checked, it was showing the memory as 1GB RAM.  As soon as I stopped and restarted SQL Sever, I got back all 31GB of it! This is a live mirrored server, and the data is exposed through a .NET Win Application. Also, this is a dedicated SQL Server.   The min and max memory of the SQL Server is set at 16 and 2147483647 respectively.   My questions: 1. What can be done to make SQL Server release the memory back once the process is complete?   2. If this involves setting a limit to the max memory of the SQL Server, what can it be set to? And does setting the max memory low affect the performance of server?   3. I have other servers that are not dedicated SQL Servers which are showing the same 'memory' related problems, what should be the max memory setting on these ?   Any help in this regard is deeply appreciated.   Little_Birdie.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend