MS SQL Server Maintenance
What is the best way to maintain a database that grows significantly. Currently i am using DBCC Shrink command. However this is said to have performance issues as the indexing of the DB is affected. Any guru n the list who can assist? R, T21
I could help, but my experience is mysql - I guess it might work for most. I'm running and maintaining systems with over 400 million rows, generating over 200,000 rows in several tables every day, with sums, counts, groups and so on all in there (no need to say; it's for a telco), all neatly running in a debian linux/mysql 4 processor 64gb animal: Carefully choose attribute types and lengths. Where possible, use small variants such as SMALLINT or MEDIUMINT rather than the regular choice INT. When using fixed-length attributes, such as CHAR, specify a length that is as short as practical. Use fixed-length attributes; that is, try to avoid types such as VARCHAR or BLOB. While fixed-length text attributes may waste space, scanning fixed-length rows in a query is much faster than scanning variable-length rows. Use trim when selecting. Only do this when you will actually have such fields in where clauses. If not, use your VARCHARS Design indexes with care. Keep the primary key index as small as possible, create only indexes that are needed if you can see where's and joins on queries, and use prefixes of attributes where possible. Ensure that the leftmost attribute in the index is the most frequently used in queries and, if all attributes are used, make sure the leftmost attribute is the one with the highest number of duplicate entries. eg. SELECT * FROM A WHERE B=3 AND C=5 have an index with B and C, so SELECT .... where B=3 will use the same index. Don't use indexes in all rows (have seen many coders do this) - depends on how many where and join hits it gets, and size of the table. Some tables (with fewer rows) are actually slower with indexing. Create a statistics table if aggregate functions such as COUNT( ) or SUM( ) are frequently used in queries on large tables. A statistics table stores only one row that is manually updated with the aggregate values of another table. For example, if the statistics table maintains the count of rows in a large customer table, each time a row is inserted or deleted in the customer table, the count is updated in the statistics table. For large tables, this is often faster than calculating aggregate functions with the slow built-in functions that require complete processing of all rows. If large numbers of rows are deleted from a table, or a table containing variable-length attributes is frequently modified, disk space may be wasted. MySQL doesn't usually remove deleted or modified data; it only marks the location as being no longer in use. Wasted space can affect access speed. To reorganize a table.by copying data to a temporary location and back again. MySQL provides the OPTIMIZE TABLE command, which should be used periodically. For example: OPTIMIZE TABLE customer; The OPTIMIZE command should be run when the DBMS is offline for scheduled maintenance. The command is nonstandard SQL. It is possible to create different table types for specific tasks. The default in MySQL is the MyISAM type. For small, temporary, frequently used lookup tables, a different type, the heap table type, can be used. There are other types like InnoDB, BDB, etc My thoughts... On 19 February 2014 15:59, Tusker 21 <tusker212@gmail.com> wrote:
What is the best way to maintain a database that grows significantly. Currently i am using DBCC Shrink command. However this is said to have performance issues as the indexing of the DB is affected.
Any guru n the list who can assist?
R, T21
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
Before you do the shrink, backup the database and the transaction log. Most times its the log thats fill of transactions ... so truncate it On Wed, Feb 19, 2014 at 5:08 PM, Jangita Nyagudi <jangita.nyagudi@gmail.com>wrote:
I could help, but my experience is mysql - I guess it might work for most. I'm running and maintaining systems with over 400 million rows, generating over 200,000 rows in several tables every day, with sums, counts, groups and so on all in there (no need to say; it's for a telco), all neatly running in a debian linux/mysql 4 processor 64gb animal:
Carefully choose attribute types and lengths. Where possible, use small variants such as SMALLINT or MEDIUMINT rather than the regular choice INT. When using fixed-length attributes, such as CHAR, specify a length that is as short as practical.
Use fixed-length attributes; that is, try to avoid types such as VARCHAR or BLOB. While fixed-length text attributes may waste space, scanning fixed-length rows in a query is much faster than scanning variable-length rows. Use trim when selecting. Only do this when you will actually have such fields in where clauses. If not, use your VARCHARS
Design indexes with care. Keep the primary key index as small as possible, create only indexes that are needed if you can see where's and joins on queries, and use prefixes of attributes where possible. Ensure that the leftmost attribute in the index is the most frequently used in queries and, if all attributes are used, make sure the leftmost attribute is the one with the highest number of duplicate entries. eg. SELECT * FROM A WHERE B=3 AND C=5 have an index with B and C, so SELECT .... where B=3 will use the same index. Don't use indexes in all rows (have seen many coders do this) - depends on how many where and join hits it gets, and size of the table. Some tables (with fewer rows) are actually slower with indexing.
Create a statistics table if aggregate functions such as COUNT( ) or SUM( ) are frequently used in queries on large tables. A statistics table stores only one row that is manually updated with the aggregate values of another table. For example, if the statistics table maintains the count of rows in a large customer table, each time a row is inserted or deleted in the customer table, the count is updated in the statistics table. For large tables, this is often faster than calculating aggregate functions with the slow built-in functions that require complete processing of all rows.
If large numbers of rows are deleted from a table, or a table containing variable-length attributes is frequently modified, disk space may be wasted. MySQL doesn't usually remove deleted or modified data; it only marks the location as being no longer in use. Wasted space can affect access speed.
To reorganize a table.by copying data to a temporary location and back again. MySQL provides the OPTIMIZE TABLE command, which should be used periodically. For example:
OPTIMIZE TABLE customer; The OPTIMIZE command should be run when the DBMS is offline for scheduled maintenance. The command is nonstandard SQL.
It is possible to create different table types for specific tasks. The default in MySQL is the MyISAM type. For small, temporary, frequently used lookup tables, a different type, the heap table type, can be used. There are other types like InnoDB, BDB, etc
My thoughts...
On 19 February 2014 15:59, Tusker 21 <tusker212@gmail.com> wrote:
What is the best way to maintain a database that grows significantly. Currently i am using DBCC Shrink command. However this is said to have performance issues as the indexing of the DB is affected.
Any guru n the list who can assist?
R, T21
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
1. You can quickly delete misc data using the TRUNCATE command, but note that it only works on tables that have no foreign keys. Really useful for an 'event log' type of table that has millions of rows. 2. If you have rarely accessed older data in your current db, see if you can move it to another database/server as an archive. Keep the frequently read/modified rows in your main database. 3. In addition to what ^Jangita has provided, replace text,ntext fields with varchar(max), also try and avoid Nulls on char/varchar columns, use an empty string instead ''. 4. ...and as usual, try and run your backup and/or maintenance jobs during low usage hours. My 2c. On 19 February 2014 17:08, Jangita Nyagudi <jangita.nyagudi@gmail.com>wrote:
I could help, but my experience is mysql - I guess it might work for most. I'm running and maintaining systems with over 400 million rows, generating over 200,000 rows in several tables every day, with sums, counts, groups and so on all in there (no need to say; it's for a telco), all neatly running in a debian linux/mysql 4 processor 64gb animal:
Carefully choose attribute types and lengths. Where possible, use small variants such as SMALLINT or MEDIUMINT rather than the regular choice INT. When using fixed-length attributes, such as CHAR, specify a length that is as short as practical.
Use fixed-length attributes; that is, try to avoid types such as VARCHAR or BLOB. While fixed-length text attributes may waste space, scanning fixed-length rows in a query is much faster than scanning variable-length rows. Use trim when selecting. Only do this when you will actually have such fields in where clauses. If not, use your VARCHARS
Design indexes with care. Keep the primary key index as small as possible, create only indexes that are needed if you can see where's and joins on queries, and use prefixes of attributes where possible. Ensure that the leftmost attribute in the index is the most frequently used in queries and, if all attributes are used, make sure the leftmost attribute is the one with the highest number of duplicate entries. eg. SELECT * FROM A WHERE B=3 AND C=5 have an index with B and C, so SELECT .... where B=3 will use the same index. Don't use indexes in all rows (have seen many coders do this) - depends on how many where and join hits it gets, and size of the table. Some tables (with fewer rows) are actually slower with indexing.
Create a statistics table if aggregate functions such as COUNT( ) or SUM( ) are frequently used in queries on large tables. A statistics table stores only one row that is manually updated with the aggregate values of another table. For example, if the statistics table maintains the count of rows in a large customer table, each time a row is inserted or deleted in the customer table, the count is updated in the statistics table. For large tables, this is often faster than calculating aggregate functions with the slow built-in functions that require complete processing of all rows.
If large numbers of rows are deleted from a table, or a table containing variable-length attributes is frequently modified, disk space may be wasted. MySQL doesn't usually remove deleted or modified data; it only marks the location as being no longer in use. Wasted space can affect access speed.
To reorganize a table.by copying data to a temporary location and back again. MySQL provides the OPTIMIZE TABLE command, which should be used periodically. For example:
OPTIMIZE TABLE customer; The OPTIMIZE command should be run when the DBMS is offline for scheduled maintenance. The command is nonstandard SQL.
It is possible to create different table types for specific tasks. The default in MySQL is the MyISAM type. For small, temporary, frequently used lookup tables, a different type, the heap table type, can be used. There are other types like InnoDB, BDB, etc
My thoughts...
On 19 February 2014 15:59, Tusker 21 <tusker212@gmail.com> wrote:
What is the best way to maintain a database that grows significantly. Currently i am using DBCC Shrink command. However this is said to have performance issues as the indexing of the DB is affected.
Any guru n the list who can assist?
R, T21
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
1) What do you mean by 'grows significantly' 2) WHY do you want to shrink it? On Wed, Feb 19, 2014 at 3:59 PM, Tusker 21 <tusker212@gmail.com> wrote:
What is the best way to maintain a database that grows significantly. Currently i am using DBCC Shrink command. However this is said to have performance issues as the indexing of the DB is affected.
Any guru n the list who can assist?
R, T21
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
Thanks @Jangita and @John. Will look at the current set configurations of the DB attribute types and lengths. @Rad The current DB stands at 250Gib, the log file used to be bigger than the DB hence we run a Job to shrink the Log and the DB everyday as part of DB Maintenance. Shrinking is our8 temp solution of freeing any unutilized space and compressing the log On Wed, Feb 19, 2014 at 5:25 PM, Rad! <conradakunga@gmail.com> wrote:
1) What do you mean by 'grows significantly' 2) WHY do you want to shrink it?
On Wed, Feb 19, 2014 at 3:59 PM, Tusker 21 <tusker212@gmail.com> wrote:
What is the best way to maintain a database that grows significantly. Currently i am using DBCC Shrink command. However this is said to have performance issues as the indexing of the DB is affected.
Any guru n the list who can assist?
R, T21
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
Note that the transaction log growing quickly is normal in a database that has a lot of inserts/updates. On 19 February 2014 17:44, Tusker 21 <tusker212@gmail.com> wrote:
Thanks @Jangita and @John. Will look at the current set configurations of the DB attribute types and lengths.
@Rad The current DB stands at 250Gib, the log file used to be bigger than the DB hence we run a Job to shrink the Log and the DB everyday as part of DB Maintenance. Shrinking is our8 temp solution of freeing any unutilized space and compressing the log
On Wed, Feb 19, 2014 at 5:25 PM, Rad! <conradakunga@gmail.com> wrote:
1) What do you mean by 'grows significantly' 2) WHY do you want to shrink it?
On Wed, Feb 19, 2014 at 3:59 PM, Tusker 21 <tusker212@gmail.com> wrote:
What is the best way to maintain a database that grows significantly. Currently i am using DBCC Shrink command. However this is said to have performance issues as the indexing of the DB is affected.
Any guru n the list who can assist?
R, T21
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
Hi In terms of performance check on indexing the tables, also check on different execution styles for the DB... for further performance separate the install hard drive, database hard drive and log hard drive, if u are using server 08 check performance of the disk you will see a notorious temp db activity also u can optimize by adding more temp db..formula usually for each 2 cores one temp db... but read from white papers to see if any of these affect your system Regards GG On Feb 19, 2014 5:44 PM, "Tusker 21" <tusker212@gmail.com> wrote:
Thanks @Jangita and @John. Will look at the current set configurations of the DB attribute types and lengths.
@Rad The current DB stands at 250Gib, the log file used to be bigger than the DB hence we run a Job to shrink the Log and the DB everyday as part of DB Maintenance. Shrinking is our8 temp solution of freeing any unutilized space and compressing the log
On Wed, Feb 19, 2014 at 5:25 PM, Rad! <conradakunga@gmail.com> wrote:
1) What do you mean by 'grows significantly' 2) WHY do you want to shrink it?
On Wed, Feb 19, 2014 at 3:59 PM, Tusker 21 <tusker212@gmail.com> wrote:
What is the best way to maintain a database that grows significantly. Currently i am using DBCC Shrink command. However this is said to have performance issues as the indexing of the DB is affected.
Any guru n the list who can assist?
R, T21
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ skunkworks mailing list skunkworks@lists.my.co.ke ------------ List info, subscribe/unsubscribe http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------
Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
participants (6)
-
Agosta Liko -
geoffrey gitagia -
Jangita Nyagudi -
John K. -
Rad! -
Tusker 21