Solved

Trace analysis sql server 2005

Posted on 2008-06-25
52
753 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

22 Experts available now in Live!

Get 1:1 Help Now