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. Commented 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 :-) Commented 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 Commented 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 Commented 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. Commented Dec 1, 2016 at 22:31

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.