August 26, 2008 at 11:53 am
I'm running SQL Server 2005 on dual-xeon quad core processors with 8 gb of ram.
Over the past few weeks, we have noticed our server running slower than normal at intermittent intervals.
Here are some examples:
1. identical table inserts to a very small table can be near instantaneous to taking 45 seconds
2. remote desktoping to the server and switching between programs can be at "normal speed" or very slow speed (i.e., a click and corresponding cursor movement can take 5-10 seconds)
Initially, we blamed consistent 100% server CPU utilization. After rebooting the server, we still saw the same problems albeit not as dramatic. For example, previous example #2's slow speed was now 1-2 seconds. One day after the reboot, CPU Utilization has occasionally spiked to 100%, but mostly remains around 25-50%. Page file usage has steadily increased, & physical memory decreased from 8GB to 50-200MB.
Here's a snapshot summary of task manager/performance tab:
Right after reboot:
PF Usage: 4.55GB
Physical Memory (K)
Total: 8383116
Available: 3584988
System Cache: 1273228
Commit Charge (K)
Total: 4777944
Limit: 10027516
Peak: 4786576
1 day after reboot:
PF Usage: 7.94GB
Physical Memory (K)
Total: 8383116
Available: 123204
System Cache: 1177444
Commit Charge (K)
Total: 8332468
Limit: 10136060
Peak: 9592208
Does anyone have any ideas on fixing the problem or further troubleshooting?
August 26, 2008 at 11:57 am
What are your memory settings in sql server?
Have you tried walking to the machine and logging on, instead of rdp?
What types of disks are you using?
August 26, 2008 at 1:19 pm
Sam,
Here are the responses to your questions:
1Q. What are your memory settings in sql server?
1A. What sql server memory settings are you referring to? And how do you access those?
2Q. Have you tried walking to the machine and logging on, instead of rdp?
2A. Yes, it exhibits the same problems as logging in with rdp,
3Q. What types of disks are you using?
3A. Dell 7200RPM SCSI drives
Thanks again for your help.
August 26, 2008 at 1:32 pm
jlp3630 (8/26/2008)
Sam,Here are the responses to your questions:
1Q. What are your memory settings in sql server?
1A. What sql server memory settings are you referring to? And how do you access those?
2Q. Have you tried walking to the machine and logging on, instead of rdp?
2A. Yes, it exhibits the same problems as logging in with rdp,
3Q. What types of disks are you using?
3A. Dell 7200RPM SCSI drives
Thanks again for your help.
You're welcome! I'm not the best or brightest on here, but hopefully I can get you started and someone can jump in who knows more...
1 - Go to SSMS and rightclick your server, click properties and then memory. What's max and min set to?
2 - RDP can be slow for me, so that could rule out network issues between your workstations and the server
3 - Where are these things installed? Windows, SQL Server, Data files, Log files, system databases.
If your processor is not pegged, you should concentrate on your memory and disk performance.
Use perfmon and set up some counters on memory and disks. Memory Cache hit ratio might be a good one to see if you need more memory.
Are you on a 64 bit system? If not, have your configured the system to take advantage of more that 4GB of memory?
---edit---
>>Page file usage has steadily increased, & physical memory decreased from 8GB to 50-200MB.
So yes it looks like you are using all that memory. But you can't tell if it needs more memory unless you evaluate some performance counters, since sql will grab all it can.
August 26, 2008 at 1:40 pm
The first link has a few counters you could look at.
August 26, 2008 at 2:48 pm
Windows and SQL Server are installed on c:master, model and msdb databases and log files are on c:tempdb, user databases and log files are on f:
I'm using a 64 bit system, and I've seen sqlserver.exe take up over 6.5GB.
The max memory on my database is:
2,147,483,647 MB
Thanks again for your help,
Jon
August 26, 2008 at 2:57 pm
On 64 bit systems you should limit the max memory. SQL's quite capable of taking all tha available memory and starving the OS. Try setting it to 6.5GB. Dunno if it will make a difference, but it is worth a try.
Enterprise or standard edition? What service pack? (run SELECT @@Version to get info on both)
Is F drive a RAID array? If so, what type of RAID?
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
August 26, 2008 at 3:07 pm
Here are a few more resources for you
SQL Performance Dashboard.
Install and then run the setup.sql script on your instance. Be sure to look at the instructions on that page.
General troubleshooting info:
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
August 26, 2008 at 3:24 pm
It's hard to tell offhand what exactly your problem is, but I did have a couple of suggestions...
Your Maximum Server Memory for your SQL Server instance probably should not be so large. From your original post, you have 8 gig of physical RAM on this server, so you'll probably want to set your Maximum Server Memory no higher than 7168. You don't want the OS swapping out RAM that has SQL Server cache pages in it out to disk, that will just create an unneccessary I/O bottleneck. You're using a 64 bit server, is the OS and SQL Server 2005 also 64 bit? If you're using 64 bit SQL Server you may want to investigate using the Lock Pages in Memory option.
Also, you mentioned that the user database, user transaction logs, and TempDB are all on the same drive. This could lead to some I/O contention, especially on writes, since it is very likely that transaction logs and data files will be written to at the same time, and somewhat likely that TempDB will be written to as well in some cases.
August 26, 2008 at 3:30 pm
Are your SQL Server Error Logs clear or are they any errors that can be noted here?
I would be curious if this KB article applies.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 26, 2008 at 3:55 pm
You may want to look at the task manager to dertermine which process is driving the CPU usage on the server.
If it is SQL server that is driving high CPU usage, you might want to check if there are any sql process/scripts are causing table/index scan. High CPU usage in SQL server is usually indication of table/index scan) Good place to start would be looking at sys.dm_db_index_usage_stats table.
August 27, 2008 at 6:50 am
GilaMonster (8/26/2008)
On 64 bit systems you should limit the max memory. SQL's quite capable of taking all tha available memory and starving the OS. Try setting it to 6.5GB. Dunno if it will make a difference, but it is worth a try.Enterprise or standard edition? What service pack? (run SELECT @@Version to get info on both)
Is F drive a RAID array? If so, what type of RAID?
I'm running RAID-0 on my c: drive (contains Windows, SQL server, master, model and msdb databases and log files).
I'm running RAID-50 on my f: drive (tempdb, user databases and log files).
Here are the results of @@version:
Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Thanks again for your help.
August 27, 2008 at 7:15 am
DavidB (8/26/2008)
Are your SQL Server Error Logs clear or are they any errors that can be noted here?
Here's a sample of notable errors:
08/25/2008 09:59:37,,Error,[298] SQLServer Error: 258 Unable to complete login process due to delay in login response [SQLSTATE 08001]
08/25/2008 09:59:37,,Error,[165] ODBC Error: 0 Login timeout expired [SQLSTATE HYT00]
08/25/2008 09:59:37,,Error,[298] SQLServer Error: 258 Shared Memory Provider: Timeout error [258]. [SQLSTATE 08001]
08/25/2008 10:21:34,,Error,[382] Logon to server '(local)' failed (ConnUpdateStartExecutionDate)
08/25/2008 10:21:34,,Error,[165] ODBC Error: 0 Unable to complete login process due to delay in opening server connection [SQLSTATE 08001]
These errors were occurring while the server was experiencing 100% CPU utilization.
I would be curious if this KB article applies.
Which KB article are you referring to?
August 27, 2008 at 8:03 am
charshman (8/26/2008)
It's hard to tell offhand what exactly your problem is, but I did have a couple of suggestions...Your Maximum Server Memory for your SQL Server instance probably should not be so large. From your original post, you have 8 gig of physical RAM on this server, so you'll probably want to set your Maximum Server Memory no higher than 7168. You don't want the OS swapping out RAM that has SQL Server cache pages in it out to disk, that will just create an unneccessary I/O bottleneck. You're using a 64 bit server, is the OS and SQL Server 2005 also 64 bit? If you're using 64 bit SQL Server you may want to investigate using the Lock Pages in Memory option.
Also, you mentioned that the user database, user transaction logs, and TempDB are all on the same drive. This could lead to some I/O contention, especially on writes, since it is very likely that transaction logs and data files will be written to at the same time, and somewhat likely that TempDB will be written to as well in some cases.
Thanks for the info. I'm using Windows Server 2003 Standard Edition 64-bit & SQL Server 2005 Standard Edition 64-bit.
Just for my own information, why did you choose 7168 as the maximum server memory?
August 27, 2008 at 8:14 am
Very sorry about that. The link I was referring to was http://support.microsoft.com/kb/918483.
Not so sure that applies based on the errors that you provided.
Have you installed the performance dashboard as recommended by an earlier response? I would be interested in seeing what you are seeing as your primary wait types.
select * from sys.dm_os_wait_stats order by wait_time_ms desc
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply