slow SQL2k5 on X64 win 2003 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

slow SQL2k5 on X64 win 2003

Author

Topic

Hitesh Shah
Yak Posting Veteran

80 Posts

Posted - 2008-07-17 : 07:00:18

Sometimes we evidence very slow SQL operation for our 150+ user application. At the time people experience slow response , 2-3 users are running a stand alone application using the SQL instance . I saw in activity monitor The CPU & memory usage by these people was normal.However the protocol used by the application was named pipes whereas our main application uses TCP_IP . Ours is clustered SQL instance. Can this be a cause for slow SQL response .

On server I ran following query and go the following response .
(select * from sys.dm_io_virtual_file_stats(-1, -1))
1 1 1495224343 3035 70680576 16032 187 1531904 306 16338 4194304 0x00000000000000CC
1 2 1495224343 44 761856 145 368 1728512 380 525 1310720 0x00000000000005F4
2 1 1495224343 614919 36049780736 945539 723467 41027403776 2517711 3463250 624361472 0x000000000000064C
2 2 1495224343 44 647168 197 1655 65073152 6834 7031 3932160 0x0000000000000678
3 1 1495224343 572 28139520 2682 169 1384448 254 2936 2293760 0x0000000000000638
3 2 1495224343 53 782336 124 171 921600 182 306 4325376 0x000000000000063C
4 1 1495224343 9222 304406528 176850 4188 36339712 5909 182759 15138816 0x00000000000008F4
4 2 1495224343 97 2076672 366 4330 21516288 6193 6559 2097152 0x00000000000008F8
5 1 1495224343 96525398 1528916623360 227479225 3854884 45464477696 82484573 309963798 42467328000 0x00000000000008EC
5 2 1495224343 78477 60093789696 527726 77587341 58830533632 79079687 79607413 5242880000 0x000000000000092C
5 3 1495224343 112514 115636445184 484224 17 139264 70 484294 6970933248 0x0000000000000920
5 4 1495224343 35320 29914415104 169418 126 2465792 3006 172424 2147483648 0x0000000000000924
5 5 1495224343 6807 976519168 106737 36984 344342528 221936 328673 5368709120 0x0000000000000928

select * from sys.dm_os_wait_stats where wait_type like '%PAGEIO%'

PAGEIOLATCH_NL 0 0 0 0
PAGEIOLATCH_KP 0 0 0 0
PAGEIOLATCH_SH 94077753 194632265 3750 299890
PAGEIOLATCH_UP 10892 85375 4171 218
PAGEIOLATCH_EX 1018807 7987687 2953 4593
PAGEIOLATCH_DT 0 0 0 0

Is there anything to check / iinvestigate correct from this .

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-07-17 : 14:48:31

What do your disk stats look like? Specifically
Physical disk:Avg Sec/read
Physical disk:Avg Sec/write
Physical disk:% idle time

Also check
SQL Server Buffer manager:Cache hit ratio

(perfmon stats)

--
Gail Shaw
SQL Server MVP

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-17 : 23:41:56

Protocol shouldn't affect query performance. Did you see waiting on network for those slow processes?

Hitesh Shah
Yak Posting Veteran

80 Posts

Posted - 2008-07-18 : 01:02:55

For most processes in the activity monitor the status is sleeping and command is awaiting command. Blocked by and blocking context is all 0 . Wait time and wait type are all 0 / blank.

What is process id (is it server process id ) and physical id . In our case user is irrelevent because db connection is through single user only .

I am checking the perfo mon statstics and will give as soon as i get it.

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-07-18 : 07:32:17

Have you checked any sql that is running using the profiler?

Hitesh Shah
Yak Posting Veteran

80 Posts

Posted - 2008-07-18 : 08:28:30

Counter name Min Min Date Max Max Date avg
Memory-pages-sec - 7/16/2008 1:59 " 18,908.2 " 7/8/2008 15:46 372.08
Network-Interface-Bytes-Total-Sec-1 588.3 7/16/2008 1:29 " 60,319,648.8 " 7/15/2008 15:46 " 2,557,431.24 "
Network-Interface-Bytes-Total-Sec-2 " 3,121.8 " 7/16/2008 0:48 " 18,292.1 " 7/15/2008 16:41 " 5,398.09 "
Network-Packets-Sec-2 13.6 7/15/2008 13:58 65.8 7/15/2008 10:33 19.12
Memory-Available-Mbytes 70.0 7/17/2008 0:23 222.0 7/16/2008 10:45 139.45
Memory-Committed-Bytes " 7,980,314,624.0 " 7/16/2008 19:42 7/17/2008 0:48 " 8,112,759,468.54 "
Memory-Pages-Faults-Sec 168.0 7/16/2008 16:33 " 63,145.5 " 7/16/2008 19:18 " 2,183,271.33 "
Memory-pages-sec - 7/17/2008 1:59 " 17,354.8 " 7/17/2008 0:23 841.10
Network-Interface-Bytes-Total-Sec-1 951.0 7/16/2008 19:36 " 63,935,607.7 " 7/16/2008 20:00 " 3,091,358.27 "
Network-Interface-Bytes-Total-Sec-2 " 2,694.6 " 7/16/2008 9:01 " 23,431.6 " 7/16/2008 17:29 " 5,615.15 "
Network-Packets-Sec-1 5.2 7/16/2008 9:01 58.0 7/16/2008 17:29 20.68
Network-Packets-Sec-2 13.6 7/16/2008 20:02 58.1 7/16/2008 17:27 19.30
Pagging-File-Per-Usage 13.3 7/17/2008 0:23 14.8 7/17/2008 0:41 13.71
Physical-Disk--Sec-Transfer - 7/16/2008 17:11 0.1 7/16/2008 11:11 0.01
Physical-Disk-Avg-disk-bytes-transfer 612.5 7/16/2008 13:47 " 504,022.7 " 7/17/2008 0:14 " 14,976.98 "
Physical-Disk-Avg-disk-queue-length 0.0 7/16/2008 22:23 18.3 7/16/2008 13:59 4.53
Physical-Disk-Per-Time 0.3 7/16/2008 22:06 141.2 7/16/2008 13:59 3.74
Physical-Disk-Transfer-Sec 6.9 7/16/2008 19:36 " 2,801.7 " 7/16/2008 17:11 199.77
Processer-Interrupts-Sec 1.8 7/17/2008 1:59 4.1 7/17/2008 0:33 1.30
Processer-per-Privilege-Time 0.5 7/16/2008 21:50 28.1 7/16/2008 19:18 7.14
Processer-per-Time 1.1 7/16/2008 19:33 71.0 7/16/2008 23:47 23.96
Processer-Per-User-Time 0.3 7/16/2008 19:22 51.5 7/16/2008 23:52 16.84
Sql -Server-Bufer 47.7 7/16/2008 11:15 99.9 7/16/2008 12:03 98.57
Sql-Server-Genral-User-Connection 31.0 7/16/2008 19:29 757.0 7/16/2008 15:20 355.41
Syste-Context-Switches-Sec " 1,679.8 " 7/16/2008 19:35 " 99,572.8 " 7/16/2008 23:46 " 31,136.23 "
System-Processer-Queue-Length - 7/17/2008 1:59 4.0 7/16/2008 14:06 8.48
System-Processes 50.0 7/16/2008 10:36 53.0 7/17/2008 0:48 51.03
System-Processes 50.0 7/16/2008 10:36 53.0 7/17/2008 0:48 51.03
Processer-Interrupts-Sec 0.0 7/16/2008 1:59 4.0 7/5/2008 0:41 1.53
Memory-Available-Mbytes 87.0 7/18/2008 0:23 405.0 7/17/2008 13:00 238.27
Memory-Committed-Bytes " 8,157,753,344.0 " 7/17/2008 22:59 " 8,311,033,856.0 " 7/18/2008 0:51 " 8,196,681,547.73 "
Memory-Pages-Faults-Sec 186.3 7/17/2008 17:48 " 18,326.4 " 7/18/2008 0:23 " 2,208,320.14 "
Memory-pages-sec - 7/18/2008 1:59 " 12,366.9 " 7/17/2008 15:46 844.46
Network-Interface-Bytes-Total-Sec-1 " 1,303.1 " 7/17/2008 21:37 " 71,971,613.0 " 7/18/2008 0:23 " 3,368,298.02 "
Network-Interface-Bytes-Total-Sec-2 " 3,126.3 " 7/18/2008 1:28 " 21,739.9 " 7/17/2008 15:23 " 6,091.76 "
Network-Packets-Sec-1 3.6 7/17/2008 9:02 53.2 7/17/2008 15:02 22.42
Network-Packets-Sec-2 13.7 7/17/2008 13:34 58.4 7/17/2008 14:55 20.93
Pagging-File-Per-Usage 17.6 7/17/2008 11:59 19.6 7/17/2008 13:00 18.34
Physical-Disk--Sec-Transfer - 7/18/2008 0:33 0.1 7/17/2008 11:48 0.01
Physical-Disk-Avg-disk-bytes-transfer 636.5 7/17/2008 12:17 " 764,582.1 " 7/17/2008 19:00 " 13,712.95 "
Physical-Disk-Avg-disk-queue-length 0.0 7/17/2008 22:49 18.4 7/17/2008 13:16 4.18
Physical-Disk-Per-Time 0.3 7/17/2008 21:52 153.9 7/17/2008 13:16 3.66
Physical-Disk-Transfer-Sec 7.1 7/17/2008 19:10 " 2,580.4 " 7/17/2008 17:07 190.31
Processer-Interrupts-Sec 0.0 7/17/2008 23:00 3.9 7/18/2008 0:39 1.53
Processer-per-Privilege-Time 0.5 7/17/2008 22:52 19.8 7/18/2008 0:26 8.20
Processer-per-Time 1.1 7/17/2008 19:14 69.6 7/18/2008 0:21 28.11
Processer-Per-User-Time 0.4 7/17/2008 19:13 51.0 7/17/2008 16:34 19.91
Sql -Server-Bufer 51.2 7/17/2008 17:06 99.9 7/17/2008 15:53 98.88
Sql-Server-Genral-User-Connection 54.0 7/17/2008 19:44 782.0 7/17/2008 15:58 371.88
Syste-Context-Switches-Sec " 1,276.9 " 7/17/2008 19:14 " 91,065.8 " 7/18/2008 0:21 " 36,456.72 "
System-Processes 48.0 7/17/2008 17:13 55.0 7/17/2008 15:46 50.07
System-Processes 48.0 7/17/2008 17:13 55.0 7/17/2008 15:46 50.07

The above counters are (Min/Max & avg ) gathered from SQL server machines for last 2 days. Does it have anything useful for helping us,

Hitesh Shah
Yak Posting Veteran

80 Posts

Posted - 2008-07-18 : 08:29:59

quote:
Originally posted by dexter.knudson

Have you checked any sql that is running using the profiler?

Not yet . Learning to use profiler.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources