Performance Issue

Question

Performance Issue

Sumon Basu

SSC Enthusiast

Points: 102
More actions
July 28, 2008 at 12:29 am

#188594

Hi All,
I have 2 SQL Servers, both the servers are in clustered environment. Server 1 is the Production Server, and Server 2 is the reporting server. Transactional Replication is set in an interval of 15 mins.
When I am issuing a SQL Statement against my production server it takes 30 Secs to executes, but while I execute the same statement again my replicated database, it takes more than 30 mins to execute. at the activity monitor, I noticed a very high counter for tempdb, also there are 2 instance of tempdb against the same user.
Please help.

Viewing 5 posts - 1 through 5 (of 5 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

So you have 4 servers? 2 of them are clustered for the production environment, and two are clustered for the reporting environment?

I suppose that does not really matter. You have transactional replication configured, so the databases must be a little different. It is no surprise you would get different execution plans. Do you have the appropriate indexes replicated? Do you have a clustered index on the table in the reporting database? Have your indexes been heavily fragmented?

Sumon Basu SSC Enthusiast Points: 102 More actions · Answer 2

Yes I have 4 Servers. The query execution plans are same for both the environments. The number of articles are huge in this case.

I can't see any other reason for the slow execution of the query? Is there any other option to fine tune the query? Is the lock is an issue??

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 3

Getting the same execution plan and vastly different performance is unusual.

Assuming the hardware in each environment is similar:

- Make sure statistics are updated on the reporting database.

- Verify your indexes are not badly fragmented (transactional replication can do this)

- Make sure you do not have a bad query plan cached

Sumon Basu SSC Enthusiast Points: 102 More actions · Answer 4

I have tested the DB by taking the backup of the DB in my local machine, which is having the capacity of 1 physical processor, and 1 GB of RAM. Here I used SQL Server developer version, it takes only 11 mins (approx) to execute the query. Whereas in my Production server (12 CPU, 6 GB RAM) it is taking 30 Mins. DB Size is only around 7 GB.