| Author |
Topic |
|
ruan4u
Posting Yak Master
132 Posts |
 Posted - 2005-05-15 : 12:09:43
|
| DBCC showcontig on one of my invoice tables gives this.I run a query by inv_duedate,but it still hits the clustered index (set on PK) and makes an index scan(as per execute pplan).why does it say table scan here.Table: 'nbs_Invoice'TABLE level scan performed.- Pages Scanned................................: 59386- Extents Scanned..............................: 7441- Extent Switches..............................: 8471- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 87.63% [7424:8472]- Logical Scan Fragmentation ..................: 86.13%- Extent Scan Fragmentation ...................: 0.31%- Avg. Bytes Free per Page.....................: 560.2- Avg. Page Density (full).....................: 93.08% |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-05-15 : 12:51:10
|
Did you read Books Onlne?quote:
The DBCC SHOWCONTIG statement traverses the page chain at the leaf level of the specified index when index_id is specified. If only table_id is specified, or if index_id is 0, the data pages of the specified table are scanned.
And, the table could definitely use some work.1. You're logical scan fragmentation is wacky:quote:
Logical Scan Fragmentation and, to a lesser extent, Extent Scan Fragmentation values give the best indication of a table's fragmentation level. Both these values should be as close to zero as possible (although a value from 0% through 10% may be acceptable).
(Books Online also)2. Your extents numbers could be a little better:quote:
The value of Extent Switches should be as close as possible to that of Extents Scanned. This ratio is calculated as the Scan Density value, which should be as high as possible. This can be improved by either method of reducing fragmentation discussed earlier.
(Books Online also)You can fix the issue by using the methods described in Books Online:quote:
When an index is heavily fragmented, there are two choices for reducing fragmentation: Drop and re-create a clustered index. Re-creating a clustered index reorganizes the data, and results in full data pages. The level of fullness can be configured using the FILLFACTOR option. The drawbacks of this method are that the index is offline during the drop/re-create cycle and that the operation is atomic. If the index creation is interrupted, the index is not re-created.Use DBCC INDEXDEFRAG to reorder the leaf level pages of the index in a logical order. The DBCC INDEXDEFRAG command is an online operation, so the index is available while the command is running. The operation is also interruptible without loss of completed work. The drawback of this method is that it does not do as good a job of reorganizing the data as a clustered index drop/re-create operation.
Let us know if you need any help after following the directions listed in Books Online. You can find the information I just showed you by going to the Index tab in Books Online and typing in "DBCC SHOWCONTIG".MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2005-05-15 : 13:28:32
|
| Please help , I am totally lost in how to fix this. I also got the list of indexes and the # of pages they have used.My clustered Index is a bigint.Is it ok?Index Name Index type Pages RowsPk_C1Invoices Cluster 114925 712615Ix_pendinv non-cluster 2934 712615IX_3 non-cluster 2147 712615Invquery non-cluster 8485 712615IX_4 non-cluster 4598 712615IX_2 non-cluster 2147 712615Is that good? |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-05-15 : 13:46:18
|
| Read my previous post. It tells you quite clearly how to fix the issue. We don't mind helping out people, but you are being lazy right now. As far as the clustered index question goes, it's kind of hard to tell. You would need to give us the actual CREATE TABLE and CREATE INDEX statements for this table and the tables that immediately join to it.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-05-15 : 13:48:49
|
| check Tara's blog (click the weblogs link at the top of the page) and look back through her archives for some index reorg scripts she has. You can schedule these to run at regular intervals to help eliminate the fragmentation problem you are worried about.btw, what specific problem are you having? Are you seeing a specific performance problem, or are you just concerned over the fragmentation of your indexes?-ec |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2005-05-16 : 08:20:16
|
| derrick,sorry i didnt read your small footnotes on each of'em. They r indicative enough on what actions i need to take.Not lazy |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-05-16 : 08:50:55
|
 I'm a little grouchy sometimes, aren't I? Let us know if you need help once you follow the notes I gave you. Also, look at Tara's blog like ec said.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2005-05-16 : 11:53:18
|
| I did run,and had the extent switches match with the extent scan.The logical scan is also looking good and is at 0.10%.I have to see if the query now doesnt take that long.I had a query that was taking way too long,after I altered a table. |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2005-05-16 : 12:35:26
|
| My query still gives CPU as 859 and reads=141590.Total records in table is around 800K.The query does hit the Index scan and brings result in a few secs in QA,but times out from app. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-05-16 : 19:38:34
|
quote:
Originally posted by ruan4u
My query still gives CPU as 859 and reads=141590.Total records in table is around 800K.The query does hit the Index scan and brings result in a few secs in QA,but times out from app.
that is why I was asking you what problem you were trying to solve.Post your query (if you can) and any pertinent table and view DDL so we can help you out.Also, are you saying that the query you run runs fine from QA, but times out when you run it from your web app? Is this a stored procedure or and ad-hoc query?-ec |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-05-16 : 21:18:17
|
quote:
Total records in table is around 800K.
How many records are you returning to the app?What is your network outbound queue length when you run this?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
ruan4u
Posting Yak Master
132 Posts |
Posted - 2005-05-17 : 15:09:52
|
| Only 133 rows are being selected.There are 700,000 records in the Receivable table. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-05-17 : 19:37:20
|
quote:
Originally posted by ruan4u
Only 133 rows are being selected.There are 700,000 records in the Receivable table.
Please post the DDL of your tables, the SQL Query and an explain plan. Without that information we cannot begin to help you troubleshoot your problem.-ec |
|
|
|
|
DBCC - is this bad