Solved

Shutdown server relation to performance at application on early use

Posted on 2016-11-14
6
57 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
  • 3
  • 2
6 Comments
 
LVL 90

Assisted Solution

by:John Hurst
John Hurst earned 100 total points
Comment Utility
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
Comment Utility
Hi John,

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

Any experiences something like it ?

Thank you.
0
 
LVL 11

Accepted Solution

by:
Nakul Vachhrajani earned 400 total points
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Hi Nakul,

Great explanation.

Is there any resource that I could learn more ?

Thank you.
0
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
Comment Utility
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
Comment Utility
Hi Nakul,

Thank you very much for your help.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

743 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now