Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [microsoft-dynamics]

The Microsoft business applications product line, which is now being rolled into a single Dynamics 365 brand. Please add more details about the product and the version in the question body.

Microsoft Dynamics is the product line for business applications by Microsoft.

Previously this product line consisted of

  • Dynamics AX

  • Dynamics GP

  • Dynamics CRM

  • Dynamics NAV

  • Dynamics SL

  • Dynamics C5

These products are now in the process of being merged into one and have been re-branded as Dynamics 365.

When using this tag please add application and version specifics into your question, and when using the Dynamics 365 stack please include which component of the stack you are using (for example Dynamics 365 for Operations or Dynamics 365 for Sales)

35 questions
35
votes
3 answers

What does OPTION FAST in SELECT statement do?

I have done some digging on what the OPTION (FAST XXX) query hint does inside a SELECT statement and I'm still confused about it. According to MSDN: Specifies that the query is optimized for fast retrieval of the first number_rows. This is a…
Matthew Verstraete
  • 925
  • 4
  • 12
  • 28
15
votes
1 answer

sp_cursoropen and parallelism

I'm running into a performance problem with a query that I can't seem to get my head around. I pulled the query out of a cursor definition. This query takes seconds to execute SELECT A.JOBTYPE FROM PRODROUTEJOB A WHERE ((A.DATAAREAID=N'IW') AND…
Tom V
  • 15,752
  • 7
  • 66
  • 87
9
votes
1 answer

sp_cursorprepexec causing 53 million reads?

We are running a Dynamics AX 2012 installation with SQL Server 2012. I know that cursors should not be used anymore but AX is using it and we can't change this behavior so we have to work with it. Today I caught a very bad query with over 53…
Hans Vader
  • 185
  • 1
  • 9
8
votes
2 answers

What is the persistent_version_store table?

What is the purpose of the MS Shipped table: sys.persistent_version_store? Why do I want to know Primarily curiosity, partly because it may relate to an issue waiting to bite us... We're running a SaaS instance of MS Dynamics 365 for Finance and…
JohnLBevan
  • 459
  • 1
  • 4
  • 15
7
votes
2 answers

How to repair database consistency errors

I have a Microsoft Dynamics AX SQL Server database. I just run DBCC CHECKDB in order to check the integrity. After a few minutes I got at the end of the results the following: CHECKDB found 0 allocation errors and 4 consistency errors in database …
VAAA
  • 213
  • 1
  • 2
  • 4
7
votes
2 answers

SQL perf issue - configure server correctly first or troubleshoot issues one by one?

This is for SQL Server 2012. I am troubleshooting overall slow performance on a Dynamics AX environment. Everything is just slow - app itself, reports, ad-hoc, etc. Right off the bat, I can see that none of the best practices for SQL Server have…
Greg
  • 582
  • 1
  • 4
  • 14
7
votes
2 answers

Verifying connection pooling server side with CONTEXT_INFO on

I'm working with a 3 tiered application, Microsoft Dynamics AX, where the middle tier maintains connections to a SQL Server. Several clients connect to this middle tier server. The middle tier server typically has several connections open to the SQL…
Tom V
  • 15,752
  • 7
  • 66
  • 87
6
votes
3 answers

Restore database of 90GB to computer of only 100GB

How do I restore a huge Microsoft SQL Server database that is 6GB as a zip file and 90GB as a bak file to a computer that only has 100GB free space? When I uncompress the zip file I get a bak file that is 90GB so only 10GB left on hard drive hence …
Gilboot
  • 173
  • 5
4
votes
2 answers

Join on dm_db_index_physical_stats very slow

I'm running a small query below in a MS Dynamics AX 2012 database and it runs more than 5 minutes which I had to cancel and it is showing PAGEIOLATCH_SH wait type. The database data file is 560GB and on SQL Server 2012 SP1. SELECT TOP 1…
HTM
  • 41
  • 1
  • 2
4
votes
2 answers

Delete unused indexes from Microsoft Dynamics CRM Application

So I found tons of unused indexes from our CRM application with this query from Pinal: SELECT o.name AS ObjectName , i.name AS IndexName , i.index_id AS IndexID , dm_ius.user_seeks AS UserSeek , dm_ius.user_scans AS UserScans , dm_ius.user_lookups…
Racer SQL
  • 7,546
  • 16
  • 77
  • 140
4
votes
2 answers

Help me tune the long-running query

I have a problem with the following query executing from the application (Microsoft Dynamics AX): DECLARE @p1 INT; SET @p1 = NULL; DECLARE @p2 INT; SET @p2 = 0; DECLARE @p5 INT; SET @p5 = 2 + 4096; DECLARE @p6 INT; SET @p6 = 8193; DECLARE @p7…
George K
  • 2,306
  • 1
  • 17
  • 32
4
votes
2 answers

Why is context_info sometimes blank in sys.dm_exec_sessions?

I am trying to get information from the context_info column using Cast(context_info as varchar(128)) from sys.dm_exec_sessions as given in the article below: Finding User Sessions from SPID in Dynamics AX 2012 by Amit R. Kulkarni However, for some…
Sanket
  • 41
  • 2
4
votes
3 answers

Setting maxdop 1 vs setting the threshold for parallelism high

I am looking at a possible fringe case for parallelism settings. I've long since followed the credence that maxdop 1 is a viable option for Microsoft Dynamics CRM databases. As the nature of the database is strictly OLTP, and parallelism has little…
Reaces
  • 2,681
  • 4
  • 27
  • 38
3
votes
2 answers

Merge join asks for seemingly unnecessary sort before joining

I use one table to update another table. Both the tables have a clustered index where the first 4 columns are the same type of data. CREATE UNIQUE CLUSTERED INDEX [i109139_I_109139CLI_44C9176D64C84ECB959C0EAA6AE7A4F4] ON…
KHP
  • 65
  • 7
3
votes
1 answer

SQL server chooses hash match over merge join but the input fields to join should be sorted

I have a simple query that joins two tables on the field PRODID. For some reason, SQL Server opts to use a hash match to join this, but I believe it should choose a merge join, as that field is part of the index, and the preceding fields of both…
KHP
  • 65
  • 7
1
2 3