SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Server tuning


Server tuning

Author
Message
grahamc
grahamc
Right there with Babe
Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)

Group: General Forum Members
Points: 744 Visits: 1039
Ok, we all know that we can change SQL Server settings to improve performance and database tuning to improve specific databases.....

Now what do we do with the servers and the OS themselves?
Dedicated SQL Server (new box)
Windows 2008 64bit Enterprise Edition
SQL Server 2008 64bit Enterprise Edition
SAN attached drives
128GB memory

going to be be around 600-700GBs of DBs transactional.

My list so far is (all require researching and testing):

pagefile - 1.5x physical memory still the base standard? obviously I dont want SQL paging
power management
background services
hyperthreading
limiting the max memory of SQL server

What else should be added to my list?
Carl Federl
Carl Federl
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3274 Visits: 4350
Windows:
Insure that "Maximize data throughput for network applications" is set instead of the default of "Maximize data throughput for file sharing"

Grant the SQL Server service account the following Windows privileges:
Perform volume maintenance tasks
Lock pages in memory

Limit the size of the System File Cache because the default is half of the physical memory.

Determine if the Windows Denial Of Service settings should be changed.

SQL Server:
Move TempDB to its own drive and add additional equal size data files.
Set the Default Database Path
Create alerts for severities 17 through 25.

SQL = Scarcely Qualifies as a Language
grahamc
grahamc
Right there with Babe
Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)

Group: General Forum Members
Points: 744 Visits: 1039
Carl Federl (10/19/2010)

Windows:
Insure that "Maximize data throughput for network applications" is set instead of the default of "Maximize data throughput for file sharing"

Grant the SQL Server service account the following Windows privileges:
Perform volume maintenance tasks
Lock pages in memory

Limit the size of the System File Cache because the default is half of the physical memory.

Determine if the Windows Denial Of Service settings should be changed.

SQL Server:
Move TempDB to its own drive and add additional equal size data files.
Set the Default Database Path
Create alerts for severities 17 through 25.


Will do some reading on the Windows items supplied... thanks

Already doing all the SQL ones, except for the Severity messages.
grahamc
grahamc
Right there with Babe
Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)Right there with Babe (744 reputation)

Group: General Forum Members
Points: 744 Visits: 1039
anything else?
jamankhan1016
jamankhan1016
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1
Data Tuning results from analyzing queries (using EXPLAIN or other similar tools) to identify bottlenecks and other issues slowing down processing. Tuning the database means tweaking the structure to try and speed up processing. Probably the most common method is to add additional indexes to often-used tables. Analysis and modification of logging files may also increase performance, as may replacing triggers and constraints with explicitly-invoked procedures. Less frequent methods may include denormalization of nonvolatile data to eliminate need for joins.


Online shopping at Chinabuye for dropship electronics Service
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search