| Author |
Topic |
|
konark
Yak Posting Veteran
60 Posts |
 Posted - 2010-08-16 : 20:53:57
|
| I have Orders table which has 8,00,00,000 rows.have 40,000 inserts per day , latter updates as well.29 columns Clustered index on 1 column.Primary key NON Clustered indexes on 7 columns.After the below checks , i want suggestions what else can i do .- Checking if statistics are updated ? Updated- Locking ? No locks.What are the ways to look at it .Chandragupta MouryaTakhyashila |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
konark
Yak Posting Veteran
60 Posts |
Posted - 2010-08-17 : 01:04:40
|
| - Indexes are there on the columns used in queries.- Indexes are rebuilt on weekly basis.I can run the missing indexes query and see.What else check, do you think would be good.Chandragupta MouryaTakhyashila |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-17 : 04:43:19
|
"Indexes are rebuilt on weekly basis"We rebuild indexes daily, but only those with high fragmentation. That way our users don;t have deteriorating performance through the week until the weekend-rebuilds  |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-08-17 : 05:41:03
|
quote:
Originally posted by konark
NON Clustered indexes on 7 columns
If those are 7 single-column indexes, they're likely to not be all that useful. Single column indexes are ideal only if all queries filter on a single column. Have you checked that SQL really is using those indexes?--Gail ShawSQL Server MVP |
|
|
|
konark
Yak Posting Veteran
60 Posts |
Posted - 2010-08-17 : 12:58:51
|
quote:
Originally posted by GilaMonster
quote:
Originally posted by konark
NON Clustered indexes on 7 columns
If those are 7 single-column indexes, they're likely to not be all that useful. Single column indexes are ideal only if all queries filter on a single column. Have you checked that SQL really is using those indexes?--Gail ShawSQL Server MVP
Usually i run query based on a single column , on which an NC index is there . Other queries uses other columns.Any thought on partitioning it ?Chandragupta MouryaTakhyashila |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-08-17 : 13:03:27
|
| What part is slow? Inserting into the Orders table? Selecting from Orders table? Select statements involving Orders table?Here are some ideas:- Partition the table- Indexes of more than one column- Indexed Views |
|
|
|
konark
Yak Posting Veteran
60 Posts |
Posted - 2010-08-17 : 13:28:06
|
| Selecting is slow - Issue with indexes , no need of partition? If insert is slow - can we partition ?Chandragupta MouryaTakhyashila |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-08-17 : 15:25:53
|
quote:
Originally posted by konark
Selecting is slow - Issue with indexes , no need of partition? If insert is slow - can we partition ?Chandragupta MouryaTakhyashila
Partitioning - haven't actually done it myself. Read that allows for more parallel operations. For example instead of aggregating over a huge table, each partition can be aggregated in parallel, and summed after that. Other than that I'm not sure why they are faster. People say it's more manageable chunks. That's like saying Partitioning is faster because it makes partitions. I think it is faster only (ie. besides the Parallel aspects) in terms of bring data from the Disk to the cache since it is already physically organized into partitions then again I thought that is what a Clustered Index does. In terms of making inserts faster, no I don't think it would. If anyone knows that answer or why Partitioning is faster, I'd be interested too?If you have joins and aggregates, the Indexed View should help. It is like pre-joining/pre-aggregating. But it will take up definitely more memory. It will also slow inserts but perhaps negligibly. |
|
|
|
|
performance Tuning issue