February 12, 2021 at 4:17 am
I am having a problem with my sql server running queries and using it in Visual Basic. The primary table I pull data from has 24 columns with 76,000 rows. Sometimes it takes less than 6 seconds to run the query. Other times, it takes over two minutes and once in a while, it stops because it runs out of memory. I use my computer name for Server name, Sql Server Authencation, user name and password to connect to the server.
Now here is the interesting part. I have another laptop that I take to a keno parlor to work with and when I connect to my home laptop from the keno parlor, it runs fast all afternoon. I have a VPN connection back to my house so I can connect to my home laptop. I also run a visual basic program to pull tables from my server. When I am at home, it also sometimes takes forever to pull data, but when I run my Visual Basic program from the keno parlor it runs fine with no problems all afternoon.
I have 6 gig of memory on my laptop and just ordered 16 gig of memory for it.
I installed Sql Server using the default settings and update it every time it tells me to, I am not much of a DBA so I don't know where to start to figure out what is happening. Any help will be appreciated.
Thanks
Sql Server version:
SQL Server Management Studio 15.0.18369.0
SQL Server Management Objects (SMO) 16.100.46041.41
Microsoft Analysis Services Client Tools 15.0.19342.0
Microsoft Data Access Components (MDAC) 10.0.19041.1
Microsoft MSXML 3.0 6.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 10.0.19042
My computer is:
Device name
Processor AMD A8-3520M APU with Radeon(tm) HD Graphics 1.60 GHz
Installed RAM 6.00 GB (5.48 GB usable)
Device ID C61F92F8-2DC0-4B7C-B414-73E13EC26677
Product ID 00330-80000-00000-AA134
System type 64-bit operating system, x64-based processor
Pen and touch No pen or touch input is available for this display
February 12, 2021 at 1:12 pm
Troubleshooting query performance is an enormous topic. In fact, if you look down in my signature line, I have two books on it. One is nearly 1,000 pages long. The other is 600+ pages long. I'm just putting this out there, not to sell the books, but to let you know that giving you all the information you might need in a post like this just isn't possible.
It's likely that you're dealing with contention. When you run queries locally, you're not only dealing with SQL Server processing the query, but your local machine has to deal with the results of the query, at the same time. While querying remotely, the SQL Server instance isn't contending with the second set of resources. However, you shouldn't see that wildly variant behaviors. I do lots and lots of demos, tests, etc., on a local VM of SQL Server and have very consistent behavior.
So, I'd suggest two things. First, enable Extended Events to capture query metrics, run time, reads, writes. Second, when your query runs slowly, get the execution plan. You can query that from cache, or, capture the plan as you run it. Between the two, you'll have information that's going to let you narrow down when things run slow and why.
"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
February 12, 2021 at 5:00 pm
You do not say what version of SQL Server is on your laptop but the default install for any version other than SQL2019 allows SQL Server to use as much memory as it can grab. This can cause other programs, including Windows, to start swapping memory to disk.
If you are not using SQL2019 try running the following within SSMS:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 3072;
GO
RECONFIGURE;
February 13, 2021 at 4:58 am
Sorry that I responded back to the email letting me know that you answered me. Here is my response.
Thank you for your message and not flaming me. All I am looking for is a starting point. I just got back from the road and will look into it tomorrow. Thanks again
February 13, 2021 at 5:01 am
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19042: )
I really thought that I had downloaded and installed 2019. I'll have to look into it. Thanks for your response. (I get confused with SSMS version and sql server version.
February 13, 2021 at 5:30 pm
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 19042: )
I really thought that I had downloaded and installed 2019. I'll have to look into it. Thanks for your response. (I get confused with SSMS version and sql server version.
Just to add to that, the RTM of 2017 had some really serious performance issues. Even normally very high performance code that used things like an fnTally function lost their minds in the execution plan. With that, I'll also state that all this "automatic tuning" junk just scares the hell out of me.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 16, 2021 at 6:04 pm
Thank you! I ran it and it seems to be working.
February 16, 2021 at 6:08 pm
OK, so I ran Ken McKelvey's solutions and it seems to be working so far. I also upgraded the machine memory to 16 Gig.
I want to thank you for not flaming me. I honestly didn't know where to start solving my problem.
Now to upgrade to Sql Server 19.
Blessings on all of you who answered and to the rest of you on the forum.
Carroll
February 17, 2021 at 5:42 pm
As you have increased the machine's memory to 16GB it may be worth increasing Max Server Memory for SQL Server from 3GB to 8GB. You can do this via script or by right clicking on the machine within SSMS and selecting Properties. This should allow plenty of memory for Windows, VS etc.
I would also increase the Cost Threshold for Parallelism to 50 as the default of 5 is far too low. You may also want to set the Max Degree of Parallelism to 2.
An in place upgrade to SQL2019 on a laptop should be fine. It is wise to have backups just in case anything goes wrong.
I would also patch SQL Server to the latest CU and not rely on the RTM. Details are here:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply