?
Solved

Shutdown server relation to performance at application on early use

Posted on 2016-11-14
6
Medium Priority
?
102 Views
Last Modified: 2016-11-15
Hi There,

We use MS SQL server.
We happens when shutting down server, our application become slower and then more and more fast after several transacion.
When we do not shut down server, it does not happens.

What is the relation about it ?

Thank you.
0
Comment
Question by:emi_sastra
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 97

Assisted Solution

by:Experienced Member
Experienced Member earned 400 total points
ID: 41887207
I am not certain, but I think that involves caching of transactions in memory which save time and which go away when you restart the server.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 41887284
Hi John,

If it is the case.
There is nothing we could do about it ?

Any experiences something like it ?

Thank you.
0
 
LVL 14

Accepted Solution

by:
Nakul Vachhrajani earned 1600 total points
ID: 41887341
What's happening is that as you access data from the database, two things happen:
- You would use the same queries again and again (because the data would be accessed from the same system). Hence, SQL Server leverages something called as "Execution Plan Cache" which maintains how to execute a query. Evaluating an execution plan is costly because the same query can be executed in multiple different ways depending upon the load, the amount of data, available CPU/memory and a lot of other things. To avoid taking that cost over and over again, SQL Server leverages the plan cache
- SQL Server will also load data pages in an area called the "buffer pool" (which is again a cache, but for data). Next time, when your application accesses the same data, it need not go back to the disk to load data again. It can reuse that's lying in the buffer pool

In addition a couple of other things happen as well - like tempdb growth. Database files (including tempdb) grow to accommodate storage demand. Once the demand ends, the space is not returned back to the OS. This is done so that in case you need it again, SQL Server does not need to spend time in initializing the data file space again.

When you restart a server, the tempdb goes back to default size, the execution plan cache is cleared and the buffer pool is cleared - resulting in poor performance for the initial transactions.

What you can do to overcome this?
I'll address this question in two stages - Basic & Advanced.

Basic steps:
- Monitor the space needed by tempdb at an average. You can then alter the tempdb database to have a higher data file and log file size and growth increments. This way - you take the hit due to file growth during the restart of the instance, but not when your transactions are running
- You can try doing a routine called as "warming up the cache". What this involves is running your application via an automated test suite or simply firing SELECT * FROM statements on your key tables before the system is available to suers. This causes the data to be loaded in the buffer pool when the first user logs in (NOTE: This is a stop-gap arrangement, and is practiced while the code is being tuned for better performance)

Advanced steps:
- While the above mentioned basic steps will resolve the issue as a stop-gap arrangement, you need to review your queries and tune them so that they perform well in the first run itself. Once this is done, you don't need to "warm up" the cache anymore
- You can evaluate "fixing" the execution plans but that is a very advanced step
1
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 1

Author Comment

by:emi_sastra
ID: 41887537
Hi Nakul,

Great explanation.

Is there any resource that I could learn more ?

Thank you.
0
 
LVL 14

Expert Comment

by:Nakul Vachhrajani
ID: 41888461
Hello!

Sorry about the delay in response.

The truth about performance optimization is that there is no magic bullet. The correct solution almost always depends on the specific scenario at hand. In your specific case, the long term is to tune your queries - how to do that needs a lot more specific information about your database design, workload and queries themselves.

In my opnion, there are two different areas to focus on when trying to understand SQL Server internals.
1. Architecture and Database design
2. The overall implementation of the database code

You can pick either area first (although I find it logical to begin with architecture & design). To get started, my suggestion would be to get a practice environment and a good book (a book from MS Press for certification works best). Follow the SQL Server community via Blogs or communities like EE (try to validate why a particular answer was chosen as right - what's so special about that answer), SQLSkills.com and SQLServerCentral.com.
0
 
LVL 1

Author Comment

by:emi_sastra
ID: 41888891
Hi Nakul,

Thank you very much for your help.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question