July 9, 2008 08:09am pdt

1. bleeuwen 96,665
2. larstr 86,759
3. jfields71 60,948
4. BestWay 49,095
5. Halindar 45,324
6. arrkerr1024 33,536
7. Stappmeyer 29,910
8. meyersd 28,665
9. bhanukir7 28,249
10. agriesser 24,302
11. robocat 23,750
12. S00007359 21,190
13. SagiEDoc 17,582
14. rvthost 15,960
15. tatluvr 15,600

1. bleeuwen 98,665
2. larstr 86,759
3. BestWay 69,585
4. jfields71 60,948
5. arrkerr1024 49,476
6. Halindar 45,324
7. rvthost 43,710
8. S00007359 35,753
9. meyersd 33,165
10. robocat 30,300
11. Stappmeyer 29,910
12. bhanukir7 29,149
13. rindi 26,568
14. agriesser 24,302
15. 65td 18,940

05.15.2008 at 01:19PM PDT, ID: 23406564

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

8.0

HowTo Optimize the Perfomance of an SQL 2005 Server ?

Zones: SQL Server 2005, Windows 2003 Server, VMWare

Tags: Microsoft, SQL, 2005, Optimization

Hello,

i have a quite general questions:

We have some performance issues with our SQL 2005 Server and are looking for possible causes.

Here some details about our Test Setup ( we've got 3 systems ):
- Windows 2003 R2 x86
- SQL 2005 SP2 x86
- 2 - 4 GB Memory
- 4 GB SWAP ( own Disk )
- Logs, Database and System files are all on seperate Disks located on a high speed SAN.
- approx. 30 Instances per Server
- approx. 50 %- 60% overall CPU Usage
- we have 1 ( single cpu ), 1 ( dual cpu ), and 1 ( quad cpu ) systems all with the same setup as stated above
- The Databases are application specific databases like WSUS and so on ( so we can't optimize there structure or indexes )

What are we looking for ?
Just some Hints, Tipps and maybe important SQL Settings which could help us to get hold of our Performance Problems.
Maybe there are some known caveeats and frauds which someone could
unwillingly do to reduce the performance.

Questions which YOU could answer:

"Is it wise to restrict an Instance to a specific amount of Memory ? e.g. 256MB ?"

"Isn't the Default Setting of 1MB per SQL Query too high ?"

"When i Setup a Windows 2003 Server with one CPU and add a second CPU later,
does the System HAL recognize this change correctly and make use of the new SMP features ?"

"What is most important for an SQL 2005 Server, having more Memory or more CPU's ?"

with best regards,

FireArmz

Start your free trial to view this solution

Question Stats

Zone: Software

Question Asked By: firearmz

Solution Provided By: larstr

Participating Experts: 5

Solution Grade: A

Translate:

Loading Advertisement...

Microsoft

Internet Protocols
Applications

Development
OS

Hardware
Windows Security

Apple

Operating Systems
Hardware

Programming
Networking

Software

Internet

Search Engines
File Sharing
WebTrends / Stats
Spy / Ad Blockers

Web Browsers
New Net Users
Web Development
Chat / IM

Anti Spam
Web Servers
Anti-Virus
Email Clients

Gamers

Tips
Online / MMORPG
Puzzle
Emulators

Action / Adventure
Role Playing
Consoles
Game Programming

Strategy
Sports
Misc
Computer Games

Digital Living

Hardware
New Net Users
New Users

Software
Digital Music
Gaming World

Home Security
Apple
Networking Hardware

Virus & Spyware

Vulnerabilities
IDS
Encryption
Anti-Virus

Operating Systems Security
Software Firewalls
WebApplications
Cell Phones

Operating Systems
Internet
Hardware Firewalls

Hardware

Handhelds / PDAs
Displays / Monitors
Components
Networking Hardware

Peripherals
Laptops/Notebooks
Storage
Servers

Desktops
New Users
Misc
Apple

Software

System Utilities
Industry Specific
Network Management
Photos / Graphics
Page Layout
VMWare
Misc
Web Development

OS
CYGWIN
Voice Recognition
Message Queue
Quality Assurance
Security
Firewalls
MultiMedia Applications

Development
Database
Office / Productivity
Business Management
OS/2 Apps
Server Software
Internet / Email

ITPro

OS
Storage
Encryption
Operating Systems Security
Apple Hardware
Laptops & Notebooks
Servers
Networking Hardware
Peripherals

Devices
Displays / Monitors
WebTrends / Stats
Search Engines
Firewalls
WebApplications
IDS
Vulnerabilities
Email Clients

File Sharing
Spy / Ad Blockers
Web Browsers
Web Servers
Networking
Anti-Virus
Chat / IM
Anti Spam

Developer

Web Servers
Web Browsers
Game Programming
Dev Tools
Industry Specific
Office / Productivity

Database
CYGWIN
Web Development
Search Engines
File Sharing
WebTrends / Stats

Programming
Content Management
Application Servers
Protocols

Storage

Removable Backup Media
Storage Technology
Servers

Grid
Remote Access
Backup / Restore

Misc
Hard Drives

Miscellaneous
Security
Development
Linux
VMWare

MainFrame OS
Unix
Apple
OS / 2
AS / 400

BeOS
Microsoft
VMS / OpenVMS

Database

Oracle
Miscellaneous
MySQL
Software
Sybase
Contact Management
PostgreSQL
Data Manipulation
Clarion
InterSystems Cache

Siebel
MUMPS
OLAP
SQLBase
SAS
GIS & GPS
4GL
Berkeley DB
DB2
Informix

Interbase / Firebird
FoxPro
Reporting
LDAP
Filemaker Pro
MS SQL Server
dBase
MS Access

Security

Misc
Web Browsers
Software Firewalls
Operating Systems Security
File Sharing

Spy / Ad Blockers
Vulnerabilities
WebApplications
IDS
Anti-Virus

Encryption
Anti Spam
Email Clients
VPN
Chat / IM

Programming

Editors IDEs
Installation
Handhelds / PDAs
Multimedia Programming
System / Kernel

Algorithms
Game
Signal Processing
Project Management
Open Source

Database
Misc
Languages
Processor Platforms
Theory

Web Development

Scripting
Blogs
Web Servers
Software
Search Engines
Web Graphics
Images

Internet Marketing
Images and Photos
Components
Document Imaging
Web Languages/Standards
Illustration
WebApplications

Fonts
WebTrends / Stats
Authoring
Digital Camera Software
Miscellaneous

Networking

Protocols
Apple Networking
Network Management
Message Queue
Application Servers
Content Management
File Servers
Email Servers
Misc
Java Editors & IDEs

Wireless
Networking Hardware
Backup / Restore
System Utilities
ISPs & Hosting
Web Servers
Storage Technology
Removable Backup Media
Servers
Broadband

Grid
OS / 2
Novell Netware
Unix Networking
Windows Networking
Security
Telecommunications
Operating Systems
Linux Networking

Other

Community Advisor
Lounge
Community Support
New Net Users

Philosophy / Religion
Math / Science
Miscellaneous
URLs

Expert Lounge
Politics
Puzzles / Riddles

Community Support

Suggestions
New to EE
New Topics
Community Advisor

CleanUp
Announcements
General

Feedback
Input
EE Bugs

05.15.2008 at 01:30PM PDT, ID: 21577687

Rank: Wizard

dqmq:

>"What is most important for an SQL 2005 Server, having more Memory or more CPU's ?"

SQL Server loves memory. So unless your CPU is pegged out, you're waiting on disk or memory.

05.15.2008 at 03:18PM PDT, ID: 21578426

tatluvr:

It does love memory, but SQL Querys utilize gobs of CPU usage. Processor power has always been the slow point when it comes to querys to a SQL Server from any app.

I am curious why is this question in the VMWare section....is this a SQL running in a VM on an ESX box?

Assisted Solution

05.15.2008 at 03:35PM PDT, ID: 21578513

Rank: Guru

SysExpert:

Have you added / /4G in the boot.ini to get an added 1 GB RAM for the SQL server on machines with at least 3 GB RAM.

Make sure all machines have 4 GB and use the /3GB switch,

I hope this helps !

Assisted Solution

05.15.2008 at 03:37PM PDT, ID: 21578519

Rank: Guru

SysExpert:

In addition, run Perfmon on each server and the statistics for SQL, to find the bottleneck.

I hope this helps !

Assisted Solution

05.16.2008 at 06:04AM PDT, ID: 21582292

rlgreene:

Since this question is posted in the VMware zone I am assuming that your SQL server is running on a virtual server. Because of the potential of High CPU and High I/O utilization SQL servers do not run well in VM environments.

05.16.2008 at 01:05PM PDT, ID: 21585987

firearmz:

Hello Experts,

sorry for the long delay, work kept me busy.

Yes you where absolutly right, the SQL Server are running on an ESX Server.
For testing purpose i've put each VM on its OWN ESX Server.
Just to see if this makes a difference.
But it didnt changed that much.
Initially i wanted to make this a VMware specific question but i feared that this would keep you from posting "normal" tipps like /3GB.

The Hint with the /3GB Switch ist great.
I'll add this on our 32Bit Systems. I know that switch but simply forgot about it.

I'm already using Perfmon to Monitor the results of DBHammer Benchmarks.
I'll setup a continous Monitoring for the following Keys:
- Transactions per Second
- Memory - Pages per Second
- CPU - Processor _Total %
Anything else i should Monitor ?

From what the Task Manager is telling me, it looks like the Memory is the Bottleneck.
The Main Memory ( 2GB for example ) and the Swap File ( 2GB )
of an affected Server are both at 100%.
The ESX Server itself Shows the VM Memory usage as approx 40%.

New Question:
Is there some recommendation or formula which could help me to show
my collegues that we need more Servers or Server with more Memory ?

Remember:
We've running around 40 Instances.

Because im actually working on this issue right now,
i'll keep you updated and i can tryout almost all recommendation immediatly.

with best regards,

FireArmz

05.16.2008 at 06:38PM PDT, ID: 21587669

Rank: Wizard

dqmq:

>Anything else i should Monitor ?

Disk I/O - queue depth (length?) is good

05.17.2008 at 12:06AM PDT, ID: 21588246

firearmz:

Hellp dgmg,

thanks, i'll put this into monitoring.
Is there any orientation of what value is "good" ?
I think if it is an I/O Queue, smaller values will be better.

Does someone has an idea why ESX is telling me that the Guest System is only using 40% of its Memory and the VM Guest OS is screaming for more memory ?!

with best regards,

FireArmz

05.18.2008 at 09:52AM PDT, ID: 21593255

Rank: Wizard

dqmq:

>I think if it is an I/O Queue, smaller values will be better.

Yes. That's the number of IO requests that are stacked up waiting others to complete.

It's impossible to give a target number because there are too many variables. But, you can compare numbers from times when performance is good to times when performance is bad and see if IO wait has changed.

05.21.2008 at 12:50PM PDT, ID: 21618422

firearmz:

Hello,

so i've did some testing.

The exact results are at work, but what i've got so far is:
I/O Queue length is in a normal range.

No matter what i do, i can't get the server to offer more than ~6500 Transactions per second.
I've used DBHammer to stress test the server. I've ran DBHammer from 3 different systems at the same time.
Is there a limit that keeps the Transaction per second limited ?
We are running SQL 2005 Standard 64Bit on this System.

All parameters like Network, Disk, CPU and Memory are not at there limits.
I've did different tests using tools like IOMeter to prove that the System can offer more performance in any of the stated fields.

with best regards,

FireArmz

05.23.2008 at 04:57AM PDT, ID: 21631197

Rank: Master

larstr:

FireArmz,
# What SAN solution are you using?
# can you monitor any parameters on your SAN? Are you able to read the cache hit ratio for these LUNs?

SQL Server is very disk intensive and will also put a very mixed workload on your disk system. Because of this you should use RAID1+0 on LUNs that are being used for SQL workloads.

Lars

05.23.2008 at 04:59AM PDT, ID: 21631208

Rank: Master

larstr:

FireArmz,
Did you also compare your iometer results with those in the Open Inofficial Storage performance thread?
http://communities.vmware.com/thread/73745

What results did you get if you run iometer with those same parameters as specified there?

Lars

Accepted Solution

06.13.2008 at 02:18AM PDT, ID: 21777135

firearmz:

Hello,

i tooked some time to conduct all the needed Benchmarks.

Here is what did the Trick.

We installed new Windows 2003 / SQL 2005 Server with 4 CPU already attached during the Installation.
I think because of this, windows choose the right HAL Model for Multi Processing.
( Keep in Mind our previous SQL Server where installed, only with one CPU attached )
Now our System always Hits the Top of the Benchmarks Scale.

with best regards,

FA

20080236-EE-VQP-29 / EE_QW_2_20070628