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


ADVERTISEMENT

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 Admin 2014 :: Giant Logfiles (LDF) During Loading Data Into Memory Optimized Table

Aug 26, 2013

I 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 ?

View 9 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

SQL Server 2014 :: Remove Memory Optimized Filegroup

Apr 28, 2015

I read this: [URL] ....

Which says you must drop the database to remove the filegroup.

I deleted all the objects and then backed up the DB and restored it and the filegroup is still there.

I was skeptical but some of the comments made me think this might work.

Do I really have to restore from a pre-memory optimized state?

View 3 Replies View Related

SQL Server 2014 :: Automating Random Inserts Into A Memory Optimized Table

Jan 28, 2015

I have this table

CREATE TABLE [Sales].[Test_inmem]
(
[c1] [int] NOT NULL,
[c2] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ModifiedDate] [datetime2](7) NOT NULL CONSTRAINT [IMDF_Test_ModifiedDate] DEFAULT (sysdatetime()),

[Code] ....

I have to generate 1000000 random records into it. I tried various ways to insert records, but not being a developer could not do it. I hope to make the C1 as a serial number, C2 can be anything, C3 I want to be the timestamp.

View 3 Replies View Related

SQL Server 2014 :: Memory-optimized Queries Using Table Scan Instead Of Seek?

Sep 19, 2015

I've been having some trouble getting a single-column "varchar(5)" field to reliably use a table seek instead of a table scan. The production table in this case contains 25 million rows. As impressive as it is to scan 25 million rows in 35 seconds, the query should run much faster.

Here's a partial table description:

CREATE TABLE [dbo].[Summaries_MO]
(
[SummaryId] [int] IDENTITY(1,1) NOT NULL,
[zipcode] [char](5) COLLATE Latin1_General_100_BIN2 NOT NULL,
[Golf] [bit] NULL,
[Homeowner] [bit] NULL,

[Code] .....

Typically, this table is accessed with a query that includes:

SELECT ...
FROM SummaryTable
WHERE ixZIP IN (SELECT ZipCode FROM @ZipCodesForMO)

This query insists on using a table scan. I've tried WITH (FORCESEEK) for example, but that just makes the query fail.

As I've investigated this issue I also tried:

SELECT * FROM Summaries WHERE ZipCode IN ('xxxxx', 'xxxxx', 'xxxxx')

When I run this query with 64 or fewer (actual, valid) ZIP codes, the query uses a table seek.But when I give it 65 or more ZIP codes it uses a table scan.

To summarize, the production query always uses a table scan, and when I specify 65 or more ZIP codes the query also uses a table scan. I'm wondering if the data type of the indexed column (Latin1_General_100_BIN2) is somehow the problem. I'll likely try converting the ZIP codes to an integer to see what happens.

View 9 Replies View Related

SQL Server Admin 2014 :: SSMS Will Only Run As Administrator

May 28, 2015

After installing SSMS on some computers - the only way we can get SSMS to run correctly is to run it as the administrator. Is there a way where you don't have to do that? These end users are logging as themselves and have accounts in SQL Server all set up - but SSMS will only launch for them if we right click and select "run as administrator".After doing some digging - it seems that this is a common problem out there.

View 3 Replies View Related

SQL Server Admin 2014 :: Restriction On DML Statements In SSMS

Apr 27, 2014

I would like to know if there is any option to Restrict DML statements in SSMS for a user where the same user should be able to perform these actions through application on particular database.

View 1 Replies View Related

SQL Server Admin 2014 :: SSMS - The Application Cannot Start

Jul 4, 2014

Has experienced this error when trying to open SSMS? Was working fine earlier. I have tried uninstall/reinstall, a few reboots,repairing the install, etc.

View 5 Replies View Related

SQL Server Admin 2014 :: How To Disable Reboot Pending Through Command

Mar 16, 2015

Any command through that can I delete/disable the DWORD value "Pendingfilerename operations" so that while I start the SQL Install work, it does not fail due to this and can write this as one of the precheck options.

View 0 Replies View Related

SQL Server Admin 2014 :: SSMS Occasionally Freezing / Hanging

Jul 7, 2015

I have a weird intermittent issue with an enterprise version of SS2014. When clicking or right clicking around SSMS will lock up and display the 'SSMS is busy - waiting for an internal operation to complete'. It is only specific to the server as when I connect using my local SSMS this doesn't happen. This was happening both pre and post SP1 install.

View 4 Replies View Related

SQL Server Admin 2014 :: Deny SSMS Connections From Remote Servers?

Nov 20, 2014

Is there any way I can deny connection from SSMS only from remote servers?

View 7 Replies View Related

SQL Server Admin 2014 :: SSMS Restarts Daily On Remote Desktop?

Dec 11, 2014

I'm using ssms 2014, but got the same problem with 2012. I use ssms almost entirely on remote desktop sessions ( Windows 7, Server 2008R2, Server 2012 ). It may be related to having filtered job activity monitor windows open for hours, but about once per day ssms fails, and has to restart. Upon resuming usually only one of several queries is restored.

View 8 Replies View Related

SQL Server Admin 2014 :: Permissions To Debug Stored Procedures Using SSMS?

Jun 25, 2015

What permission is required to run debug feature in SSMS(debug Stored Procedures). This is a development machine and developer requested for this.

EXECUTE permission was denied on the object 'sp_enable_sql_debug', database 'mssqlsystemresource', schema 'sys'.

EXECUTE permission was denied on object 'sp_sql_debug', database 'master'.

Is there any option other than giving sysadmin privilege on SQL?

View 0 Replies View Related

SQL Server Admin 2014 :: High Memory Is 70% And Growing Fast

Mar 5, 2014

My 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..

View 9 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

SQL Server Admin 2014 :: Analysis Services Not Taking Allocated Memory?

Mar 14, 2014

We 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)

View 2 Replies View Related

SQL Server Admin 2014 :: Quorum In AlwaysOn And Enabling Memory Optimization?

Nov 26, 2014

We 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?

View 8 Replies View Related

SQL Server Admin 2014 :: In-Memory Previous Transaction Aborted Exception

Sep 21, 2015

I'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.

View 2 Replies View Related

SQL Server Admin 2014 :: User Database Integrity Check Fail

Mar 8, 2015

I've been running the Ola Hallengren maintenance script for the last five months without missing a beat. Today I find an error stating the UserDatabase Integrity check job failed last night. This is running on SQL Server 2014 BI edition w/64 Gigs.

I ran a DBCC CHECKDB on each database manually and all worked until I tried it on the biggest one that is about 18 gbs. It just keeps running and I eventually stopped it so I'm guessing it is memory, but doesn't make sense considering it has 64 gbs. I have it set to 64/4 max / min. Again, this was never an issue until last night.I've been looking up all morning, but not seeing much on this error "The operating system returned error 1453"?

View 5 Replies View Related

SQL Server Admin 2014 :: Check If UNC Path Exists (It Is Folder - Not File)

Oct 29, 2015

usual way to check if file exists

DECLARE @File_Exists INT
EXEC Master.dbo.xp_fileexist 'serverBSQL_Backupfile.bak', @File_Exists OUT
print @File_Exists
1

And if check folder, can use "nul", but it doesn't work for UNC path

DECLARE @File_Exists INT
EXEC Master.dbo.xp_fileexist 'serverBSQL_Backupul', @File_Exists OUT
print @File_Exists
0

If use xp_subdirs like:

EXEC master.dbo.xp_subdirs 'serverBSQL_Backups'

If the folder doesn't exists, Msg 22006, Level 16, State 1, Line 3 xp_subdirs could not access 'ServerBSQL_Backups*.*': FindFirstFile() returned error 67, 'The network name cannot be found.'

how to check if UNC folder exists in Backup? in my code I want to check if the unc folder exists before doing backup, the unc path is retrieved from other table or backup history.

View 6 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

SQL Server Admin 2014 :: Cannot Connect To Named Instance (2nd Instance) From Local SSMS

Jul 22, 2015

I've two instances(Default, Named[dynamicsFINANCE]) running on SQL server 2014. However, when I try to connect to named instance say (dynamicsFINANCE) using SQL authentication from local SSMS, I get below error message:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

I assigned a static port number to the named instance [dynamicsFINANCE] 1450. I also setup the firewall rule to allow access to Port 1450.

View 5 Replies View Related

SQL Server Admin 2014 :: Running Frequently Transaction Log Backup During Integrity Check DB

Jul 6, 2014

Running Frequently Transaction logbackup during Integrinty check DB /optimization job will cause any issue /impact as duration will extend ...

View 2 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

SQL Server Admin 2014 :: Server In Memory OLTP

Apr 24, 2014

SQL Server In Memory OLTP 2014?

View 6 Replies View Related

SQL Server Admin 2014 :: Server Min And Max Memory On Cluster

Oct 9, 2015

I 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?

View 6 Replies View Related

SQL Server Admin 2014 :: Rebuild CMS Tables From XML File?

Jul 23, 2014

I had to reinstall my local copy of SQL a few weeks ago, which naturally overwrote the

msdb.dbo.sysmanagement_shared_server_groups_internal and
msdb.dbo.sysmanagement_shared_registered_servers_internal tables.

However I still have the local XML file that SSMS reads so I can still access the groups, I just get weird errors when trying to re-register my install as the new CMS. How to rebuilt those tables from the XML file or know of a way to repopulate?

View 3 Replies View Related

SQL Server Admin 2014 :: Storing Very Large Tables?

Jan 24, 2015

We are in the middle of re-designing few tables (namely transaction tables) that would store very large data and would be hosted on cloud (Azure). The old design of this product breaks transaction tables into monthly tables. i.e. say ORDERS Table would be physically broke into twelve monthly tables over a year like ORDERS0115 (mmyy), ORDERS0215 and so on.

We are in the opinion that keeping the entire transactions in one Table is better. Would like to know what's the best practices for transaction tables like the one mentioned above? Is it better to use one table with partitions. I read somewhere that partitions can slow down SELECT queries if not designed and thought properly.Since this would be hosted on cloud (Azure), do you think some additional things are to be taken care? How a site like Amazon keeps their transactions tables?

View 8 Replies View Related

SQL Server Admin 2014 :: Partitioning Master And 4 Child Tables

Jul 5, 2014

I have 6 tables which are very huge in row count and need to be partitioned for better manageability.

Little info: Every day, 300 Million records are inserted and 300 million records are deleted in below 7 tables. we maintain only 8 days worth of data in below tables which is the reason records which are older than 8 days are continuously deleted.

Master table which has [ID],[Timestamp]
Table Name: Sample - 2,578,106

Child tables: Foreign key [ID] is common for all the tables. There is no timestamp column in child table.
dbo.ConnectionDB - 1,147,578,048
dbo.ConnectionSS - 876,458,321
dbo.ConnectionRT - 118,133,857
dbo.ConnectionSample - 100,038,535
dbo.Command - 100,032,235

I would like to partition the above child tables based on the IDs that are inserted every 4 hours. Meaning, All IDs that are inserted in 4 hours window should be in a partition.

View 1 Replies View Related

SQL Server Admin 2014 :: Columnstore Index On Large Tables

Jul 1, 2015

I 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?

View 3 Replies View Related







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