September 15, 2010 at 6:48 am
Here is the 2008 plan. I can not save the 2000 plan. How do I get it? Sorry sitting in middle of another crisis aswell
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
September 15, 2010 at 7:20 am
Check that the Stats are actually updated with 'DBCC SHOW_STATISTICS'
sp_updateStats, to quote BOL
updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows.
The Estimated row counts are way way out of the actual row counts which normal indicates stale stats.
If the the Updated date is not the expected value , force a stats update with UPDATE STATISTICS.
September 15, 2010 at 7:37 am
this is what I ran for all tables
UPDATE STATISTICS dbo.PS_EOCM_EDUP_TMP7 with fullscan
UPDATE STATISTICS dbo.PS_TL_TA_PAY_TM1 with fullscan
UPDATE STATISTICS dbo.PS_EP_GOAL_LANG with fullscan
UPDATE STATISTICS dbo.PS_GVT_ACCR_LDGR with fullscan
UPDATE STATISTICS dbo.PS_CRSE_SESS_EQUIP with fullscan
UPDATE STATISTICS dbo.PS_SAVINGS_INVEST with fullscan
.........
there query runs of views. could there be something to be updated on the views?
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
September 15, 2010 at 7:59 am
Pick one of the problem table, say, PS_EOCM_EDUP_TMP7 and run the following script
select *,StatsDate = stats_date(object_id,stats_id) from sys.stats where object_id = object_id('PS_EOCM_EDUP_TMP7 ')
note the value for StatsDate. If you have just run Update Stats, the StatsDate should be current, otherwise, the stats are not up to date. Pay attention to those system auto generated stats, name starts with _WA_sys_*
September 15, 2010 at 8:09 am
stat date is null what does this mean. if this is a problem how do I force stats update? other than what I already ran.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
September 15, 2010 at 8:27 am
Can you post the view code ?
I would like to see how PS_NAMES ,PS_JOB and PS_PERSON are joined.
September 15, 2010 at 8:36 am
I see that the null is when there is no rows in the table. I ran the following and it returned 0 rows.
select i.name,* from sysindexes i join sysobjects o on i.id = o.id
where i.id in
(select object_id from sys.stats where stats_date(object_id,stats_id) is null)
and rowcnt > 0
All other stats updated today 15 Sept
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
September 15, 2010 at 8:42 am
SELECT A.EMPLID, A.NAME, B.G5_BU, D.JOBTITLE, D.NATIONAL_ID, C.OPRID
FROM dbo.PS_PERSONAL_DATA AS A
INNER JOIN dbo.PS_TRAINING AS B
ON A.EMPLID = B.EMPLID
and (B.ATTENDANCE = 'B')
INNER JOIN dbo.PS_G5_ENROLL_ALL AS C
ON B.COURSE = C.COURSE
AND B.SESSION_NBR = C.SESSION_NBR
INNER JOIN dbo.PS_G5_ALL_EMPLOYEE AS D
ON A.EMPLID = D.EMPLID
INNER JOIN dbo.PSOPRDEFN AS F
ON C.OPRID = F.OPRID
INNER JOIN dbo.PS_EMPLMT_SRCH_QRY AS E
ON F.ROWSECCLASS = E.ROWSECCLASS
AND D.EMPLID = E.EMPLID
AND D.EMPL_RCD = E.EMPL_RCD
2 VIEWS USED
CREATE VIEW PS_PERSONAL_DATA (EMPLID, NAME)
AS
SELECT A.EMPLID , A.NAME
FROM PS_NAMES A
WHERE A.NAME_TYPE = 'PRI'
AND A.EFFDT = ( SELECT MAX(B.EFFDT)
FROM PS_NAMES B
WHERE B.EMPLID = A.EMPLID
AND B.NAME_TYPE = A.NAME_TYPE
AND ( B.EFFDT <= { FN CURDATE() } OR (B.EFFDT > { FN CURDATE() }
AND { FN CURDATE() } < ( SELECT MIN(C.EFFDT)
FROM PS_NAMES C
WHERE C.EMPLID = B.EMPLID
AND C.NAME_TYPE = B.NAME_TYPE)
)
)
)
CREATE VIEW PS_EMPLMT_SRCH_QRY
(EMPLID, EMPL_RCD, ROWSECCLASS, ACCESS_CD, NAME, LAST_NAME_SRCH, NAME_AC, PER_STATUS)
AS
SELECT A.EMPLID ,JOB.EMPL_RCD ,SEC.ROWSECCLASS ,SEC.ACCESS_CD ,
A.NAME ,A.LAST_NAME_SRCH ,A.NAME_AC ,
A.PER_STATUS
FROM PS_PERSONAL_DT_FST A ,
PS_JOB JOB ,PS_SCRTY_TBL_DEPT SEC
WHERE A.EMPLID=JOB.EMPLID
AND (JOB.EFFDT= ( SELECT MAX(JOB2.EFFDT)
FROM PS_JOB JOB2
WHERE JOB.EMPLID=JOB2.EMPLID
AND JOB.EMPL_RCD=JOB2.EMPL_RCD
AND JOB2.EFFDT<={ FN CURDATE() }
)
AND JOB.EFFSEQ= ( SELECT MAX(JOB3.EFFSEQ)
FROM PS_JOB JOB3
WHERE JOB.EMPLID=JOB3.EMPLID
AND JOB.EMPL_RCD=JOB3.EMPL_RCD
AND JOB.EFFDT=JOB3.EFFDT )
)
AND SEC.ACCESS_CD='Y'
AND EXISTS ( SELECT 'X'
FROM PSTREENODE TN
WHERE TN.SETID = SEC.SETID
AND TN.SETID = JOB.SETID_DEPT
AND TN.TREE_NAME='DEPT_SECURITY'
AND TN.EFFDT= SEC.TREE_EFFDT
AND TN.TREE_NODE=JOB.DEPTID
AND TN.TREE_NODE_NUM BETWEEN SEC.TREE_NODE_NUM AND SEC.TREE_NODE_NUM_END
AND NOT EXISTS ( SELECT 'X'
FROM PS_SCRTY_TBL_DEPT SEC2
WHERE SEC.ROWSECCLASS = SEC2.ROWSECCLASS
AND SEC.SETID = SEC2.SETID
AND SEC.TREE_NODE_NUM <> SEC2.TREE_NODE_NUM
AND TN.TREE_NODE_NUM BETWEEN SEC2.TREE_NODE_NUM AND SEC2.TREE_NODE_NUM_END
AND SEC2.TREE_NODE_NUM BETWEEN SEC.TREE_NODE_NUM AND SEC.TREE_NODE_NUM_END )
)
Editor: Cleaned and formatted code
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
September 15, 2010 at 8:45 am
OoomBoom, our issue turned out to be memory related. The server wasn't recognizing all of the memory installed so there was lots of contention between the OS and SQL Server. The server itself was choking on lack of memory. We made a few changes, (although I can't recall exactly what it was, possibly AWE?) and all was good again.
Good luck!
September 15, 2010 at 9:02 am
I do not believe it is that but will check. Did you experiance any kind of waits.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
September 15, 2010 at 9:04 am
Try not using { FN CURDATE() } , which returns a char but getdate which is a datetime type.
September 15, 2010 at 9:20 am
Hi Dave
Thanks for the suggestion. It did not work. The script is still running long (stopped it after 3 min). Do you perhaps know where I can log this for MS. I logged a ticket on social.msdn.microsoft.com but I do not think that this is the correct place for this kind of query.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
September 15, 2010 at 9:21 am
Hi dave
Will rebuilding stats on views help? I do not see any stats on the views.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
September 15, 2010 at 9:32 am
Views dont have stats, as they look to the underlying data.
This is tough to diagnose like this , though i feel the answer is in rewriting you queries.
That will take more time , and more effort , that i have , to do that.
Maybe you should look at getting a consultant in for a few days.
September 15, 2010 at 9:53 am
Looks as though the problem lies within your joins, start bit by bit and don't try to run the whole thing at once. I am seeing things like Hash match in the query plan, which aren't always the best option.
Also, all your join keys, do they have indexes on them ?
Also look at your views and the query inside them, as it could be that one view is the bad egg amongst the entire query, look out for things like scalar functions within joins.
Try and rebuild all your indexes as well, this will force a statistics update again though.
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply