June 24, 2015 at 6:58 am
I just want to understand what happens.
I restore databases again and again in my developement cycle.
in my developement, I run the same stored procedures again and again.
If its first time after a restore, they can take 'Several minutes'
If its later - I am not sure when, it takes the usual 'Few seconds'
So something happens after having used a DB for some time.
But what?
Best regards
Edvard Korsbæk
June 24, 2015 at 7:24 am
My first guess is that all the execution plans have to be compiled again, and that maybe there is no caching at first.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 24, 2015 at 7:24 am
Loading data from disk into the data cache
Compiling the query the first time and placing the plan into the plan cache
The first is usually a far larger impact than the second.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 24, 2015 at 7:28 am
Does hardware performance of the machine are the same?
make rebild indexes and update statistics
June 24, 2015 at 7:30 am
So, when the DB runs a script, it first looks in a db adking'Have i done this before'
If the script has been run before, it knows 'How to'
Right?
Data Cache - Not understood?
Thanks for helping with some basic understanding.
Best regards
Edvard Korsbæk
June 24, 2015 at 7:45 am
Disks are slow, memory is fast. Therefore when SQL reads a page off disk it puts it into the data cache so that if it needs to read it again, it can go to memory, not to disk, to fetch the page. That's the data cache in a nutshell.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 24, 2015 at 7:52 am
Yes, and thats too how iI understand a Data Cachee
What Iam not sure of, is about this is different for the first read after restore and for the rest.
Ok,if its the same machine and the same session, yes, but I have found, that the DB is slow from whatever PC I use untill it has been opened a coule of times.
That could be explained by recompiling executening plans and indexes - But you speaks about the data cachee too?
Best regards
Edvard Korsbæk
June 24, 2015 at 8:04 am
Because the first time you run a query the data has to be loaded from disk. Disk is slow. After that first execution, SQL Server can read the pages from the data cache instead of having to go to disk. Memory is fast, hence subsequent executions are faster than the first one after a restore/restart.
Nothing to do with index rebuilds
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 24, 2015 at 8:34 am
Make a simple test. Take stored procedure which is running slow after reboot of the server (or RESTORE) and execute quickly from the second execution onwards.
Simply recompile that stored procedure and see if you are having more time or less?
Let us see know the outcome.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply