I'm junior DBA ,have table with 11 million values ,one of the indexes is like :

CREATE NONCLUSTERED INDEX [ix_mytbl_mycolumn] ON [dbo].[myTbl] 
    [my_id] DESC

enter image description here

For rebuild this index need only

ALTER INDEX [ix_mytbl_mycolumn] ON [dbo].[myTbl] 

or with big tables need to prepare something.And also one question in this table I have some columns with asc index and some with desc , can be any problems after rebuild all indexes in this table? (ex.: slow select/insert). Is good idea to rebuild this index?

SQL Server Version : Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64>

  • 1
    I'd recommend taking a look at Ola Hallengren's scripts on Index Maintenance for what you should/should not rebuild ola.hallengren.com/… – Mark Sinkinson Dec 2 '14 at 14:22
  • It's important to review your sql-server version, and whether or not online rebuilding is possible. As Mark said you should look into implementing Ola's scripts to automate your index maintenance. – Reaces Dec 2 '14 at 14:35
  • @Reaces version is Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> – GeoVIP Dec 2 '14 at 14:36
  • 1
    @GeoVIP You won't be able to rebuild the indexes online with your version: msdn.microsoft.com/en-us/library/cc645993.aspx . You might want to look into reorganizing them as a first step. Again, Ola's scripts can handle this for you. – Reaces Dec 2 '14 at 14:38
  • 1
    @GeoVIP What you want to ask yourself first is... Why am I looking to rebuild my index. If it's just because you need something to do and you would like to implement a maintenance plan. Then just do what's safest, reorganize during a maintenance window, and only reorganize high fragmentation indexes. If you're trying to fix a performance problem... Make sure index fragmentation is the cause. 47% fragmentation really isn't that worrying yet. – Reaces Dec 2 '14 at 15:03

First thing you should do is Apply SQL Server 2008 R2 SP3 ASAP. This is because SQL Server 2008 R2 RTM version is not supported at all by Microsoft. There are lots of fixes which have been included in this Service pack release and that would surely benefit SQL Server query performance.

You asked, So rebuild all indexes is good idea ?

NO IT IS NOT. One should only rebuild index which is fragmented above certain value. Probably widely used value is, if index fragmentation is >30 you should go for rebuild and it is in between 10 and 30 you should go for reorganize. Please note you have standard edition so index rebuild would not be online. If you rebuild index blindly it would cause more downtime and produce more logs and thus more overhead on system. If you have narrow maintenance window you need to be highly selective with index rebuild and all this can be taken care if you go for Ola Hallengren Index rebuild solution

Other thing to note is if page_count for index, page_count can be seen from be seen from sys.dm_db_index_physical_stats , is less than 1000 you don't need to rebuild such indexes. The reason is since page count is less pages allocated to index would be from mixed extent and these mixed pages can be lying anywhere and hence producing Logical fragmentation even after rebuild. But rest assured such small page count indexes would not affect query performance at all. You can read more about why such indexes still remain fragmented even after rebuil here

There are various options you can choose with index rebuild you can see more details here. Lot depends on your environment and configuration

You can read more about query plans from This Article. You should focus more on actual query plan than estimated query plan. Actual would tell you what exactly SQL Server is doing and what resources it is using.

You can also try SQL Sentry Plan explorer tool to get better insight into SQL Server execution plan

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.