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 |
|
rahulyadav83
Starting Member
1 Post |
 Posted - 2014-07-22 : 09:37:52
|
| Hi,I have a table in MSSQL having more than 27 million rows.Now what happens is that the table becomes slow after 2-3 days I create it (Right after creating it, I insert 25 million rows from historic data). For your reference the row count takes initially 12 secs which increases to 26 secs towards the end of 2 days.Below is the summary of the operations that run on the table:1. An insert every 5 minutes. Inserting around 1400 rows everytime.2. A Stored Procedure working every 5 mins.3. Few other queries which are not very frequent.Till now I am truncating the table every time the queries become slow and then insert the historic data (25 million rows) again.It helps and the performance boosts for the next 1-2 days. I have no idea why this happens as i. The size of the table does not increase dramatically ( just 5-10 % increase)ii. The operations running on the table are same right from the creation.iii. There is no delete operation running as of now, so the possibility of ghost records is eliminated.iv. similarly no update.I have tried a lot of things (e.g Update Statistics, Rebuild, Nolocking ) but to no avail. Are the frequent operations creating some sort of baggage on the table? Is there a way to clear it?Your suggestions will be appreciated!!!Note: My knowledge of SQL is mediocre.Rahul |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-07-22 : 11:38:02
|
| The first things I would have suggested are updating the statistics and rebuilding or reorganizing the indexes. Since you are already doing those, the next thing I would look at is the execution plans when it runs fast and compare it with the execution plan when it runs slow. You can enable execution plans using Query -> Include Actual Execution Plan from the top menu in SSMS.The execution plan can tell you a lot of information - starting with whether the query plans are the same, and where the resources are being consumed etc. |
 |
|
|
|
|
|
SQL Table becomes slow with same size+operations