Configuring a SQL Server to optimize for best performance

Tags:
ODBC
SQL Server 2005
SQL Server performance
SSMA
Sybase
Windows Server 2003
Windows Server 2003 R2
We are running SQL Server 2005 on Windows Server 2003 R2 Enterprise Edition x64 SP2. These are Dell 2950 servers with 16 GB of RAM, and they are dedicated to SQL Server. We are mainly performing large database migrations utilizing SSMA and ODBC from Sybase. How can we configure Windows and/or SQL Server to best utilize the RAM and/or optimize for best performance?

Answer Wiki

Thanks. We'll let you know when a new response is added.

The only way to truly optimize performance is to monitor (using perfmon/profiler) and test.

You need someone with the expertise to do this, or someone who can learn the rudimentary tools and give it a shot. But, I would recommend either hiring a consultant (I can give some recommendations) or maybe using Microsoft’s “health checkup” services (from there support organization).

You could start with an analysis of the missing indexes. Your can find some great article out on the web about this. All ot the data for this is on a set of DMV’s (sys.dm_db_missing_index*).

The next place to look is your storage layout – to make sure you have the right number of and type of disks in you storage framework (local RAID, NAS or SAN). One of the biggest problems that people face is only looking at the amount of space, and not taking the time to characterize the IO behavior and needs and match them with the right number of disk heads to make sure you are not IO constrained. Contact your storage vendor (EMC or NetApps?) and convince them that you want to evaluate your storage – and they may have a service where they will take some perfmon output from your system and recommend the right IO configuration.

As you can probably see from my answer, this is not a simple task, and it will take time, understanding of your systems behavior, how SQL Server works, and detailed analysis.

—————————–
Here is a missing index query I use. The base code was borrowed from someone else (I wish I could remeber to give them credit):
SELECT S.AVG_TOTAL_USER_COST * (S.AVG_USER_IMPACT / 100.0) * (S.USER_SEEKS + S.USER_SCANS) AS EST_IMPROVE,
S.AVG_USER_IMPACT,
LEFT (PARSENAME(D.STATEMENT, 1), 32) TABLE_NAME,
ISNULL (D.EQUALITY_COLUMNS,”) + CASE WHEN D.EQUALITY_COLUMNS IS NOT NULL AND D.INEQUALITY_COLUMNS IS NOT NULL THEN ‘,’ ELSE ” END + ISNULL (D.INEQUALITY_COLUMNS, ”) COLUMN_NAMES,
ISNULL (D.INCLUDED_COLUMNS , ”) AS INCLUDE_COLUMNS,
S.UNIQUE_COMPILES, S.USER_SEEKS, S.USER_SCANS, S.LAST_USER_SEEK, S.LAST_USER_SCAN, S.AVG_TOTAL_USER_COST, S.SYSTEM_SEEKS, S.SYSTEM_SCANS, S.LAST_SYSTEM_SEEK, S.LAST_SYSTEM_SCAN, S.AVG_TOTAL_SYSTEM_COST, S.AVG_SYSTEM_IMPACT
FROM SYS.DM_DB_MISSING_INDEX_GROUPS G
INNER JOIN SYS.DM_DB_MISSING_INDEX_GROUP_STATS S ON S.GROUP_HANDLE = G.INDEX_GROUP_HANDLE
INNER JOIN SYS.DM_DB_MISSING_INDEX_DETAILS D ON G.INDEX_HANDLE = D.INDEX_HANDLE
–WHERE S.AVG_TOTAL_USER_COST * (S.AVG_USER_IMPACT / 100.0) * (S.USER_SEEKS + S.USER_SCANS) > 10
ORDER BY EST_IMPROVE DESC

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • SuperCoolMoss
    How can we configure Windows and/or SQL Server to best utilize the RAM and/or optimize for best performance? I'd suggest allocating a maximum of 15GB to SQL server, leaving 1GB for the OS (assuming it's a dedicated SQL box). If you have anything else running on the box it may be worth erring with caution and give 14GB max to SQL, leaving 2GB to the OS. Regards, SCM.
    140 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following