April 1, 2009 at 8:00 am
Hi SQLGURUs,
I have 1 TB database which is heavily used for SAP , I have 2 application which Uses only 5 tables in that DB
For Example : Application 1 SAP , Application 2 XYZ
A table
B table
C table
these tables are used to create Delivery document by SAP application , once SAP completes his task Application XYZ will take the record and process some more information . just its take the records based condition and it will select and updates the field.
Now I have problem with XYZ application its giving me performance problem users are reporting its taking more then 30 to 60 seconds read the record.
I am assuming when XYZ is trying to read/update the records its waiting for SAP application to complete its task I think default SAP application locks the table for new delivery record
just I am wondering if I can set up locks on records level for XYZ application .so that I can avoid waiting time for SAP application
I really appreciate your suggestions
Thanks
Ichbin
April 1, 2009 at 8:20 am
Is this a newly acquired behavior or application has always behaved this way?
In principle, adding locking is just going to add issues.
Why don't you trace the offending query and check wait events to find out exactly what is happening?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 1, 2009 at 8:35 am
hi you can check execution plan if your querry make table scan or index scan if is your case need tune index if your querry update only one record you can put rowlock hint into your querry to avoid lock page or table or into the same transaction prepare your update whith select statement and use holdlock. somtime if you have varchar field and use ansi convertion whith where field = N'search' and this case is execution plan make index scan because need convertion to run querry.
you can use SP_lock to view lock type page tab row ECT..
April 1, 2009 at 8:44 am
Yes, try to trace the queries that are running against the table. Identify them and troubleshoot them.
Check to see TOP 10 waits
SELECT TOP 10
wait_type,
waiting_tasks_count AS tasks,
wait_time_ms,
max_wait_time_ms AS max_wait,
signal_wait_time_ms AS signal
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
Check to see which are holding locks and which are blocked:
SELECT
t1.resource_type,
'database' = DB_NAME(resource_database_id),
'blk object' = t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id,
t2.wait_duration_ms,
(SELECT SUBSTRING(text, t3.statement_start_offset/2 + 1,
(CASE WHEN t3.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max),text)) * 2
ELSE t3.statement_end_offset
END - t3.statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
t2.resource_description
FROM
sys.dm_tran_locks AS t1,
sys.dm_os_waiting_tasks AS t2,
sys.dm_exec_requests AS t3
WHERE
t1.lock_owner_address = t2.resource_address AND
t1.request_request_id = t3.request_id AND
t2.session_id = t3.session_id
April 1, 2009 at 8:57 am
Hi Thanks for replies.
Performance problem we are having 2 days in week sometimes its not
I have Reindex the tables recently but still no performance improvements.
These is heavily used production system and DB has nearly 70,000 tables.
So you mean I have to trace only 3 tables using filters in profiler ?
can I set up one trace with 3 explicit objects in filter condition?
Please let me know your information
Thanks
Ichbin
April 1, 2009 at 9:06 am
Yes, you should monitor the server when it is busy and identify the queries that are causing performance problems at least couple of them and try to tune it and monitor it again to check whether the performance has improved or not.
Check out Gails article:
April 1, 2009 at 9:08 am
objet filter not work whith completbatch or sp complet you need filter whith texdata and like your table name but did you know querry run into this table and did you review execution plan and you can run engine tuning advisor to review if have good index into table somtime cluster index into pk is not a good choice and maybe need create some statistic to help sqL to select a good index
April 1, 2009 at 9:09 am
ichbinraj (4/1/2009)
I have Reindex the tables recently but still no performance improvements.
Only Re indexing does not help. What are your table schemas and what are your frequently running queries?Would you be able to post them here?
April 1, 2009 at 9:18 am
Hi ,
I am not to sure weather I should run Profiler against my Production during the day .. definitely it will hit my performance
but I have problem only during the day ...
As per I know the XYZ application using simple select statement
Example:
1) SELECT * FROM WHERE ID = '80137809' and CUSTOM1 in (1000151942);
2) SELECT * FROM WHERE SID = '80137809' AND clm2= '300'
when these statement is being useded its taking long time
more ever this application is connecting using ODBC
Thanks for your suggestions
-Ichbin
April 1, 2009 at 9:26 am
select * not offert good performance into production enviroenment
maybe you need none cluster index on id and custom1 for the firrt querry
and
none cluster index on sid and clm2 for the seconde one
and did you look exection plan for this both querry
April 1, 2009 at 9:54 am
Hi ,
I have performance problem not every day some times response is very quick like within n 2 seconds
but some times Particularly 2 days in week response time is around 30 to 45 seconds
so just confused if its query problem then its should happened every time but in my case its different
I have execution plan for 1st query
Result : cluster index Scan
1) Physical Operation : Clustered index seek
2) logical operation : cluster index seek
3) Estimated I/O cost : 4.7920
4) Estimated CPU cost : 0.12
5) Estimated operator cost : 4.918 (100%)
6) Estimated subtree cost :4.918
7) Estimated no of rows : 2
2nd query Execution plan:
Results : cluster index seek
scanning a particular range of rows from a cluster index
1) Physical Operation : Clusterd index seek
2) logical operation : cluster index seek
3) Estimated I/O cost : 0.003125
4) Estimated CPU cost : 0.0001581
5) Estimated operator cost : 0.0032831 (100%)
6) Estimated subtree cost :0.0032831
7) Estimated no of rows : 1
Thanks
Ichbin
April 1, 2009 at 11:00 am
maybe check system performance disk queud lenght proc and memmory maybe considere monitor lock with third party software like
http://www.sqlsolutions.com/products/sql-deadlock-detector/index.html
cosidere review application to be sure lock is relase as soon as possible using begin transaction and commit
April 1, 2009 at 11:06 am
Can you please explain me more ..
Can it be possible to lock the table in rows wise if so how can ?
Thanks
ichbin
April 1, 2009 at 11:40 am
into your select statement you can put
select .... from table with (nolock) where
and into you update statement if you update only few row
update
set
from table with (rowlock)
where
or
prepapre your update with select like this
begin transaction
select .... from table with (hollock) where
update
set
from table
where
commit
beware whith nolock because you can able read change row.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply