Sql server 2012 full memory buffer, slow query

Question

1.00/5 (1 vote)

See more:

Hi all,
i have written a program that each 10 seconds hit a query in sql server 2012 db and than refresh a graphic page; this page is function of a single record from 4 different table in the database.

The db has set his maximum size as 1024 MB, while his minimum size is 0.
The db save a row each minutes, than in just 1 Month there are over 50.000 record each table, so the growing memory consumption (observed by Windows perfomance meter) raise the maximum size.

When the maximum has achieved, a later query (just a record) selected from these 4 table, takes about 15 seconds and the UI looks like freezed.
I have observed that instead a single record asked, SQL returns the entire table; i think cause it is cached...

Any suggestion to improve the speed?

Thanks

What I have tried:

DBCC... buffer clean... eccetera

Posted 12-Feb-16 9:18am

Member 11727760

Add a Solution

Comments

ZurdoDev 12-Feb-16 15:31pm

Indexing.

Member 11727760 12-Feb-16 16:07pm

I think is not a query problem, i use the where clause with a single field named 'key' that is a string formatted with the date info and minutes details.
This field is also indexed.

I hope there is an unknown configuration option of SQL server, because this delay happened when the used memory raise the maximum size limit.

Sascha Lefèvre 12-Feb-16 15:33pm

"I have observed that instead a single record asked, SQL returns the entire table"

How did you tell SQL Server to return a single record? Maybe show your SQL query here.

Member 11727760 15-Feb-16 8:34am

"SELECT * FROM [Temperatures] WHERE IdMachine = " & Id & " AND Key= '" & Key& "'"

Sascha Lefèvre 15-Feb-16 9:10am

You don't explicitly tell SQL Server here that it should return only a single record. If your constraints (IdMachine and Key) match more than one record, all matching records will be returned. To explicitly restrict the result to one record you could use the TOP-clause:
SELECT TOP 1 * FROM ...

Member 11727760 16-Feb-16 12:23pm

The 'Key' named field is unique; it is a string field with a date formatted into.
With this assumption do you think changing the query from the original "Select *..." in your's "Select TOP 1 *.." should make it quickly?

I have observed that the entire Table is loaded into memory, for future query.. but in this case, when the memory raises the maximum value (cause the record table raises...) then disk access happening.

Sascha Lefèvre 16-Feb-16 12:37pm

Well, there has to be some reason that you get more than one record returned - so probably one of your assumptions is incorrect.

Member 11727760 16-Feb-16 14:28pm

Sorry, maybe i was misunderstood. After the query it was obviouvsly returned me just a record, the 'where' clause with the field named 'key' select only one row.
Instead a query that asked a single record, i have noticed that SQL server load the entire table in memory.
When the table, that is growing in time, become bigger to raise the maximum size of memory reserverd into SQL server the query become slowly;
Should the "SELECT TOP 1..." than "SELECT *..."'s solution make it better?

Sascha Lefèvre 16-Feb-16 14:40pm

Ah, alright, got it now.
I don't think TOP 1 will help in that case but I'd give it a try anyway. And, like GhostHost said, if you don't actually need all the columns, don't use SELECT * but list the columns you need individually.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

GabrieleTronchin · Answer 1 · 2016-02-15T22:27:00

1 - Make sure that your query is optimized, for example avoid SELECT * you should individually include the specific columns that you need.

2 - Analyse query Execution Plan

3 - Profile your database on SQL Tuning mode and try to use Database Engine Tuning Advisor

4 - If is possible increase memory that SQL can use