2

I have this view vw_RFQPartVendor that's based on these two views vw_requestVendor and vw_vendorEmail and various other tables.

Every few days, it will cause time out error in my ASP.Net website and all I have to do is rebuild index of the underlying tables in view vw_RFQPartVendor and the time out error is gone.

What can I do to resolve this? I don't want to rebuild index every now and then.

2016-11-24 08:40:34,319 [10] ERROR BasePage [(null)] - ObjectDataSourceReport_Selected Error System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

vw_RFQPartVendor 83250 rows.

SELECT TOP (100) PERCENT
    dbo.SubconJobQ.Regno,
    CASE
        WHEN userName.userName IS NULL THEN UserAcc
        ELSE userName.userName
    END AS requesterName,
    CASE dbo.SubconJobQ.IsPara
        WHEN '1' THEN 'yes'
        ELSE 'no'
    END AS IsPara,
    CASE dbo.SubconJobQ.IsDxf
        WHEN '1' THEN 'yes'
        ELSE 'no'
    END AS IsDxf,
    CASE dbo.SubconJobQ.IsPdf
        WHEN '1' THEN 'yes'
        ELSE 'no'
    END AS IsPdf,
    dbo.SubconJobQ.Rec_date,
    dbo.SubconJobQ.Cov_date,
    dbo.SubconJobQ.Status,
    dbo.SubconJobQ.Remark,
    dbo.SubconJobQ.plant,
    dbo.SubconJobQ.doc_support,
    CASE
        WHEN approverName.userName IS NULL THEN approver
        ELSE approverName.userName
    END AS approverName,
    dbo.SubconJobQ.Comment,
    dbo.SubconJobQ.sync_status,
    dbo.SubconJobQ.ModuleNo,
    CASE dbo.SubconJobQ.req_type
        WHEN 'sq' THEN 'SubContracting Part'
        WHEN 'sd' THEN 'Resend Drawing'
        WHEN 'pq' THEN 'New Part'
        ELSE ''
    END AS req_type,
    dbo.SubconJobQ.projectNo,
    dbo.Reg_parts.Partno,
    dbo.Reg_parts.Quantity,
    dbo.Reg_parts.Part_desc,
    dbo.vw_requestVendor.vendorCode,
    dbo.vw_vendorEmail.vendorEmails
FROM dbo.SubconJobQ
LEFT OUTER JOIN dbo.Reg_parts
    ON dbo.SubconJobQ.Regno = dbo.Reg_parts.Regno
LEFT OUTER JOIN dbo.vw_requestVendor
    ON dbo.SubconJobQ.Regno = dbo.vw_requestVendor.Regno
LEFT OUTER JOIN dbo.vw_vendorEmail
    ON dbo.vw_requestVendor.vendorCode = dbo.vw_vendorEmail.vendor
    AND dbo.SubconJobQ.plant = dbo.vw_vendorEmail.plant
LEFT OUTER JOIN 
    (
        SELECT
        user_acc,
        userName
        FROM dbo.t_user_auth
        GROUP BY    user_acc, userName
    ) AS userName
    ON userName.user_acc = dbo.SubconJobQ.UserAcc
LEFT OUTER JOIN 
    (
        SELECT
        user_acc,
        userName
        FROM dbo.t_user_auth AS t_user_auth_1
        GROUP BY    user_acc, userName
    ) AS approverName
    ON approverName.user_acc = dbo.SubconJobQ.approver
ORDER BY dbo.SubconJobQ.Regno, dbo.Reg_parts.Partno, dbo.vw_requestVendor.vendorCode

vw_requestVendor

WITH temp(Regno, vendorCode, vendor) AS 
    ( 
        SELECT
            Regno,
            LEFT(CAST(vendor AS NVARCHAR(MAX)), CHARINDEX(';', CAST(vendor AS NVARCHAR(MAX)) + ';') - 1) AS Expr1,
            STUFF(CAST(vendor AS NVARCHAR(MAX)), 1, CHARINDEX(';', CAST(vendor AS NVARCHAR(MAX)) + ';'), '') AS Expr2
        FROM dbo.SubconJobQ 
        UNION ALL 
        SELECT
            Regno,
            LEFT(CAST(vendor AS NVARCHAR(MAX)), CHARINDEX(';', CAST(vendor AS NVARCHAR(MAX)) + ';') - 1) AS Expr1,
            STUFF(CAST(vendor AS NVARCHAR(MAX)), 1, CHARINDEX(';', CAST(vendor AS NVARCHAR(MAX)) + ';'), '') AS Expr2
        FROM temp AS temp_2
        WHERE (vendor > '')
    )
SELECT TOP (100) PERCENT
    Regno,
    vendorCode
FROM temp AS temp_1
ORDER BY Regno, vendorCode

vw_vendorEmail

SELECT TOP 100 PERCENT
    *
FROM 
(
    SELECT
    outerVendorEmail.comp_code,
    outerVendorEmail.plant,
    outerVendorEmail.vendor,
    STUFF
    (
        (
            SELECT ';' + LTRIM(RTRIM(innerVendorEmail.email))
            FROM t_vendor_email AS innerVendorEmail
            WHERE innerVendorEmail.status = 1
            AND innerVendorEmail.comp_code = outerVendorEmail.comp_code
            AND innerVendorEmail.plant = outerVendorEmail.plant
            AND innerVendorEmail.vendor = outerVendorEmail.vendor
            FOR XML PATH ('')
        ), 1, 1, ''
    ) AS [vendorEmails]
    FROM t_vendor_email AS outerVendorEmail
) AS vendorEmails
GROUP BY    vendorEmails.comp_code,
            vendorEmails.plant,
            vendorEmails.vendor,
            vendorEmails.vendorEmails
ORDER BY vendorEmails.comp_code, vendorEmails.plant, vendorEmails.vendor

| TABLE_NAME | COLUMN_NAME          | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE     | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_PRECISION_RADIX | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_CATALOG | CHARACTER_SET_SCHEMA | CHARACTER_SET_NAME | COLLATION_CATALOG | COLLATION_SCHEMA | COLLATION_NAME               | DOMAIN_CATALOG | DOMAIN_SCHEMA | DOMAIN_NAME |
|------------|----------------------|------------------|----------------|-------------|---------------|--------------------------|------------------------|-------------------|-------------------------|---------------|--------------------|-----------------------|----------------------|--------------------|-------------------|------------------|------------------------------|----------------|---------------|-------------|
| SubconJobQ | Partnos              | 1                | NULL           | YES         | ntext         | 1073741823               | 2147483646             | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | UNICODE            | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| SubconJobQ | Regno                | 2                | NULL           | NO          | char          | 12                       | 12                     | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | iso_1              | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| SubconJobQ | UserAcc              | 3                | NULL           | NO          | varchar       | 50                       | 50                     | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | iso_1              | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| SubconJobQ | IsPara               | 4                | ((0))          | NO          | char          | 1                        | 1                      | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | iso_1              | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| SubconJobQ | IsDxf                | 5                | ((0))          | NO          | char          | 1                        | 1                      | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | iso_1              | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| SubconJobQ | IsPdf                | 6                | ((1))          | NO          | char          | 1                        | 1                      | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | iso_1              | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| SubconJobQ | Rec_date             | 7                | NULL           | NO          | smalldatetime | NULL                     | NULL                   | NULL              | NULL                    | NULL          | 3                  | NULL                  | NULL                 | NULL               | NULL              | NULL             | NULL                         | NULL           | NULL          | NULL        |
| SubconJobQ | Cov_date             | 8                | NULL           | YES         | smalldatetime | NULL                     | NULL                   | NULL              | NULL                    | NULL          | 3                  | NULL                  | NULL                 | NULL               | NULL              | NULL             | NULL                         | NULL           | NULL          | NULL        |
| SubconJobQ | Status               | 9                | NULL           | YES         | char          | 1                        | 1                      | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | iso_1              | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| SubconJobQ | Remark               | 10               | NULL           | YES         | text          | 2147483647               | 2147483647             | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | iso_1              | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| SubconJobQ | plant                | 11               | NULL           | YES         | char          | 4                        | 4                      | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | iso_1              | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| SubconJobQ | vendor               | 12               | NULL           | NO          | varchar       | 200                      | 200                    | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | iso_1              | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| SubconJobQ | doc_support          | 13               | NULL           | YES         | char          | 1                        | 1                      | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | iso_1              | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| SubconJobQ | approver             | 14               | NULL           | YES         | varchar       | 50                       | 50                     | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | iso_1              | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| SubconJobQ | Comment              | 15               | NULL           | YES         | nvarchar      | 100                      | 200                    | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | UNICODE            | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| SubconJobQ | sync_status          | 16               | NULL           | YES         | char          | 1                        | 1                      | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | iso_1              | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| SubconJobQ | UserAccEmail         | 17               | NULL           | YES         | nchar         | 100                      | 200                    | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | UNICODE            | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| SubconJobQ | ModuleNo             | 18               | NULL           | YES         | nchar         | 20                       | 40                     | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | UNICODE            | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| SubconJobQ | manager_approver     | 19               | NULL           | YES         | nvarchar      | 50                       | 100                    | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | UNICODE            | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| SubconJobQ | req_type             | 20               | NULL           | YES         | char          | 2                        | 2                      | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | iso_1              | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| SubconJobQ | purchasing_manager   | 21               | NULL           | YES         | nvarchar      | 50                       | 100                    | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | UNICODE            | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| SubconJobQ | change_buyer         | 22               | NULL           | YES         | nvarchar      | 50                       | 100                    | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | UNICODE            | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| SubconJobQ | change_buyer_manager | 23               | NULL           | YES         | nvarchar      | 50                       | 100                    | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | UNICODE            | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| SubconJobQ | projectNo            | 24               | NULL           | YES         | char          | 20                       | 20                     | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | iso_1              | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |

| TABLE_NAME     | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_PRECISION_RADIX | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_CATALOG | CHARACTER_SET_SCHEMA | CHARACTER_SET_NAME | COLLATION_CATALOG | COLLATION_SCHEMA | COLLATION_NAME               | DOMAIN_CATALOG | DOMAIN_SCHEMA | DOMAIN_NAME |
|----------------|-------------|------------------|----------------|-------------|-----------|--------------------------|------------------------|-------------------|-------------------------|---------------|--------------------|-----------------------|----------------------|--------------------|-------------------|------------------|------------------------------|----------------|---------------|-------------|
| t_vendor_email | comp_code   | 1                | NULL           | YES         | nchar     | 4                        | 8                      | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | UNICODE            | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| t_vendor_email | plant       | 2                | NULL           | YES         | nchar     | 4                        | 8                      | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | UNICODE            | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| t_vendor_email | vendor      | 3                | NULL           | YES         | nchar     | 10                       | 20                     | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | UNICODE            | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| t_vendor_email | email       | 4                | NULL           | YES         | nchar     | 100                      | 200                    | NULL              | NULL                    | NULL          | NULL               | NULL                  | NULL                 | UNICODE            | NULL              | NULL             | SQL_Latin1_General_CP1_CI_AS | NULL           | NULL          | NULL        |
| t_vendor_email | status      | 5                | NULL           | YES         | int       | NULL                     | NULL                   | 10                | 10                      | 0             | NULL               | NULL                  | NULL                 | NULL               | NULL              | NULL             | NULL                         | NULL           | NULL          | NULL        |

Statistics for INDEX 'ix_RecDate' table 'SubconJobQ'.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Name                            Updated                         Rows                            Rows Sampled                    Steps                           Density                         Average Key Length              String Index                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ix_RecDate                      Nov 24 2016  8:41AM             15099                           15099                           177                             0.9686973                       22.82734                        NO                              

All Density                     Average Length                  Columns                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6.879472E-05                    4                               Rec_date                        
6.622955E-05                    16                              Rec_date, Regno                 
6.622955E-05                    22.82734                        Rec_date, Regno, vendor         

Histogram Steps                 
RANGE_HI_KEY                    RANGE_ROWS                      EQ_ROWS                         DISTINCT_RANGE_ROWS             AVG_RANGE_ROWS                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2010-02-26 4:40:00 PM           0                               1                               0                               1                               
2010-03-02 3:12:00 PM           30                              2                               30                              1                               

Statistics for INDEX 'PK_SubconJobQ' table 'SubconJobQ'.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Name                            Updated                         Rows                            Rows Sampled                    Steps                           Density                         Average Key Length              String Index                    
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PK_SubconJobQ                   Nov 24 2016  8:41AM             15099                           15099                           148                             1                               18.82734                        YES                             

All Density                     Average Length                  Columns                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6.622955E-05                    12                              Regno                           
6.622955E-05                    18.82734                        Regno, vendor                   

Histogram Steps                 
RANGE_HI_KEY                    RANGE_ROWS                      EQ_ROWS                         DISTINCT_RANGE_ROWS             AVG_RANGE_ROWS                  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
D00000000016                    0                               1                               0                               1                               
D00000000111                    94                              1                               94                              1

Execution plan for vw_RFQPartVendor

Improve this question
3
  • How about removing the sorts from all of those views? Shouldn't sort in the database that way. Surefire way to destroy perf.
    – Nic
    Nov 28, 2016 at 19:54
  • From your plan, the est vs actual varies vastly .. I would look into stats being updated frequently or you can just use sp_refreshview view_name to generate a new plan. Also, there is a table scan on Reg_parts -- no index ?? Create your views with schemabinding option to help optimizer help your queried :-)
    – Kin Shah
    Nov 28, 2016 at 21:03
  • Cross posted to codereview.stackexchange.com/questions/148063/… which appears to be tolerated on that sub, not voting to close since you got more feedback here so far
    – Tom V
    Dec 1, 2016 at 21:26

1 Answer 1

Reset to default
1

I suspect that the performance issue that you're experiencing has to do with statistics. When you rebuild an index SQL Server also updates the statistics on that index with a sample size of 100%. In general you can experience issues both if statistics are updated too often and if they aren't updated often enough.

For an example of the first type of problem, suppose that there's something about your data that requires a statistics sample of 100% of the rows to generate a good plan. An example of when this could happen is if the data is highly skewed. By default SQL Server will update the statistics with an auto sample size after 20% of the rows in the table are changed. SQL Server may choose a sample size less than 100% of the rows in the table. After a statistics update you may experience a worse plan than what you had with the 100% sample size.

For an example of the second type of problem, consider a date column for which new data always has a date of today. If statistics were last updated a week ago and you filter on the date column for just today then by default the cardinality estimator will think that your filter will eliminate all rows. The filtered value is outside of all of the RANGE_HI_KEY values in the statistics histogram. This is known as the "ascending key problem".

Based on what you've provided here, my advice would be to save off a good query plan and to save off a bad query plan when you start experiencing the issue again. Compare the two plans. Update the statistics one at a time to see if you find the one that fixes the issue. You can then create a job to update the statistics on that index daily using the FULLSCAN option. That should have less of a maintenance overhead than rebuilding the index.

If you don't want to rebuild the statistics daily there might be other ways to address the performance problem that you're experiencing, but that would require the exact query that you're running along with a good and bad query plan. It's also possible that the issue has nothing to do with statistics. Getting more information should make that clear.

Improve this answer
2
  • You might want to mention trace flag 2371
    – Tom V
    Dec 1, 2016 at 21:44
  • @TomV The reason that I didn't dive into more prescriptive solutions is that it isn't clear what the statistics issue is based on his query. I suspect that my guess is wrong or that he withheld important information in the question. You are right that trace flag 2371 can be a good way to deal with the ascending key problem.
    – Joe Obbish
    Dec 1, 2016 at 22:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.