Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 7,2000 » Performance Tuning » Bookmark Lookup Options...
Add to briefcase
16 posts, Page 1 of 2
12»»

Bookmark Lookup Options... Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
bradfordshultz
Posted Tuesday, April 08, 2008 7:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 31, 2010 2:01 PM
Points: 11, Visits: 101
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.
Post #481968
SQL ORACLE
Posted Tuesday, April 08, 2008 8:06 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 05, 2013 4:43 PM
Points: 1,473, Visits: 1,314
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.
Post #481979
bradfordshultz
Posted Tuesday, April 08, 2008 8:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 31, 2010 2:01 PM
Points: 11, Visits: 101
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.
Post #481987
David O
Posted Tuesday, April 08, 2008 8:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:50 AM
Points: 170, Visits: 702
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
Post #481988
PW-201837
Posted Tuesday, April 08, 2008 9:01 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 4:46 PM
Points: 1,415, Visits: 1,206
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.
Post #482009
GilaMonster
Posted Wednesday, April 09, 2008 12:37 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:33 AM
Points: 37,707, Visits: 29,963
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #482079
bradfordshultz
Posted Wednesday, April 09, 2008 1:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 31, 2010 2:01 PM
Points: 11, Visits: 101
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.
Post #482572
SQL ORACLE
Posted Wednesday, April 09, 2008 3:05 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 05, 2013 4:43 PM
Points: 1,473, Visits: 1,314
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.
Post #482640
David O
Posted Wednesday, April 09, 2008 3:16 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:50 AM
Points: 170, Visits: 702
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
Post #482647
srienstr
Posted Thursday, April 10, 2008 7:37 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 6:58 AM
Points: 210, Visits: 372
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.

Puto me cogitare, ergo puto me esse.
I think that I think, therefore I think that I am.
Post #483030
« Prev Topic | Next Topic »

Add to briefcase
16 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.