SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


RDP into server - queries run slow


RDP into server - queries run slow

Author
Message
asiaindian
asiaindian
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1418 Visits: 1156
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
Eirikur Eiriksson
Eirikur Eiriksson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14902 Visits: 18591
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.
Cool
asiaindian
asiaindian
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1418 Visits: 1156
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.
Eirikur Eiriksson
Eirikur Eiriksson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14902 Visits: 18591
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?
Cool
mister.magoo
mister.magoo
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4090 Visits: 7865
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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • Grant Fritchey
    Grant Fritchey
    SSC-Dedicated
    SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

    Group: General Forum Members
    Points: 39255 Visits: 32616
    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 Query Performance Tuning and SQL Server Execution Plans
    Product Evangelist for Red Gate Software
    asiaindian
    asiaindian
    Ten Centuries
    Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

    Group: General Forum Members
    Points: 1418 Visits: 1156
    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
    asiaindian
    asiaindian
    Ten Centuries
    Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

    Group: General Forum Members
    Points: 1418 Visits: 1156
    Hello,

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

    Thanks,
    Vasu
    Grant Fritchey
    Grant Fritchey
    SSC-Dedicated
    SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

    Group: General Forum Members
    Points: 39255 Visits: 32616
    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 Query Performance Tuning and SQL Server Execution Plans
    Product Evangelist for Red Gate Software
    Eirikur Eiriksson
    Eirikur Eiriksson
    SSChampion
    SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

    Group: General Forum Members
    Points: 14902 Visits: 18591
    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.
    Cool
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search