July 12, 2002 at 12:13 pm
SQL 2000
We have a web application that uses SQL Server. We run a query, using Query Analyzer that uses the same SQL server same database. While my web application is waiting to return the response the QA query crawls along. If I terminate my query to the web application my results return right away.
If while my web application is running I run the same query from another desktop it returns instantly. Any ideas?
Bruce Szabo, MCSE+I, MCDBA, MCSD
Bruce Szabo, MCSE+I, MCDBA, MCSD
July 12, 2002 at 5:16 pm
I'm a little confused, but that's nothing unusual after a long work week.
If the web app is already processing the query and you try to kick one off in QA from a particular desktop, the query crawls. If you stop the web apps query, it returns right away?
And in the case of the second desktop, it doesn't matter the web query is running?
Do I have it correct? If so, what network library is the first desktop using? It's a long shot, but that may be the cause.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 12, 2002 at 7:21 pm
You are correct in your post!
I believe it is using TCP/IP. We try to use it everywhere. It is TCP/IP and then named pipes. The wierd thing is from what I can imagine is happening is that the web server is sending data and the SQL server at the same time. I can't figure out why that would cause this problem. It is about 420K worth of data but still not too much.
I hope you are not too confused in your book. A friend just purchased your it or it is a book by another B. Kelly. 🙂
Bruce Szabo, MCSE+I, MCDBA, MCSD
Bruce Szabo, MCSE+I, MCDBA, MCSD
July 12, 2002 at 7:55 pm
I have not seen this problem myself but here are a few question that may help.
1) How many network adapters are in the server?
2) How do your segments relate, (Is the SQL server on it's own segment or a different network segment as you and the others, and could perchance you be on the same segment as the web server and the other machine is not, if so could be the ability of the network to duplex or is not switched and you are getting a high number of collisions)?
Now I suggest looking with profiler on the server to see the connections comming in and if the query is completed in a speicifc order.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 12, 2002 at 9:00 pm
There is only one network adapter. It is really strange because I can imagine the Web Server getting the data back and then sending it to the client. Profiler shows us that SQL has finished providing the data to the Web Server. Now it just a matter of getting it to the client. Obviously this may take some time.
While this process is occuring I try and do a Query to the server and it takes a long time. Currently, I have only tried my queries on the same DB the web server is accessing. I will try using a different database maybe that will provide some clues.
Bruce Szabo, MCSE+I, MCDBA, MCSD
Bruce Szabo, MCSE+I, MCDBA, MCSD
July 12, 2002 at 9:00 pm
There is only one network adapter. It is really strange because I can imagine the Web Server getting the data back and then sending it to the client. Profiler shows us that SQL has finished providing the data to the Web Server. Now it just a matter of getting it to the client. Obviously this may take some time.
While this process is occuring I try and do a Query to the server and it takes a long time. Currently, I have only tried my queries on the same DB the web server is accessing. I will try using a different server.
Bruce Szabo, MCSE+I, MCDBA, MCSD
Bruce Szabo, MCSE+I, MCDBA, MCSD
July 12, 2002 at 10:51 pm
Sounds good. Thinking maybe there is a configuration issue on you machine but it would seem more like it is the SQL server but as you said runnign from another QA is fine, so that takes me back to you. Let us know if you try the other machine and if the problem can be reproduced there.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
July 13, 2002 at 7:44 am
You might profile the server to watch the connections being made. Verify 100% it is TCP/IP and the only reason I say check is because we had a case where we thought a client system (on a DBA's box no less) was talking via TCP/IP, but it was only talking Named Pipes. We found out when for whatever reason the SQL Server did not bind on the TCP port when it was restarted and he was the only one talking!
The other thing you might do is use the sp_who2 system stored procedure because sp_who2 shows CPUTime and DiskIO. I watch it when I have a large batch processing to ensure it didn't just get hung.
Failing that, there's always the possibility of a network sniffer to see if something is going on at the network level. ONe time we were getting hammered on our database queries and didn't have a clue why. Standing up a sniffer we saw someone flooding the network with multicasts. It was an authorized program, but the person was using some of the more intensive features. Had we not stood up the sniffer, we'd still be shaking our heads.
As for the book, Start to Finish Guide to SQL Server Performance Monitoring, http://www.netimpress.com/shop/product.asp?ProductID=1 (shameless plug), is a new eBook I have written (my first, and hopefully not last). I had a lot of help from the guys here at SQL Server Central, I certainly couldn't have got it done on my own and it be readable.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply