| Author |
Topic |
|
MuadDBA
628 Posts |
 Posted - 2004-01-23 : 15:36:16
|
| My company is using eiStream's workflow for windows version 3.2.1. Because of the way it uses resources, all of the connections are fed through one server, which means profiler can't get the id of the computer submitting the query, and all queries come through witht he same user name.Someone, out of my 3,000 users, is executing a SELECT query against my 65 million row table with a wildcard as the first character. Obviously this is bad, but since I can't track him down, and we won't be upgrading to a better version of the client tools (that will let me prevent this easily) for a couple more weeks, I need another way to prevent this query from exectuing, since when one person does this it affects the other 2,999 users.Is there a way? I can see the bad query in profiler, but it's hidden amongst the 1000 other queries hitting that system every minute, and the query is so long thata I can't filter on it. Help me if you can.And yeah, I know the application sucks. But I gotta work with it because it's what we have. |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2004-01-23 : 16:10:07
|
| What drawbacks does the govenor have? I've never used it.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2004-01-23 : 16:15:21
|
| Look at the "PID". The PID is the host process id and you should be able to trace that from the server. Once you see the PID on the server it will tell you what machine has the connection open.DanielSQL Server DBA |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2004-01-23 : 16:19:32
|
quote:
Originally posted by SQLServerDBA_Dan
Look at the "PID". The PID is the host process id and you should be able to trace that from the server. Once you see the PID on the server it will tell you what machine has the connection open.DanielSQL Server DBA
When I say server I mean that the PID will be on the server that has made the connection to the SQL Server. The process id on that server will show who has connected to it and then you can go to the user.I had this same issue with Citrix and Terminal server in a big company I worked for. All the connections on SQL Server came from one of 2 terminal servers!  Well, I would give the PID to the netadmin and he would then let me know what machine the PID was associated with.  DanielSQL Server DBA |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-01-23 : 16:22:24
|
| Do you mean the SPID? If not, can you point me in the right direction for finding it and tracing it back to the machine executing the query? Keep in mind that the way this application works is all of the functions are passed through one main server which uses ODBC to execute the queries agains the databases, so I don't know if it's possible to trace it. I'd appreciate any more help you could give.I am considering the query governor. The problem is we have a lot of reporting queries which run against the database at night, so either they would all have to be rewritten to set the governor limits for their specific connections, or I would have to reset the query governor at night. It is a possibility, though. Thanks for the suggestions. |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-01-23 : 16:27:55
|
| Ok, client process ID, I see what you mean. However, all of the client process IDs are the same....for this application, they all return 33622, no matter who is using the application. |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2004-01-23 : 16:32:44
|
quote:
Originally posted by crazyjoe
Do you mean the SPID? If not, can you point me in the right direction for finding it and tracing it back to the machine executing the query? Keep in mind that the way this application works is all of the functions are passed through one main server which uses ODBC to execute the queries agains the databases, so I don't know if it's possible to trace it. I'd appreciate any more help you could give.I am considering the query governor. The problem is we have a lot of reporting queries which run against the database at night, so either they would all have to be rewritten to set the governor limits for their specific connections, or I would have to reset the query governor at night. It is a possibility, though. Thanks for the suggestions.
Sorry about that. The PID is named "Hostprocess" in the master..sysprocesses table and perhaps is named clientprocessid in profiler. To verify that the clientprocessid is the same as the hostprocess field just compare the SPID in the profiler with the SPID in the sysprocesses table.You will need to look at the host process on the server that is funneling all the data to find the workstation that is actually doing the query.The netadmin that I worked with was using some sort management software to find the pid stuff but I think it was a default tool that comes with NT.DanielSQL Server DBA |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2004-01-23 : 16:34:07
|
quote:
Originally posted by crazyjoe
Ok, client process ID, I see what you mean. However, all of the client process IDs are the same....for this application, they all return 33622, no matter who is using the application.
Well thats no good then. Sound like that software is a little different that my expirences with terminal server. Good luck.DanielSQL Server DBA |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
|
|
MuadDBA
628 Posts |
Posted - 2004-01-23 : 17:09:19
|
| It's a workflow management software we use for our imaging system here at the bank. eiStream is the new name for this division of Eastman Kodak. We image all of our loan documents and then route them via this workflow management system based on different attributes of different documents so that they get all the documents reveiwed in a tiemly fashion. It's hugely complex and every much a pain in the butt....but to duuplicate its functionality would cost more in time and money than the bank really wants to spend (and frankly I wouldn't want to be involved in that project :) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-01-23 : 20:06:49
|
| This is a completely wild idea and probably not feasible, but if you have a packet sniffer/filter you might be able to intercept the query (either at the app server or database server) and filter it out. I don't know enough about these things to tell you more about it, but if you have a networking guru in your office you might want to bounce the idea off of him/her. Couldn't hurt. |
|
|
|
|
Bad user Executing query.....
.Jonathan