How Can I Find What Tables Are Loaded In Memory ?

Jul 23, 2005

I'm doing some performance reviews and wish to know what tables SQL has
pinned in memory and which ones have are loaded through usage ...

Is there a way ?

Thanks,

Craig

View 3 Replies


ADVERTISEMENT

SQL Server 2012 :: How To Find Out If Double Loaded A File Programmatically

Apr 24, 2015

I was just doing a table import task (right click database name/Tasks/Import Data), not knowing my boss had just loaded the same file. it did not warn me that the table currently existed. It just appended the same information to the same table, doubling it. I fixed that one, but, it seems that I might have done this myself in the last couple of weeks, and I'd like to find that table, and there have been a LOT of table loads.

I'm thinking I could get the difference between tables by comparing:

select distinct count(*) from tblname
against
select count(*) from tblname

But how do I incorporate this into some sort of proc that will go through all the tables and let me know where the issue is? I'm swamped and don't have the time to go through each table manually.

I have code that shows me row counts, and have been able to eliminate a few tables from contention, as they are loading monthly data that should only increase minorly month to month, so, no double jumps there.

View 8 Replies View Related

SQL In-Memory :: How To Find Memory Usage By Index

Oct 4, 2015

i want to create a lot of index for my database for performance.but i need find memory usage by indexes.

How to find memory usage by index in sql server?

View 9 Replies View Related

Anything That You Find In SQL Object Scripts, You Can Also Find Them In System Tables?

Jul 26, 2005

I tried all the INFORMATION_SCHEMA on SQL 2000 andI see that the system tables hold pretty much everything I aminterested in: Objects names (columns, functions, stored procedures, ...)stored procedure statements in syscomments table.My questions are:If you script your whole database everything you end up havingin the text sql scripts, are those also located in the system tables?That means i could simply read those system tables to get any informationI would normally look in the sql script files?Can i quickly generate a SQL statement of all the indexes on my database?I read many places that Microsoftsays not to modify anything in those tables and not query them since theirstructure might change in future SQL versions.Is it safe to use and rely the system tables?I basically want to do at least fetching of information i want from thesystem tables rather than the SQL script files.I also want to know if it's pretty safe for me to make changes in thesetables.Can i rename an object name for example an Index name, a Stored Procedurename?Can i add a new column in the syscolumns table for a user table?Thank you

View 4 Replies View Related

SQL Server 2008 :: How To Find Statements That Cause Large Memory Paging

Apr 22, 2015

I am monitoring our production server, and noticed that periodically we have spikes of Memory Paging Rate (pages/sec).

How to find particular queries/stored procedures that causing this?

View 5 Replies View Related

SQL Server Admin 2014 :: How To Find Memory Usage By Index

Oct 4, 2015

I want to create a lot of index for my database for performance.

But I need find memory usage by indexes.

How to find memory usage by index in sql server?

View 1 Replies View Related

DB Engine :: Find Total Memory Allocated To Server And CPU Utilization

Apr 30, 2015

I am searching for query to find total memory allocated to sqlserver,  and how much being used utilized as well cpu utilization percentage . 

View 10 Replies View Related

SQL Server Admin 2014 :: How To Find Used Space In Memory Optimized Filegroup

Jun 11, 2015

How do i find Total allocated space and used space of a memory optimized filegroup?

use memory_optimized_db
Go
select (SUM(size)*8.0)/1024.0 as Space,
FILEGROUP_NAME ( data_space_id ) , type_desc from sys.database_files
group by data_space_id,type_desc;

above query gives "current used size of the container " of memory optimized file group but doesn't give Total space detail.

View 0 Replies View Related

SQL Server 2008 :: How To Find Which Queries / Processes Causing Large Memory Paging Rate

Mar 30, 2015

Our monitoring tool shows that our production system periodically experiencing large rate - up to 800 memory pages/sec. How to find out which particular queries, S.P., processes that initiate this?

View 3 Replies View Related

Pin'ing Tables In Memory

Mar 20, 2007

We seem to remember (but may be dreaming) that one can force SQL Server to permanently keep a table in memory (apart from stuffing more memory into the box). However, we can't find any links/hints on that. Any idea?

View 4 Replies View Related

How To Load Some Tables/views To Memory?

Feb 12, 2008

This is a SQL2000 sp4 Ent. machine.
I was heard that loading certain tables/views (the most frequently used ones) could help in performance.

How could i do that?

thanks
david

View 4 Replies View Related

In-Memory Processing Tables - Limitations?

Nov 11, 2014

Just been reading up on this and there are a lot of limitation which one needs to consider when designing an in-memory table. I'm going to post them below in the hope that others will add to them so I can get a definitive list;

No foreign key constraints

No clustered indexes

No schema changes once the table is set in memory

No index changes once the table is set in memory

Alter Table function is not supported

Additional filegroup needs to be created in order to process in-memory tables

Varchar MAX is not supported

XML/User Defined data types are not supported

Max page length is 8060 (page overflow not supported)

Can't create indexes on NULLABLE columns

View 2 Replies View Related

How To Prevent Memory Overflow If There Will Be Too Much Data In Those Tables

Apr 20, 2015

1. How to estimate how many free RAM I need. If I plan to create five 2 Gb memory optimized tables...10 Gb RAM for data and how many additional memory?

2. How to prevent memory overflow if there will be too much data in those tables? I dont want that my server down entirely

View 3 Replies View Related

TableDiff Out Of Memory Exception On Large Tables.

Sep 20, 2007

Hello,
I hope I am posting this in the right forum.

I am using tableDiff.exe to create a diff SQL script for a very large table (~4 million rows).


After a few minutes, I recieve a "System.OutOfMemoryException".

I have 4GB of ram on the machine executing the table diff.
The server is 32-bit, so adding ram is not an option.

I am executing the following command line:





Code Snippet

TableDiff.exe" -sourceserver "SERVER" -sourcedatabase "SourceDB" -sourcetable "Table1" -destinationserver "SERVER" -destinationdatabase "DestDB" -destinationtable "Table1" -f "C:TableDiffsTable1"

I have seen reports of other users executing tableDiff against 2million row tables.

Is there anyway to buffer tableDiff, so that I do not run out of memory on the server?

Could anything else be causing this error?

Thanks,
Dave

View 3 Replies View Related

SQL Server 2014 :: Memory Optimized Tables And Indexes

Feb 18, 2015

I'm just beginning to experiment with memory optimised tables.

I have two sets of near identical tables - one set normal, the other set memory optimised with DURABILITY=SCHEMA_ONLY - and am running test queries against these. When I say that the two sets are "near identical", I mean that they are the same except for the primary keys: for the normal tables these are defined as PRIMARY KEY CLUSTERED whereas for the memory-optimed ones they are defined as PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=nnnn) as per the requirements for such tables.

I then run a pair of test queries, again identical but one referencing the normal tables and the other referencing the memory optimised ones.

(The query uses an inner join on three tables with row counts of approx 3m rows, 100000 rows and 5000 rows.)

The query against the normal tables runs noticeably faster than that against the memory optimised ones. To try to find out why, I examined the execution plans. the plan for the memory optimised query suggests that I have a missing index: but of course I can't create this againsty a memory optimised table. Is this a bug or am I missing something? Why the performance between the two should be so different?

View 1 Replies View Related

SQL Server Admin 2014 :: In-Memory Processing - Existing Tables

Nov 11, 2014

Is there a method of forcing existing tables into the in-memory filegroup so the table data can benefit from in-memory processing.

View 7 Replies View Related

DB Engine :: In-Memory OLTP Use With Existing Tables / Index / Procedures

Nov 10, 2015

1. I need to make use of in memory engine for my pr-existed develop procedures ,tables ,index.  do I need and code changes for application and how to store tables /indexes in OLTP memory

Assume table index may have primary key index as well.

2. If table with one primary index and 2 foreign constraints, 3 non clusters indexed. which one able o load to memory area and how t do that.

3. In memory is lock free zone. usually locks will happpen in RDMS context . how this works without locks.

View 3 Replies View Related

SQL 2012 :: Memory Optimized Tables And Updatable Column Stored Index

Aug 26, 2014

We are planning to upgrade. We are using Sql 2008R2 now. Which is the better option migrating to SQL 2012 or migrating to 2014?I am thinking 2014 has memory optimized tables and updatable column stored index. So it is better option.

View 2 Replies View Related

Find A Key In All Tables

Oct 28, 2013

I have many tables that have as one of its keys POLICY_NUMBER is there a way to write a script that will look in all tables in a db for a certain POLICY_NUMBER

View 5 Replies View Related

Find Tables With No PK

Jan 27, 2004

I modified my origianl script (used a cursor) to use a looping method I saw in a different thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30286 by Nigel Rivett. Since I used code on this board I though I would post back for the greater SQL Team script toolbox. Feel free to use it to catch those deliquent sql programmers who forget to create a PK. Enjoy.



declare @database varchar(128) ,
@maxdatabase varchar(128) ,
@cmd nvarchar(1000)

create table #pk (databasename sysname, tablename sysname)

select @database = '', @maxdatabase = max(name) from master.dbo.sysdatabases where dbid > 5
while @database < @maxdatabase
begin
select @database = min(name) from master.dbo.sysdatabases where dbid > 5 and name > @database

set @cmd = 'select ''' + @database + ''', o.name from ' + @database + '.dbo.sysobjects o join ' + @database + '.dbo.sysindexes i on o.id=i.id
where o.xtype=''U'' and i.indid = 0'

insert into #pk exec sp_executesql @cmd
end

select * from #pk

drop table #pk


- Eric

View 10 Replies View Related

Find Available Tables In A DB

Mar 20, 2008

I am brand new to using SQL. I created a DB with a bunch of tables there. What I would like to do is from my VB.NET application I would like to allow the user to see what tables are available in that DB.



Also I need a way to check if a table exists. I used the following:

strTemp = "IF EXISTS " & _
"(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'simplesql') AND type in (N'U'))"
cmd.Connection = conncmd.CommandType = CommandType.Textcmdcmd.ExecuteNonQuery()

but I get an exception : Incorrect syntax near ')'

thanks for your help

View 4 Replies View Related

SQL Server Admin 2014 :: SSMS - Disable Check For Memory Optimized Tables?

Oct 2, 2014

I have the following setup:

- An MSSQL 2014 Standard server that houses multiple small databases (in excess of a hundred).
- These databases are frequently dropped and restored by an application that uses this SQL Server.
- There is a business need for this setup at this time, so I can't get away from it. Therefore answers like "don't have so many small databases that are frequently dropped and restored" would be somewhat unuseful

This is the problem I have:

- When I connect SSMS 2014 to the server and expand the "Databases" node, it takes forever to display. In comparison, SSMS 2008 connected to SQL 2008R2 server with the same number of databases displays the Databases tree very quickly.

I ran a trace to see what exactly SSMS 2014 is doing. When the "Databases" node is expanded, it runs a query that checks each database for Memory-Optimized Tables (new and wonderful feature of SQL 2014 for sure, but I'm not using it, at least yet). Naturally, when you have to loop through over a hundred DBs, it takes time. Worse yet, if one of these DBs is in process of being restored, the query sits and waits to time out before proceeding to the next DB. Sometimes this causes outright timeouts. Here is the query:

use [MyDatabase]
SELECT
ISNULL((select top 1 1 from sys.filegroups FG where FG.[type] = 'FX'), 0) AS [HasMemoryOptimizedObjects]

To be sure, this is NOT a SQL Server performance issue. This server processes a rather heavy workload and has been doing so for over a month, and the workload completes within expected time limits or better. Even so I've done some basic performance measuring, and the server itself is quite all right.

Moreover, if I connect SSMS 2008 to it, I get an error message (Index out of bounds or somesuch), but SSMS 2008 does connect, and displays the Databases tree much faster than SSMS 2014.

I'd like to turn off the option to check for Memory Optimized Objects altogether, as I'm not using the feature.

View 3 Replies View Related

DB Engine :: Disk Block Size For Memory-optimized Tables Filestream Data

May 7, 2015

In SQL Server 2014, how big for the block size is better for performance? 64 KB? 4 KB?

For normal database files, best practise is 64 KB disk block size. Not sure if it is same for memory-optimized filegroup.

View 12 Replies View Related

How To Find All The Tables Which Are In Database

Feb 24, 2008

 Hi......i want to find all the tables which are in database. is there any hint or code that can help meThanks in advance

View 4 Replies View Related

Find Tables With Column Name

Oct 16, 2006

How can I find all the tables with a specific column name?

View 3 Replies View Related

How To Find Out Tables Which Can't Be Replicated

Jun 22, 2004

Is there any query to find out all the tables without a Primary key or without a Unique index ?

View 3 Replies View Related

Find Same Coumn In Other Tables

Jun 9, 2008

Is there any way i can find some certain fields in the whole database? like i know a column called Type_ID in table Sys_Type, how can i know there are same Type_ID column in other tables? Thanks in advance.

View 2 Replies View Related

Find All Differences Between 2 Tables

Mar 5, 2015

I have this 2 tables:

Table1: Table2:

ID Value ID Value
--------- ---------
1 2 2 1
2 1 3 4
4 3 4 4I

need to find all differences between the 2 Tables.

A mismatch can be:

- Table1.ID exists, Table2.ID does not exist
- Table2.ID exists, Table1.ID does not exist
- Table1.ID = Table2.ID and Table1.Value <> Table2.Value

So I want to be returned:

ID Value ID Value
--------------------
1 2 NULL NULL
4 3 4 4
NULL NULL 3 4

There must be something simpler than the solution I came up with which is:select * from Table1 a full outer join Table2 b on a.Id = b.Id

WHERE a.value <> b.value or (a.value IS NULL and b.value IS NOT NULL) or (a.value IS NOT NULL and b.value IS NULL)Martin

View 4 Replies View Related

How To Find The Tables In A View

Nov 15, 2006

Dear friends,
is there any way to find the table names from a view?

ex: suppose i have a view named as vorganization.i need all the tables in this view. is it possible to get through a query? same case for procedures and functions also.........

or we have to use sp_helptext?


is there any better way?

thank you very much.

Vinod

View 12 Replies View Related

How To Find No. Of Databases And Tables?

Mar 26, 2008

Hi,
is there any way to find out the no. of databases and
no. of tables in a particular database using a sql query ???

If any server variable is used then please mention that???


thanx in advance

San

View 20 Replies View Related

How Can I Find Connection Between Two Tables???

Mar 19, 2008

Hi,

I have a database with 150 tables.
I need to find connections between specified tables. (I need to find through what fileds and tables are table1 and table2 connected)
Is there any tool in SQL Server for this?

Thanx

View 1 Replies View Related

How To Compare Two Tables And Find The Gap?

Mar 18, 2008



Hi,

We have inventory table which is updated everyday.
I need to find the gap between updated table vs history (which means yesterday's data).
what i did is I just copy the inventory everyday before it is updated.
So, whenver inventory table is updated I want to know the gap between these two (history vs updated) tables.

The structure of two tables are same and here are columns:
date_key (fk)
store_key(fk)
item_key(fk)
begin_inventory_dollars
ending_inventory_dollars
begin_inventory_units
ending_inventory_units

I want to compare each row and if the value is changed I want to have a gap (updated value - history value) and if data is new then just want to add.

Here is tsql: however when I compare to gap between table from excel spreadsheet and this query, this query does not return a right value. (Some are correct but some don't)




Code Snippet
select a.date_date_key,
sum(a.Beg_Inventory_Dollars- (case when b.beg_inventory_dollars is NULL then 0 else b.Beg_Inventory_Dollars end)),

sum(a.Beg_Inventory_Units-(case when b.beg_Inventory_Units is NULL then 0 else b.beg_Inventory_Units end)) ,

sum(a.Ending_Inventory_Dollars- (case when b.Ending_Inventory_Dollars is NULL then 0 else b.Ending_Inventory_Dollars end)),

Sum(a.Ending_Inventory_Units-(case when b.Ending_Inventory_units is NULL then 0 else b.Ending_Inventory_Units end))

from UPDATED TABLE a

left outer join HISTORY TABLE b

on b.item_key = a.item_key and

b.store_key =a.store_key and

b.date_key =a.date_key

group by a.fisc_date_key







Did I miss something in here?
Any kind of advice would be grealy appreciated.
Thanks.

View 4 Replies View Related

Find Max Serial Number From 2 Tables

Feb 11, 2008

I need a query to find max serial number by comparing two different tables. Here is my requirementI am having two tables named Table1 and Table2. Each tables having more than 30,000,000 records.I want a simple query to find Max srno from two tables.For exampleIf Table1 max is 245 where partno=2 and ano=2and Table2 max is 343 where partno=2 and ano=2Then 343 is max serial noIf Table1 max is 435 where partno=2 and ano=2and Table2 max is 34 where partno=2 and ano=2Then 435 is max serial noI used this query but its taking more time  select max(v.MaxSrNo) from ((select max(MaxSrNo) as MaxSrNo from Table1 where partno=@partno and ano=@ano)union all (select max(MaxSrNo) from Table2 where partno=@partno and ano=@ano)) as v Pls give me a simple query to find max srno.

View 3 Replies View Related







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