July 3, 2012 at 11:01 am
Actually, the target table has over 8M rows in it (47M in production) and we're inserting about 1.3M. The select tables are dropped and reloaded very time we import data and we rebuild the indexes after we load the data.
Check the stats of these tables. These will help in better estimates of rows inserted and thus might help in overall performance.
This is a high transaction table with lots of reads and then bulk data loads with millions of rows being inserted at a time. We also delete all the previous information for the customer and load in the information. Unfortunately we end up loading in a lot of the same information every time, though that information could have changed and new data would also be inserted. So we have lots of deletes on the table which leads to a high amount of fragmentation. We rebuild indexes weekly so in the production system fragmentation is relatively low, but in our test system we had fragmentation of 80% before I rebuilt the indexes.
Are these load and delete are bulk or do you add or delete few data here and there during day as well?If it is one time process then you do not need the fillfactor at all.
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 3, 2012 at 1:56 pm
Also you have 2 very big indexes.One has 10 columns and other has 9 columns .Table just has 12 columns.
It's a pretty sure bet you have the wrong clustered index. Identity cols are easy, convenient, the default for many people, and ... often dead wrong.
Getting the clustered index correct will improve the overall performance of most activity, including all inserts, because other indexes can usually be reduced.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 3, 2012 at 2:03 pm
ScottPletcher (7/3/2012)
Also you have 2 very big indexes.One has 10 columns and other has 9 columns .Table just has 12 columns.
It's a pretty sure bet you have the wrong clustered index. Identity cols are easy, convenient, the default for many people, and ... often dead wrong.
Getting the clustered index correct will improve the overall performance of most activity, including all inserts, because other indexes can usually be reduced.
You are probably correct, however, having worked with the database in the past you can't just go changing the indexes as it can have serious repercusions to the application itself. There are embedded sql statements in the application that actually depend on the current design. While working for a school district that uses this application I played with rewriting some of the queries only to find that the queries used were faster, and if I changed the indexes in a sandboxed version to make mine work better, it had serious performance implications to the application.
And since I could not change the code to use my SQL, I found it best to leave the indexes that were developed by the vendor in place.
July 3, 2012 at 3:45 pm
Lynn Pettis (7/3/2012)
ScottPletcher (7/3/2012)
Also you have 2 very big indexes.One has 10 columns and other has 9 columns .Table just has 12 columns.
It's a pretty sure bet you have the wrong clustered index. Identity cols are easy, convenient, the default for many people, and ... often dead wrong.
Getting the clustered index correct will improve the overall performance of most activity, including all inserts, because other indexes can usually be reduced.
You are probably correct, however, having worked with the database in the past you can't just go changing the indexes as it can have serious repercusions to the application itself. There are embedded sql statements in the application that actually depend on the current design. While working for a school district that uses this application I played with rewriting some of the queries only to find that the queries used were faster, and if I changed the indexes in a sandboxed version to make mine work better, it had serious performance implications to the application.
And since I could not change the code to use my SQL, I found it best to leave the indexes that were developed by the vendor in place.
Ok. That may indeed be the case here; unfortunate, since it will hinder performance.
In general, though, I would think the SQL index usage stats and missing index data would allow you to make an accurate assessment of what was currently needed regarding indexing, rather than being stuck with the vendor's initial set up.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 3, 2012 at 3:49 pm
ScottPletcher (7/3/2012)
Lynn Pettis (7/3/2012)
ScottPletcher (7/3/2012)
Also you have 2 very big indexes.One has 10 columns and other has 9 columns .Table just has 12 columns.
It's a pretty sure bet you have the wrong clustered index. Identity cols are easy, convenient, the default for many people, and ... often dead wrong.
Getting the clustered index correct will improve the overall performance of most activity, including all inserts, because other indexes can usually be reduced.
You are probably correct, however, having worked with the database in the past you can't just go changing the indexes as it can have serious repercusions to the application itself. There are embedded sql statements in the application that actually depend on the current design. While working for a school district that uses this application I played with rewriting some of the queries only to find that the queries used were faster, and if I changed the indexes in a sandboxed version to make mine work better, it had serious performance implications to the application.
And since I could not change the code to use my SQL, I found it best to leave the indexes that were developed by the vendor in place.
Ok. That may indeed be the case here; unfortunate, since it will hinder performance.
In general, though, I would think the SQL index usage stats and missing index data would allow you to make an accurate assessment of what was currently needed regarding indexing, rather than being stuck with the vendor's initial set up.
You'd think, but the vendor did what the vendor did. Plus, I happened to meet one of the original developers as she used to work for the same school district. She walked on water because she knew where the rocks were. Things were more often done for expediency rather than efficiency.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply