SQL Server Performance Forum – Threads Archive

ASP Querying linked servers

I have an asp page which queries a stored procedure to return a recordset. The procedure queries a local table and table on a linked server. The linked server is another sql instance on the same machine. When I run the procedure from Query analyser the result is returned in 1 second. When I run from an asp page it returns in around 90 seconds. On my test server (A considerably slower machine) the procedure queries a local table and a linked server. The linked server is the same that is queried on live. This means it is querying an instance on a physically different machine yet the web page returns almost instantly. I would expect the live system to be faster. It has more memory per instance, better processing and does not require any network transfer for the server link. I have checked Task manager while the query runs and both instances are running above 30% cpu during the query. We run an ERP system on this server and it rarely goes above 20% cpu normally. Has anyone else had this issue where 2 instances on the same machine have such a hard time talking to eachother??

---

http://www.sql-server-performance.com/linked_server.asp Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Sorry, I don’t see how this helps.
The performance tips relate to queries on two different servers. As I have stated above, when the query is ran between 2 instances on 2 physically different machines the query runs fine, it is when the query runs on 2 instances on the SAME machine that i have an issue. The server spec is not an issue here as both instances have ample memory and the processing on the server rarely hits over 20%

---

To me it sounds like an ASP issue since you say that when you run it in query analyzer it takes 1 second in the scenario where the instances are on the same machine. So I see no issue here with having the instances on same server. The issue arises when you use ASP, correct? In that case I would look into how the parameters are passed to the stored procedure from ASP. Most likely the issue is a bad query plan (on the live server). Causes could be: 1.
The parameters being different than the last run and the existing plan in cache isn’t good for those parameters. To get around that create the stored procedure with the "WITH RECOMPLIE" option. 2.
Another likely issue is that when passing the parameters from ASP the query optimizer can not read the parameters the same was as when you send them in from query analyzer. An issue with "parameter sniffing":
http://groups-beta.google.com/group….programming/msg/ff9e6e72122e1fb?hl=en&rnum=3 Basically the parameter values will be unknown when coming from an ASP call. To get around this declare your parameters as local variables within the stored procedure and then use those variables in your queries instead. Then when the compile occur the values will be known and a better plan can be generated.

---

I’m not sure if it’s because i’m not explaining this correctly or not. The issue is not the ASP. I run the asp on the testserver which links from the test server "Richmond-Demo" to the live server "RSQL" (Instances on different machines)
This runs fine – about 1 second When I run from the live server "RSQLData" to "RSQL" (Both instances on same machine)
the page returns after 1 minute. The issue is definitely down to the instances being on the same machines. I am wanting to know if anyone else has had the same problem and if service pack 4 has any related fixes.

---

Where is the ASP being executed? To me it sounds like you execute asp on server Richmond-Demo and everything works fine. Then you execute it on RSQL and it and it doesnt work fine. So it could very well be a bad query plan on RSQL. What happens if you execute the ASP from a third server against Richmond-Demo and then RSQL?

---

2 different executions: IIS: Richmond-Demo
SQL Server making Call: Richmond-Demo
Linked Server: RSQL Execution time – 1 second with 15%CPU uage on Richmond-Demo only during execution time IIS: RIIS
SQL Server making Call: RSQLDATA
Linked Server: RSQL Execution time – 60 second + with 50%CPU usage on RSQL for sqlserver.exe (RSQL instance) and 30%CPU usage on RSQL for sqlserver.exe (RSQLDATA instance) – combined 80%CPU usage for over 1 minute! RIIS and RSQL are high performance machines running Windows 2000 advanced server SP4
Richmond-Demo is an XP1600 AMD CPU running Windows 2000 pro with 512mb ram. I am now convinced that the issue is regarding the 2 instances talking to eachother because of the abnormally high processing. I want to try service pack 4 for SQL on these instances but I have had issues installing it elsewhere.

---

Are the linked server setups identical, including the default network libraries used on the machines? Might try named pipes on the one that has two instances. Run Profiler during the run to see whats going on. Maybe som connection parameters are messing things up on the 2-instance server making it sending the data row by row instead of in one large batch.

---

+So+where+can+i+get+the+real+one+from+then%3F%0D%0A
%5Burl%3D%22http%3A%2F%2Fwww.online-casinos-707.net%22%5Dcasino%5B%2Furl%5D+++

---

Moderator please check the user "basin18 " – looks like a bot posting spam …

---

—————————————————————- This is regarding linked srver…I could able to run the query in query analyzer..but i am not able to call Store Procedure from ASP..and it is throwing error like Remote Access Denied…
How to call a SP (linked server query) from ASP.
Are we need to make any registration setting thru ASP for linked servers???
Please help me in this regard..As u have already ran the same thru ASP…
And my mail id is : [email protected] (or) [email protected] a lot in advance…. Thanks
Aravind aravind

---

Aravind, there is no need to post your mail id. Since this is a forum, it is much more likely someone will reply here to this thread, than contacting you directly via mail and answer there. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

---

This is regarding linked srver…I could able to run the query in query analyzer..but i am not able to call Store Procedure from ASP..and it is throwing error like Remote Access Denied…
How to call a SP (linked server query) from ASP.
Are we need to make any registration setting thru ASP for linked servers???
Please help me in this regard..As u have already ran the same thru ASP…
…Thanks a lot in advance….
Anybody please respond immediatly..Its very urgent for me… Thanks
Aravind aravind

---

The user you are connecting as through the asp page has not got permission to the tables you are querying on the remote server. Also check the linked server settings on the server your asp page is making the initial connection to. There are 4 security options in sql 2000 for the linked server: "For a login not defined in the list above, connections will:"
Not be made
Be made without using a security context
Be made using the login’s current security context
Be made using this security context: Make sure you use either of the last 2 options unless you have logins mapped in the server login mappings box above it.

---

Hi rhankin, As this is a ASP page, naturally a web page and anybody can access the this page.So for all the users who are accessing this web page cannot be provided permissions to the tables I am querying on the remote server. As a second option I ave selected "Be made without using a security context". If i select any of last 2 options it is asking for username and password. So i have selected 2nd option of 4. Still it is throwing the same error: Remote Access denied.. Any more options need to changed??? FYI..my IIS resides on one server and database resides on different server… Please respond back.. Thanks a lot for your time.. Regards
aravind

---

The login context is the login which you pass through your sql connection string i.e:
Provider=sqloledb; Data Source=SERVER1; Initial Catalog=master; User Id=BOB; Password=APASSWORD This login "BOB" is connecting to the initial server "SERVER1". Your statement then queries the 2nd/remote server and passes across login credentials. You must select either:
"Be made using the login’s current security context"
"Be made using this security context:" one must be chosen if you haven’t filled in the mappings above it.
if you use "Be made using the login’s current security context", the tables accessed on the 2nd server will do so as "BOB" so this user requires the appropriate permissions on any tables on the 2nd server and of course be set up as a login on that server with the same password. If you choose "Be made using this security context:" then you can treat every connection going across as a single user. Ie. user "BOB" executes a stored procedure on SERVER1 in DB1 which queries a table on SERVER2 in DB2. All security context on SERVER1 is as "BOB" but by using "Be made using this security context:" in the linked server options and specifying user "FRED" password "ANOTHERPASSWORD", all security context on SERVER2 will be treated as "FRED" Does this help?

---

Hi rhankin, Thanks for your reply…. I have tried by selecting both the options ("Be made using the login’s current security context","Be made using this security context") u have mentioned in the above mail….But it is still giving me the same error "Remote Access denied"… When I have selected "Be made using the login’s current security context", option, I have given username and password in the connection string and created the same user for the database access and still giving the error. Do we need to have the access permission for linked server (ADSI) for this user also?? Can you please send me ur sample code with which you could able access stored procedure from ASP page. Or any other settings are needed?? Please help me in this regard.
Thanks much in advance for your time and patience. Regards
aravind

---

If you are linking ADSI as a server this becomes a bit different: run the following script to add your linked server:
EXEC sp_addlinkedserver ‘ADSI’, ‘Active Directory Services 2.5’,
‘ADSDSOObject’, ‘adsdatasource’
then run this query: SELECT
name,userAccountControl,givenName,SN,homeDrive,title,pager,telephoneNumber,
mobile,department,company,userWorkstations,
whenChanged,whenCreated,sAMAccountname
FROM OPENQUERY( ADSI,
‘SELECT
distinguishedName,userAccountControl,SN,
givenName,homeDrive,name,title,pager,telephoneNumber,
mobile,department,company,legacyExchangeDN,userWorkstations,
whenChanged,whenCreated,sAMAccountname
FROM ”LDAP://#1/DC=#2,DC=#3,DC=#4”
WHERE objectCategory = ”Person” AND
objectClass = ”*” and sAMAccountname = ”*”
‘) #1 = your Domain controller
#2 = your domain e.g. ‘yahoo’
#3 = your domain e.g. ‘co’
#4 = your domain e.g. ‘uk’ hopefully that will work

---

Hi rhankin, Thanks for your quick reply!!! I have done the samething (registration of ADSI using "EXEC sp_addlinkedserver…." and i mentioned the same (in the first query posting) that I could able to run the ADSI query from Query Analyzer by selecting the second option "Be made without using a security context
" and i am getting the results…The problem is with calling the same query from ASP page (different IIS server)..So could you please help me in this regard. Thanks in advance Regards
aravind

---

I am not sure about yourt asp security issues – i presume any username specified in the asp connection string would have to be an NT account to be able to access ADSI. I use Active Directory data a lot within my software and web pages. What I do is schedule a stored procedure which uses the select i posted above then save the output to a table. This is then used by all of the applications i develop plus the network manager writes reports off this to monitor his directory users. The query is not a major overhead so you can run it every 5 minutes. I actually output it to a temporary table first then manipulate a lot of the data, truncate the main table then copy all the temporary data into it. This is also much faster for your asp pages

---

]]>