December 5, 2007 at 3:34 am
Hi Guys
I have a Select query that slows down after a while on a production server.
If you stop and restart SQL the query completes after approx 1.5 minutes. After a while the query completes at around 12 - 15 minutes which is nearly ten times as long. Query response time also improves when the database is detached and reattached.
The maintenance plan remains the same regardless of the execution time. The maintenance plan doesnt use table scans only index seeks.
I've not seen this before and am at a bit of a loss.
Does anyone have any suggestions where i can start?
Thanks inadvance.
December 5, 2007 at 4:08 am
can u just put the querry or or some structure of the querry so that one can reply ur querry quicker
December 5, 2007 at 4:12 am
Hi
Just to add the query is generated from a web application which runs a reporting wizard so the statement below was captured using SQL Profiler.
Here is the query:
SELECT count(*) as [TotalVolume], Sum(monCallCharge) as [TotalCost], Sum(intDuration) as [TotalDuration], Avg(monCallCharge) as [AvgCost],
Avg(cast(intDuration as decimal)) as [AvgDuration] FROM dbo.CallSearchL3_2(0,3840,0,29) INNER JOIN
L3_Member_Instance ON dbo.CallSearchL3_2.intMemberID = L3_Member_Instance.intMemberID INNER JOIN
[Member Historic Data] ON [Member Historic Data].intMemberID = L3_Member_Instance.intMemberID
WHERE dbo.CallSearchL3_2.intBillID in (181,251,314,390,419,469,533,591,637,684,734,783,836) AND
[Member Historic Data].intBillID=836 AND dtmDateTime>='2007-08-02 00:00:00' AND dtmDateTime<='2007-11-01 23:59:59' AND chrTransmissionTypeID='V'
AND intRoamStatusID=1 AND bitWeekDay=1 AND bitOutOfHours=0 AND bitInternalCall=1 AND L3_Member_Instance.intGroupID
IN(1815,1720,1409,1410,1411,1722,1412,1468,1469,1470,1471,1472,1473,1163,1398,1422,1721,1423,1424,1425,1426,1427,1428,
1429,1430,1432,1433,1434,1435,1436,1759,1172,1438,1439,1440,1441,1442,1443,1444,1445,1448,1446,1447,1449,1450,1451,1452,1453,
1454,1455,1456,1457,1458,1459,1460,1461,1462,1463,1464,1465,1431,1466) AND L3_Member_Instance.intStructureID =29
December 5, 2007 at 12:58 pm
If the exec plans are identical then ...
Run DBCC SHOWCONTIG() on the 3 tables at the start of the day. Keep the results.
When the slowness is noticed run DBCC SHOWCONTIG() again.
Do you see any noticable changes in the stats for each table. In particular:
- Pages Scanned................................:
- Scan Density [Best Count:Actual Count].......:
- Logical Scan Fragmentation ..................:
- Extent Scan Fragmentation ...................:
December 6, 2007 at 12:05 am
Of course it'll slow down during the day... more locks present on the tables and the query must create an execution plan each time the app generates the code... it's really dynamic SQL on sterioids.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 6, 2007 at 12:07 am
After taking a longer look at this, you could turn it into a stored proc and pass parameters.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 6, 2007 at 3:10 am
PW, I will look at what you have suggested.
Jeff, I should have tried to make it clearer, once the query execution time starts to take 10x longer than usual it never returns back to its original execution regardless of whether anyone else is using the database/s or the server is at its peak time, it requires a restart to get the query to run again at around a minute and half. I also wouldnt be able to change it to an SP at the moment unless i can find out whats going on.
Thanks for your input guys and I will post some more info later.
December 6, 2007 at 9:14 am
1) use nolocks to avoid blocking situations during the day. note there are issues with this giving 'bad' data.
2) if you do put it in a sproc and use parameters I would consider using WITH RECOMPILE option. Parameter sniffing could lead to a bad plan being cached. This assumes that the collection of INs and other where clause arguments vary.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 11, 2007 at 4:46 am
Here are some results from the suggestions, apologies now for the length of the post.
I have run both a trace and execution plan on the same query.
Execution Time 1:43
Trace Results:
SELECT count(*) as [TotalVolume], Sum(monCallCharge) as [TotalCost], Sum(intDuration) as [TotalDuration], Avg(monCallCharge) as [AvgCost], Avg(cast(intDuration as decimal)) as [AvgDuration] FROM dbo.CallSearchL3_2(0,3840,0,29) INNER JOIN L3_Member_Instanc
Duration: 196 CPU: 232 Reads: 214
*************
Execution Time 8:33
Trace Results:
SELECT count(*) as [TotalVolume], Sum(monCallCharge) as [TotalCost], Sum(intDuration) as [TotalDuration], Avg(monCallCharge) as [AvgCost], Avg(cast(intDuration as decimal)) as [AvgDuration] FROM dbo.CallSearchL3_2(0,3840,0,29) INNER JOIN L3_Member_Instanc
Duration: 46 CPU: 231 Reads: 121
*************
Execution Plan Results:
There are 2 areas that take 30/70% in both execution plans
Execution Time 1:43
Nested Loop/Inner Join
Row Count 19254
CPU 0.280
Cost 30%
Estimated Row Count 67025
Number Execute 1
Clustered Index Seek
Nested Loop/Inner Join
Row Count 19254
CPU 0.005
Cost 70%
Number Execute 185359
Estimated Row Count 26
*************
Execution Time 8:33
Nested Loop/Inner Join
Row Count 13262
CPU 0.08
Cost 30%
Estimated Row Count 21012
Number Execute 1
Clustered Index Seek
Nested Loop/Inner Join
Row Count 13262
CPU 0.005
Cost 70%
Number Execute 185359
Estimated Row Count 8
*************
The following are the showcontig results.
-- Results after Offline - Online 1:38
DBCC SHOWCONTIG scanning 'Member Historic Data' table...
Table: 'Member Historic Data' (1255675521); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 824
- Extents Scanned..............................: 111
- Extent Switches..............................: 126
- Avg. Pages per Extent........................: 7.4
- Scan Density [Best Count:Actual Count].......: 81.10% [103:127]
- Logical Scan Fragmentation ..................: 2.91%
- Extent Scan Fragmentation ...................: 13.51%
- Avg. Bytes Free per Page.....................: 218.3
- Avg. Page Density (full).....................: 97.30%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'L3_Member_Instance' table...
Table: 'L3_Member_Instance' (1943677972); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 132
- Extents Scanned..............................: 20
- Extent Switches..............................: 24
- Avg. Pages per Extent........................: 6.6
- Scan Density [Best Count:Actual Count].......: 68.00% [17:25]
- Logical Scan Fragmentation ..................: 3.79%
- Extent Scan Fragmentation ...................: 30.00%
- Avg. Bytes Free per Page.....................: 364.2
- Avg. Page Density (full).....................: 95.50%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
********************
-- Results after Offline - Online 8:33
DBCC SHOWCONTIG scanning 'Member Historic Data' table...
Table: 'Member Historic Data' (1255675521); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 805
- Extents Scanned..............................: 103
- Extent Switches..............................: 102
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 98.06% [101:103]
- Logical Scan Fragmentation ..................: 0.37%
- Extent Scan Fragmentation ...................: 0.97%
- Avg. Bytes Free per Page.....................: 73.5
- Avg. Page Density (full).....................: 99.09%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'L3_Member_Instance' table...
Table: 'L3_Member_Instance' (1943677972); index ID: 1, database ID: 5
TABLE level scan performed.
- Pages Scanned................................: 127
- Extents Scanned..............................: 16
- Extent Switches..............................: 15
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 100.00% [16:16]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.00%
- Avg. Bytes Free per Page.....................: 60.9
- Avg. Page Density (full).....................: 99.25%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*************
The results dont really seem that much different the only items i think stand out are the number of executions and the estimated rows and actual rows.
The query aquires and releases thousands of locks during execution does this account for the number of executions then?
December 11, 2007 at 6:41 am
1) Using functions like this very often lead the optimizer astray. It can't perform properly because it doesn't know how many rows and what the value distributions are going to be on the output of the funciton.
2) You don't have a join ON clause
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 11, 2007 at 7:01 am
Your DBCC results are interesting, because they appear to be the opposite of what I was expecting.
Since you stated that the execution plan remained the same, yet execution times got worse, I wanted to see if the table or tables were becoming highly fragmented, causing much increased disk I/O for the same plan.
This doesn't appear to be the case. Since fragmentation is actually improving, is there any maintenance running during the online period ? Are there DBCC IndexDefrag commands running ?
December 11, 2007 at 7:23 am
Hi PW
You're correct defraging does occur every weekend so fragmentation has improved.
I'm still curious about the number of executions and locks is that because of the function use? Also it looks like my copy and paste has chopped off the end of the statement, thats why there is no "on" statement.
December 11, 2007 at 8:12 am
Can you give us some more details about the function dbo.CallSearchL3_2(). What does it do internally ? Does it use tempdb ?
Does you system have any tables pinned in memory ?
December 11, 2007 at 8:33 am
I'm curious.
Does this
create table #tmp1(TmpintGroupID int)
insert into #tmp1 values(1815)
insert into #tmp1 values(1720)
insert into #tmp1 values(1409)
...
insert into #tmp1 values(1466)
SELECT count(*) as [TotalVolume], Sum(monCallCharge) as [TotalCost], Sum(intDuration) as [TotalDuration], Avg(monCallCharge) as [AvgCost],
Avg(cast(intDuration as decimal)) as [AvgDuration] FROM dbo.CallSearchL3_2(0,3840,0,29) INNER JOIN
L3_Member_Instance ON dbo.CallSearchL3_2.intMemberID = L3_Member_Instance.intMemberID INNER JOIN
[Member Historic Data] ON [Member Historic Data].intMemberID = L3_Member_Instance.intMemberID
inner join #tmp1 t on t.TmpintGroupID = L3_Member_Instance.intGroupID
WHERE dbo.CallSearchL3_2.intBillID in (181,251,314,390,419,469,533,591,637,684,734,783,836) AND
[Member Historic Data].intBillID=836 AND dtmDateTime>='2007-08-02 00:00:00' AND dtmDateTime<='2007-11-01 23:59:59' AND chrTransmissionTypeID='V'
AND intRoamStatusID=1 AND bitWeekDay=1 AND bitOutOfHours=0 AND bitInternalCall=1 AND AND L3_Member_Instance.intStructureID =29
work much faster then previous select
SELECT count(*) as [TotalVolume], Sum(monCallCharge) as [TotalCost], Sum(intDuration) as [TotalDuration], Avg(monCallCharge) as [AvgCost],
Avg(cast(intDuration as decimal)) as [AvgDuration] FROM dbo.CallSearchL3_2(0,3840,0,29) INNER JOIN
L3_Member_Instance ON dbo.CallSearchL3_2.intMemberID = L3_Member_Instance.intMemberID INNER JOIN
[Member Historic Data] ON [Member Historic Data].intMemberID = L3_Member_Instance.intMemberID
WHERE dbo.CallSearchL3_2.intBillID in (181,251,314,390,419,469,533,591,637,684,734,783,836) AND
[Member Historic Data].intBillID=836 AND dtmDateTime>='2007-08-02 00:00:00' AND dtmDateTime<='2007-11-01 23:59:59' AND chrTransmissionTypeID='V'
AND intRoamStatusID=1 AND bitWeekDay=1 AND bitOutOfHours=0 AND bitInternalCall=1 AND L3_Member_Instance.intGroupID
IN(1815,1720,1409,1410,1411,1722,1412,1468,1469,1470,1471,1472,1473,1163,1398,1422,1721,1423,1424,1425,1426,1427,1428,
1429,1430,1432,1433,1434,1435,1436,1759,1172,1438,1439,1440,1441,1442,1443,1444,1445,1448,1446,1447,1449,1450,1451,1452,1453,
1454,1455,1456,1457,1458,1459,1460,1461,1462,1463,1464,1465,1431,1466) AND L3_Member_Instance.intStructureID =29
?
lp, Matjaz
December 11, 2007 at 9:11 am
Guys heres the function:
CREATE FUNCTION CallSearchL3_2 (@intInstanceID int,@intCustomerID int, @bitShowPersonal bit, @structure int)
RETURNS TABLE AS
RETURN
(
SELECT
dbo.Call.intCallID, dbo.Member.vchrMember,
dbo.Call.vchrConvertedDialledDigits,
ISNULL(vab.vchrName + ' (' + vab.vchrDestination + ')',
dbo.Call.vchrDialledLocation) AS vchrDialledLocation,
dbo.Call.dtmCallStartDateTime AS dtmDateTime,
dbo.TimeAsLong(dbo.Call.dtmCallStartDateTime) AS intCallTime,
dbo.Call.intDuration,
dbo.Call.monCallCharge,
dbo.Member.intCustomerID,
dbo.Member.intMemberID,
dbo.[Cost Centre].intCCID,
dbo.[Cost Centre].intBillID,
dbo.Call.vchrDiallingCode,
dbo.[Member Historic Data].vchrLabel,
dbo.Call.chrTransmissionTypeID,
dbo.[Area Code Parent Category].intCategoryID AS intParentCategoryID,
dbo.Call.bitInternalCall,
dbo.Call.intRoamStatusID,
dbo.Call.bitWeekday,
dbo.Call.bitOutOfHours,
dbo.Call.tintVPN,
dbo.Call.tintBundle,
dbo.Call.tintTaxID,
dbo.DateOnly(dbo.Call.dtmCallStartDateTime) AS dtmDateOnly,
dbo.DayOfWeekInt(dbo.Call.dtmCallStartDateTime) AS intDayOfWeek,
dbo.[Network Description].vchrNetworkDescription,
dbo.L3_Member_Instance.intID AS intInstanceID,
dbo.L3_Member_Instance.intGroupID,
dbo.L3_Member_Instance.intStructureID,
ISNULL(vab.tintABType,0) AS tintABType,
dbo.Call.tintRechargeTagStatus
FROM
dbo.Call
INNER JOIN
dbo.Member ON dbo.Call.intMemberID = dbo.Member.intMemberID
INNER JOIN
dbo.L3_Member_Instance ON dbo.Member.intMemberID = dbo.L3_Member_Instance.intMemberID
INNER JOIN
dbo.Invoice ON dbo.Call.intInvoiceID = dbo.Invoice.intInvoiceID
INNER JOIN
dbo.[Cost Centre] ON dbo.Invoice.intCCId = dbo.[Cost Centre].intCCID
INNER JOIN
dbo.[Member Historic Data] ON dbo.[Member Historic Data].intMemberID = dbo.Member.intMemberID
AND
dbo.[Member Historic Data].intBillID = dbo.[Cost Centre].intBillID
INNER JOIN
dbo.[Area Code Category] ON dbo.Call.chrCategoryID = dbo.[Area Code Category].chrCategoryID
INNER JOIN
dbo.[Area Code Parent Category] ON dbo.[Area Code Category].intParentGroupID = dbo.[Area Code Parent Category].intCategoryID
INNER JOIN
dbo.[Network Description] ON dbo.Call.tintNetworkID = [Network Description].tintNetworkID
LEFT OUTER JOIN
dbo.AddressBookEntries(@intInstanceID, @intCustomerID, @bitShowPersonal) vab ON vab.intCustomerID = dbo.Member.intCustomerID
AND
vab.vchrTelephone = dbo.Call.vchrConvertedDialledDigits
WHERE
dbo.Member.intCustomerID = @intCustomerID
AND
dbo.L3_Member_Instance.intStructureID = @structure
)
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply