SQL Server Performance

SQL 2008 slow queries

Discussion in 'ALL SQL SERVER QUESTIONS' started by cnikirk, Jan 29, 2013.

  1. cnikirk Member

    I have noticed a strange problem with SQL 2008 R2 that I can't account for. When I run a specific query on the physical sql server in question it takes about 60 seconds to run. It also takes around 60 seconds on remote windows 2008 r2 servers that have SSMS, but when I run this query from a windows 2003 server with SSMS, the query is about 20 seconds faster to return.

    I can't think of any reason to explain this behavior but I can replicate it over and over. Any 2003 server returns the query faster than any 2008 r2 server or even the actual sql server itself.

    Any ideas?

  2. Luis Martin Moderator

    Same hardware?
  3. cnikirk Member

    Well the remote machines of course are all different, but all the 2008 remote machines are slow, even the physical machine itself. The 2003 machine is a lowly VM with 4 gigs of ram but yet the query results return faster. Very odd.
  4. Luis Martin Moderator

    Could you show cpu's, memory, etc.?. Also if all are sql dedicated, maintenance jobs, etc.?. Really is odd.
  5. cnikirk Member

    I'm not really sure what else to say except this appears to be a problem with Windows 2008 R2. It isn't SQL Server and it doesn't happen with Windows 2003 no matter what hardware it runs on. I would love to see someone take an intensive query or store procedure and compare the times on average when running on Windows 2008 and 2003.

  6. davidfarr Member

    I can say from my experience that I have also had a mixed environment of Windows 2003 / Windows 2008R2 servers, having client connections to a common SQL Server 2008R2, and I have not experienced the kind of performance difference that you speak of.
    As you may know; the query will always be executed on the server using the same engine/hardware/config each time, regardless of which remote client that you use. Subsequent identical queries should actually be faster from any remote client due to that SQL Server will typically cache the query result where possible.
    Client-specific issues that can account for a time difference when returning a large data query result back to a remote client are network latency issues and memory limitations (local client paging) to store the returned query.
    If neither of these are a factor in your scenario, then your dilemma remains a mystery and is the first such problem that I've heard of before.
  7. Shehap MVP, MCTS, MCITP SQL Server

    It might help you much better if you use the option “client statistics” while executing your query (From right click option) to show out the delays at server side and client side to compromise Cleary which the layer responsible about such delay if it is DB server or your workstations ..

    So please do it from both machines of 2003 and 2008 and feedback the results of both “client processing time” and “wait on server reply “ to be able to define a concrete list of assumptions to troubleshoot fast this case
  8. satya Moderator

    ...just adding to what said above: did you get a chance to cross verify the execution plan between these 2 machines. See what Shehap mentioned to pinpoint where it is taking longer time to execute.
  9. cnikirk Member

    Okay I did this test repeatedly again. I have attached two images. One is from a 2003 server and notice the total query time was 22 seconds. The client stats are showed as well. The second image is the same query ran from the physical sql server itself. It took 54 seconds. Client stats are there as well. What is interesting is the wait time on server replies is faster on the actual server, but the time it took to return the results was slower. I'm lost. Perhaps there is a stat here that would shed some light on this? I have ruled out any type of network latency as well. Thanks.

    Attached Files:

  10. Shehap MVP, MCTS, MCITP SQL Server

    Sorry for reply delay ..Actually what I can see from your attached client statistics results,there is a delay of about 10 sec lost at the client side if executed from win 2008 Server whereas a non-appreciable delay of about 0.6 sec only if executed from wind 2003 server

    I am not suspecting the windows is the main issue beyond as I did never experience it before but I can suspect other assumptions like :

    ·NIC speed if it is Ethernet or Giga Ethernet
    ·NIC driver if updated or not...?
    ·NIC Cable
    ·Some Antivirus configuration terms to secure connections not files might impact on
    ·Some network hubs (switches /routers/ Firewall ports devices ) are stumbling connections to DB server
    ·Bad healthy status of the 2008 client like high CPU consumption that can impact adversely on executing any simple query

    Please check these assumptions and let me know your feedback please
  11. cnikirk Member

    I would tend to agree with you, but I have tested from several 2003 and 2008 servers, and I think it is safe to rule out most of these items, because running the query from the actual server itself is just as slow. All connectivity here goes through the same segments and switching equipment regardless. This is why I'm so confused. Thanks for your reply.
  12. Charandeep Nayyar New Member

    1 Use indexes
    2 Dont use * to retreive the records
    3 Update statistics on
  13. thethoughtpolice New Member

    Hi - same issue as you've explained in our environment. Did you get to the bottom of this? Thanks

Share This Page