March 27, 2008 at 8:49 am
Our clients are reporting that the sql server is slowing down over a period of time. Once the SQL Server is restarted
everything is fine.SQL server slowing down over a period of time though has technical reasons. SQL server caches (in memory) the queries that are sent it to, expecting to reuse it if the same query is sent again. Over a period of time this cache builds up (and the memory) to a point where it reaches the RAM limit. After this it starts to use the hard disk for memory (virtual memory) which is slower. Restarting the sql server will clear the cache thereby speeding up the server. This problem is partly to do with how SQL server is designed and mostly with application design. SQL Server 2005 SP2 is supposed to address this cache bloating problem. A poorly designed SQL query will never get reused but they just bloat the cache. Rewriting these queries to be reused will make caching more effective. I have a set of queries which I believe that are causing the RAM to bloat.Most of the queries are Select statements, How can I effectively rewrite these queries so that they are flushed out of the RAM each time after they are used?
March 27, 2008 at 9:02 am
You can use the OPTION(Recompile) query hint. From BOL:
RECOMPILE
Instructs the SQL Server 2005 Database Engine to discard the plan generated for the query after it executes, forcing the query optimizer to recompile a query plan the next time the same query is executed. Without specifying RECOMPILE, the Database Engine caches query plans and reuses them. When compiling query plans, the RECOMPILE query hint uses the current values of any local variables in the query and, if the query is inside a stored procedure, the current values passed to any parameters.
RECOMPILE is a useful alternative to creating a stored procedure that uses the WITH RECOMPILE clause when only a subset of queries inside the stored procedure, instead of the whole stored procedure, must be recompiled. For more information, see Recompiling Stored Procedures. RECOMPILE is also useful when you create plan guides. For more information, see Optimizing Queries in Deployed Applications by Using Plan Guides.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 27, 2008 at 10:55 am
and re-tune those queries.
The data cache is supposed to fill up with data for queries. If you have bad queries, then are they not reused? Or is just certain queries that go bad and fill the data cache?
What's your buffer cache hit ratio?
March 27, 2008 at 11:18 am
I checked each of the queries they are not what we call bad queries. They cannot be optimized further which I am sure. The one thing I noticed about these queries is that none of the queries are used in the any procedure,views and functions.
I think since all of them are single Select statement they are used in application side and not in the backend or database side. Is this is the root cause?
Sorry I have not obsevered the buffer cache hit ratio.
March 27, 2008 at 12:28 pm
Ashwin M N (3/27/2008)
I checked each of the queries they are not what we call bad queries. They cannot be optimized further which I am sure. The one thing I noticed about these queries is that none of the queries are used in the any procedure,views and functions.I think since all of them are single Select statement they are used in application side and not in the backend or database side. Is this is the root cause?
Sorry I have not obsevered the buffer cache hit ratio.
Steve is right you should check your buffer cache hit ratio and also run a profile looking for the Cache Events (Hit, Miss, Insert, Remove). My understanding of the procedure cache is that queries are "aged" out of it. Of course my understanding could be incorrect.
You can clear the procedure cache using DBCC FreeProcCache and clear the Buffer Cache using DBCC DropCleanBuffers. This eliminates a restart.
You can also check out the status of the Procedure cache using DCC ProcCache and the dm_exec_cached_plans, dm_exec_plan_attributes, and dm_exec_sql_text dynamic management views. (Found this in Microsoft SQl Server 2005 Unleashed)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 27, 2008 at 1:30 pm
How much memory do you have? What service pack of SQL are you running? 32 bit or 64 bit?
If you run the following, what's taking the most memory?
select [Name], SUM(single_pages_kb + multi_pages_kb)/1024.0 AS CacheSizeInMB
from sys.dm_os_memory_clerks
where group by [Name]
Order By CacheSizeInMB DESC
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2008 at 1:39 pm
Ashwin M N (3/27/2008)
I checked each of the queries they are not what we call bad queries. They cannot be optimized further which I am sure.
Can you post one or two of those? There are some people around here who will take that statement as a challenge. (Myself included) 😀
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2008 at 1:59 pm
GilaMonster (3/27/2008)
How much memory do you have? What service pack of SQL are you running? 32 bit or 64 bit?If you run the following, what's taking the most memory?
select [Name], SUM(single_pages_kb + multi_pages_kb)/1024.0 AS CacheSizeInMB
from sys.dm_os_memory_clerks
where group by [Name]
Order By CacheSizeInMB DESC
When I ran this query, the top 2 entries are:
SQL Plans242.687500
Object Plans42.570312
It seems to me that 284MB for plans is a HUGE. Isn't it?
March 27, 2008 at 2:16 pm
I can answer that when you tell me how much memory's on the server.
What version of SQL 2005? (What does SELECT @@version return?)
To give you an idea, one of my servers has 42 GB memory allocated to SQL and the proc cache ranges from 8-10 GB on average.
It looks like you have a lot of unparametrised ad-hoc SQL. If you could get that changed to parameterised SQL or stored procedures, you'll probably see a lot better usage of the procedure cache
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2008 at 2:48 pm
I have SQL 2005 SP2, with 16GB of RAM on the server.
Also, a couple of people here raised contradicting points about the cache used for sql plans. One said that once the cache is full, then SQL Server starts writing to virtual memory. Another said that the last used plan is flushed out. Which one is correct?
March 27, 2008 at 3:00 pm
284MB for the plan cache on a server with 16 GB is nothing. I really wouldn't worry.
What are your server's max and min memory settings? Anything else on the server?
The plan cache has a soft size limit. Once that limit is reached or exceeded, older, less used plans are removed from the plan cache.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2008 at 3:09 pm
Regarding your slow downs...
When the server goes slow, check the sys.dm_os_waiting_tasks DMV, see if queries are waiting and, if so, what it is that they are waiting for.
You may be running into this problem - Memory and SQL 2005 SP2[/url]. Check the dm_os_memory_clerks when the server's running slow.
You may also want to run perfmon during one of these slow downs, see if any counters are wildly different from normal.
Check pages/sec, disk queue length, disk idle%, processor %, SQL's cache hit ratio, average lock wait time, average latch wait time.
Anything that's different from normal may indicate the potential cause of the problem.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 27, 2008 at 3:18 pm
Thank you very much Gail for answering my questions. I don't have the same problem that the OP has, but I ran your query and was surprised by the amount of cache that is dedicated to plans. Am glad to know that it's normal!
March 27, 2008 at 3:41 pm
GilaMonster (3/27/2008)
284MB for the plan cache on a server with 16 GB is nothing. I really wouldn't worry.What are your server's max and min memory settings? Anything else on the server?
The plan cache has a soft size limit. Once that limit is reached or exceeded, older, less used plans are removed from the plan cache.
Is this a 32-bit or 64-bit instance?
If it's 32-bit, the plan cache cannot utilize the full extent of memory space available through AWE.
That is only available to the data cache. As such, 248 MB, while not a lot, is a sizable chunk of the 2 GB virtual-memory limit available by default to SQL server on a 32-bit instance.
Admittedly, it may still not be large enough to be a factor in this case, but I just thought it was worth clarifying this point.
See: http://blogs.msdn.com/joaol/archive/2008/02/05/sql-server-2000-64-bits-versus-sql-server-2000-32-bits-memory-management.aspx
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 27, 2008 at 4:45 pm
Thank you Marios!
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply