[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Shutdown server relation to performance at application on early use

Posted on 2016-11-14
6
Medium Priority
?
110 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 98

Assisted Solution

by:John Hurst
John Hurst 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

656 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