SQL Server Performance Forum – Threads Archive
OS and SQL Server upgrade maxes out processor!
We upgraded a database server from Windows 2003 Standard with SQL Server 2000 to Windows 2003 Enterprise with SQL Server 2005. We also went from the database being on a single disk (no raid) to adding a mirrored disk. The processor used to stay at under 20% on average and now is near 80% on average. Everything else is the same as far as we can figure. The disks are hardly even being accessed and there is no paging according to Performance monitor, so we can’t understand why the processor has such a huge load on it. When we restart SQL Server the processor usage is low until the memory usage gets up to the 1.5GB we have allocated to SQL Server (2GB total in server). That’s when the processor jumps up to 80% usage. Any idea why the processor would be working so hard now? Or what how we can test it to narrow down the problem? Louder+Harder+Faster = Betteruse profiler to find out what your top cpu consuming queries are
its probably just a bad execution plan for 1 or 2
We ran profiler and some of the queries are not the most efficient, but they are not anything that should max out the processor. The entire database is only 500MB and they poorest queries are reading 40k records and have a duration of 1000. We can clean them up, but I don’t think that is the whole problem. The hard drives are hardly even showing any usage at all. The database is not hit that heavily and the real stumper is that nothing has changed with the database except moving it to different OS and SQL Server versions. The database ran fine before moving it with the same indexes and queries it has now. It appears something is just plain wrong by the amount the processor usage went up (15-20% to 80%), but we don’t know how to narrow down the problem. When the database was moved, it was just dettached and re-attached. Should it have been moved in some other way that may optimize it for SQL Server 2005 maybe? Do Windows 2003 EE and SQL Server 2005 need a lot more processor/memory to function that Win 2003 SE and SQL Server 2000? Louder+Harder+Faster = Better
the key difference is SQL 2005, which uses different formulas in the optimization process which could cause this.
is the very large majority of the server cpu in the sql server process?
or is it in some other process? do you have a profiler trace before moving to this server?
other wise you do not know what the query was before you moved, hence you cannot make the assessment that a query is not the issue,
especially since you need to aggregate the queries to find the distribution of load
This server was just freshly loaded with only the OS and SQL Server on it. SQL Server is the only load on it. We do not have a trace from before the move. We only know that it wasn’t maxed out like it is now. It was even running additional databases before the move that we haven’t even put on it yet, so there "should" be less of a load now than there was before. Since the server only has 2GB of memory and SQL Server grabs as much as we allocate to it within 30 seconds of restarting it… could it be that the OS and Sql 2005 use more memory, leaving less for the queries to use? If this is possible, how would you suggest we verify this? We have more memory on order but it won’t be for for a few more days. Louder+Harder+Faster = Better
i would stop looking for magic registry settings
and do a proper profiler analysis, search pssdiag on the microsoft site and learn how to aggregate profiler traces
just remembered, SQL 2005 has capabilities so you do not have to hard work. on of the DMV views shows query plans, tot cpu, # times called etc.
just look there
Joe, It appears you were right about queries just needing to be optimized. Some of the same queries that ran fine in SQL 2000 were unbelieveable processor killers on SQL 2005. It seems the culprits are "AND" and "OR" statements. We found several or them used in stored procedures (written by someone else a long time before me, of course). Re-writing them in a more efficient manner mad a huge difference. As near as we can figure… SQL 2000 short circuits "AND" statements, causing the processing to skip the rest of the comparisons if the first statement is false, where SQL 2005 executes ALL the statements. We haven’t fully tested this theory, but this would explain why the same stements were not a problem before the upgrade but became processor hogs after. Thanks again Joe! Louder+Harder+Faster = Better
if some one could look into this.
many people use the following approach for search queries with multiple optional parameters: SELECT xx
FROM xx
WHERE ((@1 IS NULL) OR col1 = @1)
AND ((@2 IS NULL) OR col2 = @2) while this makes for compact code, it must be incredibily difficult for the optimizer to understand what you really meant,
so i made the request to MS a couple of years ago to recognize such code segements and handle it appropriately, with the argument that there were enough clueless SQL code writers out there doing this, even though this problem could have been avoid with intelligent coding i figured maybe MS would put this into 2005, and too bad for 2000,
then i found some of the later 2000 builds handling bad code like this, and 2005 not
]]>