-
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…
-
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:
-
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.…
-
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,…
-
RDP vs Client Performance NAV2009R2 with SQL
Hi All, I am by no means an expert in performance on NAV and SQL but I have been working with NAV long enough to have a “feel” for how it should perform. We are encountering a very strange situation and I was wondering if someone can shed some light on this or have some experience in this. When using Remote Desktop to the…
-
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…
-
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…
-
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,
-
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.…
-
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?.…
-
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.…
-
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…
-
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…
-
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
-
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…
-
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,…
-
NAV 3.6 with 3.70b run time running on SQL 2000 enterprise
Good Morning, Just want to ask if anyone had similar issue: We are running NAV 3.6 with 3.70b run time on SQL 2000 enterprise. The size of the database is about 92GB, with 30GB log file, RAID 10. It was not super fast but steady as it goes until yesterday morning. Users in the system now experiencing slow down and delay…
-
Table Locking became major issue!!!!
Dear all, Am getting a Table lock error. Following are the options that i have tried to resolve table locking but i have failed 1. Set the transaction type to "Update Locks" in Property 2. Removed unwanted code and tuned it well. 3. Checked Option like Updating Statistics , indexing the Objects in MS-SQLand it is updated…
-
DELETEALL-SQL Profiler How to optimize high number of Reads
Hi guys, i have turn SQL Server Profiler to view a why Planning Worsheet was taking about 2 minutes to make calculations to a single item, on a CRONUS database. I had detect some lines with this parameters SQL Profiler CPU - 187 Reads - 66820 Duration - 186 The SQL StatementDELETE FROM "Demo Database NAV…
-
Tables still locking after SQL LOCK_ESCALATION = DISABLE
Hi everyone, We are having some trouble with the Item Ledger Entry, G/L Entry, and Reservation Entry tables locking at different points in the day. It occurs when posting consumption or output, posting trucks, etc. While these are locked of course we cannot update invoices, journal entries, or anything else. I have run the…
-
Adjust cost item entries takes hours to run
We have implemented NAV 2009 (No SP) with SQL server 2008 enterprise edition. It is installed on a server with 64 bit windows 2008 operating system. The server has 16 GB RAM. The client is into distribution business with 35k SKUs with FIFO costing. When we run the Adjust cost entries batch job, it takes minimum 2 hrs for…
-
Slow GL Drilldown
We have a site experiencing unacceptable performance when drilling down on GL Accounts. The database is not that large (25 GB) and I'd consider table 17 (4 million entries) rather small. Drilling down on an account with about 200,000 entries can take 20 seconds or longer to open and display. Issue exist with both classic…