estimation the buffer cache hit ratio

Hi

I have trouble with MSSQL2000 SP4 (without any hotfixes). During last two
weeks it start works anormally. After last optimalization (about few months
ago) it works good (fast, without blocks). Its buffer cache hit ratio was
about 99.7-99.8. Last day it starts work slow, there was many blocks and
dedlocks. There are no any queries, jobs and applications was added. Now
buffer cache hit ratio oscilate about 95-98. I try update statistics and
reindex some hard used tables, but there is no effect or effect is wery
short (after few hours problem return).

Mayby somene know what it could be?

Is it possible to estimate how each table (using DBCC SHOW_STATISTICS or
DBCC SHOWCONTIG or others) how the table affect on total buffer cache hit
ratio?

Marek

--
www.programowanieobiektowe.plMarek Wierzbicki (marek.wierzbickiiiii@.azymuttttt.pl) writes:

Quote:

Originally Posted by

I have trouble with MSSQL2000 SP4 (without any hotfixes). During last
two weeks it start works anormally. After last optimalization (about few
months ago) it works good (fast, without blocks). Its buffer cache hit
ratio was about 99.7-99.8. Last day it starts work slow, there was many
blocks and dedlocks. There are no any queries, jobs and applications was
added. Now buffer cache hit ratio oscilate about 95-98. I try update
statistics and reindex some hard used tables, but there is no effect or
effect is wery short (after few hours problem return).
>
Mayby somene know what it could be?

I would run Profiler and look for long-running queries. As your amount of
data grows and statistics changes, the optimizer may go for a new plan.

Theoretically, you could also run into that when the amount of data
increases over a threshold value, the memory does no longer suffice for the
typical mix of queries.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Estimation of Full Backup size

Has anyone come across a more accurate method than sp_spaceused to estimate the size of a full database backup for SQL Server 2000 ?

I have found this to have too great a variance (even after running updateusage) to rely on any accuracy for it. I have also looked at perhaps using the ALLOCATED Pages indicated in the GAM pages but this also seems to be pretty inaccurate.

I have a number of servers where space can be limited and backups using Maintenance Plans have occasionally failed because they delete the old backups AFTER they do the latest one. I am writing a script which can check the space remaining and adjust the backup accordingly but the variance I have observed so far with sp_spaceused is too great.

Any ideas welcomed.

Full backups copy all pages within allocated extents. So, there may be a few pages which are not allocated, but which are copied to the backup. Also, there is a small percentage of overhead in the backup format, but this should be predictable.

The most accurate estimate would be to use updateusage and then look at the reserved column of sp_spaceused.

What is the variance that you are seeing? It should be fairly small.

The real bottom line is that if you have servers which you know occasionally run out of backup space, they need to have storage added so that you can ensure the safety of your databases. You can minimize the space requirements by compressing the backups and/or copying them off the server as soon as they are created, but you need the space there.

|||

Thanks for that Kevin

I need to do a few more tests to confirm the variance that I am seeing - it seemed to be inconsistent for a couple of databases (shooting up to 4MB) after being within about 85KB - 90KB for all of the others so there may have been something else happening.

You mention all pages within allocated extents - does that mean that I could use the GAM or SGAM pages to count the number of allocated extents and use this as an estimate? I already have a method of doing this for estimating differential backups using the DIFF_MAP pages.

Martin

|||

The other component of a backup is that the portion of the log created during the execution of the backup is also backed up. This is required in order to get a consistent view of the data at a single point in time.

Depending on the activity level and size of your database, this could easily account for this much variability and more.

Lets look at this from a different angle:

What are your business requirements around backups that are driving your backup plans? If we understand those, we can perhaps better optimize your strategy. I find that the best way to craft a backup strategy is to start from the recovery requirements and work backwards to come up with a plan which will support those needs.

What are the sizes of the databases you are backing up?

Estimation of Backup Time

Any idea, how to calculate estimated time for a DB Backup?
Kind regards
Khurram ShahzadHi
It is very dependent on your hardware and Server Load.
You are going to have to test it yourself.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Khurram Shahzad" wrote:

> Any idea, how to calculate estimated time for a DB Backup?
> Kind regards
> Khurram Shahzad
>
>
>sql

Estimation of Backup Time

Any idea, how to calculate estimated time for a DB Backup?
Kind regards
Khurram Shahzad
Hi
It is very dependent on your Hardware and Server Load.
You are going to have to test it yourself.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Khurram Shahzad" wrote:

> Any idea, how to calculate estimated time for a DB Backup?
> Kind regards
> Khurram Shahzad
>
>
>

Estimation of Backup Time

Any idea, how to calculate estimated time for a DB Backup?
Kind regards
Khurram ShahzadHi
It is very dependent on your Hardware and Server Load.
You are going to have to test it yourself.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Khurram Shahzad" wrote:
> Any idea, how to calculate estimated time for a DB Backup?
> Kind regards
> Khurram Shahzad
>
>
>

Estimating needed size when replicating

Hi,
Can somebody tell how to make an estimation of the size needed when creating a 'consult' DB with snapshot replication ?
Thanks !Five liters.

If you can be a bit more specific, I can be a bit more serious ;)

-PatP|||:rolleyes:
sorry, i ment how much extra size is there needed besides the size of the database itself, like for systemtables and that kind of stuff...|||The overhead within the database is trivial. I'd allow a megabyte or two tops.

Now the distribution server (both database and snapshot file space) can be a very different story!

-PatP|||There is no difference in size between the published database and the resulting snapshot on the subscriber. Subscriber's disk subsystem needs to be as fast if not faster than the publisher's. Depending on the origin of invocation (I hate this style of writing myself, so don't laugh) of the distribution agent subscriber's CPU may take quite a bit of a hit. Network, interestingly, is very often the bottleneck. To minimize its affects, use Alternate location with compression (default location does not support compression option).|||There is no difference in size between the published database and the resulting snapshot on the subscriber. Subscriber's disk subsystem needs to be as fast if not faster than the publisher's. Depending on the origin of invocation (I hate this style of writing myself, so don't laugh) of the distribution agent subscriber's CPU may take quite a bit of a hit. Network, interestingly, is very often the bottleneck. To minimize its affects, use Alternate location with compression (default location does not support compression option).At least on my servers, I get a handful of tables in the publisher that track the replication details (like syspublications, sysarticles, etc). The overhead is trivial, but it is there.

I've never had problems with disk speed... The distributor always takes up the slack for me.

I never use compression with databases. Maybe that's because I got bit so badly years ago, but I've never been able to justify doing it since.

-PatP|||Pat, "years ago" alternate location and compression weren't supported, so I don't know what you're referring to ;)

Estimate DB Size: how to calculate clustered and non-clustered ind

I am doing an estimation on db size based on this link:
http://msdn.microsoft.com/library/en...asp?frame=true
How can I check which tables have clustered index and non-clustered index
fastly?
Thank you!
In addition, noting the sentence "Total Table Size = Data Space Used +
Nonclustered Index + Clustered Index + ...n"
What are the other "...n" factors and how significant (can measure to %?)
are these factors affecting the result?
Many thanks!
|||Get the script I posted at the following link.
http://www.sqlservercentral.com/colu...xdetection.asp
"Jackie Chow" wrote:

> I am doing an estimation on db size based on this link:
> http://msdn.microsoft.com/library/en...asp?frame=true
> How can I check which tables have clustered index and non-clustered index
> fastly?
> Thank you!
|||Check for fragmentation using DBCC SHOWCONTIG.
I will send you a document explaining the use and the Excel Document.
Regards
Andre
"Jackie Chow" wrote:

> I am doing an estimation on db size based on this link:
> http://msdn.microsoft.com/library/en...asp?frame=true
> How can I check which tables have clustered index and non-clustered index
> fastly?
> Thank you!