Threads and Handles

[pcawdron]

Our SQL Server is running like a dog. I can't see why, but when I look at the win2000 process information it shows SQL Server taking 34,000 handles!!!!

Any ideas?


Process information for xxxx:

Name Pid Pri Thd Hnd Mem User Time Kernel Time Elapsed Time
Idle 0 0 1 0 16 0:00:00.000 672:29:49.343 690:14:04.640
System 2 8 46 464 28 0:00:00.000 0:48:23.250 690:14:04.640
smss 25 11 6 30 20 0:00:00.171 0:00:00.156 690:14:04.640
csrss 33 13 13 378 1652 0:03:12.687 0:05:45.031 690:13:42.375
winlogon 39 13 3 90 2448 0:00:05.500 0:00:23.062 690:13:41.609
lsass 48 9 12 125 1876 0:13:40.921 0:20:52.859 690:13:39.953
spoolss 82 8 7 58 60 0:00:00.031 0:00:00.234 690:13:34.125
RpcSs 96 8 9 126 820 0:00:09.171 0:00:13.671 690:13:29.921
msdtc 104 8 18 108 692 0:00:07.109 0:00:09.906 690:13:29.468
cisvc 123 8 9 152 556 0:00:27.140 0:01:26.015 690:13:27.546
DKService 126 8 5 47 824 0:00:01.562 0:00:02.546 690:13:27.000
DNTUS26 129 8 3 24 24 0:00:00.046 0:00:00.000 690:13:26.359
LCFD 141 8 4 54 1104 0:04:26.000 0:01:19.671 690:13:22.703
LogWatNT 154 8 2 20 356 0:00:00.218 0:00:00.625 690:13:21.937
sqlservr 165 13 43 34891 98068 4:47:53.953 2:14:52.421 690:13:19.625
pstores 178 8 5 52 116 0:00:00.968 0:00:02.328 690:13:18.578
RCMDSVC 181 8 3 30 28 0:00:00.046 0:00:00.156 690:13:18.437
MSTask 184 8 6 86 316 0:00:03.437 0:00:02.765 690:13:18.343
snmp 190 8 3 40 200 0:00:00.109 0:00:00.250 690:13:15.640
WinVNC 197 8 4 66 1892 0:11:20.765 0:24:27.781 690:13:15.453
inetinfo 203 8 25 366 6108 0:20:02.078 0:22:15.859 690:13:14.718
sqlagent 243 8 9 138 2708 0:00:20.843 0:00:13.765 690:13:08.375
mdabrd 270 8 6 54 624 0:00:04.968 0:00:12.312 690:12:52.109
dm_ep_engin 312 8 1 55 2008 0:02:42.500 0:04:05.390 208:50:57.259
RCSERV 251 8 3 20 24 0:00:00.031 0:00:00.000 191:37:50.634
INOJOBSV 276 8 21 104 1704 0:00:34.703 0:00:17.546 174:35:54.571
GetBBS 262 8 4 36 488 0:00:00.015 0:00:00.015 174:35:53.696
ALERT 49 8 5 42 232 0:00:00.046 0:00:00.359 174:35:52.853
eventvwr 253 8 1 29 244 0:00:00.093 0:00:00.125 17:48:47.884
eventvwr 304 8 1 29 236 0:00:00.125 0:00:00.140 17:48:39.931
cidaemon 302 4 1 62 36 0:00:00.078 0:00:00.078 17:02:11.915

 

[SQLSister]

There are so many things that could cause a database to be slow, it's hard to know where to start.
Use profiler to find out what queries, stored procedures are being run and see if you can find the ones running slowly.
More than likely the problems is one of the below:
No indexes or wrong indexes for the queries being run
Statistics out of data and needing update
Use of cursors instead of set-based comands for processing data
Performing calculations and conversions on tables with millions of records
Poor database design
Database too normalized for query speed (If you are frequently joining more than 5 tables, you should probably consider denormalizing)
Hardware not adequate for the size of the database
A bad trigger which is updating all records instead of the ones needed or which has some kind of processing loop
Poorly written stored procedures and queries requiing the return of unnneeded data or not running the most efficient code.
Transaction logs and indexes running on the same hard drive as the database tables.
Performing system admin tasks that take up a lot of processing power during the main usage period of the database rather than at non peak hours.
Inefficient joins (joins on integer fields are much faster than joins on triple keys consisting of long alpha numeric fields)
Processes that cause locking problems or deadlocks
A virus taking up all your processing (Slammer virus will do this)

 

[pcawdron]

Thanks for the tip, I'm looking at Triggers and IIS ADO connections to make sure everythings disconnecting properly.

The thing that troubles me is why are the handles so high? How can you monitor something like this? Or, more appropriately, how can I kill inactive handles that are clogging the system?

Thanks for your help, it's very much appreciated

Cheers,
Peter