SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Performance implications due to data growth on well tuned database


Performance implications due to data growth on well tuned database

Author
Message
sql_er
sql_er
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 562
Hi,

Recently our company began considering an increase in the amount of content we house in our MSSQL 2000 database. One of the obvious questions that came up was what would be the performance implications - mostly on the stored procedures, which are used by our clients, and are expected to continue to return results within a few hundred milliseconds.

So if all our stored procedures are well tuned to use indexes and if we have a lot of RAM, would that mean that we can grow without a large affect on our stored procedure performance?

I know this is not a yes or no question, but just wanted to hear some general thoughts/opinions.

I guess the first question is whether my assumption is correct about the MSSQL 2000 indexes. Are they always stored in memory?

Please let me know any suggestions

Thank you!
Jim McLeod
Jim McLeod
Right there with Babe
Right there with Babe (777 reputation)Right there with Babe (777 reputation)Right there with Babe (777 reputation)Right there with Babe (777 reputation)Right there with Babe (777 reputation)Right there with Babe (777 reputation)Right there with Babe (777 reputation)Right there with Babe (777 reputation)

Group: General Forum Members
Points: 777 Visits: 1121
Your assumption that indexes are stored in memory is slightly incorrect. All data, whether from a table (heap or clustered index), or a non-clustered index, is stored on disk, in the form of 8KB pages. When SQL Server is processing a query, it will look for the pages required. If the page is not found in memory, it will be loaded in from disk and cached for a period of time, depending on how often the page is used, and how much memory pressure the server is under. As the non-leaf pages of indexes are frequently accessed, these will typically stay in memory.

In a well tuned database, it will be rare that you will be performing scans of tables. You will certainly not be scanning the past 7 years if you're only interested in the last week's data. This means that the amount of data you need to store in memory is much smaller than the entire database size.

If you do have a table scan, your 7 years of data will likely not be in memory (it may not even fit in memory!), and so the query will have to perform a lot of I/Os against the disk - the query is "running from disk". If your query is well tuned, then only the necessary pages will be read. If this is, say, 1000 pages (8 MB), then this will run quickly whether the pages are in memory, or on disk (although obviously in-memory will be an order of magnitude faster).

Even if you have enough memory that your entire database will fit into memory, you will still want to have tuned queries. Even if everything is in memory, scans create contention (blocking) and use a lot more CPU resources.
sql_er
sql_er
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 562
Thanks Jim!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search