Solved

Trace analysis sql server 2005

Posted on 2008-06-25
52
745 Views
Last Modified: 2011-10-19
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
0
Comment
Question by:Sandeepiii
  • 24
  • 19
  • 8
52 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21864553
You probably have too many events select or have a high volume server.  What are you trying to find?
0
 
LVL 12

Expert Comment

by:patrikt
ID: 21864556
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
 

Author Comment

by:Sandeepiii
ID: 21864963
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
 

Author Comment

by:Sandeepiii
ID: 21865044
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
 
LVL 12

Expert Comment

by:patrikt
ID: 21865046
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
 
LVL 12

Expert Comment

by:patrikt
ID: 21865064
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
 

Author Comment

by:Sandeepiii
ID: 21865155
well the user says it is taking lot of time and is slow .
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21865171
run this to see what statement that spid is running:

dbcc inputbuffer(57)

maybe it is a long running query or something.
0
 

Author Comment

by:Sandeepiii
ID: 21865192
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
 

Author Comment

by:Sandeepiii
ID: 21865266
its update command update itemmaster_in set status=1 where status=0
and wdshipto='jp2330'
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21865287
OK...does your wdshipto field have an index on it?  
0
 
LVL 12

Expert Comment

by:patrikt
ID: 21865302
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
 

Author Comment

by:Sandeepiii
ID: 21865347
wdshipto is a primary key
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21865383
anything returned from this?

select * from sysprocesses
where blocked > 0
0
 
LVL 12

Expert Comment

by:patrikt
ID: 21865409
You write that wdshipto is PK, is it clustered or you have other clustered index?
0
 

Author Comment

by:Sandeepiii
ID: 21865424
no it is not returning any thing
0
 
LVL 12

Expert Comment

by:patrikt
ID: 21865443
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
 
LVL 12

Expert Comment

by:patrikt
ID: 21865505
Are you using Cursors? This might be problem if that simple update is comming from inside of cursor loop.
0
 

Author Comment

by:Sandeepiii
ID: 21865509
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 21865542
That isn't too out of the ordinary in terms of the memory SqL is using.

0
 

Author Comment

by:Sandeepiii
ID: 21865592
well i don't think cursors are there basically wht i can see is there are stored procedure running
0
 
LVL 12

Expert Comment

by:patrikt
ID: 21865611
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
 

Author Comment

by:Sandeepiii
ID: 21865622
wdshipto is PK and is clustered
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21865636
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
 
LVL 12

Expert Comment

by:patrikt
ID: 21865645
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Sandeepiii
ID: 21865852
sorry wdshipto is only primary key with no clustered index
0
 

Author Comment

by:Sandeepiii
ID: 21865861
nvarchar(20)
0
 
LVL 12

Expert Comment

by:patrikt
ID: 21865900
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
 

Author Comment

by:Sandeepiii
ID: 21865902
spid 55 is repeated
0
 
LVL 12

Expert Comment

by:patrikt
ID: 21865941
How many locks it has?

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

Author Comment

by:Sandeepiii
ID: 21866004
17353 rows it is showing type pag
0
 

Author Comment

by:Sandeepiii
ID: 21866022
command in input buffer (55)


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

0
 
LVL 12

Expert Comment

by:patrikt
ID: 21866143
For what hell it needs 17000 page locks for uprading one row.

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

Author Comment

by:Sandeepiii
ID: 21866206
well for itemmaster_in has one pk_itemmaster_in clustered thts all
i can't see any other index
0
 

Author Comment

by:Sandeepiii
ID: 21866303
well it took 1 minute and returned 0 rows
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21866386
see if this helps:

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

Author Comment

by:Sandeepiii
ID: 21866406
actuall can you tell wht exactly it will help us by running this query
0
 
LVL 12

Expert Comment

by:patrikt
ID: 21866412
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
 
LVL 60

Expert Comment

by:chapmandew
ID: 21866432
It is a hint to only create the locks at a row level....it may alleviate the other locks that are occurring.
0
 
LVL 12

Expert Comment

by:patrikt
ID: 21866444
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
 

Author Comment

by:Sandeepiii
ID: 21866551
please find query plan though it did not return any rows
show-plan.txt
0
 

Author Comment

by:Sandeepiii
ID: 21866613
well with your query update itemmaster_in with(rowlock)
set status=1 where status=0
and wdshipto='ch281023'

it happened in 12 secds
0
 

Author Comment

by:Sandeepiii
ID: 21866667
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
 
LVL 12

Expert Comment

by:patrikt
ID: 21866763
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
 
LVL 12

Expert Comment

by:patrikt
ID: 21866781
Sorry, my error, wrong reading.
ad 1) it is not PK.
0
 
LVL 12

Expert Comment

by:patrikt
ID: 21866799
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
 

Author Comment

by:Sandeepiii
ID: 21866828
well this is the query they are running from user end
0
 
LVL 12

Expert Comment

by:patrikt
ID: 21866880
If you have no impact on application code you should stay on SQL and index on document no DESC.
0
 
LVL 12

Accepted Solution

by:
patrikt earned 250 total points
ID: 21866951
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
 

Author Comment

by:Sandeepiii
ID: 21867340
thanks for the inputs and one more can i see in sql  tuning advisor will it help
0
 

Author Closing Comment

by:Sandeepiii
ID: 31470505
Solution was helpful in reaching a conclusion and drawing some solution.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now