[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

(pointer) SQL Server slows down after a couple hours

Posted on 2007-10-08
5
Medium Priority
?
534 Views
Last Modified: 2008-01-09
Still having trouble with SQL Server. I would appreciate any insight the experts have to offer.

Please see original question here (500 points):
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22870374.html

Thanks.
0
Comment
Question by:phxvigo
  • 2
  • 2
5 Comments
 
LVL 8

Expert Comment

by:Bradley Haynes
ID: 20035848
There are several things that jump out at me. First, whenever possible run SQL Server on its' own box.
Second, check your queries to optimize performance. You want queries to SEEK not SCAN which is done via Indexing, and providing numerical search criteria as often as you can.
Have you set up Maintenance jobs?
Reducing fragmentation or in database "speak" lack of contiguous space allocation is done with the Maintenance Plans you set up.
These are vital DBA  tasks.

note: I posted this comment on the original question as well.
0
 
LVL 5

Expert Comment

by:Crag
ID: 20039703
This is not an easy question to answer (doh!) as you'll need to collect the windows performance data and the profiler activity for a period when it transitions from good performance to poor. From those you'll need to work out if the CPU, disk,memory or network are the bottleneck and use the Profiler data to identify what transactions occurred at that time. I do this kind of work and it is very rare that you can just look at a system and go - oh yes there's the cause.
As the system isn't dedicated to SQL only, it may be that other processes are starting up and taking all of the memory from SQL - hence it goes back to the disk for the data. Does the Application process have a memory leak and it takes all of the memory? Have you tried setting SQL to have a fixed min/max memory allocation - say 2GB?
0
 
LVL 8

Accepted Solution

by:
Bradley Haynes earned 80 total points
ID: 20042570
You can also use Sysinternals to view activity in real time. Get it here:
http://www.microsoft.com/technet/sysinternals/default.mspx


There forum is:
http://forum.sysinternals.com/
0
 

Author Comment

by:phxvigo
ID: 20042893
Craig:

Thanks for the reply. I have been using perfmon to monitor the general system state, but haven't found much more than I've already said. I don't think it's a CPU issue as the CPU usage is around 20% average. It seems like a memory issue as it decides it needs to read from disk, however perfmon says the system still has free physical memory. The SQL target server memory in perfmon does not change or go down. The problem seems to occur after SQL total server memory reaches the target server memory, but not immediately. Are there any other metrics which might indicate what is going on?

Perhaps SQL server is using its allocated memory for something other than cacheing, and not releasing that memory. When it hits its max memory, it runs out of room for cacheing data. But what is it using it's memory for, and why isn't it releasing it? I don't know, just grasping at straws...
0
 
LVL 5

Expert Comment

by:Crag
ID: 20048820
I'm going to check out/reply to the original posting
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question