How To Identify Hot Tables

Jul 20, 2005

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.

View 2 Replies


ADVERTISEMENT

Identify What Tables Are Being Access In SQL

Jul 20, 2005

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

View 4 Replies View Related

Identify If A Tables Has An IDENTITY Column

Apr 1, 2008

Been poking around, but how can I tell if a an identity column exists in a table?

View 10 Replies View Related

Trying To Identify A Unique Record On Two Separate Tables

Oct 31, 2007

Hello,
I am working with a database that among other things uses multipart keys as the unique indexes which are not consistent from say one table where a parent record resides to another table which contains related child records. For example I am working with two tables right now, one that contains content that I'll call Contents and the other which contains Usage information about the contents (number of view, a rating and comments give by a customer) which I'll call ContentsUsage. The system that manages the data for the tables has a versioning system by which, whn a content item is added (first time) a "unique" id (guid) and a version number of 1 is created along with the rest of data items in the Contents table and likewise in the ContentsUsage table (essentially a one to one mapping) on the like named fields in that table. Now, each time a given record in the Contents table is updated a new version, with the same guid is created in the Contents and ContentsUsage table. So one side I have:ContentGUID > AAAAVersion > 1ContentGUID > AAAAVersion > 2And the other table (ContentsUsage)ContentGUID > AAAAVersion > 1ContentGUID > AAAAVersion > 2
While both of these tables have a quasi-unique record (row_id) of type char and stored as a guid neither obviously are the same in the two tables and having reviewed the database columns for these tables I find that the official unique key's for these tables are different (table 1, Contents combines the ContentGUID and Version) as the composite / mutli-key index, while table ContentsUsage uses the RowGUID as it's unique index. 
Contents                                   RowGUID (unique key)ContentGUIDVersionViewsRatingComments................RowGUID ContentGUID (unique key)Version (unique key)Description.....
Bearing this in mind I am unable of course to link directly the two tables by using the just the ContentGUID and have to combine the additional Version to I believe obtain the actual "unique" record in question. The question is in terms of writing queries, what would the most efficient query be? What would be the best way to join the two in a query? And are there any pitfalls with the current design that you can see with the way this database (or specifically these tables are defined)? It's something I inherited, so fire away at will on the critique. Having my druthers I would have designed these tables using a unique key of type int that was autogenerated by the database.
Any advice, thoughts or comments would be helpful.
Thanks,P. 
 
 
 
 

View 8 Replies View Related

SQL Server 2008 :: Identify Columns And / Or Tables From Records

Mar 3, 2015

I'm using MS SQL Server 2008 and I'm trying to figure out if it is possible to identify what tables / columns contain specific records.

In the example below information generated for the end user, so the column headers (Customer ID, Customer, Address, Phone, Email, Account Balance, Currency) are not necessarily the field names from the relevant tables, they are simply more identifiable headers for the user.

Customer ID CustomerAddress Phone Email Account Balance Currency
js0001 John Smith123 Nowhere Street555-123-456 jsmith@nowhere.com-100 USD
jd2345 Jane Doe 61a Down the road087-963258 jdoe@downthe road.com-2108 GBP
mx9999 Mr X Whoknowsville 147-852369 mrx@whoknows.com0 EUR

In reality the column headers may be called eg (CustID, CustName, CustAdr, CustPh, CustMail, CustACBal, Currency).

As I am not the generator of this report, I would like to know whether or not it is possible to identify the field names and / or what tables they exist in, if I were to used the report info to search for it. For example, could I perhaps find out the field name and table for "jd2345" or for "mrx@whoknows.com", because the Customer ID or Email may not be what the actual fields are called.

I'm not a DB admin and I don't have rights to do a stored procedure on the server. I'm guessing what I want is not so simple to do, but is it possible to do via a query?

View 2 Replies View Related

DB Design :: How To Identify Tables That Have Been Made On Temporary Basis Not Used

Sep 9, 2015

In the Operating environment databases, may be made tables in the database on a temporary basis but they are still yet and they are not removed, how to identify tables that have been made on a temporary basis are not used (don’t have any read & write records)? 

View 2 Replies View Related

DB Engine :: Using Metadata To Identify Which Objects Update Tables?

May 6, 2015

we run 2008 std edition (I believe r2 on this server, will double check) and I need to find a needle in a haystack.

I am looking at two tables and would like to learn which, if any objects on same db updates these tables.   I'd even live with any references to these tables.  I already know sql agent is out of the picture.  I'm looking for a static approach, not a monitoring one because its possible updates don't occur till month end.

Is there a way in sql server to query the answer to such a question using dmvs or something like dmvs?   What if I get bold and want to search all db's on this server for objects that update my two tables even across dbs?

In the mean time, i'll look at scripting all triggers and procs , and doing a search on these table names in the scripts. 

I don't know that I've ever seen a way to script all triggers before.  Procs I can easily script from the object explorer details.  But I'm not sure if in explorer details--> server objects --> triggers does what I hope it does.

View 6 Replies View Related

How To Identify Permissions For SQL Server Tables && Stored Proc. Via VB Code

Jul 20, 2005

I'm writing an application using VB 6.0 as the front-end GUI, and theMSDE version of SQL Server as the back-end (it's a program for areally small # of users --- less then 3-4).I'm trying to determine, through the Visual Basic interface, thepermissions of each user that's using the application on his/hermachine.For example, let's say I'm user "Michael" that's sitting down at mymachine using the app. I've written. The security for logging intoSQL Server will be setup using Windows Security (Trusted Connection)as opposed to Windows & SQL Server security. When Michael accesses aparticular form in the VB 6.0 GUI, I want to run some code thatautomatically checks Michael's permission levels on the underlyingtable (actually, a stored procedure supplying the data from the table)that supplies the data to the form he's looking at and then give himsome feedback on the form as to what type of permissions he has whilehe's browsing through the data shown in the form.For example, Michael opens a particular form, code in the backgroundis run to identify that this is Michael accessing the form, the codereturns a value that identifies what type of permissions he has on thedata in the form, and a text box on the form informs Michael (forexample) that he only has read-only permissions to the data he isviewing and cannot edit any of the data.As another example, user Karen sits down at her computer, logs intothe application, opens the same form that Michael just opened, thecode is run in VB to detect the level of permissions she has on thedata being displayed in the form, and the text box on the form informsher that she has editing permissions on the data in the underlyingtable.Etc...If anyone can post an example of the code they use in accomplishingthis task in an application they've written, I'd really appreciate apoint in the right direction or a real-world example that's beenimplemented by one of you. I've written several apps. thus far usingMSDE as the back-end, but the previous apps. I've written were forclients that didn't care about restricting access to theapplication... everyone could pretty much use the application as theydesired and do anything they desired to the data.The current client I'm writing the app. mentioned here for wants tohave security in place to where various users access the applicationwith various levels of permissions to do stuff (or *not* do stuff) tothe data in the application.Thanks very much in advance for any assistance / code provided!Sincerely,Brad McCollumJoin Bytes!

View 1 Replies View Related

How To Identify A Dll

Apr 17, 2008



Hi,

How would I know that a dll component I built is in .NET framework 1.0 or 1.1??

annej

View 4 Replies View Related

Identify Neg And Pos Values

May 7, 2003

How do l select a negative value from a column and set it to 'C' and if its a positive value set it to 'D' for debit

l've written it in layman's terms .Is it wise to use substring or they is a better method......

select
case
when amount is (negative) then 'C'
when amount is (positive) then 'D'
else
end

from table_Tran

View 2 Replies View Related

SHOWCONTIG - Need A Bit Of Help To Identify If

Oct 11, 2007

Greetings.

I'm currently on a company with an ms sql server 2000. I'm looking into the indexes and tables to see if there are some bottlenecks there but the LogicalFramentation is very low in the index I have searched.

However, this table has a logicalFragmentation of 99,9215698242188 which I get when I do DBCC SHOWCONTIG ([TInsurance]) WITH TABLERESULTS. Is that a value to be trusted or not to be trusted since this does not check an index? If it is, how do I defrag a table? I know only how to defrag an index. (example: DBCC INDEXDEFRAG (MFSSEK,[TInsurance], PK_InsuranceID) )

Tipps, suggestions, help, all is very wellcome! :-)



DBCC SHOWCONTIG scanning 'TInsurance' table...
Table: 'TInsurance' (2051694557); index ID: 0, database ID: 17
TABLE level scan performed.
- Pages Scanned................................: 1275
- Extents Scanned..............................: 225
- Extent Switches..............................: 224
- Avg. Pages per Extent........................: 5.7
- Scan Density [Best Count:Actual Count].......: 71.11% [160:225]
- Extent Scan Fragmentation ...................: 74.67%
- Avg. Bytes Free per Page.....................: 520.0
- Avg. Page Density (full).....................: 93.58%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

View 9 Replies View Related

How To Identify An Array

Nov 20, 2006

I have seen several examples explaining the fact that a tablecontaining a field for each day of the week is for the most part anarray. An specific example is where data representing worked hours isstored in a table.CREATE TABLE [hoursWorked] ([id] [int] NOT NULL ,[location_id] [tinyint] NOT NULL,[sunday] [int] NULL ,[monday] [int] NULL ,[tuesday] [int] NULL ,[wednesday] [int] NULL ,[thursday] [int] NULL ,[friday] [int] NULL ,[saturday] [int] NULL)I had to work with a table with a similar structure about 7 years agoand I remember that writing code against the table was pretty close toHell on earth.I am now looking at a table that is similar in nature - but different.CREATE TABLE [blah] ([concat_1_id] [int] NOT NULL ,[concat_2_id] [int] NOT NULL ,[code_1] [varchar] (30) NOT NULL ,[code_2] [varchar] (20) NULL ,[code_3] [varchar] (20) NULL ,[some_flg] [char] (1) NOT NULL) ON [PRIMARY]The value for code_2 and code_3 will be dependently null and they willrepresent similar data in both records (i.e. the value "abc" can existin both fields) . For example if code_2 contains data then code_3 willprobably not contain data.I do not think that this is an array. But with so many rows wherecode_2 and code_3 will be NULL something just does not feel right.I will appreciate your input.

View 1 Replies View Related

How To Identify The Fixpack Level?

Jul 21, 2004

hi,
for sql server 2000, how can we find the fixpack(service pack) level installed on this sql server?
is there any command, or any gui tool to identify the level?

tnks

View 3 Replies View Related

Identify Value In A Column Nullified

Jan 20, 2006

hi,

I have a huge db with many services ,users and applications hitting the db.
Suddenly one of our column is nullified , we are not able track who /how it is done,

Can any one tell be whatz the best way to identify this????
trace(what events to select ), trigger or what????



Thanks,
srini

View 2 Replies View Related

How To Identify SP Used By Front End Application

Sep 24, 2012

there is a Crystal Report run from the Front end Application. The DB used here is SQL Server 2008. I need to know the Stored Procedure used by the report that is been run from the front end. . How shall I do it?

View 6 Replies View Related

Statement To Identify Certain Fields

Sep 19, 2013

I am extract data from one of our Sql servers with the code below

Code:
Select SectDesc, PrGDesc, 'Product Description'=SetDescF, ProdSet, ProdSect, SectChar, Prdct.ProdGrp, OrigPr

From Prdct

INNER JOIN qryPRDGroupDets on Prdct.ProdGrp=qryPRDGroupDets.PGCode

where supersed =''
And OrigPr Not Like '9%' And OrigPr Not Like '%MDM%'
And LISTPR1>'0' And STANCOST>'0'

Which works fine, but what I need to do is reference the "OrigPr" field and mark it as "valid" or "Invalid", the "OrigPr" the field contains alpha numeric data e.g. A000, A001, A002 - ZZ99 and so on, amongst all of the potential different types of codes we have codes that end in treble Zero (0) e.g. A000 which are valid, but if they end in double 00 e.g. AA00 then this is invalid, the problem I have is I can't just add

Code:
'Marker'= Case When Right(OrigPr, 2) = '00' Then 'Invalid' ELSE 'Valid' End

For it will mark the A000 as invalid, is there a way of getting around this...

View 6 Replies View Related

Identify Backup Files

Apr 4, 2008

How to identify the backup files, whether the backup files were taken using SQL 2000 or SQL 2005?


Advance thanks,
Ravi

View 1 Replies View Related

Identify Potential Duplicate

Apr 22, 2015

There is one report to identify potential duplicate in a table and it is performing poor.I'm now tuning the existing SP and got struck in modifiying it. rewrite the query in a best way. I just pasted below an example of query which is now in a report.The report will be run every week currently the table has 10 million records, and every week there will 5k to 10k will be added up so with that 5k to 10 k we have to check all the 10 miilion rows that if it is duplciated the logic is (surname = surmane or forename = forename or DOB =DOB )

Create table #employee
(
ID int,
empid varchar(100),
surname varchar(100),
forename varchar(100),
DOB datetime,
empregistereddate datetime,
Createdate datetime

[code]...

View 7 Replies View Related

SQL To Identify The Primary Key In A Table?

Oct 24, 2007

Hi

I have been looking for a way to identify the primary key defined in a
table using a SQL Sentence...how can i do it?

Thanx for your support!!!

Diego Bayona

View 5 Replies View Related

How To Identify What Consumes Cpu In Sql Server

Jun 1, 2007

Hi all
I use 64 bit 2005 server with 8cpu and 8G of memory.
This server is accessed by large number of intensive or not so intensive programs.
I had eliminated all inefficient queries by means of sql profiler. What I see now is 30 procs or so runining in 1 second. They are all pretty simple and as I said use indexes. cpu column for most show 0, reads show 10 - 50 - pretty good.
But... my cpu utilization is 75% in avg. across of all 8 cpu's. I really can't find an answer for it.
If procs run so efficient, where does cpu go? Disk queue length is 0.04 or less - seems very good.
Task manager shows that all of it 75% attributed to sql server.
So which resources besides sql queries use so much cpu? Do I have to look at some other areas and which ones where cpu could be used besides sql queries themselves.

Thank you, Gene.

View 3 Replies View Related

Need To Identify Column In Resultset

May 10, 2007

Okay, I now have some dynamic SQL working. This is the SQL statement I have for a report in Reporting Services:



DECLARE @SQL nvarchar(4000)

SET @SQL=(SELECT AdHocSQL

FROM RptValueTypeMap

WHERE RptValueTypeMap.SectionCd in ('ITEM0010'))

EXECUTE (@SQL)



We have a table set up that actually holds different SQL statements based on the report items. This is reading the SQL statement from AdHocSQL for the Report item #0010 and it is returning the results. However, it does return the correct value, but under (No Column Name). I have tried to incorporate an "AS", but I get errors when I try this.



I am familiar, but new to SQL statements and I would like this to return a field so I can use this value in the report. What do I need to do?



Thanks for the information.

View 1 Replies View Related

How To Identify A Dialog As Encrypted?

Oct 3, 2006

I am trying to lookup a dialog from conversation_endpoints, however if a dialog was created with the encryption setting to ON and thereis no master Key in the database then the record put in the conversation_endpoints is the same as one without encryption.

How can I distinguish between the one requested with no ecryption and requested with encryption but setup with none due to the lack of a key?

View 2 Replies View Related

Identify Queue That Activated Sp ?

Sep 10, 2006

Hi there

I have multiple queues with the same activated stored procedure (for various reasons we are trying this scenario).

My biggest obsticle is i cannot figure out a way to determine with the activated sp which queue caused it to activate.

Basically i need to make the sp dynamic, so that no matter which queue activated the sp the sp can determine the queue name and use that dynamically to do the receive command from the right queue.

I am sure it is possible since sys.dm_broker_activated_tasks shows how many sp's are activated by each queue, however the sp name is the same for all queues so that does not help me.

How do i determine within an activated sp which queue caused it to activate?

Thanx

View 1 Replies View Related

Union All - Identify Input

Jul 11, 2007

Hello,

I have a Union All component with 7 inputs (and it will grow to 13 inputs shortly). Is there any way to correlate each of the €œUnion All Input X€? with actual inputs?
When I get an incompatible datatype message for one of the input, how do I know which input stream to pick from?

Is this a silly question or it€™s a feature request?

Kapil Kelaiya

View 3 Replies View Related

How To Format Identify Column In SQL Server?

Mar 21, 2008

I don't wnat SQL's Identify column's format ( which is 1�2�3 ...)I want my Prikey column is looks like starts in 0000000001�0000000002�0000000003....I set the Prikey columns type is Char(10) not nullis it possible to setting my identify column as I want?

View 15 Replies View Related

Identify Edition Of SQL Server And Benefits

Mar 31, 1999

Hi

How can I find out which edition of SQL Server is running on my server ?

Also what would be the difference in terms of features between standard edition and enterprise edition ?

Thanks

Vijay

View 1 Replies View Related

Remove Identify Property From A Column

Mar 1, 2002

Hi, I want to know how to remove identify property from a column without recreating the whole table...
When I do it in Enterprise Manager, it actually drop and recreate the table
in background. I just like to know if there is other way without recreating the tables. Thanks!
Xiao Tan

View 4 Replies View Related

How Identify Locking Store Procedure

Dec 30, 2005

:eek: I've have a lot of locks in a SQL Server, and I'd like to identify which SPs are locking what tables, I've being trying with sp_who, sp_who2 and sp_lock, so i can identify the process number, but I don't have any idea what this process is doing (which sp is running? and what command?) and which table is locked by this process, can anybody send me some querys to get this information

I'm novice in SQL Server, and I've not access to the Enterprice manager console, and I've only have priveleges to read data from the database

Thanks for your help
Alfredo

View 8 Replies View Related

How To Copy Good Records And Identify Bad Ones

Sep 24, 2012

In a job of migrating from an old database to a new one (with other structure, other server, other version) i'm copying from the source old tables and inserting into the new destination tables. The problem is that some records have inconsistencies (of any kind) and thus are not inserted due to foreign key, not null, etc validation. When a problem occurs none record is copied! and there is my question: How can i perform the copy in wich it copies the good records (without inconsistencies) and leave aside the bad records. I also want to know wich were not copied and better if in the copy process those were put in a temp table or exported to excel for further analisys o its data.

I'm using this model of "migration":

BEGIN TRY

INSERT INTO DESTINTATION_TABLE (
col_d1,
col_d2,
col-d3,
...)

SELECT
col_s1,
dbo.some_function(col_s2),
col_s3 * 100,
...
FROM SOURCE_TABLE join <other_table> ... where <some filters>

END TRY

BEGIN CATCH
print ERROR_MESSAGE()
END CATCH

(for now, with try/catch I've only get to know the error occurred, if some)

View 5 Replies View Related

SQL 2012 :: Identify Source For Starting A Job

Jul 18, 2014

I have an odd one. I have a SQL job that doesn't have a schedule and is being run each morning. It is a legacy system and I am trying to document the data flow process and I am having a hard time tracking down where/what is starting the job. I see which user executed the job:

SELECT message FROM sysjobhistory WHERE job_id = 'jobid' AND run_date > 'yesterday'

Which is useful, but I want to know what is starting the job.

View 2 Replies View Related

T-SQL (SS2K8) :: Using Like To Identify Value That Contain Alphanumeric Values Other Than A-Z

May 11, 2015

Trying to use LIKE / NOT LIKE to identify values that contain any alphanumeric characters outside of A-Z e.g £%$^&*_-{[@ etc etc

The field should contain only values between A-G with a numberic e.g ABCD1234567... but some rows have characters such as above, some have spaces (weeps) , and some have letters outside the A-G range ....

View 7 Replies View Related

Identify Unused Indexes On Database

May 12, 2015

There are too many indexes built on DB. As per the naming convention it seems the indexes are built as per the suggestions provided from execution plan. I presume most of the indexes are used only once in a month for the reports but are hampering the performance of daily running queries. These are also occupying a lot of space.

To confirm on this I have used the below query to know & identify the unused indexes. I have recorded the counters before and after the huge operations and I observed NO CHANGE in any of the values.

What the below values exactly indicate and when do they change? Is it good to delete the indexes having low USER_SEEKS, USER_SCANS, USER_LOOKUPS?

Query:
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME],
USER_SEEKS,
USER_SCANS,
USER_LOOKUPS,

[Code] .....

View 2 Replies View Related

Identify Change Points In A Column

Apr 18, 2008

DECLARE @EffLevels TABLE (ChangePoint int, Value Int)

INSERT@EffLevels
SELECT'1000', '767' UNION ALL--Changed
SELECT'1000', '675' UNION ALL
SELECT'1001', '600' UNION ALL--Changed
SELECT'1001', '545' UNION ALL
SELECT'1001', '765' UNION ALL
SELECT'1000', '673' UNION ALL--Changed
SELECT'1002', '343' UNION ALL--Changed
SELECT'1002', '413' UNION ALL
SELECT'1002', '334' UNION ALL
SELECT'1001', '823'--Changed

-- My Result should be
-- ChangePointPrevChangePointValue
-- 1000Null767
-- 1001 1000 675
-- 1000 1001 765
-- 1002 1000 343
-- 1001 1002 823

Any suggestion ?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved