 | |
SQL Server Indexes
microsoft.public.sqlserver.tools microsoft.public.sqlserver.tools
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: SQL Server Indexes -
06-10-2008
, 04:58 PM
|
We have a User table with 2million records in them, that has 4 indexes set for faster Search. At an average we have 187k new records coming into the table per day. We see every 4 or 5 days the queries we run on the table get slower. However if we drop and re-create the index the querys are now quick. How do we identify the issue which gets resolved by dropping and creating the index. Is there an dbcc command that we can use to identify the issue? |
You can examimne this with DBCC SHOWCONTIG. The way to address fragmentation
is DBCC DBREINDEX or DBCC INDEXDEFRAG. (Or ALTER INDEX REBUILD/REORGANIZE
in SQL 2005.) This is essentially what you do today, but you don't have to
repeat the index definitions.
It could also be an issue with parameter sniffing. Are those 187000 rows
coming in all one one go, or during the day. If all comes at once, that
could trigger auto-statistics, and the first search after the load hits
many rows that may be the plan that sticks into the cache.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
#12
| |||
| |||
|
|
We have a User table with 2million records in them, that has 4 indexes set for faster Search. At an average we have 187k new records coming into the table per day. We see every 4 or 5 days the queries we run on the table get slower. However if we drop and re-create the index the querys are now quick. How do we identify the issue which gets resolved by dropping and creating the index. Is there an dbcc command that we can use to identify the issue? |
You can examimne this with DBCC SHOWCONTIG. The way to address fragmentation
is DBCC DBREINDEX or DBCC INDEXDEFRAG. (Or ALTER INDEX REBUILD/REORGANIZE
in SQL 2005.) This is essentially what you do today, but you don't have to
repeat the index definitions.
It could also be an issue with parameter sniffing. Are those 187000 rows
coming in all one one go, or during the day. If all comes at once, that
could trigger auto-statistics, and the first search after the load hits
many rows that may be the plan that sticks into the cache.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
#13
| |||
| |||
|
|
We have a User table with 2million records in them, that has 4 indexes set for faster Search. At an average we have 187k new records coming into the table per day. We see every 4 or 5 days the queries we run on the table get slower. However if we drop and re-create the index the querys are now quick. How do we identify the issue which gets resolved by dropping and creating the index. Is there an dbcc command that we can use to identify the issue? |
You can examimne this with DBCC SHOWCONTIG. The way to address fragmentation
is DBCC DBREINDEX or DBCC INDEXDEFRAG. (Or ALTER INDEX REBUILD/REORGANIZE
in SQL 2005.) This is essentially what you do today, but you don't have to
repeat the index definitions.
It could also be an issue with parameter sniffing. Are those 187000 rows
coming in all one one go, or during the day. If all comes at once, that
could trigger auto-statistics, and the first search after the load hits
many rows that may be the plan that sticks into the cache.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
#14
| |||
| |||
|
|
We have a User table with 2million records in them, that has 4 indexes set for faster Search. At an average we have 187k new records coming into the table per day. We see every 4 or 5 days the queries we run on the table get slower. However if we drop and re-create the index the querys are now quick. How do we identify the issue which gets resolved by dropping and creating the index. Is there an dbcc command that we can use to identify the issue? |
You can examimne this with DBCC SHOWCONTIG. The way to address fragmentation
is DBCC DBREINDEX or DBCC INDEXDEFRAG. (Or ALTER INDEX REBUILD/REORGANIZE
in SQL 2005.) This is essentially what you do today, but you don't have to
repeat the index definitions.
It could also be an issue with parameter sniffing. Are those 187000 rows
coming in all one one go, or during the day. If all comes at once, that
could trigger auto-statistics, and the first search after the load hits
many rows that may be the plan that sticks into the cache.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
#15
| |||
| |||
|
|
We have a User table with 2million records in them, that has 4 indexes set for faster Search. At an average we have 187k new records coming into the table per day. We see every 4 or 5 days the queries we run on the table get slower. However if we drop and re-create the index the querys are now quick. How do we identify the issue which gets resolved by dropping and creating the index. Is there an dbcc command that we can use to identify the issue? |
You can examimne this with DBCC SHOWCONTIG. The way to address fragmentation
is DBCC DBREINDEX or DBCC INDEXDEFRAG. (Or ALTER INDEX REBUILD/REORGANIZE
in SQL 2005.) This is essentially what you do today, but you don't have to
repeat the index definitions.
It could also be an issue with parameter sniffing. Are those 187000 rows
coming in all one one go, or during the day. If all comes at once, that
could trigger auto-statistics, and the first search after the load hits
many rows that may be the plan that sticks into the cache.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
#16
| |||
| |||
|
|
We have a User table with 2million records in them, that has 4 indexes set for faster Search. At an average we have 187k new records coming into the table per day. We see every 4 or 5 days the queries we run on the table get slower. However if we drop and re-create the index the querys are now quick. How do we identify the issue which gets resolved by dropping and creating the index. Is there an dbcc command that we can use to identify the issue? |
You can examimne this with DBCC SHOWCONTIG. The way to address fragmentation
is DBCC DBREINDEX or DBCC INDEXDEFRAG. (Or ALTER INDEX REBUILD/REORGANIZE
in SQL 2005.) This is essentially what you do today, but you don't have to
repeat the index definitions.
It could also be an issue with parameter sniffing. Are those 187000 rows
coming in all one one go, or during the day. If all comes at once, that
could trigger auto-statistics, and the first search after the load hits
many rows that may be the plan that sticks into the cache.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
#17
| |||
| |||
|
|
We have a User table with 2million records in them, that has 4 indexes set for faster Search. At an average we have 187k new records coming into the table per day. We see every 4 or 5 days the queries we run on the table get slower. However if we drop and re-create the index the querys are now quick. How do we identify the issue which gets resolved by dropping and creating the index. Is there an dbcc command that we can use to identify the issue? |
You can examimne this with DBCC SHOWCONTIG. The way to address fragmentation
is DBCC DBREINDEX or DBCC INDEXDEFRAG. (Or ALTER INDEX REBUILD/REORGANIZE
in SQL 2005.) This is essentially what you do today, but you don't have to
repeat the index definitions.
It could also be an issue with parameter sniffing. Are those 187000 rows
coming in all one one go, or during the day. If all comes at once, that
could trigger auto-statistics, and the first search after the load hits
many rows that may be the plan that sticks into the cache.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
#18
| |||
| |||
|
|
We have a User table with 2million records in them, that has 4 indexes set for faster Search. At an average we have 187k new records coming into the table per day. We see every 4 or 5 days the queries we run on the table get slower. However if we drop and re-create the index the querys are now quick. How do we identify the issue which gets resolved by dropping and creating the index. Is there an dbcc command that we can use to identify the issue? |
You can examimne this with DBCC SHOWCONTIG. The way to address fragmentation
is DBCC DBREINDEX or DBCC INDEXDEFRAG. (Or ALTER INDEX REBUILD/REORGANIZE
in SQL 2005.) This is essentially what you do today, but you don't have to
repeat the index definitions.
It could also be an issue with parameter sniffing. Are those 187000 rows
coming in all one one go, or during the day. If all comes at once, that
could trigger auto-statistics, and the first search after the load hits
many rows that may be the plan that sticks into the cache.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
#19
| |||
| |||
|
|
We have a User table with 2million records in them, that has 4 indexes set for faster Search. At an average we have 187k new records coming into the table per day. We see every 4 or 5 days the queries we run on the table get slower. However if we drop and re-create the index the querys are now quick. How do we identify the issue which gets resolved by dropping and creating the index. Is there an dbcc command that we can use to identify the issue? |
You can examimne this with DBCC SHOWCONTIG. The way to address fragmentation
is DBCC DBREINDEX or DBCC INDEXDEFRAG. (Or ALTER INDEX REBUILD/REORGANIZE
in SQL 2005.) This is essentially what you do today, but you don't have to
repeat the index definitions.
It could also be an issue with parameter sniffing. Are those 187000 rows
coming in all one one go, or during the day. If all comes at once, that
could trigger auto-statistics, and the first search after the load hits
many rows that may be the plan that sticks into the cache.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.