Moving Navision 3.70 to new SQL Server installation
zaurb
Member Posts: 26
Hi!
We currently run Navision 3.70 on SQL Server 2000 and Windows 2003 Server 64 bit edition with 4 Gigs of RAM in VMWare ESX server 3.5.
I have moved the existing Navision installation to a new test vm with Windows Server 2003 64 Bit, 4 vCPU, 8 Gb RAM with Dynamics NAV 5.
To accomplish this I've done the following:
1) Installed + configured Windows 2003 Server 64 bit with latest MS updates.
2) Installed SQL Server 2008 Std with SP1 and updated through Windows Update.
3) Installed Navision Application Server.
4) Backud up Navision database in old installation using Navision client 3.7.
5) On new VM using the Dynamics NAV 5 client created a new database and restored the backup of Navision 3.70 to a new database.
Questions:
1) This is the first time I do installation of Navision. So, I would really appreciate any suggestions if the procedure I've followed so far is correct.
2) The database size on the old installation is somewhat 170 Gb, after restoring this db to a new server, the db size is around 70 Gb only. Why?
3) When I start a Dynamics NAV 5 client to connect to a new installation the connection time takes around 5-7 seconds vs 1-2 on the old server with client 3.7. Why?
4) When I do "synchronization of all users" from the Dynamics NAV 5 client (Tools->Security-> Synchronize All) it takes more than 1 hour... On old server 1-2 seconds.
5) What other procedures should I have done in this scenario? Both on SQL Server 2008 and within Dynamics NAV 5 client.
Thank you very much in advance!
We currently run Navision 3.70 on SQL Server 2000 and Windows 2003 Server 64 bit edition with 4 Gigs of RAM in VMWare ESX server 3.5.
I have moved the existing Navision installation to a new test vm with Windows Server 2003 64 Bit, 4 vCPU, 8 Gb RAM with Dynamics NAV 5.
To accomplish this I've done the following:
1) Installed + configured Windows 2003 Server 64 bit with latest MS updates.
2) Installed SQL Server 2008 Std with SP1 and updated through Windows Update.
3) Installed Navision Application Server.
4) Backud up Navision database in old installation using Navision client 3.7.
5) On new VM using the Dynamics NAV 5 client created a new database and restored the backup of Navision 3.70 to a new database.
Questions:
1) This is the first time I do installation of Navision. So, I would really appreciate any suggestions if the procedure I've followed so far is correct.
2) The database size on the old installation is somewhat 170 Gb, after restoring this db to a new server, the db size is around 70 Gb only. Why?
3) When I start a Dynamics NAV 5 client to connect to a new installation the connection time takes around 5-7 seconds vs 1-2 on the old server with client 3.7. Why?
4) When I do "synchronization of all users" from the Dynamics NAV 5 client (Tools->Security-> Synchronize All) it takes more than 1 hour... On old server 1-2 seconds.
5) What other procedures should I have done in this scenario? Both on SQL Server 2008 and within Dynamics NAV 5 client.
Thank you very much in advance!
Zaur Bahramov
0
Comments
-
It seems so. You haven't mentioned about installation of extended stored procedures, so either you've done it or you're using Database authentication.1) This is the first time I do installation of Navision. So, I would really appreciate any suggestions if the procedure I've followed so far is correct.
This is due to different technology used to calculate SIFT fields. NAV versions prior 5.0 SP1 used additional tables to precalculate and store values of SIFT fields, NAV5.0 SP1 and later uses indexed views to do that.2) The database size on the old installation is somewhat 170 Gb, after restoring this db to a new server, the db size is around 70 Gb only. Why?
? there could be due to many reasons... protocol selected to connect NAV client do SQL database... computer settings, server settings, network settings... Anything.3) When I start a Dynamics NAV 5 client to connect to a new installation the connection time takes around 5-7 seconds vs 1-2 on the old server with client 3.7. Why?
If you close NAV client and re-open and connect second time to the same SQL server does it take similar amount of time or much shorter ?
You've used Enhanced security settings. Switch it back to Standard.4) When I do "synchronization of all users" from the Dynamics NAV 5 client (Tools->Security-> Synchronize All) it takes more than 1 hour... On old server 1-2 seconds.
Test, test, and test test literally everything... Double check NAS server based solution if you use any, as some components does not work in x64 environment. Touch every single piece of your new installation. check entering documents, journals, posting, reporting.. Also test performance.5) What other procedures should I have done in this scenario? Both on SQL Server 2008 and within Dynamics NAV 5 client.
How do you organize this in procedures is up to you.Slawek Guzek
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-030 -
1) Yes, I didn't mention that. I did add the xpndo sp to sql server installation. I use a mixed mode for db authentication.
2) I see, so that explains why the db size is so little with respect to the original db. Also I did a dbcc reindex on huge tables from t-sql and the db file size grew to 80 gb.
3) Connection time resolved with switching to Standard security model from Enhanced model.
5) I will do testing of all procs together with pilot users for this project, but I wonder if there're any optimizations to be made directly on sql server and/or navision performance wise.
Thank you very much for your help!Zaur Bahramov0 -
Have you done real world performance testing? If not you might be in for some huge surprises when you go live.David Singleton0
-
Funny... I would suspected everything except database security settingszaurb wrote:3) Connection time resolved with switching to Standard security model from Enhanced model.
...
Regards,
SlawekSlawek Guzek
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-030 -
David Singleton wrote:Have you done real world performance testing? If not you might be in for some huge surprises when you go live.
Not yet really. I've just installed the Windows Server 2003 / SQL Server 2008 and made restore from fbk files created by Navision 3.70 client into a new database on a new server using Dynamics Nav 5.1 client.
As for the performance testing, I will procede with this the next week. What I was actually planning to do is to determine at least one key user in each department, like sales, accounting, marketing, etc, install a Dynamics NAV client on their computers and link them to new server and ask them to run their normal working procedures for testing processes.
This was actually my performance testing plan as of DBA.
As for customizations and localization of Navision itself I can't do anything here, since am not a Dynamics programmer. Portability of this stuff is up to our cunsultants.Zaur Bahramov0 -
Yes, I forgot to mention, that the connection time was slow not only from remote sessions but from the server itself as well. I've digged on that on the web and found out that many other people have experienced the same issue with Enhanced security model in place. And most of them use only the Standard security model with proxy authentication.Slawek Guzek wrote:Funny... I would suspected everything except database security settings...Zaur Bahramov0 -
zaurb wrote:Yes, I forgot to mention, that the connection time was slow not only from remote sessions but from the server itself as well
My first guess was the network. More precise - problem with primary DNS server. If primary DNS is dead then any first-time TCP/IP connection takes much longer (DNS query made to primary server must timeout, then another query is send to secondary DNS), but subsequent connections are fast due to local DNS caching. On the other hand 'much longer' is usually 15 sec (with standard Windows TCP/IP configuration), 5 - 7 sec is too short.
Now, knowing the solution source seems to be quite obvious. NAV uses Application Roles to manage SQL level security. With Enhanced mode NAV creates separate Application Role object per each Company/Table/Navision Role combination. That is why it takes so long to synchronize security. If so many Application Roles are created during synchronization then logging as Application Role may also take a while...
SlawekSlawek Guzek
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-030 -
One more issue to confirm. Is the Dynamics NAV 5.0 SP1 the same as Dynamics NAV 5.1? Our version is 5.0 SP1, should we upgrade it to 5.1 ? Is that actually a NAV 2009?
Just checked that 5.1 actually is 6.0 which is NAV 2009.Zaur Bahramov0 -
It is al little confusing...
NAV 5.1 was the name used long time ago by Microsoft (even before NAV 5.0 was launched). Then it was named NAV 6, and finally arrived as NAV2009
So we have :
NAV 5.0 - quite old stuff now, uses the same technology as all previous versions (I mean SIFT tables)
NAV 5.0 SP1 - smaller database due to implementing SIFT as indexed views, generally much faster, but...
NAV 2009 - same as 5.0 SP1 (from DB point of view), but introduces 3 tiered architecture, kind of 'thin' client, web services
NAV 2009 SP1 - the newest stuff. Many errors fixed
Updating to NAV 5 SP1 if fairly safe, however, as David suggested, it might suprprise with some performance problems.
NAV 2009 uses the same technology (clasic Client), but introduces some othes surprises (like case-sensivity in text comparison statements), so updating to this version is a bit more dangerous. Or it just requires a bit more detailed testing. On the other hand it opens new possibilities of application integration thanks to 3 tiered architecture.
SlawekSlawek Guzek
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-030 -
zaurb wrote:David Singleton wrote:What I was actually planning to do is to determine at least one key user in each department, like sales, accounting, marketing, etc, install a Dynamics NAV client on their computers and link them to new server and ask them to run their normal working procedures for testing processes.
[-o< [-o< [-o< [-o<
Good luck with that. :-#David Singleton0 -
David Singleton wrote:zaurb wrote:David Singleton wrote:What I was actually planning to do is to determine at least one key user in each department, like sales, accounting, marketing, etc, install a Dynamics NAV client on their computers and link them to new server and ask them to run their normal working procedures for testing processes.
[-o< [-o< [-o< [-o<
Good luck with that. :-#
I know I know \:D/ Sounds funny.
But I'm not an expert in Navision (yet). I just really need an advise on how to procede...Zaur Bahramov0 -
Why did this post twice?David Singleton0
-
You need to test it in a live environment.
There was a project last month where the client moved from 3.70 to 5.00sp1. Everyone did their testing, all was fine, they went live, and after a week the performance was so bad they went back to 3.70 and reentered the last weeks transactions again.
With 220 users that was quite a task.
Trust me you really want to test this first. Your plan at the moment is a plan to fail.
BTW my involvement was the day before they went live, when the client had some doubts and called me. Once they described what they were planning and what testing they had done, I suggested the only option was to abort the upgrade for a month to do testing.
Mind you I have also seen many upgrades like this that just worked first time no problem. BUt people never call me when things are good, the call me when the system is on fire and they have no other option. So I normally only see the disasters. Lets hope you don't need to call me
Best advise I can give is call a Navision expert and get them to test it for you before you go live. But I get the feeling that you don't have the budget for that.David Singleton0 -
Well, the issue is that the Navision expert, the company that prepares release for us takes care only of internal procedures in Navision. Like objects, c/al code and other stuff. I-m doing a technological upgrade. My task is limited to installing a SQL Server 2008, restoring existing database to the new server with Dynamics NAV 5.0 SP1 client and making sure that everything works like in the current production environment.
Then the company providing assistance for Navision itself will do the rest.
That-s why am interested mainly in setting up the working environment for future upgrade.Zaur Bahramov0 -
My boss ha launched a procedure directly on server which usually takes about 18 hours on our production server (Windows 2003 / SQL Server 2000). Today he tells me that the procedure is still running after two and half days.
How should I troubleshoot the SQL Server installation to find out the reason. I tried to do the following, however, I don't know how exaxtly interpret results:1) DBCC PROCCACHE -------------------------------------- num proc buffs 39384 num proc buffs used 288 num proc buffs active 399 proc cache size 2328 proc cache used 30 proc cache active 20 -------------------------------------- 2) DBCC SHOWCONTIG DBCC SHOWCONTIG scanning 'spt_fallback_db' table... Table: 'spt_fallback_db' (117575457); index ID: 0, database ID: 1 TABLE level scan performed. - Pages Scanned................................: 0 - Extents Scanned..............................: 0 - Extent Switches..............................: 0 - Avg. Pages per Extent........................: 0.0 - Scan Density [Best Count:Actual Count].......: 100.00% [0:0] - Extent Scan Fragmentation ...................: 0.00% - Avg. Bytes Free per Page.....................: 0.0 - Avg. Page Density (full).....................: 0.00% DBCC SHOWCONTIG scanning 'spt_fallback_dev' table... Table: 'spt_fallback_dev' (133575514); index ID: 0, database ID: 1 TABLE level scan performed. - Pages Scanned................................: 0 - Extents Scanned..............................: 0 - Extent Switches..............................: 0 - Avg. Pages per Extent........................: 0.0 - Scan Density [Best Count:Actual Count].......: 100.00% [0:0] - Extent Scan Fragmentation ...................: 0.00% - Avg. Bytes Free per Page.....................: 0.0 - Avg. Page Density (full).....................: 0.00% DBCC SHOWCONTIG scanning 'spt_fallback_usg' table... Table: 'spt_fallback_usg' (149575571); index ID: 0, database ID: 1 TABLE level scan performed. - Pages Scanned................................: 0 - Extents Scanned..............................: 0 - Extent Switches..............................: 0 - Avg. Pages per Extent........................: 0.0 - Scan Density [Best Count:Actual Count].......: 100.00% [0:0] - Extent Scan Fragmentation ...................: 0.00% - Avg. Bytes Free per Page.....................: 0.0 - Avg. Page Density (full).....................: 0.00% DBCC SHOWCONTIG scanning 'spt_monitor' table... Table: 'spt_monitor' (1115151018); index ID: 0, database ID: 1 TABLE level scan performed. - Pages Scanned................................: 1 - Extents Scanned..............................: 1 - Extent Switches..............................: 0 - Avg. Pages per Extent........................: 1.0 - Scan Density [Best Count:Actual Count].......: 100.00% [1:1] - Extent Scan Fragmentation ...................: 0.00% - Avg. Bytes Free per Page.....................: 8024.0 - Avg. Page Density (full).....................: 0.86% DBCC SHOWCONTIG scanning 'spt_values' table... Table: 'spt_values' (1131151075); index ID: 1, database ID: 1 TABLE level scan performed. - Pages Scanned................................: 12 - Extents Scanned..............................: 3 - Extent Switches..............................: 2 - Avg. Pages per Extent........................: 4.0 - Scan Density [Best Count:Actual Count].......: 66.67% [2:3] - Logical Scan Fragmentation ..................: 25.00% - Extent Scan Fragmentation ...................: 33.33% - Avg. Bytes Free per Page.....................: 544.5 - Avg. Page Density (full).....................: 93.27% DBCC SHOWCONTIG scanning 'MSreplication_options' table... Table: 'MSreplication_options' (1163151189); index ID: 0, database ID: 1 TABLE level scan performed. - Pages Scanned................................: 1 - Extents Scanned..............................: 1 - Extent Switches..............................: 0 - Avg. Pages per Extent........................: 1.0 - Scan Density [Best Count:Actual Count].......: 100.00% [1:1] - Extent Scan Fragmentation ...................: 0.00% - Avg. Bytes Free per Page.....................: 7900.0 - Avg. Page Density (full).....................: 2.40% DBCC SHOWCONTIG scanning '$ndo$srvproperty' table... Table: '$ndo$srvproperty' (1259151531); index ID: 0, database ID: 1 TABLE level scan performed. - Pages Scanned................................: 1 - Extents Scanned..............................: 1 - Extent Switches..............................: 0 - Avg. Pages per Extent........................: 1.0 - Scan Density [Best Count:Actual Count].......: 100.00% [1:1] - Extent Scan Fragmentation ...................: 0.00% - Avg. Bytes Free per Page.....................: 8053.0 - Avg. Page Density (full).....................: 0.51% DBCC execution completed. If DBCC printed error messages, contact your system administrator. 3) DBCC PERFMON Statistic Value -------------------------------- ------------- Reads Outstanding 0 Writes Outstanding 0 (2 row(s) affected) Statistic Value -------------------------------- ------------- Network Reads 4,93287E+07 Network Writes 3,748093E+07 Network Bytes Read -1,693009E+09 Network Bytes Written -5,630639E+08 Command Queue Length 0 Max Command Queue Length 0 Worker Threads 0 Max Worker Threads 0 Network Threads 0 Max Network Threads 0 (10 row(s) affected) Statistic Value -------------------------------- ------------- RA Pages Found in Cache 0 RA Pages Placed in Cache 0 RA Physical IO 0 Used Slots 0 (4 row(s) affected) Spinlock Name Collisions Spins Spins/Collision Sleep Time (ms) Backoffs ------------------------------ -------------------- -------------------- --------------- -------------------- ----------- ABR 0 0 0 0 0 GUARDIAN 0 0 0 0 0 MOP_COLL 0 0 0 0 0 MOP 0 0 0 0 0 CONNECTS 0 0 0 0 0 ASYNCSTATSLIST 0 0 0 0 0 HTTP_CONNCACHE 0 0 0 0 0 MUTEX 214 426158 1991,393 3 26 SRVPROC 9056 18530115 2046,17 160 1268 EXT_CACHE 0 0 0 0 0 FT_INIT 0 0 0 0 0 COM_INIT 0 0 0 0 0 LOGON_TRIGGER_CACHE 0 0 0 0 0 SOAPSESSIONS 0 0 0 0 0 QUERYSCAN 1 0 0 0 0 BACKUP_CTX 66 79234 1200,515 0 7 RESOURCE 0 0 0 0 0 CACHEOBJ_DBG 0 0 0 0 0 DBCC_CHECK 0 0 0 0 0 ADB_CACHE 0 0 0 0 0 X_PIPE_DEMAND 0 0 0 0 0 DINPBUF 0 0 0 0 0 SOS_OBJECT_POOL 0 0 0 0 0 SQL_MGR 0 0 0 0 0 MEM_MGR 0 0 0 0 0 BUF_HASH 130 228600 1758,462 1 11 DBT_HASH 0 0 0 0 0 HOBT_HASH 5 369 73,8 0 0 TXFRM_REPL 0 0 0 0 0 FSGHOST_STATUS 0 0 0 0 0 PAGECOPIER 0 0 0 0 0 DTT_LIST 0 0 0 0 0 ENDD_LIST 0 0 0 0 0 IDENTITY 0 0 0 0 0 BUF_LINK 0 0 0 0 0 DBTABLE 10242 2475046 241,6565 27 161 X_PORT 11 9119 829 0 0 XDESMGR 285 323973 1136,747 5 28 X_PIPE 0 0 0 0 0 LOGFLUSHQ 5 161068 32213,6 3 16 XCB_HASH 0 0 0 0 0 BASE_XACT_HASH 0 0 0 0 0 XDES_HASH 0 0 0 0 0 BUF_WRITE_LOG 56 288 5,142857 0 0 RFS_THREAD_QUEUE 0 0 0 0 0 SVC_BROKER_CTRL 1 441 441 0 0 XACT_WORKSPACE 16 2 0,125 0 0 XCB 0 0 0 0 0 X_PACKET_LIST 14 10793 770,9286 0 1 QUERYEXEC 15 3 0,2 0 0 SVC_BROKER_LIST 8 78473 9809,125 0 6 LOGCACHE_ACCESS 41630 503034263 12083,46 0 39728 LSLIST 0 0 0 0 0 SVC_BROKER_DEBUG_LIST 0 0 0 0 0 CURSOR 0 0 0 0 0 READ_AHEAD 0 0 0 0 0 DROP 0 0 0 0 0 BLOCKER_ENUM 25 0 0 0 0 XID_ARRAY 2 0 0 0 0 LOCK_HASH 1481 3977124 2685,431 3 596 LOGLC 0 0 0 0 0 QE_SHUTDOWN 0 0 0 0 0 LOGLFM 0 0 0 0 0 PERIODIC 0 0 0 0 0 GHOST_HASH 1 0 0 0 0 ISSRESOURCE 0 0 0 0 0 XVB_CSN 0 0 0 0 0 XVB_LIST 0 0 0 0 0 FCB_REPLICA_SYNC 0 0 0 0 0 SUBPDESC_LIST 0 0 0 0 0 DIAG_OBJECT 0 0 0 0 0 XACT_LOCK_INFO 0 0 0 0 0 MDB_REMOTE_SESSION_PROXY 0 0 0 0 0 MDB_REMOTE_SESSION_PROXY_MANAG 0 0 0 0 0 MATRIX_DATA_VIRTUALIZATION_MAN 0 0 0 0 0 REMOTEOP 0 0 0 0 0 MATRIX_DATA_VIRTUALIZATION_MAN 0 0 0 0 0 XACT_LOCKINFO_TASK 0 0 0 0 0 GHOST_FREE 0 0 0 0 0 BUF_FREE_LIST 100372 192016016 1913,044 602 9283 XCB_FREE_LIST 0 0 0 0 0 LOCK_FREE_LIST 0 0 0 0 0 LSID 9 0 0 0 0 XDES 269 65850 244,7955 0 4 DBT_IO_LIST 0 0 0 0 0 IOREQ 0 0 0 0 0 XCHNG_TRACE 0 0 0 0 0 DROP_TEMPO 0 0 0 0 0 INDEX_CREATE 0 0 0 0 0 XTS_MGR 0 0 0 0 0 DATASET_FREELIST 0 0 0 0 0 SHARABLE_SESSION_OBJECTS 0 0 0 0 0 PROGRESS_REPORT 0 0 0 0 0 DIAG_MANAGER 1 0 0 0 0 SUBPDESC 0 0 0 0 0 CLR_HOSTTASK 0 0 0 0 0 MGR_CACHE 0 0 0 0 0 SETRANGE_SYNC 0 0 0 0 0 ALLOC_CACHES_HASH 7 17196 2456,572 0 1 LOCK_NOTIFICATION 0 0 0 0 0 COMPPLAN_SKELETON 0 0 0 0 0 LPE_SESSION 0 0 0 0 0 LPE_BATCH 185 75625 408,7838 0 4 LPE_SXTP 0 0 0 0 0 CLR_SPIN_LOCK 0 0 0 0 0 CURSQL 0 0 0 0 0 TSQL_DEBUG 0 0 0 0 0 LOCK_RESOURCE_ID 1 0 0 0 0 SEQUEUE_SIZED_THREADSAFE 0 0 0 0 0 DROPPED_ALLOC_UNIT 0 0 0 0 0 APPENDONLY_STORAGE 0 0 0 0 0 SNI 0 0 0 0 0 SESSION_MANAGER 23 5054 219,7391 0 0 CONNECTION_MANAGER 0 0 0 0 0 SESSION_SEC_CONTEXT 0 0 0 0 0 TSQL_NICE_SHUTDOWN 0 0 0 0 0 QUERY_EXEC_STATS 1 0 0 0 0 SUBLATCH 0 0 0 0 0 MCI 0 0 0 0 0 SPL_DISPATCHER_QUEUE 0 0 0 0 0 MATRIXDB_MTM_AGENT 0 0 0 0 0 MATRIXDB_MTM_TABLE 0 0 0 0 0 RPCDISPATCH 0 0 0 0 0 RPCRESPONDERCONTEXT 0 0 0 0 0 SPL_SOS_DISPATCHER 0 0 0 0 0 MATRIXDB_MTM_TXN 0 0 0 0 0 MATRIXDB_PRISM_STATE 0 0 0 0 0 SOS_TASK 287 2760435 9618,24 54 255 SOS_VM_LOW 0 0 0 0 0 TMP_SESS_KEY 0 0 0 0 0 WRITE_PAGE_RECORDER 0 0 0 0 0 OPT_IDX_MISS_KEY 0 0 0 0 0 FS_DELETED_FOLDER_CLEANUP 0 0 0 0 0 REPL_LOGREADER_HISTORY_CACHE 0 0 0 0 0 RPCPACKAGE 0 0 0 0 0 OPT_IDX_MISS_ID 0 0 0 0 0 SOS_NODE 0 0 0 0 0 SOS_SCHEDULER 3477 2330441 670,2448 9 140 SOS_TLIST 125 629482 5035,856 5 52 SOS_SELIST_SIZED_SLOCK 0 0 0 0 0 SOS_CACHESTORE 166 434476 2617,325 1 23 RESMANAGER 0 0 0 0 0 SOS_RW 791 11654608 14734,02 5 78 SOS_WAITABLE_ADDRESS_HASHBUCKE 1 0 0 0 0 SOS_ABORT_TASK 0 0 0 0 0 SOS_SYSTHREAD_DISPATCHER 5 10886 2177,2 0 1 SOS_OBJECT_STORE 390 141046 361,6564 0 5 SOS_SYSTHREAD 0 0 0 0 0 SOS_MINITHREAD 0 0 0 0 0 SOS_SUSPEND_QUEUE 43812 12446353 284,0855 37 645 RANGE_GENERATION 0 0 0 0 0 SOS_RINGBUFFER_RECORD 0 0 0 0 0 SOS_LARGEPAGE_ALLOCATOR 0 0 0 0 0 KTM_ENLISTMENT 0 0 0 0 0 ONDEMAND_TASK 0 0 0 0 0 REMOTE_SESSION_CACHE 0 0 0 0 0 RESQUEUE 3712 1664404 448,3847 9 110 OPT_INFO_MGR 0 0 0 0 0 OPT_IDX_STATS 0 0 0 0 0 FGCB_PRP_FILL 0 0 0 0 0 PARTITIONED_HEAP_FREE_LIST 0 0 0 0 0 DELAYED_PARTITIONED_STACK 8 17257 2157,125 0 0 FS_CONTAINER_LIST_WITH_DELETE 0 0 0 0 0 RPCCHANNELPOOL 0 0 0 0 0 RPCREQUESTORCONTEXT 0 0 0 0 0 CHANNELFORCECLOSEMANAGER 0 0 0 0 0 SOS_CLOCKALG_INTERNODE_SYNC 0 0 0 0 0 REPL_LOGREADER_PERDB_HISTORY_C 0 0 0 0 0 XE_SESSION_STORAGE 0 0 0 0 0 SPL_XE_DISPATCHER_QUEUE 0 0 0 0 0 SPL_XE_BUFFER_MGR 0 0 0 0 0 SPL_XE_SESSION_MGR 0 0 0 0 0 SPL_XE_SESSION_EVENT_MGR 0 0 0 0 0 SPL_XE_SESSION_TARGET_MGR 0 0 0 0 0 SPL_DISPATCHER_LIST 0 0 0 0 0 SPL_NONYIELD_ANALYSIS 0 0 0 0 0 MATRIXDB_MEMTABLE 0 0 0 0 0 CM_ROSTER 0 0 0 0 0 CM_ENLISTMENT 0 0 0 0 0 CM_FMPRISM 0 0 0 0 0 CMA_FMPRISM 0 0 0 0 0 CM_FMCONFIG 0 0 0 0 0 SOS_DEBUG_HOOK 0 0 0 0 0 TESTTEAM 0 0 0 0 0 TESTTEAMTASTAS 0 0 0 0 0 TESTTEAMEXPONENTIAL 0 0 0 0 0 TESTTEAMEXPONENTIALTASTAS 0 0 0 0 0 STACK_HASHER 0 0 0 0 0 (188 row(s) affected) Wait Type Requests Wait Time Signal Wait Time -------------------------------- ------------- ------------- ---------------- MISCELLANEOUS 0 0 0 LCK_M_SCH_S 0 0 0 LCK_M_SCH_M 12 39 0 LCK_M_S 11 6754 60 LCK_M_U 2 2541 0 LCK_M_X 18 6240 1 LCK_M_IS 0 0 0 LCK_M_IU 0 0 0 LCK_M_IX 0 0 0 LCK_M_SIU 0 0 0 LCK_M_SIX 0 0 0 LCK_M_UIX 0 0 0 LCK_M_BU 0 0 0 LCK_M_RS_S 0 0 0 LCK_M_RS_U 0 0 0 LCK_M_RIn_NL 0 0 0 LCK_M_RIn_S 0 0 0 LCK_M_RIn_U 0 0 0 LCK_M_RIn_X 0 0 0 LCK_M_RX_S 0 0 0 LCK_M_RX_U 0 0 0 LCK_M_RX_X 0 0 0 LATCH_NL 0 0 0 LATCH_KP 0 0 0 LATCH_SH 391 656 423 LATCH_UP 0 0 0 LATCH_EX 615 10019 313 LATCH_DT 0 0 0 PAGELATCH_NL 0 0 0 PAGELATCH_KP 0 0 0 PAGELATCH_SH 21043 7409 729 PAGELATCH_UP 28 147 3 PAGELATCH_EX 18799 3535 386 PAGELATCH_DT 0 0 0 PAGEIOLATCH_NL 0 0 0 PAGEIOLATCH_KP 0 0 0 PAGEIOLATCH_SH 421527 920666 16404 PAGEIOLATCH_UP 3843 67204 42 PAGEIOLATCH_EX 133161 315374 16535 PAGEIOLATCH_DT 0 0 0 TRAN_MARKLATCH_NL 0 0 0 TRAN_MARKLATCH_KP 0 0 0 TRAN_MARKLATCH_SH 0 0 0 TRAN_MARKLATCH_UP 0 0 0 TRAN_MARKLATCH_EX 0 0 0 TRAN_MARKLATCH_DT 0 0 0 LAZYWRITER_SLEEP 813381 5,965507E+08 274933 IO_COMPLETION 200936 123988 4061 ASYNC_IO_COMPLETION 14 18249 0 ASYNC_NETWORK_IO 1,22028E+07 2665460 615309 SLEEP_BPOOL_FLUSH 127396 311057 22500 CHKPT 1 1315 2 SLEEP_DBSTARTUP 36 3509 22 SLEEP_TEMPDBSTARTUP 0 0 0 SLEEP_DCOMSTARTUP 1 51 0 SLEEP_TASK 1392683 2,995382E+08 154864 SLEEP_SYSTEMTASK 1 1430 2 RESOURCE_SEMAPHORE 0 0 0 DTC 0 0 0 OLEDB 49256 433 0 FAILPOINT 0 0 0 RESOURCE_QUEUE 0 0 0 ASYNC_DISKPOOL_LOCK 0 0 0 THREADPOOL 279 200 0 DEBUG 0 0 0 REPLICA_WRITES 0 0 0 BROKER_RECEIVE_WAITFOR 18 3793968 46 DBMIRRORING_CMD 0 0 0 WAIT_FOR_RESULTS 0 0 0 SOS_SCHEDULER_YIELD 4,707928E+07 718599 651083 SOS_VIRTUALMEMORY_LOW 0 0 0 SOS_RESERVEDMEMBLOCKLIST 0 0 0 SOS_LOCALALLOCATORLIST 0 0 0 SOS_CALLBACK_REMOVAL 0 0 0 ONDEMAND_TASK_QUEUE 1 0 0 LOGMGR_QUEUE 8811 5,987272E+08 1927 REQUEST_FOR_DEADLOCK_SEARCH 119757 5,987846E+08 5,987846E+08 CHECKPOINT_QUEUE 763343 5,980449E+08 622807 PARALLEL_BACKUP_QUEUE 0 0 0 DUMP_LOG_COORDINATOR_QUEUE 0 0 0 LOWFAIL_MEMMGR_QUEUE 0 0 0 BACKUP 0 0 0 BACKUPBUFFER 1163 10058 376 BACKUPIO 1854 11674 15 BACKUPTHREAD 154 7087 7 DBMIRROR_DBM_MUTEX 0 0 0 DBMIRROR_DBM_EVENT 0 0 0 DBMIRROR_SEND 0 0 0 DBMIRROR_EVENTS_QUEUE 0 0 0 DBMIRROR_WORKER_QUEUE 0 0 0 HTTP_START 0 0 0 HTTP_ENUMERATION 0 0 0 SOAP_READ 0 0 0 SOAP_WRITE 0 0 0 DUMP_LOG_COORDINATOR 0 0 0 DISKIO_SUSPEND 0 0 0 IMPPROV_IOWAIT 0 0 0 DEADLOCK_TASK_SEARCH 0 0 0 REPL_SCHEMA_ACCESS 0 0 0 REPL_CACHE_ACCESS 0 0 0 KSOURCE_WAKEUP 1 0 0 SQLSORT_SORTMUTEX 0 0 0 SQLSORT_NORMMUTEX 0 0 0 SQLTRACE_WAIT_ENTRIES 0 0 0 SQLTRACE_LOCK 60 384 0 SQLTRACE_BUFFER_FLUSH 149651 5,987764E+08 17577 SQLTRACE_SHUTDOWN 0 0 0 QUERY_TRACEOUT 0 0 0 DTC_STATE 0 0 0 BROKER_TRANSMITTER 2 0 0 BROKER_SERVICE 0 0 0 BROKER_SHUTDOWN 0 0 0 BROKER_MASTERSTART 1 0 0 BROKER_REGISTERALLENDPOINTS 0 0 0 BROKER_EVENTHANDLER 54 5,882839E+08 140 FCB_REPLICA_WRITE 0 0 0 FCB_REPLICA_READ 0 0 0 WRITELOG 7753 24119 648 EXCHANGE 0 0 0 EC 0 0 0 TEMPOBJ 0 0 0 XACTLOCKINFO 0 0 0 LOGMGR 0 0 0 CMEMTHREAD 241 179 116 CXPACKET 2625 20077 1937 SHUTDOWN 0 0 0 WAITFOR 0 0 0 EXECSYNC 8 0 0 SOSHOST_INTERNAL 0 0 0 SOSHOST_SLEEP 0 0 0 SOSHOST_WAITFORDONE 0 0 0 SOSHOST_MUTEX 0 0 0 SOSHOST_EVENT 0 0 0 SOSHOST_SEMAPHORE 0 0 0 SOSHOST_RWLOCK 0 0 0 SOSHOST_TRACELOCK 0 0 0 MSQL_XP 1319 34591 0 MSQL_DQ 0 0 0 LOGBUFFER 21 4137 1 TRANSACTION_MUTEX 0 0 0 SLEEP_MSDBSTARTUP 0 0 0 MSSEARCH 0 0 0 XACTWORKSPACE_MUTEX 0 0 0 TRACEWRITE 0 0 0 WAITSTAT_MUTEX 0 0 0 WAITFOR_TASKSHUTDOWN 0 0 0 MISCELLANEOUS 0 0 0 GUARDIAN 0 0 0 CLR_TASK_START 0 0 0 CLR_JOIN 0 0 0 CLR_CRST 0 0 0 CLR_SEMAPHORE 0 0 0 CLR_MANUAL_EVENT 0 0 0 CLR_AUTO_EVENT 0 0 0 CLR_MONITOR 0 0 0 CLR_RWLOCK_READER 0 0 0 CLR_RWLOCK_WRITER 0 0 0 SQLCLR_QUANTUM_PUNISHMENT 0 0 0 SQLCLR_APPDOMAIN 0 0 0 SQLCLR_ASSEMBLY 0 0 0 KTM_ENLISTMENT 0 0 0 KTM_RECOVERY_RESOLUTION 0 0 0 KTM_RECOVERY_MANAGER 0 0 0 SQLCLR_DEADLOCK_DETECTION 0 0 0 QPJOB_WAITFOR_ABORT 0 0 0 QPJOB_KILL 0 0 0 BAD_PAGE_PROCESS 0 0 0 BACKUP_OPERATOR 0 0 0 PRINT_ROLLBACK_PROGRESS 0 0 0 ENABLE_VERSIONING 0 0 0 DISABLE_VERSIONING 0 0 0 REQUEST_DISPENSER_PAUSE 0 0 0 DROPTEMP 0 0 0 FT_RESTART_CRAWL 0 0 0 LOGMGR_RESERVE_APPEND 0 0 0 LOGMGR_FLUSH 0 0 0 XACT_OWN_TRANSACTION 0 0 0 XACT_RECLAIM_SESSION 0 0 0 DTC_WAITFOR_OUTCOME 0 0 0 DTC_RESOLVE 0 0 0 SEC_DROP_TEMP_KEY 0 0 0 SRVPROC_SHUTDOWN 0 0 0 BROKER_INIT 0 0 0 BROKER_CONNECTION_RECEIVE_TASK 0 0 0 NET_WAITFOR_PACKET 0 0 0 DTC_ABORT_REQUEST 0 0 0 DTC_TMDOWN_REQUEST 0 0 0 RECOVER_CHANGEDB 0 0 0 WORKTBL_DROP 0 0 0 SNI_HTTP_WAITFOR_0_DISCON 0 0 0 UTIL_PAGE_ALLOC 0 0 0 SERVER_IDLE_CHECK 0 0 0 DEADLOCK_ENUM_MUTEX 0 0 0 VIEW_DEFINITION_MUTEX 0 0 0 QUERY_NOTIFICATION_MGR_MUTEX 0 0 0 QUERY_NOTIFICATION_TABLE_MGR_MUT 0 0 0 QUERY_NOTIFICATION_SUBSCRIPTION_ 0 0 0 QUERY_NOTIFICATION_UNITTEST_MUTE 0 0 0 RESOURCE_SEMAPHORE_MUTEX 0 0 0 IO_AUDIT_MUTEX 0 0 0 BUILTIN_HASHKEY_MUTEX 0 0 0 SOS_PROCESS_AFFINITY_MUTEX 0 0 0 MSQL_XACT_MGR_MUTEX 0 0 0 MSQL_XACT_MUTEX 0 0 0 QRY_MEM_GRANT_INFO_MUTEX 0 0 0 SNI_CRITICAL_SECTION 6 0 0 SOS_STACKSTORE_INIT_MUTEX 0 0 0 SOS_SYNC_TASK_ENQUEUE_EVENT 0 0 0 SOS_OBJECT_STORE_DESTROY_MUTEX 0 0 0 EE_PMOLOCK 0 0 0 QUERY_OPTIMIZER_PRINT_MUTEX 0 0 0 DLL_LOADING_MUTEX 0 0 0 RESOURCE_SEMAPHORE_QUERY_COMPILE 0 0 0 RESOURCE_SEMAPHORE_SMALL_QUERY 0 0 0 BROKER_ENDPOINT_STATE_MUTEX 0 0 0 QUERY_EXECUTION_INDEX_SORT_EVENT 0 0 0 ERROR_REPORTING_MANAGER 0 0 0 EE_SPECPROC_MAP_INIT 0 0 0 FULLTEXT GATHERER 0 0 0 SEQUENTIAL_GUID 1 0 0 BROKER_TASK_STOP 2061 1,025267E+07 3354 SNI_TASK_COMPLETION 1 9 0 SNI_LISTENER_ACCESS 0 0 0 EXECUTION_PIPE_EVENT_INTERNAL 0 0 0 CLR_MEMORY_SPY 0 0 0 CLRHOST_STATE_ACCESS 0 0 0 DAC_INIT 1 3 0 ASSEMBLY_LOAD 0 0 0 VIA_ACCEPT 0 0 0 CHECK_PRINT_RECORD 0 0 0 INTERNAL_TESTING 0 0 0 FS_GARBAGE_COLLECTOR_SHUTDOWN 0 0 0 FSAGENT 0 0 0 ABR 0 0 0 WCC 0 0 0 DUMPTRIGGER 0 0 0 QUERY_WAIT_ERRHDL_SERVICE 0 0 0 QUERY_ERRHDL_SERVICE_DONE 0 0 0 TIMEPRIV_TIMEPERIOD 0 0 0 DISPATCHER_QUEUE_SEMAPHORE 0 0 0 XE_MODULEMGR_SYNC 0 0 0 XE_STM_CREATE 0 0 0 XE_SESSION_SYNC 0 0 0 XE_SESSION_CREATE_SYNC 0 0 0 XE_SERVICES_MUTEX 0 0 0 XE_SERVICES_RWLOCK 0 0 0 XE_SERVICES_EVENTMANUAL 0 0 0 XE_OLS_LOCK 0 0 0 SOS_DISPATCHER_MUTEX 0 0 0 XE_BUFFERMGR_FREEBUF_EVENT 0 0 0 XE_BUFFERMGR_ALLPROCESSED_EVENT 0 0 0 XE_DISPATCHER_JOIN 0 0 0 XE_TIMER_MUTEX 0 0 0 XE_TIMER_EVENT 19961 5,987709E+08 5,987706E+08 XE_TIMER_TASK_DONE 0 0 0 XE_DISPATCHER_WAIT 5 7,044048E+07 0 XE_DISPATCHER_CONFIG_SESSION_LIS 0 0 0 XE_SESSION_FLUSH 0 0 0 XE_PACKAGE_LOCK_BACKOFF 0 0 0 BROKER_TO_FLUSH 291943 2,993879E+08 121450 NODE_CACHE_MUTEX 0 0 0 RG_RECONFIG 0 0 0 RESMGR_THROTTLED 0 0 0 SOS_MEMORY_USAGE_ADJUSTMENT 0 0 0 SECURITY_MUTEX 0 0 0 FS_HEADER_RWLOCK 0 0 0 FS_LOGTRUNC_RWLOCK 0 0 0 FS_FC_RWLOCK 0 0 0 FSTR_CONFIG_RWLOCK 0 0 0 FSTR_CONFIG_MUTEX 0 0 0 FSA_FORCE_OWN_XACT 0 0 0 COMMIT_TABLE 0 0 0 CXROWSET_SYNC 0 0 0 PREEMPTIVE_OS_GENERICOPS 15 1393 0 PREEMPTIVE_OS_AUTHENTICATIONOPS 12269 5750 0 PREEMPTIVE_OS_ACCEPTSECURITYCONT 0 0 0 PREEMPTIVE_OS_ACQUIRECREDENTIALS 0 0 0 PREEMPTIVE_OS_COMPLETEAUTHTOKEN 0 0 0 PREEMPTIVE_OS_DECRYPTMESSAGE 2206 187 0 PREEMPTIVE_OS_DELETESECURITYCONT 2055 1121 0 PREEMPTIVE_OS_ENCRYPTMESSAGE 206 41 0 PREEMPTIVE_OS_FREECREDENTIALSHAN 0 0 0 PREEMPTIVE_OS_INITIALIZESECURITY 0 0 0 PREEMPTIVE_OS_LOGONUSER 0 0 0 PREEMPTIVE_OS_QUERYSECURITYCONTE 0 0 0 PREEMPTIVE_OS_VERIFYSIGNATURE 0 0 0 PREEMPTIVE_OS_AUTHORIZATIONOPS 2448 18491 0 PREEMPTIVE_OS_AUTHZGETINFORMATIO 10 0 0 PREEMPTIVE_OS_AUTHZINITIALIZECON 5 461 0 PREEMPTIVE_OS_AUTHZINITIALIZERES 5 3 0 PREEMPTIVE_OS_LOOKUPACCOUNTSID 4437 4010 0 PREEMPTIVE_OS_REVERTTOSELF 2163 170 0 PREEMPTIVE_OS_SETNAMEDSECURITYIN 0 0 0 PREEMPTIVE_OS_CLUSTEROPS 0 0 0 PREEMPTIVE_CLUSAPI_CLUSTERRESOUR 0 0 0 PREEMPTIVE_OS_COMOPS 54 164 0 PREEMPTIVE_COM_COCREATEINSTANCE 0 0 0 PREEMPTIVE_COM_COGETCLASSOBJECT 0 0 0 PREEMPTIVE_COM_CREATEACCESSOR 78 8 0 PREEMPTIVE_COM_DELETEROWS 0 0 0 PREEMPTIVE_COM_GETCOMMANDTEXT 0 0 0 PREEMPTIVE_COM_GETDATA 87551 109 0 PREEMPTIVE_COM_GETNEXTROWS 0 0 0 PREEMPTIVE_COM_GETRESULT 0 0 0 PREEMPTIVE_COM_GETROWSBYBOOKMARK 0 0 0 PREEMPTIVE_COM_LBFLUSH 0 0 0 PREEMPTIVE_COM_LBLOCKREGION 0 0 0 PREEMPTIVE_COM_LBREADAT 0 0 0 PREEMPTIVE_COM_LBSETSIZE 0 0 0 PREEMPTIVE_COM_LBSTAT 0 0 0 PREEMPTIVE_COM_LBUNLOCKREGION 0 0 0 PREEMPTIVE_COM_LBWRITEAT 0 0 0 PREEMPTIVE_COM_QUERYINTERFACE 55 10 0 PREEMPTIVE_COM_RELEASE 32 0 0 PREEMPTIVE_COM_RELEASEACCESSOR 74 2 0 PREEMPTIVE_COM_RELEASEROWS 49201 10 0 PREEMPTIVE_COM_RELEASESESSION 0 0 0 PREEMPTIVE_COM_RESTARTPOSITION 0 0 0 PREEMPTIVE_COM_SEQSTRMREAD 0 0 0 PREEMPTIVE_COM_SEQSTRMREADANDWRI 0 0 0 PREEMPTIVE_COM_SETDATAFAILURE 0 0 0 PREEMPTIVE_COM_SETPARAMETERINFO 0 0 0 PREEMPTIVE_COM_SETPARAMETERPROPE 0 0 0 PREEMPTIVE_COM_STRMLOCKREGION 0 0 0 PREEMPTIVE_COM_STRMSEEKANDREAD 0 0 0 PREEMPTIVE_COM_STRMSEEKANDWRITE 0 0 0 PREEMPTIVE_COM_STRMSETSIZE 0 0 0 PREEMPTIVE_COM_STRMSTAT 0 0 0 PREEMPTIVE_COM_STRMUNLOCKREGION 0 0 0 PREEMPTIVE_OS_CRYPTOPS 2 1190 0 PREEMPTIVE_OS_CRYPTACQUIRECONTEX 279 193 0 PREEMPTIVE_OS_CRYPTIMPORTKEY 197 137 0 PREEMPTIVE_OS_DEVICEOPS 0 0 0 PREEMPTIVE_OS_RSFXDEVICEOPS 0 0 0 PREEMPTIVE_OS_DIRSVC_NETWORKOPS 0 0 0 PREEMPTIVE_OS_DSGETDCNAME 0 0 0 PREEMPTIVE_OS_NETGROUPGETUSERS 0 0 0 PREEMPTIVE_OS_NETLOCALGROUPGETME 0 0 0 PREEMPTIVE_OS_NETUSERGETGROUPS 0 0 0 PREEMPTIVE_OS_NETUSERGETLOCALGRO 0 0 0 PREEMPTIVE_OS_NETUSERMODALSGET 0 0 0 PREEMPTIVE_OS_NETVALIDATEPASSWOR 18 89 0 PREEMPTIVE_OS_NETVALIDATEPASSWOR 18 0 0 PREEMPTIVE_OS_DOMAINSERVICESOPS 1 46 0 PREEMPTIVE_OS_DTCOPS 0 0 0 PREEMPTIVE_DTC_ABORT 0 0 0 PREEMPTIVE_DTC_ABORTREQUESTDONE 0 0 0 PREEMPTIVE_DTC_BEGINTRANSACTION 0 0 0 PREEMPTIVE_DTC_COMMITREQUESTDONE 0 0 0 PREEMPTIVE_DTC_ENLIST 0 0 0 PREEMPTIVE_DTC_PREPAREREQUESTDON 0 0 0 PREEMPTIVE_OS_FILEOPS 487 4421 0 PREEMPTIVE_OS_CLOSEHANDLE 1 0 0 PREEMPTIVE_OS_COPYFILE 0 0 0 PREEMPTIVE_OS_CREATEDIRECTORY 0 0 0 PREEMPTIVE_OS_CREATEFILE 133 214 0 PREEMPTIVE_OS_DELETEFILE 7 724 0 PREEMPTIVE_OS_DEVICEIOCONTROL 0 0 0 PREEMPTIVE_OS_FINDFILE 0 0 0 PREEMPTIVE_FILESIZEGET 25 8 0 PREEMPTIVE_OS_FLUSHFILEBUFFERS 264 72 0 PREEMPTIVE_OS_GETCOMPRESSEDFILES 0 0 0 PREEMPTIVE_OS_GETDISKFREESPACE 259 139 0 PREEMPTIVE_OS_GETFILEATTRIBUTES 139 98 0 PREEMPTIVE_OS_GETFILESIZE 0 0 0 PREEMPTIVE_OS_GETLONGPATHNAME 0 0 0 PREEMPTIVE_OS_GETVOLUMEPATHNAME 18 10 0 PREEMPTIVE_OS_GETVOLUMENAMEFORVO 21 3 0 PREEMPTIVE_OS_MOVEFILE 0 0 0 PREEMPTIVE_OS_OPENDIRECTORY 0 0 0 PREEMPTIVE_OS_REMOVEDIRECTORY 0 0 0 PREEMPTIVE_OS_SETENDOFFILE 0 0 0 PREEMPTIVE_OS_SETFILEPOINTER 0 0 0 PREEMPTIVE_OS_SETFILEVALIDDATA 0 0 0 PREEMPTIVE_OS_WRITEFILE 0 0 0 PREEMPTIVE_OS_WRITEFILEGATHER 260 1376324 0 PREEMPTIVE_OS_LIBRARYOPS 79 1737 0 PREEMPTIVE_OS_FREELIBRARY 0 0 0 PREEMPTIVE_OS_GETPROCADDRESS 1319 34240 0 PREEMPTIVE_OS_LOADLIBRARY 6 271 0 PREEMPTIVE_OS_MESSAGEQUEUEOPS 0 0 0 PREEMPTIVE_ODBCOPS 0 0 0 PREEMPTIVE_OLEDBOPS 388387 728 0 PREEMPTIVE_OLEDB_ABORTTRAN 0 0 0 PREEMPTIVE_OLEDB_ABORTORCOMMITTR 0 0 0 PREEMPTIVE_OLEDB_GETDATASOURCE 0 0 0 PREEMPTIVE_OLEDB_GETLITERALINFO 0 0 0 PREEMPTIVE_OLEDB_GETPROPERTIES 0 0 0 PREEMPTIVE_OLEDB_GETPROPERTYINFO 0 0 0 PREEMPTIVE_OLEDB_GETSCHEMALOCK 0 0 0 PREEMPTIVE_OLEDB_JOINTRANSACTION 0 0 0 PREEMPTIVE_OLEDB_RELEASE 0 0 0 PREEMPTIVE_OLEDB_SETPROPERTIES 0 0 0 PREEMPTIVE_OS_PIPEOPS 1 1206 0 PREEMPTIVE_OS_DISCONNECTNAMEDPIP 1691 600 0 PREEMPTIVE_OS_PROCESSOPS 0 0 0 PREEMPTIVE_OS_SECURITYOPS 0 0 0 PREEMPTIVE_OS_SERVICEOPS 0 0 0 PREEMPTIVE_OS_SQLCLROPS 0 0 0 PREEMPTIVE_OS_WINSOCKOPS 0 0 0 PREEMPTIVE_OS_GETADDRINFO 0 0 0 PREEMPTIVE_OS_WSASETLASTERROR 0 0 0 PREEMPTIVE_OS_FORMATMESSAGE 0 0 0 PREEMPTIVE_OS_REPORTEVENT 175 439 0 PREEMPTIVE_OS_BACKUPREAD 0 0 0 PREEMPTIVE_OS_WAITFORSINGLEOBJEC 1633 14385 0 PREEMPTIVE_OS_QUERYREGISTRY 9973 16409 0 PREEMPTIVE_CLOSEBACKUPMEDIA 0 0 0 PREEMPTIVE_CLOSEBACKUPTAPE 0 0 0 PREEMPTIVE_CLOSEBACKUPVDIDEVICE 0 0 0 PREEMPTIVE_OS_VSSOPS 0 0 0 PREEMPTIVE_VSS_CREATESNAPSHOT 0 0 0 PREEMPTIVE_VSS_CREATEVOLUMESNAPS 0 0 0 PREEMPTIVE_DFSADDLINK 0 0 0 PREEMPTIVE_DFSLINKEXISTCHECK 0 0 0 PREEMPTIVE_DFSLINKHEALTHCHECK 0 0 0 PREEMPTIVE_DFSREMOVELINK 0 0 0 PREEMPTIVE_DFSREMOVEROOT 0 0 0 PREEMPTIVE_DFSROOTFOLDERCHECK 0 0 0 PREEMPTIVE_DFSROOTINIT 0 0 0 PREEMPTIVE_DFSROOTSHARECHECK 0 0 0 PREEMPTIVE_OLE_UNINIT 0 0 0 PREEMPTIVE_FSAOLEDB_ABORTTRANSAC 0 0 0 PREEMPTIVE_FSAOLEDB_COMMITTRANSA 0 0 0 PREEMPTIVE_FSAOLEDB_STARTTRANSAC 0 0 0 PREEMPTIVE_FSRECOVER_UNCONDITION 0 0 0 PREEMPTIVE_SERVER_STARTUP 0 0 0 PREEMPTIVE_SHAREDMEM_GETDATA 0 0 0 PREEMPTIVE_CONSOLEWRITE 0 0 0 PREEMPTIVE_OS_SQMLAUNCH 8 4 0 PREEMPTIVE_TESTING 0 0 0 PREEMPTIVE_SOSHOST 0 0 0 PREEMPTIVE_SOSTESTING 0 0 0 PREEMPTIVE_XETESTING 0 0 0 PREEMPTIVE_SB_STOPENDPOINT 0 0 0 PREEMPTIVE_STARTRM 0 0 0 PREEMPTIVE_GETRMINFO 0 0 0 PREEMPTIVE_SETRMINFO 0 0 0 PREEMPTIVE_ROLLFORWARDREDO 0 0 0 PREEMPTIVE_ROLLFORWARDUNDO 0 0 0 PREEMPTIVE_RESIZELOG 0 0 0 PREEMPTIVE_REENLIST 0 0 0 PREEMPTIVE_TRANSIMPORT 0 0 0 PREEMPTIVE_UNMARSHALPROPAGATIONT 0 0 0 PREEMPTIVE_CREATEPARAM 0 0 0 PREEMPTIVE_STREAMFCB_RECOVER 0 0 0 PREEMPTIVE_STREAMFCB_CHECKPOINT 0 0 0 PREEMPTIVE_XE_CALLBACKEXECUTE 233390 374 0 PREEMPTIVE_XE_DISPATCHER 1 0 0 PREEMPTIVE_XE_ENGINEINIT 0 0 0 PREEMPTIVE_XE_GETTARGETSTATE 0 0 0 PREEMPTIVE_XE_SESSIONCOMMIT 1 0 0 PREEMPTIVE_XE_TARGETFINALIZE 0 0 0 PREEMPTIVE_XE_TARGETINIT 1 0 0 PREEMPTIVE_XE_TIMERRUN 1 0 0 PREEMPTIVE_SNIOPEN 0 0 0 PREEMPTIVE_DEBUG 0 0 0 PREEMPTIVE_MSS_RELEASE 0 0 0 PREEMPTIVE_LOCKMONITOR 1 0 0 PREEMPTIVE_STRESSDRIVER 0 0 0 CLEAR_DB 0 0 0 PREEMPTIVE_ABR 0 0 0 LOGGENERATION 0 0 0 IO_RETRY 0 0 0 WRITE_COMPLETION 2956 4274 30 AUDIT_XE_SESSION_MGR 0 0 0 AUDIT_ON_DEMAND_TARGET_LOCK 0 0 0 PREEMPTIVE_AUDIT_ACCESS_EVENTLOG 0 0 0 PREEMPTIVE_AUDIT_ACCESS_SECLOG 0 0 0 AUDIT_LOGINCACHE_LOCK 0 0 0 AUDIT_GROUPCACHE_LOCK 0 0 0 FT_METADATA_MUTEX 0 0 0 FT_IFTSHC_MUTEX 1 1315 1 FT_IFTSISM_MUTEX 0 0 0 FT_IFTS_RWLOCK 0 0 0 FT_COMPROWSET_RWLOCK 0 0 0 FT_MASTER_MERGE 0 0 0 TRACE_EVTNOTIF 0 0 0 SOS_SMALL_PAGE_ALLOC 0 0 0 METADATA_LAZYCACHE_RWLOCK 0 0 0 IOAFF_RANGE_QUEUE 0 0 0 FT_IFTS_SCHEDULER_IDLE_WAIT 9898 5,938335E+08 2931 REPL_HISTORYCACHE_ACCESS 0 0 0 REPL_TRANHASHTABLE_ACCESS 0 0 0 PERFORMANCE_COUNTERS_RWLOCK 2 13 0 Total 6,465084E+07 5,461975E+09 1,200086E+09 (486 row(s) affected) DBCC execution completed. If DBCC printed error messages, contact your system administrator. 4) DBCC SQLPERF(NETSTATS) Statistic Value -------------------------------- ------------- Network Reads 4,936478E+07 Network Writes 3,751799E+07 Network Bytes Read -1,686942E+09 Network Bytes Written -5,52061E+08 Command Queue Length 0 Max Command Queue Length 0 Worker Threads 0 Max Worker Threads 0 Network Threads 0 Max Network Threads 0
///////////////////////////////////////////////////////////////////////
How should I interpret results of the above commands? What are the best ways of identifying the reason of the low performance?
Thank you!Zaur Bahramov0 -
Some more information on the current setup:
Our systsem is Windows Server 2008 R2 x64 which in fact is a VM on ESX 3.5 . We are going to do an upgrade to vSphere soon, since ESX 3.5 has only an experimental support for Windows Server 2008.
As for the database it's about 150 Gb, log file is on a separate disk.
RAM 8 Gb - I've configured Minimum memory on SQL as 0 and max as 6656. This was I give 1.5Gb to OS and the rest 6.5 Gb to SQL.
After restoring an fbk to sql 2008 I created several maintenance tasks as follows and ran them:
1) Update statistics
2) Rebuild and reorganize indexes
3) Integrity check
Compatibility level set to 100.
In general that's all. Also, in the very beginning I've configured the paging file as about 12Gb both min and max size, but then noticed in the Task Manager -> Performance that the Memory column was all green (7.6Gb), i.e. using all RAM for some reason, even when server was idle. I've set the paging file to be managed by windows, and ot it's between 2.6-3.2Gb even when procs are running.
I've asked my chief to re-launch his procedure, that was usually running 18 hrs on old server, and that didn't complete even after 4 days on a new server, and will see what happens.
Can you advise smth else on server configuration? What counters should I use in these scenario, which DMVs to run and which values to read/compare?Zaur Bahramov0 -
Without telling us what it is you are trying to do, it is a guessing game for anyone. If you don't know why you are doing this then maybe you shouldn't be doing it.
Looks like you have a set of custom developed stored procedures to retrieve system data. Was that a custom job just for you or did you get some sort of toolset? Have you tried to get the people/company that wrote those to come in and give you some support? I think 24 hours to run any job on SQL Server is too much, let alone 4 days.0 -
No... I don't know exactly what the procedure is doing. I can't read C/AL code since I know nothing about programming in NAV.
What happens is the following: Our Navision supporting company has told me to backup database using Navision 3.70 on our production server, then restore it on windows 2008/sql server 2008 server using the client 5.0 SP1.
That's what I've actually did. Then my chief started his procedure which he usually runs on SQL Server 2000/Navision 3.70 and this procedure runs extremely long time.
So, I just need to figure out, whether this is due to the bad configuration of SQL server or actually inside this procedure and if so, ask our Navision supporting company to take a further look and make modifications as neccessary.
My problem now is that I can't identify the source of problem... ](*,)Zaur Bahramov0 -
I'm sorry but without knowing what you are doing and without any clue as to what kind of problem you are having, how are we supposed to know what is wrong and how to fix it? How are you supposed to be able to troubleshoot your chief's SQL routine if you don't even know what it does? Did he write it or did he buy it somewhere? You need to find out what it is supposed to do and see if there are any alternatives. Right now it is a wild guessing game.
In general, when you are faced with implementing best practices, you should get someone involved that knows what they are doing. If I were you, I would get your partner involved, and get them to look at your system. If they don't know about SQL Server or virtualization, then find someone who does know about this.0 -
Well, I spoke with my chief, I spoke with the our Navision supporter, they all say, that the procedure is very very simple. It takes entries from one table, makes totalization and puts data into another table. When this data is obsolete, my chief goes to SQL server, runs truncate on the resulting table (from t-sql) and re-runs this procedure. So, there's really nothing that could go wrong with the Navision procedure itself. The problem is that it runs much faster on SQL 2000, and takes ages on SQL 2008.
I've checked one by one the settings resulted by:
EXEC sp_configure 'Show Advanced Options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure;
on both servers. They are similar.
So, once again, I know, what could be the problem, but I need some clue to resolve it. The clue could be one or all of the following:
1) Version of ESX server, which is 3.5 on which we run Windows 2008/SQL 2008 64bit
2) Settings of Windows 2008 and Sql 2008.
Since the procedure which I've described is really simple and it runs on both servers without errors, there's a very little chance that something is wrong with this procedure itself. I'm asking for help whether someone has already encountered such latencies on Windows 2008 server R2 x64bit and SQL Server 2008 Std environment in the ESX 3.5 context. I know, maybe it's kind of specific to server administration rather than navision programming, but I was hoping that someone could help me to identify the problem.
If necessary, I can write here more details about the procedure, even post the code behind forms. But am just absolutely not sure that the problem lays in C/Al and procedure itself...Zaur Bahramov0 -
I guess you've already decided then that it can't possibly be the SQL stored procedure, even though it runs for 18 hours in production and for 2.5 days on another server. Good luck solving your problem, I can't help you any further than this.0
-
You are absolutely right! Looks like it IS a proc. Namely, sp_execute and sp_cursorexecute taking long time to run. I have checked in the trace log. Both SPs have very high CPU, Reads, Writes and Duration.
TextData CPU Reads Writes Duration exec sp_execute 368,1,3,5,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2009-09-30 00:00:00' 0 7 0 276 declare @p2 int set @p2=180157011 declare @p3 int set @p3=2 declare @p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute 1073742190,@p2 output,@p3 output,@p4 output,@p5 output,1,3,5,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2009-09-30 00:00:00' select @p2, @p3, @p4, @p5 32 1056 0 30383 exec sp_execute 85,1,3,5,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2009-09-30 00:00:00' 0 13 0 419 exec sp_execute 369,1,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2009-09-30 00:00:00' 0 4 0 112 declare @p2 int set @p2=180157013 declare @p3 int set @p3=2 declare @p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute 1073742191,@p2 output,@p3 output,@p4 output,@p5 output,1,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2009-09-30 00:00:00' select @p2, @p3, @p4, @p5 0 7 0 175 exec sp_cursorclose 180157013 0 0 0 58 exec sp_execute 369,5,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2009-09-30 00:00:00' 0 5 0 133 declare @p2 int set @p2=180157015 declare @p3 int set @p3=2 declare @p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute 1073742191,@p2 output,@p3 output,@p4 output,@p5 output,5,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2009-09-30 00:00:00' select @p2, @p3, @p4, @p5 0 7 0 140 exec sp_cursorclose 180157015 0 0 0 32 exec sp_execute 369,3,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2009-09-30 00:00:00' 0 4 0 127 declare @p2 int set @p2=180157017 declare @p3 int set @p3=2 declare @p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute 1073742191,@p2 output,@p3 output,@p4 output,@p5 output,3,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2009-09-30 00:00:00' select @p2, @p3, @p4, @p5 0 7 0 120 exec sp_cursorclose 180157003 0 0 0 24 exec sp_execute 369,4,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2009-09-30 00:00:00' 0 4 0 273 declare @p2 int set @p2=180157019 declare @p3 int set @p3=2 declare @p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute 1073742182,@p2 output,@p3 output,@p4 output,@p5 output,4,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2009-09-30 00:00:00' select @p2, @p3, @p4, @p5 0 7 0 186 exec sp_execute 86,4,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2009-09-30 00:00:00' 0 5 0 117 exec sp_execute 362,'0621178','2008-10-01 00:00:00','2009-09-30 00:00:00','FACTORY_NAME' 0 4 0 94 declare @p2 int set @p2=180157021 declare @p3 int set @p3=2 declare @p4 int set @p4=1 declare @p5 int set @p5=5 exec sp_cursorexecute 1073742183,@p2 output,@p3 output,@p4 output,@p5 output,'0621178','2008-10-01 00:00:00','2009-09-30 00:00:00','FACTORY_NAME' select @p2, @p3, @p4, @p5 0 29 0 279 exec sp_cursorfetch 180157021,2,0,20 0 105 0 845 exec sp_cursorfetch 180157021,2,0,56 0 165 0 834 exec sp_cursorclose 180157021 0 1 0 128 exec sp_cursorclose 180157011 0 0 0 37 exec sp_execute 368,0,2,6,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2008-12-31 00:00:00' 0 25 0 347 exec sp_cursorclose 180157009 0 0 0 23 exec sp_execute 361,0,6,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2008-12-31 00:00:00' 0 25 0 217 exec sp_execute 368,1,3,5,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2008-12-31 00:00:00' 0 7 0 103 declare @p2 int set @p2=180157023 declare @p3 int set @p3=2 declare @p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute 1073742190,@p2 output,@p3 output,@p4 output,@p5 output,1,3,5,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2008-12-31 00:00:00' select @p2, @p3, @p4, @p5 62 2288 0 55918 exec sp_execute 85,1,3,5,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2008-12-31 00:00:00' 0 13 0 292 exec sp_cursorclose 180157017 0 0 0 82 exec sp_execute 369,1,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2008-12-31 00:00:00' 0 4 0 103 exec sp_execute 369,5,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2008-12-31 00:00:00' 0 5 0 279 declare @p2 int set @p2=180157025 declare @p3 int set @p3=2 declare @p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute 1073742191,@p2 output,@p3 output,@p4 output,@p5 output,5,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2008-12-31 00:00:00' select @p2, @p3, @p4, @p5 0 7 0 215 exec sp_cursorclose 180157025 0 0 0 28 exec sp_execute 369,3,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2008-12-31 00:00:00' 0 4 0 127 declare @p2 int set @p2=180157027 declare @p3 int set @p3=2 declare @p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute 1073742191,@p2 output,@p3 output,@p4 output,@p5 output,3,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2008-12-31 00:00:00' select @p2, @p3, @p4, @p5 0 7 0 106 exec sp_cursorclose 180157019 0 0 0 21 exec sp_execute 369,4,'0621178','FACTORY_NAME','2008-10-01 00:00:00','2008-12-31 00:00:00' 0 4 0 87 exec sp_execute 362,'0621178','2008-10-01 00:00:00','2008-12-31 00:00:00','FACTORY_NAME' 0 4 0 87 declare @p2 int set @p2=180157029 declare @p3 int set @p3=2 declare @p4 int set @p4=1 declare @p5 int set @p5=5 exec sp_cursorexecute 1073742183,@p2 output,@p3 output,@p4 output,@p5 output,'0621178','2008-10-01 00:00:00','2008-12-31 00:00:00','FACTORY_NAME' select @p2, @p3, @p4, @p5 0 29 0 279 exec sp_cursorfetch 180157029,2,0,20 0 15 0 185 exec sp_cursorclose 180157029 0 1 0 63 exec sp_cursorclose 180157023 0 0 0 28 exec sp_execute 368,0,2,6,'0621178','FACTORY_NAME','2009-01-01 00:00:00','2009-03-31 00:00:00' 0 31 0 301 declare @p2 int set @p2=180157031 declare @p3 int set @p3=2 declare @p4 int set @p4=1 declare @p5 int set @p5=1 exec sp_cursorexecute 1073742190,@p2 output,@p3 output,@p4 output,@p5 output,0,2,6,'0621178','FACTORY_NAME','2009-01-01 00:00:00','2009-03-31 00:00:00' select @p2, @p3, @p4, @p5 0 60 0 1494
I've also checked on SQL Server 2000 and these values are very low on the production server.
I've checked the forum and noticed that some other people also have had the similar problem with these SPs when migrating a DB from 2000 to 2005. In our case it's SQL Server 2008 though.Zaur Bahramov0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions