| Author |
Topic |
|
digitalriver
Starting Member
14 Posts |
 Posted - 2006-07-13 : 20:00:17
|
| Greetings and thanks for Reading my post.Our company recently rolled out an application that is mission critical. It was supposedly best product in our industry but have had nothing but problems which I have mostly solved. Here is the background - due to security flaws in the application (users must be dbo) we put the application on a Citrix farm which uses a DSN with a common SQL login to access the SQL Cluster (SQL Version 2000 Enter on Windows Advanced 2003). That solves our security gaps. But, the application, written in Clarion, is issuing all calls through ODBC and is using server side cursors. So all I see in the input buffer and sp_blocker_pss80 is sp_cursorexecute and sp_cursorfetch. So when I am trying to analye the head of the blocking chain I can not tell who the end user is (all the same user across the citrix farm) to call them tosee what they are doing, or what host it is coming from (all the citrix farm hosts) or even what they are executing (sp_cursor). I tried going through sysprocesses to get the sql handle to use that to fn_get_sql, but all the handles from this user are the same (0x000000000000....). We can't replace the software, they spent a few million converting their legacy systems (I just came on board with this company).What am I missing that haven't tried?I am afraid my only hope is to go to NT auth and completely open up the security to the database to 300 users and 20 developers, then I could identify the users but the databases would get hosed.I am stuck my fellow DBAs. The system blocks for about an hour a week and we've alreay lost about 150k because of this.I need your help. Thanks in advance!!!! |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2006-07-13 : 20:49:15
|
| I may be stating the obvious but have you run any traces (SQL Profiler)? I thought it would have been mandatory to run traces for any new software, especially for MC stuff..DavidMIntelligent Design is NOT science.A front-end is something that tries to violate a back-end. |
 |
|
|
digitalriver
Starting Member
14 Posts |
Posted - 2006-07-13 : 20:57:21
|
| Yes, I have run traces through profiler, it is hard to correlate to the block time and the spid because of the amount of activity and the scenario I described. I feel I have done this by the book. Yet, it is just hard to tie out, and I would have to constantly run profiler and then capture the blocking and correlate the time. Atleast then in theory I could get the RPC event which launches the cursors, but I have tried to no avail.Thanks for replying. Any suggestions? |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2006-07-13 : 21:26:55
|
| Pouring over trace logs is not the nicest task a DBA has to do, but by the sounds of this situation, that is what you may have to do.Just make sure the alcohol cabinet is full...Good luck!DavidMIntelligent Design is NOT science.A front-end is something that tries to violate a back-end. |
|
|
|
digitalriver
Starting Member
14 Posts |
Posted - 2006-07-13 : 21:51:08
|
| Thanks.So you are saying I need to log all blocker_pss80 to table and capture all profiler activity (its RPC with cursors, a few million a minute) and then correlate them? Won't I be weeks out by the incident date with no identification on the user? |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-07-13 : 22:00:45
|
So, you are getting a lot of blocking when users run queries right? You see this using sp_who2 I assume?What you may want to try is making all of the SELECT queries use a WITH(NOLOCK) hint and see if that helps out the situation.Something like this:SELECT * FROM MyTable WITH(NOLOCK)WHERE myField = 'variable' I'm guessing that the Clarion app is using "dynamic sql" to get its data right? It's not calling stored procs?Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2006-07-13 : 22:09:13
|
| >> few million a minuteNasty!!!!Going back a minute...What type of "blocking" is occuring? Deadlock, indefinite or long lasting lock waits?If it is not deadlocking, check the value of your LOCK TIMEOUT. By default it is never..If you change this value, and the DB exceeds it, then it will throw an error to the caller...This may help you in tracking down the issue and keep the blocking time down.. Not the most gracious way forward and definately not recommended for a MC production system...Just throwing some ideas at you...DavidMIntelligent Design is NOT science.A front-end is something that tries to violate a back-end. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-07-13 : 22:10:53
|
| i'm not a guru nor familiar with clarion but based on your info, it'll be fruitless to try to capture information for connection that uses the same login and assuming it's using a webserver so you'll get ip address from one machine (or if you can capture from the apps server, those who are connecting to it and the time of connection, then you can correlate the time on the sql server?--wild idea I know)why do you say the database will be hosed if you use windows authentication? I assume your mission is to identify the source of the block and so you only need to do this for maybe 2-3 hours until the scenario comes up again?or alternatively, you can isolate this apps by creating another dsn exclusively for its use, if possiblehope this helps...--------------------keeping it simple... |
|
|
|
digitalriver
Starting Member
14 Posts |
Posted - 2006-07-13 : 22:17:40
|
| Thanks. Let me make sure you understand all users are going through a citrix farm and using a dsn so they all connect as the same user.I can't use a nolock, the sql is embedded in their code. I don't know about the lock timeout, I will look into that on BOL, I thought one shouldn't go there.NT permissions is just so once I captured the spid I could call the user up and ask what are you running in this app?You guys rock. |
|
|
|
digitalriver
Starting Member
14 Posts |
Posted - 2006-07-13 : 22:19:25
|
| Oh yeah, database will be hosed because app requires user to be dbo so anyone can connect and modify and drop anything. With it sitting behind a citrix farm we use a dsn to get around that (workaround #231) |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-07-13 : 22:24:02
|
| Ok, try to describe your problem again.You are just getting lots and lots of blocking? Does it happen with two users or only when you get to 300 users?Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
digitalriver
Starting Member
14 Posts |
Posted - 2006-07-14 : 00:02:00
|
| All users are going through a citrix farm using a dsn sql connection so they are all the same users from my side. The application is going through ODBC so DBCC inputbuffer can't catch their SQL statements. Since it's a Citrix farm, all the user host addresses are xxx1, xxx2 - xxx15. So when I find a head of the blocking chain I don't know who they are or what they are executing. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2006-07-14 : 00:58:59
|
| Does the application keeps a log/trace of some kind?Maybe parse that into the dbms and analyse it against the sql trace..As you can see I am still suggesting the trace solution.[TongueInCheek]It's already cost you $150K and will only get worse.For $20K, I'll jump on a plane, train or automobile and I'll scroll through a few millions rows of trace for you...[/TongueInCheek]DavidMIntelligent Design is NOT science.A front-end is something that tries to violate a back-end. |
|
|
|
digitalriver
Starting Member
14 Posts |
Posted - 2006-07-14 : 01:09:40
|
| Thanks, if i could hand this off I would, scrolling through a trace wouldn't do any good. It isn't really a single problem I'm tryin to deal with :) |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-07-14 : 02:36:15
|
| "So when I find a head of the blocking chain I don't know who they are or what they are executing."That doesn't really describe a problem per-se. I understand that you've got tons of things executing at once, and no way to track down who is doing it. Having lots of things going on isn't a problem, but I can understand where blocking would be an issue. Does it really matter WHO is doing the blocking? I suspect the issue is WHY it's blocking, not WHO, right? So, is this a blocking issue? A scalability issue? Am I just lost here?I think what you'll need to do is profile the heck out of this thing, log it to a table, and start pouring through it. I think that what you will also need to do is as that logging is happening, run an sp_who2 and capture the time that you ran that and log the return to a table (so you'll have it). Then, you should be able to get an idea of who was blocking who (based on sp_who2) and then corelate that to your profiler logs to see what those SPID's were executing to cause the blocking. You may also need the output of sp_lock to a table as well to get an idea of what locks and what type of locks are being held.That being said, I think you've got a square peg round hole situation. You are trying to use a programming tool (Clarion) that is not optimzed for working with SQL Server. Clarion is a great tool, coupled with a TopSpeed database it's a pretty good performer. In this case though, we are trying to make SQL server work in a way that it's not designed to work (iterative vs set based). As far as ways to access SQL Server ODBC using cursors is about the slowest way I can think of. In order of slowest to fastest here's my short list (3rd party ODBC driver, MS ODBC, OLE-DB, TDS). That being said, I suspect that any gains you may be able to achive will probably not be enough to overcome the issues you are facing here. I think you've joined a team that has rather expensily painted themselves into a corner.I wish you luck with this, and I really hope someone here can help you find a way to solve your problem.Michael |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-07-14 : 02:43:00
|
Actually, I just noticed this from you:quote:
NT permissions is just so once I captured the spid I could call the user up and ask what are you running in this app?
Doesn't profiler tell you that? Why do you need to ask the users?? So what if profiler gives you a lot of data. You have a nice RDBMS that you are dumping it to so you can quickly query and find what you are looking for.If there's something in the app that users can run that blocks everyone else for an hour, then there are two solutions:1. Change the app so that users can't do that anymore2. Change the database so that it's not affected by this queryFrom what you are saying above I'm not sure that either are a good solution since most likly the application will have to be touched to some degree.Finding the human that is doing this and telling them "don't do that" will not solve the problem. Humans love to press the big red button labeled "do not press!" Find the query or queries in question, not the humans. Even if you find the human, they will probably say "I don't know."Good luck with this! I know this is going to be a tough one.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-07-14 : 16:34:58
|
How about enabling traceflags 3604 + 1205 to capture deadlock information int the sql server log.dbcc traceon (1204, 3605, -1)It might help you identify the objects that get deadlocked.If you identify some tables, check the indexes on them.To import and view deadlock info from the sql server log I use this:-- Import deadlocks from errorlogcreate table #errorlog(rownr int identity,eventinfo nvarchar(256), continuation_row smallint)insert #errorlog(eventinfo,continuation_row)exec master..xp_readerrorlogselect eventinfo as deadlocks from #errorlogwhere substring(eventinfo,24,5) = (select 'spid' + ltrim(spid) from master..sysprocesses where cmd = 'LOCK MONITOR')order by rownr desc Here are some references:http://support.microsoft.com/?kbid=832524http://www.eps-publications.com/articleprint.aspx?quickid=0309101&printmode=truerockmoose |
|
|
|
digitalriver
Starting Member
14 Posts |
Posted - 2006-07-17 : 16:39:35
|
| The problem isn't really dealing with deadlocks, it is simply a blocking problem from badly written code. I am still working on the solution. I have luckily been able to identify some reports that were causing some of the issues. I will post my solution when it is finished. Until then, please keep the suggestions coming.Thanks. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-07-17 : 16:51:47
|
| "it is simply a blocking problem from badly written code"Bummer man. Square peg + Round Hole == Get a bigger hammer (faster SQL server).Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
digitalriver
Starting Member
14 Posts |
Posted - 2006-07-18 : 09:15:45
|
| It's a 4 processor cluster with 10 gig or Ram on each cluster, single instance only serving a 30 gig db. Isn't that hammer big enough. Guess not, when software developers don't know what they are doing, life really sucks. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2006-07-18 : 11:44:40
|
| River, tell me about yoru disk config. Generally with SQL server you need disks, ram, cpu in that order. In your case, 10GB's of ram for a 30GB database should be plenty though, so I'm thinking your software must be killing your performance.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights. |
|
|
|
Next Page
|
HELP guru advice on blocking