Trace analysis sql server 2005

Hi experts iam seeing one trace profiler in that iam seeing audit logout event is showing
lots of Reads , iam failing to understand how much or why it is happening so high.

File is attached .
Thanks
trace.JPG
SandeepiiiAsked:
Who is Participating?
 
patriktConnect With a Mentor Commented:
May be attached procedure will help you optimize that DB.
Run the script with query output to Text and you will get suggested indexes scripts.
It generates SUGGESTIONS based on DM views. Don't use it without reading.
MissingIndexes.txt
0
 
chapmandewCommented:
You probably have too many events select or have a high volume server.  What are you trying to find?
0
 
patriktCommented:
You are misleaded by column names :) AuditLogon event show session totals in Duration, Reads atc.
Numbers you see there are fo whole session which just ended.

Patrik
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
SandeepiiiAuthor Commented:
thanks there are lots of updations going on due to which in activity monitor
it is showing suspended plus PAGEIOLATCH_EX,PAGEIOLATCH_SH are lots there.but i can see PAGEIOLATCH_SH  more in the activity monitor.

what can I do?
there is any hint or option to test?
which counter of performance I have to follow?

 
0
 
SandeepiiiAuthor Commented:
iam seeing in locks by object it is showing process id 57 which is update query
is showing .can you please explain how can i interpret it .

thanks
lock.JPG
lock2.JPG
0
 
patriktCommented:
You should start monitoring disk activity, namely Phisical Disk Queue lenght.
Also check if you can get more memory for SQL, check what is currently allocated.

Patrik
0
 
patriktCommented:
I see no problem with process 57. It is holding locks but it isnormal when updating.
Is there any other process which is blocked by 57? What are your current problems?
0
 
SandeepiiiAuthor Commented:
well the user says it is taking lot of time and is slow .
0
 
chapmandewCommented:
run this to see what statement that spid is running:

dbcc inputbuffer(57)

maybe it is a long running query or something.
0
 
SandeepiiiAuthor Commented:
well on the server it is 4GB but now sql server is consuming 1.8 gb .
disk Q lenght is high during these time  60-70 thn it comes down to less than 1.
0
 
SandeepiiiAuthor Commented:
its update command update itemmaster_in set status=1 where status=0
and wdshipto='jp2330'
0
 
chapmandewCommented:
OK...does your wdshipto field have an index on it?  
0
 
patriktCommented:
Looks like you have disk problem. Disk Que should not raise above 2 times numbr of disks in RAID. If I suppose 4 disk RAID 1 you should see queue length 8 maximum. If Queue is larger you are waiting on disk, check it.

0
 
SandeepiiiAuthor Commented:
wdshipto is a primary key
0
 
chapmandewCommented:
anything returned from this?

select * from sysprocesses
where blocked > 0
0
 
patriktCommented:
You write that wdshipto is PK, is it clustered or you have other clustered index?
0
 
SandeepiiiAuthor Commented:
no it is not returning any thing
0
 
patriktCommented:
Wher is the size of itemmaster_in?

You are writing that only 1.8 GB  is allocted to SQL. You should enable AWE and give SQL access to other memory (if you have x64 this is irrelevant).
0
 
patriktCommented:
Are you using Cursors? This might be problem if that simple update is comming from inside of cursor loop.
0
 
SandeepiiiAuthor Commented:
1061176 rows are there awe is not enabled and x86 actually wht iam saying is in
task manager it is showing sql server.exe is consuming 1.7 gb
0
 
chapmandewCommented:
That isn't too out of the ordinary in terms of the memory SqL is using.

0
 
SandeepiiiAuthor Commented:
well i don't think cursors are there basically wht i can see is there are stored procedure running
0
 
patriktCommented:
There is lots of comments, I'll try summary:
- Lots of disk activity, long disk queue
- SQL is using all available memory (max 2GB without AWE) which is expected on dedicated server.
- Not a big table. 1061176 rows is not so much. Can be problem only with very long row. What is declared size (sum from definition) of your row?
- No blocking seen. Locks are hold for short period => huge number of lockings?

I'll bet that that update is inside some loop.
0
 
SandeepiiiAuthor Commented:
wdshipto is PK and is clustered
0
 
chapmandewCommented:
Something is funky.  Run this sp and let us know if there is any one spid that is repeated a LOT of times

sp_lock
0
 
patriktCommented:
If the wdshipto is PK and is clustered, we are hunting wrong rabit. Such simple update with pure PK condition could not cause this.

Please run SQL Profiler and check for SP_Completed with high duration. May be there is something other.
0
 
SandeepiiiAuthor Commented:
sorry wdshipto is only primary key with no clustered index
0
 
SandeepiiiAuthor Commented:
nvarchar(20)
0
 
patriktCommented:
Ok, so back to itemmaster_in. What is the clustered index on this table? If it has long clustered key there is chance to cause such problems.

But I still bet on something else is hidden there.
0
 
SandeepiiiAuthor Commented:
spid 55 is repeated
0
 
patriktCommented:
How many locks it has?

Run dbcc inputbuffer(55) to see its last work.
0
 
SandeepiiiAuthor Commented:
17353 rows it is showing type pag
0
 
SandeepiiiAuthor Commented:
command in input buffer (55)


update itemmaster_in set status=1 where status=0
and wdshipto='ch281023'

0
 
patriktCommented:
For what hell it needs 17000 page locks for uprading one row.

Just lets try:
SELECT COUNT(*) FROM itemmaster_in WHERE wdshipto='ch281023'
0
 
SandeepiiiAuthor Commented:
well for itemmaster_in has one pk_itemmaster_in clustered thts all
i can't see any other index
0
 
SandeepiiiAuthor Commented:
well it took 1 minute and returned 0 rows
0
 
chapmandewCommented:
see if this helps:

update itemmaster_in with(rowlock)
set status=1 where status=0
and wdshipto='ch281023'
0
 
SandeepiiiAuthor Commented:
actuall can you tell wht exactly it will help us by running this query
0
 
patriktCommented:
What is pk_itemmaster_in  key column? Post this index structure if you can.

The test query shows that index is not working on wdshipto or is very bad.

We can continue with this:
set SHOWPLAN_XML ON
GO
SELECT COUNT(*) FROM itemmaster_in WHERE wdshipto='ch281023'

please post result.
0
 
chapmandewCommented:
It is a hint to only create the locks at a row level....it may alleviate the other locks that are occurring.
0
 
patriktCommented:
The query is just to force SQL do same lookup as the update does without modifiing data.
As it took 1 minute we see there is problem. It has to lookup 1 row in fraction of second if index is used.
0
 
SandeepiiiAuthor Commented:
please find query plan though it did not return any rows
show-plan.txt
0
 
SandeepiiiAuthor Commented:
well with your query update itemmaster_in with(rowlock)
set status=1 where status=0
and wdshipto='ch281023'

it happened in 12 secds
0
 
SandeepiiiAuthor Commented:
one more query is select max(document no) as document no from document id

is also locking 10000 pag in sp_lock but is executing in 3 secds and plan is showing table scan
0
 
patriktCommented:
Lots of information it the plan.

1) You have PK on nvarchar(4000) column !!!!
2) It is doing Clustered Index Scan. it is why reading all pages  and page locking.

First, change data size of PK as much as you can.
Second INDEX on wdshipto.
0
 
patriktCommented:
Sorry, my error, wrong reading.
ad 1) it is not PK.
0
 
patriktCommented:
For what reason you need this query
select max(document no) as document no from document id
?

It looks like wrong programming construct. Look to code, there should be better way.
0
 
SandeepiiiAuthor Commented:
well this is the query they are running from user end
0
 
patriktCommented:
If you have no impact on application code you should stay on SQL and index on document no DESC.
0
 
SandeepiiiAuthor Commented:
thanks for the inputs and one more can i see in sql  tuning advisor will it help
0
 
SandeepiiiAuthor Commented:
Solution was helpful in reaching a conclusion and drawing some solution.
0
All Courses

From novice to tech pro — start learning today.