SQL Server 2008 R2 Performance Issue


I need help with the re-indexing of tables. Due to my lack of knowledge of the subject I can not know that may be affecting response times

When I run the following query to the re-indexing of tables:

SELECT table_name
WHERE table_type = 'BASE TABLE'
 and table_name not in ('USER', 'Application')
- Like 'CR%' or table_name like 'MO%'
OPEN MyCursor
FROM INTO @ MyTable MyCursor
PRINT 'Reindex Table:' + @ MyTable
DBCC DBREINDEX (@ MyTable,'', 80)
FROM INTO @ MyTable MyCursor
CLOSE MyCursor
EXEC sp_updatestats

Response times on my old server are 28 to 30 minutes.

The characteristics of this are:

DL 180 G6 Intel Xeon Processor 2.27 8 Core 2 each. Enteprise W2K8 SP2 OS Spanish.
64 GB of memory.
Internal 3.5 Inch Disk (RAID 5):

a. Raid 5-3 disks of 146 GB
b. Raid 5-3 disks of 300 GB

But on the new server response time is 36 to 38 minutes.

The characteristics of this are:

AMD Opteron 6276 2.30GHz 16-core of each. OS W2K8 SP2 Spanish Enteprise
64 GB of memory
10HDD HP Eva 6000 with SAS 2.5 300GB 15K 6G with Raid 5 transfer.

The configuration of SQL Server is replicated in the same way, the partition configuration and level of updates.

There any settings I this forgetting?

Would greatly appreciate your help.

JnavarroMcIT ConsultantAsked:
Who is Participating?
JnavarroMcConnect With a Mentor IT ConsultantAuthor Commented:
After so long I finally find that required settings for SQL Server.


After changing the mode of operation of BIOS HP Power Profile ("Balance Power and Performance" to "Maximum Performance")

The process time was being affected under 6 hours and 30 minutes to 3 hours and 30 minutes.

Responded even better than the production server, which I did in four hours.

In reviewing the percentage of power consumption and fans will note That had risen by 80%.

But that is another issue and I will be analyzing.

Many Thanks for your help.
What is the min and max memory setting of the server? I remember DL 180 G6 Intel Xeon Processor 2.27 having 64 bit addressing. Point me if I am wrong.
What about max degree of parallislism value in the server configuration? What about tempdb and log files configuration in terms of their location?
Last but not least, what all is running during reindexing job?
JnavarroMcIT ConsultantAuthor Commented:
* The memory value is set by default.

* I have verified that SQL Server settings on both the production server and the new server are in the same way.

* The maximum amount of parallelism is any special parameters that I have set up in SQL Server?

* The Data and Logs discs are housed in different storages. But tempdb is hosted on the disk where the data, such as on the old server.

* The query shown is running and that the customer is stated as the problem.

* With some of these replies You could project by side where failure would be happening? By the SQL Server or Hardware?

If you have any more questions that may help you please advise me to raise it. I have many advanced knowledge of SQL Server but I need to determine where the delay is occurring reindexed.

I appreciate your help, dear.
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Matt BowlerDB team leadCommented:
I'm not entirely convinced you have a problem here? I assume that the reindexing is still succeeding but taking longer?

How long have you been running on the new hardware?

Has anything else changed since migrating to the new hardware? Increased workloads, SQL Server upgrade or service pack?

I have heard of issues with HP chips running in power saving mode which could slow down dbcc's - have a look at a utility like CPU-Z to verify CPU speed.

Lastly, be aware the dbcc dbreindex has been deprecated and should be replaced with ALTER INDEX ... REBUILD
Scott PletcherSenior DBACommented:
If you want update all stats, you should do it before rebuilding indexes.

DBREINDEX will update statistics based on ALL rows, which is the most accurate.

Then updatestats will come back in and do a limited sampling, ~10-20%, overwriting the 100% stats that were just stored!
JnavarroMcIT ConsultantAuthor Commented:
My dear MattSQL I have no problems when reindexing.

The discomfort of the customer is on the issue of delay times.

In the old server response times are 28 to 30 minutes ((2 processors with 8 core each). But on the new server (2 processors with 16 core each) the time it takes this process lasts until 40 to 44 minutes.

When installing SP2 SQL Server 2008 R2, this process time low of 32-34 minutes.

But the question to me because the customer is supposed to be a more powerful server response times are slower.

I'll be trying as you show me the response times with ALTER INDEX ... REBUILD and I'll be reporting.

Thanks for the help.
Matt BowlerDB team leadCommented:
Have you tried CPU-Z yet?
JnavarroMcIT ConsultantAuthor Commented:
Not yet. I have to schedule a server maintenance

I'll be sending the report within hours.

Thank you.
Scott PletcherSenior DBACommented:
Almost certainly has to do with disk I/O, as that is the main time effect on rebuilds anyway.

Those 15K drives should be fast, so something must not be set up correctly, or there's an I/O error occuring, or something else unexpected with the I/O (bad NIC, throttled SAN pipe, etc.).
JnavarroMcIT ConsultantAuthor Commented:
HP have been consulted. But they need to refer to evidence that the problem is the disk (HP Eva Storage 6000).
Scott PletcherSenior DBACommented:
Likely not the disk itself.  Could be the SAN, the path(s) configuration, a NIC(s), etc..

I'm not a storage expert so I can't give any details on that.
JnavarroMcIT ConsultantAuthor Commented:
If you can be this configuration by HP. No way to prove that this is affecting the performance of re-indexing?
Matt BowlerDB team leadCommented:
If the IO subsytem is slower then this will definately result in slower re-indexing tasks.

You can monitor some of the IO perfmon counters during your re-indexing. Avg. Disk sec/Read, Avg. Disk sec/Write would be good to look at - but if you don't have a baseline of these from your previous server then this doesn't really prove much...
Scott PletcherSenior DBACommented:
You can look at the avg I/O response time in SQL.  If it's <= 30ms (preferably even 20, with 15K drives), then that's acceptable and you have another issue.  If it's even less, that's good.  

If it's (much) higher, that's bad, and something is wrong.  For example, if it's 100ms, then you clearly have a genuine I/O issue.
JnavarroMcIT ConsultantAuthor Commented:
Dear MattSQL,

You know how to set each processor is working with only 8 cores. I was talking to a trainer of SQL Server on my problem and told me that a processor that works with more than 8 cores can make this SQL server performance problems.
JnavarroMcConnect With a Mentor IT ConsultantAuthor Commented:
Establishments and parallelism in 8 times improved, but not enough.

Then I opened a case with Microsoft and give them the description.
JnavarroMcIT ConsultantAuthor Commented:

You have 2 environments with SQL Server 2008 R2: development and production. Your instance with SQL Server 2008 R2 (10.50.1617) x64 SER099PBAN01 hosted on a server is productive and has good performance, the process of re-indexing of the database Probank_paralelo_PF takes about 29 minutes. Your instance with SQL Server 2008 R2 (10.50.4000) x64 PROFINMATCL01 is used for development, the same process of re-indexing of the database Probank_paralelo_PF takes 40 minutes. It also supports daily closing process that takes four hours production while developing takes 8 hours. MAXDOP parameter used in both instances is 0, you changed it to 8 in development and improved performance but not enough from their point of view. In general the process regardless running between both environments is always slower development.


The hardware and software configuration differs between the two environments. The server model is different as well as the manufacturer of CPUs, IO subsystem, BIOS version, number of processors and even the build number of SQL Server 2008 R2 is different.


In the development environment PSSDiag run the tool to assess the bottlenecks that cause delays. Based on the analysis of the information received was sent the following diagnosis:
1. Importantly, the two teams have different configurations and physical characteristics. Both servers are the same manufacturer HP but different models, production: Proliant DL180 G6, ProLiant BL465c Gen8 development. There are also differences in the processing, production: Xeon 2267Mhz, Development: AMD 2300 MHz. BIOS versions are also distinct, production: 2.6, development: 2.7. Considering these differences is important to note that performance on both teams will be different.

2. In implementing the reindexing process bottlenecks report states:


The exchange is expected to contribute to the generation of hope is the CKPACKET. Such waits are related to the execution of queries in parallel and indicate that the SPID is waiting for the other processes running in parallel finish or start. During execution of the rendexación, which lasted 29 minutes, waiting CXPACKET are generated by the order of 142.4 hours accumulated by internal processes or the equivalent to 8545997 milliseconds as shown in the table above. On average, for every second of the process execution reindezación experienced a total cumulative waiting 4617 seconds.

3. In implementing the daily closing process bottlenecks report states:


The exchange is expected to contribute to the generation of hope is the CKPACKET. During the execution of daily closing, which lasted from 11:30:14 AM to 6:14:16 PM, waits generated by CXPACKET are around 632.37 hours accumulated by all internal processes or the equivalent of 37,942,293 milliseconds as shows the table above. On average, for every second of execution of the closing process daily experience a cumulative total waits 1849 seconds. Indeed the process so expensive observed a little pressure on the CPU.

4.  The symptom I'm seeing in your instance, derived from the information we captured in both executions, is very timely in environments where there are a lot of logical processors. In this case SQL Server runs on a large number of parallel operations but loses much time waiting for these threads end, initiate and coordinate.

5. To try to mitigate the problem we will follow the following plan of action:
a) We will apply the following KB to rule out performance issues by the power plan configured on the computer:

Degraded overall performance on Windows Server 2008 R2
1. Click on Start and then Control Panel.

2. From the list of Displayed item under Control Panel click on Power Options, Which takes you to Select a power plan page. If you do not see Power Options, type the word 'power' in the Control Panel Search box and then select Choose a power plan.

3. By default, the option to change power plans is disabled. To enable this, click the Change settings link That are currently unavailable.

4. Choose the High Performance option

5. Close the Power Option window.

b) It is necessary to consider the recommendations to set the Max Degree of Parallelism:
General guidelines to use to configure the MAXDOP option
It is important to consider that on computers with more than 8 processors MAXDOP the maximum value is 8 but we have other considerations such as NUMA.

c) Execute the shutdown process again but now we will using different degrees of parallelism.
We four executions, using MAXDOP 6, MAXDOP 4 MAXDOP MAXDOP 2 and 1 respectively, and validate how the instance behaves with a parallel each lower.
In each run one PSSDiag capture and compress, do NOT need to rise to the workspace.
To change the MAXDOP use the following script:
sp_configure 'max degree of parallelism', 1
reconfigure with override

d) Take the estimated execution time in each run.

e) Once you have the results send me an email to tell you the following.

Once the tests made with different degrees of parallelism is concluded that the best performance was offered the MAXDOP = 8. Usually in such cases where there are differences between two environments having different configurations verify a number of points that could eventually help improve performance in the affected server, such as:

1. BIOS version, here is an updated development: 8/14/2012. Unless you can validate your server provider I think this version could be the latest.

2. Version of SQL Server, in this case you have developing the latest service pack released for the 2008 R2 which is 2.

3. Service pack level in the operating system for computers running Windows Server 2008. There are known issues with performance versions of Windows 2008 that are resolved by installing Service Pack 2. In your case the operating system has this update so we discard this possibility.

4. Power Plan. On many occasions the power plan set in the operating system is crucial to have good response times. For the development environment, we apply the following KB to change it to High Performance which is what provides the best performance:
Degraded overall performance on Windows Server 2008 R2

5. Configuration Parameters SQL Server. In both of these environments are approved but there is a difference with respect to the degree of parallelism.

6. PSSDiag tool in these cases helps us identify the main factors contributing to potential bottlenecks that generate hope in the process execution. In your case we identified a large number of waits CXPACKETS indicating a delay in the process because it must wait for the parallel threads begin or complete their execution. In these cases, based on the article "General guidelines to use to configure the MAXDOP option" (http://support.microsoft.com/kb/329204) carried out tests with different degrees of parallelism even eliminating it completely leaving work processes sequentially. From testing the MAXDOP = 8, which you had already configured above, is the person granting shorter execution times that are not necessarily equal to or better than that currently have in your production environment.

Both production and development environments are significantly different, have discrepancy in all the elements that contribute to the performance of an instance of SQL Server: CPU, IO system, engine build, manufacturer, etc..
Server Model: Proliant DL180 G6
CPU: Intel Xeon 2267Mhz
SMBIOS Version: 2.6 / HP O20
IO Subsystem: HP LOGICAL VOLUME SCSI Disk Device
SQL Build: 10.50.1617

Server Model: ProLiant BL465c Gen8
CPU: AMD Opteron 2300MHz
SMBIOS Version: 2.7 / HP A26
IO Subsystem: HP HSV340 Multi-Path Disk Device
SQL Build: 10.50.4000

Based on the above it is necessary to bear in mind that the performance between the two environments will be different, if the developing runtimes are higher and the choices we have to improve and exhausted at the level of SQL Server as there a flaw in the product as such. Several factors in the equation between the two environments are different and therefore so is the result. I understand that your concern is to have a development environment to a production like performance is however important to note that simply increasing the number of processors or memory, for instance, in a SQL Server we do not necessarily bring a benefit and this is much less proportional, ie if the process takes 10 hours with 4 processors in any way increasing to 8 processors this time reduced to 5 hours. The processing power increases should be made based on a thorough analysis of the generated SQL waits, user connections, etc. Have you note that the cost of synchronizing parallel processes is exponentially proportional to the amount of CPU used in the data processing.
Matt BowlerConnect With a Mentor DB team leadCommented:
Be interested to see the results of the power plan component of the above - I think that was one of my theories...
JnavarroMcIT ConsultantAuthor Commented:
I followed all the advice given, to no avail. I've almost given up. If anyone has any comments most welcome.

Thank you for your attention.
JnavarroMcIT ConsultantAuthor Commented:

I have 2 questions to solve ... after this I can settle this issue definitively.

If I can help I give points.

1. Since the development and production servers are different, even though technically, at the hardware level, the development is more potent Than That of production, we can not assume the same They Will Behave or better Because the models are different? , Could it have been faster or slower Depending on the type of equipment we had CHOSEN That?
2. The development server has twice the number of cores That the production and process synchronization issues of parallel processing is the higher cost is due to the Increased Presence of cores?, Does that make it take longer?
JnavarroMcIT ConsultantAuthor Commented:
There was a SQL level analysis, recommendations and steps are followed, but not get to solve the problem.

Then I look over the hardware level issue and to solve it I had to make a change in the BIOS the same server.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.