February 17, 2004 at 5:57 am
Recently we upgraded the RAM and OS to WIN 2003 , on our SQL Server Active/Active cluster and after that we have been experiencing problems of High Disk Queue Length. On investigation it was found that it is read disk queue length which is high.
This is affecting our SQL Server Performance.
No Changes has been made to the SQL Server in the upgrade process except for RAM , we have enabled awe memory and have specified the MAX size of memory.
Is this a known issue/bug? Any ideas?
February 17, 2004 at 3:45 pm
FOR example If you have 4 GM RAM in the server. Set the Fixed memory allocation for SQL server (AWE) to 3000 MB for example and leave the rest for OS and other applications in the server.
Manian CSB
csb manian
February 18, 2004 at 4:50 am
We have 12 GB of RAM on the server and we have fixed the SQL server memory at 5.5GB as it is a Active/Active cluster , so in case of a failover the total memory should not exceed 11GB on one node for both the instances.
February 18, 2004 at 7:40 am
Do you have /pae /3GB on the boo.ini and AWE enable on the SQL ?
February 18, 2004 at 9:09 am
Yes John we have that switch in boot.ini and also awe is enabled on the SQL server.
February 18, 2004 at 2:07 pm
Hi,
Since the performance problem seems to be related to
Read only, try using profiler and check for long running queries or excessive table scans.
If you are getting lots of table scans review your indexes and make sure your statistics are up to date.
I hope this is of some help
Brandon
February 19, 2004 at 8:29 am
Do you know the caching strategy on your controller ? Is paging size is et correct ?
February 19, 2004 at 11:56 am
All the queries and SP's are same as before as no chnage has been done to these. Regarding Caching we have read ahead caching at 0% on the controller and write back at 100%.
Paging file is on a sep. drive of its own. !!!
February 19, 2004 at 12:11 pm
How much memory are being used by SQL Server instances? Can you post the result of "select * from sysprocesses where waittime > 0 order by waittime desc"? If there are any queries running, verify whether they are running in parallel.
March 26, 2004 at 11:46 am
Sorry for the delay in replying.
Here is the result:
6 0 0 0x0000 14437 RESOURCE_QUEUE 1 1 47 1 0 2004-03-23 21:33:00.980 2004-03-23 21:33:00.980 0 0 background 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 TASK MANAGER sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0
4 0 0 0x0000 2375 RESOURCE_QUEUE 0 1 11047 0 0 2004-03-23 21:33:00.980 2004-03-23 21:33:00.980 0 0 background 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 LOCK MONITOR sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0
65 4080 0 0x0042 625 SLEEP 1 1 2281 4 4 2004-03-26 09:32:06.097 2004-03-26 09:33:58.817 0 0 runnable 0x8A56BBF9E63CAE409FC927D3DB59F34E00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 MIS156277 SQL Profiler 2604 SELECT 00096B5A6BA2 TCP/IP msoni 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x02000100579A5A62000000000100000000000000 164 -1
1 0 0 0x0000 546 SLEEP 0 1 6078 0 0 2004-03-23 21:33:00.980 2004-03-23 21:33:00.980 0 0 background 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 LAZY WRITER sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0
5 0 0 0x0422 359 PAGEIOLATCH_SH 5:1:15 5 1 954578 5081 0 2004-03-23 21:33:00.980 2004-03-23 21:33:00.980 0 0 background 0x0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 AUTOSHRINK sa 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x0000000000000000000000000000000000000000 0 0
62 3128 0 0x0042 281 OLEDB MISSQLVS09\PINTSQL09 (SPID=70) 10 5 109 6 19 2004-03-26 13:43:39.083 2004-03-26 13:43:54.180 0 0 runnable 0x1C88C4C8DAAD094B99E242CFCBB2970600000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 MISWEB10 Microsoft(R) Windows (R) 2000 Operating System 4144 SELECT 000BCD1A3C99 TCP/IP newhire 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x01000A00BF33952F98601C3A0000000000000000 0 -1
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply