Don't know how to really describe this but I'm hoping its really nothing. We've just upgraded our server to SQL 2000. Its running on Win 2000 with Informatica running on the server. Each time we start a load for Informatica it spans multiple threads using the same spid id. I've never seen this before and I'm hoping its just me.
That sounds perfectly normal to me, unless I've completely misunderstood the concept of multi-threaded execution (could be!) The spid is unique for a single process, however that process can use any number of threads for execution.
Thanks for the reply. After watching the server for some time I discovered it was doing this even for TSQL Query commands so I was left under the impression this was normal behavior. I have yet to judge if it is faster but at least I'm not worried about something being wrong. I knew about the multi threading but had assumed it would be done under the covers and not noticable.
The most you can do with threads is limit the maximum number (under Server Properties, on the Processor tab). SQL Server will allocate threads as needed and multi-threaded processes will run faster. I think you'd be better off leaving the settings the way they are, lowering the max # of threads will lower performance.