SQL Server Admin 2014 :: How To Find Memory Usage By Index
Oct 4, 2015I 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?
ADVERTISEMENT
SQL Server Admin 2014 :: How To Find Used Space In Memory Optimized Filegroup
Jun 11, 2015How 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.
SQL In-Memory :: How To Find Memory Usage By Index
Oct 4, 2015i 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?
SQL Server Admin 2014 :: High Memory Is 70% And Growing Fast
Mar 5, 2014My database server memory utilisation is growing faster from past 1 week. it remained same for 1 week around 55% and now it is going to 70% and increasing.
Total OS memory is 32GB and I kept cap for sql server memory upto 29GB. Dont know what to do..
SQL Server Admin 2014 :: In-Memory Processing - Existing Tables
Nov 11, 2014Is 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 RelatedSQL Server Admin 2014 :: Analysis Services Not Taking Allocated Memory?
Mar 14, 2014We have run into an issue on a dedicated SSAS 2012 SP1 server where the allocated memory is not being utilized, causing some slowness in use, connections, and queries.
Total Memory on the server is 512, and after startup, the utilized memory gets up to a max of 60GB and stops there. Checking the Resource Monitor, msmdsrv.exe is only taking around 39GB overall. With the current properties, that should be at 330GB. Am I missing something in the settings or in configuration that should be changed?
Version: SQL Server 2012 SP1 Enterprise (11.0.3000)
OS: Windows Server 2012 Datacenter - Fully patched and up to date
Databases: 2 Tabular models
Server: 512GB RAM
Current memory configuration:
Hard Memory Limit - 0 (Default)
LowMemoryLimit - 65% (Default)
TotalMemoryLimit - 95% (Default is 80)
VertiPaqMemoryLimit - 60% (Default)
VertiPaqPaginingPolicy - 1 (Default)
MemoryHeapType - 2 (Default)
SQL Server Admin 2014 :: Quorum In AlwaysOn And Enabling Memory Optimization?
Nov 26, 2014We are planning to 2014 migration in few days.
ServerA----- ServerA1
ServerB---- ServerB1
In serverA we have 5databases. And making 5databases as availability group. The replica is ServerA1
In server B we have 3 databases. And the making those 3 databases as an availibility group. The secondary replica is ServerB1.
What is the best option to configure the quoram drive in this situation.
Also Server A1 & Server B1 also we use for reporting purposes.
We have some sensitive data. Is it possible to delete the data while reading the data?
How the memory optimization feature work with always on?
SQL Server Admin 2014 :: In-Memory Previous Transaction Aborted Exception
Sep 21, 2015I'm working on a large scale project that is currently in production. We have a big process that recently changed to use In-Memory Tables with SQL 2014 for performance efficiency.
The Process uses:
51 In-Memory SQL Tables.
50 Stored Procedures (not native) that loads data(Insert) from about 150 regular Tables and IM tables.
300 Validations (short stored procedure not native) Selecting from those 50 In-Memory Tables (And insert to In-Memory table that save the validation errors if exists on In-Memory table).
At the end of this process we clean the table from the data that relavnt to etch prosses(DELETE FROM WHERE)
B.T.W
No UPDATE STAT on In-Memory are used-when we test the prosses it slow as down and cause some locks.
We are calling this process from ADO.Net, loads stored procedure first and then validations, each SP use different SQL Connection. In normal use, everything works fine and takes about 1.5 second.
Under stress test (6 Clients X 100 Tasks) for 30 minutes. After several minutes we are starting to get this SQL Exception (1 SQL Exception for every 20 tasks):
41301. A previous transaction that the current transaction took a dependency on has aborted, and the current transaction can no longer commit.
Transactions in Memory-Optimized Tables
The Exception is not clear. We are not using BEGIN TRANSACTION in the process. The SQL Exception occurs in different stored procedures each time.
SQL Server Admin 2014 :: Deadlock Because Of Non-Cluster Index
Jun 11, 2015Dead lock is coming in select query in application because of index. It is identified after enabling trace in database and identified by reading deadlock xml file. After index removal, deadlock is not coming in same query. But it is affecting query's performance slightly. Is it correct way to remove index if dead lock is coming because of index?
View 3 Replies View RelatedSQL Server Admin 2014 :: SSMS - Disable Check For Memory Optimized Tables?
Oct 2, 2014I 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.
SQL Server Admin 2014 :: Reducing Index Fragmentation During Inserts
Apr 26, 2015We have a database with a table that contains around 180m records. Each day a further 70k are inserted. No records are ever deleted as this table is used for archiving only.Users are required to perform SELECTs on this table constantly but due to the high number of INSERTs the indexes become very fragmented very quickly. My aim is to avoid daily rebuilds of the indexes which is what our software house is telling us we have to do.
This is the DDL for the table:
CREATE TABLE [dbo].[Inventory](
[EAN] [bigint] NOT NULL,
[Day] [smalldatetime] NOT NULL,
[State] [int] NOT NULL,
[Quantity] [int] NULL,
[StockValue] [float] NULL,
CONSTRAINT [PK_Inventory] PRIMARY KEY CLUSTERED
[code]...
There are also three clustered Indexes on this table each referencing a single column. The problem from my side is that I cannot understand why the three columns in a primary key would also be configured as non-clustered indexes.My solution would be one of the following:
1. Accept the tables are going to be fragmented and require a daily rebuild (don't like this one!)
2. Partition the table
3. Remove the non-clustered Indexes and let the clustered index for the primary key do the work.
SQL Server Admin 2014 :: Put A Clustered Index On A Date Column
May 18, 2015I would like to put a Clustered Index on a date column in a current heap, but one question/concern.This heap every month has thousands of rows deleted and even more added later. How much of an issue will this cause the Clustered Index as far as page splits? I was thinking Fill Factor of 70%.I would normally just test and still will on Dev box, but my Dev box is much smaller than production as far as power.
View 6 Replies View RelatedSQL Server Admin 2014 :: Columnstore Index On Large Tables
Jul 1, 2015I created columnstore index on the table with 20 columns and about 1000 000 000 rows
every day added about 5M rows
"select" queries became faster because of batch mode and table demand less disk space then before
I have also 6 similar tables with 5 000 000 000 rows and plan to move them on columnstore index
server has 128 G RAM
What pitfalls I could face if I will have so many columnstore indexes on one server?
How a could see problems in DMV?
SQL Server Admin 2014 :: High Availability Index Rebuilds
Jul 9, 2015I have a situation where I need to rebuild indexes on a large DB (500G).
When I do a test run of the rebuilds in my test environment it uses 100G of space - which is fine with me.
When I do a rebuild in my High Availability environment - same DB, same script - it eats up over 600G of space and fills the volume.
What can I do without removing my DB from H/A to rebuild the indexes?
SQL Server Admin 2014 :: Can Use Different Index On Main DB And Mirror Database
Oct 31, 2015We use SQL server always on feather on my database and we distribute statement on main database server and mirror database server for raise performance.
My police for split statement is DML (insert, update and delete) statement go to main DB and Read Data (select) statement go to mirror DB.
I want know can I use different index on main DB and mirror Database?
Because some index are used in mirror DB not used in main database.
SQL Server Admin 2014 :: Giant Logfiles (LDF) During Loading Data Into Memory Optimized Table
Aug 26, 2013I try to load data into a memOpt table (INSERT INTO ... SELECT ... FROM ...). The source table has a size about 1 Gb and 13 Mio Rows. During this load the LDF File grows to size of 350 GB (until the space if the disk is run out of space). The Server has about 110 GB Memory for the SQL Server reserved. The tempdB doesn't grow. The Bucket Size in the create statement has a size of 262144. The Hash key as 4 fields`(2 fields have the datatype int,1 has smallint, 1 has varchar(200). ) The disk for the datafiles has still space for the datafiles (incl. the hekaton files).
How can I reduce the size of the ldf files during the load of the data ?
SQL Server Admin 2014 :: Find All The Inactive Windows Logins?
Jan 30, 2014How to query in SQL to retrieve all the disabled Windows Users which are available as records(only) in the SQL Server ?
View 9 Replies View RelatedSQL Server Admin 2014 :: Stored Procedure - Find The Cause For Blocking
Sep 23, 2015I was trying to create stored proc
[code="create procedure dbo.sp_table1
@idint
as
begin
updatetable1
setisarchived = 1,
[Code] ....
But the query was continuously blocking the query below
updatetable1
setisarchived = 1,
modtime = getdate()
whereid = @id
andisarchived = 0
I was not sure, why the create procedure statement is blocking the update statement.
SQL Server Admin 2014 :: Update Stats After Rebuild And Reorganize Index
Jun 26, 2015We face slow performance issue for like taking long time for same query execution after We apply index rebuild and reorganize index. But, after execution of query or procedure for 2 -3 times, performance will be faster. I have following questions
1 do we need to update stats after we rebuild an reorganize index.
2. is it will be slow for 1-2 times for every query and stored procedure execution after we rebuild and reorganize index?
SQL Server Admin 2014 :: Restoring DB With Full Text Catalog / Index
Aug 6, 2015I need to restore a SQL 2008 db on SQL 2014 instance with out upgrading the database(changing compatibility level).
This database has full text enabled. I see one full text catalog an 2 full text indexes.
Do I need to worry about anything or can I perform a clean restore from the backup with full text import?
Do I need to rebuild the catalog in this case?
SQL Server Admin 2014 :: Rebuild Index Disk Space Requirement?
Sep 15, 2015I'm trying to determine how much space I would need for my data drive and log file drive to do index rebuild. I have a database which is 100gb, it is in simple recovery mode. let me know what to have a look at to determine how much space.
View 7 Replies View RelatedSQL Server Admin 2014 :: Server In Memory OLTP
Apr 24, 2014SQL Server In Memory OLTP 2014?
View 6 Replies View RelatedSQL Server Admin 2014 :: Server Min And Max Memory On Cluster
Oct 9, 2015I have the following SQL FCI configuration:
NODE1 -256GB
INST1 - 64GB min/64GB max
INST2 - 64GB min/64GB max
NODE2 - 256GB
INST3- 64GB min/64GB max
INST4- 64GB min/64GB max
With this configuration and if all instances are running on the same node there will be enough memory for them to run. Knowing that normally i ll have only 2 instances in each node wouldnt it be better the following config?
NODE1 -256GB
INST1 - 64GB min/128GB max
INST2 - 64GB min/128GB max
NODE2 - 256GB
INST3- 64GB min/128GB max
INST4- 64GB min/128GB max
With this configuration and in case all the instances (due to a failure) start running on only 1 node, SQL will adjust all instances to just use Min memory specified?
SQL Server Admin 2014 :: Where To Find Availability Group Fail Over Occurrences
Nov 14, 2014Where can I find dates and times to when an availability group was moved outside of the SQL error log?
View 1 Replies View RelatedSQL Server Admin 2014 :: Replication Could Not Find Or Execute Stored Procedures
Jul 20, 2015Replication-Replication Distribution Subsystem: agent PRODDATA6 failed. Could not find stored procedure 'sp_MSdel_dboActiveTask_msrepl_ccs'.
Replication-Replication Transaction-Log Reader Subsystem: agent PRODDATA6. The process could not execute 'sp_replcmds' on 'PRODDATA6'.
Replication-Replication Distribution Subsystem: agent PRODDATA6. Could not find stored procedure 'sp_MSupd_dboActiveTask'.
Looks like these sp's got deleted?
SQL Server Admin 2014 :: Script To Find Nested Views In A Database
Oct 7, 2015Any easy way to find if there are any nested views existing in the database?...using SQL server 2014...
View 1 Replies View RelatedSQL Server Admin 2014 :: Rule Of Thumb For Sizing Log Space During Index Maintenance?
Sep 13, 2014I've been fixing some issues lately where weekly maintenance has been causing logs to grow and filling disks.
Is there any rule of thumb for allocating log space for doing reorgs and rebuilds in a worst case scenario? I'm thinking 3x the largest database size?
I've been watching them run on databases in the range of 50GB where the logs are growing well over that for rebuilds or even reorgs. Once you have a few databases like this on a server, you can suddenly eat through a lot of disk space just for holding logs during maintenance.
SQL Server Admin 2014 :: 10gb Size Of Index Rebuild When Space Left Overall 5gb
Mar 2, 2015I have 10 Gb index and disk space only left 5gb .
How can i rebuild index ?
SQL Server Admin 2014 :: Find Report Server Database Name?
May 8, 2015Is there any way to find Report server database name using T-SQL?
I have SQL server report server instance and authentication credentials.
I can use sys.databases but in case of user had changed ReportDb name at the time of configuration this approach will not work then How I can find?
SQL Server 2014 :: Find Views Which Has More Than 16 Columns For Unique Index / Constraint
Oct 27, 2015We are on SQL 2014...we have a bunch of views in a database where we are trying to find the views which have more than 16 columns max for unique index/constraint...this is needed so we can convert them to indexed views...
View 1 Replies View RelatedHow To Find Index Size / Usage (mb)
Nov 18, 2004I'm trying to establish the mb usage of a series of nonclustered indexes, I'm used to using the manage indexes GUI in 6.5, and showcontig doesn't quite give me what I want, any suggestions?
View 2 Replies View RelatedIndex Usage - Find Unused Indexes
Sep 13, 2004Can anyone tell me a good way to monitor which indexes are not being used? Over time, I'm sure there are extraneous indexes in our database, which I would like to get rid of.
Any ideas would be appreciated.
Thanks,
Rob
SQL Server 2012 :: Query To Get CPU Usage / Memory Usage Details Of Server?
Jan 30, 2014providing a query for fetching the data for CPU Usage, Memory usage, blocking and all details ...
I want to create a job which will run on a Node every 15 min and store data in a table for each instance...
DMV is not giving more stuff and xtended events not sure if i can store that data into a table?