Hi Fellows
I´m transfering my blog activities to the live space… it´s better to embed photos documents, etc… besides i got a plenty of space on SKy Drive, which is very cool. And i could stay in touch with my fellows around the globe. I hope you enjoy the new experience…
http://wandenkolk.spaces.live.com/blog/
Kind regards.
Wanden
http://www.microsoft.com/azure/sql.mspx
Imagine do not have to have a database.. you only consume database services from a cloud of servers somewhere…
:)
The question is, can we spy on procedure cache to see all the plans stored in there?
Yes we can! (by the way, congrats to the new president)
Select e.cacheobjtype, q.query_plan , DB_NAME(q.dbid) as database_name, e.refcounts, e.usecounts , (e.size_in_bytes / 1024 ) as size_in_kb from sys.dm_Exec_cached_plans as e Cross Apply sys.dm_exec_query_plan(e.plan_handle) as q
The query returns the following result:`(i know is a bit messy, but who cares?)
cacheobjtype query_plan database_name refcounts usecounts size_in_kb
-------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- -----------
Compiled Plan <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.1600.22"><BatchSequence><Batch><Statements><StmtSimple StatementText="
CREATE PROCEDURE [dbo].[CleanExpiredJobs]
AS
SET NOCOUN ReportServer 2 2 48
Compiled Plan <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.1600.22"><BatchSequence><Batch><Statements><StmtSimple StatementText="
CREATE PROCEDURE [dbo].[CleanOrphanedSnapshots]
@Machine nvarcha ReportServer 2 2 1096
Compiled Plan <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.1600.22"><BatchSequence><Batch><Statements><StmtSimple StatementText="
CREATE PROCEDURE [dbo].[CleanExpiredCache]
AS
SET NOCOU ReportServer 2 2 96
Compiled Plan <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1" Build="10.0.1600.22"><BatchSequence><Batch><Statements><StmtSimple StatementText="
CREATE PROCEDURE [dbo].[DeleteExpiredPersistedStreams]
AS
&# ReportServer 2 2 56
Regarding waiting for resources on sql server, one of the most valuable resources is the DMV above mentioned. But the point is, what on earth means the wait type. Follows an excerpt from SQL Server Books Online which explains the meaning of each one wait types..
Select * from sys.dm_os_wait_stats Order by Wait_time_ms DESC -- for example
wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
------------------------------------------------------------ -------------------- -------------------- -------------------- --------------------
LAZYWRITER_SLEEP 77093 77124252 1218 7602
REQUEST_FOR_DEADLOCK_SEARCH 15425 77123140 5094 77123140
...
The following table lists the wait types encountered by tasks.
Wait type
Description
ASYNC_DISKPOOL_LOCK
Occurs when there is an attempt to synchronize parallel threads that are performing tasks such as creating or initializing a file.
ASYNC_IO_COMPLETION
Occurs when a task is waiting for I/Os to finish.
ASYNC_NETWORK_IO
Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server.
BACKUP
Occurs when a task is blocked as part of backup processing.
BACKUP_CLIENTLOCK
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
BACKUP_OPERATOR
Occurs when a task is waiting for a tape mount. To view the tape status, query sys.dm_io_backup_tapes. If a mount operation is not pending, this wait type may indicate a hardware problem with the tape drive.
BACKUPBUFFER
Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount.
BACKUPIO
Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount.
BACKUPTHREAD
Occurs when a task is waiting for a backup task to finish. Wait times may be long, from several minutes to several hours. If the task that is being waited on is in an I/O process, this type does not indicate a problem.
BAD_PAGE_PROCESS
Occurs when the background suspect page logger is trying to avoid running more than every five seconds. Excessive suspect pages cause the logger to run frequently.
BROKER_CONNECTION_RECEIVE_TASK
Occurs when waiting for access to receive a message on a connection endpoint. Receive access to the endpoint is serialized.
BROKER_ENDPOINT_STATE_MUTEX
Occurs when there is contention to access the state of a Service Broker connection endpoint. Access to the state for changes is serialized.
BROKER_EVENTHANDLER
Occurs when a task is waiting in the primary event handler of the Service Broker. This should occur very briefly.
BROKER_INIT
Occurs when initializing Service Broker in each active database. This should occur infrequently.
BROKER_MASTERSTART
Occurs when a task is waiting for the primary event handler of the Service Broker to start. This should occur very briefly.
BROKER_RECEIVE_WAITFOR
Occurs when the RECEIVE WAITFOR is waiting. This is typical if no messages are ready to be received.
BROKER_REGISTERALLENDPOINTS
Occurs during the initialization of a Service Broker connection endpoint. This should occur very briefly.
BROKER_SHUTDOWN
Occurs when there is a planned shutdown of Service Broker. This should occur very briefly, if at all.
BROKER_TRANSMITTER
Occurs when the Service Broker transmitter is waiting for work.
BUILTIN_HASHKEY_MUTEX
May occur after startup of instance, while internal data structures are initializing. Will not recur once data structures have initialized.
CHECKPOINT_QUEUE
Occurs while the checkpoint task is waiting for the next checkpoint request.
CHKPT
Occurs at server startup to tell the checkpoint thread that it can start.
CLR_AUTO_EVENT
Occurs when a task is currently performing common language runtime (CLR) execution and is waiting for a particular autoevent to be initiated. Long waits are typical, and do not indicate a problem.
CLR_CRST
Occurs when a task is currently performing CLR execution and is waiting to enter a critical section of the task that is currently being used by another task.
CLR_JOIN
Occurs when a task is currently performing CLR execution and waiting for another task to end. This wait state occurs when there is a join between tasks.
CLR_MANUAL_EVENT
Occurs when a task is currently performing CLR execution and is waiting for a specific manual event to be initiated.
CLR_MONITOR
Occurs when a task is currently performing CLR execution and is waiting to obtain a lock on the monitor.
CLR_RWLOCK_READER
Occurs when a task is currently performing CLR execution and is waiting for a reader lock.
CLR_RWLOCK_WRITER
Occurs when a task is currently performing CLR execution and is waiting for a writer lock.
CLR_SEMAPHORE
Occurs when a task is currently performing CLR execution and is waiting for a semaphore.
CLR_TASK_START
Occurs while waiting for a CLR task to complete startup.
CMEMTHREAD
Occurs when a task is waiting on a thread-safe memory object. The wait time might increase when there is contention caused by multiple tasks trying to allocate memory from the same memory object.
CURSOR
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
CURSOR_ASYNC
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
CXPACKET
Occurs when trying to synchronize the query processor exchange iterator. You may consider lowering the degree of parallelism if contention on this wait type becomes a problem.
DBMIRROR_DBM_EVENT
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
DBMIRROR_DBM_MUTEX
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
DBMIRROR_EVENTS_QUEUE
Occurs when database mirroring waits for events to process.
DBMIRROR_SEND
Occurs when a task is waiting for a communications backlog at the network layer to clear to be able to send messages. Indicates that the communications layer is starting to become overloaded and affect the database mirroring data throughput.
DBMIRROR_WORKER_QUEUE
Indicates that the database mirroring worker task is waiting for more work.
DBMIRRORING_CMD
Occurs when a task is waiting for log records to be flushed to disk. This wait state is expected to be held for long periods of time.
DBTABLE
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
DEADLOCK_ENUM_MUTEX
Occurs when the deadlock monitor and sys.dm_os_waiting_tasks try to make sure that SQL Server is not running multiple deadlock searches at the same time.
DEADLOCK_TASK_SEARCH
Large waiting time on this resource indicates that the server is executing queries on top of sys.dm_os_waiting_tasks, and these queries are blocking deadlock monitor from running deadlock search. This wait type is used by deadlock monitor only. Queries on top of sys.dm_os_waiting_tasks use DEADLOCK_ENUM_MUTEX.
DEBUG
Occurs during Transact-SQL and CLR debugging for internal synchronization.
DISABLE_VERSIONING
Occurs when SQL Server polls the version transaction manager to see whether the timestamp of the earliest active transaction is later than the timestamp of when the state started changing. If this is this case, all the snapshot transactions that were started before the ALTER DATABASE statement was run have finished. This wait state is used when SQL Server disables versioning by using the ALTER DATABASE statement.
DISKIO_SUSPEND
Occurs when a task is waiting to access a file when an external backup is active. This is reported for each waiting user process. A count larger than five per user process may indicate that the external backup is taking too much time to finish.
DLL_LOADING_MUTEX
Occurs once while waiting for the XML parser DLL to load.
DROPTEMP
Occurs between attempts to drop a temporary object if the previous attempt failed. The wait duration grows exponentially with each failed drop attempt.
DTC
Occurs when a task is waiting on an event that is used to manage state transition. This state controls when the recovery of Microsoft Distributed Transaction Coordinator (MS DTC) transactions occurs after SQL Server receives notification that the MS DTC service has become unavailable.
This state also describes a task that is waiting when a commit of a MS DTC transaction is initiated by SQL Server and SQL Server is waiting for the MS DTC commit to finish.
DTC_ABORT_REQUEST
Occurs in a MS DTC worker session when the session is waiting to take ownership of a MS DTC transaction. After MS DTC owns the transaction, the session can roll back the transaction. Generally, the session will wait for another session that is using the transaction.
DTC_RESOLVE
Occurs when a recovery task is waiting for the master database in a cross-database transaction so that the task can query the outcome of the transaction.
DTC_STATE
Occurs when a task is waiting on an event that protects changes to the internal MS DTC global state object. This state should be held for very short periods of time.
DTC_TMDOWN_REQUEST
Occurs in a MS DTC worker session when SQL Server receives notification that the MS DTC service is not available. First, the worker will wait for the MS DTC recovery process to start. Then, the worker waits to obtain the outcome of the distributed transaction that the worker is working on. This may continue until the connection with the MS DTC service has been reestablished.
DTC_WAITFOR_OUTCOME
Occurs when recovery tasks wait for MS DTC to become active to enable the resolution of prepared transactions.
DUMP_LOG_COORDINATOR
Occurs when a main task is waiting for a subtask to generate data. Ordinarily, this state does not occur. A long wait indicates an unexpected blockage. The subtask should be investigated.
EC
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
EE_PMOLOCK
Occurs during synchronization of certain types of memory allocations during statement execution.
EE_SPECPROC_MAP_INIT
Occurs during synchronization of internal procedure hash table creation. This wait can only occur during the initial accessing of the hash table after the SQL Server instance starts.
ENABLE_VERSIONING
Occurs when SQL Server waits for all update transactions in this database to finish before declaring the database ready to transition to snapshot isolation allowed state. This state is used when SQL Server enables snapshot isolation by using the ALTER DATABASE statement.
ERROR_REPORTING_MANAGER
Occurs during synchronization of multiple concurrent error log initializations.
EXCHANGE
Occurs during synchronization in the query processor exchange iterator during parallel queries.
EXECSYNC
Occurs during parallel queries while synchronizing in query processor in areas not related to the exchange iterator. Examples of such areas are bitmaps, large binary objects (LOBs), and the spool iterator. LOBs may frequently use this wait state.
FAILPOINT
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
FCB_REPLICA_READ
Occurs when the reads of a snapshot (or a temporary snapshot created by DBCC) sparse file are synchronized.
FCB_REPLICA_WRITE
Occurs when the pushing or pulling of a page to a snapshot (or a temporary snapshot created by DBCC) sparse file is synchronized.
FT_RESTART_CRAWL
Occurs when a full-text crawl needs to restart from a last known good point to recover from a transient failure. The wait lets the worker tasks currently working on that population to complete or exit the current step.
FT_RESUME_CRAWL
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
FULLTEXT GATHERER
Occurs during synchronization of full-text operations.
HTTP_ENDPOINT_COLLCREATE
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
HTTP_ENUMERATION
Occurs at startup to enumerate the HTTP endpoints to start HTTP.
HTTP_START
Occurs when a connection is waiting for HTTP to complete initialization.
IMP_IMPORT_MUTEX
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
IMPPROV_IOWAIT
Occurs when SQL Server waits for a bulkload I/O to finish.
INDEX_USAGE_STATS_MUTEX
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
IO_AUDIT_MUTEX
Occurs during synchronization of trace event buffers.
IO_COMPLETION
Occurs while waiting for I/O operations to complete. This wait type generally represents non-data page I/Os. Data page I/O completion waits appear as PAGEIOLATCH_* waits.
KSOURCE_WAKEUP
Used by the service control task while waiting for requests from the Service Control Manager. Long waits are expected and do not indicate a problem.
KTM_ENLISTMENT
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
KTM_RECOVERY_MANAGER
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
KTM_RECOVERY_RESOLUTION
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
LATCH_DT
Occurs when waiting for a DT (destroy) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.
LATCH_EX
Occurs when waiting for an EX (exclusive) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.
LATCH_KP
Occurs when waiting for a KP (keep) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.
LATCH_NL
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
LATCH_SH
Occurs when waiting for an SH (share) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.
LATCH_UP
Occurs when waiting for an UP (update) latch. This does not include buffer latches or transaction mark latches. A listing of LATCH_* waits is available in sys.dm_os_latch_stats. Note that sys.dm_os_latch_stats groups LATCH_NL, LATCH_SH, LATCH_UP, LATCH_EX, and LATCH_DT waits together.
LAZYWRITER_SLEEP
Occurs when lazywriter tasks are suspended. This is a measure of the time spent by background tasks that are waiting. Do not consider this state when you are looking for user stalls.
LCK_M_BU
Occurs when a task is waiting to acquire a Bulk Update (BU) lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_IS
Occurs when a task is waiting to acquire an Intent Shared (IS) lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_IU
Occurs when a task is waiting to acquire an Intent Update (IU) lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_IX
Occurs when a task is waiting to acquire an Intent Exclusive (IX) lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_RIn_NL
Occurs when a task is waiting to acquire a NULL lock on the current key value, and an Insert Range lock between the current and previous key. A NULL lock on the key is an instant release lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_RIn_S
Occurs when a task is waiting to acquire a shared lock on the current key value, and an Insert Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_RIn_U
Task is waiting to acquire an Update lock on the current key value, and an Insert Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_RIn_X
Occurs when a task is waiting to acquire an Exclusive lock on the current key value, and an Insert Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_RS_S
Occurs when a task is waiting to acquire a Shared lock on the current key value, and a Shared Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_RS_U
Occurs when a task is waiting to acquire an Update lock on the current key value, and an Update Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_RX_S
Occurs when a task is waiting to acquire a Shared lock on the current key value, and an Exclusive Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_RX_U
Occurs when a task is waiting to acquire an Update lock on the current key value, and an Exclusive range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_RX_X
Occurs when a task is waiting to acquire an Exclusive lock on the current key value, and an Exclusive Range lock between the current and previous key. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_S
Occurs when a task is waiting to acquire a Shared lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_SCH_M
Occurs when a task is waiting to acquire a Schema Modify lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_SCH_S
Occurs when a task is waiting to acquire a Schema Share lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_SIU
Occurs when a task is waiting to acquire a Shared With Intent Update lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_SIX
Occurs when a task is waiting to acquire a Shared With Intent Exclusive lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_U
Occurs when a task is waiting to acquire an Update lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_UIX
Occurs when a task is waiting to acquire an Update With Intent Exclusive lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LCK_M_X
Occurs when a task is waiting to acquire an Exclusive lock. For a lock compatibility matrix, see sys.dm_tran_locks (Transact-SQL).
LOGBUFFER
Occurs when a task is waiting for space in the log buffer to store a log record. Consistently high values may indicate that the log devices cannot keep up with the amount of log being generated by the server.
LOGMGR
Occurs when a task is waiting for any outstanding log I/Os to finish before shutting down the log while closing the database.
LOGMGR_FLUSH
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
LOGMGR_QUEUE
Occurs while the log writer task waits for work requests.
LOGMGR_RESERVE_APPEND
Occurs when a task is waiting to see whether log truncation frees up log space to enable the task to write a new log record. Consider increasing the size of the log file(s) for the affected database to reduce this wait.
LOWFAIL_MEMMGR_QUEUE
Occurs while waiting for memory to be available for use.
MIRROR_SEND_MESSAGE
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
MISCELLANEOUS
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
MSQL_DQ
Occurs when a task is waiting for a distributed query operation to finish. This is used to detect potential Multiple Active Result Set (MARS) application deadlocks. The wait ends when the distributed query call finishes.
MSQL_SYNC_PIPE
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
MSQL_XACT_MGR_MUTEX
Occurs when a task is waiting to obtain ownership of the session transaction manager to perform a session level transaction operation.
MSQL_XACT_MUTEX
Occurs during synchronization of transaction usage. A request must acquire the mutex before it can use the transaction.
MSQL_XP
Occurs when a task is waiting for an extended stored procedure to end. SQL Server uses this wait state to detect potential MARS application deadlocks. The wait stops when the extended stored procedure call ends.
MSSEARCH
Occurs during Full-Text Search calls. This wait ends when the full-text operation completes. It does not indicate contention, but rather the duration of full-text operations.
NET_WAITFOR_PACKET
Occurs when a connection is waiting for a network packet during a network read.
OLEDB
Occurs when SQL Server calls the SQL Server Native Client OLE DB Provider. This wait type is not used for synchronization. Instead, it indicates the duration of calls to the OLE DB provider.
ONDEMAND_TASK_QUEUE
Occurs while a background task waits for high priority system task requests. Long wait times indicate that there have been no high priority requests to process, and should not cause concern.
PAGEIOLATCH_DT
Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Destroy mode. Long waits may indicate problems with the disk subsystem.
PAGEIOLATCH_EX
Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Exclusive mode. Long waits may indicate problems with the disk subsystem.
PAGEIOLATCH_KP
Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Keep mode. Long waits may indicate problems with the disk subsystem.
PAGEIOLATCH_NL
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
PAGEIOLATCH_SH
Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.
PAGEIOLATCH_UP
Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Update mode. Long waits may indicate problems with the disk subsystem.
PAGELATCH_DT
Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Destroy mode.
PAGELATCH_EX
Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Exclusive mode.
PAGELATCH_KP
Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Keep mode.
PAGELATCH_NL
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
PAGELATCH_SH
Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Shared mode.
PAGELATCH_UP
Occurs when a task is waiting on a latch for a buffer that is not in an I/O request. The latch request is in Update mode.
PARALLEL_BACKUP_QUEUE
Occurs when serializing output produced by RESTORE HEADERONLY, RESTORE FILELISTONLY, or RESTORE LABELONLY.
PRINT_ROLLBACK_PROGRESS
Used to wait while user processes are ended in a database that has been transitioned by using the ALTER DATABASE termination clause. For more information, see ALTER DATABASE (Transact-SQL).
QNMANAGER_ACQUIRE
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
QPJOB_KILL
Indicates that an asynchronous automatic statistics update was canceled by a call to KILL as the update was starting to run. The terminating thread is suspended, waiting for it to start listening for KILL commands. A good value is less than one second.
QPJOB_WAITFOR_ABORT
Indicates that an asynchronous automatic statistics update was canceled by a call to KILL when it was running. The update has now completed but is suspended until the terminating thread message coordination is complete. This is an ordinary but rare state, and should be very short. A good value is less than one second.
QRY_MEM_GRANT_INFO_MUTEX
Occurs when Query Execution memory management tries to control access to static grant information list. This state lists information about the current granted and waiting memory requests. This state is a simple access control state. There should never be a long wait on this state. If this mutex is not released, all new memory-using queries will stop responding.
QUERY_EXECUTION_INDEX_SORT_EVENT_OPEN
Occurs in certain cases when offline create index build is run in parallel, and the different worker threads that are sorting synchronize access to the sort files.
QUERY_NOTIFICATION_MGR_MUTEX
Occurs during synchronization of the garbage collection queue in the Query Notification Manager.
QUERY_NOTIFICATION_SUBSCRIPTION_MUTEX
Occurs during state synchronization for transactions in Query Notifications.
QUERY_NOTIFICATION_TABLE_MGR_MUTEX
Occurs during internal synchronization within the Query Notification Manager.
QUERY_NOTIFICATION_UNITTEST_MUTEX
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
QUERY_OPTIMIZER_PRINT_MUTEX
Occurs during synchronization of query optimizer diagnostic output production. This wait type only occurs if diagnostic settings have been enabled under direction of Microsoft Product Support.
QUERY_TRACEOUT
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
RECOVER_CHANGEDB
Occurs during synchronization of database status in warm standby database.
RG_RECONFIG
Occurs when ALTER RESOURCE GOVERNOR RECONFIGURE or ALTER RESOURCE GOVERNOR DISABLE is called.
REPL_CACHE_ACCESS
Occurs during synchronization on a replication article cache. During these waits, the replication log reader stalls, and data definition language (DDL) statements on a published table are blocked.
REPL_SCHEMA_ACCESS
Occurs during synchronization of replication schema version information. This state exists when DDL statements are executed on the replicated object, and when the log reader builds or consumes versioned schema based on DDL occurrence.
REPLICA_WRITES
Occurs while a task waits for completion of page writes to database snapshots or DBCC replicas.
REQUEST_DISPENSER_PAUSE
Occurs when a task is waiting for all outstanding I/O to complete, so that I/O to a file can be frozen for snapshot backup.
REQUEST_FOR_DEADLOCK_SEARCH
Occurs while the deadlock monitor waits to start the next deadlock search. This wait is expected between deadlock detections, and lengthy total waiting time on this resource does not indicate a problem.
RESMGR_THROTTLED
Occurs when a new request comes in and is throttled based on the GROUP_MAX_REQUESTS setting.
RESOURCE_QUEUE
Occurs during synchronization of various internal resource queues.
RESOURCE_SEMAPHORE
Occurs when a query memory request cannot be granted immediately due to other concurrent queries. High waits and wait times may indicate excessive number of concurrent queries, or excessive memory request amounts.
RESOURCE_SEMAPHORE_MUTEX
Occurs while a query waits for its request for a thread reservation to be fulfilled. It also occurs when synchronizing query compile and memory grant requests.
RESOURCE_SEMAPHORE_QUERY_COMPILE
Occurs when the number of concurrent query compilations reaches a throttling limit. High waits and wait times may indicate excessive compilations, recompiles, or uncachable plans.
RESOURCE_SEMAPHORE_SMALL_QUERY
Occurs when memory request by a small query cannot be granted immediately due to other concurrent queries. Wait time should not exceed more than a few seconds, because the server transfers the request to the main query memory pool if it fails to grant the requested memory within a few seconds. High waits may indicate an excessive number of concurrent small queries while the main memory pool is blocked by waiting queries.
SEC_DROP_TEMP_KEY
Occurs after a failed attempt to drop a temporary security key before a retry attempt.
SERVER_IDLE_CHECK
Occurs during synchronization of SQL Server instance idle status when a resource monitor is attempting to declare a SQL Server instance as idle or trying to wake up.
SHUTDOWN
Occurs while a shutdown statement waits for active connections to exit.
SLEEP_BPOOL_FLUSH
Occurs when a checkpoint is throttling the issuance of new I/Os in order to avoid flooding the disk subsystem.
SLEEP_DBSTARTUP
Occurs during database startup while waiting for all databases to recover.
SLEEP_DCOMSTARTUP
Occurs once at most during SQL Server instance startup while waiting for DCOM initialization to complete.
SLEEP_MSDBSTARTUP
Occurs when SQL Trace waits for the msdb database to complete startup.
SLEEP_SYSTEMTASK
Occurs during the start of a background task while waiting for tempdb to complete startup.
SLEEP_TASK
Occurs when a task sleeps while waiting for a generic event to occur.
SLEEP_TEMPDBSTARTUP
Occurs while a task waits for tempdb to complete startup.
SNI_CRITICAL_SECTION
Occurs during internal synchronization within SQL Server networking components.
SNI_HTTP_ACCEPT
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
SNI_HTTP_WAITFOR_0_DISCON
Occurs during SQL Server shutdown, while waiting for outstanding HTTP connections to exit.
SOAP_READ
Occurs while waiting for an HTTP network read to complete.
SOAP_WRITE
Occurs while waiting for an HTTP network write to complete.
SOS_CALLBACK_REMOVAL
Occurs while performing synchronization on a callback list in order to remove a callback. It is not expected for this counter to change after server initialization is completed.
SOS_LOCALALLOCATORLIST
Occurs during internal synchronization in the SQL Server memory manager.
SOS_MEMORY_USAGE_ADJUSTMENT
Occurs when memory usage is being adjusted among pools.
SOS_OBJECT_STORE_DESTROY_MUTEX
Occurs during internal synchronization in memory pools when destroying objects from the pool.
SOS_PROCESS_AFFINITY_MUTEX
Occurs during synchronizing of access to process affinity settings.
SOS_RESERVEDMEMBLOCKLIST
Occurs during internal synchronization in the SQL Server memory manager.
SOS_SCHEDULER_YIELD
Occurs when a task voluntarily yields the scheduler for other tasks to execute. During this wait the task is waiting for its quantum to be renewed.
SOS_STACKSTORE_INIT_MUTEX
Occurs during synchronization of internal store initialization.
SOS_SYNC_TASK_ENQUEUE_EVENT
Occurs when a task is started in a synchronous manner. Most tasks in SQL Server are started in an asynchronous manner, in which control returns to the starter immediately after the task request has been placed on the work queue.
SOS_VIRTUALMEMORY_LOW
Occurs when a memory allocation waits for a resource manager to free up virtual memory.
SOSHOST_EVENT
Occurs when a hosted component, such as CLR, waits on a SQL Server event synchronization object.
SOSHOST_INTERNAL
Occurs during synchronization of memory manager callbacks used by hosted components, such as CLR.
SOSHOST_MUTEX
Occurs when a hosted component, such as CLR, waits on a SQL Server mutex synchronization object.
SOSHOST_RWLOCK
Occurs when a hosted component, such as CLR, waits on a SQL Server reader-writer synchronization object.
SOSHOST_SEMAPHORE
Occurs when a hosted component, such as CLR, waits on a SQL Server semaphore synchronization object.
SOSHOST_SLEEP
Occurs when a hosted task sleeps while waiting for a generic event to occur. Hosted tasks are used by hosted components such as CLR.
SOSHOST_TRACELOCK
Occurs during synchronization of access to trace streams.
SOSHOST_WAITFORDONE
Occurs when a hosted component, such as CLR, waits for a task to complete.
SQLCLR_APPDOMAIN
Occurs while CLR waits for an application domain to complete startup.
SQLCLR_ASSEMBLY
Occurs while waiting for access to the loaded assembly list in the appdomain.
SQLCLR_DEADLOCK_DETECTION
Occurs while CLR waits for deadlock detection to complete.
SQLCLR_QUANTUM_PUNISHMENT
Occurs when a CLR task is throttled because it has exceeded its execution quantum. This throttling is done in order to reduce the effect of this resource-intensive task on other tasks.
SQLSORT_NORMMUTEX
Occurs during internal synchronization, while initializing internal sorting structures.
SQLSORT_SORTMUTEX
Occurs during internal synchronization, while initializing internal sorting structures.
SQLTRACE_BUFFER_FLUSH
Occurs when a task is waiting for a background task to flush trace buffers to disk every four seconds.
SQLTRACE_LOCK
Occurs during synchronization on trace buffers during a file trace.
SQLTRACE_SHUTDOWN
Occurs while trace shutdown waits for outstanding trace events to complete.
SQLTRACE_WAIT_ENTRIES
Occurs while a SQL Trace event queue waits for packets to arrive on the queue.
SRVPROC_SHUTDOWN
Occurs while the shutdown process waits for internal resources to be released to shutdown cleanly.
TEMPOBJ
Occurs when temporary object drops are synchronized. This wait is rare, and only occurs if a task has requested exclusive access for temp table drops.
THREADPOOL
Occurs when a task is waiting for a worker to run on. This can indicate that the maximum worker setting is too low, or that batch executions are taking unusually long, thus reducing the number of workers available to satisfy other batches.
TRACEWRITE
Occurs when the SQL Trace rowset trace provider waits for either a free buffer or a buffer with events to process.
TRAN_MARKLATCH_DT
Occurs when waiting for a destroy mode latch on a transaction mark latch. Transaction mark latches are used for synchronization of commits with marked transactions.
TRAN_MARKLATCH_EX
Occurs when waiting for an exclusive mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.
TRAN_MARKLATCH_KP
Occurs when waiting for a keep mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.
TRAN_MARKLATCH_NL
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.
TRAN_MARKLATCH_SH
Occurs when waiting for a shared mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.
TRAN_MARKLATCH_UP
Occurs when waiting for an update mode latch on a marked transaction. Transaction mark latches are used for synchronization of commits with marked transactions.
TRANSACTION_MUTEX
Occurs during synchronization of access to a transaction by multiple batches.
UTIL_PAGE_ALLOC
Occurs when transaction log scans wait for memory to be available during memory pressure.
VIEW_DEFINITION_MUTEX
Occurs during synchronization on access to cached view definitions.
WAIT_FOR_RESULTS
Occurs when waiting for a query notification to be triggered.
WAITFOR
Occurs as a result of a WAITFOR Transact-SQL statement. The duration of the wait is determined by the parameters to the statement. This is a user-initiated wait.
WAITSTAT_MUTEX
Occurs during synchronization of access to the collection of statistics used to populate sys.dm_os_wait_stats.
WORKTBL_DROP
Occurs while pausing before retrying, after a failed worktable drop.
WRITELOG
Occurs while waiting for a log flush to complete. Common operations that cause log flushes are checkpoints and transaction commits.
XACT_OWN_TRANSACTION
Occurs while waiting to acquire ownership of a transaction.
XACT_RECLAIM_SESSION
Occurs while waiting for the current owner of a session to release ownership of the session.
XACTLOCKINFO
Occurs during synchronization of access to the list of locks for a transaction. In addition to the transaction itself, the list of locks is accessed by operations such as deadlock detection and lock migration during page splits.
XACTWORKSPACE_MUTEX
Occurs during synchronization of defections from a transaction, as well as the number of database locks between enlist members of a transaction.
I was looking for some the messages mentioned by an incident and i found one that is quite common that SQL is taking more time to complete an io request.
So i found some good explanation on SQL Server Storage Engine blog.
Credits for them
-------------------------------------------------------------------------------
SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [T:\MSSQL\DATA\tempdb.mdf] in database [tempdb] (2). The OS file handle is 0x00000838. The offset of the latest long I/O is: 0x000000ebdc0000
You may hear this referred to as a stalled IO. I see questions on this warning message quite a bit, mostly about what’s wrong with SQL Server and why is SQL taking so long to read or write to the disk. Let me explain why this isn’t a SQL Server problem.
SQL Server does data file reads and writes almost exclusively as asynchronous IO, using the win32 APIs ReadFile, WriteFile, ReadFileScatter and WriteFileGather. Each of these APIs behave in a fairly similar manner – the caller sends in a handle to the file, some memory location to read or write, the size of the block and a structure that tells the kernel how to handle the IO. In SQL Server’s case the how to handle the IO is Asynchronously, please. The call returns immediately so that the thread issuing the IO can get out of the way and make life happy for some other user who’d also like their query to return.
The catch here is that ordinarily the time between the Asynchronous call to read or write and the completion of the read or write should be on the order of 10ms. Of course the longer it takes for an IO to return the more noticeable a performance impact there is to end users.
Prior to SQL Server 2000 SP4 the only way you would be able to tell if your IOs were taking longer than expected would be to use System Monitor and watch the PhysicalDisk\Avg Disk sec./read, write and transfer counter. This is a relatively acceptable method when the cause of your IO bottleneck is the latency of the physical disk, you might be surprised to find out that’s not the only thing that might slow down an IO!
Once a user mode application issues an IO request it’s the equivalent of putting a package in the mail, there’s nothing to do but wait. While you may not have anything to do once you’ve dropped the package the parcel carriers job has just begun, what with all the processing and labeling and transit – you get the idea. Likewise for the kernel. Once an IO transitions over to kernel mode it’s transformed into something called and IRP (interrupt request packet) and begins a trek down levels of filter drivers, virus scanners and device drivers before it finally makes its way home to a physical device.
Windows exposes methods for device and software manufactures to be notified of, and participate in, IOs. Filter drivers are one method of doing this, and it allows for great functionality of verifying you aren’t writing a file with a virus pattern, or backing up a file as it’s being written. The problem here is that the filter driver can hold up an IO for an extended period of time, and this time isn’t reflected in the Sysmon counter.
So what are you to do? You’ve put your specially crafted package in the mail, but the intended recipient still hasn’t got it.
The approach taken in SQL Server is to make note of the time the IO started, the offset of the IO within the file then check back a short time later to make sure all IOs have completed. If an IO is still outstanding and 15 seconds have elapsed then the warning is printed to the errorlog to alert the system administrator that something’s amiss.
When you see this message the first action should still be to have a look at the physical disk counters in sysmon to ensure that the disks are servicing IOs in a reasonable period of time. If those appear to fine then start looking at what filter drivers might be installed on your system, and if there are any known issues with them, or disable them if you don’t need them.
I was reading some artible and remembering...
One thing that my students always asked me, was "we can create a variable that is a table. How come can´t we assign a parameter to a function that is a table type?" Well, in SQL 2005 the problem is that is not possible to define inside the procedure one parameter that is a table.
Well on SQL 2008 now we can. There is an new element User-Defined Table Type. Is a data type that is actually a table definition. Look:
USE AdventureWorks2000
GO
-- Create the data type
CREATE TYPE dbo.SalesOrderItemType AS TABLE
(
SalesOrderID int,
LineNumber tinyint,
ProductID int,
SpecialOfferID int,
CarrierTrackingNumber nvarchar,
OrderQty smallint,
UnitPrice money,
UnitPriceDiscount float,
ModifiedDate datetime,
rowguid uniqueidentifier,
LineTotal float
)
GO
So you can create a procedure like this
CREATE PROCEDURE usp_insert_SalesOrderItem( @SalesItem dbo.SalesOrderItemType READONLY)
AS
GO
In this way, you create a procedure that receives a table variagle as a parameter, the table variable passed as argument for that must comply with the same data type, to have the same columns.
Besides, the paramter received must have, in the stored procedure definition the readonly clause meaning that the table must have the ge only read.
HAve fun! Remember, try to get rid of cursors, use this kind of instrument to not use cursors on your procedures or other code.
I was taking a look at the PSS Engineers blog, found out many interesting things there.
Specially that sql server is being supported on a virtualized environment, microsoft has changed the policy for that.
take a look at this article.
http://support.microsoft.com/kb/956893/en-us
enjoy
This is quite insteresting. I'm posting an excerpt from Microsoft support about the Sql server builds released
for more detail take a look at http://support.microsoft.com/kb/937137/en-us
949095 (http://support.microsoft.com/kb/949095/) Cumulative update package 7 for SQL Server 2005 Service Pack 2
946608 (http://support.microsoft.com/kb/946608/) Cumulative update package 6 for SQL Server 2005 Service Pack 2
943656 (http://support.microsoft.com/kb/943656/) Cumulative update package 5 for SQL Server 2005 Service Pack 2
SQL Hotfix bug number
Knowledge Base article number
Description
50002118
Contention occurs on a CMEMTHREAD wait type or on a spinlock.
SQL Hotfix bug number
Knowledge Base article number
Description
50002030
A CMEMTHREAD wait type causes high CPU utilization to occur under a load.
SQL Hotfix bug number
Knowledge Base article number
Description
50001986
A feature has been added that logs silent conflicts that are caused by divergent metadata.
SQL Hotfix bug number
Knowledge Base article number
Description
50001708
In SQL Server Management Studio, you cannot change the password for the db_securityadmin database role if the old password is not specified.
50001999
You cannot return to the original report in Windows Internet Explorer 7 after you open a report that accepts one or more DateTime parameters.
SQL Hotfix bug number
Knowledge Base article number
Description
50001951
In SQL Server Management Studio, you cannot change the password for the db_securityadmin database role if the old password is not specified.
50001993
You cannot return to the original report in Internet Explorer 7 after you open a report that accepts one or more DateTime parameters.
50001997
The new batching model that was introduced in SQL Server 2005 SP2 causes an out-of-memory exception when you run the Rebuild Index Task.
50001998
Event ID 17055 messages that an instance of SQL Server 2000 forwards to an instance of SQL Server 2005 are not received by the instance of SQL Server 2005.
50002000 (This bug is a variation of bug number 50000314.)
The Save dialog box in Report Builder stops responding in SQL Server 2005 SP2. This problem occurs if you start Report Builder when a report name is specified in the URL in Report Builder.
941450 (http://support.microsoft.com/kb/941450/) Cumulative update package 4 for SQL Server 2005 Service Pack 2
SQL Hotfix bug number
Knowledge Base article number
Description
50001812
A null reference exception occurs when you set parameters on a Web part that is linked to a document library part.
SQL Hotfix bug number
Knowledge Base article number
Description
50001766
942087 (http://support.microsoft.com/kb/942087/)
A C# application that was built by using SQL Server 2005 Integration Services build 9.00.3042 crashes, and an access violation occurs inDtspipeline.dll.
50001619
An internal exception occurs when when you process indexes.
50001625
In SQL Server 2005 Analysis Services Service Pack 2 (SP2), you experience frequent access violations in the PFCompressIterator::DecompressArray function under stress conditions.
50001713
Report Manager drill-down (+/-) does not work consistently with Snapshots.
50001550
Merge non-convergence occurs between the Republisher and the Publisher.
939537 (http://support.microsoft.com/kb/939537/) Cumulative update package 3 for SQL Server 2005 Service Pack 2
SQL Hotfix bug number
Knowledge Base article number
Description
50001298
938712 (http://support.microsoft.com/kb/938712/)
In Analysis Services 2005, a full process of a database may not process all the records for some dimensions
50001324
940129 (http://support.microsoft.com/kb/940129/)
An MDX query may not return results unless "non empty" is specified with the dimension that has unnatural hierarchy
50001440
940128 (http://support.microsoft.com/kb/940128/)
SQL Server may fail to generate a plan for a complex query that involves cursors and error 8623 occurs
SQL Hotfix bug number
Knowledge Base article number
Description
50001392
939942 (http://support.microsoft.com/kb/939942/)
FIX: You receive an error message when you try to access a report after you configure SQL Server 2005 Reporting Services to run under the SharePoint integrated mode
SQL Hotfix bug number
Knowledge Base article number
Description
50001482
The TokenAndPermUserStore cache store may continue to grow steadily and decrease performance
50001194
938243 (http://support.microsoft.com/kb/938243/)
Executing a full-text query against a catalog that is being rebuilt may fail with index corruption errors
SQL Hotfix bug number
Knowledge Base article number
Description
50001193
938086 (http://support.microsoft.com/kb/938086/)
Running concurrent SQL Server Agent jobs in the context of proxy account may fail with errors
50001352
938086 (http://support.microsoft.com/kb/938086/)
Running concurrent SQL Server Agent jobs in the context of proxy account may fail with errors
SQL Hotfix bug number
Knowledge Base article number
Description
50001391
939562 (http://support.microsoft.com/kb/939562/)
A query that contains an INSTEAD OF trigger may fail with error 8624 in SQL Server 2005 SP2
50001367
938363 (http://support.microsoft.com/kb/938363/)
Non-convergence may occur in merge replication when there are join filters for partition groups and the articles belong to multiple publications
50001379
939285 (http://support.microsoft.com/kb/939285/)
An incorrect error message may be returned by the SQL Native Client provider when in sync mode and there is a query time-out
50001408
939564 (http://support.microsoft.com/kb/939564/)
Querying a text column that contains more than 1,024 characters may return incorrect results when you use the SQL Native Client provider
50001397
939563 (http://support.microsoft.com/kb/939563/)
An UPDATE query or a DELETE query in a Merge Replication topology may fail with error 8152
936305 (http://support.microsoft.com/kb/936305/) Cumulative update package 2 for SQL Server 2005 Service Pack 2 is available
SQL Hotfix bug number
Knowledge Base article number
Description
50001251
937041 (http://support.microsoft.com/kb/937041/)
FIX: Changes in the publisher database are not replicated to the subscribers in a transactional replication if the publisher database runs exposed in a database mirroring session in SQL Server 2005
50001248
937033 (http://support.microsoft.com/kb/937033/)
FIX: Error message when you run a linked server query in SQL Server 2005: "The oledbprovider unisys.dmsII.1 for linkserver '<ServerName>' reported an error the provider ran out of memory"
SQL Hotfix bug number
Knowledge Base article number
Description
50001020
936185 (http://support.microsoft.com/kb/936185/)
FIX: Blocking and performance problems may occur when you enable trace flag 1118 in SQL Server 2005 if the temporary table creation workload is high
50000726
932106 (http://support.microsoft.com/kb/932106/)
FIX: Error message when you run a DMX query that uses a linked server to query data from an instance of Analysis Services in SQL Server 2005: "The following system error occurred: Logon failure: unknown user name or bad password"
50001024
934734 (http://support.microsoft.com/kb/934734/)
FIX: A database is marked as suspect when you update a table that contains a nonclustered index in SQL Server 2005
50000924
936863 (http://support.microsoft.com/kb/936863/)
FIX: Some MDX queries may run slower and consume more memory after you install SQL Server 2005 Analysis Services Service Pack 2
50000925
936262 (http://support.microsoft.com/kb/936262/)
In SQL Server 2005 Analysis Services, the cache is cleaned even though the memory usage is less than the LowMemoryLimit property that is configured.
50000898
936261 (http://support.microsoft.com/kb/936261/)
FIX: The process stops responding and the CPU utilization reaches 100 percent when you process a partition in SQL Server 2005 Analysis Services
50000802
932180 (http://support.microsoft.com/kb/932180/)
FIX: The Cluster Discrimination tab is empty when you browse a clustering model of SQL Server 2005 Analysis Services in SQL Server BIDS
50000920
935341 (http://support.microsoft.com/kb/935341/)
FIX: Error message when you run an MDX query against a measure in SQL Server 2005 Analysis Services: "An internal error has occurred"
50000732
932331 (http://support.microsoft.com/kb/932331/)
You receive an error message when you try to build a mining model that contains lots of data on a 32-bit server that is running SQL Server 2005 Analysis Services.
50000890
936254 (http://support.microsoft.com/kb/936254/)
FIX: An MDX query may be slow if it reads from an aggregation that has less than 4,096 records in SQL Server 2005 Analysis Services
SQL Hotfix bug number
Knowledge Base article number
Description
50001001
932610 (http://support.microsoft.com/kb/932610/)
FIX: Error message when you run an MDX query in SQL Server 2005 Analysis Services: "An unexpected error occurred (file 'pffilestore.cpp', line 3267, function 'PFFileStore::HandleDataPageFault')"
50001056
935922 (http://support.microsoft.com/kb/935922/)
FIX: Error message when you install Microsoft Dynamics CRM 3.0: "Setup failed to validate specified Reporting Services Report Server"
50000926
935360 (http://support.microsoft.com/kb/935360/)
FIX: Error message when you run an MDX query that retrieves data from an Analysis Services database: "An error occurred while the dimension, with the ID of '<DatabaseName>', Name of '<DimensionName>' was being processed"
50000923
935829 (http://support.microsoft.com/kb/935829/)
FIX: You receive incorrect results when you run an MDX query against a dimension that contains a parent-child hierarchy in SQL Server 2005 Analysis Services
50000871
935830 (http://support.microsoft.com/kb/935830/)
FIX: A server may start slowly if you have SQL Server 2005 Analysis Services installed and if many objects are stored on the server
50000927
A value from the SQL Server 2005 Analysis Services database may be larger than the value from the source database.
50000899
935831 (http://support.microsoft.com/kb/935831/)
When you configure proactive caching for a partition in SQL Server 2005 Analysis Services, the underlying partition may be processed two times during a proactive caching update.
50000885
935832 (http://support.microsoft.com/kb/935832/)
FIX: You cannot cancel an MDX query that runs for a long time in SQL Server 2005 Analysis Services
935356 (http://support.microsoft.com/kb/935356/) Cumulative update package (build 3161) for SQL Server 2005 Service Pack 2 is available
SQL Hotfix bug number
Knowledge Base article number
Description
50001012
934459 (http://support.microsoft.com/kb/934459/)
FIX: The Check Database Integrity task and the Execute T-SQL Statement task in a maintenance plan may lose database context in certain circumstances in SQL Server 2005 builds 3150 through 3158
SQL Hotfix bug number
Knowledge Base article number
Description
50000958
934226 (http://support.microsoft.com/kb/934226/)
FIX: Error message when you try to use Database Mail to send an e-mail message in SQL Server 2005: "profile name is not valid (Microsoft SQL Server, Error 14607)"
SQL Hotfix bug number
Knowledge Base article number
Description
50000900
933549 (http://support.microsoft.com/kb/933549/)
FIX: You may receive an access violation when you perform a bulk copy operation in SQL Server 2005
50000964
933724 (http://support.microsoft.com/kb/933724/)
FIX: The query performance is slow when you run a query that uses a user-defined scalar function against an instance of SQL Server 2005
50000875
932115 (http://support.microsoft.com/kb/932115/)
FIX: The ghost row clean-up thread does not remove ghost rows on some data files of a database in SQL Server 2005
50000969
933766 (http://support.microsoft.com/kb/933766/)
FIX: Failed assertion message in the Errorlog file when you perform various operations in SQL Server 2005: "Failed Assertion = 'fFalse' Attempt to access expired blob handle (3)"
50000919
When you run an encrypted stored procedure, error 565 may occur. The error message for error 565 is as follows: "A stack overflow occurred in the server while compiling the query. Please simplify the query."
50000918
933808 (http://support.microsoft.com/kb/933808/)
FIX: Error message when you run a query that contains nested FOR XML clauses in SQL Server 2005: "The XML data type is damaged"
50000921
933499 (http://support.microsoft.com/kb/933499/)
FIX: Error message when you use transactional replication to replicate the execution of stored procedures to subscribers in SQL Server 2005: "Insufficient memory to run query"
SQL Hotfix bug number
Knowledge Base article number
Description
50000948
934106 (http://support.microsoft.com/kb/934106/)
FIX: SQL Server 2005 database engine generates failed assertion errors when you use the Replication Monitor to monitor the distribution database
50000952
934188 (http://support.microsoft.com/kb/934188/)
FIX: The Distribution Agent does not deliver commands to the Subscriber even if the Distribution Agent is running in SQL Server 2005
50000949
934109 (http://support.microsoft.com/kb/934109/)
FIX: The Distribution Agent generates an access violation when you configure a transactional replication publication to run an additional script after the snapshot is applied at the subscriber in SQL Server 2005
50000960
A content query from the Data Mining Client for Excel add-in may take a long time to display the result set. This problem occurs especially when the result set is large. Microsoft Office Excel may appear to stop responding (hang) when the query is running.
SQL Hotfix bug number
Knowledge Base article number
Description
50000945
933564 (http://support.microsoft.com/kb/933564/)
FIX: A gradual increase in memory consumption for the USERSTORE_TOKENPERM cache store occurs in SQL Server 2005
933097 (http://support.microsoft.com/kb/933097/) Cumulative hotfix package (build 3152) for SQL Server 2005 Service Pack 2 is available
To monitor sql server agent job execution not using the gui, you can do the following
SELECT program_name, login_time, host_name
FROM sys.dm_exec_sessions
WHERE status = 'running'
or use the following sp
EXEC msdb.[dbo].[sp_help_job]
@execution_status = 1
