August 5, 2008 at 9:50 am
Hello there,
our company need to upgrage to SQL Server 2005 from 2000. before upgrading, our consultant need me to provide the following numbers. but I really don't know how to get these numbers.
1. need a transaction volume report made for each table, broken out into reads and writes per table, and row and page size of the hash statistics.
we have more than 100 databases. I only know how to get row and page size per table from sysindexes table.
2. What is the write volume and frequency per database? (avg number of write transactions per second over 5 min intervals in an hour)
3. Need high volume read databases listed out with their traffic volumes (avg number of transactions per second over 5 min intervals in an hour)
thanks in advance.
February 14, 2011 at 5:26 am
I am also interested in the answer to Item 1.
sys.dm_io_virtual_file_stats will give you this information (and more), but only for individual database files, but I need it for each table and, if possible, index.
Ideally, I would also like to retrieve the numbers of selects, inserts, updates, deletes for each table and index in the database.
All help appreciated.
Howard
February 14, 2011 at 7:00 am
I don't have the answer for the OP and I'm guessing she got it since the thread is from 2008, but you can get some of the index usage from sys.dm_db_index_usage_stats. It doesn't have insert, update, and delete but it does have scans, seeks, updates (I'd have to look it up in BOL to tell you what this is), but it should give you some idea of most used tables/indexes (tables would be index_id 1 if it has a clustered index or 0 if a heap). From BOL:
The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.
YOu can also look at sys.dm_db_index_operational_stats for more detail. From BOL:
The data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available. This data is neither persistent nor transactionally consistent. This means you cannot use these counters to determine whether an index has been used or not, or when the index was last used. For information about this, see sys.dm_db_index_usage_stats (Transact-SQL).
The values for each column are set to zero whenever the metadata for the heap or index is brought into the metadata cache and statistics are accumulated until the cache object is removed from the metadata cache. Therefore, an active heap or index will likely always have its metadata in the cache, and the cumulative counts may reflect activity since the instance of SQL Server was last started. The metadata for a less active heap or index will move in and out of the cache as it is used. As a result, it may or may not have values available. Dropping an index will cause the corresponding statistics to be removed from memory and no longer be reported by the function. Other DDL operations against the index may cause the value of the statistics to be reset to zero.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 14, 2011 at 8:07 am
Our company has upgraded SQL 2000 to SQL 2005 long time ago without these questions' answers.:)
February 14, 2011 at 8:50 am
Judy Why (2/14/2011)
Our company has upgraded SQL 2000 to SQL 2005 long time ago without these questions' answers.:)
I had assumed that which is why I answered with 2005+ features. None of the objects I mention are in 2000.
I do wonder why the consultant didn't tell you how to do the information.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply