Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Bookmark Lookup Options...


Bookmark Lookup Options...

Author
Message
bradfordshultz
bradfordshultz
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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.
SQL ORACLE
SQL ORACLE
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1487 Visits: 1314
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.
bradfordshultz
bradfordshultz
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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.
David O
David O
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 913
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
PW-201837
PW-201837
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1415 Visits: 1228
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.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47235 Visits: 44375
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


bradfordshultz
bradfordshultz
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
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.
SQL ORACLE
SQL ORACLE
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1487 Visits: 1314
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.
David O
David O
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 913
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
srienstr
srienstr
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 433
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search