-
Create new index in a table
Hey, I analyzed a job which has a very long runtime. In the client monitor I saw that one SQL-Statement has a runtime around 900ms. This statement uses no index key, so in order to improve it I want to set an index. The sql-statement looks like this: SELECT SUM("Amount") FROM "XXXX" WITH (UPDLOCK, ROWLOCK) WHERE (("Ledger…
-
Optimal SQL Data files setup
Hi All, I am currently setting up a new SQL server for the customer and I want it to fly. This is the configuration we are working with: 1. 64 Gigabyte of RAM 2. 2 * 8 core intel XEON. 3. OS is Server 2008R2 Enterprise 64 Bit, 4. SQL is SQL 2008R2 Enterprise 64 Bit, 5. Disk arrays are build like this: a. OS and program…
-
SQL Diagnostics Queries
I did a search and nothing came up. Glenn Berry is a SQL MVP and I have been using his "SQL Diagnostics Queries" consistently when troubleshooting NAV/SQL performance issues. Here is a link to his SQL 2008(R2) Diagnostics Queries, but he also maintains versions for SQL 2005 and 2012:…
-
unexpected result after index maintenance
Dear Experts On of my client is using NAV 5.0 and they are having Database on sql2008 of around 530 GB so there main pain area is both Disk Space & Performance.As suggested I executed index maintenance on value Entry Table but the result was shocking for me as it increased my Database size.I know that log File are…
-
Navision Client performance difference
Hello , We are facing a performance difference on SQL Server transactions between a some servers on wich Navision Clients are running. Let me try to explain our problem: We are running Navision 5.1 Clients on citrix servers and another Server running NAS. All Servers are virtualized. SQL 2008 R2 Server is not virtualized.…
-
Anyone with DynamicsPerf for SQL2005?
When Microsoft released DynamicsPerf (http://blogs.msdn.com/b/nav/archive/201 ... amics.aspx) it supported both SQL2005 and SQL2008. Now it has been updated and only supports SQL2008. :( Anyone still having a copy of a version supporting SQL2005? [-o<
-
Optimize Nav 2009 with SQL Server
Hey yall, I was wondering what are some of the best practices to optimize nav with sql server. I tried searching on google and i cant really find a clear set of best practice. Our company is trying to fine tune the database for one of our clients. Also, we are looking for ways to increase (make efficient) the READ and…
-
Performance in SQL
hi guys, anyone knows how to view performance in SQL, afaik in navision we will go into database session and take a look at the blocking id... but at times no blocking id indicated yet userS still feedback is slow... :cry:
-
Problems after migration to VMWare (server virtualization)
Hello, we have migrated our SQL Installation to following configuration: Server with 2 Xeon CPU with each 4 Kernels, running at 2.4 GHz, 76 GB Ram, Fijitsu Storage DX90, connected with Dual Controller and ISCSI, VMWare 5.0, 1GBit Ethernet The Virtual server on this machine has 20GB Ram, all 8 Kernels from the CPUs, and 12…
-
Deadlocking of standard routines
Hello together, I am experiencing dead locking problems with the standard functionality. Like posting of invoices, Payment journal bookings etc. There were no major changes in the standard routines that handles booking. According to my knowledge the standard routines doesn't deadlock as all the tables are locked in…
-
Indexed Views
Hello together, I have a question regarding the indexes views and the real usage of them. I found a script on the internet that determines the user_seeks, user_updates, etc. for each index and also the VSIFT indexes of the indexed views. In a few tables I see that for user_seeks and user_updates…
-
sql server 2005 hangs when i click user mapping to new user
im sorry i added this article in another place by wrong forgive me Hi guys i have sql server 2005 i installed before 2 days every thing is ok but when i create user in security for all database then i click user mapping it hangs and frezes and take more time and last not open after this error reporting of sql server…
-
Impact of SQL Variant Datatype on performance
Hi, We are managing 12 companies in Microsoft Dynamics NAV, on 5 distinct C/Side databases. All the databases have exactly the same version of objects, based on a highly customized French 3.6 NAV version, with a lot of specific functionalities, running with a 4.0 SP2 server version. The DB sizes range from 140GB to 410GB,…
-
SQL Low Performance than Native Database
Scenario: SQL Database Vs Native Database1. Currently I am using SQL database for one of my client and not able to run adjust cost on daily basis in 6 Hrs. Task already done for SQL performance improvement Quad Core Processor(64 Bit) 52 GB RAM SQL Database is already divided into 10 Disk with RAID proper configurations.…
-
Navision Performance Issue
hi guys, we have feedback from user about sudden slow in navision performance (v4 sp1). initially user was unable to post, they would get and error message: "the g/l table is lock by another user, please try again." our initial findings was multiple user was doing posting concurrently (eg: finance closing, whse transfering…
-
Wrong key selection in report
Hi everyone, do you know why this chunk of codeSerialNoSummary.RESET;SerialNoSummary.SETCURRENTKEY("Patient Code","Property No.","Property Type");SerialNoSummary.SETRANGE("Patient Code",ContactOtherRC."No.")IF SerialNoSummary.FINDSET THEN BEGIN REPEAT //do some stuff, No db reads/writes involed... UNTIL…
-
Item ledger entry
hi everyone, I have a big item ledger enty table to manage (at least, it's big for me, because i've never worked with high data volumes). It's 9'700'000 records, and about the 75% of its data refers to one single item code. Provided the always useful advices about hw, indexes, network, application...Do some sql expert know…
-
How optimize Key Lookup with Wildcard on SELECT?
Hi guys i´m monitoring a process with SQL Profiler and i see this: CPU - 0 Read - 2 Writes - 0 Duration - 892 SELECT TOP 1 * FROM <database_companyname>$Value Entry" WITH (READUNCOMMITTED) WHERE (("Item Ledger Entry No_"=@P1)) ORDER BY "Item Ledger Entry No_","Document No_","Document Line No_","Entry No_ The Index Seek…
-
Defrag NAV database with 13 company's
Hi Guys, In Nav. you've got Database -> Information -> Tables -> Optimize. This is a very good tool. But i wonder if somebody's got a script which does the same. So i can schedule it. I run into more sql defrag scripts only they have problems with the 13 company's or schemas that we use in Navision. Thanks very much.…
-
Full Scans on table Object Tracking
I was monitoring SQL Server in a load test for NAV 2009R2 Web Service when I noticed a Full Scan happening every two seconds. I started digging into it and found out that the table being scanned is "Object Tracking". The table is quite small, there are somewhat 4000 rows in that table (one line for each NAV object) so I do…
-
To restrict one session per user
Friends,
-
Slow Only on role center
Hi everyone, I've a pretty strange behavior here: i've tried to profile it and i identified a single, long running query, but i can't figure out what can have generated it. Specs: nav2009r2 build 32775 sql 2008r2 But first, a litlle explanation: I have a list of serial numbers, and a factobox that shows every item ledger…
-
optimize for ad hoc workloads
Any experience out there with this SQL option? I troubleshooting some performance issues that have cropped up in a larger system. One thing I noticed was a high plan count on a number of queries. Thinkign this may help alleviate that problem. Thoughts? NAV 5.01 Database NAV 2009 SP1 clients SQL 2008 SP1 Windows 2008 R2
-
Why Microsoft doesn´t change all FIND(-) for FINDSET?
I just export all objects from a Cronus database (2009 R2) to txt and find out many FIND('-') on all the code. If this kind of command creates a lot of cursor´s wich cause a lot of impact to SQL performance why Microsoft doesn´t do a full review and changes this kind of commands, or are they helpfull on some processes?.…
-
VM Ware with Navision / SQL Server Performance
Hi Experts! How is the performace of Nav SQL in VM Ware Environement, as one of our customer is requesting for this solution. Any issues are there?? Any one experienced any Issues or enjoying with Nav SQL in VM Ware environment. Please share your experience. Best Regards
-
sp_cursor fetch, FETCH API CURSOR - How to handle with this?
I have a Report that is on Item Ledger Entry and is doing a lot of calculation. I monitorize and see this SQL statmentes taking about 10 minutes:exec sp_cursorfetch 180150747,2,0,35 and FETCH API_CURSOR0000000000002E57 I also detected On SQL Profiler an instruction that as this Execution Plan CPU - 0 Reads - 241 Writes -0…
-
Microsoft® SQL Server® 2008 R2 Best Practices Analyzer
It came out and can help you a lot http://www.microsoft.com/download/en/details.aspx?id=15289
-
Navision - DB files placement
Hello, can you tell me what you think about this? We are gonna to buy som new HDDs and will completely reinstall server .... We will have 66 HDDs. 6 of them will be spares, so I have 60 disks available. Server is HP DL 380 , 2xXeon (6core),72GB RAM, p812 controller, 16 int. HDDs, 2xD2700 external cages for 50 HDDs I was…
-
Serializable vs. Repeatable Read
Hi, has someone changed the default locking level from serializable to repeatable read? What are your experiences with repeatable read? Does it really reduce blocking? Did you have problems with phantom reads? Do you use it as a default for your customers (if you are working for a nsc)? How can I check in a test…
-
error 14420 MSSQLSERVER
How Can I fix this error? Explanation: "Log shipping is out of synchronization beyond the backup threshold. The backup threshold is the number of minutes that are allowed to elapse between log-shipping backup jobs before an alert is generated. This message does not necessarily indicate a problem with log shipping. Instead,…