March 12, 2009 at 2:12 am
One more point:
I've come at work on weekend to check the server, today no one is using that server because its holiday. But still the sqlserver is so slow and the same deleteArea stored procedure times out. so it might not be due to slow queries, missing indexes etc. what do you think? isn't there any other thing that I can check and modify?
March 12, 2009 at 2:30 am
can you free the proccache ?
(to get rid of the "unperformant" plans)
dbcc freeproccache
Maybe now (holliday) is the moment to rebuild indexes, ...
befor you implement the new ones.
We've recently had a server which didn't perform as we would expect.
Reason: Someone turned of parallel plans and worker threads.
exec sp_configure 'max degree of parallelism' -- will show the value for this setting
exec sp_configure 'max worker threads' -- will show the value for this setting
After setting these settings to their default values, everithing started to work as we would have expected.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 12, 2009 at 3:31 am
I rebuilt indexes and updated statistics however that values for worker thread etc is as follow:
name min max config_value run_value
max degree of parallelism 0 64 0 0
max worker threads 128 32767 0 0
we have 140 connections at maximum level. so that might not be the problem. Am I true?
March 12, 2009 at 6:43 am
you stated cpu and memory were ok.
What's the ammount of physical RAM on the windows server, how much is in use and how much is in use by sqlserver ?
Any other memory hogs ?
Any IO bottlenecks ? (io wait times)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 13, 2009 at 11:34 pm
you stated cpu and memory were ok.
yes it is
What's the amount of physical RAM on the windows server, how much is in use and how much is in use by sqlserver ?
the server has 8GB of RAM. PF usage was about 7GB the day before yesterday but when we restarted the server it became 1 GB and is still 1 GB
Any IO bottlenecks ? (io wait times)
there's no IO bottleneck now but still the server is slow
I really need help cause its a production server and needs to be fixed soon 🙁
March 14, 2009 at 12:17 am
I had run a profiler trace (standard template) but no recommendation is reported by DTS!! and only 38% of the trace is analyzed then it stops reporting errors!!
March 14, 2009 at 1:27 am
Hi;
In your cursor try to use this hint FAST_FORWARD
DECLARE XXXCURSORXXX CURSOR LOCAL FAST_FORWARD FOR
Esat Erkec
March 14, 2009 at 1:29 am
here's the stored procedure that most of the times cause time out. on the previous messages u said that some clustered indexes should be defined on related tables. I'd like to know if the stored procedure also can be tuned.
March 14, 2009 at 7:11 am
peace2007 (3/14/2009)
here's the stored procedure that most of the times cause time out. on the previous messages u said that some clustered indexes should be defined on related tables. I'd like to know if the stored procedure also can be tuned.
Can you post the DDL for EDMS.vw_AreaHierarchy ?
(and the refered tables)
Why do you use a cursor ?
You encapsulate it all in one transaction, so you might as well delete
the child-rows directly starting from your temptb or view.
To feed your history, use the output clause of the delete statement
of the parent object !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 14, 2009 at 7:27 am
this is the view:
WITH Tree(PageId, ParentId, PageTitle, strPath, strPathName, [Level]) AS (SELECT PageID, ParentID, PageTitle, CAST(PageID AS varchar(MAX)) AS strPath,
CAST(PageTitle AS varchar(MAX)) AS strPathName, CAST(1 AS int)
AS [Level]
FROM EDMS.tb_SiteStructure
WHERE (ParentID = '-2')
UNION ALL
SELECT ST.PageID, ST.ParentID, ST.PageTitle,
T.strPath + '/' + CAST(ST.PageID AS varchar(MAX)) AS Expr1,
T.strPathName + ' > ' + CAST(ST.PageTitle AS varchar(MAX)) AS Tree,
T.[Level] + 1 AS [Level]
FROM EDMS.tb_SiteStructure AS ST INNER JOIN
Tree AS T ON ST.ParentID = T.PageId)
SELECT TOP (100) PERCENT PageId, ParentId, PageTitle, strPath, strPathName, [Level]
FROM Tree AS Tree_1
ORDER BY strPath
March 14, 2009 at 7:50 am
Did you try to handle it this way ?
ALTER PROCEDURE [EDMS].[sp_DeleteArea]
@PageID int,
@DeletedBy varchar(100)
AS
BEGIN
Set Nocount on
CREATE TABLE #vw_AreaHierarchyTemp
(
PageId bigint,
ParentId bigint,
PageTitle varchar(500),
strPathvarchar(max), -- don't use varchar(max) if you can use varchar(5000) !!
strPathNamevarchar(max), -- don't use varchar(max) if you can use varchar(5000) !!
[Level] int
)
CREATE index #vw_AreaHierarchyTempX on #vw_AreaHierarchyTemp (PageId)
Declare
@ParentID int,
@pid bigint,
@parId bigint,
@ptitle varchar(500),
@InheritOrNo SMALLINT,
@ParentPageID int,
@ThisPage int
BEGIN TRY
BEGIN TRANSACTION -- Start the transaction
--Keep view data in temp
insert into #vw_AreaHierarchyTemp
select * from EDMS.vw_AreaHierarchy
-- find pages that must be delete and insert in history table
-- Delete Users and Groups of this page
Delete UPA
From EDMS.tb_UserPageAccess UPA
inner join EDMS.tb_SiteStructure TS
on TS.PageID = UPA.PageID
-- IS THIS PART STILL NEEDEDand TS.inherit = 0
inner join #vw_AreaHierarchyTemp tmp
on TS.PageID = tmp.PageID
where tmp.strPath like '%/'+convert(varchar(50),@PageID)+'/%')
OR (tmp.strPath like '%/'+convert(varchar(50),@PageID))
Delete GPA
from EDMS.tb_GroupPageAccess GPA
inner join EDMS.tb_SiteStructure TS
on TS.PageID = GPA.PageID
-- IS THIS PART STILL NEEDEDand TS.inherit = 0
inner join #vw_AreaHierarchyTemp tmp
on TS.PageID = tmp.PageID
where tmp.strPath like '%/'+convert(varchar(50),@PageID)+'/%')
OR (tmp.strPath like '%/'+convert(varchar(50),@PageID))
--********HISTORY
DELETE TS
from EDMS.tb_SiteStructure TS
inner join #vw_AreaHierarchyTemp tmp
on TS.PageID = tmp.PageID
OUTPUT ( TS.PageID, TS.ParentID, TS.PageTitle ts.PageID, 'Delete', @DeletedBy)
into EDMS.tb_PageHistory (h_PageID,h_ParentID,h_InheritParentID,h_PTitle,h_Action,h_Date,h_UserName)
where tmp.strPath like '%/'+convert(varchar(50),@PageID)+'/%')
OR (tmp.strPath like '%/'+convert(varchar(50),@PageID))
End
COMMIT TRANSACTION
END TRY
BEGIN CATCH
-- Whoops, there was an error
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
END CATCH
END
Keep in mind this may still cause a huge transaciton.
You may want to optimize it handling one (or a couple of) PageID per transaction to lesser the transactional impact ( + the locking consequences)
...edited...
TEST IT _ TEST IT _ TEST IT !!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 16, 2009 at 12:32 am
I'll test it
Thank you ALZDBA 🙂
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply