| Author |
Topic |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2010-07-13 : 18:29:04
|
| 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 suggestionsThank you! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-07-13 : 20:37:22
|
| No, whether or not the data is in memory is dependent upon how much memory SQL Server can consume, whether or not they have been put into memory by a query, and the age of the data pages. I'm sure Gail will come along soon to help out with the specifics, but the short answer to your question is no.If performance is a concern for you, then why are you still using SQL Server 2000?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-14 : 03:00:28
|
"an increase in the amount of content we house"What sort of sizes are we talking about?my general, sweeping statement  , would be that a carefully tuned SQL database will be quick.But "carefully tuned" may involve months of work / lots of expensive consultancy. Particularly if what you currently have is a long way removed from "best practice" - e.g. snippets of SQL bolted together in the application and just "thrown" at the database.If you've got a well written application then it should scale well, and scaling it up will reveal some weaknesses more clearly, which can then be fixed by rewriting the slow-running queries, or adding indexes etc.Throwing a newer version of SQL, and more modern hardware, at the problem will also help of course but that's no substitute for tight-code |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2010-07-14 : 10:06:12
|
| Tara - I have been asked to consider upgrade to SQL 2005/8 as well, if that would help in scaling up. I have to do some exploration about the subject. I would appreciate some good links on the subject if you have any [i.e. those that might not come up with a simple google search]Kristen - Currently our database is ~ 20 GB in size. Our RAM is 7.5 GB and the servers are dedicated MSSQL 2000 Servers. Our applications use stored procedures only. The stored procedures were well tuned [adding necessary indexes, analyzing subtree-cost/io reads/writes] to ensure that most of the time they return results in a few hundred milliseconds. The tables against which the stored procedures run are currently 1-2 GB in size each.There was no specific scale up number. I was just told that they want to scale up. For example, we currently purge data every 4 days, but they prefer to keep it longer and bring more content in. The question was: How much can we scale up before considering hardware or software [i.e. mssql] upgrades and without affecting performance much [as our clients except stored procedures to return non-cached results under a second].Thank you! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-14 : 10:40:48
|
| I don't see any point upgrading to SQL 2005. You will have to do regression testing whatever you upgrade to, you might as well do that once and leapfrog to the latest version (well, maybe not if the latest version only came out yesterday and you are cautious folk!! ... but "within reason")SQL 2008 will use all available RAM, whereas SQL 2000 used a variety of kludges (as was common back then) to access extended memory. Plus you had to have Enterprise version then to access lots of memory, now you are only restricted by the RAM that the O/S can access (so you may not need Enterprise Version of SQL Server). You can also use a 64 bit version. There is probably other hardware-related benefits, but I think those are top of my list.Then there are new features that your application may be able to take advantage of. Maybe you use TEXT / NTEXT datatypes and could (more efficiently) use N/VARCHAR(MAX). Maybe you could use Filtered Indexes - e.g. if you have a lot of indexes on columns where many rows are NULL - then you can exclude the NULL rows from the index and dramatically reduce the size of the index / database.You may also find that ROW_NUMBER() ... OVER offers significantly better performance on Paging ... and there are no doubt other feature-changes to SQL over the last 10 years that will be significant for your application.Even if not: SQL 2000 is extinct!!, so getting support, and knowledgeable people, is going to become harder over the lifetime of your next "phase"If you want to scale up by keeping historical data for longer you could use horizontal partitioning. That should mean that your "current data" queries are unchanged (performance wise) but if you choose to query a wider [date] range you will be able to - at a cost of possibly slightly slower performance. Horizontal partitioning is improved in SQL 2008 compared to SQL2000 - but it was available back then too. No need to change your application either Sounds like you are in good shape if you have well tuned Sprocs already. However, if you have broad "find anything" type search processes there may be a case for changing then to use dynamic SQL under SQL 2008 (using certificates to provide permission safety and thus still only needing Execute permission on the Sproc, rather than Select/etc permission on the underlying table(s)) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-14 : 10:45:39
|
| Possibly of interest: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230 |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2010-07-14 : 18:21:55
|
| Thank you for the useful suggestions and the link. I'll think these over and look at the link and come back with more questions! |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2010-07-19 : 17:29:06
|
| Partially related to this topic - when indexes are rebuilt (DBCC DBREINDEX(@TableName,' ',@Fillfactor) ) in MSSQL 2000, are they taken out of memory?Thank you! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-07-19 : 17:54:51
|
quote:
Originally posted by sql_er
Partially related to this topic - when indexes are rebuilt (DBCC DBREINDEX(@TableName,' ',@Fillfactor) ) in MSSQL 2000, are they taken out of memory?Thank you!
Data pages for indexes or anything else are removed from memory when SQL Server decides it has a need for that memory for something else.CODO ERGO SUM |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2010-07-19 : 17:58:42
|
| So I'll take it as a no ;) Thanks for the clarification Michael! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-07-19 : 19:47:13
|
quote:
So I'll take it as a no
Would be safer to take it as a "yes". Unless your server has a significantly higher amount of RAM than the total size of all the databases on your server, it will page some of it out. Heavy tempdb activity could cause data to move out of RAM even if there's enough room for it. You can monitor the performance counters under SQL:Buffer Manager to catch some of it. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-07-19 : 21:51:12
|
quote:
Originally posted by sql_er
So I'll take it as a no ;) Thanks for the clarification Michael!
Seems like your heart is set on that assumption.CODO ERGO SUM |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2010-07-20 : 09:15:14
|
| Oh - I guess I did not get the full picture. Our RAM is 7.5 GB, but the total size of all dbs is over 25 GB. Yesterday after indexes were rebuilt sql server has slowed down all of a sudden. That is what made me wonder whether data in memory/cache has been (partially?) cleared out, causing the slow down.Looking at the error log I saw that tempdb had to outgrow a few times and there was a lot of locking going on. Not entirely sure what has caused it, as we rebuilt indexes before in the same way and it never happened before.Thank you! |
|
|
|
|
MSSQL indexes always stored in memory?