Trace analysis sql server 2005

You probably have too many events select or have a high volume server.  What are you trying to find?

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

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?

 

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

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

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?

well the user says it is taking lot of time and is slow .

run this to see what statement that spid is running:

dbcc inputbuffer(57)

maybe it is a long running query or something.

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.

its update command update itemmaster_in set status=1 where status=0
and wdshipto='jp2330'

OK...does your wdshipto field have an index on it?  

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.

wdshipto is a primary key

anything returned from this?

select * from sysprocesses
where blocked > 0

You write that wdshipto is PK, is it clustered or you have other clustered index?

no it is not returning any thing

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).

Are you using Cursors? This might be problem if that simple update is comming from inside of cursor loop.

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

That isn't too out of the ordinary in terms of the memory SqL is using.

well i don't think cursors are there basically wht i can see is there are stored procedure running

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.

wdshipto is PK and is clustered

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

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.

sorry wdshipto is only primary key with no clustered index

nvarchar(20)

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.

spid 55 is repeated

How many locks it has?

Run dbcc inputbuffer(55) to see its last work.

17353 rows it is showing type pag

command in input buffer (55)


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

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

Just lets try:
SELECT COUNT(*) FROM itemmaster_in WHERE wdshipto='ch281023'

well for itemmaster_in has one pk_itemmaster_in clustered thts all
i can't see any other index

well it took 1 minute and returned 0 rows

see if this helps:

update itemmaster_in with(rowlock)
set status=1 where status=0
and wdshipto='ch281023'

actuall can you tell wht exactly it will help us by running this query

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.

It is a hint to only create the locks at a row level....it may alleviate the other locks that are occurring.

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.

please find query plan though it did not return any rows
show-plan.txt

well with your query update itemmaster_in with(rowlock)
set status=1 where status=0
and wdshipto='ch281023'

it happened in 12 secds

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

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.

Sorry, my error, wrong reading.
ad 1) it is not PK.

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.

well this is the query they are running from user end

If you have no impact on application code you should stay on SQL and index on document no DESC.

thanks for the inputs and one more can i see in sql  tuning advisor will it help

Solution was helpful in reaching a conclusion and drawing some solution.