Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,106,057 hits

  • Select GETDATE()

    November 2014
    M T W T F S S
     12
    3456789
    10111213141516
    17181920212223
    24252627282930

Archive for November, 2014

« Previous Entries

SSMS Internals : What is the meaning of Wait Category in Activity Monitor Under Resource Waits

Posted by blakhani on November 27, 2014


Recently I was working with a friend on performance issue and she asked an simple question.

I use activity monitor tool which is provided with management studio. When I look at the Resource Waits group, there is a column called Wait Category. What does that show and how data is picked? Here is the screenshot from my machine.

At almost same time, same question popped in Facebook group (SQLBangalore) as well. So I decided to dig into more details. Captured the profiler and found that Activity Monitor uses two DMVs to show the values in that section (Resource Waits).

  • sys.dm_os_wait_stats is used to get information for completed waits
  • sys.dm_os_waiting_tasks for threads that are in an in-progress wait.

Here are the various waits we could potentially see.

Wait Category Count(Type)
Backup 7
Buffer I/O 12
Buffer Latch 6
Compilation 3
Full Text Search 3
Idle 26
Latch 9
Lock 21
Logging 5
Memory 6
Network I/O 6
Other 120
Parallelism 3
SQLCLR 17
Transaction 18
User Waits 1

 

How did I find this? By running profiler! As per profiler, the waits related information in Activity Monitor (AM) is populated by procedure #am_generate_waitstats. This is created when AM is launched. The temporary table which is populated in #am_wait_types which has mapping of “display” values and DMV values.

 

category_name wait_type ignore
Backup BACKUP 0
Backup BACKUP_CLIENTLOCK 0
Backup BACKUP_OPERATOR 0
Backup BACKUPBUFFER 0
Backup BACKUPIO 0
Backup BACKUPTHREAD 0
Backup DISKIO_SUSPEND 0
Buffer I/O ASYNC_DISKPOOL_LOCK 0
Buffer I/O ASYNC_IO_COMPLETION 0
Buffer I/O FCB_REPLICA_READ 0
Buffer I/O FCB_REPLICA_WRITE 0
Buffer I/O IO_COMPLETION 0
Buffer I/O PAGEIOLATCH_DT 0
Buffer I/O PAGEIOLATCH_EX 0
Buffer I/O PAGEIOLATCH_KP 0
Buffer I/O PAGEIOLATCH_NL 0
Buffer I/O PAGEIOLATCH_SH 0
Buffer I/O PAGEIOLATCH_UP 0
Buffer I/O REPLICA_WRITES 0
Buffer Latch PAGELATCH_DT 0
Buffer Latch PAGELATCH_EX 0
Buffer Latch PAGELATCH_KP 0
Buffer Latch PAGELATCH_NL 0
Buffer Latch PAGELATCH_SH 0
Buffer Latch PAGELATCH_UP 0
Compilation RESOURCE_SEMAPHORE_MUTEX 0
Compilation RESOURCE_SEMAPHORE_QUERY_COMPILE 0
Compilation RESOURCE_SEMAPHORE_SMALL_QUERY 0
Full Text Search SOAP_READ 0
Full Text Search SOAP_WRITE 0
Full Text Search MSSEARCH 0
Idle ONDEMAND_TASK_QUEUE 1
Idle REQUEST_FOR_DEADLOCK_SEARCH 1
Idle RESOURCE_QUEUE 1
Idle LOGMGR_QUEUE 1
Idle KSOURCE_WAKEUP 1
Idle LAZYWRITER_SLEEP 1
Idle BROKER_RECEIVE_WAITFOR 1
Idle CHECKPOINT_QUEUE 1
Idle CHKPT 1
Idle BROKER_EVENTHANDLER 1
Idle BROKER_TRANSMITTER 1
Idle SERVER_IDLE_CHECK 1
Idle SNI_HTTP_ACCEPT 1
Idle SLEEP_BPOOL_FLUSH 1
Idle SLEEP_DBSTARTUP 1
Idle SLEEP_DCOMSTARTUP 1
Idle SLEEP_MSDBSTARTUP 1
Idle SLEEP_SYSTEMTASK 1
Idle SLEEP_TASK 1
Idle SLEEP_TEMPDBSTARTUP 1
Idle SQLTRACE_BUFFER_FLUSH 1
Idle TRACEWRITE 1
Idle WAITFOR_TASKSHUTDOWN 1
Idle WAIT_FOR_RESULTS 1
Idle XE_DISPATCHER_WAIT 1
Idle XE_TIMER_EVENT 1
Latch VIEW_DEFINITION_MUTEX 0
Latch DEADLOCK_ENUM_MUTEX 0
Latch LATCH_DT 0
Latch LATCH_EX 0
Latch LATCH_KP 0
Latch LATCH_NL 0
Latch LATCH_SH 0
Latch LATCH_UP 0
Latch INDEX_USAGE_STATS_MUTEX 0
Lock LCK_M_BU 0
Lock LCK_M_IS 0
Lock LCK_M_IU 0
Lock LCK_M_IX 0
Lock LCK_M_RIn_NL 0
Lock LCK_M_RIn_S 0
Lock LCK_M_RIn_U 0
Lock LCK_M_RIn_X 0
Lock LCK_M_RS_S 0
Lock LCK_M_RS_U 0
Lock LCK_M_RX_S 0
Lock LCK_M_RX_U 0
Lock LCK_M_RX_X 0
Lock LCK_M_S 0
Lock LCK_M_SCH_M 0
Lock LCK_M_SCH_S 0
Lock LCK_M_SIU 0
Lock LCK_M_SIX 0
Lock LCK_M_U 0
Lock LCK_M_UIX 0
Lock LCK_M_X 0
Logging LOGBUFFER 0
Logging LOGMGR 0
Logging LOGMGR_FLUSH 0
Logging LOGMGR_RESERVE_APPEND 0
Logging WRITELOG 0
Memory UTIL_PAGE_ALLOC 0
Memory SOS_RESERVEDMEMBLOCKLIST 0
Memory SOS_VIRTUALMEMORY_LOW 0
Memory LOWFAIL_MEMMGR_QUEUE 0
Memory RESOURCE_SEMAPHORE 0
Memory CMEMTHREAD 0
Network I/O ASYNC_NETWORK_IO 0
Network I/O DBMIRROR_SEND 0
Network I/O NET_WAITFOR_PACKET 0
Network I/O OLEDB 0
Network I/O MSQL_DQ 0
Network I/O DTC_STATE 0
Other EXECUTION_PIPE_EVENT_INTERNAL 0
Other FAILPOINT 0
Other INTERNAL_TESTING 0
Other IO_AUDIT_MUTEX 0
Other KTM_ENLISTMENT 0
Other KTM_RECOVERY_MANAGER 0
Other KTM_RECOVERY_RESOLUTION 0
Other MSQL_SYNC_PIPE 0
Other MIRROR_SEND_MESSAGE 0
Other MISCELLANEOUS 0
Other MSQL_XP 0
Other REQUEST_DISPENSER_PAUSE 0
Other PARALLEL_BACKUP_QUEUE 0
Other PRINT_ROLLBACK_PROGRESS 0
Other QNMANAGER_ACQUIRE 0
Other QPJOB_KILL 0
Other QPJOB_WAITFOR_ABORT 0
Other QRY_MEM_GRANT_INFO_MUTEX 0
Other QUERY_ERRHDL_SERVICE_DONE 0
Other QUERY_EXECUTION_INDEX_SORT_EVENT_OPEN 0
Other QUERY_NOTIFICATION_MGR_MUTEX 0
Other QUERY_NOTIFICATION_SUBSCRIPTION_MUTEX 0
Other QUERY_NOTIFICATION_TABLE_MGR_MUTEX 0
Other QUERY_NOTIFICATION_UNITTEST_MUTEX 0
Other QUERY_OPTIMIZER_PRINT_MUTEX 0
Other QUERY_REMOTE_BRICKS_DONE 0
Other QUERY_TRACEOUT 0
Other RECOVER_CHANGEDB 0
Other REPL_CACHE_ACCESS 0
Other REPL_SCHEMA_ACCESS 0
Other DBMIRROR_WORKER_QUEUE 0
Other DBMIRRORING_CMD 0
Other DBTABLE 0
Other DAC_INIT 0
Other DBCC_COLUMN_TRANSLATION_CACHE 0
Other DBMIRROR_DBM_EVENT 0
Other DBMIRROR_DBM_MUTEX 0
Other DBMIRROR_EVENTS_QUEUE 0
Other DEADLOCK_TASK_SEARCH 0
Other DEBUG 0
Other DISABLE_VERSIONING 0
Other DLL_LOADING_MUTEX 0
Other DROPTEMP 0
Other DUMP_LOG_COORDINATOR 0
Other DUMP_LOG_COORDINATOR_QUEUE 0
Other DUMPTRIGGER 0
Other EC 0
Other EE_PMOLOCK 0
Other EE_SPECPROC_MAP_INIT 0
Other ENABLE_VERSIONING 0
Other ERROR_REPORTING_MANAGER 0
Other FSAGENT 1
Other FT_RESTART_CRAWL 0
Other FT_RESUME_CRAWL 0
Other FULLTEXT GATHERER 0
Other GUARDIAN 0
Other HTTP_ENDPOINT_COLLCREATE 0
Other HTTP_ENUMERATION 0
Other HTTP_START 0
Other IMP_IMPORT_MUTEX 0
Other IMPPROV_IOWAIT 0
Other ABR 0
Other BROKER_REGISTERALLENDPOINTS 0
Other BROKER_SHUTDOWN 0
Other BROKER_TASK_STOP 1
Other BAD_PAGE_PROCESS 0
Other BROKER_CONNECTION_RECEIVE_TASK 0
Other BROKER_ENDPOINT_STATE_MUTEX 0
Other CURSOR 0
Other CURSOR_ASYNC 0
Other BUILTIN_HASHKEY_MUTEX 0
Other CHECK_PRINT_RECORD 0
Other BROKER_INIT 0
Other BROKER_MASTERSTART 0
Other SOSHOST_EVENT 0
Other SOSHOST_INTERNAL 0
Other SOSHOST_MUTEX 0
Other SOSHOST_RWLOCK 0
Other SOSHOST_SEMAPHORE 0
Other SOSHOST_SLEEP 0
Other SOSHOST_TRACELOCK 0
Other SOSHOST_WAITFORDONE 0
Other SOS_STACKSTORE_INIT_MUTEX 0
Other SOS_SYNC_TASK_ENQUEUE_EVENT 0
Other SHUTDOWN 0
Other SOS_CALLBACK_REMOVAL 0
Other SOS_DISPATCHER_MUTEX 0
Other SOS_LOCALALLOCATORLIST 0
Other SOS_OBJECT_STORE_DESTROY_MUTEX 0
Other SOS_PROCESS_AFFINITY_MUTEX 0
Other SNI_CRITICAL_SECTION 0
Other SNI_HTTP_WAITFOR_0_DISCON 0
Other SNI_LISTENER_ACCESS 0
Other SNI_TASK_COMPLETION 0
Other SEC_DROP_TEMP_KEY 0
Other SEQUENTIAL_GUID 0
Other VIA_ACCEPT 0
Other SQLSORT_NORMMUTEX 0
Other SQLSORT_SORTMUTEX 0
Other WAITSTAT_MUTEX 0
Other WCC 0
Other WORKTBL_DROP 0
Other XE_BUFFERMGR_ALLPROCECESSED_EVENT 0
Other XE_BUFFERMGR_FREEBUF_EVENT 0
Other XE_DISPATCHER_JOIN 0
Other SQLTRACE_LOCK 0
Other SQLTRACE_SHUTDOWN 0
Other SQLTRACE_WAIT_ENTRIES 0
Other SRVPROC_SHUTDOWN 0
Other TEMPOBJ 0
Other THREADPOOL 1
Other TIMEPRIV_TIMEPERIOD 0
Other XE_TIMER_MUTEX 0
Other XE_TIMER_TASK_DONE 0
Other XE_MODULEMGR_SYNC 0
Other XE_OLS_LOCK 0
Other XE_SERVICES_MUTEX 0
Other XE_SESSION_CREATE_SYNC 0
Other XE_SESSION_SYNC 0
Other XE_STM_CREATE 0
Parallelism CXPACKET 1
Parallelism EXCHANGE 1
Parallelism EXECSYNC 1
SQLCLR CLR_AUTO_EVENT 1
SQLCLR CLR_CRST 0
SQLCLR CLR_JOIN 0
SQLCLR CLR_MANUAL_EVENT 1
SQLCLR CLR_MEMORY_SPY 0
SQLCLR CLR_MONITOR 0
SQLCLR CLR_RWLOCK_READER 0
SQLCLR CLR_RWLOCK_WRITER 0
SQLCLR CLR_SEMAPHORE 0
SQLCLR CLR_TASK_START 0
SQLCLR CLRHOST_STATE_ACCESS 0
SQLCLR ASSEMBLY_LOAD 0
SQLCLR FS_GARBAGE_COLLECTOR_SHUTDOWN 0
SQLCLR SQLCLR_APPDOMAIN 0
SQLCLR SQLCLR_ASSEMBLY 0
SQLCLR SQLCLR_DEADLOCK_DETECTION 0
SQLCLR SQLCLR_QUANTUM_PUNISHMENT 0
Transaction TRAN_MARKLATCH_DT 0
Transaction TRAN_MARKLATCH_EX 0
Transaction TRAN_MARKLATCH_KP 0
Transaction TRAN_MARKLATCH_NL 0
Transaction TRAN_MARKLATCH_SH 0
Transaction TRAN_MARKLATCH_UP 0
Transaction TRANSACTION_MUTEX 0
Transaction XACT_OWN_TRANSACTION 0
Transaction XACT_RECLAIM_SESSION 0
Transaction XACTLOCKINFO 0
Transaction XACTWORKSPACE_MUTEX 0
Transaction DTC_TMDOWN_REQUEST 0
Transaction DTC_WAITFOR_OUTCOME 0
Transaction MSQL_XACT_MGR_MUTEX 0
Transaction MSQL_XACT_MUTEX 0
Transaction DTC 0
Transaction DTC_ABORT_REQUEST 0
Transaction DTC_RESOLVE 0
User Waits WAITFOR 1

 

Hope this helps in understanding Activity monitor wait category little better.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOn – Paperback, Kindle

    • Posted in Internals, SQL Server Management Studio, SSMS | Tagged: , , , , , , , | 3 Comments »

    Solution : Error during setup – The specified value for setting ‘MEDIALAYOUT’ is invalid

    Posted by blakhani on November 26, 2014


    While trying to install new instance of SQL Server 2014 using command line and configuration file, I was welcomed by below error message

    TITLE: SQL Server Setup failure.
    ——————————
    SQL Server Setup has encountered the following error:
    The specified value for setting ‘MEDIALAYOUT’ is invalid. The expected values are:
    None
    Core
    Advanced
    Full
    Error code 0x84B40001.
    For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&EvtType=0x28498E8E%25400x2841E06E%25401204%25401
    ——————————
    BUTTONS:
    OK
    ——————————

    The command which I was running was as below

    setup.exe configurationfile="C:\ConfigurationFile.ini"

    Here is the error on command line.

    I went back and looked into log (Detail_LandingPage.txt) files and found below.

    (01) 2014-11-26 06:13:38 Slp: Setting: INSTALLMEDIAPATH
    (01) 2014-11-26 06:13:38 Slp: Value specified: E:\SQL2014\SQLFull_ENU\x64\setup\
    (01) 2014-11-26 06:13:38 Slp: New setting source: CommandLine; previous setting source: NotSpecified
    (01) 2014-11-26 06:13:38 Slp: —————————————-
    (01) 2014-11-26 06:13:38 Slp: Setting: ENU
    (01) 2014-11-26 06:13:38 Slp: Value specified: True
    (01) 2014-11-26 06:13:38 Slp: New setting source: CommandLine; previous setting source: Default
    (01) 2014-11-26 06:13:38 Slp: —————————————-
    (01) 2014-11-26 06:13:38 Slp: Setting: MEDIALAYOUT
    (01) 2014-11-26 06:13:38 Slp: Value specified: Full,configurationfile=C:\ConfigurationFile.ini
    (01) 2014-11-26 06:13:38 Slp: New setting source: CommandLine; previous setting source: Default
    (01) 2014-11-26 06:13:38 Slp: Error: Action "Microsoft.SqlServer.Configuration.BootstrapExtension.ProcessChainerCommandLineArgumentsAction" threw an exception during execution.
    (01) 2014-11-26 06:13:38 Slp: Microsoft.SqlServer.Setup.Chainer.Workflow.ActionExecutionException: The specified value for setting ‘MEDIALAYOUT’ is invalid. The expected values are:
    (01) 2014-11-26 06:13:38 Slp: None
    (01) 2014-11-26 06:13:38 Slp: Core
    (01) 2014-11-26 06:13:38 Slp: Advanced
    (01) 2014-11-26 06:13:38 Slp: Full —> Microsoft.SqlServer.Chainer.Infrastructure.InputSettingValidationException: The specified value for setting ‘MEDIALAYOUT’ is invalid. The expected values are:

     

    Well, I didn’t find any documentation that I need to specify the value. If we look closer at the log, it seems that the value which is taken by the parameter is

    Full,configurationfile=C:\ConfigurationFile.ini

    Wait a second!!! it has parameter which I have specified. I looked into documentation and found that parameter name should start with “/” Since I have not specified the parameter name configurationfile with a slash. Same error can also happen if below is used

    setup.exe IACCEPTSQLSERVERLICENSETERMS /configurationfile="C:\ConfigurationFile.ini"

    In above, I have missed slash before IACCEPTSQLSERVERLICENSETERMS

    Bottom line:  Check the setup Logs and find which parameter is not having correct value and what is the value passed and taken from setup.exe

    Hope this helps.
    Balmukund

    Posted in Installation | Tagged: , , | 5 Comments »

    Help : How to Downgrade SQL Server Edition (Enterprise to Developer/Standard)

    Posted by blakhani on November 25, 2014


    Life is beautiful when you have good friend around you. If you are regular reader of my blog, you would understand what kind of friends I have. Anyways, as usual, another friend, another interesting question and another blog topic.

    My dear friend called and told that he has enterprise edition Installed on his laptop and it was “accidental" install. His end goal was to use developer edition but “someone” installed enterprise. Since he has done a lot of work in this database and wanted to make sure his data is not lost, he called me for advice. In short, he wanted to change his edition from enterprise to developer with no data loss.

    Well, upgrading edition of SQL Server is easy (my earlier blog) and downgrade is not “so” easy. We are calling it as “downgrade” because its not listed as supported upgrade on books online. If you are using features which are not supported in edition where we want to downgrade (like enterprise to standard) then it’s tricky as we have to remove those features first. Note that Developer and Enterprise are exactly the same so my friend was little safe. I must confess that I am not the inventor of this “downgrade trick”. It is well known and tested by many earlier. I am just writing them down so that it would help someone in future. Standard disclaimer applied on this blog post.

    Before attempting the steps we should take backup of ALL databases (system and user). Make a note of location of various component (system databases, shared components) in operating system. The trick would work only if the paths are not changed.

    • Open Reporting Service Configuration Manager and take a backup the encryption keys.
    • Use SQL Server Management Studio and connect to SQL. We need to run the following query on each database.  

      SELECT       * FROM sys.dm_db_persisted_sku_features
    • Above DMV will tell us if the database is utilizing any of the “Enterprise only” features (like Partitioning, Compression etc.). If there is any such feature which is not supported on destination editions then we should remove it otherwise database would not come online after last step. In my friend’s case he was using compression but it works in developer edition as well so there was no need to remove that. If there are no rows returned in any database then we are good to move next.
    • Run SELECT @@VERSION and make a note of exact version and build number (like 11.00.3000 –  which is SQL 2012 + SP1) . This is needed because we have to upgrade the newly installed SQL instance to the exact same build later.
    • Stop SQL Server service. Copy all the database files. We need to copy all mdf, ldf and ndf files for system and user databases.
    • Now we can safely uninstall SQL Server. You should take a screenshot of the “Select Features” screen while uninstalling so that you could be certain to install the correct features when installing again in later steps.
    • Reboot, if necessary.
    • Now, install New SQL Server Server instance having SAME name and SAME path as of earlier instance.
    • Since we want to reuse databases, we need to apply SQL Server Patches so that the version matches with what we had earlier. (11.00.3000 in my example)
    • Stop SQL Server service.
    • Take backup of current files to make sure we can revert to this state.
    • Move all databases files back to their original locations. We also need to replace the system database files with the previous one.
    • Now we need to Start SQL Service again.
    • Verify that databases are online and healthy again with new Edition!

    Someone said that this is old trick and used in Sybase as well. And my answer – hmm.. Yeah, I am old now.

    Posted in SQL Server | Tagged: , , , , , | 5 Comments »

    « Previous Entries