February 8, 2013 at 12:25 am
Hi,
I have 244 tables in test environment recently created . Now its my responsibility to check whether proper indexes are created and check referral integrity. how can i do this as i am a new bee any suggestions are appreciated.
Thank you
February 8, 2013 at 1:07 am
m.rajesh.uk (2/8/2013)
I have 244 tables in test environment recently created . Now its my responsibility to check whether proper indexes are created and check referral integrity. how can i do this as i am a new bee any suggestions are appreciated.Thank you
Following are the things you need to consider.
1) study the catalog view sys.dm_db_index_usage_stats . see http://basitaalishan.com/2012/06/15/find-unused-indexes-using-sys-dm_db_index_usage_stats/
2) you need to do extensive study ion queries , profiler trace to catch queries with high resource usage then study or analyze them with the help of exec plan .. look for table/index scan.
3 ) monitor applicaiton with QEs and catch the queries/areas which are slow runnig or getting time-out.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
February 8, 2013 at 8:13 am
Wow that is quite a task for a newbie.
Over on the left side of this site you will find a link called Stairways. There is an entire series on indexes. You can't possibly know if the indexes are setup correctly until you know what proper indexing strategy entails. This is not a small topic that can be learned by reading a couple of articles. There are lots and lots of entire books just on indexing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 8, 2013 at 8:24 am
I agree with Sean, this is not a task for an entry level person.
Building on his comment, ask whoever tasked you for the ER Model - if it exists it will help you to figure out Referential Integrity which would make apparent a bunch of indexes. In general, an index is expected to support each and every single PK or FK contraint.
Once those are in place the long and painful road of monitoring poorly performing queries would unvel the need of other indexes, one at a time.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 8, 2013 at 9:23 pm
Thanks for your valuable suggestions.
February 9, 2013 at 12:15 am
is it necessary to have primary key on each table.
i am confused related to primary key and clustered index.
so far i know that primary key on a column creates clustered index.
can we create a clustered index with out primary key on a colum and non clustered primary key
February 9, 2013 at 7:48 am
m.rajesh.uk (2/9/2013)
is it necessary to have primary key on each table.i am confused related to primary key and clustered index.
I recommended in SQL Server to begin with the idea that all tables should have both a primary key and clustered index. Some would argue that without a primary key it is not truly a table. There are cases when you do not want either on a table but mostly they are special cases, e.g. staging tables, tables accepting only a high volume of inserts, there are others but best to stick to basics for now.
SQL Server creates an index underlying all primary keys.
A clustered index can be declared separately from a primary key, it does not have to be unique and some columns can be nullable.
A primary key must be unique, cannot contain null columns and can be defined on the same columns as the clustered index but it is not required, i.e. a primary key can be declared as nonclustered.
We said that SQL Server will create an index underlying the the primary key. SQL Server takes it one step further if there is not already a clustered index on the table and you do not specify that the primary key should be declared as nonclustered. In this scenario SQL Server will make use the primary key columns to create the clustered index for you.
Not all tables need a clustered index or a primary key but for most applications it is a good idea.
so far i know that primary key on a column creates clustered index.
Only if a clustered index does not already exist on the table.
can we create a clustered index with out primary key on a colum and non clustered primary key
Yes.
Stairways to Indexes[/url] is a great place to look for information about indexes.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 10, 2013 at 11:27 pm
Thanks for all your replys
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply