May 21, 2002 at 9:10 am
We have a couple of complex processes that generate thousands of rows spread across four tables. The largest table has about one million rows. Each process is coded in a stored procedure, which is called by a job to allow for background processing. Each stored procedure wraps the table modifications in a transaction. We use an identity column as a surrogate primary key for each table.
I suspect there are ways that we could improve the efficiency of the stored procedures, but that's not the point of this posting. When SQL Server Agent runs one of these jobs, it pretty much consumes the server. Any queries submitted to the server may take 20 to 100 times longer than usual. Is this normal? Are there any settings that would put more resources into current requests at the expense of a job?
We are running SQL Server 2000 Standard on a Windows 2000 Advanced Server system with 1 GB RAM. The hardware is a bit more than a year old (fairly current). Would a second processor help?
Thanks.
Creator of SQLFacts, a free suite of tools for SQL Server database professionals.
May 21, 2002 at 9:34 am
quote:
I suspect there are ways that we could improve the efficiency of the stored procedures, but that's not the point of this posting. When SQL Server Agent runs one of these jobs, it pretty much consumes the server. Any queries submitted to the server may take 20 to 100 times longer than usual. Is this normal? Are there any settings that would put more resources into current requests at the expense of a job?
This is normal, first request in is processed and it will always impact further requests until done. There (at least to my knowledge and I have looked deeply) no way to control a queries resource usage. You could try an OPTION clause and use MAXDOP set to 1 to prevent parallel execution thus freeing up all other CPUs but that is the limit I know of. But as noted you only have one processor so this won't help.
Excerpt from BOL
quote:
max degree of parallelism OptionUse the max degree of parallelism option to limit the number of processors (a maximum of 32) to use in parallel plan execution. The default value is 0, which uses the actual number of available CPUs. Set max degree of parallelism to 1 to suppress parallel plan generation. Set the value to a number greater than 1 to restrict the maximum number of processors used by a single query execution. If a value greater than the number of available CPUs is specified, the actual number of available CPUs is used.
Note If the affinity mask option is not set to the default, it may restrict the number of CPUs available to Microsoft® SQL Server™ on a symmetric multiprocessor (SMP) systems.
Change max degree of parallelism rarely for servers running on an SMP computer. If your computer has only one processor, the max degree of parallelism value is ignored.
max degree of parallelism is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change max degree of parallelism only when show advanced options is set to 1. The setting takes effect immediately (without a server stop and restart).
In addition to queries, this option also controls the parallelism of DBCC CHECKTABLE, DBCC CHECKDB, and DBCC CHECKFILEGROUP. Parallel checking can be overridden by using trace flag 2528. For more information, see Trace Flags.
See "OPTION clause" in SQL BOL for more detail. You might also if the query is using tables that will be accessed by other queries try using locking HINT NOLOCK.
In addition to your question, more than 1 CPU does help a lot with SQL and adding more RAM may also show major bennifits. I would thou persue your statement about the query may be able to be optimized and try to get as streamlined as possible rather than throwing hardware at it. IMHO it is better to find a solution with the code than is with hiding the bugs with hardware. Once you have streamlined the code then definently bulk up the box.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - antares686 on 05/21/2002 09:38:57 AM
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply