July 7, 2014 at 8:51 am
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
July 7, 2014 at 9:36 am
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.
😎
July 7, 2014 at 9:56 am
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.
July 7, 2014 at 10:22 am
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?
😎
July 7, 2014 at 4:25 pm
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);July 8, 2014 at 6:35 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 8, 2014 at 7:56 am
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
July 8, 2014 at 7:58 am
Hello,
This is happening with all queries. Same user windows login used to connect to server.
Thanks,
Vasu
July 8, 2014 at 8:17 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 8, 2014 at 12:35 pm
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.
😎
July 9, 2014 at 4:28 am
Where the heck did I see it was 8gb? Maybe time to up my coffee intake. Sorry for the misunderstanding there.
The rest of the processing bit is still correct. You're expecting two separate executables on a single machine to process as much information as quickly as it would on two machines.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply