SQL Server 2014 (major build version 12.0.xxxx). Please also tag sql-server.
5
votes
0answers
61 views
TokenAndPermUserStore Clear decreases CPU usage for a short period of time
Intro
In short, there are a lot of ad hoc queries occuring on my server, from an application which I do not control and cannot change (Even pushing indexes is hard, and they use many heaps... ).
...
0
votes
0answers
38 views
Access Database Size
I have an Access database, which is 1.9 GB. I linked that Access database to SQL Server. So far my understanding data flow to SQL server tables if any changes occur in Access database. How about the ...
1
vote
0answers
38 views
Memory distribution across numa nodes within Sql server
I need to understand following behavior of memory distribution across numa nodes within sql server
Current set up:
2 node cluster SQL server 2014 SP2 with virtualization disabled
We have 4 NUMA ...
1
vote
1answer
47 views
Strangely high writes per second
I'm experiencing some extremely strange behavior on one of our OLTP Servers. Just to provide a bit of background, we have an 'OLTP' which has many large and wide tables. We have 560 tables within it ...
2
votes
1answer
62 views
CPU usage among Numa nodes in sql server
I need to understand the below scenario
On Sql server 2014 with compatibility mode of 2008R2, having 80 logical processors with 20 distributed amongst 4 numa nodes. I. E numa node 1 has 0-19 , numa ...
1
vote
0answers
26 views
UPSIZING ACCESS 2010 to SQL Server 2014
When I run the upsizing module it goes through the motions but the report at the end says
"table was skipped or export failed"
for each of the tables.
I can't find a solution for this.
I ...
1
vote
1answer
23 views
Sql Server : How to query list of subscriptions and Articles (for a given publication) from Publisher
In Sql Server 2014, how to query a list of Subscriptions & its articles for a given Publication from Publisher? (transactional replication)
Yes, there are easier ways to query from Distributor ...
1
vote
1answer
199 views
“The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.” after adding indexes to temp tables
Recently, I added some indexes on temp tables to procedures which prepare data for data warehouse. On a testing db, tests of these procedures worked fine but when running on client's server it failed ...
0
votes
1answer
17 views
Monitor volume of data returned to the client application
I've identified ASYNC_NETWORK_IO as our top wait by some distance averaging between 70-77% of wait time for our client application SQL Server. This has been put forward towards the development team ...
0
votes
0answers
22 views
Error: 823, Severity: 24 --> error 21 device is not ready after System Reboot. How do I prevent it?
During a scheduled reboot of the machine running my SQL Server 2014 instance tonight, several live servers became corrupted and had to be restored from backup.
Checking the error log/event viewer I ...
0
votes
0answers
40 views
Restore time on dev servers
We have 4 test environments running SQL Server 2014 Developer edition. Every night, we refresh one of those environments by restoring a SQL Backup file stored on a separate server, so that it's in a ...
0
votes
1answer
32 views
SQL Server hangs on index rebuild, works well after SQL service restart
We have a SQL Server 2014 enterprise cluster. Server 1 and Server 2.
SQL Server is limited to 64GB of memory.
We are using Ola Hallengren scripts for index rebuilding and optimizations once per week ...
3
votes
0answers
44 views
Need help with SQL Server 2014 and disaster recovery
I am tasked with building a highly available SQL Server 2014 Enterprise environment for our new policy admin system. This database will eventually grow to 1TB +.
Background
Our normal method of ...
1
vote
0answers
23 views
How do I see what update statistics is doing while running?
I have a very large table with 500m rows and 150 columns (I did not design the table, im simply trying to figure out what is going on here). I try to update a single statistic for this table.
I run ...
2
votes
2answers
42 views
Duplicate index - Same key, one with INCLUDE columns, which one to drop?
I have 2 indexes that have the same key, but one also has INCLUDE columns. Which one can I drop?
They are the same, except IndexInclude has 6 INCLUDE columns.
Both have some reads (IndexNoInclude ...
1
vote
1answer
31 views
Does number of rows affect Update Statistics performance if you are sampling with n rows?
For example, I have table A with 80 million rows and table B with 200 million rows.
Assume both tables have 30+ stat tables (all manually generated by SQL Server).
I randomly take the first 10 stat ...
2
votes
1answer
31 views
History table implementation: “Tuple-versioning” vs Effective Date
I'm designing a database that will have history tables (SCD Type 4 I guess) for auditing purpose.
The question is what are the advantages of having tuple-versioning (start and end dates) vs just ...
-1
votes
0answers
20 views
Index Fill Factor [duplicate]
On SQL Server 2014, does fill factor 0 means the same when I set 80 ?
Is it some automatic setting ?
I put fill factor 80, when I did rebuild of indexes and performance went down.
Now, I figured ...
0
votes
1answer
53 views
Validation of data with checksum vs Binary_checksum
Based on this post, I was able to solve the performance issue by adding indexes and thought I completed my project until I ran into an issue with the checksum... I then did research (should have done ...
2
votes
2answers
57 views
How does patching work in SQL Server?
I need a clarification on how below SQL Server service pack patching works.
Few of my servers are at below patch for SQL Server 2014 which has latest meltdown patch
12.00.5214 2014.120.5214.6 ...
2
votes
1answer
33 views
Concurrent requests not running in parallel
I am trying to run update statistics in parallel.
Say I start with 2 sessions: S1, S2
In S1 I run
Update Statistics A with fullscan and time it to take 2 minutes.
In S2 I run Update Statistics B ...
5
votes
2answers
77 views
Configure SQL Server Analysis Services (SSAS) to run on same IP as SQL Server instance
TL;DR
I would like to add SQL Server 2014 Analysis Services to a specific instance of SQL Server and to configure it in such a way, that the Analysis Services (SSAS) and future Reporting Services (...
1
vote
1answer
43 views
Deadlock - Due to update causing Index page movement within an index
I am still troubleshooting deadlock mentioned in this questions. In that I got help in understanding why a select would hold on to a shared lock and I was able to test and confirm that also.
Now I ...
1
vote
1answer
39 views
Avoiding code duplication for counting and selecting the same resultset
I have a requirement to return the total number of records and paged data from a stored procedure. The row count should only be computed if a boolean parameter is true. How do I perform the 2 queries (...
0
votes
1answer
36 views
How to Replace Hash Match (Partial Aggregate) with Stream Aggregate
My execution plan shows a hash match (partial aggregate) instead of a stream aggregate even though I have a non-clustered index (which basically means sorting is already in place). It gives me a ...
2
votes
0answers
27 views
Query involving TVF very slow on SQL Server 2008
I have a query that runs in under 1 second on SQL Server 2014. A similar query takes over 12 minutes when run against a table on a linked server running SQL Server 2008. I don't know if it's because ...
1
vote
2answers
42 views
Can't change authentication mode from Windows to both SQL Server and Windows
As the title suggests I'm having problems changing the authentication mode from Windows to both SQL Server and Windows mode in version 12.0.2. I have tried right right-clicking the server, go to ...
0
votes
0answers
31 views
Edition upgrade on SQL Server failover clustered instance
We did Edition upgrade on our active node for the SQL Server instance SQL01 and that was successful.
I have a question/doubt --> Do I need to proceed the same on other node as well since SQL Server ...
1
vote
1answer
30 views
How can I permanently remove replication by script if I only have access to the subscriber?
We've been building a replication network of DBs to give us some degree of Always On without paying the full price tag. Currently we have a master DB which replicates to a handful of slaves, so that ...
0
votes
0answers
17 views
Trouble dealing with deadlock in a Dynamics NAV 2009 instance
I'm having a lot of deadlocks ocurring in a Dynamics NAV 2009 instance.
Lots of select * ... and select top 1 * ... with (updlock) coming from the application.
Is there anything I can do without ...
1
vote
0answers
29 views
Recreating Layers based on data already entered
Background:
Construction events take place on a certain block of road. The events are based on data like Lane#, Layer#, and SP and EP (Start Position and End Position).
I created a picture to make ...
2
votes
2answers
36 views
Database Log File Growth change does not reflect on secondary replica sys.master_files
Environment: Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Apr 20 2015 17:29:27 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
...
0
votes
1answer
23 views
Redirecting Report Server connections using host file
I am connected remotely to a server called New_Server and trying to connect to the Report Server which is installed on it by referring to another server:
I've changed the host file on NEW_SERVER to ...
0
votes
0answers
30 views
Enabling TDE on PRODUCTION Servers - Guidance , Suggestions and Testing TDE ways
First of all just want to say , I am completely new to Transparent Data Encryption.
Can I get some guidance on things to keep in mind while I am enabling TDE on Critical PRODUCTION Servers. I have ...
0
votes
0answers
32 views
Access Denied issue, SQL Server LocalDB on Windows Server 2008 R2 SP1
Running SQL Server LocalDB 2012 and 2014 on Windows Server 2008 R2 SP1 VM I have some bizarre issues which don't happen when running on Windows 7 or Windows 10.
I can create databases, save data to ...
-1
votes
0answers
80 views
Is it common to store compressed byte array in SQL Server?
We are not using SQL Enterprise Edition. We have to do audit trail by ourselves.
I refer the sample from https://www.codeproject.com/Articles/1019511/Audit-trail-and-data-versioning-with-Csharp-and-...
2
votes
1answer
56 views
Troubleshooting Deadlock, Victim holding on to shared lock
I am trying to troubleshoot the below deadlock situation
Looking at the deadlock xml, both session are using read committed isolation level.
Question is, in read committed isolation level, in this ...
1
vote
1answer
69 views
Distribute multiple payments to invoice lines
I'm having a problem allocating payments to invoice lines.
We’re using MsSql 2014
Data looks like this:
Invoice lines table (sales):
lineId invoiceId value
1 1 100
2 ...
1
vote
0answers
29 views
Version store blowing up, but responsible sessions have no open transactions
Tempdb is growing and it's all version store data. When I run:
select hostname,elapsed_time_seconds,session_id, transaction_id, is_snapshot, blocked, lastwaittype, cpu, physical_io, open_tran, cmd
...
1
vote
0answers
50 views
Help with Frequent Data Updates on Clustered Columnstore index [closed]
I’ve been asked to look at a SQL Server 2014 Enterprise VM with 6 cores MAXDOP set to 3, 256 GB Ram.
The CPU is running very high but there isn’t enough activity going on to justify the CPU workload....
2
votes
4answers
115 views
Best practices for transferring data across long, wide tables
As part of my data warehouse project, I'll be transferring data from our OLTPs to the Data Warehouse. Some of the tables are long and wide so obviously, I'll only be transferring required columns.
...
0
votes
0answers
32 views
Moving tempdb from shared to local drive in a failover cluster
We have an existing failover cluster that I would like to have the tempdb in local drive. I'm not sure how to proceed. My thought process is below:
Create two drives on each node one for tempdb and ...
0
votes
1answer
49 views
How do I allow non sysadmin accounts the ability to create/query linked servers in SQL Sever 2014?
I want to allow a non-admin account the ability to query a linked server.
If I create a linked server with the following under a sysadmin account like below.
EXEC master.dbo.sp_addlinkedserver @...
1
vote
2answers
94 views
Will Re-Seeding an Identity column back to 0 cause page splits?
Simple table with an identity table as a clustered primary key. We unexpectedly hit the max value for the column, so I requested our DBA re-seed the table back to 0 to give us some time to examine ...
0
votes
0answers
48 views
Cleaning up the environment on old cluster after migrating to newer SQL Server cluster
We migrated many instances of SQL Server 2008/R2 on Windows 2008R2 and 2012 on Windows 2012 to newer Windows 2016 with SQL Server 2014 and 2016 versions.
The old clusters were configured with either ...
0
votes
2answers
23 views
Transactional replication failure handling
Web Application is High school record management system (HSRMS). Users of the application are
High school management (Normal Users)
Data verifying users (Admin)
User group 2 will allow the User ...
1
vote
1answer
33 views
Cleaning up MSrepl_errors, SQL Server Replication
Our MSrepl_errors table is around 15GB and got records from the beginning of time.
What is the correct way of cleaning up this table. Do we simply delete from it?
Edit
The distribution agent clean ...
2
votes
0answers
46 views
DBCC TABLE CHECK command won't stop
We have a SQL Server 2014 with 140 user databases, each of which have approx. 180 tables.
A maintenance plan was scheduled for once a week but it was discovered that the Check Database Integrity (DBCC ...
0
votes
0answers
34 views
Tracking parameter passed to Stored procedure - extended events
I am trying to track a slow performing SQL and I am trying to capture the parameters that are passed in for the slow runs. I am tracing module_end and rpc_completed. The stored procedure is run as ...
0
votes
1answer
36 views
Enabling TDE on SQL Server AOAG 2014 Databases - Guidance
I am completely new to Transparent Data encryption and please help me on below questions related to enabling the TDE on existing PRODUCTION Databases.
Primary Question:
What are the things which I ...
