sql server 2000: most frequented tables -> script wanted

hallo,

could someone please provide me a script, which throws the most frequented 
tables used within a sql server 2000 database?

i am aware of one (see below) for sql server 2005, but due to new 
functionalities within sql server 2005 it can not be backported.

SELECT TOP 20
db_name(database_ID),
object_name(object_id),
leaf_insert_count,
leaf_delete_Count,
leaf_update_count,
range_scan_count,
row_lock_count
FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL) WHERE 
db_name(database_ID)='<Datenbank-Name>'  ORDER BY leaf_update_count desc   
--oder jede andere Spalte, die man weiter analysiert haben möchte

the background is: i would like to run "Update Statistics <table-name> With 
FullScan" for the top 20 frequented tables of one specific database within 
sql server 2000.

-- 
thanks in advance / danke im voraus, fritz
-- 
Fritz Theiss
0
Utf
12/8/2009 12:40:01 PM
sqlserver.server 1327 articles. 0 followers. Follow

3 Replies
644 Views

Similar Articles

[PageSpeed] 29

Fritz
I have been using SQL Server Profiler to capture such info whne I was on SQL 
Server 2000



"Fritz Theiss" <FritzTheiss@discussions.microsoft.com> wrote in message 
news:0AF91989-1DE9-4646-9D37-503818E3AB6A@microsoft.com...
> hallo,
>
> could someone please provide me a script, which throws the most frequented
> tables used within a sql server 2000 database?
>
> i am aware of one (see below) for sql server 2005, but due to new
> functionalities within sql server 2005 it can not be backported.
>
> SELECT TOP 20
> db_name(database_ID),
> object_name(object_id),
> leaf_insert_count,
> leaf_delete_Count,
> leaf_update_count,
> range_scan_count,
> row_lock_count
> FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL) WHERE
> db_name(database_ID)='<Datenbank-Name>'  ORDER BY leaf_update_count desc
> --oder jede andere Spalte, die man weiter analysiert haben m?chte
>
> the background is: i would like to run "Update Statistics <table-name> 
> With
> FullScan" for the top 20 frequented tables of one specific database within
> sql server 2000.
>
> -- 
> thanks in advance / danke im voraus, fritz
> -- 
> Fritz Theiss 


0
Uri
12/8/2009 12:52:47 PM
to my knowledge there is no way to do what you ask.

-- 
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Fritz Theiss" <FritzTheiss@discussions.microsoft.com> wrote in message 
news:0AF91989-1DE9-4646-9D37-503818E3AB6A@microsoft.com...
> hallo,
>
> could someone please provide me a script, which throws the most frequented
> tables used within a sql server 2000 database?
>
> i am aware of one (see below) for sql server 2005, but due to new
> functionalities within sql server 2005 it can not be backported.
>
> SELECT TOP 20
> db_name(database_ID),
> object_name(object_id),
> leaf_insert_count,
> leaf_delete_Count,
> leaf_update_count,
> range_scan_count,
> row_lock_count
> FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL) WHERE
> db_name(database_ID)='<Datenbank-Name>'  ORDER BY leaf_update_count desc
> --oder jede andere Spalte, die man weiter analysiert haben m�chte
>
> the background is: i would like to run "Update Statistics <table-name> 
> With
> FullScan" for the top 20 frequented tables of one specific database within
> sql server 2000.
>
> -- 
> thanks in advance / danke im voraus, fritz
> -- 
> Fritz Theiss 


0
TheSQLGuru
12/8/2009 9:53:27 PM
nobody?

-- 
thanks in advance / danke im voraus, fritz
-- 
Fritz Theiss



"Fritz Theiss" wrote:

> hallo,
> 
> could someone please provide me a script, which throws the most frequented 
> tables used within a sql server 2000 database?
> 
> i am aware of one (see below) for sql server 2005, but due to new 
> functionalities within sql server 2005 it can not be backported.
> 
> SELECT TOP 20
> db_name(database_ID),
> object_name(object_id),
> leaf_insert_count,
> leaf_delete_Count,
> leaf_update_count,
> range_scan_count,
> row_lock_count
> FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL) WHERE 
> db_name(database_ID)='<Datenbank-Name>'  ORDER BY leaf_update_count desc   
> --oder jede andere Spalte, die man weiter analysiert haben möchte
> 
> the background is: i would like to run "Update Statistics <table-name> With 
> FullScan" for the top 20 frequented tables of one specific database within 
> sql server 2000.
> 
> -- 
> thanks in advance / danke im voraus, fritz
> -- 
> Fritz Theiss
0
Utf
12/9/2009 8:33:01 AM
Reply:

Similar Artilces:

I want year in one table to be less or equal year in another table
Hi I have some problems writing a query and I hope someone can help me. I have a database with serveal tables. In one table I have this information, Lake ID-number, treatment, year for treatment. In another table I have Lake ID-number, fish species (I am intrested in pike), year when pike is present. I want to find all lakes that have pike present before the treatment was done, I want the year in the second table to me less or equal the year in the first table. Is there a easy way to do this? Thanks Try something like this substituting your table and field names. S...

Double Counting in Pivot Tables
I need help :( The data in my spreadsheet is organized as shown below with each numbe representing a column 1. Customer Name 2. Name of Customer's Company 3. Name of specific project with customer (for simplicity think Projec x, Project y...) 4. Internal Consultant assigned to specific engagement (Jim, Jane...) 5. Customer satisfaction rating with our company (Good, Very Good...) My problem is that I need to sort the data in various ways and I a running into a double counting issue. For example, three interna consultants may be assigned to Project x and the customer satisfactio r...

Exchange 2000 DST Changes
How is one supposed to address the DST changes for Exchange 2000 if they do not have extended support to get the patch? Will Microsoft be providing a workaround like they did for Windows 2000? I'm referencing the following article: http://support.microsoft.com/kb/930879 It states : What to do before you run the Exchange tool Install DST updates Before you run the Exchange tool, make sure that client and server computers are updated correctly with the operating system and application DST updates. These updates must be installed in the following order: 1. Install the Windows DST upda...

Restoring Exchange 2000 cluster to "standard" Exchange 2000 Enterp
Hi all, At the moment we are creating a "full" test environment built from backup tapes from our live environment. DC's are all up and running. Our 2000 native forest exists out of a top domain and 2 child domains. All DC's are running 2000 SP3 as well as the Exchange servers. Exchange servers are located in the child domains. In one of the child domains which is NL.top.net we have an Exchange cluster, 2 nodes active/active eg. These would be EX01 and EX02 and running EX04 and EX05. Since we unfortunately do not have a SAN in our test site I will not be able to do a DR...

Outlook 2000 send/receive
I am having troubles trying to get Outlook 2000 to automaticly send/receive e-mails...any help? I am not sure if it is what you are looking for but here goes: Under the Tools menu, click Options. In the dialog box that opens next click on Mail Delivery tab, then make sure there is a check in the box beside "send messages immediately when connected." <anonymous@discussions.microsoft.com> wrote in message news:14fdf01c41cac$9696e2c0$a001280a@phx.gbl... > I am having troubles trying to get Outlook 2000 to > automaticly send/receive e-mails...any help? ...

Have Outlook 2000. How do I get rid of spam?
I need step by step directions because I don't know the termenology. When we open Outlook it comes up under my husband's e-mail. Then I Open mine. He is not spamed. I get hundreds each day. HELP! winnieB <winnieB@discussions.microsoft.com> wrote: > I need step by step directions because I don't know the termenology. > When we open Outlook it comes up under my husband's e-mail. Then I > Open mine. He is not spamed. I get hundreds each day. HELP! Step by step: 1) Open Internet Explorer. 2) Browse to http://spambayes.sourceforge.net/ 3) Downoad SpamBaye...

Microsoft CRM server is not responding
Hi, Workflow Manager write: The specified Microsoft CRM server is not responding. This might happen if it is currently unavailable, it is not a Microsoft CRM server, or you are not a valid user. Fro more information, contact your system administrator. How fix it? In IE i can logon to CRM. ----- wbr, Eugene I've resolved this problem by restoring the metabase with the original version in IIS Regards Edgard Eugene wrote: > Hi, > > > Workflow Manager write: > > The specified Microsoft CRM server is not responding. This might happen if > it is currently un...

Pass Through to SQL Server Optimization Questions
On an autoexec function several recordsets are opened. Some are queries to local tables, some are to sql server tables. I have been trying to optimize performance but I haven't seen much difference in speed between queries run directly against linked SQL Server tables vs pass through queries. Here is my code for creating the pass throughs (I have another function OpenRecordSetLocal that handles queries to tables within the frontend. The ODBC connection string resides in the query Q_PassThrough. Am I missing something key? Is there a better way to do this? Thanks. Function OpenRecord...

How do I convert a word table into an excel document?
I have managed to get the info accross no problem but the formatting is all over the place. For instance - 07/10 meaning July 2010 is appearing as 07/Oct despite me going into format cells custom then enter mm/yy which has always worked previously. Any ideas? You can't use it like that regardless of formatting, you need to put in the whole date or else Excel will always assume the current year so any real date used for calculations needs to be numeric and needs a day, so you can enter (assuming US date format) 07/01/10 and use a custom format of mm/yy or if you don't need it for...

how do I change the table from which a form obtains data
I have a form that I have set up, but I would like to re-create it several times and change the table that each copy gets data from. EG Table 1 feeds Form A. I want to copy this form to create form B but it currently gets data from table 1, so I need to change it so that it now gets data from table 2. That's probably clear as mud but hopefully someone can help me! thanks Jo, If all the tables have exactly the same fields, you just need to change the Form's RecordSource. Copy the form to another name, and change the RecordSource table, or more usually, a query against the...

Dropped connection to POP3 servers
Just installed Office 2003 under XP Home. Outlook is dropping connection to POP3 servers, I check 2 different servers with a S/R. Both fail at the same time, never separately. Sometimes I can exit Outlook gracefully but a reboot is required to get Outlook to work again. Sometimes locks entire system and have to powerdown manually. Pattern not fully determined but it appears to happen when there is other activity to the internet that is going to specific servers using an ID and password. I use Medved Quotetracker with E*Trade quotes (requires ID). If I bring up Quotetracker, Ou...

how to config Server for one user which has 2 mail address.
we have one NT4.0 + Exchange 5.5 Server "A" Email abc@xxx.com.cn ��and another windows2003 + exchange 2003 Server "B" abc@cn.xxx.com user name is same in A B��Mx record was be set ,question is how to config Exchange 2003 to receive down the mail to Server A , because of we need to replace A with B. thanks a lot. E2K3 setup guides you through common scenarios (like yours) pretty much holding your hand - on welcome screen tell it that you have existing E55 org and want to join it, then follow on-screen instructions. Having usable backup of Exchange databases and AD i...

help with dynamic tables
This is a bit complicated to explain but I'll try my best. In columns A, B, C I have different drop down lists. Column A has Store1, Store2, Store3, etc. Column B has Dept1, Dept2, Dep3, etc. Column C has ProductA, ProductB, ProductC. As of right now, these lists are not dependent on each other, I can choose anything from any list regardless of the previous category. Also, the length of these lists is undefined, meaning I will constantly be adding to them in sequential rows below. And then columns D and beyond have data such as Sales, Profits, # of items, etc. What I...

Exchange Server 2000 Secuirty Setup problems
Dear I have apply wrong setting to our exchange server 2000 . The wrong setting as from exchange manager . mail store , I have apply everyone send as and receive as access right, the group included remote access via offline sync. This setting once apply , all user access other mailbox without apply access right. I have try disable this setting, after all local office user no problems, but a remote offline sync laptop user does not work . The error as below: Unable to display the folder. Microsoft Outlook could not access the specified folder location. The file C:\exchange\shawnb.ost cannot b...

How Can I Control When Messages are Downloaded from Server
I have the box "Check for messages every ___ minutes" unchecked (tools/options/general). Yet seemingly randomly, messages get downloaded a couple of times a day without my clicking "Sync." This is problematic due to the way I share the computer. Is there another setting I need to keep this spontaneous downloading from occurring? "Gripper" <Gripper@discussions.microsoft.com> wrote in message news:F22A9D3A-AFB5-4C85-B94B-79B109E4829E@microsoft.com... > I have the box "Check for messages every ___ minutes" unchecked > (too...

Publishing Layout and tables
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) I am trying to copy and paste text from one cell of a table to another cell in the same table. The document is in Publishing Layout. The paste command deletes the text in the destination cell and then places a big empty text box on top of the table. I do dozens of these documents that are primarily tables and graphics. Previously I used Publisher on my old PC. Should I go back, or can this be done in Word for the mac? Hello, On 2010.01.29 8:44 AM, in article 59bb1ce2.-1@webcrossing.JaKIaxP2ac0, "Toni_T@officefor...

Insert,Update Data in sage (MS Access Linked tables) using Vb.net form
Hi folks, I am developing application using vb.net which requires integration with SAGE LINE 50 (Accounting software ) V11... The data which SAGE is using is MC ACCESS 2003 database... with linked tables in it... Now I Have developed the Sage connection using ODBC which works fine when reading the record but cannot Add or Update record into the Linked tables.... When i debug the program the error is at the line where it has... <br> MyodbcCommand.ExecutenonQuery() <br> Can anybody Help ????? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acce...

converting tabular structures in a Word document into an actual table or reading data from the tabular structures using VBA code
I have a macro which can read the last cell/column of all tables in a Word 2003/2007 document and store the data in an MS-Access table. But, some Word documents have the data in structures like a table format but are not actually tables. The structure looks like a table, but the table borders are actually line connectors. These documents were created by a software(VeryPDF PDF to Word converter) which converted the PDF documents(the original format these documents were) into Word documents. 1. Is there a way I can convert/replace the tabular structures with actual tables in Word so t...

I want to trace the precedents in a formula when they are on mult.
Multiple spreadsheets in a workbook. When I click on formula only shows precedent on 1st sheet I want to see on rest of sheets Paul wrote: > Multiple spreadsheets in a workbook. When I click on formula only shows > precedent on 1st sheet I want to see on rest of sheets Unfortunately it doesn't. That's something I would like to see as well. -- Registered Linux User no 240308 Just waiting for Broadband to complete the conversion!(3 weeks and counting!) gordonATgbpcomputingDOTcoDOTuk to email me remove the obvious! Depending on what version of excel (xl2002 in front of me),...

Excel 2000 vs. Excel 2002
I am having troubles with a workbook that I created that is havin problems opening. I created it in 2002, and it opens fine in Excel 2002 for other people However, when I send it to someone who has Excel 2000, it takes over a hour to open. Now I also made a very similar report that works just fine whe trasferred to excel 2000. Here are a couple of stats on the workbook that is having problems: 1.5mb 500+ externel links 500+ subtotals 200+ simple calculations (a1+b1; a1/b1;etc..) 1 Worksheet in the book. 2 columns with conditional formatting Thanks, Joh -- Message posted from http://ww...

Querying multiple records in two tables
Hi, in my database I have tables for users (UserID, UserName), projects (ProjectID, Project Name), and qualifications (QualID, QualName). I have join tables for users_qualifications (UserID, QualID), and projects_qualifications. (ProjectID, QualID). What I need to do is run a query for a project to show which users have the exact matching qualificiations. Users can have many qualifications, projects can require many qualifications, users may only work on a project if the qualifications required/held match exactly. Please help. Assuming that ProjID, and QualID are numeric, the following sho...

Sql Agent Error
After installing CRM we are receiving the following error. "An error occurred when attempting to set the SQLSERVERAGENT service to AutoStart: System.ComponentModel.Win32Exception: The specified service does not exist as an installed service at Microsoft.Crm.Setup.Common.Utility.ServiceUtility.SetServiceToAutoStart(String serviceName, String machineName) at Microsoft.Crm.Setup.Server.ServiceRunningValidator.AutoStartLocalService(String serviceName, TimeSpan waitTimeForStart) We have CRM on a SBS 2003 machine and SQL EE 2000 on a W2K3 machine. Both are DCs the SBS holds all FS...

DPM and VMM on same server using side-by-side SQL installation
Windows 2008 Std R2 Server, 64bit VMM 2007 is currently installed (with the built-in default database) SQL 2005 Express Edition, SP3, 32bit. I was advised by Microsoft that both VMM and DPM could be installed on the same server (small DR environment) The VMs are hosted on a Windows 2008 Enterprise Server w/ VS2005R2 - because the hardware didn't support HyperV. VMM works great to manage the Windows 2008 VS2005R2 Host running Win2K3 server instances ... after lots of trial and error. Now, I need to install DPM 2007 onto the same server... DPM wants to install the 64bit vers...

Excel pivot table #2
i encountered an error in my pivot table. i created an olap cube using the analysis manager. the cube displays the correct data of my measures but on my pivot report, it displays #N/A.... i need help to fix this one... thanks.... =) ...

email
How can I change the time that it takes for message to shift from BOLD (unread) to PLAIN TEXT (as read) in my inbox. If I open MS OUTLOOK it will change within 30 seconds to PLAIN TEXT from BOLD appearing as read, even if I don't want to read them at that time. Therefore, later, I don't know if I read that one or not. I have seen the time limit change on the setup somewhere, but can't find it now! joe@dixiesteamway.com Outlook does not have this option to mark an email as read when viewing in the Preview pane. Only Outlook Express does. "Joe Dobbins" <joe...