Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rav3n0u5
Yak Posting Veteran
88 Posts |
 Posted - 2005-11-22 : 03:15:13
|
| I have a table with about 860,000,000 rows, and it is grows every day by a few million. It is 7 columns, and it has a unique index on 2 columns. Each column gets queried individually for multiple reasons, and this table pops up everywhere. Overall the database is pretty normalized. There is a stored proc that runs daily, that has to read heavily from this table. Normally the proc takes about an hour to run, but lately it has been hanging (one time it finished in 9 hours, and after that it would run for 16 and not finish). I ran it through the Index Tuning Wizard, and the Wizard advised me to remove some indexes on this big table from columns not used by this proc. I removed a few indexes, and sure enough, the proc is running at an hour again. (Does this seem a bit strange?)If I create an indexed view on the unique id of the table and an individual column, for each column, and then query the appropriate view, will this be any faster than querying the non-indexed base table? Is this even a decent idea? Have any better ideas?Thanks! |
|
|
surendrakalekar
Posting Yak Master
120 Posts |
Posted - 2005-11-22 : 03:38:02
|
| I would like to suggest you to run once "maintenance statements-DBCC" on that table before running your sp. Also check what is the FILLFACTOR for your indexes created on that table. I don't think view will increase the performance of your sp rather it will decrease the performance of your insert statement on that table. It will be better if you paste your stored procedure and table structure here to understand it better.Surendra |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-22 : 06:42:33
|
| Sounds like the SProc needed recompiling, or the table/indexes needed Defragging (or Reindexing, but I expect that would take too long) or the Statistics needed updating. Do you do that sort of stuff regularly?If not then modifying the indexes may have caused all of those housekeeping things to happen, as a side effect, and thus may not have been the solution you needed at all!Kristen |
|
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2005-11-22 : 11:05:19
|
| Each time a table is inserted, updated or deleted from, the system has to go and adjust every single index that exists on a table. That work has to finish before the system releases the lock for that row. So if you had a bunch of uneeded indexes it would make sense that while your query was running and the system was live, that it could have kept getting locked out row by row as the system kept modifying those unneeded indexes. You have to find the right balance between having indexes for reporting and not having them to speed up the OLTP operations. Likelihood is that someone else tried to improve the performance of other queries against that table, and then created a whole bunch of indexes to make their particular queries faster. Be careful of how you create Indexed Views. If you do it through the Index Wizard it will set a particular flag for how the system should deal with divide by zero error. If your application assumes it can safely do that, but you end up changing it when you create the Indexed View your application will start yielding errors. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-11-22 : 11:30:37
|
| Also consider if you can create summary or reporting tables from this large table. If a transaction is static once it is "posted" (or has some similiar status), and much of your reporting is summarized from this table, you may be able to create summary tables of this large table that you can update periodically (or via a trigger if it needs to be instant) and report off of that. This may allow you to have less indexes on your current table and will make inserts/deletes faster. |
|
|
|
rav3n0u5
Yak Posting Veteran
88 Posts |
Posted - 2005-11-22 : 15:46:01
|
| And this is why I love this forum... Thank you for all the responses! I will try to address them one by one. There is a lot of overlapping (obviously).Surendra - You mention "run-once" DBCC statements. Could you elaborate on this? I do have a MS SQL maintenance plan that runs weekly on the database containing this table. Among other things, it "Optimizes" the database. I am about 60% sure that this is a reindex with some flags. I haven't really had the time to focus on learning dbcc commmands, so any hints would be greatly appreciated! In resposne to your other statement about inserting... I am not having a problem with writing to this monster table. I am having a problem reading from it. The big sproc that runs does not insert into this table, it just reads values from this table, among others. Kristen - How do I force the recompile of a sproc? I believe the Statistics are updated with the weekly maintenance plan I mention earlier (although I must admit I don't know what updating statistics does - doh!). Assuming the "optimization" I run weekly doesn't do things perfectly, how long do you think a reindex would take (a range of values would be fine)?Druer - You are spot on about someone having been index-happy on this table (and every other freaking table in the database). You also mention the divide by zero error, and I got kind of lost here. I am not dividing by zero, so I don't know why error handling would come into play (of course the sproc isnt perfect, but I believe it has enough error handling to prevent this from happening). The sproc that runs is for internal purposes, and the data it creates is then fed to a client application. Can you expand on this?jsmith - I have definitely considered creating a summary table. I wasn't sure if a summary table or an indexed view would better serve the database. I think I entertained the idea of an indexed view because I was anxious to flex some sql muscle. But I don't know if there is a comparison between the two, and if the indexed view is worth it. The logical trouble I am having with a summary table is that it would only have 3 columns, it would be redundant data, and I would want a few 3 column summary tables. Further thought?I really appreciate all the help! Thank you so much! |
|
|
|
surendrakalekar
Posting Yak Master
120 Posts |
Posted - 2005-11-23 : 02:17:32
|
quote:
Originally posted by rav3n0u5
Surendra - You mention "run-once" DBCC statements. Could you elaborate on this? I do have a MS SQL maintenance plan that runs weekly on the database containing this table. Among other things, it "Optimizes" the database. I am about 60% sure that this is a reindex with some flags. I haven't really had the time to focus on learning dbcc commmands, so any hints would be greatly appreciated! In resposne to your other statement about inserting... I am not having a problem with writing to this monster table. I am having a problem reading from it. The big sproc that runs does not insert into this table, it just reads values from this table, among others.
(1) Database Maintenance Statements are... DBCC DBREINDEX DBCC DBREPAIR DBCC INDEXDEFRAG DBCC SHRINKDATABASE DBCC SHRINKFILE DBCC UPDATEUSAGERead help and use those as per your requirments.(2) I said “I don't think view will increase the performance of your SP rather it will decrease the performance of your insert statement on that table.” I mean the other SP, which is inserting records into that table will perform slower, because of view.Surendra |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-25 : 02:19:15
|
| "How do I force the recompile of a sproc?"You can force it to recompile every time (not what you want) in the header part of the Sproc.There is a system Sproc which will recompile a given stored procedure, from memory:sp_recompile 'MySprocName'obviously you would have to write a loop to fish the names out of the system tables, and then call sp_recompile. Alternatively stop-start the SQL Server (or reboot the machine!) if the databases are unused during seom maintenance window."I have a table with about 860,000,000 rows ...""... how long do you think a reindex would take "A long time! However a DEFRAG instead does not lock the table, and can be aborted (the work it will have done will be retained).Kristen |
|
|
|
|
|
|
|
|
To index view, or to not index view?