May 21, 2008 at 8:59 am
We have a Database of Datawarehouse and having issues with Performance.
I found that they were no Indices on many tables with more than 1000 Rows. I am not sure whether indices are required or not, but i know onething is that, any table which exceeds more than 1000 Rows should have index created on it .
I'll be thankful for all your answers.
May 21, 2008 at 9:04 am
IMO there should at the least be 1 clustered index on every table. In a datawarehouse situation, which you mention you are in, there should be indexes on every table since a datawarehouse exists to support reporting and indexes will make reporting faster.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 21, 2008 at 9:05 am
It is hard to comment without knowing some more information about the table structures and how they are used.
1000 rows is not really a lot of rows for SQL server, but in general it is a good idea to have at least one Index on a table, which should at least be the clusterd index of the primary key.
Check your execution plans and select the correct indexes for your tables.
May 21, 2008 at 9:44 am
Most of the tables are more than 2000 rows
What are the things that i need to check out in Execution Plan.
I don't have idea about , how to look at Table Structures.
Please help me out in answering to my questions.
Thanks for your time.
May 21, 2008 at 9:52 am
Table structure is what the table contains, the names and types of columns contained in the table, expand the table node in SSMS to have a look at the contents of the table, important things are the number of columns and data types for the coulmns.
In the execution plan have a look at what processes are taking the most time, if you can idenftify one that is related to a look-up or join on specific column then this could be a candidate for an index.
May 21, 2008 at 9:58 am
You'll need to look at the queries being run and determine what the most likely access paths are for your data. There you'll want to put indexes. After that initial pass, it's tuning, meaning, finding the poor performers and rewriting the query and/or adding indexes.
Follow Jack's advice, start with a cluster on each table.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply