October 8, 2002 at 2:48 am
Hi,
I have a general question please!
How and where the data flows when there is a large data operation? Say SQL is serving a simple select * statement of 10 gb size of rows (though hypothetical!)or may be for insert or update. I wonder how it manages it's memory resources.. and wish to know more on this.
Will the data is searched from RAM pages or Hard disk or from where, but importantly how?
Is there any role of tempdb at this stage?
Can any of you suggest good books/sites please? (other than Inside SQL server 2000 an excellent book by Kelan Delaney!)
My sincere thanks for your time and consideration.
Regards
Gopi
October 8, 2002 at 4:49 am
First off memory is actually managed by the OS more so than SQL Server, even that memory allocated to SQL Server. The programmers most likely did not implement too much if any specific memory management in SQL other than to establish data pointers to objects in memory.
SQL Servers data flow varies based on factors of the query.
For your example of "select * statement of 10 gb size of rows" it will pull the data and put into the TDS (tabular data stream) buffer to be sent immediately. The server may barely even notice it but the client side will as the data will all be in memory until that is exausted then the machine will start failing.
However if say you add an ORDER BY clause then the data is put into a temp table created in the tempdb for sorting during execution. As long as enough HD space and memory is available then it will be put on the TDS to the client.
Now INSERTS, UPDATES and DELETES are all similar in nature in that they create row images in the Transcation Log, a before and/or after shot of the data. This means whatever change you make the relative size of the log will grow either the same size or twice as large as the actually data in the query. The TL will containt all the individual transactions in the query until HD space is gone, another type of failure occurrs or the query completes. Then the data will commit to the change and mak those portion of the TL complete or the transactions will rollback marking those portions as complete even thou they completed as a failure.
Beyond that there are lots of complexities that can alter where the data is at any one point as far as CPU, memory, swap file, tl, or datat file.
The idea is to try to keep as much available in memory at one time to speed things along, this is where the buffer cache comes in, it stores some pages of data based on often used queries and recent queries. By doing so if the same query is run then the data is readily available.
Also, there is overhead inccurred with index selection and creation of execution plans, this is where the procedure cache and where Stored Procedures shine. By keeping their execution plans and storing them it makes each consective run after the first faster and less tme consuming, especially when combined with the data buffer cache.
Now, this is very generic and probably did not answer the questions properly. If you need more detail please ask away, most I won't know but what I do I will try to answer or someone else will be kind enough to add, alter, or answer anything stated here.
Hope this helps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 8, 2002 at 5:57 am
Thank You Antares686.
I believe your explanation is really fair enough regarding the memory issues. Very relevent and excellent.
However has anyone seen the documents or white papers regarding the internal resource management? I could not get anything from MS. But may be someone will be kind enough to throw light on the subject.
Once again my sincere thanks for the same.
Cheers
Gopi
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply