Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
barryd00
Starting Member
2 Posts |
 Posted - 2012-08-14 : 12:09:57
|
| I am having a problem with a specific SQL application that we are having some weird problems with. Recently when transactions are made (accounting software) to a Ledger table, the user enters those transactions, then goes to another screen to post those transactions, however the application states that there are not transactions.If the user waits (sometimes an indeterminate amount of time), the transactions show up and they are able to post. What I am wanting to know is there a way (whether it be an ODBC setting or SQL setting) to push those transactions to commit or write to the database quicker. If not or if that would be too much of a resource bottleneck, what ways can I troubleshoot this type of problem?I have googled and ran across checkpoint duration and other things that don't really seem to address my specific issue. I have ran I/Ometers on my SAN to verify that I'm not bottlenecking on IOPs. I am not a SQL guy, but have been tasked with trying to troubleshoot.Any help or direction would be greatly appreciated. |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-08-14 : 12:35:40
|
| They are probably blocking.-Chad |
 |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2012-08-14 : 12:51:02
|
| First find out long running queries by using SQL Server Profiler (an SQL Server own tool), then find out costliest nodes for costliest query, by using Actual Query Execution Plan in SQL Server Management Studio). Through this graphical plan you will have better idea, where to place indexes and which queries need to be rewritten for better performance.To find out exclusive lock, use following simple query.SELECT session_id,host_name,request_mode,last_request_start_time,resource_type, resource_descriptionFROM sys.dm_tran_locks INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id =sys.databases.database_id INNER JOIN sys.dm_exec_sessions ON sys.dm_exec_sessions.session_id=sys.dm_tran_locks.request_session_id WHERE resource_type <> 'DATABASE'AND request_mode LIKE '%X%'AND name ='YourDatabaseName'--------------------------http://connectsql.blogspot.com/ |
|
|
|
barryd00
Starting Member
2 Posts |
Posted - 2012-08-14 : 15:37:36
|
| This is the return on the query, however this is an out of the box (so to speak) software that we can't really tweak the queries or processes that are running.103 JLBRADFORD X 2012-08-14 14:31:57.707 APPLICATION 0:[DOE:1079:38666]:(7a05994e) 84 VPRICE X 2012-08-14 14:34:09.173 APPLICATION 0:[DOE:1006:130690]:(7e0cef9f) 116 CGEADY X 2012-08-14 13:35:07.873 APPLICATION 0:[DOE:1079:47208]:(eb3c1c55) 84 VPRICE X 2012-08-14 14:34:09.173 APPLICATION 0:[DOE:3203:11137]:(46003786) 84 VPRICE X 2012-08-14 14:34:09.173 APPLICATION 0:[DOE:1006:130691]:(3f3df486) 84 VPRICE X 2012-08-14 14:34:09.173 APPLICATION 0:[DOE:3203:9380]:(4c7d5514) 67 CTMETHVIN X 2012-08-14 14:34:20.770 APPLICATION 0:[DOE:2:42]:(75414d70) 84 VPRICE X 2012-08-14 14:34:09.173 APPLICATION 0:[DOE:953:9526]:(6351d33a) 116 CGEADY X 2012-08-14 13:35:07.873 APPLICATION 0:[DOE:1058:9714]:(5731e5e1) 84 VPRICE X 2012-08-14 14:34:09.173 APPLICATION 0:[DOE:1079:46940]:(e9d1cab4) 84 VPRICE X 2012-08-14 14:34:09.173 APPLICATION 0:[DOE:1006:130689]:(52d091f6) 103 JLBRADFORD X 2012-08-14 14:31:57.707 APPLICATION 0:[DOE:1057:10044]:(05f7d49b) |
|
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2012-08-14 : 15:47:45
|
| Lot of exclusive locks.... you must refactor your queries/store procedures. But first get calprit query from SQL Profiler.--------------------------http://connectsql.blogspot.com/ |
|
|
|
|
|
|
|
|
SQL Server 2008 R2 Data Commit