Home » SQL Server

SQL Server 2008 Indexes

Hi All,

I have upgraded from SQL 2000 to SQL 2008 and I have some suggested indexes from dev guys I should apply on a DB to improve the application perfromance but i don't know the right type for each one (Non-cluster,cluster) is there anyway i can do to apply them based on best practice or tool like DTA

thanks in advance


11 Answers Found


Answer 1

There can be only one clustered index on a table and many (999 I think) non clustered indexes in SQL Server 2008.


It is hard for anyone to recommend what type of index should be created without understanding the usage patterns.


I normally look at the missing indexes DMV to see their impact first; I wouldn’t create all indexes suggested by this DMV or DTA. Sometimes they do go overboard in recommending few indexes, which are NOT optimal. http://sqlfool.com/2009/03/find-missing-indexes/




I am recommending three good resources where you can get good understanding of indexes which might help you in the right direction.






SQL Server 2008 Internals: Chapter 6 Indexes: Internals and Management


And From BOL:




Answer 2

Hi Sankar,

thanks you for the useful resources, but I have a question ....I have used that script mentioned to determin missing indexes in my databse hosted in SQL Server 2008 and i have got 0 recordes what does it mean ? i have no missing indexs "I doubt" ..or I have to run some activities on the DB while running that script .... FYI this enviroment still offline and we are going to have it online when reach the acceptable level of the performance..

for your info I am facing a peromance issue since upraded to 2008 and can't go online and still using 2000 :)..

what do you think ?



Answer 3

The Missing Indexes DMV relies on the usage of SQL Server to be able to give you suggestions. So, when users run queries and SQL Server finds that a particular Index would be beneficial for a query, that information is saved in the DMV. This continues till the SQL Server is recycled.

So, if you database is offline, or no one has used the database since the last SQL Server recycle, you will not get any recommendations.

If you want the information, run your production load on the database for a day, and then run the missing indexes query, it will give you the recommendations. If you still get 0 rows, then probably you do not have any missing index. :-)



Answer 4


have perfromed the production load on the test enviroment and have checked the missing indexes after that I have got arount 5 STATISTIC and one Non-Cluster Indexes and have created but still have the same perfromance very slow response..... although i have monitor the SQL Server resources utlization it's seems normal but the processes a lot of them with State SUSPEND !!!!!

any clue........thanks in advance



Answer 5

From what I understand, you have upgraded from SQL Server 2000 to SQL Server 2008 on a test server and hoping the performance will improve dramatically?


I am afraid, it doesn’t happen like that automatically.


Suhas asked you to run the production load at-least a day but you have replied in less than 4 hours and am NOT sure how much time did you spend actually running the workload.


Coming to the processes being in SUSPEND state, after the upgrade to SQL Server 2008, have you performed any post upgrade tasks like rebuilding the indexes or updating the statistics of all tables/columns?


If you haven’t, then download this MSFT paper and glance over for the steps.




Also, is your test server (including IO subsytem) comparable to the production server? In most companies those won’t be anywhere near comparable. Then how are you measuring the performance?


Also look at WAIT STATS on test server to see what are the bottlenecks?




Answer 6

Have you updated Statistics after migration?

Answer 7

There can be only one clustered index on a table and many (999 I think) non clustered indexes in SQL Server 2008.


There can be only one clustered index and 249 non-clustered indexes.


0 = HEAP, 1 = Clustered Index, 2-250 = Non-Clustered Index, and others for LOBs, XML and so on...

First of all, what's the size of the table? If less then 200 datapages you probably only need a clustered index on the relative unique columnset that represent your relative unique WHERE-clauses, and one unique non-clustered index to support the Primary Key.

If you have workloads, use them with Database Engine Tuning Advisor! You'll probably get the "most right answer" with that tool based on the intel you've provided us with.




Answer 8



As the title of this thread indicates, this question is relevant to SQL Server 2008 and in you can create 999 non clustered indexes. 249 is only upto SQL Server 2005.


Check out the below links.


Maximum Capacity Specifications for SQL Server

Maximum Capacity Specifications for SQL Server 2005



Answer 9

Sankar, Yes! You're completely right. I'm sorry for that. Next time I should read!


Back to initial question...

If you have a workload just use Database Engine Tuning Advisor(DETA). If you have scripts from the devguys for creating indexes you either trust them, or you use DETA to test workloads...



Answer 10

Hi Sankar,

Sorry for late response, but I was working on your suggesting and it's have WORKED LIKE MAGIC :)....I have run the workload trace for 4h and user start run the stress test and then used the DTA to analyze the workload and finally apply the DTA Recommendations.....

One more thing Jthis server (test environment SQL2k8x64 cluster) will be consolidated server (production) for all database servers we have around 6 servers hosted 50 DBs, this test performed only on one database let's say it's the most important one but when host all database i guess the picture will change

What do you think ...do i need to perform the same excarcis for each database OR run the workload trace for all databases at the same time and run the production load and then analyze the workload...?what do you think…

 Thanks a lot and appreciate your great support




Answer 11

Any progress?


 Hi, I am using VWD 2008 Express and SQL Server 2008 Express. I want to enable full text index in some of my table. How do I do that? I only have  SQL Server Management Studio Express 2008. As far as I know, I cannot use SQL Server Management Studio Express 2008 to update the full text catalog according to : http://msdn.microsoft.com/en-us/library/ms365247.aspx

My tables are also not enable Full Text Search by default. 


 Any help would be very much appreciated!

 Edit 1: Someone had a similar problem to mine on this link: http://forums.asp.net/t/1169961.aspx
I installed the Sql Server 2008 Express with Advance Services and made sure that Full Text functionality is installed. However I don't get a full text menu in SQL Server Management Studio. 

 Edit 2: I uninstall and reinstall SQL Server 2008 Express with Advance Services but still couldn't be able to enable full text index.

 Edit 3: After see this, I guess I have to do it manually then:  http://forums.microsoft.com/MSDN/ShowPost.aspx?siteid=1&PostID=3912985

"Microsoft® SQL Server® 2008 Express with Advanced Services provides full text search support in engine.
As of now the SSMS support for Full Text is not present in express edition. We are tracking this issue internally.
Please watch the updates and patches for SQL Server to see development regarding this."



The following SPs return no results against an active database.  They do not throw any exceptions, just return no rows: sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups, sys.dm_db_missing_index_details

Has anyone been able to full-text index PDF documents on 64-bit SQL Server 2008 using the Adobe 64-bit PDF iFilter?

We're getting the following error in the log for any row that has a PDF document:
Warning: No appropriate filter was found during full-text index population for table or indexed view '[TestDB1].[dbo].[Documents]' (table or indexed view ID '277576027', database ID '10'), full-text key value 'D448F8EC-EB6E-4658-B9D4-CE44E7DE6552'. Some columns of the row were not indexed.

OS: 64-bit Windows 2008 Server R2
SQL Server: 64-bit 2008 Developer Edition SP1 + Cumulative Update 3 (Build 10.0.2723)
PDF iFilter: Adobe PDF iFilter 9 for 64-bit platforms http://www.adobe.com/support/downloads/detail.jsp?ftpID=4025

Note that this does work correctly on 32-bit platforms (e.g. Windows XP Pro) with SQL Server 2008 at the same maintenance level (Build 10.0.2723) using the iFilter provided with the Adobe Reader 9.1. So the problem is purely related to 64-bit.

The 64-bit PDF iFilter is properly installed and shows properly in sys.fulltext_document_types. The path shown in sys.fulltext_document_types is also correct.  I.e.:

document_type class_id path version manufacturer
.pdf E8978DA6-047F-4E3D-9C78-CDBE46041603 C:\Program Files\Adobe\Adobe PDF iFilter 9 for 64-bit platforms\bin\PDFFilter.dll Adobe Systems, Inc.

There is a KB article (http://support.microsoft.com/kb/972650) that deals with an issue related to iFilters. However,
1. the KB doesn't mention Windows Server 2008 nor 32-bit/64-bit
2. The fix is in CU6 (pre-SP1) but not in CU3 (post-SP1)
3. The path that SQL Server shows to the iFilter is correct on both 32-bit and 64-bit version of my SQL Server 2008
4. I've confirmed that the problem doesn't exist on 32-bit OSs at CU3.

Hi All,

Is it possible to do bitmap indexing in SQL server 2008 ? Please let me know if there are any useful links.


Hello! We found a problem when migrating from SQL Server 2005 to SQL Server 2008. SQL-statements like

SELECT <table1>.<fields>, <table2>.<fields>, <table3>.<fields>

FROM <table1>

JOIN <table2>

ON <table2>.<field> = <table1>.<field>

LEFT JOIN <table3>

ON <table3>.<field> = <table1>.<field>

WHERE (<table2>.<field> = 2 AND <table2>.<another_field> = 'some_text'

OR <table3>.<field> = 2 AND <table3>.<another_field> = 'some_text')

give different results on 2005 / 2008. On 2008 I get lots of exessive rows which have NULLs instead of the values required in the WHERE-term.

Changing the Compability Level does nothing. It also doesn't matter from where the query is executed (direct via ODBC, in a view or from the Enterprise Manager).

2005/2008 also use different execution plans. (Btw: how can I make an screenshot of a EP larger than the screen?)

After some hours I drilled it down to the existence of a index! By deleting or deactivating the index 2008 gives the correct results like the 2005 did, after activating the index I get these exessive NULL-rows again!

Anyone else ran into this problem? Any tips (dropping all indexes in a 25+ GB DB is NOT an option :-) ?

Thanks in advance!

Holger Wellenkötter


I have a database that will store a table consisting of email files in a VARBINARY(MAX) data type.  In SQL Server 2005 (9.0.4053) I have had no problems.  However, in SQL Server 2008 the indexing is 'failing' with a number of errors which include:

Error '0x80040e97' occurred during full-text index population for table or indexed view '[MA].[dbo].[MaMailItem]' (table or indexed view ID '2018822254', database ID '9'), full-text key value '6860'. Attempt will be made to reindex it.

Error '0x80040e28' occurred during full-text index population for table or indexed view '[MA].[dbo].[MaMailItem]' (table or indexed view ID '2018822254', database ID '9'), full-text key value '6422'. Attempt will be made to reindex it.

Error '0x80043630: The filter daemon process MSFTEFD timed out for an unknown reason. This may indicate a bug in a filter, wordbreaker, or protocol handler.' occurred during full-text index population for table or indexed view '[MA].[dbo].[MaMailItem]' (table or indexed view ID '2018822254', database ID '9'), full-text key value '6424'. Attempt will be made to reindex it.

However, if I leave it for a LONG time, some records are being indexed eventually. eg There are 16,900 files in this database table and the catalog rebuild was commenced at 4:30PM and is still running after processing only 1160 of the items and the time is now 10:30AM.

The test system is as follows:
Windows Server Standard 2008 SP2 64 bit (6GB RAM)
SQL Server 2008 SP1 (10.0.2531) 64 bit
The filter in use for the .eml files is c:\windows\system32\mimefilt.dll (2006.0.6002.18005) which was configured (along with the office filter pack and adobe 9) using:
  exec sys.sp_fulltext_service 'load_os_resources', 1
  exec sys.sp_fulltext_service 'verify_signature', 0
The full text service is running under Local Service

In desperation, I found another support item similar to the last 2 errors above (KB963659), and installed the Cumulative Update 6 for SQL Server 2008 SP1 to bring its version to (10.0.2757).  (This is a test server).  However, this has had no effect.  I have also tried increasing the ISM size.  CPU usage and memory use are low.

I never had this problem with the SQL Server 2005 version of this test database and it currently has over 56000 records in it.

I have confirmed that other file types have no such problems.

Thanks in advance of any help.

Hi All,

I have upgraded from SQL 2000 to SQL 2008 and I have some suggested indexes from dev guys I should apply on a DB to improve the application perfromance but i don't know the right type for each one (Non-cluster,cluster) is there anyway i can do to apply them based on best practice or tool like DTA

thanks in advance



i am trying to put some indexes on an sql server 2008 database, there is a clustered index by default on any table on the primary key only, i want to add another columns to this index, can i alter it, or the only way is to drop it and recreating it,,,,,,,  but in this way when i try to drop it it gives me an error that it can not drop the index because it is been used for primary key constraints..... any advice





I have some tables that use a Id attribute as string. And that's my primary key too.

No problem to map it at all, using EF4.

The problem is: Everytoime I generated my sql file and try to run it, I get some error of index, because sql server 2008 can't generate indexes for varchars.

I don't need the indexes at all. What I want to know is if there's some way of working with String as Primary Key.

That's the output from the Sql Server 2008: Msg 1919, Level 16, State 1, Line 3 Column 'IdLibro' in table 'Libros' is of a type that is invalid for use as a key column in an index.

Is it possible to upgrade SQL Server 2008 to Sql Server 2008 R2 on a SBS 2008 Server

I have a cluster server with SQL Server 2000 SP1 Enterprise. This server is distributor and publisher of 2 databases (about 40 subscribers with MSDE 2000 SP4).
I have to change the old server with a new one (Windows Server 2008 Enterprise + SQL Server 2008 Std).
What is the best way to migrate the replication considering that the applications running on subscribers use their local DB and they need to update quickly the data between other subscribers?

Many thanks for your help.



Is it legitimate to upgrade as I have stated in the Title? When I tried to do it, the installation program told me that it wasn't valid. Does anyone know what is the correct answer?

Ray Stevens

At initial install of SQL Server 2008 R2 November CTP there was an option to use the Evaluation Version License Key or enter an actual License Key. We installed using the Evaluation Key. Will we be able to update the key to an acutual License once available? If so, what is the process for updateing the License Key from Evaluation to RTM. I ahve had many past experiences where RTM License Keys required new RTM Media.  


I have been researching this online and connot seem to find a definitive answer. So, forgive me for the newbie question, but is it possible to view OLAP cubes in SQL Server 2008 Express? I have been trying to view these through Excel 2003 but this seems to be a limitation and I cannot find a data connector for the transport layer in Excel. So, I thought I would try to view the OLAP cubes through SQL Server 2008 Express version. Is this a limitation of SQL Server express? If so, is there another alternative someone could suggest?




I have a volume license dvd of SQL server 2008 Standard and have just purchased additional volume license to install another SQL server, however the license discription is for SQL server 2008 r2 Standard.

Do I need to downloard a new ISO file or will just getting the service packs and patches bring me up to the r2 version?


I need to take advantage of the web edition of SQL Server 2008, by my hosting company does not have version R2, how do I migrate from SQL Server Express 2008 R2 to SQL Server 2008 Web edition?



The upgrade to SQL Server 2008 R2 fails (unfortunately after upgrading already all user databases) in database "master" with the following error message:

2010-07-28 15:18:46.19 spid6s      Error: 574, Severity: 16, State: 0.
2010-07-28 15:18:46.19 spid6s      DROP EVENT SESSION statement cannot be used inside a user transaction.
2010-07-28 15:18:46.19 spid6s      Error: 912, Severity: 21, State: 2.
2010-07-28 15:18:46.19 spid6s      Script level upgrade for database 'master' failed because upgrade step 'u_tables.sql' encountered error 574, state 0, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2010-07-28 15:18:46.19 spid6s      Error: 3417, Severity: 21, State: 3.
2010-07-28 15:18:46.19 spid6s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2010-07-28 15:18:46.69 spid6s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

no relevant entries can be found in the Windows event logs.


If I purchased a copy of SQL Server Workgroup 2008 R2 do I have downgrade rights to SQL Server Workgroup 2008? (I know there might be no technical reason not to run R2 over normal 2008)



After I installed SQL Server Express 2008 R2 I realized that if I wanted to use the AdventureWorks sample databases I needed to have SQL Server Express 2008 R2 Advanced Services installed because Advanced Services has full text search installed which is required to install and run the sample databases. I downloaded the installation but nothing happened. So, do I need to uninstall SQL Server Express 2008 R2 and install SQL Server Express 2008 R2 Advanced Services? Or, is there a way I can upgrade my existing installation of SQL Server Express 2008 R2 to SQL Server Express 2008 R2 Advanced Services.



I have recently upgraded from SQL Server 2008 SP2 Enterprise edition to 2008 R2 Enterprise, and was in the process of validating that the process did not have any adverse affects on my existing 150 reports originally designed and managed via BIDS 2008.

Several reports were designed where the first page has a header different from the rest of the pages.  I created the generic header in the PageHeader and set PrintOnFirstPage to False.  This worked great in 2008...but in 2008 R2 the real estate on the first page is not "freed," so the first page looks like it has an enormous top margin when rendered as a PDF. Even playing around with the visibility of the text box doesn't eliminate the whitespace - and setting visibility properties was not required to get it to work in SQL Server, which rendered as a PDF perfectly just yesterday before our upgrade!

I noticed that there were three patches available, but I did not see a Fix available that addressed this issue, therefore I did not install them as advised on the site.

Is there any way to do free up the real estate so my first page can use it (maybe a setting in 2008 R2 that wasn't there for 2008)? Or is this a known bug between the two and a patch/hotfix is in the works? I'd hate to have reports that were specifically designed with certain paging to have to be revisited one by one.

Thank you for the prompt feedback!


<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure