September 12, 2003 at 6:50 pm
My company recently went from SQL Server 7.0 to 2000. One of the things we have noticed since switching is that large queries will break into multiple sysprocesses for a single spid. For example, I will run a 100000 record query, and while it is running, query master..sysprocesses and it will show my spid with 10 rows performing the query. Is this due to a server setting, or is it a standard feature of SQL Server 2000? It has caused some problems in SPs that count sysprocess rows, so I was hoping there is some way to turn it off.
September 12, 2003 at 7:16 pm
I would suggest change your admin SP that counts sysprocesses rows, rather than looking to change the way SQL's working for you.
You'll probably upset the SQL Development Team if you try to turn off the functionality that they developed over 11,346 cokes and 4,638 pizzas.
Cheers,
- Mark
Cheers,
- Mark
September 12, 2003 at 8:55 pm
Yes, is standard in SQL2K and no way to turn off that I know of. Agree that changing your proc (look at ecid for the parallel threads).
Andy
September 15, 2003 at 1:32 am
Hello,
I may be wrong, but I think this behavior is due to Parallelism - more complex queries on large tables will be split into several processes running at the same time, which are composed together again before you get the result of query. Sometimes parallelism may adversely affect performance, because the cost for splitting and putting together not-so-complex queries is higher than the time saved by parallel execution - in which case it is recommended to set higher Minimum query plan threshold (Server Properties / Processor). You could even set it so high, that it wouldn't be used at all, but IMHO that wouldn't be wise. It helps a lot with those slow queries on huge tables (and also during maintenance jobs and maybe backups, although I'm not sure about that).
Well, I just peeped at the help topic about parallelism and it says "Although not recommended, users can change the default value of 5". The fact is that developer of the IS we are running with SQLS 2000 recommends to have this threshold set higher than default. We have tried several settings and finally settled at 25, which seems to work nicely.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply