March 12, 2020 at 12:03 pm
The exam page fetches data from SQL Server. We are expecting 15K exams by this month end. What should i do so that databases performance will be good and it doesnt crash
March 12, 2020 at 12:15 pm
ok
we probably need to see the SQL that is being executed, but 15k records is very small for SQL. are you trying to put them all on screen in a web site? in which case your web site is the issue
if you don't trust the database layer, then make a copy, bulk load 15k records in and test (I hope you are using procs - that makes it easier ) - always rollback your test environment after each insert/update test
get query plans and post them here. but please before anyone can help you , you kinda need to post some (maybe masked) version of your sql code and we can help out - table structures (including indexes and triggers) are also a good way to get us to help
otherwise it's a little bit like me emailing you and saying "help, database is broken"
pop some details on here and I bet you will have lots of answers
MVDBA
March 13, 2020 at 10:46 am
Backups!
Always first. Test them. The only good test is a restore.
Next, statistics maintenance. Understand how your data modifications affect your statistics and then put necessary statistics updates in place to ensure the most accurate statistics possible. After code and data structures, statistics are the biggest cause of issues with performance and behavior within SQL Server.
Next, and frankly, this should already be done, do the work to tune the queries. Adjust the code. Change the data structures (appropriate normalization is WAY better than denormalization and I'll die on that hill) to enhance performance. Ensure you have the right indexes. Ensure the code uses the indexes appropriately. All this ought to be done before you start saying, "Oh gosh, we're looking at an increase in volume."
Next, put monitoring in place. Yes, I work for a software vendor, the but the right answer is to buy a tool. Why? It buys you time. Can you set up 100% of your own monitoring? Yes. Do you have to the time to do that, plus the steps above? Probably not. So, buy a monitoring tool to get yourself some more time to do the steps above. The steps above are your job, not building monitoring software.
Did I mention testing your backups?
----------------------------------------------------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
March 13, 2020 at 10:58 am
+1 for grant
MVDBA
March 14, 2020 at 5:35 am
Ditto that.
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply