SQL Server slower after 2005 upgrade

Michael Lutz141607

We have an extensive stress test for our OLTP application.that runs slower after upgrading to SQL Server 2005.

We have done the following:

- applied SP1 and SP2

- updated statistics (sp_updatestats)

- recompiled all stored procedures

Some behavior we notice in Perfmon over the course of the 1-hour test is:

- Average CPU Utilization is up from 22% to 32% and we're trying to figure out why

- Compilations per second are up from 200 to 250

- Recompilations per second are down from 5 to 1 (which is good).

- Memory utilization is identical.

- We see exagerated spikes in response times. The general performance

seems pretty good - but there are periodic spikes that hurt our averages.

- I examined perfmon for waits and nothing jumped out.

- A small handful of application workflows are much slower, while most are roughly identical.

I realize there could be a zillion different causes. But if anyone has experience upgrading a large OLTP solution which uses a mixture of dynamic SQL and stored procedures to SS2005 - and tuning the upgraded database - please offer any suggestions.

Our current plan includes (a) running the Best Practices analyzer which already uncovered a trace flag we may want to use since we're running on an 8-way, (b) rebuilding indexes, and (c) examine a SQL Trace this time around too since it appears the slowness is isolated to certain application workflows.

Please offer any suggestions that have worked for your team.

Thanks so much!

Mike

PS I will post all the things we end up doing in the end once we get performance equal or better - hopefully the latter!!!

ggciubuc


Did you read SQL Server 2005 Upgrade Technical Reference Guide .

Arnie Rowland

You've covered the obvious, and collected great baseline data.

One of the reasons for performance differences is that with SQL 2005, the query processor has been rewritten. Some resulting query plans may be significantly different than SQL 2000. Slower running queries 'may' need to be revised. Rebuilding the indexes 'may' help. (If you have Enterprise Edition, you can do 'on-line' index rebuilds.)

My testing has shown that sometimes dynamic SQL causes an increase in compilations, slowing performance.

Is the hardware exactly the same (New installation or Upgrade)

Are you using

• 32bit SQL and 32bit Win2003
• 64bit SQL and 64bit Win2003
• 32bit SQL and 64bit Win2003

Michael Lutz

Hi Arnie. Thanks so much for your response. It is helpful to know we covered the basics.

Yes our hardware is identical. We are using Mercury LoadRunner to create the load. Our Web, App, and DB servers are the same physical machines for the tests. We have SS2005 installed as a different instance on the DB server and we shut down the instance not being used for a given test. The physical layout of the database is also identical.

I'm hoping you're on the right track about the increased compilations & compilation time. I've been doing research on the web - and have found numerous listings on user forums where CPU utilization has increased and throughput has decreased after an upgrade. The solution that seems to work (especially when the application uses dynamic SQL - which our applications does in addition to stored procedures) is setting the DB to forced parameterization. It looks like Microsoft put a lot of work into auto-parameterizing dynamic SQL in this release so that plans can be more aggressively reused. BUT - after a DB upgrade the setting is not set for forced parameterization (and there are reasons for this). The setting continues with setting "SIMPLE" which doesn't provide the majority of benefits.

We are going to rerun on Monday with parameterization set to forced. I'll post a summary of how that goes. We also analyzed a trace of our test and found a dynamic SQL statement and a stored procedure that are taking much longer to execute and using way more CPU time than in SQL 2000. And GUESS WHAT ! - the slow stored procedure builds SQL and dynamically executes it!!! This supports our theory.

So we'll rerun Monday with parametarization set to forced and I'll post results.

If you have additional ideas please share.

Thanks!

Mike

Pranil

We had a massive slowdown for a web page query on one of our databases after the upgrade. A SP which used to take less than a second slowed down to 5 minutes and more. There was a lot of dynamic sql involved, and functions were used extensively. After doing a lot of tests and traces, we narrowed down the cause of the issue to the complied query plans of certain functions. The wrong query plans were being utilized and the performance would get back to lightening speed as soon as we cleared the procedure cache, and then start slowing down again. It was a complex function that would require, depending on the parameters, different tables or indexes would be utilized. The trace showed that there were cache hits for the function, but running the exactly same function under SQL 2000 (in our test environment) showed that the query plans for the functions were never retreived from the cache. This suggests there are differences between 2000 and 2005 when it comes to compiling and caching plans for functions, but I was not able to find any documentation on this behaviour change.

Madhu K Nair

refer this it may help u

http://www.sql-server-performance.com/forum/topic.asp TOPIC_ID=19622

Madhu

Michael Lutz

Just a quick update ...

We reran our tests with parameterization set to FORCED and the performance didn't improve.

Next step is to explicitly parameterize the offending stored procedures (that execute dynamic SQL with sp_executesql without parametezing the parameters) and rerun the tests ... I will post findings.

GlennAlanBerry

You never said whether you are running 32-bit or 64-bit. If you are running 64-bit, it is extremely important that you give the "Lock Pages in Memory" right to the SQL Server service account to allow SQL to control paging instead of the OS.

For your specific workload for your tests, you should try running these DMV queries to get an idea what is going on (rather than just guessing).

-- Isolate top waits

WITH Waits AS

(

SELECT

wait_type,

wait_time_ms / 1000. AS wait_time_s,

100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,

ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn

FROM sys.dm_os_wait_stats

WHERE wait_type NOT LIKE '%SLEEP%'

-- filter out additional irrelevant waits

)

SELECT

W1.wait_type,

CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,

CAST(W1.pct AS DECIMAL(12, 2)) AS pct,

CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct

FROM Waits AS W1

INNER JOIN Waits AS W2

ON W2.rn <= W1.rn

GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

HAVING SUM(W2.pct) - W1.pct < 90 -- percentage threshold

ORDER BY W1.rn;

-- Missing Indexes

SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage,

migs.*, mid.*

FROM sys.dm_db_missing_index_group_stats AS migs

INNER JOIN sys.dm_db_missing_index_groups AS mig

ON migs.group_handle = mig.index_group_handle

INNER JOIN sys.dm_db_missing_index_details AS mid

ON mig.index_handle = mid.index_handle

--WHERE statement = '[ngservices].[dbo].[UserFeedUnreadCountRollup]' -- Specify one table

ORDER BY index_advantage DESC;

-- Get Top 50 executed SP's ordered by calls/sec

SELECT TOP 50 qt.text AS 'SP Name', qs.execution_count AS 'Execution Count',

qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime',

qs.total_worker_time AS 'TotalWorkerTime',

qs.total_elapsed_time/qs.execution_count AS 'AvgElapsedTime',

qs.max_logical_reads, qs.max_logical_writes, qs.total_physical_reads, qs.creation_time,

DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Age in Cache',

--qs.execution_count/DATEDIFF(Minute, qs.creation_time, GetDate()) AS 'Calls/Minute',

qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) AS 'Calls/Second'

, qt.dbid --, qs.*

FROM sys.dm_exec_query_stats AS qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt

--WHERE qt.dbid = 5 -- Filter by database

ORDER BY qs.execution_count/DATEDIFF(Second, qs.creation_time, GetDate()) DESC

Michael Lutz

Unfortunately the instance was restarted so we've lost this information. We can rerun to get them ...

But after analyzing a SQL Trace there are specific procedures that have bubbled up to the top in both Duration and CPU Utilization. These stored procs build and execute SQL dynamically with sp_executesql.

I run these same calls manually - when I run them for the first time the CPU utilization is roughly 3/4 of a second - and equals the total Duration. So the runtime of the call is spent in CPU time. I see a proc cach miss in Profiler. When I run with the exact same parameters - CPU time drops to 0 and the procedure runs in 0 ms. Reads are negligible. I see a proc cache hit in Profiler.

Then I change one parameter and rerun. CPU time goes back up to 3/4 of a scond and Duration is roughly 3/4 of a second. Reads are again negligible (just a couple pages or so). Proc cache miss in Profiler. Then I run again - and it's back down to zero. Proc cache hit in Profiler.

So in Profiler - proc cache misses correlate to the proc running for 3/4 of a second and this is all CPU time. Proc cache hits correlate to the calls that run in 0 ms when CPU utilization is also 0.

Is this enough to prove the point Please let me know your thoughts.

rafiq_hay

Hi

The missing index query - im not sure how to read the output.

what key fields in this query should i look out for and what does this actually tell me