-
A blocking Report
Does anyone know how to produce a report like the following: In excel, collecting all of the blocking instance per day. For each instance we would find out the table that is blocked, the person blocking and the person blocked? If this were generated on a daily basis it would really help traking down performance issues.
-
Table Split - FileGroup
Hi Master, How to make table split and assign in filegroup? Please guide me step by step? thx, Angelo
-
How does locktable actualy work ?
Table 37 OnInsert() TestStatusOpen; IF Quantity <> 0 THEN ReserveSalesLine.VerifyQuantity(Rec,xRec); DocDim.LOCKTABLE; LOCKTABLE; SalesHeader."No." := ''; DimMgt.InsertDocDim( DATABASE::"Sales Line","Document Type","Document No.","Line No.", "Shortcut Dimension 1 Code","Shortcut Dimension 2 Code"); Above you can see te…
-
Always Rowlock
Hi Master, What is the effect if I checked mark "always rowlock" option? The explanation about this is too short. May I know positive and negative side if checked mark this option? if this option will improve performance also? Please advice... [-o< rgds, Angelo
-
Steps to Optimize SQL Server
Hi, I have a :?: What steps/guidelines do you follow when you optimize your sql server database besides code improvement and indexes. And one last question, what tools do you use to measure performance of the database. After all, you need to measure running processes, system resources etc before and after a change to…
-
Re-Organize Indexes on SQL2005 VS Navision Tables Optimize ?
Hi Guys, can someone show me the difference between doing Re-Organize Indexes on SQL2005 and running Navision Tables Optimize. in case that i will run one of them or both. Thanks a lot.
-
Boot.ini
Hi Master, I want to know if I use memory 6GB in my server(2003 enterprise and sql 2000 enterprise), Have I edit Boot.ini file to add "/3GB /PAE" ? and AWE Memory must be "enable"? this modification can make our navision performance better? I'm not system engineer so I blind of this term...pls advice... thx, angelo
-
SQL Server 2005 Dynamic Management View Perf. Data Warehouse
A SQL Server 2005 Dynamic Management View Performance Data Warehouse Introduction Microsoft SQL Server 2005 provides Dynamic Management Views (DMVs) to expose valuable information that you can use for performance analysis. DMVstats 1.0 is an application that can collect, analyze and report on SQL Server 2005 DMV…
-
Field duration in Sql profiler
Hi sql guys and gals, i'm stil profiling and have a little questing SELECT TOP 1 * FROM "SSC W1500SP0 SI1170 Performance Test"."dbo"."Performance Test Company$G_L Entry" WITH (UPDLOCK, ROWLOCK) ORDER BY "Entry No_" DESC is a query that takes a long time, which i find strange. SQL is only going for 1 record , the first ,…
-
MaintainSQLIndex
Hi, I'm working on a performance issue. I'm looking at the MaintainSQLIndex property. If the key is only used for sorting and not indexing I can disable this property to gain performance. Is with sorting meant that the key is used to sort reports and forms. What if this key is used in a SETCURRENTKEY. Can I still disable…
-
Just found this on Partner Source
Title: Microsoft Dynamics Support Advanced Workshop Series: NAV SQL Performance When: February 12-14, 2008 Who: Microsoft Dynamic Partners Trainer: Michael DeVoe Level: 200 – 300 – Administrators Summary: This workshop will cover performance analysis, troubleshooting, and tuning for Microsoft Dynamics – NAV SQL Server…
-
statsman query question
Hi Nav community, SELECT StatMan([SC0]) FROM (SELECT TOP 100 PERCENT [f11018207] AS [SC0] FROM [dbo].[Performance Test Company$337$2] WITH (READUNCOMMITTED,SAMPLE 1.000000e+002 PERCENT) ORDER BY [SC0] ) AS _MS_UPDSTATS_TBL i'm running the application benchmark toolkit and the sql server profiler is scanning for queries…
-
Find Not used Sift levels
Hello, I try to unmaitain sift levels that are unused. I used query (show me unused indexes. Thats great for UnMiantainSQLIndex) select a.*,b.name as idxname,c.name as objname from sys.dm_db_index_usage_stats a,alfa.[700].sys.indexes b,alfa.[700].sys.all_objects c where a.user_seeks=0 and a.user_scans=0 and…
-
Investigating index locking with SQL Server Profiler
I am trying to find out what indexes are locked and how long they are locked when executing a query. I want to use Profiler for this. I choose the events Lock:Acquired and Lock:Released. When I see the results in Profiler, I do not find the objectid of the index being locked. Is it possible to do this like for example with…
-
SQL Database Performance
We recently upgraded from Nav 2.6 to 4.01 and converted to SQL from a native Nav database. Over the past few months we have noticed some performance issues. We do not have any SQL DB admins onsite so I have been asked to do the investigation and make a recommendation on how to increase the DB performance. Here is our…
-
Update 6 for Navision 4.0 SP3: BE CAREFULL
This week Microsoft released update 6 for Navision 4.0 SP3. Mainly a lot of SQL fixes, or so they say. At first I thought that it was a combination of update 1, 4 & 5 but that seems not to be the case. What they seem to have done is enable indexhints out of the box. This was already possible in older versions but it was by…
-
effect of MODIFY on temp table
MS SQL database. Does MODIFY on temporary table set the transaction into SERIALIZABLE isolation level mode ?
-
Locking issue with 4.0 SP3 Hotfix 6
Although it is crossposting I think this should be in this forum also. http://www.mibuso.com/forum/viewtopic.p ... highlight=
-
Optimazation questions
I'm Readin a lot of Sql NAV performance related literature lately. I have the following questions: Question 1: - We do not use the variant code in our solution. I assume that it would not be usefull to have the variant code in de Sql key, or is it? In the standart nav code there are several setranges on this field, since…
-
MaintainSQLIndex=no
Hi, I´m no expert but this question has been in my head for some time now, and need to get it out ](*,) Would one gain any performance if the key is disabled and MaintainSQLIndex=no , I have a database where some keys are disabled but the MaintainSQLIndex=yes , would the Sqlindex then still be maintained somewhere. The…
-
Re-Indexing Tables - SQL Server Vs Navision
Hi Experts! 1.What is the difference between Navision Test tables option Vs Selecting Navision tables at SQL Server then reindexing that particular tables, Which method is advisable. 2. After reindexing from SQL server will that table size will grow - ? What could be the best method for reindexing table - Can any one pls…
-
HOWTO go about reducing indexes?
Hi, I have been reading up on Sql nav performance, because the solution i'm building has some legacy code, keys and sift's. For example the cost of the item ledger entry is 104 :oops: and te sales line has a cost of about 90. From what i understand from the sql resource kit and the performance trouble shooting guide , ...…
-
Index Problem..
I made somme indexes on one table and these indexes have each almost the size of data of the table.. Sommeone can tall me what is wrong?? In this moment data on the table is somewhere at 50MB and Indexes are at 1GB...
-
Setcurrentkey
In the hotly debated "SP3 Update 6" topic pdj wrote: "It also kills several of the solutions I have made the last years for customers, when the solution was made under the assumption that it was executed on SQL. I have gotten used to never use SETCURRENTKEY unless I need a special sorting order. So I have a lot of places…
-
Object/DBMS Cache increase at Server with SQL option
Hi Experts! How to increase DBMS/Object Cache at Server level with Navision 3.70 & SQL Server option. As I know how to increase DBMS/object Cache in Native Database, I am unable to increase Navision server Object/DBMS cache, as when ever closing Navision Cache also changing as default. Thanks
-
New trace flag in SQL 2005 SP4 for NAV
If you are watching Microsoft Dynamics NAV Sustained Engineering Team Blog for new articles, you know about that. For others I recommend to read that article. There is one special flag for NAV which can solve some (SOME) perf. problems with SQL 2005 and NAV. Good to have it somewhere in your memory... 8)…
-
Lock timeout - How to find blocking session
A customer is complaining about big problems with locked tables. "Table X is locked by another user". Lock timeout is 10s. Running SQL Profiler logging event lock:timeout, I do catch several timeouts and information about who was locked. But there is no information about which session that is locking the table. By…
-
CALCSUMS makes SQL for all Indexed fields?
I need a quick education for you GURUs. I have two questions. I am very curious how Navision 5 creates SQL for a CALCSUMS I have this code: SalesLine2.RESET;SalesLine2.SETCURRENTKEY("Document Type",Status,Type,"No.","Variant Code","Logistic Status","Location Code",DateCreated, TimeCreated,"Outstanding Qty.…
-
Replace SIFT tables with materialised views
I'm actually on holiday next week so won't be able to see response, but I was just wondering if anyone had tried comparing the performance of a physical SIFT tables vs a materialised view - I figure the maintenence overhead would be much lower. Just a thought.
-
SQL on Virtual Server / Vmware or not....
Hi All, A lot of our customers (Dynamics-AX and Dynamics-NAV) asked us or there is a possibility to run SQL server on a virtual server. Our policy is to run this kind of Database server on a 'reeel' server and not a virtual server. I asked MS about this and they say that there is no support for SQL on virtual server. But…