How to Identify Hot Tables - Microsoft SQL Server

Jeff Roughgarden

Does anyone know how to identify the hottest, most active tables in a
database?

We have hundreds of users hitting a PeopleSoft database with hundreds
of tables. We are I/O bound on our SAN, and are thinking of putting
the hottest tables on a solid state (RAM) drive for improved
performance. Problem is: which are the hottest tables? Would like to
do this based on hard data instead of developer/vendor guesses.

Any suggestions are much appreciated.

Jul 20 '05 #1

Subscribe Reply

3892

John Bell

Hi

You could profile system usage, this will not give tables if you are using
stored procedures, but you could work it out knowing which procedures are
being used. Knowing the procedures being called also helps to put things
into context and allows you to examine query plans.

John
"Jeff Roughgarden" <jr**********@s tanfordalumni.o rg> wrote in message
news:b8******** *************** ***@posting.goo gle.com...

Does anyone know how to identify the hottest, most active tables in a
database?

We have hundreds of users hitting a PeopleSoft database with hundreds
of tables. We are I/O bound on our SAN, and are thinking of putting
the hottest tables on a solid state (RAM) drive for improved
performance. Problem is: which are the hottest tables? Would like to
do this based on hard data instead of developer/vendor guesses.

Any suggestions are much appreciated.

Jul 20 '05 #2

news.mmm.com

My immediate thought was "dbcc memusage" but I looked in the documentation
and it's now obsolete. Too bad. I think that it showed the top objects in
memory, which is almost exactly what you seek.

If I had to approach this myself, I might give some thought to evaluating
the locks taken out. They should give you at least some clue as to what
objects are important to SQL Server (but not all objects locked may be in
memory). I suppose I'd snapshot the lock list periodically and then develop
some processes to evaluate what was locked and summarize the objects locked
and sort of estimate how much storage of each is locked (you have table,
extend, page and row locks to consider). I don't think this will tell you
much about logs, though, which may be critical.

"Jeff Roughgarden" <jr**********@s tanfordalumni.o rg> wrote in message
news:b8******** *************** ***@posting.goo gle.com...

Does anyone know how to identify the hottest, most active tables in a
database?

We have hundreds of users hitting a PeopleSoft database with hundreds
of tables. We are I/O bound on our SAN, and are thinking of putting
the hottest tables on a solid state (RAM) drive for improved
performance. Problem is: which are the hottest tables? Would like to
do this based on hard data instead of developer/vendor guesses.

Any suggestions are much appreciated.

Jul 20 '05 #3

Similar topics

1437

identify what tables are being access in SQL

by: Mike Barlow | last post by:

The database I'm currently working with is very old and some of the tables, SP, and views are not being used. I'm looking for a way to identify what items are no longer in uses, or what items are currently in use.

Microsoft SQL Server

4774

How to identify fragmented index for rebuild

by: xo55ox | last post by:

Hi, I want to find out what is a good way to identify indexes that need rebuilding. I tried to run DBCC showcontig and identify them based on the scan density %. And according to a Microsoft Tech Net - All About SQL Server Indexes discussion, the expert stated that based on the Density (below 20%), I can pick out those indexes associated to the table and run DBCC INDEXDEFRAG. But instead, I ran DBCC DBREINDEX to rebuild all indexes...

Microsoft SQL Server

3070

How to Identify Permissions for SQL Server Tables & Stored Proc. via VB Code

by: Brad H McCollum | last post by:

I'm writing an application using VB 6.0 as the front-end GUI, and the MSDE version of SQL Server as the back-end (it's a program for a really small # of users --- less then 3-4). I'm trying to determine, through the Visual Basic interface, the permissions of each user that's using the application on his/her machine. For example, let's say I'm user "Michael" that's sitting down at my machine using the app. I've written. The security...

Microsoft SQL Server

17345

How identify active control name

by: Woody Splawn | last post by:

I know that I can do the following to identify the parent name of an active control Dim sParentName As String = ActiveControl.Parent.Name But how do I identify the name of the active control? Str = activeControl.Name does not seem to work.

Visual Basic .NET

1579

How to identify the tables in a dataAdapter?

by: Geoff Pennington | last post by:

I have a class method that returns a DataAdapter. I want to access the table(s) contained in the DataAdapter. Of course, accessing the DataSets would be good enough, because I could get the tables from there. I can't find a way to do this. Am I missing something? Much obliged.

Visual Basic .NET

1053

How to identify duplicate records in 2 databases

by: Roy | last post by:

Hi all, My task is to identify duplicate records in 2 tables that each located in different Access database. These tables have exactly the same structure. My immediate response was to create two datasets from each table and loop through one and compare it with another one, but I'm sure there are better way to this. Any suggestions is appreciated. Roy

Visual Basic .NET

3342

How to identify money datatype in ASP.Net

by: Sri | last post by:

In VB, to know the field type of a column stored in a recordset the command I use is If rsQuery.Fields(k).Type = adCurrency Then How will I achieve the same in ASP.net. I could not find a currency data type in asp.net and the type is idenfied as decimal. I have two fields defined in Sql-Server, one is money and other is decimal. In asp.net both are identified as decimal.

.NET Framework

2179

How to identify which column in a table is the primary key

by: Scot L. Harris | last post by:

I am using Postgresql 7.2.4 and php 4.2.2 to write some generic functions to access and manipulate all tables in a database. I have been looking for a way to identify the primary key defined in a table. The closest I have found is a filed in the pg_class table that is a boolean that tells me if that table has or every had a primary key. So given the table name is there a way to identify the column defined as the primary key for that...

PostgreSQL Database

1214

could not determine which columns uniquely identify the rows for ...

by: bazzer | last post by:

im trying to access a microsoft access database from an asp.net webform. but when i use the OdbcDataAdapter config wizard i get the following errors: *Generated UPDATE statement -could not determine which columns uniquely identify the rows for "Customers" *Generated DELETE statement

ASP.NET

9398

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10156

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10007

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

9951

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

9832

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

6649

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5419

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

3924

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3531

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP