Indexes

Msg 2767, Level 16, State 1, Line 2 Could not locate statistics

The other day when tuning some indexes on a project I was being my usual lazy self and I dragged/dropped the column name into the query window. After executing DBCC SHOW_STATISTICS I was confronted with the following error message.

could not locate statistics

Wait, what? Why am I getting this error? I know there are statistics. So why do you get this error? I am not sure to be completely honest but I can tell you there is a very simple fix. Remove the brackets at the beginning and end of the column.

remove_brackets

Then execute DBCC SHOW_STATISTICS and you should be fine.

results

Hopefully the helps reduce some of the stress you have related to the indexes you are tuning.

Cheers!

Advertisements

Msg 2576, Level 16, State 1, Line 1 or Index Allocation Map (IAM)

There is a project currently hosted at another location within our infrastructure. In the not too distant future it will be moved to our data center and fall under my support. In preparation of the move servers have been setup and I am using a copy of the database to set up the database(s) and the various components required to support the project.

The project requirements are to have a primary database server and a secondary one. The secondary will be used for reporting purposes. I was told they currently use transaction replication to keep the two up to date so reports can be generated with near real time information. I ran into a little problem with setting things up and wanted to share my experience.

The Error

DBCC CHECKDB(MyReport)WITH NO_INFOMSGS

Msg 2576, Level 16, State 1, Line 1

The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (24:40) in object ID 16719112, index ID 1, partition ID 72057594152419328, alloc unit ID 72057594158579712 (type In-row data), but it was not detected in the scan.

CHECKDB found 44 allocation errors and 0 consistency errors in database ‘MyReport’.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MyReport).

Note: this error was being thrown on the secondary/report database server.

Investigation

First, I called up my trusty DBA partner, the Internet and looked up the error message and what it meant. I found some information but most if it was on forums. After reading up on the error and what potentially caused it I looked at the primary database again with the thought that maybe somehow I missed doing something. But the error was not being reported there.

Since there were no errors in the primary database I thought maybe it had to do with something in the replication I either missed or setup wrong. So I dropped the replication and the reporting database and started over. After restoring a brand new backup on the report server I ran CHECKDB and did not get any errors. O.K. some progress.

Next I setup the replication again and after it was running, I ran CHECKDB. BAM! The error appeared right away. More progress but what in the replication process could be causing the corruption?

I then asked my other DBA partner, #SQLHelp on Twitter. Robert Davis ( blog | twitter ) he was instrumental in helping me focus back on the actual cause, Indexes. More importantly, the missing meta data for them was missing. I had moved away from the actual error when I determined (or thought I did) there was something in the replication that was causing the corruption. Wrong.

Solution

Looking at the Indexes I found the ones causing the error were the Full Text Indexes. Further investigation should I did not have the Full Text Component installed on two of the secondary or report servers. Doh! After installing the feature and redoing the entire replication setup the error was gone.

Conclusion

Lesson learned? Focus on the error and what the root cause is and try not to lose focus. Make a plan and then follow the plan. I have check sheets to follow but sometimes mistakes happen. We can learn from them or repeat them again. Hopefully others may learn from my mistakes.

Cheers!

 

T-SQL Tuesday #10 – Indexes 101

This post is for T-SQL Tuesday #10 hosted by Michael J Swart twitter | blog.  

This is also an introduction to indexes. The goal is for you to gain a basic understanding of indexes. The different types and why you want to use them. I will cover advantages and disadvantages of the use of indexes.

What are indexes?
Indexes in databases are like indexes in a library. They allow the information you are seeking to be retrieved quickly. If the books in the Library are stored alphabetically by book name then finding a book on SQL Server is easy. You go right to the section that begins with the letter “S”. Instead of starting a the letter “A”.

You have the option of creating an index on one column or a number of columns. Indexes are basically storage items or data structures within a database that contain information (keys). SQL Server knows how to quickly get the row or rows associated with the values in the keys.  

Types of Indexes:
There are 8 different types of indexes with brief explanations.

Clustered:
Clustered indexes sort and store data in order based on the clustered index key. A Primary key is a good example of a clustered index. Each entry represents one row in a table. You may only have one clustered index on a table.

Nonclustered:
Nonclustered indexes can be used on tables or views having a clustered index (primary key) or on tables or views (heap) not having a clustered index defined. The nonclustered index has a key and row locator pointing to data. If a table or view does not have a clustered index defined the order of the rows in the index are not guaranteed. SQL 2005 allows up to 249 nonclustered on a table and SQL 2008 allows  up to 999.

Unique:
Using a unique index on a table or view will ensure the key does not contain any duplicate values and every row will be unique. You can apply a unique index on clustered and nonclustered indexes.

Index with included columns:
An index with included columns is a nonclustered index that you add additional columns to that are not covered by the key columns. You can increase performance greatly by using non-key columns covering more queries.

Full-Text:
Full-text indexes can be created on tables or indexed views. This index can include up to 1024 columns and you can only have one is permitted per table or view. To use Full-text indexes you must setup the Full-text engine with SQL server. This type of index is primarily used for word searches in character string data.

Spatial:
Spatial indexes are special indexes specific to spatial data stored geometry data type columns. This index is only available in SQL 2008 and later versions.

Filtered:
A Filtered index is used to cover queries selecting from a defined subset of data.
Comparing this type of index with a table index, you can improve performance, reduce maintenance, and reduces index storage.

XML:
An XML index is a used on XML data type columns. These indexes cover tags, values and paths within your xml data and can improve performance.

What do we gain by using indexes?
If used properly indexes can reduce disk I/O operations and reduce the resources needed and overall improving query performance.

Indexes on tables can improve performance when selecting data from a table because if no indexes are present SQL server must look at each record in the table starting at the top until it finds what it is looking for. Imagine if it had a several thousand or several million records. You can see where performance can and will quickly go right out the window without indexes.

When designing indexes you should keep in mind performance benefits gained must not outweigh the cost of storage and resources needed for processing the requests. What this means is tables or views with indexes require more storage space. Keeping in mind certain statements that insert, update, and delete data can take longer to perform and require more processing resources to complete their tasks as well.

So, in summary, I talked about what indexes are, why we can benefit from their use and some of the costs associated with using indexes. Hopefully you have a better understanding of indexes now.

Cheers!