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
Best Answer
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.