April 8, 2008 at 7:34 pm
Hi. I'm a developer that kind of got stuck with the databases when the last guy left, so I'm kind of learning as I go. We have a legacy application that is used pretty heavily, and Im realizing that the database wasnt set up to be very scalable. Due to being short on time, schema/query changes arent an option.
So a client reported that when running a certain report from within the application, they are encountering a server time out. Tracing the database call our component makes for the report, we found that the query being run is a simple select running against a view:
SELECT *
FROM dbo.abc_view WITH (NOLOCK)
WHERE limiterID = 99
ORDER BY date DESC
The view is pretty simple :
SELECT column1, column2, column3... column150
FROM tableA
INNER JOIN tableB
ON pID.tableA = fID.tableB
The table/view stats:
tableA:
1580 rows
10 columns
2 indexes - 1 clustered on the primary key (the column we are joining tableB on)
- 1 non-clustered on a keyword field
tableB:
1266989 rows
140 columns
15 indexes - 1 clustered on its primary key
- 1 non-clustered on the foreign key used for the join with tableA
- 1 non-clustered on the date field used to order the resultset
- 1 non-clustered on the limiterID which is a dynamic integer
The problem- this query will not finish without killing database performance, as well as taking a ridiculous amount of time to finish (if it finishes at all). I ran the estimated execution plan in query analyzer and it said that a Bookmark Lookup would be used. After Googling for a little while, I realized that this query (which is probably attempted rather often by clients) is probably the cause of our performance issues. Unfortunately, I cannot change the query to use fewer fields (the select statement covers 150 fields total) due to the schema being hardcoded into our legacy components (we dont have enough developers at this point to attempt to modify the components).
I read a few articles about using a covering index. Hard drive space is not an issue with this database, so we have room for the index. There were mixed messages regarding creating a covering index with so many fields, however.
A)you shouldnt index 150 fields
B)if you are getting a bookmark lookup, you would most likely benefit from a covering index
Any suggestions?
Thanks.
April 8, 2008 at 8:06 pm
Performance tuning may be a very complex task. Whether or not to use covering indexes also depend on data types of these columns. Changing indexes may do good for one query but may be bad for other queries. There is no a simple, straight approach.
In your scenario, you may try either indexed view, or clustered indexes on the joined columns.
May the above help.
April 8, 2008 at 8:24 pm
the vast majority of the fields are varchar(50). the ids are decimal(18).
i have yet to find a nice simple tutorial on creating an indexed view. any recommendations?
thanks.
April 8, 2008 at 8:24 pm
My suggestion would be not to start changes indexes yet. If the query was working at one time and has stopped that smells like indexes and\or statistics are in tough shape. Additionally, estimated execution plans can be unreliable. Try to get the actual plan, adding a top 1 to the select should do that for you.
Run this command this two commands during low server load and post the results
dbcc showcontig('table_name_here') with all_indexes
dbcc show_statistics('table_name_here', 'index_name_here')
Run the second command for each table and every index. Run the first command for both tables.
Once you post the results I jump on and look at them for you.
David
April 8, 2008 at 9:01 pm
A bookmark lookup is not necessarily a bad thing - it indicates a non-clustered index is being used, which may be preferable to a table or index scan.
In this case, a "covering" index is not feasible, but a "composite" index may be. If the table has 15 indexes, and they are all single-column indexes, I bet a large percentage of the non-clustereds aren't even used due to not being selective enough.
A composite, 2 column index on (ForeignKeyID, LimiterID) might help here.
April 9, 2008 at 12:37 am
bradfordshultz (4/8/2008)
A)you shouldnt index 150 fields
If you're using SQL 2000, you can't create a covering index over 150 fields. Index keys are limited to 16 columns.
In 2005 and higher you can use include columns to create larger covering idexes.
Can you save the exec plan and attach it to a post please. On 2000, best wey would be to switch ShowPlan_all on, run the query (to grid) then copy the results into Excel.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 9, 2008 at 1:17 pm
ran this on the table last night. looks like there is a little fragmentation on some of the indexes, but not horrible- right?
/*-----------------------------
dbcc showcontig('katabat.dbo.inquirymanagerresponse') with all_indexes
-----------------------------*/
DBCC SHOWCONTIG scanning 'InquiryManagerResponse' table...
Table: 'InquiryManagerResponse' (271340031); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 150338
- Extents Scanned..............................: 18858
- Extent Switches..............................: 22310
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 84.23% [18793:22311]
- Logical Scan Fragmentation ..................: 1.64%
- Extent Scan Fragmentation ...................: 13.92%
- Avg. Bytes Free per Page.....................: 636.1
- Avg. Page Density (full).....................: 92.14%
DBCC SHOWCONTIG scanning 'InquiryManagerResponse' table...
Table: 'InquiryManagerResponse' (271340031); index ID: 2, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 5959
- Extents Scanned..............................: 755
- Extent Switches..............................: 817
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 91.08% [745:818]
- Logical Scan Fragmentation ..................: 0.64%
- Extent Scan Fragmentation ...................: 20.26%
- Avg. Bytes Free per Page.....................: 684.2
- Avg. Page Density (full).....................: 91.55%
DBCC SHOWCONTIG scanning 'InquiryManagerResponse' table...
Table: 'InquiryManagerResponse' (271340031); index ID: 3, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 4244
- Extents Scanned..............................: 539
- Extent Switches..............................: 748
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 70.89% [531:749]
- Logical Scan Fragmentation ..................: 2.83%
- Extent Scan Fragmentation ...................: 8.35%
- Avg. Bytes Free per Page.....................: 930.8
- Avg. Page Density (full).....................: 88.50%
DBCC SHOWCONTIG scanning 'InquiryManagerResponse' table...
Table: 'InquiryManagerResponse' (271340031); index ID: 4, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 4083
- Extents Scanned..............................: 522
- Extent Switches..............................: 537
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 94.98% [511:538]
- Logical Scan Fragmentation ..................: 1.54%
- Extent Scan Fragmentation ...................: 18.01%
- Avg. Bytes Free per Page.....................: 958.6
- Avg. Page Density (full).....................: 88.16%
DBCC SHOWCONTIG scanning 'InquiryManagerResponse' table...
Table: 'InquiryManagerResponse' (271340031); index ID: 6, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 3894
- Extents Scanned..............................: 495
- Extent Switches..............................: 777
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 62.60% [487:778]
- Logical Scan Fragmentation ..................: 4.24%
- Extent Scan Fragmentation ...................: 15.56%
- Avg. Bytes Free per Page.....................: 1013.7
- Avg. Page Density (full).....................: 87.48%
DBCC SHOWCONTIG scanning 'InquiryManagerResponse' table...
Table: 'InquiryManagerResponse' (271340031); index ID: 7, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 4242
- Extents Scanned..............................: 540
- Extent Switches..............................: 570
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 92.99% [531:571]
- Logical Scan Fragmentation ..................: 0.97%
- Extent Scan Fragmentation ...................: 7.96%
- Avg. Bytes Free per Page.....................: 790.1
- Avg. Page Density (full).....................: 90.24%
DBCC SHOWCONTIG scanning 'InquiryManagerResponse' table...
Table: 'InquiryManagerResponse' (271340031); index ID: 8, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 4585
- Extents Scanned..............................: 582
- Extent Switches..............................: 734
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 78.10% [574:735]
- Logical Scan Fragmentation ..................: 3.05%
- Extent Scan Fragmentation ...................: 10.65%
- Avg. Bytes Free per Page.....................: 1015.2
- Avg. Page Density (full).....................: 87.46%
DBCC SHOWCONTIG scanning 'InquiryManagerResponse' table...
Table: 'InquiryManagerResponse' (271340031); index ID: 9, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 4234
- Extents Scanned..............................: 540
- Extent Switches..............................: 589
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 89.83% [530:590]
- Logical Scan Fragmentation ..................: 1.09%
- Extent Scan Fragmentation ...................: 25.74%
- Avg. Bytes Free per Page.....................: 901.1
- Avg. Page Density (full).....................: 88.87%
DBCC SHOWCONTIG scanning 'InquiryManagerResponse' table...
Table: 'InquiryManagerResponse' (271340031); index ID: 11, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 4642
- Extents Scanned..............................: 592
- Extent Switches..............................: 597
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 97.16% [581:598]
- Logical Scan Fragmentation ..................: 0.19%
- Extent Scan Fragmentation ...................: 26.35%
- Avg. Bytes Free per Page.....................: 724.6
- Avg. Page Density (full).....................: 91.05%
DBCC SHOWCONTIG scanning 'InquiryManagerResponse' table...
Table: 'InquiryManagerResponse' (271340031); index ID: 12, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 4633
- Extents Scanned..............................: 589
- Extent Switches..............................: 710
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 81.58% [580:711]
- Logical Scan Fragmentation ..................: 1.79%
- Extent Scan Fragmentation ...................: 8.32%
- Avg. Bytes Free per Page.....................: 812.6
- Avg. Page Density (full).....................: 89.96%
DBCC SHOWCONTIG scanning 'InquiryManagerResponse' table...
Table: 'InquiryManagerResponse' (271340031); index ID: 46, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 7413
- Extents Scanned..............................: 940
- Extent Switches..............................: 1224
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 75.67% [927:1225]
- Logical Scan Fragmentation ..................: 2.51%
- Extent Scan Fragmentation ...................: 33.09%
- Avg. Bytes Free per Page.....................: 917.2
- Avg. Page Density (full).....................: 88.67%
DBCC SHOWCONTIG scanning 'InquiryManagerResponse' table...
Table: 'InquiryManagerResponse' (271340031); index ID: 47, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 5923
- Extents Scanned..............................: 754
- Extent Switches..............................: 1131
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 65.46% [741:1132]
- Logical Scan Fragmentation ..................: 4.00%
- Extent Scan Fragmentation ...................: 7.43%
- Avg. Bytes Free per Page.....................: 1027.6
- Avg. Page Density (full).....................: 87.30%
DBCC SHOWCONTIG scanning 'InquiryManagerResponse' table...
Table: 'InquiryManagerResponse' (271340031); index ID: 145, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 5871
- Extents Scanned..............................: 744
- Extent Switches..............................: 780
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 93.98% [734:781]
- Logical Scan Fragmentation ..................: 0.43%
- Extent Scan Fragmentation ...................: 2.96%
- Avg. Bytes Free per Page.....................: 658.2
- Avg. Page Density (full).....................: 91.87%
DBCC SHOWCONTIG scanning 'InquiryManagerResponse' table...
Table: 'InquiryManagerResponse' (271340031); index ID: 146, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 4212
- Extents Scanned..............................: 537
- Extent Switches..............................: 679
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 77.50% [527:680]
- Logical Scan Fragmentation ..................: 1.97%
- Extent Scan Fragmentation ...................: 8.94%
- Avg. Bytes Free per Page.....................: 876.3
- Avg. Page Density (full).....................: 89.17%
DBCC SHOWCONTIG scanning 'InquiryManagerResponse' table...
Table: 'InquiryManagerResponse' (271340031); index ID: 148, database ID: 8
LEAF level scan performed.
- Pages Scanned................................: 4991
- Extents Scanned..............................: 635
- Extent Switches..............................: 730
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 85.36% [624:731]
- Logical Scan Fragmentation ..................: 1.32%
- Extent Scan Fragmentation ...................: 15.59%
- Avg. Bytes Free per Page.....................: 907.2
- Avg. Page Density (full).....................: 88.79%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
April 9, 2008 at 3:05 pm
VARCHAR(50) and DECIMAL(18) are not good candidates for indexing. If you cannot modify indexes, you may not have choice.
In principle, data type of column chosen to be indexed should not be long in byte.
April 9, 2008 at 3:16 pm
Most of them are pretty good but you got two I'm thinking could be a problem.
If you've go the maintenance window. I'd run dbcc dbreindex against the whole table plus you get a full stats update.
David
April 10, 2008 at 7:37 am
If possible, one thing that may help is to do the sorting in the application.
Creating an index on the view sounds like it will probably be your best bet, but the restrictions on doing that are rather long. (I've only tried once, I don't remember why it hadn't worked) Check 'Indexed Views' in BOL for the list.
Keep in mind, adding an index on the view means that the server will have to maintain the index whenever you do anything with the underlying data, so test it to see if it unacceptably slows down inserts, deletes or updates.
April 14, 2008 at 1:10 pm
I dont think you can create an indexed view with Standard Edition (which we have. a bunch of a**holes set the environment up. pos is completely non-scalable).
Anyway, this was more or less resolved. One of our parent companies DBAs looked at it and added three indexes to the tables. This shortened query times to abt half what they were. Not perfect, but much better than before. Thanks for all your help guys.
June 23, 2009 at 9:57 am
Hi ,
I am new to SQL SERVER .. Is there a chance to remove Bookmark look up in execution plan , without changing an Index part ...
i mean if there anything to replace the BOOKMARK LOOK UP .
Thanks
June 23, 2009 at 2:01 pm
A bookmark lookup is done when SQL uses an index for a query but that index is not covering. Hence SQL has to lookup to the cluster/heap (bookmark lookup) to fetch the remaining rows.
To remove a bookmark lookup from a query either widen the index or remove the columns that are not part of the index from the query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 23, 2009 at 2:13 pm
David O (4/9/2008)
Most of them are pretty good but you got two I'm thinking could be a problem.If you've go the maintenance window. I'd run dbcc dbreindex against the whole table plus you get a full stats update.
David
Hey David,
In order to help everyone else out that is trying to learn this stuff, what do you see that has you thinking there be a problem with two of them?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 23, 2009 at 2:16 pm
WayneS (6/23/2009)
David O (4/9/2008)
Most of them are pretty good but you got two I'm thinking could be a problem.If you've go the maintenance window. I'd run dbcc dbreindex against the whole table plus you get a full stats update.
David
Hey David,
In order to help everyone else out that is trying to learn this stuff, what do you see that has you thinking there be a problem with two of them?
Warning...year and a bit old post...
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply