| Author |
Topic |
|
niranjankumark
Posting Yak Master
164 Posts |
 Posted - 2007-10-11 : 06:00:16
|
| The execution time of select query is very high.Index is available in that table.How to find indexes and other performance related things updated propely in the sql server 2000.I saw the dbcc showcontig result. But how to find exactly and deeply performance is less due to the specific issue.Defragmentation should be done in which kind of situation.here Logical Scan Fragmentation is 42.30%.will this affect any major performance.kindly send detail about this performance realted issue.- Pages Scanned................................: 160712- Extents Scanned..............................: 20161- Extent Switches..............................: 127082- Avg. Pages per Extent........................: 8.0- Scan Density [Best Count:Actual Count].......: 15.81% [20089:127083]- Logical Scan Fragmentation ..................: 42.30%- Extent Scan Fragmentation ...................: 3.61%- Avg. Bytes Free per Page.....................: 3407.8- Avg. Page Density (full).....................: 57.90% |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-11 : 06:41:26
|
| "The execution time of select query is very high"We would need to see the DDL for the table(s) and the Query to advise - otherwise we are just guessing.With regard to your SHOWCONTIG results:Scan Density -- 100% is best, 0% worstLogical Scan Fragmentation - Lower is better Extent Scan Fragmentation - Lower is better Avg. Bytes free per page - Lower is better Avg. Page density (full) - Higher is better so you should reindex that table.Kristen |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2007-10-11 : 06:43:50
|
| Logical Scan Fragmentation will affect performance. The higher the worse. Usually I use 30% as a threshold. Do a DBCC INDEXDEFRAG (<my_db>, <my_table>, <my_index>) , then do a STATISTICS update for those objects.You do get a lot of fragementation issues related to data modifications , depending on fill factors and logic order of INSERTS.If there a lot of data modifications consider running a daily job where you defragment once indices have reached a certain level. I would favour running DBCC INDEXDEFRAG as it's an online operation as opposed to REINDEXJack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL |
|
|
|
niranjankumark
Posting Yak Master
164 Posts |
Posted - 2007-10-11 : 07:04:59
|
| Thank u kristen and jack ...Kris . query is perfectly fine.. joins made up on based on index, no functions on where condition.Even if i run any simple query it takes so many time. If i have to do the DBCC INDEXDEFRAG what are all the other analysis i have to take.why should i do update statistics. can i have any document in full depth related to this SHOWCONTIG ,INDEXDEFRAG ,fragment,defragment .jack and kris waiting for your early reply... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-11 : 07:14:12
|
| "query is perfectly fine.. joins made up on based on index"How do you know that? Are you just assuming the index is used, or have you checked the Query Plan?"If i have to do the DBCC INDEXDEFRAG what are all the other analysis i have to take"You need to do regular housekeeping. The things that Maintenance Wizard does for you are sufficient for most circumstances."why should i do update statistics"Because if you don't SQL Server has no useful Hints as to which index etc. will offer good performance."can i have any document in full depth related to ..."Books Online should have all that you need, if that raises any questions please come back and ask them here.Kristen |
|
|
|
niranjankumark
Posting Yak Master
164 Posts |
Posted - 2007-10-11 : 08:09:04
|
| Saw the execution plan , no table scan exist , It is only two table join . but having more than 1 crore record."If i have to do the DBCC INDEXDEFRAG what are all the other analysis i have to take"You need to do regular housekeeping. The things that Maintenance Wizard does for you are sufficient for most circumstances.kris i cud not get ur point. what is maintenance wizard...can u send books online link..u have mentioned logic scan density % should be 100. like other percentage i have to find. TSQL help i saw .. but not exactly given that percentage. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-11 : 09:57:08
|
| "no table scan exist"But was the index you expected used?"what is maintenance wizard"Utility that comes with SQL Server (find it in Enterprise Manager) that allows you to set up a maintenance plan"can u send books online link"Start : Programs : Microsoft SQL Server : Books online"u have mentioned logic scan density % should be 100"Not exactly, 100% is best, 0% is worst. Yours is 15% - that's very low indeed.Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-11 : 23:57:20
|
| No table scan doesn't necessary mean best performance, did you check physical read count? Defrag table can reduce physical read. |
|
|
|
niranjankumark
Posting Yak Master
164 Posts |
Posted - 2007-10-12 : 03:04:10
|
| dbcc indexdefrag needs aruguments(database,table and index name)..is it way to do entire database in single command. |
|
|
|
Kristen
Test
22859 Posts |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2007-10-12 : 03:35:32
|
| Also , if you check in SQL Server Books Online , there is a script on the DBCC INDEXDEFRAG index search that does some checking for you to analyse the level of fragmentation , and then will execute the relevant commandsJack Vamvas--------------------Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 03:56:26
|
| "there is a script"SQL 2000 BoL??I'd quite like to have a look too ...Kristen |
|
|
|
niranjankumark
Posting Yak Master
164 Posts |
Posted - 2007-10-12 : 04:56:44
|
| I checked in tara's blog .. but but my requirement does not exist.I have multiple table and index.so it is difficult to put index name table name in syntax. sql books online also not exist. can u help me |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 05:31:36
|
| "but my requirement does not exist.I have multiple table and index."Eh?"[b]This stored procedure runs DBCC INDEXDEFRAG for each of the indexes in the database.[b]"sounds like just what you need, doesn't it?Kristen |
|
|
|
niranjankumark
Posting Yak Master
164 Posts |
Posted - 2007-10-12 : 05:36:28
|
| EXACTLY .. I can write a query from sysindexes .. but i need to know anything exist..like dbcc indexdefrag (db_name) ... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 05:38:15
|
| Sorry, I don't understand.Tara's procedure will defrag EVERY index for EVERY table in a given database.Is that what you want? If not can you please describe what you do want, thanks.Kristen |
|
|
|
niranjankumark
Posting Yak Master
164 Posts |
Posted - 2007-10-12 : 05:50:36
|
| "Tara's procedure will defrag EVERY index for EVERY table in a given database."yes that what i need....But i cud not find that in your given link. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 06:09:16
|
| Follow linkScroll down until you get to "Tara Blog Houskeeping Routines" (it will be highlighted for you)Follow that link, then you will see:"defragments indexes (SQL Server 2000)"follow that link.Kristen |
|
|
|
niranjankumark
Posting Yak Master
164 Posts |
Posted - 2007-10-12 : 06:19:39
|
| Thank u kris i found that ..one more info need.. i ran the indexdefrag on my main table .It takes 32 minutes to complete.To run in entire table more time will take right ?? any way to complete in short time ? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-12 : 06:44:43
|
| "any way to complete in short time ?"What do you think??You could just do tables A-B I suppose ... then C-D another day ...Either you are trying to get some housekeeping in place, or you aren't, but there is no magic wand for this.Kristen |
|
|
|
niranjankumark
Posting Yak Master
164 Posts |
Posted - 2007-10-12 : 08:13:41
|
| Kris ,In my query joins , index and clustered index scan exist.It took 10 minutes to execute, after executing the indexdefrag 1.5 minutes only got reduced, in other server also same thing happend. Atleast 5 miniutes has to be reduced.Initially fragmentation was high scan density is low.. now everything is fine.What can be done still more to improve performance. i have to do the defragmeantation for entire database.Now i have done table which used in that query only.That is enough for that query right ??? |
|
|
|
Next Page
|
Performance issue