SQL server 2008/2005 Tuning
I am having a look at SQL server parameters which I can tune to optimize database and not having much luck.
When I execute
SELECT * FROM sys.configurations
ORDER BY name ;
GO
I see list of 70 parameters but is that all we have?
Which config parameters one should look at for configuring a production server of database 150GB and between 250-500 concurrent connections?
Regards
SQlServer-Tuning-Parametres.png
When I execute
SELECT * FROM sys.configurations
ORDER BY name ;
GO
I see list of 70 parameters but is that all we have?
Which config parameters one should look at for configuring a production server of database 150GB and between 250-500 concurrent connections?
Regards
SQlServer-Tuning-Parametres.png
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
On the long list of dtodd I will add some more
multiple files in tempdb (http://www.mssqltips.com/sqlservertip/1980/sql-server-tempdb-one-or-multiple-data-files/), (filtered) indexes, partioned tables, statistics.
Following the hartbeat of your system and gradualy know where there could be improvement you do by loocking at your DMV's (dynamic management views)
http://www.mssqltips.com/sql-server-tip-category/31/dynamic-management-views-and-functions/
There's a hands-on book about DMV's http://www.manning.com/stirk/ focusing on monitoring and finding problems with query execution's.
multiple files in tempdb (http://www.mssqltips.com/sqlservertip/1980/sql-server-tempdb-one-or-multiple-data-files/), (filtered) indexes, partioned tables, statistics.
Following the hartbeat of your system and gradualy know where there could be improvement you do by loocking at your DMV's (dynamic management views)
http://www.mssqltips.com/sql-server-tip-category/31/dynamic-management-views-and-functions/
There's a hands-on book about DMV's http://www.manning.com/stirk/ focusing on monitoring and finding problems with query execution's.
@dtodd
Fillfactor can be set per clustered index, or it uses the server default. If the table is using a common sql practice of say TableID int identity then anything other than fillfactor 100 is wasting space, as inserts are going to the end of the index - logically appends not inserts.
depends on amount of update activity surely...
Fillfactor can be set per clustered index, or it uses the server default. If the table is using a common sql practice of say TableID int identity then anything other than fillfactor 100 is wasting space, as inserts are going to the end of the index - logically appends not inserts.
depends on amount of update activity surely...
>> If the table is using a common sql practice of say TableID int identity then anything other than fillfactor 100 is wasting space <<
>> depends on amount of update activity surely... <<
I don't think it depends on the amount of UPDATE activity as much as on the types of UPDATE done.
Particularly if you commonly increase the length of varchar columns, you will definitely want to leave freespace on the page. [In fact, if you know you will do that, it can make sense to "pad" the varchar column with a dummy value prior to UPDATE so that the column does not expand when UPDATEd.]
A fillfactor of 95-99, depending on the types of UPDATEs, will still pack your data tightly but not risk too many page moves/splits, which can really hurt overall performance.
>> depends on amount of update activity surely... <<
I don't think it depends on the amount of UPDATE activity as much as on the types of UPDATE done.
Particularly if you commonly increase the length of varchar columns, you will definitely want to leave freespace on the page. [In fact, if you know you will do that, it can make sense to "pad" the varchar column with a dummy value prior to UPDATE so that the column does not expand when UPDATEd.]
A fillfactor of 95-99, depending on the types of UPDATEs, will still pack your data tightly but not risk too many page moves/splits, which can really hurt overall performance.
thanks Scott, that was then intention of my point...
or potentially giving values to columns which where previously null.
or potentially giving values to columns which where previously null.
>> or potentially giving values to columns which where previously null. <<
Again, you mean to varchar columns that were previously null, right?
Which would increase their length, yes :-). And so fall under what I said before.
As I understand it, a fixed-length column, say an int, still takes 4 bytes whether it is null or not.
Again, you mean to varchar columns that were previously null, right?
Which would increase their length, yes :-). And so fall under what I said before.
As I understand it, a fixed-length column, say an int, still takes 4 bytes whether it is null or not.
once again yes... thanks scott.
but now you've got me thinking of "text in row"/pointers and wondering where the break even points occur...
can't give that proper attention now will leave that for after the xmas pud...
but now you've got me thinking of "text in row"/pointers and wondering where the break even points occur...
can't give that proper attention now will leave that for after the xmas pud...
Hi Scott and Lowfat,
Good point about the varchar updates. I was thinking more of a standard transaction table, wich almost never has updates, and no varchar columns.
Regards
David
Good point about the varchar updates. I was thinking more of a standard transaction table, wich almost never has updates, and no varchar columns.
Regards
David
Naturally the same applies to nvarchar. (And varbinary, for those that use it.)
Even [n]varchar(max) can cause a split/move, depending on the size specified to be stored in-row.
Even [n]varchar(max) can cause a split/move, depending on the size specified to be stored in-row.
ASKER
Question not asnwered to the point
ASKER
Is that all I need to do for memory or database settings?
I was expecting possibly 10s of parameters which I do on Oracle databases everyday.
Also are these parameters applicable to all databases on instance/server or they could be set on each SQL database level ?
Regards