Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

RDP into server - queries run slow Expand / Collapse
Author
Message
Posted Monday, July 7, 2014 8:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 12:53 PM
Points: 1,271, Visits: 1,066
Hello,

Environment:

I have a remote server with SQL server 2014 instance on it. There is nothing else running on the SQL Server box(dedicated SQL box). There is only one instance of SQL 2014 on the server. No other versions of SQL server are on the server.

Issue:

1. When I execute a query connecting to the SQL server instance through my local SSMS, the query executes in 30 secs.
2. When i connect to remote server through windows RDP session and execute the same query in the SSMS(on server), then query executes in 1 minute.

Could someone shed more light on this? What am i missing here?

Thanks,
Vasu
Post #1589925
Posted Monday, July 7, 2014 9:36 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:00 AM
Points: 1,914, Visits: 5,277
asiaindian (7/7/2014)
Hello,

Environment:

I have a remote server with SQL server 2014 instance on it. There is nothing else running on the SQL Server box(dedicated SQL box). There is only one instance of SQL 2014 on the server. No other versions of SQL server are on the server.

Issue:

1. When I execute a query connecting to the SQL server instance through my local SSMS, the query executes in 30 secs.
2. When i connect to remote server through windows RDP session and execute the same query in the SSMS(on server), then query executes in 1 minute.

Could someone shed more light on this? What am i missing here?

Thanks,
Vasu

Looks to me that the box doesn't have much to spare in terms of resources. The RDP has the overhead of the user's session and applications in addition to the SQL work. This is probably the explanation.
Post #1589975
Posted Monday, July 7, 2014 9:56 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 12:53 PM
Points: 1,271, Visits: 1,066
Thank you for the response. I am sorry for not giving out the SQL box specifications. It has 256 GB RAM, of which 200 GB is dedicated to SQL Server instance. Hard drives are 1 TB each with a total of 5 drives. Data files, log files and tempDB have dedicated drives(D, E and F). Hope this information helps.
Post #1589988
Posted Monday, July 7, 2014 10:22 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:00 AM
Points: 1,914, Visits: 5,277
asiaindian (7/7/2014)
Thank you for the response. I am sorry for not giving out the SQL box specifications. It has 256 GB RAM, of which 200 GB is dedicated to SQL Server instance. Hard drives are 1 TB each with a total of 5 drives. Data files, log files and tempDB have dedicated drives(D, E and F). Hope this information helps.


Unless the local SSMS (RDP) connects to the SQL Server in a funny way, there is hardly another explanation. Does the query return a large resultset? Does the server have a dedicated graphics card? RDP session will impose load for which the server is not designed for regardless if the box's sql processing capabilities.

Quick question, what happens if you run the query from sqlcmd in the RDP session with nothing else running?
Post #1590000
Posted Monday, July 7, 2014 4:25 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 3:47 PM
Points: 1,778, Visits: 5,730
How are you logging in to SQL via SSMS - Is it SQL Authentication or Windows Authentication?

Is it the same sql user for both SSMS sessions?

Is this problem happening for ALL queries or just one particular one?



MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1590148
    Posted Tuesday, July 8, 2014 6:35 AM


    SSChampion

    SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

    Group: General Forum Members
    Last Login: Yesterday @ 5:46 AM
    Points: 13,752, Visits: 28,148
    Also check the default ANSI settings between your local SSMS and the remote SSMS. Differences there can result in different execution plans.

    How big are these queries. 30 seconds for a query is slow to begin with for most of the systems I've dealt with (except large data warehouses).


    ----------------------------------------------------
    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2012 Query Performance Tuning
    SQL Server 2008 Query Performance Tuning Distilled
    and
    SQL Server Execution Plans

    Product Evangelist for Red Gate Software
    Post #1590330
    Posted Tuesday, July 8, 2014 7:56 AM
    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Wednesday, August 6, 2014 12:53 PM
    Points: 1,271, Visits: 1,066
    Thank you all for responding. Below are the details all of you asked for:

    1. Dataset returned is 1.75 million rows. The query is a simple SELECT query, but aggregating (SUM) on a large number of rows. So i guess 30 secs is acceptable for now.
    2. Number of tables joined in the query are two. The join is a simple INNER JOIN. First table has around 5 million rows and second table has 30,000 rows. you might have already guessed, aggregation is happening on first table.
    3. Same user for all connections.
    4. Windows login used for connecting to remote server from my local SSMS client.
    5. Windows login used when connecting to the box through RDP session and through SSMS client on the box.
    6. I checked ANSI settings and both SSMS clients have similar settings. No difference.

    I tried executing the query from other machines using other user logins and got same result. Query executes much faster when connecting through remote SSMS client and same executes much slower when connecting through SSMS client on server box. For the life of me, i am not able to figure this out. Any help is highly appreciated.

    Thanks,
    Vasu
    Post #1590368
    Posted Tuesday, July 8, 2014 7:58 AM
    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Wednesday, August 6, 2014 12:53 PM
    Points: 1,271, Visits: 1,066
    Hello,

    This is happening with all queries. Same user windows login used to connect to server.

    Thanks,
    Vasu
    Post #1590369
    Posted Tuesday, July 8, 2014 8:17 AM


    SSChampion

    SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

    Group: General Forum Members
    Last Login: Yesterday @ 5:46 AM
    Points: 13,752, Visits: 28,148
    So you're going to an underpowered machine (8gb is half the memory my laptop has) and you're running a very large aggregation query and returning 1.7 million rows. The machine is expected to both process the query and then process and display the result set, but you're unsure why that's causing the system to slow down? I'm pretty sure I can tell you why. Think about it, the result set isn't just stored once. It's being processed by the server instance and then it's being processed by the client, both on the same machine. Plus, I'll be you don't have max memory set inside of SQL Server. So the server instance is chewing up as much of the memory resources as it possibly can to answer your query. Then, the OS has to try to either reclaim a bunch of that memory form SQL Server in order to store it within the client application of SSMS (which isn't using the same memory space as your SQL Server instance, they are two completely separate executables) or, it's swapping out to disk like mad in order to consume all that data.

    ----------------------------------------------------
    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2012 Query Performance Tuning
    SQL Server 2008 Query Performance Tuning Distilled
    and
    SQL Server Execution Plans

    Product Evangelist for Red Gate Software
    Post #1590385
    Posted Tuesday, July 8, 2014 12:35 PM This worked for the OP Answer marked as solution
    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 7:00 AM
    Points: 1,914, Visits: 5,277
    Grant Fritchey (7/8/2014)
    So you're going to an underpowered machine (8gb is half the memory my laptop has) and you're running a very large aggregation query and returning 1.7 million rows. The machine is expected to both process the query and then process and display the result set, but you're unsure why that's causing the system to slow down? I'm pretty sure I can tell you why. Think about it, the result set isn't just stored once. It's being processed by the server instance and then it's being processed by the client, both on the same machine. Plus, I'll be you don't have max memory set inside of SQL Server. So the server instance is chewing up as much of the memory resources as it possibly can to answer your query. Then, the OS has to try to either reclaim a bunch of that memory form SQL Server in order to store it within the client application of SSMS (which isn't using the same memory space as your SQL Server instance, they are two completely separate executables) or, it's swapping out to disk like mad in order to consume all that data.


    The server spec isn't bad and according to this the max_mem is set to 200Gb:

    Thank you for the response. I am sorry for not giving out the SQL box specifications. It has 256 GB RAM, of which 200 GB is dedicated to SQL Server instance. Hard drives are 1 TB each with a total of 5 drives. Data files, log files and tempDB have dedicated drives(D, E and F). Hope this information helps.


    I agree with Grant here, the overhead of spawning a desktop session and everything that comes with it is most likely the main cause, although this are respectable specs for a server. The desktop session is just putting pressure in the wrong place if you like. My suggestion is to try both remote SSMS and local (RDP) SSMS with the settings of discarding the result set (query options->Results->Discard Results after execution. If there is less of a difference between the two, this is definitely the culprit.

    Post #1590464
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse