Environment related SQL 2008 R2 performance issue.

Question

Environment related SQL 2008 R2 performance issue.

liuwilli

SSC Enthusiast

Points: 175
More actions
May 5, 2011 at 10:58 am

#250386

Hi,
We have a SQL server 2008 R2 performance issue.
A simple select query running much slower in our production than in our DEV environment.
Here are some basic info:
Prod environment is much powerful than DEV. Perf monitoring didn't find any resources bottleneck.
Both prod and Dev are in 64 bit Windows 2008 R2 enterprize edition - server setting suppose to be the same.
Database are exactly same in the 2 environments. All statistics are updated and indexes are rebuilt. no block issue(single user while testing).
CPU times are almost the same (about 27 second) but waiting time in prod is 12 seconds longer than Dev. The waiting type (10 out of 12 seconds) in prod for the session is SLEEP_TASK.
We tested with no antivirus installed - same result.
Any idea what environment setting might caused this delay in prod?
Thanks in advance,
Will

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Check out the following:

http://www.microsoft.com/events/podcasts/default.aspx?seriesID=Series-11bc7f7b-77a6-4e18-ac58-6c9cfbe102aa.xml&pageId=x6234

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 2

I'd look to see if there are blocks of some sort going on in production, but other than that, I'd want to see more data gathered. You said the servers are supposed to have the same settings, do they? Exact same execution plans? Stuff like that.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

liuwilli SSC Enthusiast Points: 175 More actions · Answer 3

Thanks for reply.

There is no block process in prod.

SQL server setting are the same. Windows might be little bit different.

Execution plans are similar. The database in DEV is a copy from production.

Thanks,

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

What steps were taken in the development environment to test the performance?

When did the problem start?

Is the performance issue consistent or intermittent?

Are the disk fragmented?

Is the Database fragmented?

Do you have routine bulk loads and are you rebuilding the indexes and updating statistics

Do you have a high volume of users and or Transactions?

How you run profiler to check for poor performing queries.

What system tools have you ran in addition the performance indicators that you mentioned that you checked?

What steps are you taking to attempt in resolving this problem?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

liuwilli SSC Enthusiast Points: 175 More actions · Answer 5

The issue is steady. test database is a copy and no new data input and no other user can login. Same performance even only single user in prod server. And NO resource contention/bottleneck. It is SAN disks.

As my title indicated - the issue is environment related. We need to think beyond the database itself.

I forgot to mention two things - Prod is boot from SAN, DEv is boot from local disk, and prod Db server is hardened.

Does anyone has experiences with performance issue of server boot from SAN?

Thanks,

Will

Oliiii SSCertifiable Points: 5328 More actions · Answer 6

First of all if there was no bottleneck the speed would be unlimited and result instant, there is always a bottleneck, you just have to find it. It might be something else than the SQL Server like the application server, the network or the SAN perf.

You should compare perfmon counter between the 2 environments, check if the CPU usage is similar, the memory usage and the disk latency.

What kind of query are you running?

A single select, a stored proc or a more complex series of queries?

Does the DB have the same disk layout (data and log on different drive, tempdb on it's own drive, etc...)?

Does both server have lock pages in memory?

Do you have any errors or warning in the SQL errorlog (like io completition taking more than 15sec)?

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 7

SELECT ST.TEXT,SP.* FROM SYS.SYSPROCESSES SP

CROSS APPLY SYS.DM_EXEC_SQL_TEXT(SP.SQL_HANDLE) ST

WHERE SPID > 55

ORDER BY SP.CPU DESC

this query provide you information about the bottleneck

My Blog
www.aureus-salah.com

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

liuwilli,

You stated "Database are exactly same in the 2 environments".

Is that singular or plural?

Do you have any other Databases on the PROD Server that you are having problems with?

Regards.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/